PHP PDO Postgres versus Sqlite column type for count(*)

Question

Using PHP PDO with this SQL statement

SELECT count(*) FROM my_table;

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

Is this as intended, or is this a bug?

[edit to add below]

In case you want to follow along at home, here's a demonstration script I threw together. I actually encountered this when my PHPUnit tests passed (using in-memory Sqlite as a test fixture) and but my application failed using the production Postrgres database.

<?php
function connect($dsn)
{
    try {
        $pdo = new \PDO($dsn);
    }
    catch (\PDOException $e) {
        echo 'New PDO failed: ' . $e->getMessage() . PHP_EOL;
        exit;
    }
    return $pdo;
}

function doQuery($pdo, $sql)
{
    if ( ($result = $pdo->query($sql)) === false) {
        echo "'$sql' failed: " . print_r($pdo->errorInfo(), true) . PHP_EOL;
    }
    return $result;
}

$pgo = connect('pgsql:host=localhost;dbname=postgres');
$sqo = connect('sqlite::memory:');

doQuery($pgo, 'DROP TABLE IF EXISTS public.foo');
doQuery($pgo, 'CREATE TABLE public.foo ( ii int )');
doQuery($pgo, 'INSERT INTO public.foo VALUES (42)');

doQuery($sqo, "ATTACH DATABASE ':memory:' AS public;") or die();
doQuery($sqo, 'DROP TABLE IF EXISTS public.foo');
doQuery($sqo, 'CREATE TABLE public.foo ( ii int )');
doQuery($sqo, 'INSERT INTO public.foo VALUES (42)');

$pgResult = doQuery($pgo, 'SELECT COUNT(ii) FROM foo');
echo 'Postgres: ';
var_dump($pgResult->fetchColumn());

echo 'Sqlite3: ';
$ltResult = doQuery($sqo, 'SELECT COUNT(ii) FROM foo');
var_dump($ltResult->fetchColumn());

e4c5 · Accepted Answer · 2016-08-09T18:03:30.963

1

This is a side effect of sqlite not having datatypes. Or rather, having what they call the dynamic type system. But quite interestingly

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

produces integer as the output! So clearly something is being lost in translation from sqlite to php. However it doesn't really matter because in php '1' + 2 gives 3. Because let's not forget, PHP is also a dynamic typed system.

edited Aug 09 '16 at 18:03

answered Aug 09 '16 at 17:56

e4c5

52,766
11
101
134

I really hope so. I am horrible at arithmatic. @muistooshort – e4c5 Aug 09 '16 at 18:03
` – CXJ Aug 09 '16 at 18:05
@CXJ I was just kidding in my previous comment. I had made a typo in the answer – e4c5 Aug 09 '16 at 18:05
@e4c5 Yup. But sometimes PHP's dynamic typing produces unexpected results, so I thought I'd explicitly test it. – CXJ Aug 09 '16 at 18:35
@CXJ Implicit conversion between strings and integers (and other data types) is not dynamic typing. Some people call it _weak typing_, but that term is fairly ill-defined. – Colonel Thirty Two Aug 10 '16 at 01:17
Yes @ColonelThirtyTwo yes it's fairly ill define but this definition here is right from the sqlite manual. There have been numerous debates here on SO on what exactly is the right term for the type system in PHP – e4c5 Aug 10 '16 at 01:40

score 0 · Answer 2 · answered Aug 10 '16 at 18:31

0

Reported and accepted as a PHP PDO bug:

https://bugs.php.net/bug.php?id=72798

answered Aug 10 '16 at 18:31

CXJ

4,301
3
32
62

PHP PDO Postgres versus Sqlite column type for count(*)

2 Answers2

Linked