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 -
        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.
        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 ]
        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 ]

          People

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

            Dates

            • Created:
              Updated:
              Resolved: