You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2019/08/04 04:15:28 UTC
[calcite] 03/07: [CALCITE-3147] In JDBC adapter,
accommodate the idiosyncrasies of how BigQuery (standard SQL)
quotes character literals and identifiers
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit ac06e64fed6865949207aabd4ef839418589b6e3
Author: Julian Hyde <jh...@apache.org>
AuthorDate: Thu Jun 13 00:37:07 2019 -0700
[CALCITE-3147] In JDBC adapter, accommodate the idiosyncrasies of how BigQuery (standard SQL) quotes character literals and identifiers
Make literalQuoteString and literalEscapedQuoteString configurable in SqlDialect.
---
.../apache/calcite/sql/SqlCharStringLiteral.java | 11 +-
.../java/org/apache/calcite/sql/SqlDialect.java | 159 ++++++++++++++++-----
.../calcite/sql/dialect/BigQuerySqlDialect.java | 53 +++++++
.../calcite/sql/fun/SqlLiteralChainOperator.java | 4 +-
.../java/org/apache/calcite/util/NlsString.java | 20 +--
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 101 ++++++++++++-
6 files changed, 293 insertions(+), 55 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlCharStringLiteral.java b/core/src/main/java/org/apache/calcite/sql/SqlCharStringLiteral.java
index c3846f1..d7d7b10 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlCharStringLiteral.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlCharStringLiteral.java
@@ -27,8 +27,8 @@ import java.util.List;
/**
* A character string literal.
*
- * <p>Its {@link #value} field is an {@link NlsString} and {@link #typeName} is
- * {@link SqlTypeName#CHAR}.
+ * <p>Its {@link #value} field is an {@link NlsString} and
+ * {@link #getTypeName typeName} is {@link SqlTypeName#CHAR}.
*/
public class SqlCharStringLiteral extends SqlAbstractStringLiteral {
@@ -62,14 +62,15 @@ public class SqlCharStringLiteral extends SqlAbstractStringLiteral {
SqlWriter writer,
int leftPrec,
int rightPrec) {
+ assert value instanceof NlsString;
+ final NlsString nlsString = (NlsString) this.value;
if (false) {
Util.discard(Bug.FRG78_FIXED);
- String stringValue = ((NlsString) value).getValue();
+ String stringValue = nlsString.getValue();
writer.literal(
writer.getDialect().quoteStringLiteral(stringValue));
}
- assert value instanceof NlsString;
- writer.literal(value.toString());
+ writer.literal(nlsString.asSql(true, true, writer.getDialect()));
}
protected SqlAbstractStringLiteral concat1(List<SqlLiteral> literals) {
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 3eab428..237f8b3 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -134,9 +134,12 @@ public class SqlDialect {
//~ Instance fields --------------------------------------------------------
- private final String identifierQuoteString;
- private final String identifierEndQuoteString;
- private final String identifierEscapedQuote;
+ protected final String identifierQuoteString;
+ protected final String identifierEndQuoteString;
+ protected final String identifierEscapedQuote;
+ protected final String literalQuoteString;
+ protected final String literalEndQuoteString;
+ protected final String literalEscapedQuote;
private final DatabaseProduct databaseProduct;
protected final NullCollation nullCollation;
private final RelDataTypeSystem dataTypeSystem;
@@ -203,6 +206,12 @@ public class SqlDialect {
this.dataTypeSystem = Objects.requireNonNull(context.dataTypeSystem());
this.databaseProduct =
Objects.requireNonNull(context.databaseProduct());
+ this.literalQuoteString =
+ Objects.requireNonNull(context.literalQuoteString());
+ this.literalEndQuoteString =
+ Objects.requireNonNull(context.literalQuoteString());
+ this.literalEscapedQuote =
+ Objects.requireNonNull(context.literalEscapedQuoteString());
String identifierQuoteString = context.identifierQuoteString();
if (identifierQuoteString != null) {
identifierQuoteString = identifierQuoteString.trim();
@@ -227,7 +236,8 @@ public class SqlDialect {
/** Creates an empty context. Use {@link #EMPTY_CONTEXT} if possible. */
protected static Context emptyContext() {
- return new ContextImpl(DatabaseProduct.UNKNOWN, null, null, -1, -1, null,
+ return new ContextImpl(DatabaseProduct.UNKNOWN, null, null, -1, -1,
+ "'", "''", null,
Casing.UNCHANGED, Casing.TO_UPPER, true, SqlConformanceEnum.DEFAULT,
NullCollation.HIGH, RelDataTypeSystemImpl.DEFAULT,
JethroDataSqlDialect.JethroInfo.EMPTY);
@@ -348,17 +358,18 @@ public class SqlDialect {
public StringBuilder quoteIdentifier(
StringBuilder buf,
String val) {
- if (identifierQuoteString == null) {
- buf.append(val); // quoting is not supported
- return buf;
+ if (identifierQuoteString == null // quoting is not supported
+ || !identifierNeedsQuote(val)) {
+ buf.append(val);
+ } else {
+ String val2 =
+ val.replaceAll(
+ identifierEndQuoteString,
+ identifierEscapedQuote);
+ buf.append(identifierQuoteString);
+ buf.append(val2);
+ buf.append(identifierEndQuoteString);
}
- String val2 =
- val.replaceAll(
- identifierEndQuoteString,
- identifierEscapedQuote);
- buf.append(identifierQuoteString);
- buf.append(val2);
- buf.append(identifierEndQuoteString);
return buf;
}
@@ -382,18 +393,41 @@ public class SqlDialect {
return buf;
}
+ /** Returns whether to quote an identifier.
+ * By default, all identifiers are quoted. */
+ protected boolean identifierNeedsQuote(String val) {
+ return true;
+ }
+
/**
- * Converts a string into a string literal. For example, <code>can't
- * run</code> becomes <code>'can''t run'</code>.
+ * Converts a string into a string literal.
+ *
+ * <p>For example, {@code "can't run"} becomes {@code "'can''t run'"}.
*/
- public String quoteStringLiteral(String val) {
- if (containsNonAscii(val)) {
- final StringBuilder buf = new StringBuilder();
+ public final String quoteStringLiteral(String val) {
+ final StringBuilder buf = new StringBuilder();
+ quoteStringLiteral(buf, null, val);
+ return buf.toString();
+ }
+
+ /** Appends a string literal to a buffer.
+ *
+ * @param buf Buffer
+ * @param charsetName Character set name, e.g. "utf16", or null
+ * @param val String value
+ */
+ public void quoteStringLiteral(StringBuilder buf, String charsetName,
+ String val) {
+ if (containsNonAscii(val) && charsetName == null) {
quoteStringLiteralUnicode(buf, val);
- return buf.toString();
} else {
- val = FakeUtil.replace(val, "'", "''");
- return "'" + val + "'";
+ if (charsetName != null) {
+ buf.append("_");
+ buf.append(charsetName);
+ }
+ buf.append(literalQuoteString);
+ buf.append(val.replace(literalEndQuoteString, literalEscapedQuote));
+ buf.append(literalEndQuoteString);
}
}
@@ -486,15 +520,18 @@ public class SqlDialect {
/**
* Returns whether the string contains any characters outside the
- * comfortable 7-bit ASCII range (32 through 127).
+ * comfortable 7-bit ASCII range (32 through 127, plus linefeed (10) and
+ * carriage return (13)).
+ *
+ * <p>Such characters can be used unquoted in SQL character literals.
*
* @param s String
* @return Whether string contains any non-7-bit-ASCII characters
*/
- private static boolean containsNonAscii(String s) {
+ protected static boolean containsNonAscii(String s) {
for (int i = 0; i < s.length(); i++) {
char c = s.charAt(i);
- if (c < 32 || c >= 128) {
+ if (c < 32 && c != 10 && c != 13 || c >= 128) {
return true;
}
}
@@ -535,16 +572,13 @@ public class SqlDialect {
* run'</code> becomes <code>can't run</code>.
*/
public String unquoteStringLiteral(String val) {
- if ((val != null)
- && (val.charAt(0) == '\'')
- && (val.charAt(val.length() - 1) == '\'')) {
- if (val.length() > 2) {
- val = FakeUtil.replace(val, "''", "'");
- return val.substring(1, val.length() - 1);
- } else {
- // zero length string
- return "";
- }
+ if (val != null
+ && val.startsWith(literalQuoteString)
+ && val.endsWith(literalEndQuoteString)) {
+ final String stripped =
+ val.substring(literalQuoteString.length(),
+ val.length() - literalEndQuoteString.length());
+ return stripped.replace(literalEscapedQuote, literalEndQuoteString);
}
return val;
}
@@ -1041,6 +1075,7 @@ public class SqlDialect {
* copied them because we wish to keep SqlDialect's dependencies to a
* minimum.
*/
+ @Deprecated // to be removed before 2.0
public static class FakeUtil {
public static Error newInternal(Throwable e, String s) {
String message = "Internal error: \u0000" + s;
@@ -1200,6 +1235,11 @@ public class SqlDialect {
Context withDatabaseMajorVersion(int databaseMajorVersion);
int databaseMinorVersion();
Context withDatabaseMinorVersion(int databaseMinorVersion);
+ @Nonnull String literalQuoteString();
+ @Nonnull Context withLiteralQuoteString(String literalQuoteString);
+ @Nonnull String literalEscapedQuoteString();
+ @Nonnull Context withLiteralEscapedQuoteString(
+ String literalEscapedQuoteString);
String identifierQuoteString();
@Nonnull Context withIdentifierQuoteString(String identifierQuoteString);
@Nonnull Casing unquotedCasing();
@@ -1225,6 +1265,8 @@ public class SqlDialect {
private final String databaseVersion;
private final int databaseMajorVersion;
private final int databaseMinorVersion;
+ private final String literalQuoteString;
+ private final String literalEscapedQuoteString;
private final String identifierQuoteString;
private final Casing unquotedCasing;
private final Casing quotedCasing;
@@ -1237,6 +1279,7 @@ public class SqlDialect {
private ContextImpl(DatabaseProduct databaseProduct,
String databaseProductName, String databaseVersion,
int databaseMajorVersion, int databaseMinorVersion,
+ String literalQuoteString, String literalEscapedQuoteString,
String identifierQuoteString, Casing quotedCasing,
Casing unquotedCasing, boolean caseSensitive,
SqlConformance conformance, NullCollation nullCollation,
@@ -1247,6 +1290,8 @@ public class SqlDialect {
this.databaseVersion = databaseVersion;
this.databaseMajorVersion = databaseMajorVersion;
this.databaseMinorVersion = databaseMinorVersion;
+ this.literalQuoteString = literalQuoteString;
+ this.literalEscapedQuoteString = literalEscapedQuoteString;
this.identifierQuoteString = identifierQuoteString;
this.quotedCasing = Objects.requireNonNull(quotedCasing);
this.unquotedCasing = Objects.requireNonNull(unquotedCasing);
@@ -1265,10 +1310,9 @@ public class SqlDialect {
@Nonnull DatabaseProduct databaseProduct) {
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
- identifierQuoteString, quotedCasing, unquotedCasing,
- caseSensitive,
- conformance, nullCollation,
- dataTypeSystem, jethroInfo);
+ literalQuoteString, literalEscapedQuoteString,
+ identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ conformance, nullCollation, dataTypeSystem, jethroInfo);
}
public String databaseProductName() {
@@ -1278,6 +1322,7 @@ public class SqlDialect {
public Context withDatabaseProductName(String databaseProductName) {
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1289,6 +1334,7 @@ public class SqlDialect {
public Context withDatabaseVersion(String databaseVersion) {
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1300,6 +1346,7 @@ public class SqlDialect {
public Context withDatabaseMajorVersion(int databaseMajorVersion) {
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1311,6 +1358,32 @@ public class SqlDialect {
public Context withDatabaseMinorVersion(int databaseMinorVersion) {
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
+ identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ conformance, nullCollation, dataTypeSystem, jethroInfo);
+ }
+
+ public String literalQuoteString() {
+ return literalQuoteString;
+ }
+
+ public Context withLiteralQuoteString(String literalQuoteString) {
+ return new ContextImpl(databaseProduct, databaseProductName,
+ databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
+ identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
+ conformance, nullCollation, dataTypeSystem, jethroInfo);
+ }
+
+ public String literalEscapedQuoteString() {
+ return literalEscapedQuoteString;
+ }
+
+ public Context withLiteralEscapedQuoteString(
+ String literalEscapedQuoteString) {
+ return new ContextImpl(databaseProduct, databaseProductName,
+ databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1323,6 +1396,7 @@ public class SqlDialect {
String identifierQuoteString) {
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1334,6 +1408,7 @@ public class SqlDialect {
@Nonnull public Context withUnquotedCasing(Casing unquotedCasing) {
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1345,6 +1420,7 @@ public class SqlDialect {
@Nonnull public Context withQuotedCasing(Casing quotedCasing) {
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1356,6 +1432,7 @@ public class SqlDialect {
@Nonnull public Context withCaseSensitive(boolean caseSensitive) {
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1367,6 +1444,7 @@ public class SqlDialect {
@Nonnull public Context withConformance(SqlConformance conformance) {
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1379,6 +1457,7 @@ public class SqlDialect {
@Nonnull NullCollation nullCollation) {
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1390,6 +1469,7 @@ public class SqlDialect {
public Context withDataTypeSystem(@Nonnull RelDataTypeSystem dataTypeSystem) {
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
identifierQuoteString, quotedCasing, unquotedCasing, caseSensitive,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
@@ -1401,6 +1481,7 @@ public class SqlDialect {
public Context withJethroInfo(JethroDataSqlDialect.JethroInfo jethroInfo) {
return new ContextImpl(databaseProduct, databaseProductName,
databaseVersion, databaseMajorVersion, databaseMinorVersion,
+ literalQuoteString, literalEscapedQuoteString,
identifierQuoteString, 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 07881cf..9475296 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
@@ -20,11 +20,19 @@ import org.apache.calcite.config.NullCollation;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlDialect;
import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlSetOperator;
import org.apache.calcite.sql.SqlSyntax;
import org.apache.calcite.sql.SqlWriter;
+import com.google.common.collect.ImmutableList;
+
+import java.util.Arrays;
+import java.util.List;
+import java.util.Locale;
+import java.util.regex.Pattern;
+
/**
* A <code>SqlDialect</code> implementation for Google BigQuery's "Standard SQL"
* dialect.
@@ -34,13 +42,58 @@ public class BigQuerySqlDialect extends SqlDialect {
new BigQuerySqlDialect(
EMPTY_CONTEXT
.withDatabaseProduct(SqlDialect.DatabaseProduct.BIG_QUERY)
+ .withLiteralQuoteString("'")
+ .withLiteralEscapedQuoteString("\\'")
+ .withIdentifierQuoteString("`")
.withNullCollation(NullCollation.LOW));
+ private static final List<String> RESERVED_KEYWORDS =
+ ImmutableList.copyOf(
+ Arrays.asList("ALL", "AND", "ANY", "ARRAY", "AS", "ASC",
+ "ASSERT_ROWS_MODIFIED", "AT", "BETWEEN", "BY", "CASE", "CAST",
+ "COLLATE", "CONTAINS", "CREATE", "CROSS", "CUBE", "CURRENT",
+ "DEFAULT", "DEFINE", "DESC", "DISTINCT", "ELSE", "END", "ENUM",
+ "ESCAPE", "EXCEPT", "EXCLUDE", "EXISTS", "EXTRACT", "FALSE",
+ "FETCH", "FOLLOWING", "FOR", "FROM", "FULL", "GROUP", "GROUPING",
+ "GROUPS", "HASH", "HAVING", "IF", "IGNORE", "IN", "INNER",
+ "INTERSECT", "INTERVAL", "INTO", "IS", "JOIN", "LATERAL", "LEFT",
+ "LIKE", "LIMIT", "LOOKUP", "MERGE", "NATURAL", "NEW", "NO",
+ "NOT", "NULL", "NULLS", "OF", "ON", "OR", "ORDER", "OUTER",
+ "OVER", "PARTITION", "PRECEDING", "PROTO", "RANGE", "RECURSIVE",
+ "RESPECT", "RIGHT", "ROLLUP", "ROWS", "SELECT", "SET", "SOME",
+ "STRUCT", "TABLESAMPLE", "THEN", "TO", "TREAT", "TRUE",
+ "UNBOUNDED", "UNION", "UNNEST", "USING", "WHEN", "WHERE",
+ "WINDOW", "WITH", "WITHIN"));
+
+ /** An unquoted BigQuery identifier must start with a letter and be followed
+ * by zero or more letters, digits or _. */
+ private static final Pattern IDENTIFIER_REGEX =
+ Pattern.compile("[A-Za-z][A-Za-z0-9_]*");
+
/** Creates a BigQuerySqlDialect. */
public BigQuerySqlDialect(SqlDialect.Context context) {
super(context);
}
+ @Override public String quoteIdentifier(String val) {
+ return quoteIdentifier(new StringBuilder(), val).toString();
+ }
+
+ @Override protected boolean identifierNeedsQuote(String val) {
+ return !IDENTIFIER_REGEX.matcher(val).matches()
+ || RESERVED_KEYWORDS.contains(val.toUpperCase(Locale.ROOT));
+ }
+
+ @Override public SqlNode emulateNullDirection(SqlNode node,
+ boolean nullsFirst, boolean desc) {
+ return emulateNullDirectionWithIsNull(node, nullsFirst, desc);
+ }
+
+ @Override public void unparseOffsetFetch(SqlWriter writer, SqlNode offset,
+ SqlNode fetch) {
+ unparseFetchUsingLimit(writer, offset, fetch);
+ }
+
@Override public void unparseCall(final SqlWriter writer, final SqlCall call, final int leftPrec,
final int rightPrec) {
switch (call.getKind()) {
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLiteralChainOperator.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLiteralChainOperator.java
index fc0a241..b1750c5 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLiteralChainOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLiteralChainOperator.java
@@ -171,10 +171,10 @@ public class SqlLiteralChainOperator extends SqlSpecialOperator {
collation = nls.getCollation();
// print with prefix
- writer.literal(nls.asSql(true, false));
+ writer.literal(nls.asSql(true, false, writer.getDialect()));
} else {
// print without prefix
- writer.literal(nls.asSql(false, false));
+ writer.literal(nls.asSql(false, false, writer.getDialect()));
}
} else if (operand.i == 0) {
// print with prefix
diff --git a/core/src/main/java/org/apache/calcite/util/NlsString.java b/core/src/main/java/org/apache/calcite/util/NlsString.java
index fd41811..8c67274 100644
--- a/core/src/main/java/org/apache/calcite/util/NlsString.java
+++ b/core/src/main/java/org/apache/calcite/util/NlsString.java
@@ -19,7 +19,9 @@ package org.apache.calcite.util;
import org.apache.calcite.avatica.util.ByteString;
import org.apache.calcite.runtime.SqlFunctions;
import org.apache.calcite.sql.SqlCollation;
+import org.apache.calcite.sql.SqlDialect;
import org.apache.calcite.sql.SqlUtil;
+import org.apache.calcite.sql.dialect.AnsiSqlDialect;
import com.google.common.cache.CacheBuilder;
import com.google.common.cache.CacheLoader;
@@ -206,25 +208,27 @@ public class NlsString implements Comparable<NlsString>, Cloneable {
return this;
}
+ /** As {@link #asSql(boolean, boolean, SqlDialect)} but with SQL standard
+ * dialect. */
+ public String asSql(boolean prefix, boolean suffix) {
+ return asSql(prefix, suffix, AnsiSqlDialect.DEFAULT);
+ }
+
/**
* Returns the string quoted for SQL, for example <code>_ISO-8859-1'is it a
* plane? no it''s superman!'</code>.
*
* @param prefix if true, prefix the character set name
* @param suffix if true, suffix the collation clause
+ * @param dialect Dialect
* @return the quoted string
*/
public String asSql(
boolean prefix,
- boolean suffix) {
+ boolean suffix,
+ SqlDialect dialect) {
StringBuilder ret = new StringBuilder();
- if (prefix && (null != charsetName)) {
- ret.append("_");
- ret.append(charsetName);
- }
- ret.append("'");
- ret.append(Util.replace(getValue(), "'", "''"));
- ret.append("'");
+ dialect.quoteStringLiteral(ret, prefix ? charsetName : null, getValue());
// NOTE jvs 3-Feb-2005: see FRG-78 for why this should go away
if (false) {
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 a1fcc8b..eea7962 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
@@ -63,10 +63,12 @@ import org.apache.calcite.tools.RuleSets;
import org.apache.calcite.util.TestUtil;
import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
import org.junit.Test;
import java.util.List;
+import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
@@ -136,6 +138,33 @@ public class RelToSqlConverterTest {
.withNullCollation(nullCollation));
}
+ /** Returns a collection of common dialects, and the database products they
+ * represent. */
+ private static Map<SqlDialect, DatabaseProduct> dialects() {
+ return ImmutableMap.<SqlDialect, DatabaseProduct>builder()
+ .put(SqlDialect.DatabaseProduct.BIG_QUERY.getDialect(),
+ SqlDialect.DatabaseProduct.BIG_QUERY)
+ .put(SqlDialect.DatabaseProduct.CALCITE.getDialect(),
+ SqlDialect.DatabaseProduct.CALCITE)
+ .put(SqlDialect.DatabaseProduct.DB2.getDialect(),
+ SqlDialect.DatabaseProduct.DB2)
+ .put(SqlDialect.DatabaseProduct.HIVE.getDialect(),
+ SqlDialect.DatabaseProduct.HIVE)
+ .put(jethroDataSqlDialect(),
+ SqlDialect.DatabaseProduct.JETHRO)
+ .put(SqlDialect.DatabaseProduct.MSSQL.getDialect(),
+ SqlDialect.DatabaseProduct.MSSQL)
+ .put(SqlDialect.DatabaseProduct.MYSQL.getDialect(),
+ SqlDialect.DatabaseProduct.MYSQL)
+ .put(mySqlDialect(NullCollation.HIGH),
+ SqlDialect.DatabaseProduct.MYSQL)
+ .put(SqlDialect.DatabaseProduct.ORACLE.getDialect(),
+ SqlDialect.DatabaseProduct.ORACLE)
+ .put(SqlDialect.DatabaseProduct.POSTGRESQL.getDialect(),
+ SqlDialect.DatabaseProduct.POSTGRESQL)
+ .build();
+ }
+
/** Creates a RelBuilder. */
private static RelBuilder relBuilder() {
return RelBuilder.create(RelBuilderTest.config().build());
@@ -852,6 +881,50 @@ public class RelToSqlConverterTest {
sql(query).withBigQuery().ok(expected);
}
+ /** Tests that we escape single-quotes in character literals using back-slash
+ * in BigQuery. The norm is to escape single-quotes with single-quotes. */
+ @Test public void testCharLiteralForBigQuery() {
+ final String query = "select 'that''s all folks!' from \"product\"";
+ final String expectedPostgresql = "SELECT 'that''s all folks!'\n"
+ + "FROM \"foodmart\".\"product\"";
+ final String expectedBigQuery = "SELECT 'that\\'s all folks!'\n"
+ + "FROM foodmart.product";
+ sql(query)
+ .withPostgresql().ok(expectedPostgresql)
+ .withBigQuery().ok(expectedBigQuery);
+ }
+
+ @Test public void testIdentifier() {
+ // Note that IGNORE is reserved in BigQuery but not in standard SQL
+ 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"
+ + " 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"
+ + "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"
+ + "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"
+ + "FROM \"foodmart\".\"days\") AS \"t\"\n"
+ + "WHERE \"one\" < \"tWo\" AND \"THREE\" < \"fo$ur\"";
+ final String expectedOracle = expectedPostgresql.replaceAll(" AS ", " ");
+ sql(query)
+ .withBigQuery().ok(expectedBigQuery)
+ .withMysql().ok(expectedMysql)
+ .withOracle().ok(expectedOracle)
+ .withPostgresql().ok(expectedPostgresql);
+ }
+
@Test public void testModFunctionForHive() {
final String query = "select mod(11,3) from \"product\"";
final String expected = "SELECT 11 % 3\n"
@@ -1149,7 +1222,14 @@ public class RelToSqlConverterTest {
+ "ORDER BY \"net_weight\"\n"
+ "OFFSET 10 ROWS\n"
+ "FETCH NEXT 100 ROWS ONLY";
- sql(query).ok(expected);
+ // BigQuery uses LIMIT/OFFSET, and nulls sort low by default
+ final String expectedBigQuery = "SELECT product_id, net_weight\n"
+ + "FROM foodmart.product\n"
+ + "ORDER BY net_weight IS NULL, net_weight\n"
+ + "LIMIT 100\n"
+ + "OFFSET 10";
+ sql(query).ok(expected)
+ .withBigQuery().ok(expectedBigQuery);
}
@Test public void testSelectQueryWithParameters() {
@@ -3561,6 +3641,25 @@ public class RelToSqlConverterTest {
assertTrue(postgresqlDialect.supportsDataType(integerDataType));
}
+ @Test public void testDialectQuoteStringLiteral() {
+ dialects().forEach((dialect, databaseProduct) -> {
+ assertThat(dialect.quoteStringLiteral(""), is("''"));
+ assertThat(dialect.quoteStringLiteral("can't run"),
+ databaseProduct == DatabaseProduct.BIG_QUERY
+ ? is("'can\\'t run'")
+ : is("'can''t run'"));
+
+ assertThat(dialect.unquoteStringLiteral("''"), is(""));
+ if (databaseProduct == DatabaseProduct.BIG_QUERY) {
+ assertThat(dialect.unquoteStringLiteral("'can\\'t run'"),
+ is("can't run"));
+ } else {
+ assertThat(dialect.unquoteStringLiteral("'can't run'"),
+ is("can't run"));
+ }
+ });
+ }
+
/** Fluid interface to run tests. */
static class Sql {
private final SchemaPlus schema;