[
https://issues.apache.org/jira/browse/CALCITE-259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14533392#comment-14533392
]
Julian Hyde commented on CALCITE-259:
-------------------------------------
Here are the facts as I see them (or rather, design constraints):
* Relational algebra cannot represent scalar sub-queries. We have to convert
them to joins.
* If Calcite identifies that the most efficient way to execute the query is to
push it down to a JDBC data source, it is not practical to convert these joins
back to scalar sub-queries. So, the SQL we generate to send to the JDBC data
source will not contain scalar sub-queries (except "little" scalar sub-queries
that are purposely introduced just to throw an error).
* The difference between a join and a scalar-sub-query is that the scalar
sub-query must FAIL if the query returns more than one 1 row. (If it returns 0
rows, it will generate null, which is kind of similar to left-join.)
So, we must generate SQL to send to the JDBC data source that will generate an
error if the query returns more than one row. *That SQL does not need to be
same for each database*. But the most obvious way of writing that SQL involves
CASE and COUNT( * ).
I think I've solved it for hsqldb (see above). I'll let others solve it for
MySQL, Oracle, ... any other database you care about.
> 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)
