[DBAL-82] orderBy(), setFirstResult() bug with MSSQL Server Created: 16/Jan/11  Updated: 31/Jul/11  Resolved: 31/Jul/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0
Fix Version/s: 2.0.7, 2.1.1

Type: Bug Priority: Major
Reporter: Aaron DM Assignee: Juozas Kaziukenas
Resolution: Fixed Votes: 1
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.



 Comments   
Comment by Aaron DM [ 16/Jan/11 ]

Added the ugly hack fix.

Comment by Benjamin Eberlei [ 23/Jan/11 ]

Assigned to Juozas.

Comment by Minxuan GUO [ 05/May/11 ]

Thanks Aaron DM

Your code works perfectly

Comment by Jean-Marc Fontaine [ 20/Jun/11 ]

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

Comment by Aaron DM [ 24/Jun/11 ]

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

Comment by Aaron DM [ 24/Jun/11 ]

Pull request: https://github.com/doctrine/dbal/pull/37

Comment by Benjamin Eberlei [ 31/Jul/11 ]

Fixed, merged pull request

Generated at Sat Nov 29 07:40:06 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.