This is an automated email from the ASF dual-hosted git repository.
amashenkov 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 e78b369bb5 IGNITE-21920 Cover SQL E051-04 (Basic query specification,
GROUP BY can contain columns not in <select list>) feature by tests (#3572)
e78b369bb5 is described below
commit e78b369bb5e7dc35c611eaf68b11457d6b062b53
Author: Andrew V. Mashenkov <[email protected]>
AuthorDate: Tue Apr 9 15:13:39 2024 +0300
IGNITE-21920 Cover SQL E051-04 (Basic query specification, GROUP BY can
contain columns not in <select list>) feature by tests (#3572)
---
.../group/test_group_by_not_in_select.test | 64 ++++++++++++++++++++++
1 file changed, 64 insertions(+)
diff --git
a/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by_not_in_select.test
b/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by_not_in_select.test
new file mode 100644
index 0000000000..3f2c3feea1
--- /dev/null
+++
b/modules/sql-engine/src/integrationTest/sql/aggregate/group/test_group_by_not_in_select.test
@@ -0,0 +1,64 @@
+# name: test/sql/aggregate/group/test_group_by_not_in_select.test
+# description: Test group by statements has no restriction that group by
clause can contains column not in the select list.
+# feature: E051-04
+# group: [group]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE test (a INTEGER, b INTEGER);
+
+statement ok
+CREATE TABLE integers(i INTEGER, j INTEGER, k INTEGER);
+
+statement ok
+INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)
+
+statement ok
+INSERT INTO integers VALUES (1, 1, 2), (1, 2, 2), (1, 1, 2), (2, 1, 2), (1, 2,
4), (1, 2, NULL);
+
+query R rowsort
+SELECT SUM(a) FROM test GROUP BY b;
+----
+12.000000
+24.000000
+
+query R
+SELECT SUM(a) FROM test GROUP BY b ORDER BY COUNT(a);
+----
+12.000000
+24.000000
+
+query R
+SELECT SUM(a) FROM test GROUP BY b ORDER BY COUNT(a) DESC;
+----
+24.000000
+12.000000
+
+# multi-columns
+query RI rowsort
+SELECT SUM(k), COUNT(k) FROM integers GROUP BY i, j
+----
+2.000000 1
+4.000000 2
+6.000000 2
+
+# HAVING with condition on group
+query R
+SELECT SUM(a) FROM test GROUP BY b HAVING b=22;
+----
+24.000000
+
+# HAVING with condition on aggregate column
+query R
+SELECT SUM(a) FROM test GROUP BY b HAVING SUM(a) < 20;
+----
+12.000000
+
+# HAVING with condition on non-select column
+query R
+SELECT SUM(k) FROM integers GROUP BY i HAVING COUNT(j) < 20;
+----
+10.000000
+2.000000
\ No newline at end of file