[calcite] branch main updated: Add tests for correlated CTEs

[jh...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git


The following commit(s) were added to refs/heads/main by this push:
     new df8ee289b1 Add tests for correlated CTEs
df8ee289b1 is described below

commit df8ee289b16d0d43c4a63fd585d9ffe9da19f30b
Author: Julian Hyde <jh...@apache.org>
AuthorDate: Wed Sep 7 11:20:52 2022 -0700

    Add tests for correlated CTEs
---
 core/src/test/resources/sql/sub-query.iq | 47 ++++++++++++++++++++++++++++++++
 1 file changed, 47 insertions(+)

diff --git a/core/src/test/resources/sql/sub-query.iq b/core/src/test/resources/sql/sub-query.iq
index c50ab43c0f..7772c743f7 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -567,6 +567,53 @@ from "scott".dept as d;
 !ok
 !}
 
+# Correlated scalar sub-query
+SELECT d.dname,
+    (WITH clerks AS
+      (SELECT *
+       FROM "scott".emp
+       WHERE deptno = d.deptno)
+     SELECT min(sal)
+     FROM clerks) AS min_clerk_sal
+FROM "scott".dept AS d;
++------------+---------------+
+| DNAME      | MIN_CLERK_SAL |
++------------+---------------+
+| ACCOUNTING |       1300.00 |
+| OPERATIONS |               |
+| RESEARCH   |        800.00 |
+| SALES      |        950.00 |
++------------+---------------+
+(4 rows)
+
+!ok
+
+# As previous, minimal
+SELECT (WITH t2 AS (SELECT t.a)
+        SELECT a FROM t2) AS c
+FROM (SELECT 1 AS a) AS t;
++---+
+| C |
++---+
+| 1 |
++---+
+(1 row)
+
+!ok
+
+# As above, converting CTE to inline view
+SELECT (SELECT a
+        FROM (SELECT t.a) AS t2) AS c
+FROM (SELECT 1 AS a) AS t;
++---+
+| C |
++---+
+| 1 |
++---+
+(1 row)
+
+!ok
+
 # [CALCITE-1494] Inefficient plan for correlated sub-queries
 # Plan must have only one scan each of emp and dept.
 select sal