Problem in MSSQL when DISTINCT, OFFSET AND LIMIT used together

[GeeH]

This issue has been moved from the zendframework repository as part of the bug migration program as outlined here - http://framework.zend.com/blog/2016-04-11-issue-closures.html

---

Original Issue: https://api.github.com/repos/zendframework/zendframework/issues/7603
User: @canyildiz
Created On: 2015-07-03T08:23:32Z
Updated At: 2015-11-06T22:06:55Z
Body
When trying to write a query using mssql driver which uses both DISTINCT quantifer and OFFSET+LIMIT together, it failed. When I investigated the issue saw that, processLimitOffset method in Db/Sql/Platform/SqlServer/SelectDecorator.php always uses 0th element of $selectParameters, but when ->quantifier('DISTINCT') is used in select element, 0th element is only the word 'DISTINCT' and 1st element is the select query desired.

I am not sure if this is the perfect solution while I am not aware of full project, but made following simple modification to solve this:

--- a/Db/Sql/Platform/SqlServer/SelectDecorator.php
+++ b/Db/Sql/Platform/SqlServer/SelectDecorator.php
@@ -54,9 +54,18 @@
             return;
         }

         $selectParameters = $parameters[self::SELECT];
+                /** if this is a DISTINCT query then real SELECT part goes to second element in array **/
+                $parameterIndex = 0;
+                if ($selectParameters[0] === 'DISTINCT')
+                {
+                        unset($selectParameters[0]);
+                        $selectParameters =     array_values($selectParameters);
+                        $parameterIndex = 1;
+                }
+                
         $starSuffix = $platform->getIdentifierSeparator() . self::SQL_STAR;
         foreach ($selectParameters[0] as $i => $columnParameters) {
             if ($columnParameters[0] == self::SQL_STAR || (isset($columnParameters[1]) && $columnParameters[1] == self::SQL_STAR) || strpos($columnParameters[0], $starSuffix)) {
                 $selectParameters[0] = array(array(self::SQL_STAR));
@@ -98,9 +107,9 @@
             $orderBy = 'ORDER BY (SELECT 1)';
         }

         // add a column for row_number() using the order specification
-        $parameters[self::SELECT][0][] = array('ROW_NUMBER() OVER (' . $orderBy . ')', '[__ZEND_ROW_NUMBER]');
+        $parameters[self::SELECT][$parameterIndex][] = array('ROW_NUMBER() OVER (' . $orderBy . ')', '[__ZEND_ROW_NUMBER]');

         $sqls[self::SELECT] = $this->createSqlFromSpecificationAndParameters(
             $this->specifications[self::SELECT],
             $parameters[self::SELECT]

---

[samsonasik]

@canyildiz I pull your suggestion and create PR for it at #145

[canyildiz]

@samsonasik I thought Zend team is working on issues for ZF2 on their own now. So I didn't create a PR for this. Anyway, thanks for your interest