Doctrine 1
  1. Doctrine 1
  2. DC-586

Doctrine outputs invalid SQL when using Limit and Order By conditions in MSSQL

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Critical Critical
    • Resolution: Unresolved
    • Affects Version/s: 1.2.1
    • Fix Version/s: None
    • Component/s: Connection
    • Labels:
      None
    • Environment:
      Windows XP
      Apache 2.2
      PHP 5.3
      Doctrine 1.2.1
      Symfony 1.4

      Description

      I have a Doctrine model which connects to a MSSQL database. I was trying to run the following query:

      $q = Doctrine_Query::create()
          ->select('*')
          ->from('Comment c')
          ->innerJoin('c.RecordType')
          ->innerJoin('c.Department')
          ->limit(10)
          ->orderBy('c.Counter');
      

      The code failed with a SQL Syntax exception so I took a look at the generated query and found the following (SELECT fields shortened for readabilty):

      SELECT * FROM (
      	SELECT TOP 10 * FROM (
      		SELECT TOP 10 [c].[counter] AS [c__counter], [c].[loanid] AS [c__loanid]... ... ...
      		FROM comments c
      			INNER JOIN [SystemTypes] [s] ON [c].[recordtype] = [s].[code] AND [s].[fieldname] = 'RecordType'
      			INNER JOIN [SystemTypes] [s2] ON [c].[department] = [s2].[code] AND [s2].[fieldname] = 'Department'
      		ORDER BY [c].[counter]
      	) AS [inner_tbl]
      	ORDER BY [inner_tbl].[counter] AS [c__counter] DESC
      ) AS [outer_tbl]
      ORDER BY [outer_tbl].[counter] AS [c__counter] ASC
      

      As you can see, the ORDER BY clauses on the inner_tbl and outer_tbl segments have AS clauses which do not belong there. If you fix the ORDER BY statements the query runs just fine.

      So I decided to prod around the Mssql.php connection class and found the following:

      140 public function modifyLimitQuery($query, $limit = false, $offset = false, $isManip = false, $isSubQuery = false)
      141 {
      ...
      169                    $field_array = explode(',', $fields_string);
      170                    $field_array = array_shift($field_array);
      171                    $aux2 = preg_split('/ as /', $field_array);
      172                    $aux2 = explode('.', end($aux2));
      173
      174                    $aliases[$i] = trim(end($aux2));
      ...
      232 }
      

      Line 171 seems to be in charge of setting up the orderBy aliases but it is looking for a lower case ' as ' string which doesn't exist in this SQL expression. Changing that to a case insensitive regular expression search seems to fix the problem:

      171                    $aux2 = preg_split('/ as /i', $field_array);
      

      Here is the resulting SQL with the change:

      SELECT * FROM (
      	SELECT TOP 10 * FROM (
      		SELECT TOP 10 [c].[counter] AS [c__counter], [c].[loanid] AS [c__loanid]... ... ...
      		FROM comments c
      			INNER JOIN [SystemTypes] [s] ON [c].[recordtype] = [s].[code] AND [s].[fieldname] = 'RecordType'
      			INNER JOIN [SystemTypes] [s2] ON [c].[department] = [s2].[code] AND [s2].[fieldname] = 'Department'
      		ORDER BY [c].[counter]
      	) AS [inner_tbl]
      	ORDER BY [inner_tbl].[c__counter] DESC
      ) AS [outer_tbl]
      ORDER BY [outer_tbl].[c__counter] ASC]
      

      This seems to fix the problem but I don't know if it'll create a regression. It's a start though. Anyone have any thoughts on this?

        Activity

        Jose Prado created issue -

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DC-586, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Jose Prado
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: