[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

Windows 7, Apache2, PHP 5.3.5, Microsoft SQL Server 2008

		$query = $this->createQueryBuilder('account')
					  ->orderBy('account.id', 'DESC')

                $result = $query->setMaxResults($this->resultsPerPage+1)

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:


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

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:

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 Tue Sep 01 02:10:06 EDT 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.