This is an automated email from the ASF dual-hosted git repository. liyafan pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit c310f7c6ef5a3af509c1369c677c8c610ce0cdc7 Author: xiejiajun <[email protected]> AuthorDate: Sat Jan 8 16:05:56 2022 +0800 [CALCITE-4323] If a view definition has an ORDER BY clause, retain the sort if the view is used in a query at top level The goal is for simple queries to retain the sorting. For example, given CREATE VIEW v AS SELECT * FROM emp WHERE job = 'CLERK' ORDER BY deptno the query SELECT * FROM v WHERE ename LIKE 'M%' will be expanded as if the user had written SELECT * FROM emp WHERE ename LIKE 'M%' AND job = 'CLERK' ORDER BY deptno But the ORDER BY will be removed from more complex queries (e.g. those involving GROUP BY, UNION, sub-query) where it would have no effect. If the ORDER BY also includes LIMIT or OFFSET, it affects the rows returned, not just their sort order; such an ORDER BY is never removed. Close apache/calcite#2679 --- .../apache/calcite/prepare/CalcitePrepareImpl.java | 2 +- .../apache/calcite/sql2rel/SqlToRelConverter.java | 45 +++++- .../java/org/apache/calcite/test/JdbcTest.java | 175 +++++++++++++++++++++ 3 files changed, 219 insertions(+), 3 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/prepare/CalcitePrepareImpl.java b/core/src/main/java/org/apache/calcite/prepare/CalcitePrepareImpl.java index 774319e..6d06705 100644 --- a/core/src/main/java/org/apache/calcite/prepare/CalcitePrepareImpl.java +++ b/core/src/main/java/org/apache/calcite/prepare/CalcitePrepareImpl.java @@ -1076,7 +1076,7 @@ public class CalcitePrepareImpl implements CalcitePrepare { SqlToRelConverter sqlToRelConverter = getSqlToRelConverter(validator, catalogReader, config); RelRoot root = - sqlToRelConverter.convertQuery(sqlNode, true, false); + sqlToRelConverter.convertQuery(sqlNode, true, true); --expansionDepth; return root; 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 9d06f37..973f6dd 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -683,6 +683,42 @@ public class SqlToRelConverter { convertFrom( bb, select.getFrom()); + + // We would like to remove ORDER BY clause from an expanded view, except if + // it is top-level or affects semantics. + // + // Top-level example. Given the view definition + // CREATE VIEW v AS SELECT * FROM t ORDER BY x + // we would retain the view's ORDER BY in + // SELECT * FROM v + // or + // SELECT * FROM v WHERE y = 5 + // but remove the view's ORDER BY in + // SELECT * FROM v ORDER BY z + // and + // SELECT deptno, COUNT(*) FROM v GROUP BY deptno + // because the ORDER BY and GROUP BY mean that the view is not 'top level' in + // the query. + // + // Semantics example. Given the view definition + // CREATE VIEW v2 AS SELECT * FROM t ORDER BY x LIMIT 10 + // we would never remove the ORDER BY, because "ORDER BY ... LIMIT" is about + // semantics. It is not a 'pure order'. + if (RelOptUtil.isPureOrder(castNonNull(bb.root)) + && config.isRemoveSortInSubQuery()) { + // Remove the Sort if the view is at the top level. Also remove the Sort + // if there are other nodes, which will cause the view to be in the + // sub-query. + if (!bb.top + || validator().isAggregate(select) + || select.isDistinct() + || select.hasOrderBy() + || select.getFetch() != null + || select.getOffset() != null) { + bb.setRoot(castNonNull(bb.root).getInput(0), true); + } + } + convertWhere( bb, select.getWhere()); @@ -2586,14 +2622,19 @@ public class SqlToRelConverter { extendedColumns); table = table.extend(extendedFields); } - final RelNode tableRel; // Review Danny 2020-01-13: hacky to construct a new table scan // in order to apply the hint strategies. final List<RelHint> hints = hintStrategies.apply( SqlUtil.getRelHint(hintStrategies, tableHints), LogicalTableScan.create(cluster, table, ImmutableList.of())); - tableRel = toRel(table, hints); + final RelNode tableRel = toRel(table, hints); bb.setRoot(tableRel, true); + + if (RelOptUtil.isPureOrder(castNonNull(bb.root)) + && removeSortInSubQuery(bb.top)) { + bb.setRoot(castNonNull(bb.root).getInput(0), true); + } + if (usedDataset[0]) { bb.setDataset(datasetName); } diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java index ea8471a..162d496 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java @@ -80,12 +80,14 @@ import org.apache.calcite.sql.SqlSpecialOperator; import org.apache.calcite.sql.parser.SqlParser; import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.parser.impl.SqlParserImpl; +import org.apache.calcite.sql2rel.SqlToRelConverter.Config; import org.apache.calcite.test.schemata.catchall.CatchallSchema; import org.apache.calcite.test.schemata.foodmart.FoodmartSchema; import org.apache.calcite.test.schemata.hr.Department; import org.apache.calcite.test.schemata.hr.Employee; import org.apache.calcite.test.schemata.hr.HrSchema; import org.apache.calcite.util.Bug; +import org.apache.calcite.util.Holder; import org.apache.calcite.util.JsonBuilder; import org.apache.calcite.util.Pair; import org.apache.calcite.util.Smalls; @@ -144,6 +146,7 @@ import java.util.stream.Collectors; import java.util.stream.Stream; import javax.sql.DataSource; +import static org.apache.calcite.test.CalciteAssert.checkResult; import static org.apache.calcite.test.Matchers.isLinux; import static org.apache.calcite.util.Static.RESOURCE; @@ -5878,6 +5881,178 @@ public class JdbcTest { }); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-4323";>[CALCITE-4323] + * View with ORDER BY throws AssertionError during view expansion</a>. */ + @Test void testSortedView() { + final String viewSql = "select * from \"EMPLOYEES\" order by \"deptno\""; + final CalciteAssert.AssertThat with = modelWithView(viewSql, null); + // Keep sort, because view is top node + with.query("select * from \"adhoc\".V") + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalProject(empid=[$0], deptno=[$1], name=[$2], " + + "salary=[$3], commission=[$4])\n" + + " LogicalSort(sort0=[$1], dir0=[ASC])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + // Remove sort, because view not is top node + with.query("select * from \"adhoc\".V union all select * from \"adhoc\".\"EMPLOYEES\"") + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalUnion(all=[true])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + with.query("select * from " + + "(select \"empid\", \"deptno\" from \"adhoc\".V) where \"deptno\" > 10") + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalProject(empid=[$0], deptno=[$1])\n" + + " LogicalFilter(condition=[>($1, 10)])\n" + + " LogicalProject(empid=[$0], deptno=[$1])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + with.query("select * from \"adhoc\".\"EMPLOYEES\" where exists (select * from \"adhoc\".V)") + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalProject(empid=[$0], deptno=[$1], name=[$2], " + + "salary=[$3], commission=[$4])\n" + + " LogicalFilter(condition=[EXISTS({\n" + + "LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n" + + "})])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + // View is used in a query at top level，but it's not the top plan + // Still remove sort + with.query("select * from \"adhoc\".V order by \"empid\"") + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalSort(sort0=[$0], dir0=[ASC])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + with.query("select * from \"adhoc\".V, \"adhoc\".\"EMPLOYEES\"") + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalProject(empid=[$0], deptno=[$1], name=[$2], " + + "salary=[$3], commission=[$4], empid0=[$5], deptno0=[$6], name0=[$7], salary0=[$8]," + + " commission0=[$9])\n" + + " LogicalJoin(condition=[true], joinType=[inner])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + with.query("select \"empid\", count(*) from \"adhoc\".V group by \"empid\"") + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalAggregate(group=[{0}], EXPR$1=[COUNT()])\n" + + " LogicalProject(empid=[$0])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + with.query("select distinct * from \"adhoc\".V") + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalAggregate(group=[{0, 1, 2, 3, 4}])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + } + + @Test void testCustomRemoveSortInView() { + final String viewSql = "select * from \"EMPLOYEES\" order by \"deptno\""; + final CalciteAssert.AssertThat with = modelWithView(viewSql, null); + // Some cases where we may or may not want to keep the Sort + with.query("select * from \"adhoc\".V where \"deptno\" > 10") + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalProject(empid=[$0], deptno=[$1], name=[$2], " + + "salary=[$3], commission=[$4])\n" + + " LogicalFilter(condition=[>($1, 10)])\n" + + " LogicalSort(sort0=[$1], dir0=[ASC])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], " + + "salary=[$3], commission=[$4])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + with.query("select * from \"adhoc\".V where \"deptno\" > 10") + .withHook(Hook.SQL2REL_CONVERTER_CONFIG_BUILDER, + (Consumer<Holder<Config>>) configHolder -> + configHolder.set(configHolder.get().withRemoveSortInSubQuery(false))) + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalProject(empid=[$0], deptno=[$1], name=[$2], " + + "salary=[$3], commission=[$4])\n" + + " LogicalFilter(condition=[>($1, 10)])\n" + + " LogicalSort(sort0=[$1], dir0=[ASC])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + + with.query("select * from \"adhoc\".V limit 10") + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalSort(fetch=[10])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + with.query("select * from \"adhoc\".V limit 10") + .withHook(Hook.SQL2REL_CONVERTER_CONFIG_BUILDER, + (Consumer<Holder<Config>>) configHolder -> + configHolder.set(configHolder.get().withRemoveSortInSubQuery(false))) + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalSort(fetch=[10])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalSort(sort0=[$1], dir0=[ASC])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + + with.query("select * from \"adhoc\".V offset 10") + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalSort(offset=[10])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + with.query("select * from \"adhoc\".V offset 10") + .withHook(Hook.SQL2REL_CONVERTER_CONFIG_BUILDER, + (Consumer<Holder<Config>>) configHolder -> + configHolder.set(configHolder.get().withRemoveSortInSubQuery(false))) + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalSort(offset=[10])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalSort(sort0=[$1], dir0=[ASC])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + + + with.query("select * from \"adhoc\".V limit 5 offset 5") + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalSort(offset=[5], fetch=[5])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + with.query("select * from \"adhoc\".V limit 5 offset 5") + .withHook(Hook.SQL2REL_CONVERTER_CONFIG_BUILDER, + (Consumer<Holder<Config>>) configHolder -> + configHolder.set(configHolder.get().withRemoveSortInSubQuery(false))) + .explainMatches(" without implementation ", + checkResult("PLAN=" + + "LogicalSort(offset=[5], fetch=[5])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalSort(sort0=[$1], dir0=[ASC])\n" + + " LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], " + + "commission=[$4])\n" + + " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n")); + } + /** Tests a view with ORDER BY and LIMIT clauses. */ @Test void testOrderByView() throws Exception { final CalciteAssert.AssertThat with =
