[
https://issues.apache.org/jira/browse/CALCITE-259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14530161#comment-14530161
]
YeongWei commented on CALCITE-259:
----------------------------------
Hi [~julianhyde],
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,
Say,
select SINGLE_VALUE(col1) from table1 where col2 = something
Translated into,
SELECT
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)
END
FROM
table1
WHERE
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.
Thanks!
> Using sub-queries in CASE statement against JDBC tables generates invalid
> Oracle SQL
> ------------------------------------------------------------------------------------
>
> Key: CALCITE-259
> URL: https://issues.apache.org/jira/browse/CALCITE-259
> Project: Calcite
> Issue Type: Bug
> Reporter: GitHub Import
> Labels: github-import
> Attachments: [CALCITE-259]-Description.patch,
> [CALCITE-259]-Description.patch
>
>
> 1.
> select e.NAME,
> (CASE e.dept_ID WHEN (Select d.id from PV_ADMIN.dept d where d.id =
> e.dept_id)
> THEN (Select d.name from PV_ADMIN.dept d where d.id = e.dept_id)
> ELSE 'DepartmentNotFound' END ) AS DEPTNAME
> from PV_ADMIN.EMP e;
>
> 2.
> select e.NAME,
> CASE WHEN e.dept_ID = (select d.ID from PV_ADMIN.dept d where d.NAME =
> 'SALES') then 'SALES'
> ELSE 'Not Matched.'
> END as department
> from PV_ADMIN.EMP e ;
> ---------------- Imported from GitHub ----------------
> Url: https://github.com/julianhyde/optiq/issues/259
> Created by: [kunal-mahale|https://github.com/kunal-mahale]
> Labels: duplicate,
> Created at: Fri Apr 25 06:41:04 CEST 2014
> State: open
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
