suibianwanwan created CALCITE-6942:
--------------------------------------
Summary: Support decorrelated for sub-queries with LIMIT 1 and
OFFSET
Key: CALCITE-6942
URL: https://issues.apache.org/jira/browse/CALCITE-6942
Project: Calcite
Issue Type: Improvement
Reporter: suibianwanwan
CALCITE-6652 supports correlated sub-query with limit 1, such as the following
query:
{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();
} {code}
But the current implementation via first_value does not support the offset
clause:
{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 offset 10)";
sql(query).withRule(
CoreRules.FILTER_SUB_QUERY_TO_CORRELATE)
.withLateDecorrelate(true)
.check();
} {code}
Paper [Improving Unnesting of Complex
Queries|https://15799.courses.cs.cmu.edu/spring2025/papers/11-unnesting/neumann-btw2025.pdf]
describes a more generalized solution:
SQL:
{code:java}
SELECT *
FROM S
ORDER BY x LIMIT l OFFSET o {code}
Transformed to:
{code:java}
SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY x) AS RN FROM S) S
WHERE RN BETWEEN o+1 AND l+o {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
