Skip to content

Loading…

DBAL-82: orderBy(), setFirstResult() bug with MSSQL Server #2053

Closed
doctrinebot opened this Issue · 8 comments

1 participant

@doctrinebot

Jira issue originally created by user aarondm:

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

                $result = $query->setMaxResults($this->resultsPerPage<ins>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('/\"[<sup>,]*\".\"([</sup>,]*)\"/i', '"inner*tbl"."$1"', $orderby);

Add before:

                    # Get Columns
                    $columns = array();
                    if(preg*match_all('/([a-zA-Z][0-9]</ins>_\.[a-zA-Z0-9\-_]<ins>)\sAS\s([a-zA-Z0-9\-\*]</ins>)/', $query, $matched)) {
                        for($i=0; $i<count($matched[1]); <ins></ins>$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]<ins>_\.[a-zA-Z0-9\-*]</ins>)\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.

@doctrinebot

Comment created by aarondm:

Added the ugly hack fix.

@doctrinebot

Comment created by @beberlei:

Assigned to Juozas.

@doctrinebot

Comment created by minxuan.guo:

Thanks Aaron DM

Your code works perfectly

@doctrinebot

Comment created by jmfontaine:

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

@doctrinebot

Comment created by aarondm:

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('/<sup>SELECT\s/i', 'SELECT TOP ' . $count . ' ', $query);
            } else {
                $orderby = stristr($query, 'ORDER BY');

                if (!$orderby) {
                    $over = 'ORDER BY (SELECT 0)';
                } else {
                    $over = preg*replace('/\"[</sup>,]*\".\"([^,]*)\"/i', '"inner*tbl"."$1"', $orderby);
                }

                // Remove ORDER BY clause from $query
                $query = preg_replace('/\s</ins>ORDER BY(.*)/', '', $query);
                $query = preg_replace('/SELECT\s/', '', $query);

                $start = $offset <ins> 1;
                $end = $offset </ins> $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

@doctrinebot

Comment created by aarondm:

Pull request: #37

@doctrinebot

Comment created by @beberlei:

Fixed, merged pull request

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added the Bug label
@doctrinebot doctrinebot added this to the 2.0.7 milestone
@doctrinebot doctrinebot closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.