[CALCITE-4767] JDBC adapter wrongly quotes backticks inside BigQuery identifiers (Jack Scott)
Currently escapes a backtick with another backtick, but
should escape a backtick with a backslash. Add method
SqlDialect.Config.withIdentifierEscapedQuoteString() so
that this is configurable.
Close apache/calcite#2527
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 06d5de9..362f4fb 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -229,8 +229,11 @@
: identifierQuoteString.equals("[") ? "]"
: identifierQuoteString;
this.identifierEscapedQuote =
- identifierQuoteString == null ? null
- : this.identifierEndQuoteString + this.identifierEndQuoteString;
+ context.identifierEscapedQuoteString() == null
+ ? identifierQuoteString == null
+ ? null
+ : this.identifierEndQuoteString + this.identifierEndQuoteString
+ : context.identifierEscapedQuoteString();
this.unquotedCasing = Objects.requireNonNull(context.unquotedCasing());
this.quotedCasing = Objects.requireNonNull(context.quotedCasing());
this.caseSensitive = context.caseSensitive();
@@ -241,7 +244,7 @@
/** Creates an empty context. Use {@link #EMPTY_CONTEXT} to reference the instance. */
private static Context emptyContext() {
return new ContextImpl(DatabaseProduct.UNKNOWN, null, null, -1, -1,
- "'", "''", null,
+ "'", "''", null, null,
Casing.UNCHANGED, Casing.TO_UPPER, true, SqlConformanceEnum.DEFAULT,
NullCollation.HIGH, RelDataTypeSystemImpl.DEFAULT,
JethroDataSqlDialect.JethroInfo.EMPTY);
@@ -1374,6 +1377,9 @@
String literalEscapedQuoteString);
@Nullable String identifierQuoteString();
Context withIdentifierQuoteString(@Nullable String identifierQuoteString);
+ @Nullable String identifierEscapedQuoteString();
+ Context withIdentifierEscapedQuoteString(
+ @Nullable String identifierEscapedQuoteString);
Casing unquotedCasing();
Context withUnquotedCasing(Casing unquotedCasing);
Casing quotedCasing();
@@ -1400,6 +1406,7 @@
private final String literalQuoteString;
private final String literalEscapedQuoteString;
private final @Nullable String identifierQuoteString;
+ private final @Nullable String identifierEscapedQuoteString;
private final Casing unquotedCasing;
private final Casing quotedCasing;
private final boolean caseSensitive;
@@ -1412,8 +1419,9 @@
@Nullable String databaseProductName, @Nullable String databaseVersion,
int databaseMajorVersion, int databaseMinorVersion,
String literalQuoteString, String literalEscapedQuoteString,
- @Nullable String identifierQuoteString, Casing quotedCasing,
- Casing unquotedCasing, boolean caseSensitive,
+ @Nullable String identifierQuoteString,
+ @Nullable String identifierEscapedQuoteString,
+ Casing quotedCasing, Casing unquotedCasing, boolean caseSensitive,
SqlConformance conformance, NullCollation nullCollation,
RelDataTypeSystem dataTypeSystem,
JethroDataSqlDialect.JethroInfo jethroInfo) {
@@ -1425,6 +1433,7 @@
this.literalQuoteString = literalQuoteString;
this.literalEscapedQuoteString = literalEscapedQuoteString;
this.identifierQuoteString = identifierQuoteString;
+ this.identifierEscapedQuoteString = identifierEscapedQuoteString;
this.quotedCasing = Objects.requireNonNull(quotedCasing, "quotedCasing");
this.unquotedCasing = Objects.requireNonNull(unquotedCasing, "unquotedCasing");
this.caseSensitive = caseSensitive;
@@ -1443,7 +1452,8 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1455,7 +1465,8 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1467,7 +1478,8 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1479,7 +1491,8 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1491,7 +1504,8 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1503,7 +1517,8 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1516,7 +1531,8 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1529,7 +1545,22 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
+ conformance, nullCollation, dataTypeSystem, jethroInfo);
+ }
+
+ @Override public @Nullable String identifierEscapedQuoteString() {
+ return identifierEscapedQuoteString;
+ }
+
+ @Override public Context withIdentifierEscapedQuoteString(
+ @Nullable String identifierEscapedQuoteString) {
+ return new ContextImpl(databaseProduct, databaseProductName,
+ databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1541,7 +1572,8 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1553,7 +1585,8 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1565,7 +1598,8 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1577,7 +1611,8 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1590,7 +1625,8 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1602,7 +1638,8 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1614,7 +1651,8 @@
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
literalQuoteString, literalEscapedQuoteString,
- identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ identifierQuoteString, identifierEscapedQuoteString,
+ quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
}
diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
index 72b42e2..baeec1e 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
@@ -65,6 +65,7 @@
.withLiteralQuoteString("'")
.withLiteralEscapedQuoteString("\\'")
.withIdentifierQuoteString("`")
+ .withIdentifierEscapedQuoteString("\\`")
.withNullCollation(NullCollation.LOW)
.withUnquotedCasing(Casing.UNCHANGED)
.withQuotedCasing(Casing.UNCHANGED)
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 40d3c75..b87a0a5 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
@@ -1922,28 +1922,28 @@
final String query = "select *\n"
+ "from (\n"
+ " select 1 as \"one\", 2 as \"tWo\", 3 as \"THREE\",\n"
- + " 4 as \"fo$ur\", 5 as \"ignore\"\n"
+ + " 4 as \"fo$ur\", 5 as \"ignore\", 6 as \"si`x\"\n"
+ " from \"foodmart\".\"days\") as \"my$table\"\n"
+ "where \"one\" < \"tWo\" and \"THREE\" < \"fo$ur\"";
final String expectedBigQuery = "SELECT *\n"
+ "FROM (SELECT 1 AS one, 2 AS tWo, 3 AS THREE,"
- + " 4 AS `fo$ur`, 5 AS `ignore`\n"
+ + " 4 AS `fo$ur`, 5 AS `ignore`, 6 AS `si\\`x`\n"
+ "FROM foodmart.days) AS t\n"
+ "WHERE one < tWo AND THREE < `fo$ur`";
final String expectedMysql = "SELECT *\n"
+ "FROM (SELECT 1 AS `one`, 2 AS `tWo`, 3 AS `THREE`,"
- + " 4 AS `fo$ur`, 5 AS `ignore`\n"
+ + " 4 AS `fo$ur`, 5 AS `ignore`, 6 AS `si``x`\n"
+ "FROM `foodmart`.`days`) AS `t`\n"
+ "WHERE `one` < `tWo` AND `THREE` < `fo$ur`";
final String expectedPostgresql = "SELECT *\n"
+ "FROM (SELECT 1 AS \"one\", 2 AS \"tWo\", 3 AS \"THREE\","
- + " 4 AS \"fo$ur\", 5 AS \"ignore\"\n"
+ + " 4 AS \"fo$ur\", 5 AS \"ignore\", 6 AS \"si`x\"\n"
+ "FROM \"foodmart\".\"days\") AS \"t\"\n"
+ "WHERE \"one\" < \"tWo\" AND \"THREE\" < \"fo$ur\"";
final String expectedOracle = expectedPostgresql.replace(" AS ", " ");
final String expectedExasol = "SELECT *\n"
+ "FROM (SELECT 1 AS one, 2 AS tWo, 3 AS THREE,"
- + " 4 AS \"fo$ur\", 5 AS \"ignore\"\n"
+ + " 4 AS \"fo$ur\", 5 AS \"ignore\", 6 AS \"si`x\"\n"
+ "FROM foodmart.days) AS t\n"
+ "WHERE one < tWo AND THREE < \"fo$ur\"";
sql(query)
