[
https://issues.apache.org/jira/browse/CALCITE-259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14493782#comment-14493782
]
Xavier FH Leong commented on CALCITE-259:
-----------------------------------------
Hi [~julianhyde], I'd discuss over with [~YAY], basically we had run some test
for the SQL to be handle by JDBC adapter so that the query is pass on to the
adaptee to run the SQL, some database like Oracle will fail and expect the
sub-query to be with single value, some other database however will treat it as
IN() statement and works for multiple value return from the sub-query.
Hence, there's no consistency on the sub-query join handling, and will be an
issue if the adaptee able to process the query using the adapter rule but fail
when using Calcite EnumarableJoin rule, which handles SINGLE_VALUE. In this
case, one would see failure on half the case depends which execution plan is
chosen.
Since we lack of idea (and standards) on how to enforce the SINGLE_VALUE check
via SQL on the JDBCAdapter side, what do you think if we handle this case using
Calcite default rule so that the results are consistent, even though the
execution cost is higher compare on JDBCAdapter. And what will be the guiding
design principle of Calcite for handling non-standards behavior, especially for
adapter that takes SQL.
> 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)
