ZF-4790: Limit() for DB2

Description

Hello, The ROW_NUMBER() OVER(), is a select with random order. To avoid this, you must define an "ORDER BY" clause in the OVER().

See the official IBM documentation :

ROW_NUMBER specifies that a sequential row number is computed for the row that is defined by the ordering, starting with 1 for the first row. If the ORDER BY clause is not specified in the window, the row numbers are assigned to the rows in an arbitrary order, as the rows are returned (but not according to any ORDER BY clause in the select-statement). You can use ROW_NUMBER to number the result rows of a query. Row numbers also enable easy formulation of queries for computing histogram statistics (quantile computations), and they enable formation of other OLAP specifications (for example, moving sums, moving averages, and so on).


    function limit  ($sql, $count, $offset = 0){
...... 
       $limit_sql = "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);
        return $limit_sql;



  function limit  ($sql, $count, $offset = 0,$attribut,$order='ASC'){
...... 
       $limit_sql = "SELECT z2.*
            FROM (
                SELECT ROW_NUMBER() OVER(". $attribut ." ". $order .") AS \"ZEND_DB_ROWNUM\", z1.*
                FROM (
                    " . $sql . "
                ) z1
            ) z2
            WHERE z2.zend_db_rownum BETWEEN " . ($offset+1) . " AND " . ($offset+$count);
        return $limit_sql;


Comments

is this still an issue?

For 1.7.2, Yes

A similar issue as this was solved in ZF-8148 for Zend_Db_Adapter_Sqlsrv back in April.

In addition to ZF-10980, here's two tests for DB2 that uses a profiler to check the SQL generated by the testAdapterLimit and testAdapterLimitOffset tests from TestCommon.php. The point is to express what the SQL should be. These tests don't need to be a permanent part of the test suite, although they could. The idea is to make it dead simple to study any proposed fixes in the short term -- "write the tests first", test-driven development, yadda-yadda.

Index: tests/Zend/Db/Adapter/Db2Test.php
===================================================================
--- tests/Zend/Db/Adapter/Db2Test.php   (revision 23613)
+++ tests/Zend/Db/Adapter/Db2Test.php   (working copy)
@@ -365,6 +365,62 @@
         $this->markTestSkipped($this->getDriver() . ' does not have TEXT field type');
     }
 
+    public function testLimit()
+    {
+        $profiler = new Zend_Db_Profiler();
+        $profiler->setEnabled(true);
+        $this->_db->setProfiler($profiler);
+
+        $this->testAdapterLimit();
+
+        $profiler = $this->_db->getProfiler();
+        $this->assertInstanceOf('Zend_Db_Profiler',
+                                $profiler);
+
+        $profile = $profiler->getLastQueryProfile();
+        $this->assertInstanceOf('Zend_Db_Profiler_Query',
+                                $profile);
+
+        $this->assertEquals(Zend_Db_Profiler::SELECT,
+                            $profile->getQueryType());
+
+        $qry = 'SELECT * FROM "zfproducts" ORDER BY "product_id" FETCH FIRST 1 ROWS ONLY';
+        $this->assertEquals($qry, $profile->getQuery());
+    }
+
+    public function testLimitOffset()
+    {
+        $profiler = new Zend_Db_Profiler();
+        $profiler->setEnabled(true);
+        $this->_db->setProfiler($profiler);
+
+        $this->testAdapterLimitOffset();
+
+        $profiler = $this->_db->getProfiler();
+        $this->assertInstanceOf('Zend_Db_Profiler',
+                                $profiler);
+
+        $profile = $profiler->getLastQueryProfile();
+        $this->assertInstanceOf('Zend_Db_Profiler_Query',
+                                $profile);
+
+        $this->assertEquals(Zend_Db_Profiler::SELECT,
+                            $profile->getQueryType());
+
+        $qry = 'SELECT z2.*
+            FROM (
+                SELECT ROW_NUMBER() OVER(ORDER BY "product_id") AS "ZEND_DB_ROWNUM", z1.*
+                FROM (
+                    SELECT * FROM "zfproducts"
+                ) z1
+            ) z2
+            WHERE z2.zend_db_rownum BETWEEN 2 AND 2';
+
+        $this->assertEquals($qry, $profile->getQuery(),
+'ZF-4790: Zend_Db_Adapter_Db2::limit() needs to specify proper OVER()');
+
+    }
+
     public function getDriver()
     {
         return 'Db2';

Here's a proposed fix with some tests. The tests are against the change suggested in ZF-10980.

This also proposes letting the user specify the {{ROW_NUMBER() OVER() AS "ZEND_DB_ROWNUM"}} syntax in their query. I'm thinking this could help in the corner cases where the naive solution to extract the ORDER BY statement becomes a hindrance.

Index: library/Zend/Db/Adapter/Db2.php
===================================================================
--- library/Zend/Db/Adapter/Db2.php (revision 23692)
+++ library/Zend/Db/Adapter/Db2.php (working copy)
@@ -685,6 +685,24 @@
             return $limit_sql;
         }
 
+        $select = stripos($sql, 'SELECT');
+        $rownum = strpos($sql, 'ZEND_DB_ROWNUM');
+        if (false === $rownum && false !== $select) {
+            $order = preg_match('/ORDER\s+BY/i', $sql, $matches);
+            if ($order > 0) {
+                $order_pos = strpos($sql, $matches[$order - 1]);
+                $order = substr($sql, $order_pos);
+                $sql = substr($sql, 0, $order_pos - 1);
+            } else {
+                $order = '';
+            }
+            $sql = "SELECT ROW_NUMBER() OVER($order) AS \"ZEND_DB_ROWNUM\", z1.*
+                FROM (
+                    $sql
+                ) z1";
+                    
+        }
+
         /**
          * DB2 does not implement the LIMIT clause as some RDBMS do.
          * We have to simulate it with subqueries and ROWNUM.
@@ -693,10 +711,7 @@
          */
         $limit_sql = "SELECT z2.*
             FROM (
-                SELECT ROW_NUMBER() OVER() AS \"ZEND_DB_ROWNUM\", z1.*
-                FROM (
-                    " . $sql . "
-                ) z1
+                " . $sql . "
             ) z2
             WHERE z2.zend_db_rownum BETWEEN " . ($offset+1) . " AND " . ($offset+$count);
         return $limit_sql;
Index: tests/Zend/Db/Adapter/Db2Test.php
===================================================================
--- tests/Zend/Db/Adapter/Db2Test.php   (revision 23692)
+++ tests/Zend/Db/Adapter/Db2Test.php   (working copy)
@@ -365,6 +365,191 @@
         $this->markTestSkipped($this->getDriver() . ' does not have TEXT field type');
     }
 
+    public function testLimitWithRownum()
+    {
+        $profiler = new Zend_Db_Profiler();
+        $profiler->setEnabled(true);
+        $this->_db->setProfiler($profiler);
+
+        $products = $this->_db->quoteIdentifier('zfproducts');
+        $product_id = $this->_db->quoteIdentifier('product_id');
+
+        $sql = $this->_db->limit("SELECT $products.*, ROW_NUMBER() OVER(ORDER BY $product_id DESC) AS \"ZEND_DB_ROWNUM\" FROM $products", 1);
+
+        $stmt = $this->_db->query($sql);
+        $result = $stmt->fetchAll();
+        $this->assertEquals(1, count($result),
+            'Expecting row count to be 1');
+        $this->assertEquals(2, count($result[0]),
+            'Expecting column count to be 2');
+        $this->assertEquals(3, $result[0]['product_id'],
+            'Expecting to get product_id 3');
+
+        // Check that extra field ZEND_DB_ROWNUM isn't present
+        // (particulary with Db2 & Oracle)
+        $this->assertArrayNotHasKey('zend_db_rownum', $result[0]);
+        $this->assertArrayNotHasKey('ZEND_DB_ROWNUM', $result[0]);
+
+        $profiler = $this->_db->getProfiler();
+        $this->assertInstanceOf('Zend_Db_Profiler',
+                                $profiler);
+
+        $profile = $profiler->getLastQueryProfile();
+        $this->assertInstanceOf('Zend_Db_Profiler_Query',
+                                $profile);
+
+        $this->assertEquals(Zend_Db_Profiler::SELECT,
+                            $profile->getQueryType());
+
+        $qry = "SELECT $products.*, ROW_NUMBER() OVER(ORDER BY $product_id DESC) AS \"ZEND_DB_ROWNUM\" FROM $products FETCH FIRST 1 ROWS ONLY";
+
+        $this->assertEquals($qry, $profile->getQuery(),
+'ZF-4790: Zend_Db_Adapter_Db2::limit() needs to specify proper OVER()');
+    }
+
+    public function testLimitOffsetWithRownum()
+    {
+        $profiler = new Zend_Db_Profiler();
+        $profiler->setEnabled(true);
+        $this->_db->setProfiler($profiler);
+
+        $products = $this->_db->quoteIdentifier('zfproducts');
+        $product_id = $this->_db->quoteIdentifier('product_id');
+
+        $sql = $this->_db->limit("SELECT * FROM (SELECT $products.*, ROW_NUMBER() OVER(ORDER BY $product_id DESC) AS \"ZEND_DB_ROWNUM\" FROM $products) zfp", 1, 2);
+
+        $stmt = $this->_db->query($sql);
+        $result = $stmt->fetchAll();
+        $this->assertEquals(1, count($result),
+            'Expecting row count to be 1');
+        $this->assertEquals(2, count($result[0]),
+            'Expecting column count to be 2');
+        $this->assertEquals(1, $result[0]['product_id'],
+            'Expecting to get product_id 1');
+
+        // Check that extra field ZEND_DB_ROWNUM isn't present
+        // (particulary with Db2 & Oracle)
+        $this->assertArrayNotHasKey('zend_db_rownum', $result[0]);
+        $this->assertArrayNotHasKey('ZEND_DB_ROWNUM', $result[0]);
+
+        $profiler = $this->_db->getProfiler();
+        $this->assertInstanceOf('Zend_Db_Profiler',
+                                $profiler);
+
+        $profile = $profiler->getLastQueryProfile();
+        $this->assertInstanceOf('Zend_Db_Profiler_Query',
+                                $profile);
+
+        $this->assertEquals(Zend_Db_Profiler::SELECT,
+                            $profile->getQueryType());
+
+        $qry = "SELECT z2.*
+            FROM (
+                SELECT * FROM (SELECT $products.*, ROW_NUMBER() OVER(ORDER BY $product_id DESC) AS \"ZEND_DB_ROWNUM\" FROM $products) zfp
+            ) z2
+            WHERE z2.zend_db_rownum BETWEEN 3 AND 3";
+
+        $this->assertEquals($qry, $profile->getQuery(),
+'ZF-4790: Zend_Db_Adapter_Db2::limit() needs to specify proper OVER()');
+    }
+
+    public function testLimitOffsetWithSubselect()
+    {
+        $profiler = new Zend_Db_Profiler();
+        $profiler->setEnabled(true);
+        $this->_db->setProfiler($profiler);
+
+        $products = $this->_db->quoteIdentifier('zfproducts');
+        $product_id = $this->_db->quoteIdentifier('product_id');
+
+        $sql = $this->_db->limit("SELECT * FROM (SELECT * FROM $products) zfp ORDER BY $product_id DESC", 1, 2);
+
+        $stmt = $this->_db->query($sql);
+        $result = $stmt->fetchAll();
+        $this->assertEquals(1, count($result),
+            'Expecting row count to be 1');
+        $this->assertEquals(2, count($result[0]),
+            'Expecting column count to be 2');
+        $this->assertEquals(1, $result[0]['product_id'],
+            'Expecting to get product_id 1');
+
+        // Check that extra field ZEND_DB_ROWNUM isn't present
+        // (particulary with Db2 & Oracle)
+        $this->assertArrayNotHasKey('zend_db_rownum', $result[0]);
+        $this->assertArrayNotHasKey('ZEND_DB_ROWNUM', $result[0]);
+
+        $profiler = $this->_db->getProfiler();
+        $this->assertInstanceOf('Zend_Db_Profiler',
+                                $profiler);
+
+        $profile = $profiler->getLastQueryProfile();
+        $this->assertInstanceOf('Zend_Db_Profiler_Query',
+                                $profile);
+
+        $this->assertEquals(Zend_Db_Profiler::SELECT,
+                            $profile->getQueryType());
+
+        $qry = "SELECT z2.*
+            FROM (
+                SELECT ROW_NUMBER() OVER(ORDER BY $product_id DESC) AS \"ZEND_DB_ROWNUM\", z1.*
+                FROM (
+                    SELECT * FROM (SELECT * FROM $products) zfp
+                ) z1
+            ) z2
+            WHERE z2.zend_db_rownum BETWEEN 3 AND 3";
+
+        $this->assertEquals($qry, $profile->getQuery(),
+'ZF-4790: Zend_Db_Adapter_Db2::limit() needs to specify proper OVER()');
+
+    }
+
+    /**
+     * @expectedException Zend_Db_Statement_Db2_Exception
+     */
+    public function testLimitOffsetWithSubselectOrderby()
+    {
+        $profiler = new Zend_Db_Profiler();
+        $profiler->setEnabled(true);
+        $this->_db->setProfiler($profiler);
+
+        $products = $this->_db->quoteIdentifier('zfproducts');
+        $product_id = $this->_db->quoteIdentifier('product_id');
+
+        $sql = $this->_db->limit("SELECT * FROM (SELECT * FROM $products ORDER BY $product_id)", 1, 2);
+
+        try {
+            $stmt = $this->_db->query($sql);
+            $result = $stmt->fetchAll();
+        } catch (Zend_Db_Statement_Db2_Exception $e) {
+            $this->assertContains('Token ) was not valid.', $e->getMessage());
+            throw $e;
+        }
+    }
+
+    /**
+     * @expectedException Zend_Db_Statement_Db2_Exception
+     */
+    public function testLimitOffsetWithSubselectWithTwoOrderby()
+    {
+        $profiler = new Zend_Db_Profiler();
+        $profiler->setEnabled(true);
+        $this->_db->setProfiler($profiler);
+
+        $products = $this->_db->quoteIdentifier('zfproducts');
+        $product_id = $this->_db->quoteIdentifier('product_id');
+
+        $sql = $this->_db->limit("SELECT * FROM (SELECT * FROM $products ORDER BY $product_id) ORDER BY $product_id", 1, 2);
+
+        try {
+            $stmt = $this->_db->query($sql);
+            $result = $stmt->fetchAll();
+        } catch (Zend_Db_Statement_Db2_Exception $e) {
+            $this->assertContains('Keyword BY not expected.', $e->getMessage());
+            throw $e;
+        }
+    }
+
+
     public function getDriver()
     {
         return 'Db2';

I like this proposal.

But after two years is it really necessary to deal with it?

Gérard

Gérard, you are no longer interested in it?

Of course it does, but I wonder if there is a necessity, because in two years, two people are interested in the problem.

Yeah, clearly there are very few people using DB2. And of those people, very few using PHP. And of those, very few using Zend Framework. And of those, very few using Zend_Db::limit(). Zend has an offering for the IBM AS/400, so the importance of this functionality may increase.

http://zend.com/en/products/…

Aaron's patch worked for me. I am one of those people using DB2, Zend Framework and Zend_Db::limit() on IBM i (formerly AS/400).

The limit() problem causes Zend_Paginator_DbSelect not to work properly. The paginator's results for pages 2 and up are returned in random order, ignoring any ORDER BY specified in the select.

Thanks to Aaron's patch, my application's pagination is now working properly. I'd say this is an important bug fix as more IBM i developers begin to write pagination code with ZF.