search

ibmdb / python-ibmdb-django

IBM DB2 Driver for the Django application Framework
Apache License 2.0
29 stars 42 forks source link

OVER() in Db2 for i #19

Closed norwizzle closed 3 years ago

norwizzle commented 6 years ago

The whole implementation to work around the lack of LIMIT/OFFSET support in Db2 has a glitch in Db2 for i (AS400) whereby the ORDER BY of the original query is not followed for results beyond the initial fetch. The problem seems to be here:

sql = '%s, ( ROW_NUMBER() OVER() ) AS "%s" FROM ( %s ) AS M' % ( sql_sel, self.__rownum, sql_pri )

Specifically, OVER() in Db2 for i seems to need an ORDER BY as a parameter for the ordering to work (I tested this by copy-and-pasting the ORDER BY of the original query in OVER(), and it worked. Whereas when I tested the exact same query in Db2 for LUW 11.1, it worked as-is.

Interestingly, LIMIT/OFFSET is now supported in Db2 for i (as of TR7 for 7.2). Pondering whether to just rewrite the code to incorporate this, or if anyone else has a better suggestion.

praveen-db2 commented 3 years ago

I hope this would be supported in latest adapter version. Hence closing this.