[
https://issues.apache.org/jira/browse/CALCITE-6652?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17893951#comment-17893951
]
suibianwanwan commented on CALCITE-6652:
----------------------------------------
The optimization for the following query is a deterministic optimization,
independent of the uniqueness of the fields.
{code:java}
// origin sql
SELECT deptno FROM dept WHERE 1000 > (SELECT sal FROM emp WHERE dept.deptno =
emp.deptno order by emp.sal limit 1)
// optimize
SELECT deptno FROM dept LEFT JOIN WHERE 1000 > (SELECT max(sal) FROM emp WHERE
dept.deptno = emp.deptno order by emp.sal limit 1) {code}
However, this optimization is restricted to LIMIT 1, whereas the optimization
of [CALCITE-1317] appears to be generalized.
For the mentioned paper on WinMagic, I had expected to implement a similar
optimization. In the traditional business department that I come across, I
often encounter statements with a lot of correlated subqueries, and it would be
a good optimization for some query engines if they could be optimized as window
functions and could do some CTEs. In Calcite, this seems to be somewhat similar
to MaterializationRule. This requires extracting information from the SPJG to
determine if the external query can satisfy the internal query. (I may have
digressed slightly)
In fact, what this PR's trying to do is decorrelate such a query. If you write
a SQL, Calcite can decorrelate the following correlation subqueries:
{code:java}
SELECT deptno, (SELECT sal FROM emp where dept.deptno = emp.deptno) FROM
dept{code}
But SQL users tend to add limit 1, to ensure that there is only one value. But
this will decorrelate fail in calcite. So decorrelate such query is what this
PR expects, and I may have been a bit misleading in this description
{code:java}
SELECT deptno, (SELECT sal FROM emp where dept.deptno = emp.deptno limit 1)
FROM dept {code}
> 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)
