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 <[email protected]> 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;
