[
https://issues.apache.org/jira/browse/CALCITE-259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14516553#comment-14516553
]
Yuri Au Yong commented on CALCITE-259:
--------------------------------------
Hi [~julianhyde],
Initial Statement: SELECT * FROM DEPT WHERE DEPTNO=(SELECT DEPTNO FROM EMP
WHERE ENAME='ALLEN')
- Generated SQL by Calcite with single_value func:
SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC
FROM (SELECT SINGLE_VALUE(DEPTNO) as $f0
FROM EMP) as t1
INNER JOIN DEPT ON t1.$f0 = DEPT.DEPTNO;
In MySQL "SELECT NULL UNION ALL SELECT NULL" can be applied as the "CASE"
expression's "ELSE" clause for return type TINYINT(DEPTNO) and VARCHAR(ENAME).
In Hsqldb, SELECT statements require FROM clause and using COUNT requires GROUP
BY clause, e.g.:
- Generated SQL by Calcite after replacing single_value func(this works in
mysql too):
SELECT "DEPT"."DEPTNO", "DEPT"."DNAME", "DEPT"."LOC"
FROM (SELECT CASE "t"."$f0"
WHEN 1 THEN "t"."DEPTNO"
WHEN 0 THEN NULL
ELSE (SELECT 1 FROM "EMP" UNION ALL SELECT 1 FROM "EMP") END
AS "$f0"
FROM (SELECT "DEPTNO", COUNT("DEPTNO") AS "$f0"
FROM "EMP" WHERE "ENAME"='ALLEN' GROUP BY "DEPTNO") AS "t") as "t1"
INNER JOIN "DEPT" ON "t1"."$f0" = "DEPT"."DEPTNO";
However, in Hsqldb, the "CASE" expression's "ELSE" clause is required to match
all the "THEN" clause data type, e.g.:
- for TINYINT(DEPTNO) return type: "SELECT 1 FROM EMP UNION ALL SELECT 1 FROM
EMP"
- for VARCHAR(ENAME) return type: "SELECT NULL FROM EMP UNION ALL SELECT NULL
FROM EMP"
It would be too tedious to try to cater for all different data types scenarios
with CASE expression.
Thus, it would be better to embed a scalar subquery statement as replacement
for the SINGLE_VALUE function instead (as per the latest attached patch fix) as
it covers the above mentioned scenarios.
> 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)
