This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 4908264ee5 [CALCITE-6480] OracleDialect does not support CASE WHEN
returning boolean
4908264ee5 is described below
commit 4908264ee55b689b3f36b15d26fc231edd25d123
Author: suibianwanwan <[email protected]>
AuthorDate: Tue Aug 6 22:25:46 2024 +0800
[CALCITE-6480] OracleDialect does not support CASE WHEN returning boolean
---
.../apache/calcite/rel/rel2sql/SqlImplementor.java | 25 ++++++++++
.../java/org/apache/calcite/sql/SqlDialect.java | 5 ++
.../calcite/sql/dialect/OracleSqlDialect.java | 4 ++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 56 ++++++++++++++++++++++
4 files changed, 90 insertions(+)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index 23ca30e952..63335d12f3 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -726,6 +726,31 @@ public abstract class SqlImplementor {
}
}
elseNode = caseNodeList.get(caseNodeList.size() - 1);
+
+ if (caseCall.getType().getSqlTypeName() == SqlTypeName.BOOLEAN
+ && !dialect.supportBooleanCaseWhen()) {
+ // Transformed when expressions of boolean type in SqlCase
+ // For example, given
+ // CASE WHEN x > 1 THEN y > 1 ELSE y < 10 END
+ // Transformed:
+ // (CASE WHEN x > 1 THEN (CASE WHEN y > 1 THEN 1 ELSE 0 END)
+ // ELSE (CASE WHEN y < 10 THEN 1 ELSE 0 END) END) = 1
+ final List<SqlNode> thenList2 = thenList.stream()
+ .map(
+ thenNode -> new SqlCase(POS, null, SqlNodeList.of(thenNode),
+ SqlNodeList.of(ONE), SqlNodeList.of(ZERO)))
+ .collect(SqlNode.toList());
+ final SqlNode elseNode2 =
+ new SqlCase(POS, null, SqlNodeList.of(elseNode),
+ SqlNodeList.of(ONE), SqlNodeList.of(ZERO));
+
+ final SqlCase sqlCase =
+ new SqlCase(POS, valueNode,
+ new SqlNodeList(whenList, POS),
+ new SqlNodeList(thenList2, POS), elseNode2);
+ return SqlStdOperatorTable.EQUALS.createCall(POS, sqlCase, ONE);
+ }
+
return new SqlCase(POS, valueNode, new SqlNodeList(whenList, POS),
new SqlNodeList(thenList, POS), elseNode);
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
index 3d937581c5..9ebc4c383c 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -784,6 +784,11 @@ public class SqlDialect {
return true;
}
+ /** Returns whether this dialect supports case when return boolean type. */
+ public boolean supportBooleanCaseWhen() {
+ return true;
+ }
+
/** Returns whether this dialect supports a given function or operator.
* It only applies to built-in scalar functions and operators, since
* user-defined functions and procedures should be read by JdbcSchema. */
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java
index 4314438d6c..75ce64286b 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java
@@ -85,6 +85,10 @@ public class OracleSqlDialect extends SqlDialect {
return false;
}
+ @Override public boolean supportBooleanCaseWhen() {
+ return majorVersion >= 23;
+ }
+
@Override public boolean supportsDataType(RelDataType type) {
switch (type.getSqlTypeName()) {
case BOOLEAN:
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 811427d809..bf80bd8fee 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
@@ -7775,6 +7775,62 @@ class RelToSqlConverterTest {
.withOracle(11).ok(expectedVersionLow);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6480";>[CALCITE-6480]
+ * OracleDialect does not support CASE WHEN returning boolean</a>. */
+ @Test void testBooleanCaseWhenOracle() {
+ String query0 = "SELECT \"e1\".\"department_id\" "
+ + "FROM \"employee\" \"e1\""
+ + "LEFT JOIN \"employee\" \"e2\""
+ + "ON CASE WHEN \"e2\".\"employee_id\" = 'a' "
+ + "THEN \"e1\".\"department_id\" > 10 "
+ + "WHEN \"e2\".\"employee_id\" = 'b' "
+ + "THEN \"e1\".\"department_id\" > 20 "
+ + "ELSE \"e2\".\"employee_id\" = 'c' END";
+ String expectedVersionLow0 = "SELECT \"employee\".\"department_id\"\n"
+ + "FROM \"foodmart\".\"employee\"\n"
+ + "LEFT JOIN \"foodmart\".\"employee\" \"employee0\" "
+ + "ON CASE WHEN \"employee0\".\"employee_id\" = 'a' "
+ + "THEN CASE WHEN \"employee\".\"department_id\" > 10 "
+ + "THEN 1 ELSE 0 END WHEN \"employee0\".\"employee_id\" = 'b' "
+ + "THEN CASE WHEN \"employee\".\"department_id\" > 20 "
+ + "THEN 1 ELSE 0 END ELSE CASE WHEN \"employee0\".\"employee_id\" =
'c' "
+ + "THEN 1 ELSE 0 END END = 1";
+ String expectedVersionHigh0 = "SELECT \"employee\".\"department_id\"\n"
+ + "FROM \"foodmart\".\"employee\"\n"
+ + "LEFT JOIN \"foodmart\".\"employee\" \"employee0\" "
+ + "ON CASE WHEN \"employee0\".\"employee_id\" = 'a' "
+ + "THEN \"employee\".\"department_id\" > 10 "
+ + "WHEN \"employee0\".\"employee_id\" = 'b' "
+ + "THEN \"employee\".\"department_id\" > 20"
+ + " ELSE \"employee0\".\"employee_id\" = 'c' END";
+
+ String query1 = "SELECT \"department_id\" "
+ + "FROM \"employee\""
+ + "WHERE CASE \"employee_id\" "
+ + "WHEN 'a' THEN \"department_id\" > 10 "
+ + "WHEN 'b' THEN \"department_id\" > 20 "
+ + "ELSE TRUE END";
+ String expectedVersionLow1 = "SELECT \"department_id\"\n"
+ + "FROM \"foodmart\".\"employee\"\n"
+ + "WHERE CASE WHEN \"employee_id\" = 'a' THEN CASE WHEN
\"department_id\" > 10 THEN 1 ELSE 0 END "
+ + "WHEN \"employee_id\" = 'b' THEN CASE WHEN \"department_id\" > 20
THEN 1 ELSE 0 END ELSE "
+ + "CASE WHEN (1 = 1) THEN 1 ELSE 0 END END = 1";
+ String expectedVersionHigh1 = "SELECT \"department_id\"\n"
+ + "FROM \"foodmart\".\"employee\"\n"
+ + "WHERE CASE WHEN \"employee_id\" = 'a' THEN \"department_id\" > 10 "
+ + "WHEN \"employee_id\" = 'b' THEN \"department_id\" > 20 "
+ + "ELSE TRUE END";
+
+ sql(query0)
+ .withOracle(23).ok(expectedVersionHigh0)
+ .withOracle(11).ok(expectedVersionLow0);
+
+ sql(query1)
+ .withOracle(23).ok(expectedVersionHigh1)
+ .withOracle(11).ok(expectedVersionLow1);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-5265";>[CALCITE-5265]
* JDBC adapter sometimes adds unnecessary parentheses around SELECT in
INSERT</a>. */
