(ignite-3) branch main updated: IGNITE-21920 Cover SQL E051-04 (Basic query specification, GROUP BY can contain columns not in ) feature by tests (#3572) amashenkov Tue, 09 Apr 2024 05:13:49 -0700 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 <amashen...@users.noreply.github.com> 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 Previous message View by thread View by date Next message Reply via email to

[amashenkov]

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 <amashen...@users.noreply.github.com>
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