Doctrine 1
  1. Doctrine 1
  2. DC-759

MSSQL Server / ODBC: Cannot order by a column aggregate when a limit subquery is required.

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.2
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None
    • Environment:
      Windows XP
      Apache 2.2
      PHP 5.3.2

      Description

      It's not possible to use a column aggregate function and a limit subquery together with SQL Server; the logic that constructs the limit subquery is flawed, not recognising the presence of a function and assuming it to be a column. The resulting error occurs:

      SQLSTATE[42S22]: Column not found: 207 [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name ...

      This can be observed when running the random sorting example from the manual against SQL Server through ODBC:
      http://www.doctrine-project.org/documentation/manual/1_2/hu/dql-doctrine-query-language:order-by-clause:using-random-order

      In addition to the syntax error, it seems SQL Server requires that all aggregate functions that appear in the order by section of the query are declared (i.e. aliased) in the select section which is not currently happening.

      I also noticed what I think is a bug in the ordering of results within the sub-select on line 214 of Doctrine_Connection_Mssql:
      $query .= (stripos($sorts[$i], 'asc') !== false) ? 'DESC' : 'ASC';

      which appears to be sorting in the opposite order as to what was intended.

      I've a fix and a test case which attempts to address these issues. I noticed two other open issues surrounding this section of code (#586 and #744) - if this patch is accepted I think these issues will also be resolved.

      1. DC759TestCase.php
        2 kB
        Craig Marvelley
      2. doctrine_aggregate_function_order_patch.patch
        2 kB
        Craig Marvelley

        Activity

        Hide
        Craig Marvelley added a comment -

        Uploaded patch and test case.

        Show
        Craig Marvelley added a comment - Uploaded patch and test case.

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Craig Marvelley
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: