Doctrine 1
  1. Doctrine 1
  2. DC-573

Invalid SQL generated by Doctrine_Query::getLimitSubquery() when ordering by multiple columns

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 1.2.1
    • Fix Version/s: 1.2.2
    • Component/s: Query
    • Labels:
      None
    • Environment:
      PHP 5.3
      Windows XP
      MSSQL Server 2008

      Description

      When attempting to order a query by multiple columns I came across this bug. I've attached a reproduction script which will hopefully illustrate the behaviour, but essentially getLimitSubquery() is parsing the order by column incorrectly, leading to an erroneous comma in the sub-select field select clause, e.g

      SELECT [m].[id] AS [m__id], [m].[name] AS [m__name], [m].[date] AS [m__date], [r].[id] AS [r__id], [r].[model_id] AS [r__model_id], [r].[name] AS [r__name] FROM [model] [m] INNER JOIN [relation] [r] ON [m].[id] = [r].[model_id] WHERE [m].[id] IN (SELECT TOP 1 [inner_tbl].[id] FROM (SELECT DISTINCT TOP 1 [m].[id], [r].[name],, [r].[id] FROM [model] [m] INNER JOIN [relation] [r] ON [m].[id] = [r].[model_id] ORDER BY [r].[name], [r].[id]) AS [inner_tbl] ORDER BY [inner_tbl].[name] DESC, [inner_tbl].[id] DESC) ORDER BY [r].[name], [r].[id]

      (note [r].[name],, [r].[id] - that's the offender).

      I've attached a patch against 1.2 HEAD that strips any commas from the fields before they are added to the select clause; it works for me but I'm not sure of all the use cases across the relevant drivers.

      1. doctrine_query_patch.patch
        0.7 kB
        Craig Marvelley
      2. index.php
        2 kB
        Craig Marvelley
      3. Query.php
        82 kB
        Craig Marvelley

        Activity

        Hide
        Jonathan H. Wage added a comment -

        I don't see any patch attached to the ticket. If you can provide more information and a diff/patch we can consider it for the next release. Thanks, Jon

        Show
        Jonathan H. Wage added a comment - I don't see any patch attached to the ticket. If you can provide more information and a diff/patch we can consider it for the next release. Thanks, Jon
        Hide
        Craig Marvelley added a comment -

        Sigh. Sorry, attached the file instead of the patch.

        Show
        Craig Marvelley added a comment - Sigh. Sorry, attached the file instead of the patch.
        Hide
        Jonathan H. Wage added a comment -

        Thanks for the issue and patch

        Show
        Jonathan H. Wage added a comment - Thanks for the issue and patch

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Craig Marvelley
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: