This is an automated email from the ASF dual-hosted git repository.
dmsysolyatin 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 9007d3afad [CALCITE-5677] SUBSTR signature incorrect for BigQuery
9007d3afad is described below
commit 9007d3afad88070d4a66ec8391f03b4cd3adaa57
Author: Tanner Clary <[email protected]>
AuthorDate: Wed Apr 26 11:01:21 2023 -0700
[CALCITE-5677] SUBSTR signature incorrect for BigQuery
---
.../calcite/sql/dialect/ClickHouseSqlDialect.java | 54 +++++++++-------------
.../apache/calcite/sql/dialect/HiveSqlDialect.java | 16 -------
.../calcite/sql/fun/SqlSubstringFunction.java | 20 --------
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 20 +++++---
.../apache/calcite/sql/parser/SqlParserTest.java | 14 +++---
5 files changed, 42 insertions(+), 82 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
index 66743108f6..bd4b2a052c 100644
---
a/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
+++
b/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
@@ -30,11 +30,9 @@ import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlTimeLiteral;
import org.apache.calcite.sql.SqlTimestampLiteral;
import org.apache.calcite.sql.SqlWriter;
-import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.BasicSqlType;
import org.apache.calcite.sql.type.SqlTypeName;
-import org.apache.calcite.util.RelToSqlConverterUtil;
import org.checkerframework.checker.nullness.qual.Nullable;
@@ -155,38 +153,30 @@ public class ClickHouseSqlDialect extends SqlDialect {
@Override public void unparseCall(SqlWriter writer, SqlCall call,
int leftPrec, int rightPrec) {
- if (call.getOperator() == SqlStdOperatorTable.SUBSTRING) {
- RelToSqlConverterUtil.specialOperatorByName("substring")
- .unparse(writer, call, 0, 0);
- } else {
- switch (call.getKind()) {
- case FLOOR:
- if (call.operandCount() != 2) {
- super.unparseCall(writer, call, leftPrec, rightPrec);
- return;
- }
-
- unparseFloor(writer, call);
- break;
-
- case COUNT:
- // CH returns NULL rather than 0 for COUNT(DISTINCT) of NULL values.
- // https://github.com/yandex/ClickHouse/issues/2494
- // Wrap the call in a CH specific coalesce (assumeNotNull).
- if (call.getFunctionQuantifier() != null
- && call.getFunctionQuantifier().toString().equals("DISTINCT")) {
- writer.print("assumeNotNull");
- SqlWriter.Frame frame = writer.startList("(", ")");
- super.unparseCall(writer, call, leftPrec, rightPrec);
- writer.endList(frame);
- } else {
- super.unparseCall(writer, call, leftPrec, rightPrec);
- }
- break;
-
- default:
+ switch (call.getKind()) {
+ case FLOOR:
+ if (call.operandCount() != 2) {
super.unparseCall(writer, call, leftPrec, rightPrec);
+ return;
}
+ unparseFloor(writer, call);
+ break;
+ case COUNT:
+ // CH returns NULL rather than 0 for COUNT(DISTINCT) of NULL values.
+ // https://github.com/yandex/ClickHouse/issues/2494
+ // Wrap the call in a CH specific coalesce (assumeNotNull).
+ if (call.getFunctionQuantifier() != null
+ && call.getFunctionQuantifier().toString().equals("DISTINCT")) {
+ writer.print("assumeNotNull");
+ SqlWriter.Frame frame = writer.startList("(", ")");
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ writer.endList(frame);
+ } else {
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ }
+ break;
+ default:
+ super.unparseCall(writer, call, leftPrec, rightPrec);
}
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
index 3762972cf6..2feacf6a6a 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
@@ -27,7 +27,6 @@ import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlSyntax;
import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
-import org.apache.calcite.sql.fun.SqlSubstringFunction;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.BasicSqlType;
import org.apache.calcite.util.RelToSqlConverterUtil;
@@ -99,21 +98,6 @@ public class HiveSqlDialect extends SqlDialect {
case TRIM:
RelToSqlConverterUtil.unparseHiveTrim(writer, call, leftPrec, rightPrec);
break;
- case OTHER_FUNCTION:
- if (call.getOperator() instanceof SqlSubstringFunction) {
- final SqlWriter.Frame funCallFrame =
writer.startFunCall(call.getOperator().getName());
- call.operand(0).unparse(writer, leftPrec, rightPrec);
- writer.sep(",", true);
- call.operand(1).unparse(writer, leftPrec, rightPrec);
- if (3 == call.operandCount()) {
- writer.sep(",", true);
- call.operand(2).unparse(writer, leftPrec, rightPrec);
- }
- writer.endFunCall(funCallFrame);
- } else {
- super.unparseCall(writer, call, leftPrec, rightPrec);
- }
- break;
default:
super.unparseCall(writer, call, leftPrec, rightPrec);
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlSubstringFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlSubstringFunction.java
index ffbf4761fc..ddc6c9796b 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlSubstringFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlSubstringFunction.java
@@ -18,7 +18,6 @@ package org.apache.calcite.sql.fun;
import org.apache.calcite.linq4j.Ord;
import org.apache.calcite.rel.type.RelDataType;
-import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlCallBinding;
import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlFunctionCategory;
@@ -27,7 +26,6 @@ import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlOperandCountRange;
import org.apache.calcite.sql.SqlOperatorBinding;
import org.apache.calcite.sql.SqlUtil;
-import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.sql.type.SqlOperandCountRanges;
@@ -137,24 +135,6 @@ public class SqlSubstringFunction extends SqlFunction {
return SqlOperandCountRanges.between(2, 3);
}
- @Override public void unparse(
- SqlWriter writer,
- SqlCall call,
- int leftPrec,
- int rightPrec) {
- final SqlWriter.Frame frame = writer.startFunCall(getName());
- call.operand(0).unparse(writer, leftPrec, rightPrec);
- writer.sep("FROM");
- call.operand(1).unparse(writer, leftPrec, rightPrec);
-
- if (3 == call.operandCount()) {
- writer.sep("FOR");
- call.operand(2).unparse(writer, leftPrec, rightPrec);
- }
-
- writer.endFunCall(frame);
- }
-
@Override public SqlMonotonicity getMonotonicity(SqlOperatorBinding call) {
// SUBSTRING(x FROM 0 FOR constant) has same monotonicity as x
if (call.getOperandCount() == 3) {
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 f57c251a72..fc5ec3f03a 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
@@ -4444,20 +4444,23 @@ class RelToSqlConverterTest {
@Test void testSubstring() {
final String query = "select substring(\"brand_name\" from 2) "
+ "from \"product\"\n";
- final String expectedClickHouse = "SELECT substring(`brand_name`, 2)\n"
+ final String expectedBigQuery = "SELECT SUBSTRING(brand_name, 2)\n"
+ + "FROM foodmart.product";
+ final String expectedClickHouse = "SELECT SUBSTRING(`brand_name`, 2)\n"
+ "FROM `foodmart`.`product`";
final String expectedOracle = "SELECT SUBSTR(\"brand_name\", 2)\n"
+ "FROM \"foodmart\".\"product\"";
- final String expectedPostgresql = "SELECT SUBSTRING(\"brand_name\" FROM
2)\n"
+ final String expectedPostgresql = "SELECT SUBSTRING(\"brand_name\", 2)\n"
+ "FROM \"foodmart\".\"product\"";
final String expectedPresto = "SELECT SUBSTR(\"brand_name\", 2)\n"
+ "FROM \"foodmart\".\"product\"";
final String expectedSnowflake = expectedPostgresql;
final String expectedRedshift = expectedPostgresql;
final String expectedFirebolt = expectedPresto;
- final String expectedMysql = "SELECT SUBSTRING(`brand_name` FROM 2)\n"
+ final String expectedMysql = "SELECT SUBSTRING(`brand_name`, 2)\n"
+ "FROM `foodmart`.`product`";
sql(query)
+ .withBigQuery().ok(expectedBigQuery)
.withClickHouse().ok(expectedClickHouse)
.withFirebolt().ok(expectedFirebolt)
.withMssql()
@@ -4474,22 +4477,25 @@ class RelToSqlConverterTest {
@Test void testSubstringWithFor() {
final String query = "select substring(\"brand_name\" from 2 for 3) "
+ "from \"product\"\n";
- final String expectedClickHouse = "SELECT substring(`brand_name`, 2, 3)\n"
+ final String expectedBigQuery = "SELECT SUBSTRING(brand_name, 2, 3)\n"
+ + "FROM foodmart.product";
+ final String expectedClickHouse = "SELECT SUBSTRING(`brand_name`, 2, 3)\n"
+ "FROM `foodmart`.`product`";
final String expectedOracle = "SELECT SUBSTR(\"brand_name\", 2, 3)\n"
+ "FROM \"foodmart\".\"product\"";
- final String expectedPostgresql = "SELECT SUBSTRING(\"brand_name\" FROM 2
FOR 3)\n"
+ final String expectedPostgresql = "SELECT SUBSTRING(\"brand_name\", 2,
3)\n"
+ "FROM \"foodmart\".\"product\"";
final String expectedPresto = "SELECT SUBSTR(\"brand_name\", 2, 3)\n"
+ "FROM \"foodmart\".\"product\"";
final String expectedSnowflake = expectedPostgresql;
final String expectedRedshift = expectedPostgresql;
final String expectedFirebolt = expectedPresto;
- final String expectedMysql = "SELECT SUBSTRING(`brand_name` FROM 2 FOR
3)\n"
+ final String expectedMysql = "SELECT SUBSTRING(`brand_name`, 2, 3)\n"
+ "FROM `foodmart`.`product`";
final String expectedMssql = "SELECT SUBSTRING([brand_name], 2, 3)\n"
+ "FROM [foodmart].[product]";
sql(query)
+ .withBigQuery().ok(expectedBigQuery)
.withClickHouse().ok(expectedClickHouse)
.withFirebolt().ok(expectedFirebolt)
.withMysql().ok(expectedMysql)
@@ -5806,7 +5812,7 @@ class RelToSqlConverterTest {
final String sql = "SELECT SUBSTRING(y, 1, 1) FROM (" + sql0 + ") t";
final RuleSet rules =
RuleSets.ofList(PruneEmptyRules.SORT_FETCH_ZERO_INSTANCE);
- final String expected = "SELECT SUBSTRING(`Y` FROM 1 FOR 1)\n"
+ final String expected = "SELECT SUBSTRING(`Y`, 1, 1)\n"
+ "FROM (SELECT NULL AS `X`, NULL AS `Y`) AS `t`\n"
+ "WHERE 1 = 0";
sql(sql).optimize(rules, null).withMysql().ok(expected);
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 7e25f6aae6..30c326f732 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -2001,23 +2001,23 @@ public class SqlParserTest {
@Test void testSubstring() {
expr("substring('a'\nFROM \t 1)")
- .ok("SUBSTRING('a' FROM 1)");
+ .ok("SUBSTRING('a', 1)");
expr("substring('a' FROM 1 FOR 3)")
- .ok("SUBSTRING('a' FROM 1 FOR 3)");
+ .ok("SUBSTRING('a', 1, 3)");
expr("substring('a' FROM 'reg' FOR '\\')")
- .ok("SUBSTRING('a' FROM 'reg' FOR '\\')");
+ .ok("SUBSTRING('a', 'reg', '\\')");
expr("substring('a', 'reg', '\\')")
- .ok("SUBSTRING('a' FROM 'reg' FOR '\\')");
+ .ok("SUBSTRING('a', 'reg', '\\')");
expr("substring('a', 1, 2)")
- .ok("SUBSTRING('a' FROM 1 FOR 2)");
+ .ok("SUBSTRING('a', 1, 2)");
expr("substring('a' , 1)")
- .ok("SUBSTRING('a' FROM 1)");
+ .ok("SUBSTRING('a', 1)");
}
@Test void testFunction() {
sql("select substring('Eggs and ham', 1, 3 + 2) || ' benedict' from emp")
- .ok("SELECT (SUBSTRING('Eggs and ham' FROM 1 FOR (3 + 2)) || '
benedict')\n"
+ .ok("SELECT (SUBSTRING('Eggs and ham', 1, (3 + 2)) || ' benedict')\n"
+ "FROM `EMP`");
expr("log10(1)\r\n"
+ "+power(2, mod(\r\n"
