Boolean literals in DQL queries get translated wrongly on PostgreSQL (PDOException)

Is this issue still valid in latest trunk?

Also, it seems PostgreSQL still supports '0', 'false', 0, f. 'off' in 8.4: http://www.postgresql.org/docs/8.4/static/datatype-boolean.html

Cheers,

I can not say anything about the latest dev snapshot, but in the 2.1.1 release it is valid. That should make it valid for the 2.1.2 release, too, which AFAIK is a pure security update to release 2.1.1.

The PostgreSQL docs say '0' (string value) is supported, while 0 (integer value) is not, even for Version 8.4. The single quotes are significant.

What doctrine type is the "runClosed" column?

The following test here compiles the boolean to 'false':

    /**
     * @group DDC-1394
     */
    public function testBoolean()
    {
        $bool = new BooleanModel();
        $bool->booleanField = true;

        $this->_em->persist($bool);
        $this->_em->flush();
        $this->_em->clear();

        $dql = "SELECT b FROM Doctrine\Tests\Models\Generic\BooleanModel b WHERE b.booleanField = true";
        $bool = $this->_em->createQuery($dql)->getSingleResult();

        $this->assertTrue($bool->booleanField);

        $bool->booleanField = false;

        $this->_em->flush();
        $this->_em->clear();

        $dql = "SELECT b FROM Doctrine\Tests\Models\Generic\BooleanModel b WHERE b.booleanField = false";
        $query = $this->_em->createQuery($dql);
        var_dump($query->getSQL());
        $bool = $query->getSingleResult();

        $this->assertFalse($bool->booleanField);
    }

string(111) "SELECT b0_.id AS id0, b0_.booleanField AS booleanField1 FROM boolean_model b0_ WHERE b0_.booleanField = 'false'"

I think this fixes the problem https://github.com/doctrine/doctrine2/commit/772b4135793f4405fc004cd008351a5ddfe8bdf5

Can you verify?

This is strange, for me this snippet of code encodes correctly (see above).

Can you print your PDO PGSQL Version? (php --info):

Mine is:

pdo_pgsql

PDO Driver for PostgreSQL => enabled
PostgreSQL(libpq) Version => 8.4.8
Module version => 1.0.2
Revision =>  $Id: pdo_pgsql.c 306939 2011-01-01 02:19:59Z felipe $ 

Here is the information you requested:

Run.isClosed is declared this way:

   /**
    * @Column(type="boolean")
    */
   
   protected $isClosed;

And the php --info output regarding pdo_pgsql:

pdo_pgsql

PDO Driver for PostgreSQL => enabled
PostgreSQL(libpq) Version => 8.4.7
Module version => 1.0.2
Revision =>  $Id: pdo_pgsql.c 300351 2010-06-10 12:11:19Z iliaa $

pg_sql

PostgreSQL Support -> enabled
PostgreSQL(libpq) Version => 8.4.7
Multibyte character support => enabled
SSL support => enabled
Active Persistent Links => 0
Active Links => 0

Looking at the fix, there could still be situations where wrapping a boolean literal into quotes could be necessary, e. g. when the representation returned by $this->_conn->getDatabasePlatform()->convertBooleans($bool) is 't'. This would depend on the value returned by convertBooleans(). Then again, this could still be moved into convertBooleans() in cases where it is necessary.

I applied the fix, and again ran the code on PostgreSQL 8.4. It worked.

Now, funnily, after reverting the change to really prove it depended on the change, it still worked. So I am currently unable to reproduce the exact circumstances under which the error happened, and I am unhappy with this. The queries are still the same.

The place where guilherme removed the quote was only ever reachable for using the literal constants "true" and "false" in DQL. The convertBoolean methods returns the data correctly already from my first glance.

I would rather know what exactly the problem is myself though. This is not satisfactory otherwise.

You are on the latest version aswell, the diff between my pdo_pgsql.c and yours is just the fixed year.

I have tried to reproduce the behaviour, but with no success. Without the github fix the boolean literal ends up with quotes in the SQL query, and with the fix it ends up without them, but since both false and 'false' are valid boolean literals, this does not matter to PostgreSQL, and it should be valid both ways.

How boolean literals could have been compiled to numeric literals is beyond me. Regarding the github fix, I do not think it cannot have any influence on this except for quoting values.

I can only speculate that I must have goofed up somewhere while syncing the online code with the IDE, causing old code (with numeric literals in the DQL queries) to get executed,
while the code in the IDE was already updated to use proper boolean literals. This is pure speculation however. Reproducing what exactly happened at the time of reporting the bug is impossible, since I executed the code it in a VM, which was reset several times since then. But since right now I cannot myself reproduce the behaviour in any way, I must assume I messed up.

Feel free to close this as invalid for lack of reproducibility. I am sorry, if I wasted your time.

If I should run into something similiar in the future I'll update this.

Closing for now, hopefully for good