You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by al...@apache.org on 2024/09/24 14:48:21 UTC
(ignite) branch master updated: IGNITE-23112 SQL Calcite: Allow grouping by alias and ordinal value - Fixes #11500.
This is an automated email from the ASF dual-hosted git repository.
alexpl pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/master by this push:
new 6fa2be72c57 IGNITE-23112 SQL Calcite: Allow grouping by alias and ordinal value - Fixes #11500.
6fa2be72c57 is described below
commit 6fa2be72c574d02381a0e4236a88ad3f8e39d748
Author: Vladimir Steshin <vl...@gmail.com>
AuthorDate: Tue Sep 24 17:46:43 2024 +0300
IGNITE-23112 SQL Calcite: Allow grouping by alias and ordinal value - Fixes #11500.
Signed-off-by: Aleksey Plekhanov <pl...@gmail.com>
---
.../query/calcite/sql/IgniteSqlConformance.java | 10 ++
.../integration/AggregatesIntegrationTest.java | 27 +++
.../calcite/planner/AggregatePlannerTest.java | 33 ++++
.../test/sql/aggregate/group/test_group_by.test | 34 +++-
.../sql/aggregate/group/test_group_by.test_ignore | 182 ---------------------
.../sql/aggregate/group/test_group_by_alias.test | 17 ++
.../group/test_group_by_alias.test_ignore | 1 -
.../calcite/src/test/sql/order/test_order_by.test | 20 +++
.../src/test/sql/order/test_order_by.test_ignore | 9 -
9 files changed, 137 insertions(+), 196 deletions(-)
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/IgniteSqlConformance.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/IgniteSqlConformance.java
index c30e6725986..3316e71c2fa 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/IgniteSqlConformance.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/IgniteSqlConformance.java
@@ -35,4 +35,14 @@ public class IgniteSqlConformance extends SqlAbstractConformance {
@Override public boolean isBangEqualAllowed() {
return true;
}
+
+ /** {@inheritDoc} */
+ @Override public boolean isGroupByAlias() {
+ return true;
+ }
+
+ /** {@inheritDoc} */
+ @Override public boolean isGroupByOrdinal() {
+ return true;
+ }
}
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AggregatesIntegrationTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AggregatesIntegrationTest.java
index 4b910029cae..3afa400e873 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AggregatesIntegrationTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AggregatesIntegrationTest.java
@@ -18,6 +18,7 @@
package org.apache.ignite.internal.processors.query.calcite.integration;
import java.util.List;
+import org.apache.calcite.sql.validate.SqlConformance;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.IgniteCheckedException;
import org.apache.ignite.cache.CacheMode;
@@ -140,6 +141,32 @@ public class AggregatesIntegrationTest extends AbstractBasicIntegrationTest {
assertQuery("SELECT COUNT(b) FROM tbl").returns(100L).check();
}
+ /**
+ * Tests grouping result by an alias and an ordinal value.
+ *
+ * @see SqlConformance#isGroupByAlias()
+ * @see SqlConformance#isGroupByOrdinal()
+ */
+ @Test
+ public void testGroupingByAlias() {
+ executeSql("CREATE TABLE t1(id INT, val_int INT, val_char VARCHAR, PRIMARY KEY(id))");
+
+ for (int i = 0; i < 10; i++)
+ executeSql("INSERT INTO t1 VALUES (?, ?, ?)", i, i % 3, "val" + i % 3);
+
+ assertQuery("SELECT val_char as ALS, count(val_int) FROM t1 GROUP BY ALS")
+ .returns("val0", 4L)
+ .returns("val1", 3L)
+ .returns("val2", 3L)
+ .check();
+
+ assertQuery("SELECT val_char, count(val_int) FROM t1 GROUP BY 1")
+ .returns("val0", 4L)
+ .returns("val1", 3L)
+ .returns("val2", 3L)
+ .check();
+ }
+
/** */
@Test
public void testCountOfNonNumericField() {
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/AggregatePlannerTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/AggregatePlannerTest.java
index f464545b06d..cd2ef4d8f91 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/AggregatePlannerTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/AggregatePlannerTest.java
@@ -29,6 +29,7 @@ import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.sql.SqlExplainLevel;
import org.apache.calcite.sql.fun.SqlAvgAggFunction;
+import org.apache.calcite.sql.validate.SqlConformance;
import org.apache.ignite.internal.processors.query.calcite.rel.IgniteAggregate;
import org.apache.ignite.internal.processors.query.calcite.rel.IgniteIndexScan;
import org.apache.ignite.internal.processors.query.calcite.rel.IgniteRel;
@@ -326,6 +327,38 @@ public class AggregatePlannerTest extends AbstractAggregatePlannerTest {
assertEquals(tf.createJavaType(Double.class), rowTypes.getFieldList().get(7).getType());
}
+ /**
+ * Tests that grouping plan works with an alias and an ordinal value.
+ *
+ * @see SqlConformance#isGroupByAlias()
+ * @see SqlConformance#isGroupByOrdinal()
+ */
+ @Test
+ public void groupingByAliasAndOrdinal() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable(
+ "TEST", IgniteDistributions.broadcast(),
+ "ID", Integer.class,
+ "GRP", Integer.class,
+ "VAL_INT", Integer.class
+ )
+ );
+
+ assertPlan(
+ "SELECT GRP AS ALS, SUM(VAL_INT) FROM test GROUP BY ALS",
+ schema,
+ nodeOrAnyChild(isInstanceOf(algo.colocated)),
+ algo.rulesToDisable
+ );
+
+ assertPlan(
+ "SELECT GRP, SUM(VAL_INT) FROM test GROUP BY 1",
+ schema,
+ nodeOrAnyChild(isInstanceOf(algo.colocated)),
+ algo.rulesToDisable
+ );
+ }
+
/**
* @throws Exception If failed.
*/
diff --git a/modules/calcite/src/test/sql/aggregate/group/test_group_by.test b/modules/calcite/src/test/sql/aggregate/group/test_group_by.test
index e1fbc5d64df..56ffeccd355 100644
--- a/modules/calcite/src/test/sql/aggregate/group/test_group_by.test
+++ b/modules/calcite/src/test/sql/aggregate/group/test_group_by.test
@@ -78,6 +78,13 @@ SELECT b, SUM(a), COUNT(*), SUM(a+2) FROM test WHERE a <= 12 GROUP BY b ORDER BY
statement error
SELECT b % 2 AS f, COUNT(SUM(a)) FROM test GROUP BY f;
+# 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
+
statement ok
INSERT INTO test VALUES (12, 21), (12, 21), (12, 21)
@@ -113,13 +120,32 @@ SELECT i, SUM(j), ANY_VALUE(j) FROM integers GROUP BY i ORDER BY i
2 4.000000 4
3 8.000000 4
-# use an alias that is identical to a column name (should prioritize column name)
+# group by constant alias
query IR
-SELECT 1 AS i, SUM(i) FROM integers GROUP BY i ORDER BY 2;
+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 and the table reference
+query IR
+SELECT 1 AS i, SUM(t.i) FROM integers t GROUP BY t.i ORDER BY 2;
----
1 2.000000
1 6.000000
+# use an alias that is identical to a column name
+query IR
+SELECT 1 AS i, SUM(i) FROM integers GROUP BY i ORDER BY 2;
+----
+1 8.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;
@@ -138,9 +164,9 @@ NULL NULL
2 2.000000
3 3.000000
-# column reference should have preference over alias reference in grouping
+# column reference have to be specified when an alias with the same name exists
query IIR
-SELECT i, i % 2 AS i, SUM(i) FROM integers GROUP BY i ORDER BY 1 NULLS FIRST;
+SELECT i, i % 2 AS i, SUM(i) FROM integers t GROUP BY t.i ORDER BY 1 NULLS FIRST;
----
NULL NULL NULL
1 1 1.000000
diff --git a/modules/calcite/src/test/sql/aggregate/group/test_group_by.test_ignore b/modules/calcite/src/test/sql/aggregate/group/test_group_by.test_ignore
deleted file mode 100644
index 22efd582bad..00000000000
--- a/modules/calcite/src/test/sql/aggregate/group/test_group_by.test_ignore
+++ /dev/null
@@ -1,182 +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
-
-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 only be referenced in the GROUP BY as the root column: operations not allowed
-# CONTROVERSIAL: this query DOES work in SQLite
-statement error
-SELECT 1 AS k, SUM(i) FROM integers GROUP BY k+1 ORDER BY 2;
-
-# 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/calcite/src/test/sql/aggregate/group/test_group_by_alias.test b/modules/calcite/src/test/sql/aggregate/group/test_group_by_alias.test
index 4b70467bbf5..19bdfb57094 100644
--- a/modules/calcite/src/test/sql/aggregate/group/test_group_by_alias.test
+++ b/modules/calcite/src/test/sql/aggregate/group/test_group_by_alias.test
@@ -8,6 +8,23 @@ CREATE TABLE integers(i INTEGER)
statement ok
INSERT INTO integers VALUES (1), (2), (3), (NULL)
+# an alias in grouping + HAVING
+# 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;
+
# this now orders by the actual grouping column
query IIR
SELECT i, i % 2 AS k, SUM(i) FROM integers GROUP BY i ORDER BY i;
diff --git a/modules/calcite/src/test/sql/aggregate/group/test_group_by_alias.test_ignore b/modules/calcite/src/test/sql/aggregate/group/test_group_by_alias.test_ignore
index 846468d8894..5587ba28d2f 100644
--- a/modules/calcite/src/test/sql/aggregate/group/test_group_by_alias.test_ignore
+++ b/modules/calcite/src/test/sql/aggregate/group/test_group_by_alias.test_ignore
@@ -1,7 +1,6 @@
# 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
diff --git a/modules/calcite/src/test/sql/order/test_order_by.test b/modules/calcite/src/test/sql/order/test_order_by.test
index 05b6e67b8c5..7de6965e3a3 100644
--- a/modules/calcite/src/test/sql/order/test_order_by.test
+++ b/modules/calcite/src/test/sql/order/test_order_by.test
@@ -106,6 +106,13 @@ SELECT b, a FROM test WHERE a < 13 ORDER BY b DESC;
22 11
21 12
+# order by expression
+query IR
+SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY b % 2;
+----
+0 24.000000
+1 12.000000
+
# order by expression that is not in SELECT
query II
SELECT b % 2 AS f, a FROM test ORDER BY b % 4, a;
@@ -114,6 +121,19 @@ SELECT b % 2 AS f, a FROM test ORDER BY b % 4, a;
0 11
0 13
+# ORDER 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
+
+query IR
+SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY 1;
+----
+0 24.000000
+1 12.000000
+
# ORDER BY after union
query I
SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY k;
diff --git a/modules/calcite/src/test/sql/order/test_order_by.test_ignore b/modules/calcite/src/test/sql/order/test_order_by.test_ignore
index fdc6a5b0dbc..a4756556bc5 100644
--- a/modules/calcite/src/test/sql/order/test_order_by.test_ignore
+++ b/modules/calcite/src/test/sql/order/test_order_by.test_ignore
@@ -143,15 +143,6 @@ SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY k;
2
3
-# ORDER BY on alias in right-most query
-# CONTROVERSIAL: SQLite allows both "k" and "l" to be referenced here, Postgres and MonetDB give an error.
-query I
-SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY l;
-----
-1
-2
-3
-
# computations with aliases are not allowed though
statement error
SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY 1-k;