You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by zs...@apache.org on 2022/02/03 11:47:44 UTC
[ignite] branch sql-calcite updated: IGNITE-15563 Calcite. Support RANGE functionality - Fixes #9774.
This is an automated email from the ASF dual-hosted git repository.
zstan 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 cf76a91 IGNITE-15563 Calcite. Support RANGE functionality - Fixes #9774.
cf76a91 is described below
commit cf76a91b9fe5c3b19ba0b2d2d49fb56049147501
Author: zstan <st...@gmail.com>
AuthorDate: Thu Feb 3 14:11:46 2022 +0300
IGNITE-15563 Calcite. Support RANGE functionality - Fixes #9774.
Signed-off-by: zstan <st...@gmail.com>
---
.../query/calcite/logical/SqlScriptRunner.java | 5 +-
...e_delete.test_ignore => test_large_delete.test} | 3 +-
.../sql/delete/test_segment_deletes.test_ignore | 180 ---------------------
..._clause.test_ignore => test_filter_clause.test} | 135 +++-------------
.../test/sql/filter/test_filter_clause.test_ignore | 67 +++-----
...rs.test_ignore => test_transitive_filters.test} | 119 ++++++++------
.../calcite/src/test/sql/filter/test_zonemap.test | 34 ++++
.../test/sql/filter/test_zonemap.test_slow_ignore | 77 ---------
.../src/test/sql/insert/null_values.test_ignore | 42 -----
...nore => test_full_outer_join_many_matches.test} | 12 +-
.../test_cross_product_parallelism.test_ignore | 12 +-
...der_large.test_ignore => test_order_large.test} | 3 +-
.../test_order_variable_size_payload.test_ignore | 6 +-
.../test/sql/types/date/test_bc_dates.test_ignore | 2 +-
.../src/test/sql/types/list/list.test_slow_ignore | 4 +-
.../sql/types/list/list_aggregates.test_ignore | 17 +-
16 files changed, 179 insertions(+), 539 deletions(-)
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/SqlScriptRunner.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/SqlScriptRunner.java
index 4d4ed4b..7f34495 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/SqlScriptRunner.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/SqlScriptRunner.java
@@ -713,8 +713,9 @@ public class SqlScriptRunner {
}
if (!res0.equals(expectedHash))
- throw new AssertionError("Unexpected hash result, expected=" + expectedHash +
- ", values=" + res.size() * res.get(0).size() + ", expected=" + expectedRows);
+ throw new AssertionError("Unexpected hash result, error at: " + posDesc +
+ ", expected=" + expectedHash + ", calculated=" + res0 +
+ ", expectedRows=" + expectedRows + ", returnedRows=" + res.size() * res.get(0).size());
}
/** {@inheritDoc} */
diff --git a/modules/calcite/src/test/sql/delete/test_large_delete.test_ignore b/modules/calcite/src/test/sql/delete/test_large_delete.test
similarity index 65%
rename from modules/calcite/src/test/sql/delete/test_large_delete.test_ignore
rename to modules/calcite/src/test/sql/delete/test_large_delete.test
index 6e1b8d5..3263a36 100644
--- a/modules/calcite/src/test/sql/delete/test_large_delete.test_ignore
+++ b/modules/calcite/src/test/sql/delete/test_large_delete.test
@@ -1,10 +1,9 @@
# name: test/sql/delete/test_large_delete.test
# description: Test scan with large deletions
# group: [delete]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15563
statement ok
-CREATE TABLE a AS SELECT * FROM range(0, 10000, 1) t1(i);
+CREATE TABLE a AS SELECT * FROM table(system_range(1, 10000, 1)) t1(i);
statement ok
DELETE FROM a WHERE i >= 2000 AND i < 5000;
diff --git a/modules/calcite/src/test/sql/delete/test_segment_deletes.test_ignore b/modules/calcite/src/test/sql/delete/test_segment_deletes.test_ignore
deleted file mode 100644
index 8cc8ed6..0000000
--- a/modules/calcite/src/test/sql/delete/test_segment_deletes.test_ignore
+++ /dev/null
@@ -1,180 +0,0 @@
-# name: test/sql/delete/test_segment_deletes.test
-# description: Test deletions
-# group: [delete]
-# https://issues.apache.org/jira/browse/IGNITE-15563
-
-statement ok con1
-CREATE TABLE a(i INTEGER);
-
-# insert the values [0, 1, 2, .. 1022, 1023] repeatedly
-# however, make sure the order that we insert them is
-loop i 0 20
-
-statement ok con1
-INSERT INTO a SELECT * FROM range(0, 1024, 1);
-
-endloop
-
-# verify the count
-
-query I con1
-SELECT COUNT(*) FROM a
-----
-20480
-
-# we test the values 0, 1, 1022 and 1023
-# for every tested value, delete it and then rollback
-
-# value = 0
-statement ok con1
-BEGIN TRANSACTION;
-DELETE FROM a WHERE i=0;
-
-# verify the deleted count
-query I con1
-SELECT COUNT(*) FROM a
-----
-20460
-
-statement ok con1
-ROLLBACK
-
-# verify the initial count
-query I con1
-SELECT COUNT(*) FROM a
-----
-20480
-
-# value = 1
-statement ok con1
-BEGIN TRANSACTION;
-DELETE FROM a WHERE i=1;
-
-# verify the deleted count
-query I con1
-SELECT COUNT(*) FROM a
-----
-20460
-
-statement ok con1
-ROLLBACK
-
-# verify the initial count
-query I con1
-SELECT COUNT(*) FROM a
-----
-20480
-
-# value = 1022
-statement ok con1
-BEGIN TRANSACTION;
-DELETE FROM a WHERE i=1022;
-
-# verify the deleted count
-query I con1
-SELECT COUNT(*) FROM a
-----
-20460
-
-statement ok con1
-ROLLBACK
-
-# verify the initial count
-query I con1
-SELECT COUNT(*) FROM a
-----
-20480
-
-# value = 1023
-statement ok con1
-BEGIN TRANSACTION;
-DELETE FROM a WHERE i=1023;
-
-# verify the deleted count
-query I con1
-SELECT COUNT(*) FROM a
-----
-20460
-
-statement ok con1
-ROLLBACK
-
-# verify the initial count
-query I con1
-SELECT COUNT(*) FROM a
-----
-20480
-
-# now, for every tested value, delete it in a separate connection and verify the count
-# con2 -> 0
-statement ok con2
-BEGIN TRANSACTION;
-
-statement ok con2
-DELETE FROM a WHERE i=0;
-
-query I con2
-SELECT COUNT(*) FROM a;
-----
-20460
-
-# con3 -> 1
-statement ok con3
-BEGIN TRANSACTION;
-
-statement ok con3
-DELETE FROM a WHERE i=1;
-
-query I con3
-SELECT COUNT(*) FROM a;
-----
-20460
-
-# con4 -> 1022
-statement ok con4
-BEGIN TRANSACTION;
-
-statement ok con4
-DELETE FROM a WHERE i=1022;
-
-query I con4
-SELECT COUNT(*) FROM a;
-----
-20460
-
-# con5 -> 1023
-statement ok con5
-BEGIN TRANSACTION;
-
-statement ok con5
-DELETE FROM a WHERE i=1023;
-
-query I con5
-SELECT COUNT(*) FROM a;
-----
-20460
-
-# con1 still has the original count
-query I con1
-SELECT COUNT(*) FROM a;
-----
-20480
-
-# until we update the other transactions
-statement ok con2
-COMMIT
-
-statement ok con3
-COMMIT
-
-statement ok con4
-COMMIT
-
-statement ok con5
-COMMIT
-
-# now the count is updated
-query I con1
-SELECT COUNT(*) FROM a;
-----
-20400
diff --git a/modules/calcite/src/test/sql/filter/test_filter_clause.test_ignore b/modules/calcite/src/test/sql/filter/test_filter_clause.test
similarity index 70%
copy from modules/calcite/src/test/sql/filter/test_filter_clause.test_ignore
copy to modules/calcite/src/test/sql/filter/test_filter_clause.test
index 58d2188..c484468 100644
--- a/modules/calcite/src/test/sql/filter/test_filter_clause.test_ignore
+++ b/modules/calcite/src/test/sql/filter/test_filter_clause.test
@@ -1,13 +1,14 @@
# name: test/sql/filter/test_filter_clause.test
# description: Test aggregation with filter clause
# group: [filter]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15563
statement ok
-PRAGMA enable_verification
+create table t as select x i, mod(x,10) j,mod(x,10)*10000000 k from table(system_range(0, 999));
-statement ok
-create temporary table t as select range i, mod(range,10) j,mod(range,10)*10000000 k from range(1000);
+query I
+SELECT count(*) from t;
+----
+1000
query II
@@ -94,7 +95,7 @@ FROM t;
----
45 10
-query II
+query II rowsort
SELECT
sum(i) AS unfiltered,
sum(i) FILTER (WHERE i < 5) AS filtered
@@ -112,15 +113,14 @@ group by j;
50300 NULL
50400 NULL
-
-
-query III
+query III rowsort
SELECT
COUNT(*) AS unfiltered,
COUNT(*) FILTER (WHERE i > 5 and i < 10) AS filtered,
j
FROM t
-group by j;
+group by j
+order by j;
----
100 0 0
100 0 1
@@ -133,9 +133,7 @@ group by j;
100 1 8
100 1 9
-
-
-query II
+query II rowsort
SELECT
SUM(i) AS unfiltered,
SUM(i) FILTER (WHERE i between 5 and 10) AS filtered
@@ -153,10 +151,7 @@ group by j;
50300 8
50400 9
-
-
-
-query III
+query III rowsort
SELECT
SUM(i) AS unfiltered,
SUM(j) FILTER (WHERE j = 1) AS filtered,
@@ -165,7 +160,7 @@ FROM t
group by j;
----
49500 NULL 0
-49600 100 1
+49600 100 1
49700 NULL 2
49800 NULL 3
49900 NULL 4
@@ -175,8 +170,7 @@ group by j;
50300 NULL 8
50400 NULL 9
-
-query I
+query I rowsort
SELECT
SUM(j) FILTER (WHERE i < 10)
FROM t
@@ -193,7 +187,7 @@ group by j;
8
9
-query I
+query I rowsort
SELECT
SUM(j) FILTER (WHERE i < (select 10))
FROM t
@@ -242,7 +236,7 @@ limit 5;
45
# multiple filters
-query II
+query II rowsort
SELECT
SUM(j) FILTER (WHERE i < 10),
SUM(i) FILTER (WHERE i < 5)
@@ -260,8 +254,7 @@ group by j;
8 NULL
9 NULL
-
-query II
+query II rowsort
SELECT
sum(i) AS unfiltered,
sum(i) FILTER (WHERE i < 5) AS filtered
@@ -279,9 +272,7 @@ group by k;
50300 NULL
50400 NULL
-
-
-query II
+query II rowsort
SELECT
COUNT(*) AS unfiltered,
COUNT(*) FILTER (WHERE i > 5 and i < 10) AS filtered
@@ -299,9 +290,7 @@ group by k;
100 1
100 1
-
-
-query II
+query II rowsort
SELECT
SUM(i) AS unfiltered,
SUM(i) FILTER (WHERE i between 5 and 10) AS filtered
@@ -319,10 +308,7 @@ group by k;
50300 8
50400 9
-
-
-
-query III
+query III rowsort
SELECT
SUM(i) AS unfiltered,
SUM(j) FILTER (WHERE j = 1) AS filtered,
@@ -341,8 +327,7 @@ group by k;
50300 NULL 80000000
50400 NULL 90000000
-
-query I
+query I rowsort
SELECT
SUM(j) FILTER (WHERE i < 10)
FROM t
@@ -359,7 +344,7 @@ group by k;
8
9
-query I
+query I rowsort
SELECT
SUM(j) FILTER (WHERE i < (select 10))
FROM t
@@ -376,7 +361,7 @@ group by k;
8
9
-query I
+query I rowsort
SELECT
SUM(i) FILTER (WHERE i < (select i from t as t2 where t.i = t2.i))
FROM t
@@ -408,7 +393,7 @@ limit 5;
45
# multiple filters
-query II
+query II rowsort
SELECT
SUM(j) FILTER (WHERE i < 10),
SUM(i) FILTER (WHERE i < 5)
@@ -428,7 +413,7 @@ group by k;
# use correlated expression inside the filter itself
-query I
+query I rowsort
SELECT
(select sum(t2.i) FILTER (where t.i = t2.i) from t as t2)
FROM t
@@ -442,7 +427,7 @@ where i < 5
#Query with many different filter clauses (e.g. 5 aggregates, 5 different filters)
statement ok
-create temporary table t_2 as select range a, length(range) b, mod(range,100) c, 5 d, 10000 e from range(1000);
+create table t_2 as select x a, length(x) b, mod(x,100) c, 5 d, 10000 e from table(system_range(0, 999)) t1(x);
query IIIII
select count (a) filter (where a>10 and a < 15), count (b) filter (where b between 1 and 3),
@@ -461,75 +446,6 @@ select count (a) filter (where a>10 and a < 15), count (b) filter (where b betwe
4 90 0 90 0
0 900 90 900 0
-
-#Filter with some more complex aggregates: COVAR_POP (multiple input columns), STRING_AGG (strings) and ARRAY_AGG (lists)
-query II
-select COVAR_POP(a,b) filter (where a < 100), COVAR_POP(a,b) filter (where b <5) from t_2;
-----
-4.5 49.95
-
-query II
-select COVAR_POP(a,c) filter (where a < 100), COVAR_POP(a,c) filter (where c <50)
-from t_2
-group by b;
-----
-8.250000 8.250000
-674.916667 133.250000
-NULL 208.250000
-
-statement ok
-CREATE TABLE films(film_id INTEGER, title VARCHAR)
-
-statement ok
-CREATE TABLE actors(actor_id INTEGER, first_name VARCHAR, last_name VARCHAR)
-
-statement ok
-CREATE TABLE film_actor(film_id INTEGER, actor_id INTEGER)
-
-statement ok
-INSERT INTO films VALUES (1, 'The Martian'), (2, 'Saving Private Ryan'), (3, 'Team America');
-
-statement ok
-INSERT INTO actors VALUES (1, 'Matt', 'Damon'), (2, 'Jessica', 'Chastain'), (3, 'Tom', 'Hanks'), (4, 'Edward', 'Burns'),
- (5, 'Kim', 'Jong Un'), (6, 'Alec', 'Baldwin');
-
-statement ok
-INSERT INTO film_actor VALUES (1, 1), (2, 1), (3, 1), (1, 2), (2, 3), (2, 4), (3, 5), (3, 6);
-
-query II
-SELECT
- title,
- ARRAY_AGG (first_name || ' ' || last_name) filter (where first_name = 'Matt') actors
-FROM films
-JOIN film_actor USING (film_id)
-JOIN actors USING (actor_id)
-GROUP BY
- title
-ORDER BY
- title;
-----
-Saving Private Ryan [Matt Damon]
-Team America [Matt Damon]
-The Martian [Matt Damon]
-
-
-query II
-SELECT
- title,
- STRING_AGG (first_name || ' ' || last_name) filter (where first_name = 'Matt') actors
-FROM films
-JOIN film_actor USING (film_id)
-JOIN actors USING (actor_id)
-GROUP BY
- title
-ORDER BY
- title;
-----
-Saving Private Ryan Matt Damon
-Team America Matt Damon
-The Martian Matt Damon
-
-
#DISTINCT aggregates
statement ok
CREATE TABLE integers(i INTEGER)
@@ -541,3 +457,4 @@ query I
select sum(distinct i) filter (where i >1 and i < 5) from integers;
----
9
+
diff --git a/modules/calcite/src/test/sql/filter/test_filter_clause.test_ignore b/modules/calcite/src/test/sql/filter/test_filter_clause.test_ignore
index 58d2188..adbdd7d 100644
--- a/modules/calcite/src/test/sql/filter/test_filter_clause.test_ignore
+++ b/modules/calcite/src/test/sql/filter/test_filter_clause.test_ignore
@@ -1,14 +1,15 @@
# name: test/sql/filter/test_filter_clause.test
# description: Test aggregation with filter clause
# group: [filter]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15563
+# Ignore https://issues.apache.org/jira/browse/IGNITE-15589
statement ok
-PRAGMA enable_verification
-
-statement ok
-create temporary table t as select range i, mod(range,10) j,mod(range,10)*10000000 k from range(1000);
+create table t as select x i, mod(x,10) j,mod(x,10)*10000000 k from table(system_range(0, 999));
+query I
+SELECT count(*) from t;
+----
+1000
query II
SELECT
@@ -94,7 +95,7 @@ FROM t;
----
45 10
-query II
+query II rowsort
SELECT
sum(i) AS unfiltered,
sum(i) FILTER (WHERE i < 5) AS filtered
@@ -112,15 +113,14 @@ group by j;
50300 NULL
50400 NULL
-
-
-query III
+query III rowsort
SELECT
COUNT(*) AS unfiltered,
COUNT(*) FILTER (WHERE i > 5 and i < 10) AS filtered,
j
FROM t
-group by j;
+group by j
+order by j;
----
100 0 0
100 0 1
@@ -133,9 +133,7 @@ group by j;
100 1 8
100 1 9
-
-
-query II
+query II rowsort
SELECT
SUM(i) AS unfiltered,
SUM(i) FILTER (WHERE i between 5 and 10) AS filtered
@@ -153,10 +151,7 @@ group by j;
50300 8
50400 9
-
-
-
-query III
+query III rowsort
SELECT
SUM(i) AS unfiltered,
SUM(j) FILTER (WHERE j = 1) AS filtered,
@@ -165,7 +160,7 @@ FROM t
group by j;
----
49500 NULL 0
-49600 100 1
+49600 100 1
49700 NULL 2
49800 NULL 3
49900 NULL 4
@@ -175,8 +170,7 @@ group by j;
50300 NULL 8
50400 NULL 9
-
-query I
+query I rowsort
SELECT
SUM(j) FILTER (WHERE i < 10)
FROM t
@@ -193,7 +187,7 @@ group by j;
8
9
-query I
+query I rowsort
SELECT
SUM(j) FILTER (WHERE i < (select 10))
FROM t
@@ -242,7 +236,7 @@ limit 5;
45
# multiple filters
-query II
+query II rowsort
SELECT
SUM(j) FILTER (WHERE i < 10),
SUM(i) FILTER (WHERE i < 5)
@@ -260,8 +254,7 @@ group by j;
8 NULL
9 NULL
-
-query II
+query II rowsort
SELECT
sum(i) AS unfiltered,
sum(i) FILTER (WHERE i < 5) AS filtered
@@ -279,9 +272,7 @@ group by k;
50300 NULL
50400 NULL
-
-
-query II
+query II rowsort
SELECT
COUNT(*) AS unfiltered,
COUNT(*) FILTER (WHERE i > 5 and i < 10) AS filtered
@@ -299,9 +290,7 @@ group by k;
100 1
100 1
-
-
-query II
+query II rowsort
SELECT
SUM(i) AS unfiltered,
SUM(i) FILTER (WHERE i between 5 and 10) AS filtered
@@ -319,10 +308,7 @@ group by k;
50300 8
50400 9
-
-
-
-query III
+query III rowsort
SELECT
SUM(i) AS unfiltered,
SUM(j) FILTER (WHERE j = 1) AS filtered,
@@ -341,8 +327,7 @@ group by k;
50300 NULL 80000000
50400 NULL 90000000
-
-query I
+query I rowsort
SELECT
SUM(j) FILTER (WHERE i < 10)
FROM t
@@ -359,7 +344,7 @@ group by k;
8
9
-query I
+query I rowsort
SELECT
SUM(j) FILTER (WHERE i < (select 10))
FROM t
@@ -376,7 +361,7 @@ group by k;
8
9
-query I
+query I rowsort
SELECT
SUM(i) FILTER (WHERE i < (select i from t as t2 where t.i = t2.i))
FROM t
@@ -408,7 +393,7 @@ limit 5;
45
# multiple filters
-query II
+query II rowsort
SELECT
SUM(j) FILTER (WHERE i < 10),
SUM(i) FILTER (WHERE i < 5)
@@ -428,7 +413,7 @@ group by k;
# use correlated expression inside the filter itself
-query I
+query I rowsort
SELECT
(select sum(t2.i) FILTER (where t.i = t2.i) from t as t2)
FROM t
@@ -442,7 +427,7 @@ where i < 5
#Query with many different filter clauses (e.g. 5 aggregates, 5 different filters)
statement ok
-create temporary table t_2 as select range a, length(range) b, mod(range,100) c, 5 d, 10000 e from range(1000);
+create table t_2 as select x a, length(x) b, mod(x,100) c, 5 d, 10000 e from table(system_range(0, 999)) t1(x);
query IIIII
select count (a) filter (where a>10 and a < 15), count (b) filter (where b between 1 and 3),
diff --git a/modules/calcite/src/test/sql/filter/test_transitive_filters.test_ignore b/modules/calcite/src/test/sql/filter/test_transitive_filters.test
similarity index 78%
rename from modules/calcite/src/test/sql/filter/test_transitive_filters.test_ignore
rename to modules/calcite/src/test/sql/filter/test_transitive_filters.test
index 0a0f720..d09af92 100644
--- a/modules/calcite/src/test/sql/filter/test_transitive_filters.test_ignore
+++ b/modules/calcite/src/test/sql/filter/test_transitive_filters.test
@@ -1,17 +1,13 @@
# name: test/sql/filter/test_transitive_filters.test
# description: Test expressions with transitive filters
# group: [filter]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15563
-
-statement ok
-PRAGMA enable_verification
# Test various transitive filters with simple constant comparisons followed by non-scalar comparisons
# The main point here is to check that the optimizer handles all these cases correctly
# Inserting i, j = i
statement ok
-CREATE TABLE vals1 AS SELECT i AS i, i AS j FROM range(0, 11, 1) t1(i)
+CREATE TABLE vals1 AS SELECT i AS i, i AS j FROM table(system_range(0, 11, 1)) t1(i)
# Inserting i, j = i+1
statement ok
@@ -19,35 +15,39 @@ INSERT INTO vals1 SELECT i, i+1 FROM vals1
# Inserting i, j = i-1
statement ok
-INSERT INTO vals1 SELECT DISTINCT(i), i-1 FROM vals1 ORDER by i
+INSERT INTO vals1 SELECT DISTINCT(i), i-1 j FROM vals1 ORDER by i, j
### constant comparison [=, >, >=, <, <=] followed by j >= i #########################
-query II
+query II rowsort
SELECT * FROM vals1 WHERE i=5 AND j>=i
----
5 5
5 6
-query II
+query II rowsort
SELECT * FROM vals1 WHERE i>9 AND j>=i
----
10 10
10 11
+11 11
+11 12
-query II
+query II rowsort
SELECT * FROM vals1 WHERE i>=10 AND j>=i
----
10 10
10 11
+11 11
+11 12
-query II
+query II rowsort
SELECT * FROM vals1 WHERE i<1 AND j>=i
----
0 0
0 1
-query II
+query II rowsort
SELECT * FROM vals1 WHERE i<=0 AND j>=i
----
0 0
@@ -55,31 +55,35 @@ SELECT * FROM vals1 WHERE i<=0 AND j>=i
### constant comparison [=, >, >=, <, <=] followed by j <= i #########################
-query II
+query II rowsort
SELECT * FROM vals1 WHERE i=5 AND j<=i
----
-5 5
5 4
+5 5
-query II
+query II rowsort
SELECT * FROM vals1 WHERE i>9 AND j<=i
----
+11 11
+11 10
10 10
10 9
-query II
+query II rowsort
SELECT * FROM vals1 WHERE i>=10 AND j<=i
----
-10 10
10 9
+10 10
+11 10
+11 11
-query II
+query II rowsort
SELECT * FROM vals1 WHERE i<1 AND j<=i
----
0 0
0 -1
-query II
+query II rowsort
SELECT * FROM vals1 WHERE i<=0 AND j<=i
----
0 0
@@ -92,15 +96,17 @@ SELECT * FROM vals1 WHERE i=5 AND j>i
----
5 6
-query II
+query II rowsort
SELECT * FROM vals1 WHERE i>9 AND j>i
----
10 11
+11 12
-query II
+query II rowsort
SELECT * FROM vals1 WHERE i>=10 AND j>i
----
10 11
+11 12
query II
SELECT * FROM vals1 WHERE i<1 AND j>i
@@ -119,15 +125,17 @@ SELECT * FROM vals1 WHERE i=5 AND j<i
----
5 4
-query II
+query II rowsort
SELECT * FROM vals1 WHERE i>9 AND j<i
----
10 9
+11 10
-query II
+query II rowsort
SELECT * FROM vals1 WHERE i>=10 AND j<i
----
10 9
+11 10
query II
SELECT * FROM vals1 WHERE i<1 AND j<i
@@ -146,31 +154,35 @@ SELECT * FROM vals1 WHERE i<=0 AND j<i
### j >= i followed by constant comparison [=, >, >=, <, <=] #########################
-query II
+query II rowsort
SELECT * FROM vals1 WHERE j>=i AND i=5
----
5 5
5 6
-query II
+query II rowsort
SELECT * FROM vals1 WHERE j>=i AND i>9
----
10 10
10 11
+11 11
+11 12
-query II
+query II rowsort
SELECT * FROM vals1 WHERE j>=i AND i>=10
----
10 10
10 11
+11 11
+11 12
-query II
+query II rowsort
SELECT * FROM vals1 WHERE j>=i AND i<1
----
0 0
0 1
-query II
+query II rowsort
SELECT * FROM vals1 WHERE j>=i AND i<=0
----
0 0
@@ -178,35 +190,39 @@ SELECT * FROM vals1 WHERE j>=i AND i<=0
### j <= i followed by constant comparison [=, >, >=, <, <=] #########################
-query II
+query II rowsort
SELECT * FROM vals1 WHERE j<=i AND i=5
----
-5 5
5 4
+5 5
-query II
+query II rowsort
SELECT * FROM vals1 WHERE j<=i AND i>9
----
-10 10
10 9
+10 10
+11 10
+11 11
-query II
+query II rowsort
SELECT * FROM vals1 WHERE j<=i AND i>=10
----
-10 10
10 9
+10 10
+11 10
+11 11
-query II
+query II rowsort
SELECT * FROM vals1 WHERE j<=i AND i<1
----
0 0
0 -1
-query II
+query II rowsort
SELECT * FROM vals1 WHERE j<=i AND i<=0
----
-0 0
0 -1
+0 0
### j > i followed by constant comparison [=, >, >=, <, <=] #########################
@@ -215,15 +231,17 @@ SELECT * FROM vals1 WHERE j>i AND i=5
----
5 6
-query II
+query II rowsort
SELECT * FROM vals1 WHERE j>i AND i>9
----
10 11
+11 12
-query II
+query II rowsort
SELECT * FROM vals1 WHERE j>i AND i>=10
----
10 11
+11 12
query II
SELECT * FROM vals1 WHERE j>i AND i<1
@@ -242,15 +260,17 @@ SELECT * FROM vals1 WHERE j<i AND i=5
----
5 4
-query II
+query II rowsort
SELECT * FROM vals1 WHERE j<i AND i>9
----
10 9
+11 10
-query II
+query II rowsort
SELECT * FROM vals1 WHERE j<i AND i>=10
----
10 9
+11 10
query II
SELECT * FROM vals1 WHERE j<i AND i<1
@@ -269,28 +289,33 @@ CREATE TABLE vals2(k BIGINT, l BIGINT)
statement ok
INSERT INTO vals2 SELECT * FROM vals1
-query IIII
+query IIII rowsort
SELECT * FROM vals1, vals2 WHERE i>9 AND j<=l AND k>=i AND l<11
----
-10 10 10 10
-10 9 10 10
10 9 10 9
+10 9 10 10
+10 9 11 10
+10 10 10 10
+10 10 11 10
+11 10 11 10
+
query IIII
-SELECT * FROM vals1, vals2 WHERE i>9 AND j>=i AND k>=j ORDER by l
+SELECT * FROM vals1, vals2 WHERE i>9 AND j>=i AND k>=j ORDER BY vals1.j, vals1.i, vals2.k, vals2.l LIMIT 3
----
10 10 10 9
10 10 10 10
10 10 10 11
query IIII
-SELECT * FROM vals1, vals2 WHERE i>9 AND k>=j AND j>=i AND l>=k
+SELECT * FROM vals1, vals2 WHERE i>9 AND k>=j AND j>=i AND l>=k ORDER BY vals1.j, vals1.i, vals2.k, vals2.l LIMIT 3
----
10 10 10 10
10 10 10 11
+10 10 11 11
-query IIII
+query IIII rowsort
SELECT * FROM vals1, vals2 WHERE i<1 AND k<=j AND j<=i AND l<=k
----
-0 0 0 0
0 0 0 -1
+0 0 0 0
diff --git a/modules/calcite/src/test/sql/filter/test_zonemap.test b/modules/calcite/src/test/sql/filter/test_zonemap.test
new file mode 100644
index 0000000..d36a623
--- /dev/null
+++ b/modules/calcite/src/test/sql/filter/test_zonemap.test
@@ -0,0 +1,34 @@
+# name: test/sql/filter/test_zonemap.test_slow
+# description: Test expressions with transitive filters
+# group: [filter]
+
+
+statement ok
+create table t as select x a, length(x) b, mod(x,10000) c, 5 d, 10000 e from table(system_range(0,999));
+
+query I
+select count(*) from t where a > 500 or a <= 700
+----
+1000
+
+query I
+select count(*) from t where (a > 500 and b = 3) or (a > 7000 and b = 2)
+----
+499
+
+query I
+select count(*) from t where (a > 500 AND b = 3) OR (a >= 400) OR (a > 300 AND b=4) OR (a > 600 AND a > 300)
+----
+600
+
+query I
+select count(*) from t where (a > 500 AND b = 3) OR (c = 400) OR (a > 300 AND b=4) OR (a > 600 AND a > 300) or (d < 10)
+----
+1000
+
+query I
+select count(*) from t where (a > 500 AND b = 1) OR b < 2
+----
+10
+
+
diff --git a/modules/calcite/src/test/sql/filter/test_zonemap.test_slow_ignore b/modules/calcite/src/test/sql/filter/test_zonemap.test_slow_ignore
deleted file mode 100644
index 69a93bd..0000000
--- a/modules/calcite/src/test/sql/filter/test_zonemap.test_slow_ignore
+++ /dev/null
@@ -1,77 +0,0 @@
-# name: test/sql/filter/test_zonemap.test_slow
-# description: Test expressions with transitive filters
-# group: [filter]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15563
-
-# FIXME: temporarily removed because of too much memory usage on 32-bit
-# should be re-enabled when validity segments are no longer limited to 80 vectors
-mode skip
-
-statement ok
-PRAGMA explain_output = PHYSICAL_ONLY;
-
-statement ok
-create temporary table t as select range a, length(range) b, mod(range,10000) c, 5 d, 10000 e from range(100000000);
-
-query I
-select count(*) from t where a > 500 or a <= 700
-----
-100000000
-
-query II
-explain select count(*) from t where (a > 500 and b = 3) or (a > 7000 and b = 2)
-----
-physical_plan <REGEX>:.* Filters: .*b>=2.*b<=3.*
-
-query I
-select count(*) from t where (a > 500 and b = 3) or (a > 7000 and b = 2)
-----
-499
-
-query II
-explain select count(*) from t where (a > 500 AND b = 3) OR (a > 400) OR (a > 300 AND b=4) OR (a > 600 AND a > 300)
-----
-physical_plan <REGEX>:.* Filters: .*a>=300.*
-
-
-query I
-select count(*) from t where (a > 500 AND b = 3) OR (a > 400) OR (a > 300 AND b=4) OR (a > 600 AND a > 300)
-----
-99999599
-
-query II
-explain select count(*) from t where (a > 500 AND b = 3) OR (a > 400) OR (a > 300 AND b=4) OR (a > 600 AND a > 300)
-----
-physical_plan <REGEX>:.* Filters: .*a>=300.*
-
-
-query I
-select count(*) from t where (a > 500 AND b = 3) OR (a > 400) OR (a > 300 AND b=4) OR (a > 600 AND a > 300)
-----
-99999599
-
-
-query II
-explain select count(*) from t where (a > 500 AND b = 3) OR (c = 400) OR (a > 300 AND b=4) OR (a > 600 AND a > 300) or (d < 10)
-----
-physical_plan <!REGEX>:.* Filters: .*a>=300.*
-
-
-query I
-select count(*) from t where (a > 500 AND b = 3) OR (a > 400) OR (a > 300 AND b=4) OR (a > 600 AND a > 300)
-----
-99999599
-
-
-query II
-explain select count(*) from t where (a > 500 AND b = 1) OR b < 2
-----
-physical_plan <REGEX>:.* Filters: .*b<=2.*
-
-
-query I
-select count(*) from t where (a > 500 AND b = 1) OR b < 2
-----
-10
-
-
diff --git a/modules/calcite/src/test/sql/insert/null_values.test_ignore b/modules/calcite/src/test/sql/insert/null_values.test_ignore
deleted file mode 100644
index bd514dc..0000000
--- a/modules/calcite/src/test/sql/insert/null_values.test_ignore
+++ /dev/null
@@ -1,42 +0,0 @@
-# name: test/sql/insert/null_values.test
-# description: Test inserting many null values
-# group: [insert]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-14555
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15563
-
-statement ok
-CREATE TABLE integers(i INTEGER)
-
-statement ok
-INSERT INTO integers SELECT i FROM range(100) tbl(i);
-
-statement ok
-BEGIN TRANSACTION
-
-loop i 0 5
-
-statement ok
-INSERT INTO integers SELECT i FROM range(100) tbl(i);
-
-statement ok
-INSERT INTO integers SELECT NULL FROM range(100) tbl(i);
-
-endloop
-
-query IIIII
-SELECT COUNT(i), SUM(i), MIN(i), MAX(i), COUNT(*) FROM integers
-----
-600 29700 0 99 1100
-
-statement ok
-COMMIT
-
-query IIIII
-SELECT COUNT(i), SUM(i), MIN(i), MAX(i), COUNT(*) FROM integers
-----
-600 29700 0 99 1100
-
-query I
-SELECT SUM(CASE WHEN i IS NULL THEN 1 ELSE 0 END) FROM integers
-----
-500
diff --git a/modules/calcite/src/test/sql/join/full_outer/test_full_outer_join_many_matches.test_ignore b/modules/calcite/src/test/sql/join/full_outer/test_full_outer_join_many_matches.test
similarity index 55%
rename from modules/calcite/src/test/sql/join/full_outer/test_full_outer_join_many_matches.test_ignore
rename to modules/calcite/src/test/sql/join/full_outer/test_full_outer_join_many_matches.test
index 5fc60ae..5bf1e5e 100644
--- a/modules/calcite/src/test/sql/join/full_outer/test_full_outer_join_many_matches.test_ignore
+++ b/modules/calcite/src/test/sql/join/full_outer/test_full_outer_join_many_matches.test
@@ -1,25 +1,21 @@
# name: test/sql/join/full_outer/test_full_outer_join_many_matches.test
# description: Test FULL OUTER JOIN with many matches
# group: [full_outer]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15563
statement ok
-PRAGMA enable_verification
+CREATE TABLE integers AS SELECT a i, 1 j FROM table(system_range(0, 1999, 1)) t1(a)
statement ok
-CREATE TABLE integers AS SELECT a i, 1 j FROM range(0, 2000, 1) t1(a)
-
-statement ok
-CREATE TABLE integers2 AS SELECT a k, 2 l FROM range(2000, 4000, 1) t1(a)
+CREATE TABLE integers2 AS SELECT a k, 2 l FROM table(system_range(2000, 3999, 1)) t1(a)
# equality join
query IIII
SELECT i, j, k, l FROM integers FULL OUTER JOIN integers2 ON integers.i=integers2.k ORDER BY 1, 2, 3, 4
----
-16000 values hashing to 8b9eab043624ff470b00a981c1d588d9
+16000 values hashing to 7f3fd3a1cf91a34b461601ac6420e880
# range join
query IIII
SELECT i, j, k, l FROM integers FULL OUTER JOIN integers2 ON integers.j>integers2.l ORDER BY 1, 2, 3, 4
----
-16000 values hashing to 8b9eab043624ff470b00a981c1d588d9
+16000 values hashing to 7f3fd3a1cf91a34b461601ac6420e880
diff --git a/modules/calcite/src/test/sql/join/test_cross_product_parallelism.test_ignore b/modules/calcite/src/test/sql/join/test_cross_product_parallelism.test_ignore
index 36f206e..9eb1e7a 100644
--- a/modules/calcite/src/test/sql/join/test_cross_product_parallelism.test_ignore
+++ b/modules/calcite/src/test/sql/join/test_cross_product_parallelism.test_ignore
@@ -1,19 +1,9 @@
# name: test/sql/join/test_cross_product_parallelism.test_slow
# description: Test cross product with parallelism
# group: [join]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-14555
statement ok
-PRAGMA enable_verification
-
-statement ok
-PRAGMA threads=4
-
-statement ok
-PRAGMA force_parallelism
-
-statement ok
-CREATE TABLE integers AS SELECT * FROM range(0, 5000) tbl(i)
+CREATE TABLE integers AS SELECT * FROM system_range(0, 4999) tbl(i)
# test if order remains the same
query I
diff --git a/modules/calcite/src/test/sql/order/test_order_large.test_ignore b/modules/calcite/src/test/sql/order/test_order_large.test
similarity index 69%
rename from modules/calcite/src/test/sql/order/test_order_large.test_ignore
rename to modules/calcite/src/test/sql/order/test_order_large.test
index 31f9642..832299f 100644
--- a/modules/calcite/src/test/sql/order/test_order_large.test_ignore
+++ b/modules/calcite/src/test/sql/order/test_order_large.test
@@ -1,11 +1,10 @@
# name: test/sql/order/test_order_large.test
# description: Test ORDER BY with large table
# group: [order]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15563
# order of a large table, with burgers and fries
statement ok
-CREATE TABLE test AS SELECT a FROM range(10000, 0, -1) t1(a)
+CREATE TABLE test AS SELECT a FROM table(system_range(10000, 1, -1)) t1(a)
query I
SELECT * FROM test ORDER BY a
diff --git a/modules/calcite/src/test/sql/order/test_order_variable_size_payload.test_ignore b/modules/calcite/src/test/sql/order/test_order_variable_size_payload.test_ignore
index 6c66004..c5e7bcb 100644
--- a/modules/calcite/src/test/sql/order/test_order_variable_size_payload.test_ignore
+++ b/modules/calcite/src/test/sql/order/test_order_variable_size_payload.test_ignore
@@ -3,14 +3,10 @@
# group: [order]
# Ignore https://issues.apache.org/jira/browse/IGNITE-14636
# Ignore https://issues.apache.org/jira/browse/IGNITE-14555
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15563
-
-statement ok
-PRAGMA enable_verification
# payload string that is longer than the 8MB initial string size - this tests whether RowChunk dynamically increases block capacity
statement ok
-select range % 2 i, string_agg('thisstringis200characterslong..........................................................................................................................................................................') s from range(100000) group by i order by i
+select x % 2 i, string_agg('thisstringis200characterslong..........................................................................................................................................................................') s from table(system_range(0, 99999)) group by i order by i
# weird one from SQLancer
statement ok
diff --git a/modules/calcite/src/test/sql/types/date/test_bc_dates.test_ignore b/modules/calcite/src/test/sql/types/date/test_bc_dates.test_ignore
index 9df007a..be4ef8c 100644
--- a/modules/calcite/src/test/sql/types/date/test_bc_dates.test_ignore
+++ b/modules/calcite/src/test/sql/types/date/test_bc_dates.test_ignore
@@ -40,7 +40,7 @@ SELECT DATE '-1992-01-01'
# check intervals on negative dates
statement ok
-CREATE TABLE bc_dates AS SELECT date '0020-01-01' - interval (i) years AS d from range(0, 40) tbl(i);
+CREATE TABLE bc_dates AS SELECT date '0020-01-01' - interval (i) years AS d from system_range(0, 39) tbl(i);
query II
SELECT d, d::VARCHAR FROM bc_dates ORDER BY 1
diff --git a/modules/calcite/src/test/sql/types/list/list.test_slow_ignore b/modules/calcite/src/test/sql/types/list/list.test_slow_ignore
index 47b6e82..73530d1 100644
--- a/modules/calcite/src/test/sql/types/list/list.test_slow_ignore
+++ b/modules/calcite/src/test/sql/types/list/list.test_slow_ignore
@@ -1,10 +1,10 @@
# name: test/sql/types/list/list.test_slow
# description: Test big list
# group: [list]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15563
+# Ignore https://issues.apache.org/jira/browse/IGNITE-16417
statement ok
-CREATE TABLE test AS (SELECT range i, 0 as j FROM range(70000));
+CREATE TABLE test AS (SELECT x i, 0 as j FROM table(system_range(0, 69999)));
query I
SELECT list(i)[69999] FROM test GROUP BY j;
diff --git a/modules/calcite/src/test/sql/types/list/list_aggregates.test_ignore b/modules/calcite/src/test/sql/types/list/list_aggregates.test_ignore
index a5c72ff..7407b2e 100644
--- a/modules/calcite/src/test/sql/types/list/list_aggregates.test_ignore
+++ b/modules/calcite/src/test/sql/types/list/list_aggregates.test_ignore
@@ -1,29 +1,26 @@
# name: test/sql/types/list/list_aggregates.test
# description: Test lists with aggregations
# group: [list]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15563
-
-statement ok
-PRAGMA enable_verification
+# Ignore https://issues.apache.org/jira/browse/IGNITE-16417
query II
-select min(i::varchar), max(i::varchar) from range(10) tbl(i);
+select min(i::varchar), max(i::varchar) from table(system_range(0, 9)) tbl(i);
----
0 9
query II
-select min(list_value(i)), max(list_value(i)) from range(10) tbl(i);
+select min(list_value(i)), max(list_value(i)) from table(system_range(0, 9)) tbl(i);
----
[0] [9]
query I
-select string_agg(list_value(i), ',') from range(10) tbl(i);
+select string_agg(list_value(i), ',') from table(system_range(0, 9)) tbl(i);
----
[0],[1],[2],[3],[4],[5],[6],[7],[8],[9]
# window aggregate
query III
-select i, i % 2, min(list_value(i)) over(partition by i % 2 order by i) from range(10) tbl(i) ORDER BY 1;
+select i, i % 2, min(list_value(i)) over(partition by i % 2 order by i) from table(system_range(0, 9)) tbl(i) ORDER BY 1;
----
0 0 [0]
1 1 [1]
@@ -38,7 +35,7 @@ select i, i % 2, min(list_value(i)) over(partition by i % 2 order by i) from ran
# list
query III
-select i, i % 2, list(i) over(partition by i % 2 order by i) from range(10) tbl(i) ORDER BY 2, 1;
+select i, i % 2, list(i) over(partition by i % 2 order by i) from table(system_range(0, 9)) tbl(i) ORDER BY 2, 1;
----
0 0 [0]
2 0 [0, 2]
@@ -52,7 +49,7 @@ select i, i % 2, list(i) over(partition by i % 2 order by i) from range(10) tbl(
9 1 [1, 3, 5, 7, 9]
query III
-select i, i % 2, list(i) over(partition by i % 2 order by i rows between 1 preceding and 1 following) from range(10) tbl(i) ORDER BY 2, 1;
+select i, i % 2, list(i) over(partition by i % 2 order by i rows between 1 preceding and 1 following) from table(system_range(0, 9)) tbl(i) ORDER BY 2, 1;
----
0 0 [0, 2]
2 0 [0, 2, 4]