[
https://issues.apache.org/jira/browse/CALCITE-259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14495561#comment-14495561
]
Julian Hyde edited comment on CALCITE-259 at 4/15/15 2:30 AM:
--------------------------------------------------------------
Which databases don't give an error if the sub-query returns more than one row?
MySQL gives an error:
{code}mysql> select * from department;
+---------------+---------------------------+
| department_id | department_description |
+---------------+---------------------------+
| 1 | HQ General Management |
| 2 | HQ Information Systems |
| 3 | HQ Marketing |
| 4 | HQ Human Resources |
| 5 | HQ Finance and Accounting |
| 11 | Store Management |
| 14 | Store Information Systems |
| 15 | Store Permanent Checkers |
| 16 | Store Temporary Checkers |
| 17 | Store Permanent Stockers |
| 18 | Store Temporary Stockers |
| 19 | Store Permanent Butchers |
+---------------+---------------------------+
12 rows in set (0.00 sec)
mysql> select * from employee where department_id = (select department_id from
department where department_id > 17);
ERROR 1242 (21000): Subquery returns more than 1 row{code}
and so does Oracle. And so should Calcite, regardless of which adapter it is
running on.
In MySQL, for {{SINGLE_VALUE(x)}} you can generate
{code}CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN x ELSE (SELECT NULL UNION ALL
SELECT NULL) END{code}
Maybe you can do something similar in hsqldb, Oracle etc.
was (Author: julianhyde):
Which databases don't give an error if the sub-query returns more than one row?
MySQL gives an error:
{code}mysql> select * from department;
+---------------+---------------------------+
| department_id | department_description |
+---------------+---------------------------+
| 1 | HQ General Management |
| 2 | HQ Information Systems |
| 3 | HQ Marketing |
| 4 | HQ Human Resources |
| 5 | HQ Finance and Accounting |
| 11 | Store Management |
| 14 | Store Information Systems |
| 15 | Store Permanent Checkers |
| 16 | Store Temporary Checkers |
| 17 | Store Permanent Stockers |
| 18 | Store Temporary Stockers |
| 19 | Store Permanent Butchers |
+---------------+---------------------------+
12 rows in set (0.00 sec)
mysql> select * from employee where department_id = (select department_id from
department where department_id > 17);
ERROR 1242 (21000): Subquery returns more than 1 row{code}
and so does Oracle. And so should Calcite, regardless of which adapter it is
running on.
In MySQL, for SINGLE_VALUE(x) you can generate
{code}CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN x ELSE (SELECT NULL UNION ALL
SELECT NULL) END{code}
Maybe you can do something similar in hsqldb, Oracle etc.
> 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
>
>
> 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)
