[DC-586] Doctrine outputs invalid SQL when using Limit and Order By conditions in MSSQL Created: 18/Mar/10  Updated: 18/Mar/10

Status: Open
Project: Doctrine 1
Component/s: Connection
Affects Version/s: 1.2.1
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Jose Prado Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
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?


Generated at Mon Sep 01 14:01:02 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.