Affects Version/s: 1.2.2
Fix Version/s: None
It's not possible to use a column aggregate function and a limit subquery together with SQL Server; the logic that constructs the limit subquery is flawed, not recognising the presence of a function and assuming it to be a column. The resulting error occurs:
SQLSTATE[42S22]: Column not found: 207 [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name ...
This can be observed when running the random sorting example from the manual against SQL Server through ODBC:
In addition to the syntax error, it seems SQL Server requires that all aggregate functions that appear in the order by section of the query are declared (i.e. aliased) in the select section which is not currently happening.
I also noticed what I think is a bug in the ordering of results within the sub-select on line 214 of Doctrine_Connection_Mssql:
$query .= (stripos($sorts[$i], 'asc') !== false) ? 'DESC' : 'ASC';
which appears to be sorting in the opposite order as to what was intended.
I've a fix and a test case which attempts to address these issues. I noticed two other open issues surrounding this section of code (#586 and #744) - if this patch is accepted I think these issues will also be resolved.