zendframework/zf2 — pull request #3198 by ralphschindler (2014-01-07 17:23:08+00:00)
Title: Limit/offset doesn't work properly when using parameters and SQL Server drivers

When using the native SQL Server driver, it seems that using limit and offset works fine with a query that has no parameters, but doesn't work with a query that has parameters. For example, the following will return the expected results:

$select = new \Zend\Db\Sql\Select();
$select->from('mytable')
->order('myfield')
->limit(5)
->offset(0);

$sql = new \Zend\Db\Sql\Sql($db);
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();

Whereas the following won't:

$select = new \Zend\Db\Sql\Select();
$select->from('mytable')
->where('aField = ?')
->order('myfield')
->limit(5)
->offset(0);

$sql = new \Zend\Db\Sql\Sql($db);
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute(array(1));

I believe the reason has something to do with the order of the parameters getting messed up. To be more specific, it seems like when ParameterContainer calls getPositionalArray(), the offset/limit parameters come before the where parameters when it should actually be the opposite.

For the PDO SQL server, the problem is totally different. When trying to run the first case above, I get the following error:

Incorrect syntax near 'LIMIT'

That leads me to believe the ROW_NUMBER() function has not been implemented, not sure it's a bug or if it'll be unsupported for PDO, though I've searched and don't see anything saying that the drivers provided by Microsoft don't support it.

If I run the second case above, then I get the following error:

Invalid parameter number: mixed named and positional parameters

It does not throw this error if I change my where clause to use named parameters, but seems like I should be allowed to use positional or named parameters in my where clause as long as I stick to only one. Regardless, if I change to named parameters, I get the first error for syntax error near LIMIT.