[#DC-586] Doctrine outputs invalid SQL when using Limit and Order By conditions in MSSQL

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?

[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

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