This is an automated email from the ASF dual-hosted git repository.
xiong pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new d2e3350211 [CALCITE-6436] JDBC adapter generates SQL missing
parentheses when comparing 3 values with the same precedence like (a=b)=c
d2e3350211 is described below
commit d2e335021184b7a30af459c1f2d587286d017e61
Author: Xiong Duan <[email protected]>
AuthorDate: Sat Jun 29 07:53:43 2024 +0800
[CALCITE-6436] JDBC adapter generates SQL missing parentheses when
comparing 3 values with the same precedence like (a=b)=c
---
.../main/java/org/apache/calcite/sql/SqlCall.java | 3 +-
.../main/java/org/apache/calcite/sql/SqlKind.java | 3 +-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 76 ++++++++++++++++++++++
.../org/apache/calcite/test/JdbcAdapterTest.java | 15 +++++
4 files changed, 95 insertions(+), 2 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlCall.java
b/core/src/main/java/org/apache/calcite/sql/SqlCall.java
index 617fa469eb..7a69938815 100755
--- a/core/src/main/java/org/apache/calcite/sql/SqlCall.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlCall.java
@@ -123,7 +123,8 @@ public abstract class SqlCall extends SqlNode {
final SqlDialect dialect = writer.getDialect();
if (leftPrec > operator.getLeftPrec()
|| (operator.getRightPrec() <= rightPrec && (rightPrec != 0))
- || writer.isAlwaysUseParentheses() && isA(SqlKind.EXPRESSION)) {
+ || writer.isAlwaysUseParentheses() && isA(SqlKind.EXPRESSION)
+ || (operator.getRightPrec() <= rightPrec + 1 &&
isA(SqlKind.COMPARISON))) {
final SqlWriter.Frame frame = writer.startList("(", ")");
dialect.unparseCall(writer, this, 0, 0);
writer.endList(frame);
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index b5eaec86c9..73a6c3a0da 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -1423,6 +1423,7 @@ public enum SqlKind {
*
* <p>Consists of:
* {@link #IN},
+ * {@link #NOT_IN},
* {@link #EQUALS},
* {@link #NOT_EQUALS},
* {@link #LESS_THAN},
@@ -1432,7 +1433,7 @@ public enum SqlKind {
*/
public static final Set<SqlKind> COMPARISON =
EnumSet.of(
- IN, EQUALS, NOT_EQUALS,
+ IN, NOT_IN, EQUALS, NOT_EQUALS,
LESS_THAN, GREATER_THAN,
GREATER_THAN_OR_EQUAL, LESS_THAN_OR_EQUAL);
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 2ba73ae8d7..7d59542b73 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
@@ -1379,6 +1379,82 @@ class RelToSqlConverterTest {
.withPostgresql().ok(expectedPostgresql);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6436";>[CALCITE-6436]
+ * JDBC adapter generates SQL missing parentheses when comparing 3 values
with
+ * the same precedence like (a=b)=c</a>. */
+ @Test void testMissingParenthesesWithCondition1() {
+ final String query = "select \"product_id\" from \"foodmart\".\"product\"
where "
+ + "(\"product_id\" = 0) = (\"product_class_id\" = 0)";
+ final String expectedQuery = "SELECT \"product_id\"\nFROM
\"foodmart\".\"product\"\nWHERE "
+ + "(\"product_id\" = 0) = (\"product_class_id\" = 0)";
+ sql(query)
+ .ok(expectedQuery);
+ }
+
+ @Test void testMissingParenthesesWithCondition2() {
+ final String query = "select \"product_id\" from \"foodmart\".\"product\"
where"
+ + " (\"product_id\" = 0) in (select \"product_id\" = 0 from
\"foodmart\".\"product\")";
+ final String expectedQuery = "SELECT \"product_id\"\nFROM
\"foodmart\".\"product\"\n"
+ + "WHERE (\"product_id\" = 0) IN "
+ + "(SELECT \"product_id\" = 0\nFROM \"foodmart\".\"product\")";
+ sql(query)
+ .ok(expectedQuery);
+ }
+
+ @Test void testMissingParenthesesWithProject() {
+ final String query = "select (\"product_id\" = 0) = (\"product_class_id\"
= 0) "
+ + "from \"foodmart\".\"product\"";
+ final String expectedQuery = "SELECT (\"product_id\" = 0) =
(\"product_class_id\" = 0)\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(query)
+ .ok(expectedQuery);
+ }
+
+ @Test void testMissingParenthesesWithSubquery1() {
+ final String query = "select (\"product_id\" in "
+ + "(select \"product_class_id\" from \"foodmart\".\"product\")) in\n"
+ + " (select \"product_class_id\" = 0 from
\"foodmart\".\"product\")\n"
+ + "from \"foodmart\".\"product\"";
+ final String expectedQuery = "SELECT (\"product_id\" IN "
+ + "(SELECT \"product_class_id\"\nFROM \"foodmart\".\"product\")) "
+ + "IN (SELECT \"product_class_id\" = 0\nFROM
\"foodmart\".\"product\")\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(query)
+ .withConfig(c -> c.withExpand(false))
+ .ok(expectedQuery);
+ }
+
+ @Test void testMissingParenthesesWithSubquery2() {
+ final String query = "select (\"product_id\" not in "
+ + "(select \"product_class_id\" from \"foodmart\".\"product\")) in\n"
+ + " (select \"product_class_id\" = 0 from
\"foodmart\".\"product\")\n"
+ + "from \"foodmart\".\"product\"";
+ final String expectedQuery = "SELECT (\"product_id\" NOT IN "
+ + "(SELECT \"product_class_id\"\nFROM \"foodmart\".\"product\")) "
+ + "IN (SELECT \"product_class_id\" = 0\nFROM
\"foodmart\".\"product\")\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(query)
+ .withConfig(c -> c.withExpand(false))
+ .ok(expectedQuery);
+ }
+
+ @Test void testMissingParenthesesWithSubquery3() {
+ final String query = "select \"product_id\"\n"
+ + "from \"foodmart\".\"product\"\n"
+ + "where (\"product_id\" not in\n"
+ + " (select \"product_class_id\" from
\"foodmart\".\"product\"))\n"
+ + " in (select \"product_class_id\" = 0 from
\"foodmart\".\"product\")";
+ final String expectedQuery = "SELECT \"product_id\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "WHERE (\"product_id\" NOT IN "
+ + "(SELECT \"product_class_id\"\nFROM \"foodmart\".\"product\")) "
+ + "IN (SELECT \"product_class_id\" = 0\nFROM
\"foodmart\".\"product\")";
+ sql(query)
+ .withConfig(c -> c.withExpand(false))
+ .ok(expectedQuery);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-5955";>[CALCITE-5955]
* BigQuery PERCENTILE functions are unparsed incorrectly</a>. */
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
index fbcb1727f9..8cfee5377e 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -323,6 +323,21 @@ class JdbcAdapterTest {
+ "AND (\"t\".\"SAL\" > \"t0\".\"SAL\" OR \"t\".\"HIREDATE\" <
\"t0\".\"HIREDATE\")");
}
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6436";>[CALCITE-6436]
+ * JDBC adapter generates SQL missing parentheses when comparing 3 values
with
+ * the same precedence like (a=b)=c</a>. */
+ @Test void testMissingParentheses() {
+ CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
+ .query("select * from \"sales_fact_1997\" "
+ + "where (\"product_id\" = 1) = ?")
+ .consumesPreparedStatement(p -> p.setBoolean(1, true))
+ .returnsCount(26)
+ .planHasSql("SELECT *\nFROM \"foodmart\".\"sales_fact_1997\"\n"
+ + "WHERE (\"product_id\" = 1) = ?");
+ }
+
@Test void testJoin3TablesPlan() {
CalciteAssert.model(JdbcTest.SCOTT_MODEL)
.query("select empno, ename, dname, grade\n"
