You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by al...@apache.org on 2021/11/29 13:21:22 UTC
[ignite] branch sql-calcite updated: IGNITE-15603 Unignore correlated queries script tests - Fixes #9606.
This is an automated email from the ASF dual-hosted git repository.
alexpl pushed a commit to branch sql-calcite
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/sql-calcite by this push:
new 67f7398 IGNITE-15603 Unignore correlated queries script tests - Fixes #9606.
67f7398 is described below
commit 67f739808e1191fc290f5d655523c17c3043125f
Author: Aleksey Plekhanov <pl...@gmail.com>
AuthorDate: Mon Nov 29 16:11:32 2021 +0300
IGNITE-15603 Unignore correlated queries script tests - Fixes #9606.
Signed-off-by: Aleksey Plekhanov <pl...@gmail.com>
---
.../any_all/test_uncorrelated_all_subquery.test | 22 ++++
.../any_all/test_uncorrelated_any_subquery.test | 50 ++++++++
.../test_uncorrelated_any_subquery.test_ignore | 131 ---------------------
.../scalar/test_complex_correlated_subquery.test | 28 +++++
.../test_complex_correlated_subquery.test_ignore | 2 +-
.../scalar/test_correlated_aggregate_subquery.test | 87 +++++++++++++-
.../test_correlated_aggregate_subquery.test_ignore | 20 ++--
.../subquery/scalar/test_correlated_subquery.test | 36 +++++-
.../scalar/test_correlated_subquery.test_ignore | 32 ++---
.../scalar/test_correlated_subquery_cte.test | 71 ++++++++++-
.../test_correlated_subquery_cte.test_ignore | 43 +++----
.../scalar/test_grouped_correlated_subquery.test | 12 ++
.../test_grouped_correlated_subquery.test_ignore | 2 +-
.../sql/subquery/scalar/test_scalar_subquery.test | 18 +++
.../scalar/test_scalar_subquery.test_ignore | 17 +--
.../subquery/scalar/test_scalar_subquery_cte.test | 21 ++++
.../scalar/test_scalar_subquery_cte.test_ignore | 16 +--
17 files changed, 384 insertions(+), 224 deletions(-)
diff --git a/modules/calcite/src/test/sql/subquery/any_all/test_uncorrelated_all_subquery.test b/modules/calcite/src/test/sql/subquery/any_all/test_uncorrelated_all_subquery.test
index f3d1717..2d0b2cc 100644
--- a/modules/calcite/src/test/sql/subquery/any_all/test_uncorrelated_all_subquery.test
+++ b/modules/calcite/src/test/sql/subquery/any_all/test_uncorrelated_all_subquery.test
@@ -63,6 +63,15 @@ SELECT i FROM integers WHERE i <= ALL(SELECT i FROM integers WHERE i IS NOT NULL
----
1
+query I
+SELECT i FROM integers WHERE i = ALL(SELECT i FROM integers WHERE i=1)
+----
+1
+
+query I
+SELECT i FROM integers WHERE i = ALL(SELECT i FROM integers WHERE i IS NOT NULL)
+----
+
query I rowsort
SELECT i FROM integers WHERE i <> ALL(SELECT i FROM integers WHERE i=1)
----
@@ -124,6 +133,14 @@ NULL false
3 false
query IT
+SELECT i, i <> ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i NULLS FIRST;
+----
+NULL false
+1 false
+2 false
+3 false
+
+query IT
SELECT i, i < ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i NULLS FIRST;
----
NULL false
@@ -131,6 +148,11 @@ NULL false
2 false
3 false
+query IR
+SELECT (SELECT MAX(i) FROM integers) AS k, SUM(i) FROM integers GROUP BY 1;
+----
+3 6.000000
+
# nested uncorrelated subqueries
query I
SELECT (SELECT (SELECT (SELECT 42)))
diff --git a/modules/calcite/src/test/sql/subquery/any_all/test_uncorrelated_any_subquery.test b/modules/calcite/src/test/sql/subquery/any_all/test_uncorrelated_any_subquery.test
index ea26867..baf039c 100644
--- a/modules/calcite/src/test/sql/subquery/any_all/test_uncorrelated_any_subquery.test
+++ b/modules/calcite/src/test/sql/subquery/any_all/test_uncorrelated_any_subquery.test
@@ -79,3 +79,53 @@ NULL NULL
1 NULL
2 NULL
3 true
+
+query IT
+SELECT i, i <> ANY(SELECT i FROM integers WHERE i>2) FROM integers ORDER BY i NULLS FIRST
+----
+NULL NULL
+1 true
+2 true
+3 false
+
+query IT
+SELECT i, i <> ANY(SELECT i FROM integers WHERE i>2 OR i IS NULL) FROM integers ORDER BY i NULLS FIRST
+----
+NULL NULL
+1 true
+2 true
+3 NULL
+
+# use a bunch of cross products to make bigger data sets (> STANDARD_VECTOR_SIZE)
+query IT
+SELECT i, i = ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, integers i5, integers i6 WHERE i1.i IS NOT NULL) FROM integers ORDER BY i NULLS FIRST
+----
+NULL NULL
+1 true
+2 true
+3 true
+
+query IT
+SELECT i, i = ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, integers i5, integers i6 WHERE i1.i IS NOT NULL AND i1.i <> 2) FROM integers ORDER BY i NULLS FIRST
+----
+NULL NULL
+1 true
+2 false
+3 true
+
+query IT
+SELECT i, i >= ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, integers i5, integers i6 WHERE i1.i IS NOT NULL) FROM integers ORDER BY i NULLS FIRST
+----
+NULL NULL
+1 true
+2 true
+3 true
+
+query IT
+SELECT i, i >= ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, integers i5, integers i6 WHERE i1.i IS NOT NULL AND i1.i <> 1 ORDER BY 1 LIMIT 1) FROM integers ORDER BY i NULLS FIRST
+----
+NULL NULL
+1 false
+2 true
+3 true
+
diff --git a/modules/calcite/src/test/sql/subquery/any_all/test_uncorrelated_any_subquery.test_ignore b/modules/calcite/src/test/sql/subquery/any_all/test_uncorrelated_any_subquery.test_ignore
deleted file mode 100644
index c4c7120..0000000
--- a/modules/calcite/src/test/sql/subquery/any_all/test_uncorrelated_any_subquery.test_ignore
+++ /dev/null
@@ -1,131 +0,0 @@
-# name: test/sql/subquery/any_all/test_uncorrelated_any_subquery.test
-# description: Test uncorrelated ANY subqueries
-# group: [any_all]
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE integers(i INTEGER)
-
-statement ok
-INSERT INTO integers VALUES (1), (2), (3), (NULL)
-
-# uncorrelated ANY
-query I rowsort
-SELECT i FROM integers WHERE i <= ANY(SELECT i FROM integers);
-----
-1
-2
-3
-
-query I rowsort
-SELECT i FROM integers WHERE i > ANY(SELECT i FROM integers);
-----
-2
-3
-
-query IT
-SELECT i, i > ANY(SELECT i FROM integers) FROM integers ORDER BY i NULLS FIRST
-----
-NULL NULL
-1 NULL
-2 true
-3 true
-
-query IT
-SELECT i, i > ANY(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers ORDER BY i NULLS FIRST
-----
-NULL NULL
-1 false
-2 true
-3 true
-
-query IT
-SELECT i, NULL > ANY(SELECT i FROM integers) FROM integers ORDER BY i NULLS FIRST
-----
-NULL NULL
-1 NULL
-2 NULL
-3 NULL
-
-query IT
-SELECT i, NULL > ANY(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers ORDER BY i NULLS FIRST
-----
-NULL NULL
-1 NULL
-2 NULL
-3 NULL
-
-query I rowsort
-SELECT i FROM integers WHERE i = ANY(SELECT i FROM integers)
-----
-1
-2
-3
-
-query IT
-SELECT i, i = ANY(SELECT i FROM integers WHERE i>2) FROM integers ORDER BY i NULLS FIRST
-----
-NULL NULL
-1 false
-2 false
-3 true
-
-query IT
-SELECT i, i = ANY(SELECT i FROM integers WHERE i>2 OR i IS NULL) FROM integers ORDER BY i NULLS FIRST
-----
-NULL NULL
-1 NULL
-2 NULL
-3 true
-
-query IT
-SELECT i, i <> ANY(SELECT i FROM integers WHERE i>2) FROM integers ORDER BY i NULLS FIRST
-----
-NULL NULL
-1 true
-2 true
-3 false
-
-query IT
-SELECT i, i <> ANY(SELECT i FROM integers WHERE i>2 OR i IS NULL) FROM integers ORDER BY i NULLS FIRST
-----
-NULL NULL
-1 true
-2 true
-3 NULL
-
-# use a bunch of cross products to make bigger data sets (> STANDARD_VECTOR_SIZE)
-query IT
-SELECT i, i = ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, integers i5, integers i6 WHERE i1.i IS NOT NULL) FROM integers ORDER BY i NULLS FIRST
-----
-NULL NULL
-1 true
-2 true
-3 true
-
-query IT
-SELECT i, i = ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, integers i5, integers i6 WHERE i1.i IS NOT NULL AND i1.i <> 2) FROM integers ORDER BY i NULLS FIRST
-----
-NULL NULL
-1 true
-2 false
-3 true
-
-query IT
-SELECT i, i >= ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, integers i5, integers i6 WHERE i1.i IS NOT NULL) FROM integers ORDER BY i NULLS FIRST
-----
-NULL NULL
-1 true
-2 true
-3 true
-
-query IT
-SELECT i, i >= ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, integers i5, integers i6 WHERE i1.i IS NOT NULL AND i1.i <> 1 LIMIT 1) FROM integers ORDER BY i NULLS FIRST
-----
-NULL NULL
-1 false
-2 true
-3 true
-
diff --git a/modules/calcite/src/test/sql/subquery/scalar/test_complex_correlated_subquery.test b/modules/calcite/src/test/sql/subquery/scalar/test_complex_correlated_subquery.test
index 5c7bc20..786a52c 100644
--- a/modules/calcite/src/test/sql/subquery/scalar/test_complex_correlated_subquery.test
+++ b/modules/calcite/src/test/sql/subquery/scalar/test_complex_correlated_subquery.test
@@ -20,6 +20,24 @@ NULL NULL
2 2
3 3
+# implicit join with correlated expression in filter
+query II
+SELECT i, (SELECT s1.i FROM integers s1, integers s2 WHERE s1.i=s2.i AND s1.i=4-i1.i) AS j FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 3
+2 2
+3 1
+
+# join with a correlated expression in the join condition
+query II
+SELECT i, (SELECT s1.i FROM integers s1 INNER JOIN integers s2 ON s1.i=s2.i AND s1.i=4-i1.i) AS j FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 3
+2 2
+3 1
+
# left outer join on arbitrary correlated subquery: not supported
statement error
SELECT * FROM integers s1 LEFT OUTER JOIN integers s2 ON (SELECT CASE WHEN s1.i+s2.i>10 THEN TRUE ELSE FALSE END) ORDER BY s1.i;
@@ -44,3 +62,13 @@ SELECT i, (SELECT SUM(s1.i) FROM integers s1 FULL OUTER JOIN integers s2 ON s1.i
# correlated expression inside window function not supported
statement error
SELECT i, (SELECT row_number() OVER (ORDER BY i)) FROM integers i1 ORDER BY i;
+
+# uncorrelated query inside correlated query
+query IR
+SELECT i, (SELECT (SELECT SUM(i) FROM integers)+42+i1.i) AS j FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 49.000000
+2 50.000000
+3 51.000000
+
diff --git a/modules/calcite/src/test/sql/subquery/scalar/test_complex_correlated_subquery.test_ignore b/modules/calcite/src/test/sql/subquery/scalar/test_complex_correlated_subquery.test_ignore
index 2fdb4b1..c98bb24 100644
--- a/modules/calcite/src/test/sql/subquery/scalar/test_complex_correlated_subquery.test_ignore
+++ b/modules/calcite/src/test/sql/subquery/scalar/test_complex_correlated_subquery.test_ignore
@@ -6,7 +6,7 @@
# Ignore https://issues.apache.org/jira/browse/IGNITE-15593
# Ignore https://issues.apache.org/jira/browse/IGNITE-15588
# Ignore https://issues.apache.org/jira/browse/IGNITE-15592
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15603
+# Ignore https://issues.apache.org/jira/browse/IGNITE-16000
statement ok
PRAGMA enable_verification
diff --git a/modules/calcite/src/test/sql/subquery/scalar/test_correlated_aggregate_subquery.test b/modules/calcite/src/test/sql/subquery/scalar/test_correlated_aggregate_subquery.test
index 3afa462..f81714e 100644
--- a/modules/calcite/src/test/sql/subquery/scalar/test_correlated_aggregate_subquery.test
+++ b/modules/calcite/src/test/sql/subquery/scalar/test_correlated_aggregate_subquery.test
@@ -9,13 +9,21 @@ INSERT INTO integers VALUES (1), (2), (3), (NULL)
# correlated COUNT(*)
query II
-SELECT i, (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i) FROM integers i1 ORDER BY i NULLS FIRST;
+SELECT i, (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i) FROM integers i1 ORDER BY i;
----
NULL 0
1 2
2 1
3 0
+# aggregate with correlation inside aggregation
+query II
+SELECT i, (SELECT MIN(i+2*i1.i) FROM integers) FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 3
+2 5
+3 7
# this will fail, because "i" is not an aggregate but the SUM(i1.i) turns this query into an aggregate
statement error
@@ -23,3 +31,80 @@ SELECT i, (SELECT SUM(i1.i)) FROM integers i1;
statement error
SELECT i+1, (SELECT SUM(i1.i)) FROM integers i1;
+
+# subquery inside aggregation
+query RR
+SELECT SUM(i), SUM((SELECT i FROM integers WHERE i=i1.i)) FROM integers i1;
+----
+6.000000 6.000000
+
+# aggregate with correlation in filter
+query II
+SELECT i, (SELECT MIN(i) FROM integers WHERE i>i1.i) FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 2
+2 3
+3 NULL
+
+# aggregate with correlation in GROUP BY
+query II
+SELECT i, (SELECT MIN(i) FROM integers GROUP BY i1.i) AS j FROM integers i1 ORDER BY i;
+----
+NULL 1
+1 1
+2 1
+3 1
+
+query R
+SELECT (SELECT SUM(i)+SUM((CASE WHEN i IS NOT NULL THEN i*0 ELSE 0 END)+i1.i) FROM integers) FROM integers i1 ORDER BY 1;
+----
+NULL
+10.000000
+14.000000
+18.000000
+
+# subquery inside aggregate
+query R
+SELECT SUM((SELECT i+i1.i FROM integers WHERE i=1)) FROM integers i1;
+----
+9.000000
+
+query R
+SELECT (SELECT SUM(i1.i*i) FROM integers) FROM integers i1 ORDER BY i;
+----
+NULL
+6.000000
+12.000000
+18.000000
+
+# correlated subquery in WHERE
+query R
+SELECT SUM(i) FROM integers i1 WHERE i>(SELECT (i+i1.i)/2 FROM integers WHERE i=1);
+----
+5.000000
+
+# use scalar subquery as argument to ALL/ANY
+query IT
+SELECT i, (SELECT MIN(i) FROM integers WHERE i=i1.i) >= ALL(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 false
+2 false
+3 true
+
+query IT
+SELECT i, (SELECT MIN(i) FROM integers WHERE i<>i1.i) > ANY(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 true
+2 false
+3 false
+
+query IT
+SELECT i, NOT((SELECT MIN(i) FROM integers WHERE i<>i1.i) > ANY(SELECT i FROM integers WHERE i IS NOT NULL)) FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 false
+2 true
+3 true
diff --git a/modules/calcite/src/test/sql/subquery/scalar/test_correlated_aggregate_subquery.test_ignore b/modules/calcite/src/test/sql/subquery/scalar/test_correlated_aggregate_subquery.test_ignore
index 6f2fdad..8115546 100644
--- a/modules/calcite/src/test/sql/subquery/scalar/test_correlated_aggregate_subquery.test_ignore
+++ b/modules/calcite/src/test/sql/subquery/scalar/test_correlated_aggregate_subquery.test_ignore
@@ -2,9 +2,9 @@
# description: Test correlated aggregate subqueries
# group: [scalar]
# Ignore https://issues.apache.org/jira/browse/IGNITE-15588
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15603
# Ignore https://issues.apache.org/jira/browse/IGNITE-15605
# Ignore https://issues.apache.org/jira/browse/IGNITE-15594
+# Ignore https://issues.apache.org/jira/browse/IGNITE-16000
statement ok
PRAGMA enable_verification
@@ -312,25 +312,25 @@ query IT
SELECT i, (SELECT MIN(i) FROM integers WHERE i=i1.i) >= ALL(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers i1 ORDER BY i;
----
NULL NULL
-1 0
-2 0
-3 1
+1 false
+2 false
+3 true
query IT
SELECT i, (SELECT MIN(i) FROM integers WHERE i<>i1.i) > ANY(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers i1 ORDER BY i;
----
NULL NULL
-1 1
-2 0
-3 0
+1 true
+2 false
+3 false
query IT
SELECT i, NOT((SELECT MIN(i) FROM integers WHERE i<>i1.i) > ANY(SELECT i FROM integers WHERE i IS NOT NULL)) FROM integers i1 ORDER BY i;
----
NULL NULL
-1 0
-2 1
-3 1
+1 false
+2 true
+3 true
# aggregates with multiple parameters
query R
diff --git a/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery.test b/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery.test
index f97b405..66efc69 100644
--- a/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery.test
+++ b/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery.test
@@ -29,6 +29,28 @@ NULL
2
1
+# subquery returning multiple results
+statement error
+SELECT i, (SELECT 42+i1.i FROM integers) AS j FROM integers i1 ORDER BY i;
+
+# subquery with LIMIT
+query II
+SELECT i, (SELECT 42+i1.i FROM integers LIMIT 1) AS j FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 43
+2 44
+3 45
+
+# subquery with LIMIT 0
+query II
+SELECT i, (SELECT 42+i1.i FROM integers LIMIT 0) AS j FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 NULL
+2 NULL
+3 NULL
+
# subquery with WHERE clause that is always FALSE
query II
SELECT i, (SELECT i FROM integers WHERE 1=0 AND i1.i=i) AS j FROM integers i1 ORDER BY i;
@@ -40,7 +62,7 @@ NULL NULL
# correlated EXISTS with WHERE clause that is always FALSE
query IT
-SELECT i, EXISTS(SELECT i FROM integers WHERE 1=0 AND i1.i=i) AS j FROM integers i1 ORDER BY i NULLS FIRST;
+SELECT i, EXISTS(SELECT i FROM integers WHERE 1=0 AND i1.i=i) AS j FROM integers i1 ORDER BY i;
----
NULL false
1 false
@@ -49,7 +71,7 @@ NULL false
# correlated ANY with WHERE clause that is always FALSE
query IT
-SELECT i, i=ANY(SELECT i FROM integers WHERE 1=0 AND i1.i=i) AS j FROM integers i1 ORDER BY i NULLS FIRST;
+SELECT i, i=ANY(SELECT i FROM integers WHERE 1=0 AND i1.i=i) AS j FROM integers i1 ORDER BY i;
----
NULL false
1 false
@@ -64,9 +86,13 @@ SELECT i, (SELECT i+i1.i FROM integers LIMIT 1 OFFSET 1) AS j FROM integers i1 O
statement error
SELECT i, (SELECT i+i1.i FROM integers ORDER BY 1 LIMIT 1 OFFSET 1) AS j FROM integers i1 ORDER BY i;
+# correlated filter without FROM clause
+statement error
+SELECT i, (SELECT 42 WHERE i1.i>2) AS j FROM integers i1 ORDER BY i;
+
# scalar select with correlation in projection
query II
-SELECT i, (SELECT i+i1.i FROM integers WHERE i=1) AS j FROM integers i1 ORDER BY i NULLS FIRST;
+SELECT i, (SELECT i+i1.i FROM integers WHERE i=1) AS j FROM integers i1 ORDER BY i;
----
NULL NULL
1 2
@@ -75,7 +101,7 @@ NULL NULL
# scalar select with correlation in filter
query II
-SELECT i, (SELECT i FROM integers WHERE i=i1.i) AS j FROM integers i1 ORDER BY i NULLS FIRST;
+SELECT i, (SELECT i FROM integers WHERE i=i1.i) AS j FROM integers i1 ORDER BY i;
----
NULL NULL
1 1
@@ -84,7 +110,7 @@ NULL NULL
# scalar select with operation in projection
query II
-SELECT i, (SELECT i+1 FROM integers WHERE i=i1.i) AS j FROM integers i1 ORDER BY i NULLS FIRST;
+SELECT i, (SELECT i+1 FROM integers WHERE i=i1.i) AS j FROM integers i1 ORDER BY i;
----
NULL NULL
1 2
diff --git a/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery.test_ignore b/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery.test_ignore
index a739426..62cca4c 100644
--- a/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery.test_ignore
+++ b/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery.test_ignore
@@ -1,8 +1,8 @@
# name: test/sql/subquery/scalar/test_correlated_subquery.test
# description: Test correlated subqueries
# group: [scalar]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15603
# Ignore https://issues.apache.org/jira/browse/IGNITE-15604
+# Ignore https://issues.apache.org/jira/browse/IGNITE-15993
statement ok
PRAGMA enable_verification
@@ -32,13 +32,8 @@ NULL
1
# subquery returning multiple results
-query II
+statement error
SELECT i, (SELECT 42+i1.i FROM integers) AS j FROM integers i1 ORDER BY i;
-----
-NULL NULL
-1 43
-2 44
-3 45
# subquery with LIMIT
query II
@@ -71,19 +66,19 @@ NULL NULL
query IT
SELECT i, EXISTS(SELECT i FROM integers WHERE 1=0 AND i1.i=i) AS j FROM integers i1 ORDER BY i;
----
-NULL 0
-1 0
-2 0
-3 0
+NULL false
+1 false
+2 false
+3 false
# correlated ANY with WHERE clause that is always FALSE
query IT
SELECT i, i=ANY(SELECT i FROM integers WHERE 1=0 AND i1.i=i) AS j FROM integers i1 ORDER BY i;
----
-NULL 0
-1 0
-2 0
-3 0
+NULL false
+1 false
+2 false
+3 false
# subquery with OFFSET is not supported
statement error
@@ -94,13 +89,8 @@ statement error
SELECT i, (SELECT i+i1.i FROM integers ORDER BY 1 LIMIT 1 OFFSET 1) AS j FROM integers i1 ORDER BY i;
# correlated filter without FROM clause
-query II
+statement error
SELECT i, (SELECT 42 WHERE i1.i>2) AS j FROM integers i1 ORDER BY i;
-----
-NULL NULL
-1 NULL
-2 NULL
-3 42
# correlated filter with matching entry on NULL
query II
diff --git a/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery_cte.test b/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery_cte.test
index 722c3ad..bbe6bcb 100644
--- a/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery_cte.test
+++ b/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery_cte.test
@@ -13,13 +13,34 @@ INSERT INTO integers VALUES (1), (2), (3), (NULL)
# scalar select with correlation
query II
-SELECT i, (WITH i2 AS (SELECT 42+i1.i AS j) SELECT j FROM i2) AS j FROM integers i1 ORDER BY i NULLS FIRST;
+SELECT i, (WITH i2 AS (SELECT 42+i1.i AS j) SELECT j FROM i2) AS j FROM integers i1 ORDER BY i;
----
NULL NULL
1 43
2 44
3 45
+# subquery returning multiple results
+statement error
+SELECT i, (WITH i2 AS (SELECT 42+i1.i AS j FROM integers) SELECT j FROM i2) AS j FROM integers i1 ORDER BY i;
+
+# subquery with LIMIT
+query II
+SELECT i, (WITH i2 AS (SELECT 42+i1.i AS j FROM integers) SELECT j FROM i2 LIMIT 1) AS j FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 43
+2 44
+3 45
+
+# subquery with LIMIT 0
+query II
+SELECT i, (WITH i2 AS (SELECT 42+i1.i AS j FROM integers) SELECT j FROM i2 LIMIT 0) AS j FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 NULL
+2 NULL
+3 NULL
# subquery with WHERE clause that is always FALSE
query II
@@ -32,7 +53,7 @@ NULL NULL
# correlated EXISTS with WHERE clause that is always FALSE
query IT
-SELECT i, EXISTS(WITH i2 AS (SELECT i FROM integers WHERE 1=0 AND i1.i=i) SELECT i FROM i2) AS j FROM integers i1 ORDER BY i NULLS FIRST;
+SELECT i, EXISTS(WITH i2 AS (SELECT i FROM integers WHERE 1=0 AND i1.i=i) SELECT i FROM i2) AS j FROM integers i1 ORDER BY i;
----
NULL false
1 false
@@ -41,9 +62,53 @@ NULL false
# correlated ANY with WHERE clause that is always FALSE
query IT
-SELECT i, i=ANY(WITH i2 AS (SELECT i FROM integers WHERE 1=0 AND i1.i=i) SELECT i FROM i2) AS j FROM integers i1 ORDER BY i NULLS FIRST;
+SELECT i, i=ANY(WITH i2 AS (SELECT i FROM integers WHERE 1=0 AND i1.i=i) SELECT i FROM i2) AS j FROM integers i1 ORDER BY i;
----
NULL false
1 false
2 false
3 false
+
+# subquery with OFFSET is not supported
+statement error
+SELECT i, (WITH i2 AS (SELECT i+i1.i FROM integers LIMIT 1 OFFSET 1) SELECT * FROM i2) AS j FROM integers i1 ORDER BY i;
+
+# subquery with ORDER BY is not supported
+statement error
+SELECT i, (WITH i2 AS (SELECT i+i1.i FROM integers ORDER BY 1 LIMIT 1 OFFSET 1) SELECT * FROM i2) AS j FROM integers i1 ORDER BY i;
+
+# correlated filter without FROM clause
+statement error
+SELECT i, (WITH i2 AS (SELECT 42 WHERE i1.i>2) SELECT * FROM i2) AS j FROM integers i1 ORDER BY i;
+
+# correlated filter with matching entry on NULL
+statement error
+SELECT i, (WITH i2 AS (SELECT 42 WHERE i1.i IS NULL) SELECT * FROM i2) AS j FROM integers i1 ORDER BY i;
+
+# scalar select with correlation in projection
+query II
+SELECT i, (WITH i2 AS (SELECT i+i1.i FROM integers WHERE i=1) SELECT * FROM i2) AS j FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 2
+2 3
+3 4
+
+# scalar select with correlation in filter
+query II
+SELECT i, (WITH i2 AS (SELECT i FROM integers WHERE i=i1.i) SELECT * FROM i2) AS j FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 1
+2 2
+3 3
+
+# scalar select with operation in projection
+query II
+SELECT i, (WITH i2 AS (SELECT i+1 FROM integers WHERE i=i1.i) SELECT * FROM i2) AS j FROM integers i1 ORDER BY i;
+----
+NULL NULL
+1 2
+2 3
+3 4
+
diff --git a/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery_cte.test_ignore b/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery_cte.test_ignore
index 57971c4..de5bae8 100644
--- a/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery_cte.test_ignore
+++ b/modules/calcite/src/test/sql/subquery/scalar/test_correlated_subquery_cte.test_ignore
@@ -1,8 +1,8 @@
# name: test/sql/subquery/scalar/test_correlated_subquery_cte.test
# description: Test correlated subqueries
# group: [scalar]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15583
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15603
+# Ignore https://issues.apache.org/jira/browse/IGNITE-15993
+# Ignore https://issues.apache.org/jira/browse/IGNITE-15995
statement ok
PRAGMA enable_verification
@@ -32,13 +32,8 @@ NULL
1
# subquery returning multiple results
-query II
+statement error
SELECT i, (WITH i2 AS (SELECT 42+i1.i AS j FROM integers) SELECT j FROM i2) AS j FROM integers i1 ORDER BY i;
-----
-NULL NULL
-1 43
-2 44
-3 45
# subquery with LIMIT
query II
@@ -60,7 +55,7 @@ NULL NULL
# subquery with WHERE clause that is always FALSE
query II
-SELECT i, (WITH i2 AS (SELECT i FROM integers WHERE 1=0 AND i1.i=i) SELECT i FROM i2) AS j FROM integers i1 ORDER BY i;
+SELECT i, (WITH i2 AS (SELECT i FROM integers WHERE 1=0 AND i1.i=i) SELECT i FROM i2) AS j FROM integers i1 ORDER BY i NULLS FIRST;
----
NULL NULL
1 NULL
@@ -71,19 +66,19 @@ NULL NULL
query IT
SELECT i, EXISTS(WITH i2 AS (SELECT i FROM integers WHERE 1=0 AND i1.i=i) SELECT i FROM i2) AS j FROM integers i1 ORDER BY i;
----
-NULL 0
-1 0
-2 0
-3 0
+NULL false
+1 false
+2 false
+3 false
# correlated ANY with WHERE clause that is always FALSE
query IT
SELECT i, i=ANY(WITH i2 AS (SELECT i FROM integers WHERE 1=0 AND i1.i=i) SELECT i FROM i2) AS j FROM integers i1 ORDER BY i;
----
-NULL 0
-1 0
-2 0
-3 0
+NULL false
+1 false
+2 false
+3 false
# subquery with OFFSET is not supported
statement error
@@ -94,22 +89,12 @@ statement error
SELECT i, (WITH i2 AS (SELECT i+i1.i FROM integers ORDER BY 1 LIMIT 1 OFFSET 1) SELECT * FROM i2) AS j FROM integers i1 ORDER BY i;
# correlated filter without FROM clause
-query II
+statement error
SELECT i, (WITH i2 AS (SELECT 42 WHERE i1.i>2) SELECT * FROM i2) AS j FROM integers i1 ORDER BY i;
-----
-NULL NULL
-1 NULL
-2 NULL
-3 42
# correlated filter with matching entry on NULL
-query II
+statement error
SELECT i, (WITH i2 AS (SELECT 42 WHERE i1.i IS NULL) SELECT * FROM i2) AS j FROM integers i1 ORDER BY i;
-----
-NULL 42
-1 NULL
-2 NULL
-3 NULL
# scalar select with correlation in projection
query II
diff --git a/modules/calcite/src/test/sql/subquery/scalar/test_grouped_correlated_subquery.test b/modules/calcite/src/test/sql/subquery/scalar/test_grouped_correlated_subquery.test
index f66ab64..e40c6d7 100644
--- a/modules/calcite/src/test/sql/subquery/scalar/test_grouped_correlated_subquery.test
+++ b/modules/calcite/src/test/sql/subquery/scalar/test_grouped_correlated_subquery.test
@@ -24,3 +24,15 @@ true
false
false
false
+
+query T
+SELECT col1 IN (SELECT ColID + col1 FROM tbl_ProductSales) FROM another_T;
+----
+false
+false
+false
+false
+
+# this should fail, col1 + 42 is not a grouping column
+statement error
+SELECT col1+1, col1+42 FROM another_T GROUP BY col1+1;
diff --git a/modules/calcite/src/test/sql/subquery/scalar/test_grouped_correlated_subquery.test_ignore b/modules/calcite/src/test/sql/subquery/scalar/test_grouped_correlated_subquery.test_ignore
index 6021511..90cbab3 100644
--- a/modules/calcite/src/test/sql/subquery/scalar/test_grouped_correlated_subquery.test_ignore
+++ b/modules/calcite/src/test/sql/subquery/scalar/test_grouped_correlated_subquery.test_ignore
@@ -1,8 +1,8 @@
# name: test/sql/subquery/scalar/test_grouped_correlated_subquery.test
# description: Test correlated subquery with grouping columns
# group: [scalar]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15603
# Ignore https://issues.apache.org/jira/browse/IGNITE-15607
+# Ignore https://issues.apache.org/jira/browse/IGNITE-16000
statement ok
PRAGMA enable_verification
diff --git a/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery.test b/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery.test
index 07d9a0a..3fa46fc 100644
--- a/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery.test
+++ b/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery.test
@@ -107,3 +107,21 @@ SELECT a, (SELECT CASE WHEN test.a=11 THEN 22 ELSE NULL END) FROM test ORDER BY
11 22
12 NULL
13 NULL
+
+query II
+SELECT * from test where a=(SELECT MIN(a) FROM test t WHERE t.b=test.b) ORDER BY 1
+----
+11 22
+12 21
+
+# exists / in / any subqueries
+query II
+SELECT * FROM test WHERE EXISTS (SELECT a FROM test ts WHERE ts.a = test.a AND b>21) ORDER BY 1
+----
+11 22
+13 22
+
+# duplicate name in subquery
+statement error
+SELECT * FROM (SELECT 42 AS a, 44 AS a) tbl1
+
diff --git a/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery.test_ignore b/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery.test_ignore
index 044aa54..9c9bc24 100644
--- a/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery.test_ignore
+++ b/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery.test_ignore
@@ -1,7 +1,7 @@
# name: test/sql/subquery/scalar/test_scalar_subquery.test
# description: Test subqueries
# group: [scalar]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15603
+# Ignore https://issues.apache.org/jira/browse/IGNITE-15992
statement ok
PRAGMA enable_verification
@@ -110,28 +110,21 @@ SELECT a, (SELECT CASE WHEN test.a=11 THEN 22 ELSE NULL END) FROM test ORDER BY
13 NULL
query II
-SELECT a, (SELECT CASE WHEN test.a=11 THEN b ELSE NULL END FROM test tsub) FROM test ORDER BY a
+SELECT a, (SELECT CASE WHEN test.a=11 THEN b ELSE NULL END FROM test tsub ORDER BY 1 LIMIT 1) FROM test ORDER BY a
----
-11 22
-12 NULL
-13 NULL
-
-query II
-SELECT a, (SELECT CASE WHEN test.a=11 THEN b ELSE NULL END FROM test tsub LIMIT 1) FROM test ORDER BY a
-----
-11 22
+11 21
12 NULL
13 NULL
query II
-SELECT * from test where a=(SELECT MIN(a) FROM test t WHERE t.b=test.b)
+SELECT * from test where a=(SELECT MIN(a) FROM test t WHERE t.b=test.b) ORDER BY 1
----
11 22
12 21
# exists / in / any subqueries
query II
-SELECT * FROM test WHERE EXISTS (SELECT a FROM test ts WHERE ts.a = test.a AND b>21)
+SELECT * FROM test WHERE EXISTS (SELECT a FROM test ts WHERE ts.a = test.a AND b>21) ORDER BY 1
----
11 22
13 22
diff --git a/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery_cte.test b/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery_cte.test
index 1598ae3..49a7b17 100644
--- a/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery_cte.test
+++ b/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery_cte.test
@@ -107,3 +107,24 @@ SELECT a, (WITH cte AS (SELECT CASE WHEN test.a=11 THEN 22 ELSE NULL END) SELECT
11 22
12 NULL
13 NULL
+
+statement error
+SELECT a, (WITH cte AS (SELECT CASE WHEN test.a=11 THEN b ELSE NULL END FROM test tsub) SELECT * FROM cte) FROM test ORDER BY a
+
+query II
+SELECT * from test where a=(WITH cte AS (SELECT a FROM test t WHERE t.b=test.b) SELECT min(a) FROM cte) ORDER BY 1
+----
+11 22
+12 21
+
+# exists / in / any subqueries
+query II
+SELECT * FROM test WHERE EXISTS (WITH cte AS (SELECT * FROM test ts WHERE ts.a = test.a AND b>21) SELECT a FROM cte) ORDER BY 1
+----
+11 22
+13 22
+
+# duplicate name in subquery
+statement error
+SELECT * FROM (WITH cte AS (SELECT 42 AS a, 44 AS a) SELECT * FROM cte) tbl1
+
diff --git a/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery_cte.test_ignore b/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery_cte.test_ignore
index 9c5ed16..c390b45 100644
--- a/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery_cte.test_ignore
+++ b/modules/calcite/src/test/sql/subquery/scalar/test_scalar_subquery_cte.test_ignore
@@ -1,7 +1,7 @@
# name: test/sql/subquery/scalar/test_scalar_subquery_cte.test
# description: Test subqueries with CTEs
# group: [scalar]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15603
+# Ignore https://issues.apache.org/jira/browse/IGNITE-15992
statement ok
PRAGMA enable_verification
@@ -115,29 +115,25 @@ SELECT a, (WITH cte AS (SELECT CASE WHEN test.a=11 THEN 22 ELSE NULL END) SELECT
12 NULL
13 NULL
-query II
+statement error
SELECT a, (WITH cte AS (SELECT CASE WHEN test.a=11 THEN b ELSE NULL END FROM test tsub) SELECT * FROM cte) FROM test ORDER BY a
-----
-11 22
-12 NULL
-13 NULL
query II
-SELECT a, (WITH cte AS (SELECT CASE WHEN test.a=11 THEN b ELSE NULL END FROM test tsub LIMIT 1) SELECT * FROM cte) FROM test ORDER BY a
+SELECT a, (WITH cte AS (SELECT CASE WHEN test.a=11 THEN b ELSE NULL END FROM test tsub ORDER BY 1 LIMIT 1) SELECT * FROM cte) FROM test ORDER BY a
----
-11 22
+11 21
12 NULL
13 NULL
query II
-SELECT * from test where a=(WITH cte AS (SELECT a FROM test t WHERE t.b=test.b) SELECT min(a) FROM cte)
+SELECT * from test where a=(WITH cte AS (SELECT a FROM test t WHERE t.b=test.b) SELECT min(a) FROM cte) ORDER BY 1
----
11 22
12 21
# exists / in / any subqueries
query II
-SELECT * FROM test WHERE EXISTS (WITH cte AS (SELECT * FROM test ts WHERE ts.a = test.a AND b>21) SELECT a FROM cte)
+SELECT * FROM test WHERE EXISTS (WITH cte AS (SELECT * FROM test ts WHERE ts.a = test.a AND b>21) SELECT a FROM cte) ORDER BY 1
----
11 22
13 22