Activity

1: SQLServerPlatform.php modifies the query to prepend 'SELECT ROW_NUMBER() OVER ($over)', which is inserted before the DISTINCT keyword.

2: The order needs to be placed inside the OVER($over) block. At this point, the regex is using the exact column name rather than the alias, so the outer query cannot ORDER.

3: The DISTINCT queries select only the ID columns - as OVER() required the sort column to be available in the outer query, IDs alone will not work.

4: SQL Server cannot DISTINCT on TEXT columns. 2005,2008 and 2012 recommend using VARCHAR(MAX) instead, which does support it. That doesn't help us with 2003. We work around that with a custom TEXT type that casts as varchar.

Craig Mason
added a comment - 18/Oct/12 8:44 AM - edited There are four major issues with this:
1: SQLServerPlatform.php modifies the query to prepend 'SELECT ROW_NUMBER() OVER ($over)', which is inserted before the DISTINCT keyword.
2: The order needs to be placed inside the OVER($over) block. At this point, the regex is using the exact column name rather than the alias, so the outer query cannot ORDER.
3: The DISTINCT queries select only the ID columns - as OVER() required the sort column to be available in the outer query, IDs alone will not work.
4: SQL Server cannot DISTINCT on TEXT columns. 2005,2008 and 2012 recommend using VARCHAR(MAX) instead, which does support it. That doesn't help us with 2003. We work around that with a custom TEXT type that casts as varchar.
Incidentally, 2012 supports LIMIT, which gets rid of this issue altogether.
Edit: Added #3

I have a (very hacky) implementation working that uses regexes to correct the query so that it will execute. This also required modification in the ORM paginator, to select all columns instead of just IDs.

One interesting point... I had to wrap the whole query in a second SELECT *, as the WHERE IN confusingly returns non-distinct rows when part of the first inner query. No idea why this happens, but moving it out one layer makes it operate correctly.

Craig Mason
added a comment - 18/Oct/12 1:31 PM I have a (very hacky) implementation working that uses regexes to correct the query so that it will execute. This also required modification in the ORM paginator, to select all columns instead of just IDs.
https://github.com/CraigMason/dbal/commit/4ecd018c73e387904f78d81f1d327e34e905c5f1
https://github.com/CraigMason/doctrine2/commit/b416d3b2a38495e4435bde872b19fec371fe5657
This is certainly not a patch - more guidance.
One interesting point... I had to wrap the whole query in a second SELECT *, as the WHERE IN confusingly returns non-distinct rows when part of the first inner query. No idea why this happens, but moving it out one layer makes it operate correctly.

This got waaaay too messy with regex alone due to the complicated nesting. As such, I have written the basis of a new SqlWalker class which can be used to create DISTINCT queries based on the root identifiers. It's not proper DISTINCT support, but it's a step forward.

The current SqlWalker always sticks the ORDER BY on the end of the query, which just doesn't work properly with SqlServer. Is a vendor-specific walker breaking the DQL abstraction? Should this type of code be on the Platform object in the DBAL?

Anyway, this repo fixes our immediate problem, and it would be good to revisit this in a wider context. Hopefully we can get some good SQL server support - there are plenty of other issues to deal with (UTF-8/UCS2, nvarchar etc)

Craig Mason
added a comment - 29/Oct/12 8:13 PM This got waaaay too messy with regex alone due to the complicated nesting. As such, I have written the basis of a new SqlWalker class which can be used to create DISTINCT queries based on the root identifiers. It's not proper DISTINCT support, but it's a step forward.
https://github.com/CraigMason/DoctrineSqlServerExtensions
I've also added a Paginator (which was the original issue I had!)
The current SqlWalker always sticks the ORDER BY on the end of the query, which just doesn't work properly with SqlServer. Is a vendor-specific walker breaking the DQL abstraction? Should this type of code be on the Platform object in the DBAL?
Anyway, this repo fixes our immediate problem, and it would be good to revisit this in a wider context. Hopefully we can get some good SQL server support - there are plenty of other issues to deal with (UTF-8/UCS2, nvarchar etc)