This is an automated email from the ASF dual-hosted git repository.
xiong 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 7c423ef [CALCITE-4876] Converting RelNode to SQL with
CalciteSqlDialect gets wrong result while EnumerableIntersect is followed by
EnumerableLimit
7c423ef is described below
commit 7c423ef23878271b1c50c03629ebfff674985681
Author: huzhe <[email protected]>
AuthorDate: Sun Nov 14 11:27:55 2021 +0800
[CALCITE-4876] Converting RelNode to SQL with CalciteSqlDialect gets wrong
result while EnumerableIntersect is followed by EnumerableLimit
---
.../calcite/sql/fun/SqlStdOperatorTable.java | 24 ++++++++++-----------
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 25 ++++++++++++++++++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 5 +++--
3 files changed, 40 insertions(+), 14 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 342719f..9d6ed53 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -103,58 +103,58 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
// INTERSECT -> *
// which explains the different precedence values
public static final SqlSetOperator UNION =
- new SqlSetOperator("UNION", SqlKind.UNION, 14, false);
+ new SqlSetOperator("UNION", SqlKind.UNION, 12, false);
public static final SqlSetOperator UNION_ALL =
- new SqlSetOperator("UNION ALL", SqlKind.UNION, 14, true);
+ new SqlSetOperator("UNION ALL", SqlKind.UNION, 12, true);
public static final SqlSetOperator EXCEPT =
- new SqlSetOperator("EXCEPT", SqlKind.EXCEPT, 14, false);
+ new SqlSetOperator("EXCEPT", SqlKind.EXCEPT, 12, false);
public static final SqlSetOperator EXCEPT_ALL =
- new SqlSetOperator("EXCEPT ALL", SqlKind.EXCEPT, 14, true);
+ new SqlSetOperator("EXCEPT ALL", SqlKind.EXCEPT, 12, true);
public static final SqlSetOperator INTERSECT =
- new SqlSetOperator("INTERSECT", SqlKind.INTERSECT, 18, false);
+ new SqlSetOperator("INTERSECT", SqlKind.INTERSECT, 14, false);
public static final SqlSetOperator INTERSECT_ALL =
- new SqlSetOperator("INTERSECT ALL", SqlKind.INTERSECT, 18, true);
+ new SqlSetOperator("INTERSECT ALL", SqlKind.INTERSECT, 14, true);
/**
* The {@code MULTISET UNION DISTINCT} operator.
*/
public static final SqlMultisetSetOperator MULTISET_UNION_DISTINCT =
- new SqlMultisetSetOperator("MULTISET UNION DISTINCT", 14, false);
+ new SqlMultisetSetOperator("MULTISET UNION DISTINCT", 12, false);
/**
* The {@code MULTISET UNION [ALL]} operator.
*/
public static final SqlMultisetSetOperator MULTISET_UNION =
- new SqlMultisetSetOperator("MULTISET UNION ALL", 14, true);
+ new SqlMultisetSetOperator("MULTISET UNION ALL", 12, true);
/**
* The {@code MULTISET EXCEPT DISTINCT} operator.
*/
public static final SqlMultisetSetOperator MULTISET_EXCEPT_DISTINCT =
- new SqlMultisetSetOperator("MULTISET EXCEPT DISTINCT", 14, false);
+ new SqlMultisetSetOperator("MULTISET EXCEPT DISTINCT", 12, false);
/**
* The {@code MULTISET EXCEPT [ALL]} operator.
*/
public static final SqlMultisetSetOperator MULTISET_EXCEPT =
- new SqlMultisetSetOperator("MULTISET EXCEPT ALL", 14, true);
+ new SqlMultisetSetOperator("MULTISET EXCEPT ALL", 12, true);
/**
* The {@code MULTISET INTERSECT DISTINCT} operator.
*/
public static final SqlMultisetSetOperator MULTISET_INTERSECT_DISTINCT =
- new SqlMultisetSetOperator("MULTISET INTERSECT DISTINCT", 18, false);
+ new SqlMultisetSetOperator("MULTISET INTERSECT DISTINCT", 14, false);
/**
* The {@code MULTISET INTERSECT [ALL]} operator.
*/
public static final SqlMultisetSetOperator MULTISET_INTERSECT =
- new SqlMultisetSetOperator("MULTISET INTERSECT ALL", 18, true);
+ new SqlMultisetSetOperator("MULTISET INTERSECT ALL", 14, true);
//-------------------------------------------------------------
// BINARY OPERATORS
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 1b700f3..5a88068 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -1881,6 +1881,31 @@ class RelToSqlConverterTest {
relFn(relFn).ok(expectedSql);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-4876";>[CALCITE-4876]
+ * Converting RelNode to SQL with CalciteSqlDialect gets wrong result
+ * while EnumerableIntersect is followed by EnumerableLimit</a>.
+ */
+ @Test void testUnparseIntersectWithLimit() {
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("DEPT")
+ .project(b.field("DEPTNO"))
+ .scan("EMP")
+ .project(b.field("DEPTNO"))
+ .intersect(true)
+ .limit(1, 3)
+ .build();
+ final String expectedSql = "SELECT *\n"
+ + "FROM (SELECT \"DEPTNO\"\n"
+ + "FROM \"scott\".\"DEPT\"\n"
+ + "INTERSECT ALL\n"
+ + "SELECT \"DEPTNO\"\n"
+ + "FROM \"scott\".\"EMP\")\n"
+ + "OFFSET 1 ROWS\n"
+ + "FETCH NEXT 3 ROWS ONLY";
+ relFn(relFn).ok(expectedSql);
+ }
+
@Test void testSelectQueryWithLimitClause() {
String query = "select \"product_id\" from \"product\" limit 100 offset
10";
final String expected = "SELECT product_id\n"
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index a800117..5104d01 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -9610,12 +9610,13 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
+ "RESPECT NULLS -\n"
+ "TABLESAMPLE -\n"
+ "\n"
+ + "NULLS FIRST post\n"
+ + "NULLS LAST post\n"
+ + "\n"
+ "INTERSECT left\n"
+ "INTERSECT ALL left\n"
+ "MULTISET INTERSECT ALL left\n"
+ "MULTISET INTERSECT DISTINCT left\n"
- + "NULLS FIRST post\n"
- + "NULLS LAST post\n"
+ "\n"
+ "EXCEPT left\n"
+ "EXCEPT ALL left\n"
