Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-82

orderBy(), setFirstResult() bug with MSSQL Server

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0.7, 2.1.1
    • Component/s: Platforms
    • Labels:
      None
    • Environment:
      Windows 7, Apache2, PHP 5.3.5, Microsoft SQL Server 2008

      Description

      QueryBuilder
      		$query = $this->createQueryBuilder('account')
      					  ->select('account')
      					  ->orderBy('account.id', 'DESC')
      					  ->getQuery();
      
                      $result = $query->setMaxResults($this->resultsPerPage+1)
              							->setFirstResult($this->offset)
              							->getResult();
      

      The above, when using "setFirstResult" and "orderBy" (like that), results in an error like so:

      SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]The multi-part identifier "t0_.id" could not be bound.

      With a statement that looks something like so:

      WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY t0_.id DESC) AS "doctrine_rownum",   ...

      The reason the error is occurring is because you apparently need to use the "alias" (e.g, SELECT t0_.id AS id0) "id0" in the "ORDER BY" clause.

      So query will run with no problems if t0_.id as id0

      WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY id0 DESC) AS "doctrine_rownum",   ...

      It looks like this behavior might only occur with Microsoft SQL Server, but it is a bug.

      ---------------

      If you go into:
      Doctrine\DBAL\Platforms\MsSqlPlatform

      Find:

      $over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby);

      Add before:

                          # Get Columns
                          $columns = array();
                          if(preg_match_all('/([a-zA-Z][0-9]+_\.[a-zA-Z0-9\-_]+)\sAS\s([a-zA-Z0-9\-\_]+)/', $query, $matched)) {
                              for($i=0; $i<count($matched[1]); ++$i)
                              {
                                  $columns[$matched[1][$i]] = $matched[2][$i];
                              }
                          }
                          
                          # Replace columns with their alias in the "orderby" statement
                          if(preg_match_all('/([a-zA-Z][0-9]+_\.[a-zA-Z0-9\-_]+)\s/i', $orderby, $matches)) {
                              foreach($matches[1] as $column) 
                              {
                                  $orderby = preg_replace('/'.$column.'/', $columns[$column], $orderby);
                              }
                          }
      

      Obviously this is a really ugly hack, but this resolves it.

        Activity

        Aaron DM created issue -
        Hide
        Aaron DM added a comment -

        Added the ugly hack fix.

        Show
        Aaron DM added a comment - Added the ugly hack fix.
        Aaron DM made changes -
        Field Original Value New Value
        Description {code:title=QueryBuilder}
        $query = $this->createQueryBuilder('account')
        ->select('account')
        ->orderBy('account.id', 'DESC')
        ->getQuery();

                        $result = $query->setMaxResults($this->resultsPerPage+1)
                 ->setFirstResult($this->offset)
                 ->getResult();
        {code}

        The above, when using "setFirstResult" and "orderBy" (like that), results in an error like so:

        {code}SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]The multi-part identifier "t0_.id" could not be bound.{code}

        With a statement that looks something like so:

        {code}WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY t0_.id DESC) AS "doctrine_rownum", ...{code}

        The reason the error is occurring is because you apparently need to use the "alias" (e.g, SELECT t0_.id AS id0) "id0" in the "ORDER BY" clause.

        So query will run with no problems if t0_.id as id0

        {code}WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY id0 DESC) AS "doctrine_rownum", ...{code}

        It looks like this behavior might only occur with Microsoft SQL Server, but it is a bug.


        {code:title=QueryBuilder}
        $query = $this->createQueryBuilder('account')
        ->select('account')
        ->orderBy('account.id', 'DESC')
        ->getQuery();

                        $result = $query->setMaxResults($this->resultsPerPage+1)
                 ->setFirstResult($this->offset)
                 ->getResult();
        {code}

        The above, when using "setFirstResult" and "orderBy" (like that), results in an error like so:

        {code}SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]The multi-part identifier "t0_.id" could not be bound.{code}

        With a statement that looks something like so:

        {code}WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY t0_.id DESC) AS "doctrine_rownum", ...{code}

        The reason the error is occurring is because you apparently need to use the "alias" (e.g, SELECT t0_.id AS id0) "id0" in the "ORDER BY" clause.

        So query will run with no problems if t0_.id as id0

        {code}WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY id0 DESC) AS "doctrine_rownum", ...{code}

        It looks like this behavior might only occur with Microsoft SQL Server, but it is a bug.

        ---------------

        If you go into:
        Doctrine\DBAL\Platforms\MsSqlPlatform

        Find:
         {code}$over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby);{code}

        Add before:
        {code}
                            # Get Columns
                            $columns = array();
                            if(preg_match_all('/([a-zA-Z][0-9]+_\.[a-zA-Z0-9\-_]+)\sAS\s([a-zA-Z0-9\-\_]+)/', $query, $matched)) {
                                for($i=0; $i<count($matched[1]); ++$i)
                                {
                                    $columns[$matched[1][$i]] = $matched[2][$i];
                                }
                            }
                            
                            # Replace columns with their alias in the "orderby" statement
                            if(preg_match_all('/([a-zA-Z][0-9]+_\.[a-zA-Z0-9\-_]+)\s/i', $orderby, $matches)) {
                                foreach($matches[1] as $column)
                                {
                                    $orderby = preg_replace('/'.$column.'/', $columns[$column], $orderby);
                                }
                            }
        {code}

        Obviously this is a really ugly hack, but this resolves it.
        Hide
        Benjamin Eberlei added a comment -

        Assigned to Juozas.

        Show
        Benjamin Eberlei added a comment - Assigned to Juozas.
        Benjamin Eberlei made changes -
        Assignee Benjamin Eberlei [ beberlei ] Juozas Kaziukenas [ juokaz ]
        Benjamin Eberlei made changes -
        Project Doctrine 2 - ORM [ 10032 ] Doctrine DBAL [ 10040 ]
        Key DDC-988 DBAL-82
        Affects Version/s 2.0 [ 10067 ]
        Affects Version/s Git Master [ 10100 ]
        Component/s Platforms [ 10056 ]
        Component/s DQL [ 10014 ]
        Hide
        Minxuan GUO added a comment -

        Thanks Aaron DM

        Your code works perfectly

        Show
        Minxuan GUO added a comment - Thanks Aaron DM Your code works perfectly
        Hide
        Jean-Marc Fontaine added a comment -

        I am experiencing this bug too. Is there a way to get it fixed anytime soon? Maybe in the 2.1 release.

        Show
        Jean-Marc Fontaine added a comment - I am experiencing this bug too. Is there a way to get it fixed anytime soon? Maybe in the 2.1 release.
        Hide
        Aaron DM added a comment -

        Here is what I think, a proper fix for this.

        I re-wrote the query however it works the same and is pretty much the exact same in performance (from couple of tests I've done looking at profiler) + it fixes the issues regarding this ticket.

            /**
             * Adds an adapter-specific LIMIT clause to the SELECT statement.
             *
             * @param string $query
             * @param mixed $limit
             * @param mixed $offset
             * @link http://lists.bestpractical.com/pipermail/rt-devel/2005-June/007339.html
             * @return string
             */
            protected function doModifyLimitQuery($query, $limit, $offset = null)
            {
                if ($limit > 0) {
                    $count = intval($limit);
                    $offset = intval($offset);
        
                    if ($offset < 0) {
                        throw new DBALException("LIMIT argument offset=$offset is not valid");
                    }
        
                    if ($offset == 0) {
                        $query = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . $count . ' ', $query);
                    } else {
                        $orderby = stristr($query, 'ORDER BY');
        
                        if (!$orderby) {
                            $over = 'ORDER BY (SELECT 0)';
                        } else {
                            $over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby);
                        }
        
                        // Remove ORDER BY clause from $query
                        $query = preg_replace('/\s+ORDER BY(.*)/', '', $query);
                        $query = preg_replace('/SELECT\s/', '', $query);
        
                        $start = $offset + 1;
                        $end = $offset + $count;
        
                        // Limit query
                        $query = "SELECT * FROM (SELECT ROW_NUMBER() OVER ($over) AS \"doctrine_rownum\", $query) AS doctrine_tbl WHERE \"doctrine_rownum\" BETWEEN $start AND $end";
                    }
                }
        
                return $query;
            }
        

        I had found this query a long time ago and found it to be fairly fast, however I'm not sure where exactly I found it.
        I'm going to take a guess and assume it might be this comment:
        http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/comment-page-1/#comment-28594

        Show
        Aaron DM added a comment - Here is what I think, a proper fix for this. I re-wrote the query however it works the same and is pretty much the exact same in performance (from couple of tests I've done looking at profiler) + it fixes the issues regarding this ticket. /** * Adds an adapter-specific LIMIT clause to the SELECT statement. * * @param string $query * @param mixed $limit * @param mixed $offset * @link http: //lists.bestpractical.com/pipermail/rt-devel/2005-June/007339.html * @ return string */ protected function doModifyLimitQuery($query, $limit, $offset = null ) { if ($limit > 0) { $count = intval($limit); $offset = intval($offset); if ($offset < 0) { throw new DBALException( "LIMIT argument offset=$offset is not valid" ); } if ($offset == 0) { $query = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . $count . ' ', $query); } else { $orderby = stristr($query, 'ORDER BY'); if (!$orderby) { $over = 'ORDER BY (SELECT 0)'; } else { $over = preg_replace('/\ "[^,]*\" .\ "([^,]*)\" /i', ' "inner_tbl" . "$1" ', $orderby); } // Remove ORDER BY clause from $query $query = preg_replace('/\s+ORDER BY(.*)/', '', $query); $query = preg_replace('/SELECT\s/', '', $query); $start = $offset + 1; $end = $offset + $count; // Limit query $query = "SELECT * FROM (SELECT ROW_NUMBER() OVER ($over) AS \" doctrine_rownum\ ", $query) AS doctrine_tbl WHERE \" doctrine_rownum\ " BETWEEN $start AND $end" ; } } return $query; } I had found this query a long time ago and found it to be fairly fast, however I'm not sure where exactly I found it. I'm going to take a guess and assume it might be this comment: http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/comment-page-1/#comment-28594
        Hide
        Aaron DM added a comment -
        Show
        Aaron DM added a comment - Pull request: https://github.com/doctrine/dbal/pull/37
        Hide
        Benjamin Eberlei added a comment -

        Fixed, merged pull request

        Show
        Benjamin Eberlei added a comment - Fixed, merged pull request
        Benjamin Eberlei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.0.7 [ 10151 ]
        Fix Version/s 2.1.1 [ 10156 ]
        Resolution Fixed [ 1 ]
        Benjamin Eberlei made changes -
        Workflow jira [ 12306 ] jira-feedback2 [ 17689 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 17689 ] jira-feedback3 [ 20044 ]

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DBAL-82, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Juozas Kaziukenas
            Reporter:
            Aaron DM
          • Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: