Hi Julian Hyde,
For the case with correlated scalar sub-query,
1) Are we going to re-enable the SINGLE_VALUE in JdbcAggregate.canImplement to handle it? Probably this is not the case because once re-enabled the SINGLE_VALUE would appear for most scalar sub-queries?
2) Do we explicitly check if a query contains correlated sub-queries then attempt to translate SINGLE_VALUE into something equivalent then push it down into datasource?
On translating SINGLE_VALUE into something equivalent,
Few points gathered from this thread,
a) CASE Expression that has selection of multiple NULL rows to emulate the "More than 1 row scalar sub-query" error
b) Different databases need different SQL syntax to construct the CASE Expression mentioned in above (#a).
(E.g. MySQL does not support the syntax "values(1, 2 ..); HSQLDB needs the GroupBy clause when Aggregator is used along with CASE Expressions)
How about putting concrete queries into the CASE Expression. Please refer below,
select SINGLE_VALUE(col1) from table1 where col2 = something
CASE (SELECT COUNT(col1) FROM table1 WHERE col2 = something)
WHEN 1 THEN (SELECT col1 FROM table1 WHERE col2 = something)
ELSE (SELECT col1 FROM table1 WHERE col2 = something)
col2 = something
But if the original query has multiple scalar sub-queries, this would make the translated query very bloated. However, the Select query syntax would work for most databases.
On the other hand, the CASE Expression may be replaced by the by the Select statement excluding the COUNT aggregator
Let me know your comments.