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 <[email protected]> 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 (
