You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2024/08/08 15:50:14 UTC
(calcite) 01/02: [CALCITE-6519] Non-aggregate query that uses measure in ORDER BY
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 27fd58308cf9106bf2f6ee21cec8230946ecd64a
Author: Julian Hyde <jh...@apache.org>
AuthorDate: Tue Aug 6 18:33:45 2024 -0700
[CALCITE-6519] Non-aggregate query that uses measure in ORDER BY
As of [CALCITE-4496], a non-aggregate query can use measures
in its SELECT clause; this change further allows a
non-aggregate query to use measures in its ORDER BY clause.
Example:
WITH empm AS
(SELECT *, avg(sal) AS MEASURE avgSal FROM emp)
SELECT avgSal, deptno
FROM empm
ORDER BY avgSal DESC LIMIT 3;
Close apache/calcite#3907
---
.../org/apache/calcite/rel/rules/MeasureRules.java | 10 +--
.../calcite/sql/validate/SqlValidatorImpl.java | 31 +++++--
.../apache/calcite/sql2rel/SqlToRelConverter.java | 10 +--
.../java/org/apache/calcite/tools/Programs.java | 2 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 47 ++++++++++
.../apache/calcite/test/SqlToRelConverterTest.java | 18 +++-
.../org/apache/calcite/test/RelOptRulesTest.xml | 84 +++++++++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 17 ++++
core/src/test/resources/sql/measure.iq | 100 +++++++++++++++++++--
9 files changed, 294 insertions(+), 25 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/MeasureRules.java b/core/src/main/java/org/apache/calcite/rel/rules/MeasureRules.java
index 953343dddd..0191e2d211 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/MeasureRules.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/MeasureRules.java
@@ -75,8 +75,8 @@ public abstract class MeasureRules {
}
/** Rule that matches an {@link Aggregate}
- * that contains a {@code M2V} call
- * and pushes down the {@code M2V} call into a {@link Project}. */
+ * that contains an {@code AGG_M2V} call
+ * and pushes down the {@code AGG_M2V} call into a {@link Project}. */
public static final RelOptRule AGGREGATE =
AggregateMeasureRuleConfig.DEFAULT
.toRule();
@@ -197,8 +197,8 @@ public abstract class MeasureRules {
}
/** Rule that matches an {@link Aggregate}
- * that contains a {@code M2V} call
- * and pushes down the {@code M2V} call into a {@link Project}. */
+ * that contains an {@code AGG_M2V} call
+ * and pushes down the {@code AGG_M2V} call into a {@link Project}. */
public static final RelOptRule AGGREGATE2 =
AggregateMeasure2RuleConfig.DEFAULT
.toRule();
@@ -460,7 +460,7 @@ public abstract class MeasureRules {
.as(ProjectSortMeasureRuleConfig.class)
.toRule();
- /** Rule that matches a {@link Project} that contains a {@code M2V} call
+ /** Rule that matches a {@link Project} that contains an {@code M2V} call
* on top of a {@link Sort} and pushes down the {@code M2V} call.
*
* @see MeasureRules#PROJECT_SORT */
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 5adfea1375..e433a5254c 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -91,6 +91,7 @@ import org.apache.calcite.sql.SqlWith;
import org.apache.calcite.sql.SqlWithItem;
import org.apache.calcite.sql.TableCharacteristic;
import org.apache.calcite.sql.fun.SqlCase;
+import org.apache.calcite.sql.fun.SqlInternalOperators;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.AssignableOperandTypeChecker;
@@ -4646,15 +4647,31 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
}
@Override public SqlNode expandOrderExpr(SqlSelect select, SqlNode orderExpr) {
- final SqlNode newSqlNode =
+ final SqlNode orderExpr2 =
new OrderExpressionExpander(select, orderExpr).go();
- if (newSqlNode != orderExpr) {
- final SqlValidatorScope scope = getOrderScope(select);
- inferUnknownTypes(unknownType, scope, newSqlNode);
- final RelDataType type = deriveType(scope, newSqlNode);
- setValidatedNodeType(newSqlNode, type);
+ if (orderExpr2 == orderExpr) {
+ return orderExpr2;
}
- return newSqlNode;
+
+ final SqlValidatorScope scope = getOrderScope(select);
+ inferUnknownTypes(unknownType, scope, orderExpr2);
+ final RelDataType type = deriveType(scope, orderExpr2);
+ setValidatedNodeType(orderExpr2, type);
+ if (!type.isMeasure()) {
+ return orderExpr2;
+ }
+
+ final SqlNode orderExpr3 = measureToValue(orderExpr2);
+ final RelDataType type3 = deriveType(scope, orderExpr3);
+ setValidatedNodeType(orderExpr3, type3);
+ return orderExpr3;
+ }
+
+ private SqlNode measureToValue(SqlNode e) {
+ if (e.getKind() == SqlKind.V2M) {
+ return ((SqlCall) e).operand(0);
+ }
+ return SqlInternalOperators.M2V.createCall(e.getParserPosition(), e);
}
/**
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 54eb918445..5bb6b50e0a 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -3726,7 +3726,6 @@ public class SqlToRelConverter {
SqlNode orderItem, List<SqlNode> extraExprs,
RelFieldCollation.Direction direction,
RelFieldCollation.NullDirection nullDirection) {
- assert select != null;
// Handle DESC keyword, e.g. 'select a, b from t order by a desc'.
switch (orderItem.getKind()) {
case DESCENDING:
@@ -3754,11 +3753,10 @@ public class SqlToRelConverter {
break;
}
- SqlNode converted = validator().expandOrderExpr(select, orderItem);
-
+ final SqlValidator validator = validator();
switch (nullDirection) {
case UNSPECIFIED:
- nullDirection = validator().config().defaultNullCollation().last(desc(direction))
+ nullDirection = validator.config().defaultNullCollation().last(desc(direction))
? RelFieldCollation.NullDirection.LAST
: RelFieldCollation.NullDirection.FIRST;
break;
@@ -3766,9 +3764,11 @@ public class SqlToRelConverter {
break;
}
+ SqlNode converted = validator.expandOrderExpr(select, orderItem);
+
// Scan the select list and order exprs for an identical expression.
final SelectScope selectScope =
- requireNonNull(validator().getRawSelectScope(select),
+ requireNonNull(validator.getRawSelectScope(select),
() -> "getRawSelectScope is not found for " + select);
int ordinal = -1;
List<SqlNode> expandedSelectList = selectScope.getExpandedSelectList();
diff --git a/core/src/main/java/org/apache/calcite/tools/Programs.java b/core/src/main/java/org/apache/calcite/tools/Programs.java
index 4481e29559..0b7a12eacf 100644
--- a/core/src/main/java/org/apache/calcite/tools/Programs.java
+++ b/core/src/main/java/org/apache/calcite/tools/Programs.java
@@ -263,7 +263,7 @@ public class Programs {
private static boolean containsAggM2v(RelNode rel) {
return RelNodes.contains(rel,
aggCall -> aggCall.getAggregation().kind == SqlKind.AGG_M2V,
- RexUtil.find(SqlKind.AGG_M2V));
+ RexUtil.find(ImmutableSet.of(SqlKind.AGG_M2V, SqlKind.M2V)));
}
@Deprecated
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index bff3b87778..be44def3b6 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -5841,6 +5841,53 @@ class RelOptRulesTest extends RelOptTestBase {
.check();
}
+ /** Tests use of a measure in a non-aggregate query. Calls
+ * {@link RelOptFixture#checkUnchanged()} because Sql-to-rel has done the
+ * necessary work already. */
+ @Test void testMeasureWithoutGroupBy() {
+ final String sql = "with empm as\n"
+ + " (select *, avg(sal) as measure avgSal from emp)\n"
+ + "select deptno, avgSal\n"
+ + "from empm";
+ sql(sql)
+ .withRule(MeasureRules.AGGREGATE2,
+ CoreRules.PROJECT_MERGE,
+ MeasureRules.PROJECT)
+ .checkUnchanged();
+ }
+
+ @Test void testMeasureWithoutGroupByWithOrderBy() {
+ final String sql = "with empm as\n"
+ + " (select *, avg(sal) as measure avgSal from emp)\n"
+ + "select deptno, avgSal\n"
+ + "from empm\n"
+ + "order by 2 desc limit 3";
+ sql(sql)
+ .withRule(MeasureRules.PROJECT_SORT,
+ CoreRules.PROJECT_MERGE,
+ MeasureRules.PROJECT)
+ .check();
+ }
+
+ @Disabled
+ @Test void testMeasureJoin() {
+ final String sql = "with deptm as\n"
+ + " (select deptno, name, avg(char_length(name)) as measure m\n"
+ + " from dept)\n"
+ + "select deptno, aggregate(m) as m\n"
+ + "from deptm join emp using (deptno)\n"
+ + "group by deptno";
+ sql(sql)
+ .withFactory(t ->
+ t.withOperatorTable(opTab ->
+ SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
+ SqlLibrary.STANDARD, SqlLibrary.CALCITE))) // for AGGREGATE
+ .withRule(MeasureRules.AGGREGATE,
+ CoreRules.PROJECT_MERGE,
+ MeasureRules.PROJECT)
+ .check();
+ }
+
@Test void testPushAggregateThroughJoin1() {
final String sql = "select e.job,d.name\n"
+ "from (select * from sales.emp where ename = 'A') as e\n"
diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index fd43546710..94c76d9178 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -4764,6 +4764,8 @@ class SqlToRelConverterTest extends SqlToRelTestBase {
.ok();
}
+ /** A query that references a measure that does not contain any aggregate
+ * functions. The measure is fully expanded in the plan. */
@Test void testMeasure1() {
final String sql = "select * from (\n"
+ " select deptno,\n"
@@ -4783,7 +4785,8 @@ class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).ok();
}
- /** As {@link #testMeasure1()} but uses an aggregate measure. */
+ /** As {@link #testMeasure1()} but uses an aggregate measure. The plan
+ * contains a call to {@code AGG_M2V} on top of a call to {@code V2M}. */
@Test void testMeasure3() {
final String sql = "select deptno, count_plus_10, min(job) as min_job\n"
+ "from (\n"
@@ -4796,6 +4799,19 @@ class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).ok();
}
+ /** As {@link #testMeasure3()} but no {@code GROUP BY}.
+ * The measure is expanded to {@code OVER}. */
+ @Test void testMeasure3b() {
+ final String sql = "select deptno, count_plus_10\n"
+ + "from (\n"
+ + " select deptno,\n"
+ + " job,\n"
+ + " count(*) + 10 as measure count_plus_10,\n"
+ + " count_plus_10 + deptno as measure e2\n"
+ + " from emp)";
+ sql(sql).ok();
+ }
+
/** Measures defined in the outermost query are converted to values. */
@Test void testMeasure4() {
final String sql = "select deptno, count(*) as measure c,\n"
diff --git a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index 45d25266b3..b9b4e85aa6 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -6700,6 +6700,37 @@ LogicalProject(DEPTNO=[$0], C1=[$1])
LogicalAggregate(group=[{0}], agg#0=[COUNT()])
LogicalProject(DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMeasureJoin">
+ <Resource name="sql">
+ <![CDATA[select deptno, aggregate(m) as m
+from (select deptno, name, avg(char_length(name)) as measure m
+ from dept)
+join emp using (deptno)
+group by deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], M=[$1])
+ LogicalAggregate(group=[{0}], M=[AGG_M2V($1)])
+ LogicalProject($f0=[$0], M=[$2])
+ LogicalJoin(condition=[=($0, $10)], joinType=[inner])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], M=[V2M(CAST(/(SUM(CHAR_LENGTH($1)), COUNT(CHAR_LENGTH($1)))):INTEGER NOT NULL)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], M=[$1])
+ LogicalAggregate(group=[{0}], agg#0=[SINGLE_VALUE($1)])
+ LogicalProject($f0=[$0], $f2=[M2X($2, SAME_PARTITION($0))])
+ LogicalJoin(condition=[=($0, $10)], joinType=[inner])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], M=[V2M(CAST(/(SUM(CHAR_LENGTH($1)), COUNT(CHAR_LENGTH($1)))):INTEGER NOT NULL)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -6725,6 +6756,59 @@ LogicalProject(DEPTNO=[$0], C1=[$2])
LogicalProject(DEPTNO=[$0], C1=[$1], $f2=[M2V($1)])
LogicalProject(DEPTNO=[$7], C1=[V2M(+(COUNT(), 1))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMeasureWithoutGroupBy">
+ <Resource name="sql">
+ <![CDATA[with empm as
+ (select *, avg(sal) as measure avgSal from emp)
+select deptno, avgSal
+from empm]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7], AVGSAL=[CAST(/(SUM($5) OVER (), COUNT($5) OVER ())):INTEGER NOT NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMeasureWithoutGroupBy2">
+ <Resource name="sql">
+ <![CDATA[with empm as
+ (select *, avg(sal) as measure avgSal from emp)
+select deptno, avgSal from empm]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7], AVGSAL=[CAST(/(SUM($5) OVER (), COUNT($5) OVER ())):INTEGER NOT NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMeasureWithoutGroupByWithOrderBy">
+ <Resource name="sql">
+ <![CDATA[with empm as
+ (select *, avg(sal) as measure avgSal from emp)
+select deptno, avgSal
+from empm
+order by 2 desc limit 3]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], AVGSAL=[$1])
+ LogicalProject(DEPTNO=[$0], AVGSAL=[M2V($1)], EXPR$2=[$2])
+ LogicalSort(sort0=[$2], dir0=[DESC], fetch=[3])
+ LogicalProject(DEPTNO=[$7], AVGSAL=[V2M(CAST(/(SUM($5), COUNT($5))):INTEGER NOT NULL)], EXPR$2=[CAST(/(SUM($5) OVER (), COUNT($5) OVER ())):INTEGER NOT NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], AVGSAL=[$3])
+ LogicalSort(sort0=[$2], dir0=[ASC], fetch=[3])
+ LogicalProject(DEPTNO=[$7], AVGSAL=[V2M(CAST(/(SUM($5), COUNT($5))):INTEGER NOT NULL)], EXPR$2=[CAST(/(SUM($5) OVER (), COUNT($5) OVER ())):INTEGER NOT NULL], $f3=[CAST(/(SUM($5) OVER (), COUNT($5) OVER ())):INTEGER NOT NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 6dd9180e18..dd0c66731c 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -4608,6 +4608,23 @@ LogicalProject(DEPTNO=[$0], COUNT_PLUS_10=[$1], MIN_JOB=[$2])
LogicalAggregate(group=[{0}], agg#0=[AGG_M2V($1)], MIN_JOB=[MIN($2)])
LogicalProject(DEPTNO=[$7], COUNT_PLUS_10=[V2M(+(COUNT(), 10))], JOB=[$2])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMeasure3b">
+ <Resource name="sql">
+ <![CDATA[select deptno, count_plus_10
+from (
+ select deptno,
+ job,
+ count(*) + 10 as measure count_plus_10,
+ count_plus_10 + deptno as measure e2
+ from emp)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7], COUNT_PLUS_10=[+(COUNT() OVER (), 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/measure.iq b/core/src/test/resources/sql/measure.iq
index 056537d1f7..8a59142424 100644
--- a/core/src/test/resources/sql/measure.iq
+++ b/core/src/test/resources/sql/measure.iq
@@ -216,25 +216,113 @@ from empm;
+---------+--------+
| A | DEPTNO |
+---------+--------+
+| 2450.00 | 10 |
+| 5000.00 | 10 |
+| 1300.00 | 10 |
| 1100.00 | 20 |
+| 3000.00 | 20 |
+| 2975.00 | 20 |
+| 3000.00 | 20 |
+| 800.00 | 20 |
+| 1600.00 | 30 |
+| 2850.00 | 30 |
+| 950.00 | 30 |
| 1250.00 | 30 |
-| 1250.00 | 30 |
-| 1300.00 | 10 |
| 1500.00 | 30 |
-| 1600.00 | 30 |
+| 1250.00 | 30 |
++---------+--------+
+(14 rows)
+
+!ok
+
+# Measure in query with ORDER BY and no GROUP BY.
+# (As above, but sorted.)
+select avg_sal as a, deptno
+from empm
+order by deptno, ename;
++---------+--------+
+| A | DEPTNO |
++---------+--------+
| 2450.00 | 10 |
-| 2850.00 | 30 |
-| 2975.00 | 20 |
+| 5000.00 | 10 |
+| 1300.00 | 10 |
+| 1100.00 | 20 |
| 3000.00 | 20 |
+| 2975.00 | 20 |
| 3000.00 | 20 |
-| 5000.00 | 10 |
| 800.00 | 20 |
+| 1600.00 | 30 |
+| 2850.00 | 30 |
| 950.00 | 30 |
+| 1250.00 | 30 |
+| 1500.00 | 30 |
+| 1250.00 | 30 |
+---------+--------+
(14 rows)
!ok
+# Measure in query with ORDER BY and LIMIT but no GROUP BY.
+select avg_sal as a, ename, deptno
+from empm
+order by ename limit 3;
++---------+-------+--------+
+| A | ENAME | DEPTNO |
++---------+-------+--------+
+| 1100.00 | ADAMS | 20 |
+| 1600.00 | ALLEN | 30 |
+| 2850.00 | BLAKE | 30 |
++---------+-------+--------+
+(3 rows)
+
+!ok
+
+# Measure in query with ORDER BY and LIMIT but no GROUP BY, omitting sort key.
+# (As above, but not projecting ename.)
+select avg_sal as a, deptno
+from empm
+order by ename limit 3;
++---------+--------+
+| A | DEPTNO |
++---------+--------+
+| 1100.00 | 20 |
+| 1600.00 | 30 |
+| 2850.00 | 30 |
++---------+--------+
+(3 rows)
+
+!ok
+
+# Measure in query with ORDER and LIMIT but no GROUP BY, sorting by measure.
+select avg_sal, deptno
+from empm
+order by avg_sal desc limit 3;
++---------+--------+
+| AVG_SAL | DEPTNO |
++---------+--------+
+| 1100.00 | 20 |
+| 950.00 | 30 |
+| 800.00 | 20 |
++---------+--------+
+(3 rows)
+
+!ok
+
+# Measure in query with WHERE but no GROUP BY
+select avg_sal as a, deptno
+from empm
+where job = 'MANAGER';
++---------+--------+
+| A | DEPTNO |
++---------+--------+
+| 2450.00 | 10 |
+| 2850.00 | 30 |
+| 2975.00 | 20 |
++---------+--------+
+(3 rows)
+
+!ok
+
# Measure that references another measure
select *
from (