Regression version 2.9.3: Using limit in subselects with PDO causes bindParameters to fail

[rjd22]

• I was not able to find an open or closed issue matching what I'm seeing.
• This is not a question. (Questions should be asked on slack (Signup for Slack here) or our forums.)

Sub queries with limit seems not to work like it is supposed to anymore in version 2.9.3 in combination with PDO. Likely the regression has been introduced in the following PR.

#300

Code to reproduce the issue

$queryBuilder = \Zend\Db\Sql\Sql();

$update = $queryBuilder->update('table1');
$update->set([ 'start_date' => 'now()']);

$select = $queryBuilder
    ->select('table1')
    ->columns(['id'])
    ->where([
        'start_date' => null,
        'completed_date' => null,
    ])
    ->limit(25);

$update->where(new In('id', $select));

$statement = $queryBuilder->prepareStatementForSqlObject($update);
$statement->execute();

Expected results

The query should work as expected like in version 2.9.2

Actual results

The following query and parameters are generated:

UPDATE "table1" 
SET "start_date" = :c_0 
WHERE "id" IN (
    SELECT "table1"."id" AS "id" 
    FROM "table1" 
    WHERE "start_date" IS NULL 
    AND "completed_date" IS NULL 
    LIMIT :limit
)

Parameters

array(2) {
    ["c_0"]=> string(5) "now()"
    ["subselect1limit"]=>  int(25)
}

An exception occurs:

exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: :subselect1limit' in vendor/zendframework/zend-db/src/Adapter/Driver/Pdo/Statement.php:298

Stack trace:

#0 vendor/zendframework/zend-db/src/Adapter/Driver/Pdo/Statement.php(298): PDOStatement->bindParam(':subselect1limi...', 25, 1)

#1 vendor/zendframework/zend-db/src/Adapter/Driver/Pdo/Statement.php(232): Zend\Db\Adapter\Driver\Pdo\Statement->bindParametersFromContainer()

<snip>

[samsonasik]

ping @tptrixtop for verify

[Ocramius]

Yeah, I'm also locked onto 2.9.2

[michalbundyra]

@rjd22

I was trying to investigate that issue but the provided test case is invalid...

First of all I am getting: Call to undefined method Zend\Db\Sql\Update::returning().
Then I believe we should have $queryBuilder->prepareStatementForSqlObject($update); ($query -> $update).

Anyway I can't get any failing example based on provided details and code, which should fail. Checked with latest master...

[rjd22]

@webimpress You're right. I changed the example. We are using pdo_pgsql. Are you able to run the query without any issues? Can you share how you tried to replicate it?

[michalbundyra]

@rjd22 this is what I was trying to do:

    public function test293()
    {
        $driver = new \Zend\Db\Adapter\Driver\Pdo\Pdo([
            'driver' => 'Pdo_Pgsql',
        ]);

        $platform  = new Postgresql($driver);

        $mockStatement = $this->getMockBuilder('Zend\Db\Adapter\Driver\StatementInterface')->getMock();
        $mockDriver = $this->getMockBuilder('Zend\Db\Adapter\Driver\DriverInterface')->getMock();
        $mockDriver->expects($this->any())->method('formatParameterName')->will($this->returnValue('?'));
        $mockDriver->expects($this->any())->method('createStatement')->will($this->returnValue($mockStatement));

        $adapter = new Adapter($mockDriver, $platform);

        $queryBuilder = new \Zend\Db\Sql\Sql($adapter);

        $update = $queryBuilder->update('table1');
        // $update->returning([ 'id', 'other_id']); // <!-- when uncommented I am getting: Error: Call to undefined method Zend\Db\Sql\Update::returning()
        $update->set([ 'start_date' => 'now()']);

        $select = $queryBuilder
            ->select('table1')
            ->columns(['id'])
            ->where([
                'start_date' => null,
                'completed_date' => null,
            ])
            ->limit(25);

        $update->where(new In('id', $select));

        $statement = $queryBuilder->prepareStatementForSqlObject($update);
        $statement->execute();
    }

So with uncommented $update->returning([ 'id', 'other_id']); I am getting the following error:

Error: Call to undefined method Zend\Db\Sql\Update::returning()

and when I comment that line I am not getting any errors.

If you can provide some valid test case - test which is failing - it will be much easier to find the problem and fix it. Thanks!

[rjd22]

@webimpress Thank you for testing. I checked the returning part and it seems that is a functionality that we added. I removed this functionality in our codebase and the same issue still occurs. I removed it from the example code for now.

[tptrixtop]

Looks like that issue was fixed in #329 after my fix, i am not sure that fix released)

[michalbundyra]

@tptrixtop Hah... it looks merged, but not released 😢

@rjd22 Any chance you can try the same with master of zend-db, please?

/ping @weierophinney / @ezimuel Any chance we can release hotfixes merged to master?

[rjd22]

@webimpress @tptrixtop I was able to replicate the issue with a composer checked out version of 2.9.3 with the following hash 5b4f2c42f94c9f7f4b2f456a0ebe459fab12b3d9 by running the following script against a real pgsql database.

    public function test293()
    {
        $driver = new \Zend\Db\Adapter\Driver\Pdo\Pdo([
            'driver' => 'pdo_pgsql',
            'database' => 'database',
            'username' => 'username',
            'password' => 'password',
            'hostname' => 'localhost',
        ]);

        $adapter = new Adapter($driver);

        $queryBuilder = new \Zend\Db\Sql\Sql($adapter);

        $update = $queryBuilder->update('table1');
        $update->set([ 'start_date' => 'now()']);

        $select = $queryBuilder
            ->select('table1')
            ->columns(['id'])
            ->where([
                'start_date' => null,
                'completed_date' => null,
            ])
            ->limit(25);

        $update->where(new In('id', $select));

        $statement = $queryBuilder->prepareStatementForSqlObject($update);
        $statement->execute();
    }

[rjd22]

@webimpress I also ran the above test against master with the hash 164a1043a0f222fb5d718d3267193d53df54c7d5 and the result is still the same:

1) FooTest::test293
PDOException: SQLSTATE[HY093]: Invalid parameter number: :subselect1limit

vendor/zendframework/zend-db/src/Adapter/Driver/Pdo/Statement.php:293
vendor/zendframework/zend-db/src/Adapter/Driver/Pdo/Statement.php:230
FooTest.php:40

[michalbundyra]

@rjd22 Ok, I'll have a look on it, soon ! Thanks

[michalbundyra]

Please see #357. I have found that the same problem we have for offset in subquery. It's fixed now.

@rjd22 Can you retest it please with the hotfix? Thanks!

[rjd22]

@webimpress This indeed fixes the issue. 👍

[michalbundyra]

ping @ezimuel