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-09 18:03:30Z

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 9, 2016 at 18:03

answered Aug 9, 2016 at 17:56

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

e4c5 Over a year ago

I really hope so. I am horrible at arithmatic. @muistooshort

CXJ Over a year ago

<?php var_dump('2' + 1); var_dump(2 + '1'); int(3) int(3)

e4c5 Over a year ago

@CXJ I was just kidding in my previous comment. I had made a typo in the answer

CXJ Over a year ago

@e4c5 Yup. But sometimes PHP's dynamic typing produces unexpected results, so I thought I'd explicitly test it.

Colonel Thirty Two Over a year ago

@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.

|

CXJ · Accepted Answer · 2016-08-10 18:31:54Z

0

Reported and accepted as a PHP PDO bug:

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

answered Aug 10, 2016 at 18:31

CXJ

4,5073 gold badges38 silver badges66 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related