[
https://issues.apache.org/jira/browse/CALCITE-6652?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17893958#comment-17893958
]
Julian Hyde commented on CALCITE-6652:
--------------------------------------
Decorrelation of scalar subqueries is complicated if the subquery looks like it
might return more than 1 row. Decorrelating
{code:java}
SELECT deptno,
(SELECT sal FROM emp where dept.deptno = emp.deptno)
FROM dept {code}
to
{code:java}
SELECT dept.deptno, emp.sal
FROM dept, emp
WHERE dept.deptno = emp.deptno{code}
is invalid because if there is more than one employee in any department the
first query will throw and the second query will return multiple rows. (Maybe
it is valid if there are the right primary and foreign key constraints.)
Can you enumerate the cases you know of where LIMIT can safely be removed? I
only know "select x from t order by x limit 1" → "select min(x) from t" and the
similar query with "max".
> RelDecorrelator can't decorrelate query with limit 1
> ----------------------------------------------------
>
> Key: CALCITE-6652
> URL: https://issues.apache.org/jira/browse/CALCITE-6652
> Project: Calcite
> Issue Type: Bug
> Reporter: suibianwanwan
> Assignee: suibianwanwan
> Priority: Major
>
> The following query can't decorrelate by RelDecorrelate:
> {code:java}
> @Test void testDecorrelateFetchOne1() {
> final String query = "SELECT deptno FROM dept WHERE\n" +
> "1000 > (SELECT sal FROM emp WHERE dept.deptno = emp.deptno order by
> emp.sal limit 1)";
> sql(query).withRule(
> CoreRules.FILTER_SUB_QUERY_TO_CORRELATE)
> .withLateDecorrelate(true)
> .check();
> }
> @Test void testDecorrelateFetchOne2() {
> final String query = "SELECT deptno, (SELECT sal FROM emp where dept.deptno
> = emp.deptno order by emp.sal limit 1) FROM dept";
> sql(query).withRule(
> CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE)
> .withLateDecorrelate(true)
> .check();
> }
> @Test void testDecorrelateFetchOne3() {
> final String query = "SELECT deptno, (SELECT sal FROM emp where dept.deptno
> = emp.deptno order by emp.sal desc limit 1) FROM dept";
> sql(query).withRule(
> CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE)
> .withLateDecorrelate(true)
> .check();
> }{code}
> is equivalent to the following query, and the following query can be
> decorrelated into a join, so we can do some appropriate conversions in
> RelDecorrelate:
> {code:java}
> @Test void testDecorrelateFetchOne1() {
> final String query = "SELECT deptno FROM dept WHERE\n" +
> "1000 > (SELECT min(sal) FROM emp WHERE dept.deptno = emp.deptno)";
> sql(query).withRule(
> CoreRules.FILTER_SUB_QUERY_TO_CORRELATE)
> .withLateDecorrelate(true)
> .check();
> }
> @Test void testDecorrelateFetchOne2() {
> final String query = "SELECT deptno, (SELECT SINGLE_VALUE(sal) FROM emp
> where dept.deptno = emp.deptno) FROM dept";
> sql(query).withRule(
> CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE)
> .withLateDecorrelate(true)
> .check();
> }
> @Test void testDecorrelateFetchOne3() {
> final String query = "SELECT deptno, (SELECT max(sal) FROM emp where
> dept.deptno = emp.deptno) FROM dept";
> sql(query).withRule(
> CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE)
> .withLateDecorrelate(true)
> .check();
> }{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
