ZF-11429: Zend_Paginator_Adapter_DbSelect returns incorrect results after page 1 on DB2 IBM i/iSeries/AS/400

Description

The SQL query for limiting and offsetting results isn't being created properly for DB2 running on the IBM i (a.k.a., iSeries, AS/400).

In my use case, the paginator is set to 20 items per page, and there are 26 items.

It works fine for page 1 ("fetch first 20 rows only" is appended to the end of the SQL query).

For page 2, however, the SQL being created by Zend_Paginator_Adapter_DbSelect is like this:

{{SELECT z2.* FROM ( SELECT ROW_NUMBER() OVER() AS "ZEND_DB_ROWNUM", z1.* FROM ( SELECT field1, field2, field3 FROM table1 INNER JOIN table2 ON somefield = someotherfield WHERE (field4 = 'foo') ORDER BY field1 DESC ) z1 ) z2 WHERE z2.zend_db_rownum BETWEEN 21 AND 40}}

... and it returns the first six rows (that is, rows 1-6, not rows 21-26), ordered by field1 in ascending order.

The SQL query would work fine if the order by clause were inserted into {{OVER()}} like this:

{{SELECT z2.* FROM ( SELECT ROW_NUMBER() OVER(ORDER BY field1 DESC) AS "ZEND_DB_ROWNUM", z1.* FROM ( SELECT field1, field2, field3 FROM table1 INNER JOIN table2 ON somefield = someotherfield WHERE (field4 = 'foo') ORDER BY field1 DESC ) z1 ) z2 WHERE z2.zend_db_rownum BETWEEN 21 AND 40}}

We are running ZF 1.11.4 on an IBM iSeries V5R4 running PHP 5.3.2.

Comments

No comments to display