[jira] [Created] (CALCITE-7382) The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1

["Zhen Chen (Jira)" <ji...@apache.org>]

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)