[
https://issues.apache.org/jira/browse/CALCITE-6942?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihai Budiu resolved CALCITE-6942.
----------------------------------
Fix Version/s: 1.42.0
Resolution: Fixed
Fixed in
[https://github.com/apache/calcite/commit/1e92bbe799252dd47b1bd7133211ad4761e53a06]
Thank you for the fix [~zwhtx]
Thank you to all the people who commented on the PR
> 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
> Priority: Critical
> Labels: pull-request-available
> Fix For: 1.42.0
>
>
> 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)
