You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by xi...@apache.org on 2024/06/08 22:12:06 UTC
(calcite) branch main updated: [CALCITE-6430] SINGLE_VALUE rewrite to wrong sql when the sub-query return one NOT-NULL value and NULL value in PostgreSQL、MySQL、HSQL dialect
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 fb15511e76 [CALCITE-6430] SINGLE_VALUE rewrite to wrong sql when the sub-query return one NOT-NULL value and NULL value in PostgreSQL、MySQL、HSQL dialect
fb15511e76 is described below
commit fb15511e76c660cbd440578421645ebe63941bf7
Author: Xiong Duan <no...@gmail.com>
AuthorDate: Fri Jun 7 21:01:29 2024 +0800
[CALCITE-6430] SINGLE_VALUE rewrite to wrong sql when the sub-query return one NOT-NULL value and NULL value in PostgreSQL、MySQL、HSQL dialect
---
.../apache/calcite/sql/dialect/HsqldbSqlDialect.java | 6 +++++-
.../apache/calcite/sql/dialect/MysqlSqlDialect.java | 4 +++-
.../calcite/sql/dialect/PostgresqlSqlDialect.java | 12 ++++++++----
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 14 +++++++++++---
core/src/test/resources/sql/some.iq | 18 ++++++++++++++++++
5 files changed, 45 insertions(+), 9 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/HsqldbSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/HsqldbSqlDialect.java
index 30f4436d03..e5b4791358 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/HsqldbSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/HsqldbSqlDialect.java
@@ -21,6 +21,7 @@ import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlDialect;
+import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
@@ -30,6 +31,8 @@ import org.apache.calcite.sql.fun.SqlFloorFunction;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
+import com.google.common.collect.ImmutableList;
+
import org.checkerframework.checker.nullness.qual.Nullable;
/**
@@ -101,7 +104,8 @@ public class HsqldbSqlDialect extends SqlDialect {
// END
final SqlNode caseExpr =
new SqlCase(SqlParserPos.ZERO,
- SqlStdOperatorTable.COUNT.createCall(SqlParserPos.ZERO, operand),
+ SqlStdOperatorTable.COUNT.createCall(SqlParserPos.ZERO,
+ ImmutableList.of(SqlIdentifier.STAR)),
SqlNodeList.of(
SqlLiteral.createExactNumeric("0", SqlParserPos.ZERO),
SqlLiteral.createExactNumeric("1", SqlParserPos.ZERO)),
diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
index f86f0b0dfc..fc2a7a1b05 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
@@ -32,6 +32,7 @@ import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlDataTypeSpec;
import org.apache.calcite.sql.SqlDialect;
import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
@@ -205,7 +206,8 @@ public class MysqlSqlDialect extends SqlDialect {
// END
final SqlNode caseExpr =
new SqlCase(SqlParserPos.ZERO,
- SqlStdOperatorTable.COUNT.createCall(SqlParserPos.ZERO, operand),
+ SqlStdOperatorTable.COUNT.createCall(SqlParserPos.ZERO,
+ ImmutableList.of(SqlIdentifier.STAR)),
SqlNodeList.of(
SqlLiteral.createExactNumeric("0", SqlParserPos.ZERO),
SqlLiteral.createExactNumeric("1", SqlParserPos.ZERO)),
diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
index 7071286c59..627b3c79a4 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
@@ -26,6 +26,7 @@ import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlDataTypeSpec;
import org.apache.calcite.sql.SqlDialect;
+import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
@@ -38,6 +39,8 @@ import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.SqlTypeName;
+import com.google.common.collect.ImmutableList;
+
import org.checkerframework.checker.nullness.qual.Nullable;
import java.util.List;
@@ -114,14 +117,15 @@ public class PostgresqlSqlDialect extends SqlDialect {
SqlNodeList.of(castNonNull(getCastSpec(relDataType))))), null, null, null, null,
SqlNodeList.EMPTY, null, null, null, null, SqlNodeList.EMPTY);
// For PostgreSQL, generate
- // CASE COUNT(value)
+ // CASE COUNT(*)
// WHEN 0 THEN NULL
- // WHEN 1 THEN min(value)
- // ELSE (SELECT CAST(NULL AS valueDataType) UNION ALL SELECT CAST(NULL AS valueDataType))
+ // WHEN 1 THEN MIN(<result>)
+ // ELSE (SELECT CAST(NULL AS resultDataType) UNION ALL SELECT CAST(NULL AS resultDataType))
// END
final SqlNode caseExpr =
new SqlCase(SqlParserPos.ZERO,
- SqlStdOperatorTable.COUNT.createCall(SqlParserPos.ZERO, operand),
+ SqlStdOperatorTable.COUNT.createCall(SqlParserPos.ZERO,
+ ImmutableList.of(SqlIdentifier.STAR)),
SqlNodeList.of(
SqlLiteral.createExactNumeric("0", SqlParserPos.ZERO),
SqlLiteral.createExactNumeric("1", SqlParserPos.ZERO)),
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 60285257b9..69532ef02d 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
@@ -5105,7 +5105,7 @@ class RelToSqlConverterTest {
+ "from \"product\" where \"net_weight\" > (select \"product_class_id\" from \"product\")";
final String expectedMysql = "SELECT `product`.`product_class_id` AS `C`\n"
+ "FROM `foodmart`.`product`\n"
- + "LEFT JOIN (SELECT CASE COUNT(`product_class_id`) "
+ + "LEFT JOIN (SELECT CASE COUNT(*) "
+ "WHEN 0 THEN NULL WHEN 1 THEN `product_class_id` ELSE (SELECT NULL\n"
+ "UNION ALL\n"
+ "SELECT NULL) END AS `$f0`\n"
@@ -5113,15 +5113,23 @@ class RelToSqlConverterTest {
+ "WHERE `product`.`net_weight` > `t0`.`$f0`";
final String expectedPostgresql = "SELECT \"product\".\"product_class_id\" AS \"C\"\n"
+ "FROM \"foodmart\".\"product\"\n"
- + "LEFT JOIN (SELECT CASE COUNT(\"product_class_id\") WHEN 0 THEN NULL WHEN 1 THEN MIN(\"product_class_id\") ELSE (SELECT CAST(NULL AS INTEGER)\n"
+ + "LEFT JOIN (SELECT CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN MIN(\"product_class_id\") ELSE (SELECT CAST(NULL AS INTEGER)\n"
+ "UNION ALL\n"
+ "SELECT CAST(NULL AS INTEGER)) END AS \"$f0\"\n"
+ "FROM \"foodmart\".\"product\") AS \"t0\" ON TRUE\n"
+ "WHERE \"product\".\"net_weight\" > \"t0\".\"$f0\"";
+ final String expectedHsqldb = "SELECT product.product_class_id AS C\n"
+ + "FROM foodmart.product\n"
+ + "LEFT JOIN (SELECT CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN MIN(product_class_id) ELSE ((VALUES 0E0)\n"
+ + "UNION ALL\n"
+ + "(VALUES 0E0)) END AS $f0\n"
+ + "FROM foodmart.product) AS t0 ON TRUE\n"
+ + "WHERE product.net_weight > t0.$f0";
sql(query)
.withConfig(c -> c.withExpand(true))
.withMysql().ok(expectedMysql)
- .withPostgresql().ok(expectedPostgresql);
+ .withPostgresql().ok(expectedPostgresql)
+ .withHsqldb().ok(expectedHsqldb);
}
@Test void testLike() {
diff --git a/core/src/test/resources/sql/some.iq b/core/src/test/resources/sql/some.iq
index 3c1638b374..d8ce6cdb7a 100644
--- a/core/src/test/resources/sql/some.iq
+++ b/core/src/test/resources/sql/some.iq
@@ -554,6 +554,24 @@ where sal > (select sal * 2 from "scott".emp);
more than one value in agg SINGLE_VALUE
!error
+# If sub-query return one NO-NULL value and NULL value, sub-query will throw exception.
+select * from "scott".emp
+where comm > (select comm from "scott".emp where comm = 300.00 or comm is null);
+more than one value in agg SINGLE_VALUE
+!error
+
+select * from "scott".emp
+where comm > (select comm from "scott".emp where comm = 300.00);
++-------+--------+----------+------+------------+---------+---------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+--------+----------+------+------------+---------+---------+--------+
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
++-------+--------+----------+------+------------+---------+---------+--------+
+(2 rows)
+
+!ok
+
select * from "scott".emp
where sal > any (select sal * 2 from "scott".emp)
order by sal desc;