You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by jo...@apache.org on 2024/11/26 13:40:37 UTC
(ignite-3) branch main updated: IGNITE-23740: Sql. Missing rowsort comparison mode in test_filter_clause.test (#4772)
This is an automated email from the ASF dual-hosted git repository.
jooger pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new ee71697b6a IGNITE-23740: Sql. Missing rowsort comparison mode in test_filter_clause.test (#4772)
ee71697b6a is described below
commit ee71697b6a1493ab918027bf47d5a155704b876c
Author: Max Zhuravkov <sh...@gmail.com>
AuthorDate: Tue Nov 26 15:40:30 2024 +0200
IGNITE-23740: Sql. Missing rowsort comparison mode in test_filter_clause.test (#4772)
---
.../aggregate/aggregates/test_covar.test_ignore | 40 ++
.../test_string_agg_array_agg.test_ignore | 56 +++
.../sql/filter/test_filter_clause.test | 2 +-
.../sql/filter/test_filter_clause.test_ignore | 534 ---------------------
4 files changed, 97 insertions(+), 535 deletions(-)
diff --git a/modules/sql-engine/src/integrationTest/sql/aggregate/aggregates/test_covar.test_ignore b/modules/sql-engine/src/integrationTest/sql/aggregate/aggregates/test_covar.test_ignore
index 13323c3cea..7f8638d156 100644
--- a/modules/sql-engine/src/integrationTest/sql/aggregate/aggregates/test_covar.test_ignore
+++ b/modules/sql-engine/src/integrationTest/sql/aggregate/aggregates/test_covar.test_ignore
@@ -99,3 +99,43 @@ SELECT COVAR_POP(NULL, NULL), COVAR_SAMP(NULL, NULL) FROM integers
NULL
NULL
+#Query with many different filter clauses (e.g. 5 aggregates, 5 different filters)
+statement ok
+create table t_2(a int, b int, c int, d int, e int);
+
+statement ok
+insert into t_2 select x a, length(x) b, mod(x,100) c, 5 d, 10000 e from table(system_range(0, 999));
+
+query IIIII
+select count (a) filter (where a>10 and a < 15), count (b) filter (where b between 1 and 3),
+ count (c) filter ( where c < 10), count (d) filter (where d =5), count(e) filter (where e < 10)
+ from t_2;
+----
+4 1000 100 1000 0
+
+query IIIII
+select count (a) filter (where a>10 and a < 15), count (b) filter (where b between 1 and 3),
+ count (c) filter ( where c < 10), count (d) filter (where d =5), count(e) filter (where e < 10)
+ from t_2
+ group by b;
+----
+0 10 10 10 0
+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
+
diff --git a/modules/sql-engine/src/integrationTest/sql/aggregate/aggregates/test_string_agg_array_agg.test_ignore b/modules/sql-engine/src/integrationTest/sql/aggregate/aggregates/test_string_agg_array_agg.test_ignore
new file mode 100644
index 0000000000..8f5ab1ec6a
--- /dev/null
+++ b/modules/sql-engine/src/integrationTest/sql/aggregate/aggregates/test_string_agg_array_agg.test_ignore
@@ -0,0 +1,56 @@
+# name: test/sql/filter/test_string_agg_array_agg.test
+# description: Test STRING_AGG, ARRAY_AGG
+# group: [aggregates]
+# Ignore: https://issues.apache.org/jira/browse/IGNITE-15589
+
+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
diff --git a/modules/sql-engine/src/integrationTest/sql/filter/test_filter_clause.test b/modules/sql-engine/src/integrationTest/sql/filter/test_filter_clause.test
index c56c1bec6f..89a808abb7 100644
--- a/modules/sql-engine/src/integrationTest/sql/filter/test_filter_clause.test
+++ b/modules/sql-engine/src/integrationTest/sql/filter/test_filter_clause.test
@@ -441,7 +441,7 @@ select count (a) filter (where a>10 and a < 15), count (b) filter (where b betwe
----
4 1000 100 1000 0
-query IIIII
+query IIIII rowsort
select count (a) filter (where a>10 and a < 15), count (b) filter (where b between 1 and 3),
count (c) filter ( where c < 10), count (d) filter (where d =5), count(e) filter (where e < 10)
from t_2
diff --git a/modules/sql-engine/src/integrationTest/sql/filter/test_filter_clause.test_ignore b/modules/sql-engine/src/integrationTest/sql/filter/test_filter_clause.test_ignore
deleted file mode 100644
index f526e792ba..0000000000
--- a/modules/sql-engine/src/integrationTest/sql/filter/test_filter_clause.test_ignore
+++ /dev/null
@@ -1,534 +0,0 @@
-# name: test/sql/filter/test_filter_clause.test
-# description: Test aggregation with filter clause
-# group: [filter]
-# Ignore: https://issues.apache.org/jira/browse/IGNITE-15589
-
-statement ok
-create table t(i int, j int, k int);
-
-statement ok
-insert into t 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
- COUNT(*) AS unfiltered,
- COUNT(*) FILTER (WHERE i < 5) AS filtered
-FROM t;
-----
-1000 5
-
-query II
-SELECT
- COUNT(*) AS unfiltered,
- COUNT(*) FILTER (WHERE i > 5 and i < 10) AS filtered
-FROM t;
-----
-1000 4
-
-query II
-SELECT
- SUM(i) AS unfiltered,
- SUM(i) FILTER (WHERE i < 5) AS filtered
-FROM t;
-----
-499500 10
-
-query II
-SELECT
- SUM(i) AS unfiltered,
- SUM(i) FILTER (WHERE i between 5 and 10) AS filtered
-FROM t;
-----
-499500 45
-
-query II
-SELECT
- SUM(i) AS unfiltered,
- SUM(j) FILTER (WHERE j < 2) AS filtered
-FROM t;
-----
-499500 100
-
-
-query I
-SELECT
- SUM(j) FILTER (WHERE i < 10)
-FROM t;
-----
-45
-
-query I
-SELECT
- SUM(j) FILTER (WHERE i < (select 10))
-FROM t;
-----
-45
-
-query I
-SELECT
- SUM(i) FILTER (WHERE i < (select i from t as t2 where t.i = t2.i))
-FROM t;
-----
-NULL
-
-# use it inside subquery
-query I
-SELECT
- (select sum(t2.i) FILTER (where t2.i < 10) from t as t2)
-FROM t
-limit 5;
-----
-45
-45
-45
-45
-45
-
-# multiple filters
-query II
-SELECT
- SUM(j) FILTER (WHERE i < 10),
- SUM(i) FILTER (WHERE i < 5)
-FROM t;
-----
-45 10
-
-query II rowsort
- SELECT
- sum(i) AS unfiltered,
- sum(i) FILTER (WHERE i < 5) AS filtered
-FROM t
-group by j;
-----
-49500 0
-49600 1
-49700 2
-49800 3
-49900 4
-50000 NULL
-50100 NULL
-50200 NULL
-50300 NULL
-50400 NULL
-
-query III rowsort
- SELECT
- COUNT(*) AS unfiltered,
- COUNT(*) FILTER (WHERE i > 5 and i < 10) AS filtered,
- j
-FROM t
-group by j
-order by j;
-----
-100 0 0
-100 0 1
-100 0 2
-100 0 3
-100 0 4
-100 0 5
-100 1 6
-100 1 7
-100 1 8
-100 1 9
-
-query II rowsort
-SELECT
- SUM(i) AS unfiltered,
- SUM(i) FILTER (WHERE i between 5 and 10) AS filtered
-FROM t
-group by j;
-----
-49500 10
-49600 NULL
-49700 NULL
-49800 NULL
-49900 NULL
-50000 5
-50100 6
-50200 7
-50300 8
-50400 9
-
-query III rowsort
-SELECT
- SUM(i) AS unfiltered,
- SUM(j) FILTER (WHERE j = 1) AS filtered,
- j
-FROM t
-group by j;
-----
-49500 NULL 0
-49600 100 1
-49700 NULL 2
-49800 NULL 3
-49900 NULL 4
-50000 NULL 5
-50100 NULL 6
-50200 NULL 7
-50300 NULL 8
-50400 NULL 9
-
-query I rowsort
-SELECT
- SUM(j) FILTER (WHERE i < 10)
-FROM t
-group by j;
-----
-0
-1
-2
-3
-4
-5
-6
-7
-8
-9
-
-query I rowsort
-SELECT
- SUM(j) FILTER (WHERE i < (select 10))
-FROM t
-group by j;
-----
-0
-1
-2
-3
-4
-5
-6
-7
-8
-9
-
-query I
-SELECT
- SUM(i) FILTER (WHERE i < (select i from t as t2 where t.i = t2.i))
-FROM t
-group by j;
-----
-NULL
-NULL
-NULL
-NULL
-NULL
-NULL
-NULL
-NULL
-NULL
-NULL
-
-# use it inside subquery
-query I
- SELECT
- (select sum(t2.i) FILTER (where t2.i < 10) from t as t2)
-FROM t
-group by j
-limit 5;
-----
-45
-45
-45
-45
-45
-
-# multiple filters
-query II rowsort
-SELECT
- SUM(j) FILTER (WHERE i < 10),
- SUM(i) FILTER (WHERE i < 5)
-FROM t
-group by j;
-----
-0 0
-1 1
-2 2
-3 3
-4 4
-5 NULL
-6 NULL
-7 NULL
-8 NULL
-9 NULL
-
-query II rowsort
- SELECT
- sum(i) AS unfiltered,
- sum(i) FILTER (WHERE i < 5) AS filtered
-FROM t
-group by k;
-----
-49500 0
-49600 1
-49700 2
-49800 3
-49900 4
-50000 NULL
-50100 NULL
-50200 NULL
-50300 NULL
-50400 NULL
-
-query II rowsort
- SELECT
- COUNT(*) AS unfiltered,
- COUNT(*) FILTER (WHERE i > 5 and i < 10) AS filtered
-FROM t
-group by k;
-----
-100 0
-100 0
-100 0
-100 0
-100 0
-100 0
-100 1
-100 1
-100 1
-100 1
-
-query II rowsort
-SELECT
- SUM(i) AS unfiltered,
- SUM(i) FILTER (WHERE i between 5 and 10) AS filtered
-FROM t
-group by k;
-----
-49500 10
-49600 NULL
-49700 NULL
-49800 NULL
-49900 NULL
-50000 5
-50100 6
-50200 7
-50300 8
-50400 9
-
-query III rowsort
-SELECT
- SUM(i) AS unfiltered,
- SUM(j) FILTER (WHERE j = 1) AS filtered,
- k
-FROM t
-group by k;
-----
-49500 NULL 0
-49600 100 10000000
-49700 NULL 20000000
-49800 NULL 30000000
-49900 NULL 40000000
-50000 NULL 50000000
-50100 NULL 60000000
-50200 NULL 70000000
-50300 NULL 80000000
-50400 NULL 90000000
-
-query I rowsort
-SELECT
- SUM(j) FILTER (WHERE i < 10)
-FROM t
-group by k;
-----
-0
-1
-2
-3
-4
-5
-6
-7
-8
-9
-
-query I rowsort
-SELECT
- SUM(j) FILTER (WHERE i < (select 10))
-FROM t
-group by k;
-----
-0
-1
-2
-3
-4
-5
-6
-7
-8
-9
-
-query I rowsort
-SELECT
- SUM(i) FILTER (WHERE i < (select i from t as t2 where t.i = t2.i))
-FROM t
-group by k;
-----
-NULL
-NULL
-NULL
-NULL
-NULL
-NULL
-NULL
-NULL
-NULL
-NULL
-
-# use it inside subquery
-query I
- SELECT
- (select sum(t2.i) FILTER (where t2.i < 10) from t as t2)
-FROM t
-group by k
-limit 5;
-----
-45
-45
-45
-45
-45
-
-# multiple filters
-query II rowsort
-SELECT
- SUM(j) FILTER (WHERE i < 10),
- SUM(i) FILTER (WHERE i < 5)
-FROM t
-group by k;
-----
-0 0
-1 1
-2 2
-3 3
-4 4
-5 NULL
-6 NULL
-7 NULL
-8 NULL
-9 NULL
-
-
-# use correlated expression inside the filter itself
-query I rowsort
- SELECT
- (select sum(t2.i) FILTER (where t.i = t2.i) from t as t2)
-FROM t
-where i < 5
-----
-0
-1
-2
-3
-4
-
-#Query with many different filter clauses (e.g. 5 aggregates, 5 different filters)
-statement ok
-create table t_2(a int, b int, c int, d int, e int);
-
-statement ok
-insert into t_2 select x a, length(x) b, mod(x,100) c, 5 d, 10000 e from table(system_range(0, 999));
-
-query IIIII
-select count (a) filter (where a>10 and a < 15), count (b) filter (where b between 1 and 3),
- count (c) filter ( where c < 10), count (d) filter (where d =5), count(e) filter (where e < 10)
- from t_2;
-----
-4 1000 100 1000 0
-
-query IIIII
-select count (a) filter (where a>10 and a < 15), count (b) filter (where b between 1 and 3),
- count (c) filter ( where c < 10), count (d) filter (where d =5), count(e) filter (where e < 10)
- from t_2
- group by b;
-----
-0 10 10 10 0
-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)
-
-statement ok
-insert into integers values (1),(1),(2),(2),(3),(4),(8);
-
-query I
-select sum(distinct i) filter (where i >1 and i < 5) from integers;
-----
-9