php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #72798 SELECT COUNT() returns string, not int.
Submitted: 2016-08-09 19:12 UTC Modified: 2016-08-10 11:29 UTC
Votes:1
Avg. Score:4.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: php dot chaska at xoxy dot net Assigned:
Status: Analyzed Package: PDO SQLite
PHP Version: 5.6.24 OS: FreeBSD, Mac OSX
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: php dot chaska at xoxy dot net
New email:
PHP Version: OS:

 

 [2016-08-09 19:12 UTC] php dot chaska at xoxy dot net
Description:
------------
---
From manual page: http://www.php.net/pdostatement.fetchcolumn
---
With this SQL statement

SELECT count(*) FROM my_table;

fetchColumn() returns an INTEGER with Postgres and a STRING with Sqlite. That is, if there is one row in the table, Postgres returns (int)1 and Sqlite returns '1'.

Note that

SELECT TYPEOF(b) FROM ( select count(*) as b from my_table) a;

produces integer in Sqlite.

See also http://stackoverflow.com/questions/38857255/php-pdo-postgres-versus-sqlite-column-type-for-count



Test script:
---------------
http://pastebin.com/arGpxjm7

Expected result:
----------------
I expect both Postgres and Sqlite to return an integer type from fetchColumn() in both cases, since that is what the database claims it is returning.  That is, I expect this result from my test script:

Postgres: int(1)
Sqlite3:  int(1)

Actual result:
--------------
Postgres: int(1)
Sqlite3:  string(1) "1"

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-08-09 20:00 UTC] cmb@php.net
-Status: Open +Status: Verified
 [2016-08-09 20:00 UTC] cmb@php.net
I can confirm this behavior for other queries as well, see
<https://3v4l.org/bh03r>.

Actually, this report is a duplicate of request #38334, but I do
not really agree with the mentioned reasoning that "SQLite by its
nature is a typeless database, […]". SQLite3 supports manifest
typing[1], what is not typeless; otherwise one may claim that PHP
would be also typeless.

And, for what it's worth, ext/sqlite3 returns an int from the same
query, see <https://3v4l.org/hiPXa#v560>. It shouldn't be too hard
to add something like sqlite_value_to_zval() to PDO_SQLite.

[1] <http://sqlite.org/different.html#typing>
[2] <https://github.com/php/php-src/blob/PHP-7.0.10/ext/sqlite3/sqlite3.c#L580-L608>
 [2016-08-09 21:27 UTC] yohgaki@php.net
-Status: Verified +Status: Wont fix
 [2016-08-09 21:27 UTC] yohgaki@php.net
Numeric values MUST NOT be converted to PHP types.

It just don't work. Int could be 32 or 64 bits (+ signedness). It may be 32, 64 or 128 bits in near future.
 [2016-08-09 21:40 UTC] yohgaki@php.net
External numeric values must not be converted to PHP (or any other language) types automatically.

Automatic conversion is OK only when external numeric value and PHP type is 100% compatible. Therefore, conversion must not be automatic, but manual. Otherwise, programs lose data or misbehave.

We've seen this kind of problems in DB, XML, JSON, etc already and should not create no more problems.
 [2016-08-10 09:51 UTC] cmb@php.net
-Status: Wont fix +Status: Re-Opened
 [2016-08-10 09:51 UTC] cmb@php.net
> Automatic conversion is OK only when external numeric value and
> PHP type is 100% compatible. Therefore, conversion must not be
> automatic, but manual. Otherwise, programs lose data or
> misbehave.

IMHO, it is better to do the conversion internally, if and only if
it is lossless (like sqlite_value_to_zval() does). Otherwise the
burden of proper conversion is up to the userland developer, who
might easily just do a simple `(int) $val`, what would be wrong,
see <https://3v4l.org/jgO2M>. With ext/sqlite3 the dev could rely
on a simple `is_int($val)` instead of `$val <= PHP_INT_MAX && $val
>= PHP_INT_MIN` or `filter_var($val, FILTER_VALIDATE_INT) !==
false`.

Anyhow, the behavior of the different PDO drivers should be
adjusted to match each other. Doing otherwise, would somehow
defeat the the purpose of having PDO[1] (emphasis mine):

> The PHP Data Objects (PDO) extension defines a lightweight,
> *consistent* interface for accessing databases in PHP.

[1] <http://php.net/manual/en/intro.pdo.php>
 [2016-08-10 09:59 UTC] kalle@php.net
-Status: Re-Opened +Status: Analyzed
 [2016-08-10 09:59 UTC] kalle@php.net
@yohgaki well in 7.0+, we have a much better and more reliant 64 bit system implemented, and I think that at least on Windows we would make PHP's int always 64 bit, and have a way to emulate that, and I don't see a reason why we cannot reliably convert to a PHP int if we can guarantee that PHP's int always will be at mimimum 64 bit.

As for the 128 bit part, I expect us to support 128 bit as other database systems and hardware decides to adopt that, so I don't forsee that as an issue.
 [2016-08-10 10:15 UTC] cmb@php.net
On x86 builds (which are still distributed), however:

  PHP_INT_MAX === 2147483647
 [2016-08-10 11:25 UTC] yohgaki@php.net
Even with 64 bit systems, we have issues. Signedness is issue.

For instance, I have code that store 64 bits hash value and stored into unsigned int64 database field. Since PHP's int is signed, conversion will have unwanted result unless I use sprintf().

i.e. 

 $hash == $hash_val_stored_in_db

cannot evaluated as the same value because of signed int...

PostgreSQL's int64 is always signed, but MySQL int64 could be unsigned for instance.
 [2016-08-10 11:29 UTC] yohgaki@php.net
However, I don't object strongly, if this is the PDO way should be :)
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Tue Nov 12 18:01:32 2019 UTC