Issues

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

Issue Type: Bug Created: 2011-06-02T14:16:56.000+0000 Last Updated: 2011-06-02T14:16:56.000+0000 Status: Open Fix version(s): Reporter: Eugene Morgan (eugenemorgan) Assignee: Jurrien Stutterheim (norm2782) Tags: - Zend_Paginator

Related issues: Attachments:

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

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts