Zhen Chen created CALCITE-7382:
----------------------------------
Summary: The TopDownGeneralDecorrelator returns an error result
when a subquery contains a LIMIT 1
Key: CALCITE-7382
URL: https://issues.apache.org/jira/browse/CALCITE-7382
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Zhen Chen
The following are the correct results.
{code:java}
!use scott
SELECT dname, (SELECT emp.comm FROM "scott".emp where dept.deptno = emp.deptno
ORDER BY emp.comm limit 1) FROM "scott".dept;
+------------+--------+
| DNAME | EXPR$1 |
+------------+--------+
| ACCOUNTING | |
| OPERATIONS | |
| RESEARCH | |
| SALES | 0.00 |
+------------+--------+
(4 rows)
!ok {code}
However, TopDownGeneralDecorrelator yielded the following result.
{code:java}
!use scott
SELECT dname, (SELECT emp.comm FROM "scott".emp where dept.deptno = emp.deptno
ORDER BY emp.comm limit 1) FROM "scott".dept;
+------------+---------+
| DNAME | EXPR$1 |
+------------+---------+
| ACCOUNTING | |
| ACCOUNTING | |
| ACCOUNTING | |
| OPERATIONS | |
| RESEARCH | |
| RESEARCH | |
| RESEARCH | |
| RESEARCH | |
| RESEARCH | |
| SALES | 0.00 |
| SALES | 1400.00 |
| SALES | 300.00 |
| SALES | 500.00 |
| SALES | |
| SALES | |
+------------+---------+
(15 rows)
!ok {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
