Details
-
Type:
Bug
-
Status:
Open
-
Priority:
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?