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/08/02 12:11:28 UTC
(ignite-3) branch main updated: IGNITE-22706 Sql. Unignore sql test related to group by alias (#4167)
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 39bbc6603a IGNITE-22706 Sql. Unignore sql test related to group by alias (#4167)
39bbc6603a is described below
commit 39bbc6603ab9f2bab312571f1321184c02359851
Author: ygerzhedovich <41...@users.noreply.github.com>
AuthorDate: Fri Aug 2 15:11:23 2024 +0300
IGNITE-22706 Sql. Unignore sql test related to group by alias (#4167)
---
.../aggregates/test_perfect_ht.test_ignore_old | 175 -------------------
.../sql/aggregate/group/test_group_by.test | 40 +++++
.../sql/aggregate/group/test_group_by.test_ignore | 190 ---------------------
.../sql/aggregate/group/test_group_by_alias.test | 45 ++++-
.../group/test_group_by_alias.test_ignore | 68 --------
.../sql/engine/sql/IgniteSqlConformance.java | 6 +
6 files changed, 88 insertions(+), 436 deletions(-)
diff --git a/modules/sql-engine/src/integrationTest/sql/aggregate/aggregates/test_perfect_ht.test_ignore_old b/modules/sql-engine/src/integrationTest/sql/aggregate/aggregates/test_perfect_ht.test_ignore_old
deleted file mode 100644
index 7460bd9c71..0000000000
--- a/modules/sql-engine/src/integrationTest/sql/aggregate/aggregates/test_perfect_ht.test_ignore_old
+++ /dev/null
@@ -1,175 +0,0 @@
-# name: test/sql/aggregate/aggregates/test_perfect_ht.test
-# description: Test aggregates that can trigger a perfect HT
-# group: [aggregates]
-# Ignored: https://issues.apache.org/jira/browse/IGNITE-14885
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-PRAGMA perfect_ht_threshold=20;
-
-statement ok
-CREATE TABLE timeseries(year INTEGER, val INTEGER);
-
-statement ok
-INSERT INTO timeseries VALUES (1996, 10), (1997, 12), (1996, 20), (2001, 30), (NULL, 1), (1996, NULL);
-
-query IIII
-SELECT year, SUM(val), COUNT(val), COUNT(*) FROM timeseries GROUP BY year ORDER BY year;
-----
-NULL 1 1 1
-1996 30 2 3
-1997 12 1 1
-2001 30 1 1
-
-# use aggregates with destructors
-query III
-SELECT year, LIST(val), STRING_AGG(val, ',') FROM timeseries GROUP BY year ORDER BY year;
-----
-NULL [1] 1
-1996 [10, 20, NULL] 10,20
-1997 [12] 12
-2001 [30] 30
-
-# many small columns each having only the values 0 and 1
-# total possible combinations is 2^10, but there are only 2 groups
-statement ok
-create table manycolumns as select x a, x b, x c, x d, x e from table(system_range(0,1));
-
-query IIIII
-select a, b, c, d, e FROM manycolumns GROUP BY a, b, c, d, e ORDER BY 1
-----
-0 0 0 0 0
-1 1 1 1 1
-
-# test edge cases: multiple tinyints without statistics
-# create a table of tinyints [-127, 127] stored as varchar
-# by forcing a varchar to tinyint cast we lose statistics
-statement ok
-CREATE TABLE tinyints AS SELECT x::TINYINT::VARCHAR AS t FROM table(system_range(-127, 127));
-
-query IIII
-SELECT COUNT(DISTINCT i), MIN(i), MAX(i), SUM(i) / COUNT(i) FROM (SELECT t::TINYINT i FROM tinyints GROUP BY t)
-----
-255 -127 127 0
-
-# now do the same with a single smallint column
-statement ok
-CREATE TABLE smallints AS SELECT x::SMALLINT::VARCHAR AS t FROM table(system_range(-32767, 32767));
-
-query IIII
-SELECT COUNT(DISTINCT i), MIN(i), MAX(i), SUM(i) / COUNT(i) FROM (SELECT t::SMALLINT i FROM smallints GROUP BY t)
-----
-65535 -32767 32767 0
-
-# test result ordering of perfect HT
-statement ok
-PRAGMA disable_verification
-
-statement ok
-create table dates as select date '1992-01-01' + concat(x, ' months')::interval as d from table(system_range(0, 99));
-
-query II
-select extract(year from d), extract(month from d) from dates group by 1, 2 order by 1, 2;
-----
-1992 1
-1992 2
-1992 3
-1992 4
-1992 5
-1992 6
-1992 7
-1992 8
-1992 9
-1992 10
-1992 11
-1992 12
-1993 1
-1993 2
-1993 3
-1993 4
-1993 5
-1993 6
-1993 7
-1993 8
-1993 9
-1993 10
-1993 11
-1993 12
-1994 1
-1994 2
-1994 3
-1994 4
-1994 5
-1994 6
-1994 7
-1994 8
-1994 9
-1994 10
-1994 11
-1994 12
-1995 1
-1995 2
-1995 3
-1995 4
-1995 5
-1995 6
-1995 7
-1995 8
-1995 9
-1995 10
-1995 11
-1995 12
-1996 1
-1996 2
-1996 3
-1996 4
-1996 5
-1996 6
-1996 7
-1996 8
-1996 9
-1996 10
-1996 11
-1996 12
-1997 1
-1997 2
-1997 3
-1997 4
-1997 5
-1997 6
-1997 7
-1997 8
-1997 9
-1997 10
-1997 11
-1997 12
-1998 1
-1998 2
-1998 3
-1998 4
-1998 5
-1998 6
-1998 7
-1998 8
-1998 9
-1998 10
-1998 11
-1998 12
-1999 1
-1999 2
-1999 3
-1999 4
-1999 5
-1999 6
-1999 7
-1999 8
-1999 9
-1999 10
-1999 11
-1999 12
-2000 1
-2000 2
-2000 3
-2000 4
diff --git a/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by.test b/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by.test
index 4f48454bf9..0846ec853a 100644
--- a/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by.test
+++ b/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by.test
@@ -67,6 +67,13 @@ SELECT b, SUM(a), COUNT(*), SUM(a+2) FROM test GROUP BY b ORDER BY b;
21 12.000000 1 14.000000
22 24.000000 2 28.000000
+# group by alias
+query IR
+SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY f;
+----
+0 24.000000
+1 12.000000
+
# group by with filter
query IRIR
SELECT b, SUM(a), COUNT(*), SUM(a+2) FROM test WHERE a <= 12 GROUP BY b ORDER BY b;
@@ -113,6 +120,28 @@ SELECT i, SUM(j), ANY_VALUE(j) FROM integers GROUP BY i ORDER BY i
2 4.000000 4
3 8.000000 4
+# group by constant alias
+query IR
+SELECT 1 AS k, SUM(i) FROM integers GROUP BY k ORDER BY 2;
+----
+1 8.000000
+
+# use an alias that is identical to a column name (should prioritize column name)
+skipif ignite3
+# https://issues.apache.org/jira/browse/IGNITE-18412
+query IR
+SELECT 1 AS i, SUM(i) FROM integers GROUP BY i ORDER BY 2;
+----
+1 2.000000
+1 6.000000
+
+# refer to the same alias twice
+query IR
+SELECT i % 2 AS k, SUM(i) FROM integers GROUP BY k, k ORDER BY 1;
+----
+0 2.000000
+1 6.000000
+
statement ok
DROP TABLE integers;
@@ -131,6 +160,17 @@ NULL NULL
2 2.000000
3 3.000000
+# column reference should have preference over alias reference in grouping
+skipif ignite3
+# https://issues.apache.org/jira/browse/IGNITE-18412
+query IIR
+SELECT i, i % 2 AS i, SUM(i) FROM integers GROUP BY i ORDER BY 1 NULLS FIRST;
+----
+NULL NULL NULL
+1 1 1.000000
+2 0 2.000000
+3 1 3.000000
+
# aliases can be referenced in group by <columns> clause.
query II
SELECT 1 AS k, SUM(i) FROM integers GROUP BY k+1 ORDER BY 2
diff --git a/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by.test_ignore b/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by.test_ignore
deleted file mode 100644
index f042382bb9..0000000000
--- a/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by.test_ignore
+++ /dev/null
@@ -1,190 +0,0 @@
-# name: test/sql/aggregate/group/test_group_by.test
-# description: Test aggregation/group by statements
-# group: [group]
-# Ignored: https://issues.apache.org/jira/browse/IGNITE-14885
-# Ignored: https://issues.apache.org/jira/browse/IGNITE-18412
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE test (a INTEGER, b INTEGER);
-
-statement ok
-INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)
-
-# aggregates cannot be nested
-statement error
-SELECT SUM(SUM(41)), COUNT(*);
-
-# simple aggregates without group by
-query RIR
-SELECT SUM(a), COUNT(*), AVG(a) FROM test;
-----
-36.000000 3 12.000000
-
-query I
-SELECT COUNT(*) FROM test;
-----
-3
-
-query RI
-SELECT SUM(a), COUNT(*) FROM test WHERE a = 11;
-----
-11.000000 1
-
-query RRR
-SELECT SUM(a), SUM(b), SUM(a) + SUM (b) FROM test;
-----
-36.000000 65.000000 101.000000
-
-query RR
-SELECT SUM(a+2), SUM(a) + 2 * COUNT(*) FROM test;
-----
-42.000000 42.000000
-
-# aggregations with group by
-query IRRR
-SELECT b, SUM(a), SUM(a+2), AVG(a) FROM test GROUP BY b ORDER BY b;
-----
-21 12.000000 14.000000 12.000000
-22 24.000000 28.000000 12.000000
-
-# ORDER BY aggregation that does not occur in SELECT clause
-query IR
-SELECT b, SUM(a) FROM test GROUP BY b ORDER BY COUNT(a);
-----
-21 12.000000
-22 24.000000
-
-query IR
-SELECT b, SUM(a) FROM test GROUP BY b ORDER BY COUNT(a) DESC;
-----
-22 24.000000
-21 12.000000
-
-query IRIR
-SELECT b, SUM(a), COUNT(*), SUM(a+2) FROM test GROUP BY b ORDER BY b;
-----
-21 12.000000 1 14.000000
-22 24.000000 2 28.000000
-
-# group by alias
-query IR
-SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f;
-----
-0 24.000000
-1 12.000000
-
-# group by with filter
-query IRIR
-SELECT b, SUM(a), COUNT(*), SUM(a+2) FROM test WHERE a <= 12 GROUP BY b ORDER BY b;
-----
-21 12.000000 1 14.000000
-22 11.000000 1 13.000000
-
-# nested aggregate in group by
-statement error
-SELECT b % 2 AS f, COUNT(SUM(a)) FROM test GROUP BY f;
-
-statement ok
-INSERT INTO test VALUES (12, 21), (12, 21), (12, 21)
-
-# group by with filter and multiple values per groups
-query IRIR
-SELECT b, SUM(a), COUNT(*), SUM(a+2) FROM test WHERE a <= 12 GROUP BY b ORDER BY b;
-----
-21 48.000000 4 56.000000
-22 11.000000 1 13.000000
-
-# group by with filter and multiple values per groups
-statement ok
-CREATE TABLE integers(i INTEGER, j INTEGER);
-
-statement ok
-INSERT INTO integers VALUES (3, 4), (3, 4), (2, 4);
-
-# use GROUP BY column in math operator
-query II
-SELECT i, i + 10 FROM integers GROUP BY i ORDER BY i
-----
-2 12
-3 13
-
-# using non-group column and non-aggregate should throw an error
-statement error
-SELECT i, SUM(j), j FROM integers GROUP BY i ORDER BY i
-
-# but it works if we wrap it in ANY_VALUE()
-query IRI
-SELECT i, SUM(j), ANY_VALUE(j) FROM integers GROUP BY i ORDER BY i
-----
-2 4.000000 4
-3 8.000000 4
-
-# group by constant alias
-query IR
-SELECT 1 AS k, SUM(i) FROM integers GROUP BY k ORDER BY 2;
-----
-1 8.000000
-
-# use an alias that is identical to a column name (should prioritize column name)
-query IR
-SELECT 1 AS i, SUM(i) FROM integers GROUP BY i ORDER BY 2;
-----
-1 2.000000
-1 6.000000
-
-# refer to the same alias twice
-query IR
-SELECT i % 2 AS k, SUM(i) FROM integers GROUP BY k, k ORDER BY 1;
-----
-0 2.000000
-1 6.000000
-
-statement ok
-DROP TABLE integers;
-
-statement ok
-CREATE TABLE integers(i INTEGER);
-
-statement ok
-INSERT INTO integers VALUES (1), (2), (3), (NULL);
-
-# group by NULL
-query IR
-SELECT i, SUM(i) FROM integers GROUP BY i ORDER BY 1 NULLS FIRST;
-----
-NULL NULL
-1 1.000000
-2 2.000000
-3 3.000000
-
-# column reference should have preference over alias reference in grouping
-query IIR
-SELECT i, i % 2 AS i, SUM(i) FROM integers GROUP BY i ORDER BY 1 NULLS FIRST;
-----
-NULL NULL NULL
-1 1 1.000000
-2 0 2.000000
-3 1 3.000000
-
-# aliases can be referenced in group by <columns> clause.
-query II
-SELECT 1 AS k, SUM(i) FROM integers GROUP BY k+1 ORDER BY 2
-----
-1 6
-
-# aliases can be referenced in group by <grouping sets> clause.
-query II
-SELECT 1 AS k, SUM(i) FROM integers GROUP BY GROUPING SETS ((k+1)) ORDER BY 2
-----
-1 6
-
-# group by column refs should be recognized, even if one uses an explicit table specifier and the other does not
-query II
-SELECT test.b, SUM(a) FROM test GROUP BY b ORDER BY COUNT(a) DESC;
-----
-21 48
-22 24
-
diff --git a/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by_alias.test b/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by_alias.test
index f7b9c53fb5..50617d631e 100644
--- a/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by_alias.test
+++ b/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by_alias.test
@@ -8,12 +8,50 @@ CREATE TABLE integers(i INTEGER)
statement ok
INSERT INTO integers VALUES (1), (2), (3), (NULL)
-# this now orders by the actual grouping column
+# use alias in HAVING clause
+# CONTROVERSIAL: this query DOES NOT work in PostgreSQL
+query IR
+SELECT i % 2 AS k, SUM(i) FROM integers WHERE i IS NOT NULL GROUP BY k HAVING k>0;
+----
+1 4.000000
+
+# this is identical to this query
+# CONTROVERSIAL: this query does not work in MonetDB
+query IR
+SELECT i % 2 AS k, SUM(i) FROM integers WHERE i IS NOT NULL GROUP BY k HAVING i%2>0;
+----
+1 4.000000
+
+# select groups by constant (similar to order by constant)
+query IR
+SELECT i % 2 AS k, SUM(i) FROM integers WHERE i IS NOT NULL GROUP BY 1 HAVING i%2>0;
+----
+1 4.000000
+
+# constant out of range
+statement error
+SELECT i % 2 AS k, SUM(i) FROM integers WHERE i IS NOT NULL GROUP BY 42 HAVING i%2>0;
+
+# entry in GROUP BY should refer to base column
+# ...BUT the alias in ORDER BY should refer to the alias from the select list
+# note that both Postgres and MonetDB reject this query because of ambiguity. SQLite accepts it though so we do
+# too.
+skipif ignite3
+# https://issues.apache.org/jira/browse/IGNITE-18412
query IIR
-SELECT i, i % 2 AS k, SUM(i) FROM integers GROUP BY i ORDER BY i;
+SELECT i, i % 2 AS i, SUM(i) FROM integers GROUP BY i ORDER BY i, 3;
----
+NULL NULL NULL
+2 0 2.000000
1 1 1.000000
+3 1 3.000000
+
+# changing the name of the alias makes it more explicit what should happen
+query IIR
+SELECT i, i % 2 AS k, SUM(i) FROM integers GROUP BY i ORDER BY k, 3;
+----
2 0 2.000000
+1 1 1.000000
3 1 3.000000
NULL NULL NULL
@@ -35,4 +73,5 @@ NULL NULL
# but SQLite resolves it by first pushing a "FIRST(i)" aggregate into the projection, and then ordering by that
# aggregate
statement error
-SELECT (10-i) AS k, SUM(i) FROM integers GROUP BY k ORDER BY i;
+SELECT (10-i) AS k, SUM(i) FROM integers GROUP BY k ORDER BY FIRST(i);
+
diff --git a/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by_alias.test_ignore b/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by_alias.test_ignore
deleted file mode 100644
index 846468d889..0000000000
--- a/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by_alias.test_ignore
+++ /dev/null
@@ -1,68 +0,0 @@
-# name: test/sql/aggregate/group/test_group_by_alias.test
-# description: Test aliases in group by/aggregation
-# group: [group]
-# Ignored: https://issues.apache.org/jira/browse/IGNITE-14885
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE integers(i INTEGER)
-
-statement ok
-INSERT INTO integers VALUES (1), (2), (3), (NULL)
-
-# use alias in HAVING clause
-# CONTROVERSIAL: this query DOES NOT work in PostgreSQL
-query IR
-SELECT i % 2 AS k, SUM(i) FROM integers WHERE i IS NOT NULL GROUP BY k HAVING k>0;
-----
-1 4.000000
-
-# this is identical to this query
-# CONTROVERSIAL: this query does not work in MonetDB
-query IR
-SELECT i % 2 AS k, SUM(i) FROM integers WHERE i IS NOT NULL GROUP BY k HAVING i%2>0;
-----
-1 4.000000
-
-# select groups by constant (similar to order by constant)
-query IR
-SELECT i % 2 AS k, SUM(i) FROM integers WHERE i IS NOT NULL GROUP BY 1 HAVING i%2>0;
-----
-1 4.000000
-
-# constant out of range
-statement error
-SELECT i % 2 AS k, SUM(i) FROM integers WHERE i IS NOT NULL GROUP BY 42 HAVING i%2>0;
-
-# entry in GROUP BY should refer to base column
-# ...BUT the alias in ORDER BY should refer to the alias from the select list
-# note that both Postgres and MonetDB reject this query because of ambiguity. SQLite accepts it though so we do
-# too.
-query IIR
-SELECT i, i % 2 AS i, SUM(i) FROM integers GROUP BY i ORDER BY i, 3;
-----
-NULL NULL NULL
-2 0 2.000000
-1 1 1.000000
-3 1 3.000000
-
-# changing the name of the alias makes it more explicit what should happen
-query IIR
-SELECT i, i % 2 AS k, SUM(i) FROM integers GROUP BY i ORDER BY k, 3;
-----
-NULL NULL NULL
-2 0 2.000000
-1 1 1.000000
-3 1 3.000000
-
-# we can manually get this behavior by pushing FIRST
-query IR
-SELECT (10-i) AS k, SUM(i) FROM integers GROUP BY k ORDER BY FIRST(i);
-----
-NULL NULL
-9 1.000000
-8 2.000000
-7 3.000000
-
diff --git a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/sql/IgniteSqlConformance.java b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/sql/IgniteSqlConformance.java
index cb4ef685a2..6344cf3906 100644
--- a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/sql/IgniteSqlConformance.java
+++ b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/sql/IgniteSqlConformance.java
@@ -50,4 +50,10 @@ public class IgniteSqlConformance extends SqlAbstractConformance {
public boolean isGroupByOrdinal() {
return true;
}
+
+ /** {@inheritDoc} */
+ @Override
+ public boolean isHavingAlias() {
+ return true;
+ }
}