I was testing
MDL-12893 under 1.8 when I discovered a bunch of
SELECT DISTINCT ... clauses breaking both MSSQL and Oracle when sorting was specified.
Both MSSQL and Oracle doesn't allow to specify such DISTINCT clause when the list of fields contain one TEXT (CLOB) field.
Looking at code I've found:
- 3 occurrences in 18_STABLE view.php
- 2 occurrences in 19_STABLE view.php
- 2 occurrences in HEAD view.php
IMO, there are 3 ways to fix this:
1) Analyse if we really need that DISTINCT clause in those queries. Take it out if not needed.
2) Analyse if we need to fetch the TEXT (CLOB) field really from DD. Take it out if not needed.
3) Build one subselect getting the TEXT field in the outer query, once everything has been executed in the inner one.
I've tried directly the 1st alternative (dropping the DISTINCT clause) and seems to work here in my basic tests under MSSQL.
I guess we could implement the 2nd alternative too (saving some TEXT traffic between DB and PHP...
And finally, the 3rd alternative should be only used if both the 2 above aren't ok.
For your consideration... my +1 to implement 1) and 2) if possible. From 18_STABLE to HEAD.