Edit report at https://bugs.php.net/bug.php?id=72798&edit=1
ID: 72798
Updated by: yohgaki@php.net
Reported by: php dot chaska at xoxy dot net
Summary: SELECT COUNT() returns string, not int.
Status: Wont fix
Type: Bug
Package: PDO SQLite
Operating System: FreeBSD, Mac OSX
PHP Version: 5.6.24
Block user comment: N
Private report: N
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[2016-08-09 21:27:31] 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 20:00:56] 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 19:12:05] 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"
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=72798&edit=1