alex-plekhanov commented on a change in pull request #9774: URL: https://github.com/apache/ignite/pull/9774#discussion_r795514346
########## File path: 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(1, 70000))); Review comment: I think `range(70000)` is equivalent to `table(system_range(0, 69999)` ########## File path: modules/calcite/src/test/sql/filter/test_filter_clause.test ########## @@ -0,0 +1,448 @@ +# name: test/sql/filter/test_filter_clause.test +# description: Test aggregation with filter clause +# group: [filter] + +statement ok +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 + 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 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), + 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 + Review comment: Looks like query `DISTINCT aggregates` can be added to `test` file from `test_ignored` too ########## File path: modules/calcite/src/test/sql/order/test_order_large.test_ignore ########## @@ -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 Review comment: Why file is still ignored? Looks like it working now ########## File path: modules/calcite/src/test/sql/join/full_outer/test_full_outer_join_many_matches.test_ignore ########## @@ -1,16 +1,12 @@ # 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(1, 2000, 1)) t1(a) Review comment: To make the test work we should replace ranges to 0-1999 and 2000-3999, also we hash null values as `null`, but here assumed that null values hashed as `NULL`, so we should change hashes in this test and unignore it, or fix hashing of null values and check that other test works too. ########## File path: 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, 10)) tbl(i); Review comment: The same here and below, `range(10)` is equivalent to `table(system_range(0, 9)` ########## File path: modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/SqlScriptRunner.java ########## @@ -696,6 +698,7 @@ private void checkResultsHashed(List<List<?>> res) { messageDigest.update(SqlScriptRunner.toString(col).getBytes(Charset.forName(UTF_8.name()))); messageDigest.update(NL_BYTES); } + Review comment: Redundant -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
