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 2022/02/16 21:32:57 UTC
[calcite] branch master updated: [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
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new de86d4f [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
de86d4f is described below
commit de86d4f8dc2d70b84f712446a285f5357ebf9d2c
Author: xiejiajun <ji...@foxmail.com>
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 =