Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Zend_Db_Adapter_Db2 limit does not work #391

Closed
ushiday opened this issue Nov 24, 2023 · 1 comment
Closed

Zend_Db_Adapter_Db2 limit does not work #391

ushiday opened this issue Nov 24, 2023 · 1 comment
Milestone

Comments

@ushiday
Copy link

ushiday commented Nov 24, 2023

In the IBM i DB2 environment, when using "order by", the limit method of Zend_Db_Adapter_Db2 does not work in the following code section.(I can't try it with DB2 LUW in my environment)

        /**
         * DB2 does not implement the LIMIT clause as some RDBMS do.
         * We have to simulate it with subqueries and ROWNUM.
         * Unfortunately because we use the column wildcard "*",
         * this puts an extra column into the query result set.
         */
        return "SELECT z2.*
            FROM (
                SELECT ROW_NUMBER() OVER() AS \"ZEND_DB_ROWNUM\", z1.*
                FROM (
                    " . $sql . "
                ) z1
            ) z2
            WHERE z2.zend_db_rownum BETWEEN " . ($offset+1) . " AND " . ($offset+$count);

I think the problem is that ORDER BY is not included in OVER(). I also think this method is especially complicated because the LIMIT,OFFSET function was not implemented in previous versions of DB2, and the method was to emulate it.
Modern versions implement LIMIT,OFFSET, so it may be possible to write it simply. However, this method comes at the expense of some older version users...
Therefore, I will be posting two proposed code patterns in a pull request.

@develart-projects
Copy link
Collaborator

Closing this one, keeping LIMIT/OFFSET PR in draft for now.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants