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 0ab1fe3118cc5adb8750a5c785e24fa4381afda1 Author: Julian Hyde <[email protected]> AuthorDate: Tue Dec 8 13:01:39 2020 -0800 [CALCITE-4427] Make SUBSTRING operator comply with ISO standard SQL If you want the old behavior (which was consistent with BigQuery but not with ISO standard SQL), set conformance=BIG_QUERY in your connection properties. In SqlConformance, add method semantics() that controls the semantics of standard functions. The default is STANDARD. In BIG_QUERY semantics, the SUBSTRING function treats negative start values differently. Add implementations of SUBSTR for BigQuery, MySQL, PostgreSQL (we already have Oracle). Each have slightly different behavior, consistent with their DBs of origin. PostgreSQL's SUBSTR is consistent with ISO standard SQL's SUBSTRING. Unlike SUBSTRING, the behavior of your SUBSTR function depends on which operator table you got it from. For example, if you specify fun=bigquery in your connection properties, your SUBSTR function will be consistent with BigQuery. Add function OCTET_LENGTH, the BINARY equivalent of CHAR_LENGTH. --- babel/src/test/resources/sql/redshift.iq | 2 +- .../calcite/adapter/enumerable/RexImpTable.java | 6 +- .../adapter/enumerable/RexToLixTranslator.java | 22 +- .../calcite/config/CalciteConnectionProperty.java | 17 +- .../org/apache/calcite/runtime/SqlFunctions.java | 59 +-- .../main/java/org/apache/calcite/sql/SqlKind.java | 12 + .../calcite/sql/dialect/OracleSqlDialect.java | 2 +- .../calcite/sql/fun/OracleSqlOperatorTable.java | 2 +- .../calcite/sql/fun/SqlInternalOperators.java | 13 + .../calcite/sql/fun/SqlLibraryOperators.java | 28 +- .../calcite/sql/fun/SqlStdOperatorTable.java | 10 + .../org/apache/calcite/sql/type/OperandTypes.java | 4 + .../sql/validate/SqlAbstractConformance.java | 7 + .../calcite/sql/validate/SqlConformance.java | 27 ++ .../calcite/sql/validate/SqlConformanceEnum.java | 16 + .../sql/validate/SqlDelegatingConformance.java | 6 + .../calcite/sql2rel/StandardConvertletTable.java | 193 ++++++++-- .../org/apache/calcite/util/BuiltInMethod.java | 2 + .../calcite/sql/test/SqlOperatorBaseTest.java | 394 +++++++++++++++------ site/_docs/reference.md | 3 +- 20 files changed, 636 insertions(+), 189 deletions(-) diff --git a/babel/src/test/resources/sql/redshift.iq b/babel/src/test/resources/sql/redshift.iq index f6e4ee4..5cfc279 100755 --- a/babel/src/test/resources/sql/redshift.iq +++ b/babel/src/test/resources/sql/redshift.iq @@ -1805,7 +1805,7 @@ f7415e33f972c03abd4f3fed36748f7a # OCTET_LENGTH -- returns 9 (cf LENGTH) select octet_length('français'); -SELECT "OCTET_LENGTH"(u&'fran\00e7ais') +SELECT OCTET_LENGTH(CAST(u&'fran\00e7ais' AS VARBINARY)) !explain-validated-on calcite # POSITION is a synonym for STRPOS diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java index 27feddf..eebac14 100644 --- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java +++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java @@ -267,6 +267,7 @@ import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NOT_EQUALS; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NOT_SUBMULTISET_OF; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NTH_VALUE; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NTILE; +import static org.apache.calcite.sql.fun.SqlStdOperatorTable.OCTET_LENGTH; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.OR; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.OVERLAY; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.PI; @@ -357,9 +358,12 @@ public class RexImpTable { NullPolicy.STRICT); defineMethod(CHAR_LENGTH, BuiltInMethod.CHAR_LENGTH.method, NullPolicy.STRICT); + defineMethod(OCTET_LENGTH, BuiltInMethod.OCTET_LENGTH.method, + NullPolicy.STRICT); defineMethod(CONCAT, BuiltInMethod.STRING_CONCAT.method, NullPolicy.STRICT); - defineMethod(CONCAT_FUNCTION, BuiltInMethod.MULTI_STRING_CONCAT.method, NullPolicy.STRICT); + defineMethod(CONCAT_FUNCTION, BuiltInMethod.MULTI_STRING_CONCAT.method, + NullPolicy.STRICT); defineMethod(CONCAT2, BuiltInMethod.STRING_CONCAT.method, NullPolicy.STRICT); defineMethod(OVERLAY, BuiltInMethod.OVERLAY.method, NullPolicy.STRICT); defineMethod(POSITION, BuiltInMethod.POSITION.method, NullPolicy.STRICT); diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java index 983830e..01574dc 100644 --- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java +++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java @@ -50,7 +50,6 @@ import org.apache.calcite.rex.RexUtil; import org.apache.calcite.rex.RexVisitor; import org.apache.calcite.runtime.GeoFunctions; import org.apache.calcite.runtime.Geometries; -import org.apache.calcite.runtime.SqlFunctions; import org.apache.calcite.sql.SqlIntervalQualifier; import org.apache.calcite.sql.SqlOperator; import org.apache.calcite.sql.SqlWindowTableFunction; @@ -63,6 +62,7 @@ import org.apache.calcite.util.Pair; import org.apache.calcite.util.Util; import com.google.common.collect.ImmutableList; +import com.google.common.collect.ImmutableMap; import org.checkerframework.checker.nullness.qual.Nullable; @@ -77,8 +77,8 @@ import java.util.Map; import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CASE; -import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CHARACTER_LENGTH; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CHAR_LENGTH; +import static org.apache.calcite.sql.fun.SqlStdOperatorTable.OCTET_LENGTH; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.PREV; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SEARCH; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SUBSTRING; @@ -92,17 +92,13 @@ import static java.util.Objects.requireNonNull; */ public class RexToLixTranslator implements RexVisitor<RexToLixTranslator.Result> { public static final Map<Method, SqlOperator> JAVA_TO_SQL_METHOD_MAP = - Util.mapOf( - findMethod(String.class, "toUpperCase"), UPPER, - findMethod( - SqlFunctions.class, "substring", String.class, Integer.TYPE, - Integer.TYPE), SUBSTRING, - findMethod(SqlFunctions.class, "charLength", String.class), - CHARACTER_LENGTH, - findMethod(SqlFunctions.class, "charLength", String.class), - CHAR_LENGTH, - findMethod(SqlFunctions.class, "translate3", String.class, String.class, - String.class), TRANSLATE3); + ImmutableMap.<Method, SqlOperator>builder() + .put(findMethod(String.class, "toUpperCase"), UPPER) + .put(BuiltInMethod.SUBSTRING.method, SUBSTRING) + .put(BuiltInMethod.OCTET_LENGTH.method, OCTET_LENGTH) + .put(BuiltInMethod.CHAR_LENGTH.method, CHAR_LENGTH) + .put(BuiltInMethod.TRANSLATE3.method, TRANSLATE3) + .build(); final JavaTypeFactory typeFactory; final RexBuilder builder; diff --git a/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java b/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java index 6d0b8c4..d195c15 100644 --- a/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java +++ b/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java @@ -151,7 +151,22 @@ public enum CalciteConnectionProperty implements ConnectionProperty { * default constructor or an {@code INSTANCE} constant. */ TYPE_SYSTEM("typeSystem", Type.PLUGIN, null, false), - /** SQL conformance level. */ + /** SQL conformance level. + * + * <p>Controls the semantics of ISO standard SQL features that are implemented + * in non-standard ways in some other systems. + * + * <p>For example, the {@code SUBSTRING(string FROM start [FOR length])} + * operator treats negative {@code start} values as 1, but BigQuery's + * implementation regards negative {@code starts} as counting from the end. + * If {@code conformance=BIG_QUERY} we will use BigQuery's behavior. + * + * <p>This property only affects ISO standard SQL features. For example, the + * {@code SUBSTR} function is non-standard, so is controlled by the + * {@link #FUN fun} property. If you set {@code fun=oracle} you will get + * {@code SUBSTR} with Oracle's semantics; if you set {@code fun=postgres} you + * will get {@code SUBSTR} with PostgreSQL's (slightly different) + * semantics. */ CONFORMANCE("conformance", Type.ENUM, SqlConformanceEnum.DEFAULT, false), /** Whether to make implicit type coercion when type mismatch diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java index 455c6cc..1632e1e 100644 --- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java +++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java @@ -257,50 +257,58 @@ public class SqlFunctions { return flags; } + /** SQL SUBSTRING(string FROM ...) function. */ + public static String substring(String c, int s) { + final int s0 = s - 1; + if (s0 <= 0) { + return c; + } + if (s > c.length()) { + return ""; + } + return c.substring(s0); + } + /** SQL SUBSTRING(string FROM ... FOR ...) function. */ public static String substring(String c, int s, int l) { int lc = c.length(); - if (s < 0) { - s += lc + 1; - } int e = s + l; - if (e < s) { + if (l < 0) { throw RESOURCE.illegalNegativeSubstringLength().ex(); } if (s > lc || e < 1) { return ""; } - int s1 = Math.max(s, 1); - int e1 = Math.min(e, lc + 1); - return c.substring(s1 - 1, e1 - 1); + final int s0 = Math.max(s - 1, 0); + final int e0 = Math.min(e - 1, lc); + return c.substring(s0, e0); } - /** SQL SUBSTRING(string FROM ...) function. */ - public static String substring(String c, int s) { - return substring(c, s, c.length() + 1); + /** SQL SUBSTRING(binary FROM ...) function for binary. */ + public static ByteString substring(ByteString c, int s) { + final int s0 = s - 1; + if (s0 <= 0) { + return c; + } + if (s > c.length()) { + return ByteString.EMPTY; + } + return c.substring(s0); } - /** SQL SUBSTRING(binary FROM ... FOR ...) function. */ + /** SQL SUBSTRING(binary FROM ... FOR ...) function for binary. */ public static ByteString substring(ByteString c, int s, int l) { int lc = c.length(); - if (s < 0) { - s += lc + 1; - } int e = s + l; - if (e < s) { + if (l < 0) { throw RESOURCE.illegalNegativeSubstringLength().ex(); } if (s > lc || e < 1) { return ByteString.EMPTY; } - int s1 = Math.max(s, 1); - int e1 = Math.min(e, lc + 1); - return c.substring(s1 - 1, e1 - 1); - } - - /** SQL SUBSTRING(binary FROM ...) function. */ - public static ByteString substring(ByteString c, int s) { - return substring(c, s, c.length() + 1); + final int s0 = Math.max(s - 1, 0); + final int e0 = Math.min(e - 1, lc); + return c.substring(s0, e0); } /** SQL UPPER(string) function. */ @@ -450,6 +458,11 @@ public class SqlFunctions { return String.valueOf(Character.toChars((int) n)); } + /** SQL OCTET_LENGTH(binary) function. */ + public static int octetLength(ByteString s) { + return s.length(); + } + /** SQL CHARACTER_LENGTH(string) function. */ public static int charLength(String s) { return s.length(); diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java b/core/src/main/java/org/apache/calcite/sql/SqlKind.java index 6c42c41..d737fb4 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java @@ -626,6 +626,18 @@ public enum SqlKind { /** {@code REVERSE} function (SQL Server, MySQL). */ REVERSE, + /** {@code SUBSTR} function (BigQuery semantics). */ + SUBSTR_BIG_QUERY, + + /** {@code SUBSTR} function (MySQL semantics). */ + SUBSTR_MYSQL, + + /** {@code SUBSTR} function (Oracle semantics). */ + SUBSTR_ORACLE, + + /** {@code SUBSTR} function (PostgreSQL semantics). */ + SUBSTR_POSTGRESQL, + /** Call to a function using JDBC function syntax. */ JDBC_FN, diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java index 9b1f4e4..b98b284 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java @@ -142,7 +142,7 @@ public class OracleSqlDialect extends SqlDialect { @Override public void unparseCall(SqlWriter writer, SqlCall call, int leftPrec, int rightPrec) { if (call.getOperator() == SqlStdOperatorTable.SUBSTRING) { - SqlUtil.unparseFunctionSyntax(SqlLibraryOperators.ORACLE_SUBSTR, writer, + SqlUtil.unparseFunctionSyntax(SqlLibraryOperators.SUBSTR_ORACLE, writer, call, false); } else { switch (call.getKind()) { diff --git a/core/src/main/java/org/apache/calcite/sql/fun/OracleSqlOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/OracleSqlOperatorTable.java index d39a1ad..dcd1860 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/OracleSqlOperatorTable.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/OracleSqlOperatorTable.java @@ -50,7 +50,7 @@ public class OracleSqlOperatorTable extends ReflectiveSqlOperatorTable { public static final SqlFunction RTRIM = SqlLibraryOperators.RTRIM; @Deprecated // to be removed before 2.0 - public static final SqlFunction SUBSTR = SqlLibraryOperators.ORACLE_SUBSTR; + public static final SqlFunction SUBSTR = SqlLibraryOperators.SUBSTR_ORACLE; @Deprecated // to be removed before 2.0 public static final SqlFunction GREATEST = SqlLibraryOperators.GREATEST; diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java index 3cae3a0..e22b179 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java @@ -18,11 +18,15 @@ package org.apache.calcite.sql.fun; import org.apache.calcite.rex.RexCall; import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlFunction; +import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.SqlNodeList; import org.apache.calcite.sql.SqlOperatorTable; import org.apache.calcite.sql.SqlWriter; +import org.apache.calcite.sql.type.OperandTypes; +import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.util.Litmus; import org.checkerframework.checker.nullness.qual.Nullable; @@ -99,4 +103,13 @@ public abstract class SqlInternalOperators { return litmus.succeed(); } }; + + /** All implementations of {@code SUBSTRING} and {@code SUBSTR} map onto + * this. */ + // TODO: + public static final SqlFunction SUBSTRING_INTERNAL = + new SqlFunction("$SUBSTRING", SqlKind.OTHER_FUNCTION, + ReturnTypes.ARG0_NULLABLE_VARYING, null, + OperandTypes.STRING_INTEGER_INTEGER, SqlFunctionCategory.SYSTEM); + } diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java index f42e05f..d5cdecc 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java @@ -155,6 +155,22 @@ public abstract class SqlLibraryOperators { .andThen(SqlTypeTransforms.TO_VARYING), null, OperandTypes.STRING, SqlFunctionCategory.STRING); + /** BigQuery's "SUBSTR(string, position [, substringLength ])" function. */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction SUBSTR_BIG_QUERY = + new SqlFunction("SUBSTR", SqlKind.SUBSTR_BIG_QUERY, + ReturnTypes.ARG0_NULLABLE_VARYING, null, + OperandTypes.STRING_INTEGER_OPTIONAL_INTEGER, + SqlFunctionCategory.STRING); + + /** MySQL's "SUBSTR(string, position [, substringLength ])" function. */ + @LibraryOperator(libraries = {MYSQL}) + public static final SqlFunction SUBSTR_MYSQL = + new SqlFunction("SUBSTR", SqlKind.SUBSTR_MYSQL, + ReturnTypes.ARG0_NULLABLE_VARYING, null, + OperandTypes.STRING_INTEGER_OPTIONAL_INTEGER, + SqlFunctionCategory.STRING); + /** Oracle's "SUBSTR(string, position [, substringLength ])" function. * * <p>It has different semantics to standard SQL's @@ -170,8 +186,16 @@ public abstract class SqlLibraryOperators { * </ul> */ @LibraryOperator(libraries = {ORACLE}) - public static final SqlFunction ORACLE_SUBSTR = - new SqlFunction("SUBSTR", SqlKind.OTHER_FUNCTION, + public static final SqlFunction SUBSTR_ORACLE = + new SqlFunction("SUBSTR", SqlKind.SUBSTR_ORACLE, + ReturnTypes.ARG0_NULLABLE_VARYING, null, + OperandTypes.STRING_INTEGER_OPTIONAL_INTEGER, + SqlFunctionCategory.STRING); + + /** PostgreSQL's "SUBSTR(string, position [, substringLength ])" function. */ + @LibraryOperator(libraries = {POSTGRESQL}) + public static final SqlFunction SUBSTR_POSTGRESQL = + new SqlFunction("SUBSTR", SqlKind.SUBSTR_POSTGRESQL, ReturnTypes.ARG0_NULLABLE_VARYING, null, OperandTypes.STRING_INTEGER_OPTIONAL_INTEGER, SqlFunctionCategory.STRING); diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java index b00885e..1c6e635 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java @@ -1518,6 +1518,7 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { OperandTypes.CHARACTER, SqlFunctionCategory.NUMERIC); + /** Alias for {@link #CHAR_LENGTH}. */ public static final SqlFunction CHARACTER_LENGTH = new SqlFunction( "CHARACTER_LENGTH", @@ -1527,6 +1528,15 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { OperandTypes.CHARACTER, SqlFunctionCategory.NUMERIC); + public static final SqlFunction OCTET_LENGTH = + new SqlFunction( + "OCTET_LENGTH", + SqlKind.OTHER_FUNCTION, + ReturnTypes.INTEGER_NULLABLE, + null, + OperandTypes.BINARY, + SqlFunctionCategory.NUMERIC); + public static final SqlFunction UPPER = new SqlFunction( "UPPER", diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java index 4020de6..9596bde 100644 --- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java +++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java @@ -442,6 +442,10 @@ public abstract class OperandTypes { public static final SqlSingleOperandTypeChecker STRING_INTEGER = family(SqlTypeFamily.STRING, SqlTypeFamily.INTEGER); + public static final SqlSingleOperandTypeChecker STRING_INTEGER_INTEGER = + family(SqlTypeFamily.STRING, SqlTypeFamily.INTEGER, + SqlTypeFamily.INTEGER); + public static final SqlSingleOperandTypeChecker STRING_INTEGER_OPTIONAL_INTEGER = family( ImmutableList.of(SqlTypeFamily.STRING, SqlTypeFamily.INTEGER, diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java index 11c7691..92d0889 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java @@ -16,6 +16,8 @@ */ package org.apache.calcite.sql.validate; +import org.apache.calcite.sql.fun.SqlLibrary; + /** * Abstract base class for implementing {@link SqlConformance}. * @@ -126,4 +128,9 @@ public abstract class SqlAbstractConformance implements SqlConformance { @Override public boolean allowAliasUnnestItems() { return SqlConformanceEnum.DEFAULT.allowAliasUnnestItems(); } + + @Override public SqlLibrary semantics() { + return SqlConformanceEnum.DEFAULT.semantics(); + } + } diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java index e971319..15902fb 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java @@ -16,6 +16,8 @@ */ package org.apache.calcite.sql.validate; +import org.apache.calcite.sql.fun.SqlLibrary; + /** * Enumeration of valid SQL compatibility modes. * @@ -502,4 +504,29 @@ public interface SqlConformance { */ boolean allowQualifyingCommonColumn(); + /** + * Controls the behavior of operators that are part of Standard SQL but + * nevertheless have different behavior in different databases. + * + * <p>Consider the {@code SUBSTRING} operator. In ISO standard SQL, negative + * start indexes are converted to 1; in Google BigQuery, negative start + * indexes are treated as offsets from the end of the string. For example, + * {@code SUBSTRING('abcde' FROM -3 FOR 2)} returns {@code 'ab'} in standard + * SQL and 'cd' in BigQuery. + * + * <p>If you specify {@code conformance=BIG_QUERY} in your connection + * parameters, {@code SUBSTRING} will give the BigQuery behavior. Similarly + * MySQL and Oracle. + * + * <p>Among the built-in conformance levels: + * <ul> + * <li>{@link SqlConformanceEnum#BIG_QUERY} returns + * {@link SqlLibrary#BIG_QUERY}; + * <li>{@link SqlConformanceEnum#MYSQL_5} returns {@link SqlLibrary#MYSQL}; + * <li>{@link SqlConformanceEnum#ORACLE_10} and + * {@link SqlConformanceEnum#ORACLE_12} return {@link SqlLibrary#ORACLE}; + * <li>otherwise returns {@link SqlLibrary#STANDARD}. + * </ul> + */ + SqlLibrary semantics(); } diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java index d8b255b..aaeda48 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java @@ -16,6 +16,8 @@ */ package org.apache.calcite.sql.validate; +import org.apache.calcite.sql.fun.SqlLibrary; + /** * Enumeration of built-in SQL compatibility modes. */ @@ -391,4 +393,18 @@ public enum SqlConformanceEnum implements SqlConformance { } } + @Override public SqlLibrary semantics() { + switch (this) { + case BIG_QUERY: + return SqlLibrary.BIG_QUERY; + case MYSQL_5: + return SqlLibrary.MYSQL; + case ORACLE_12: + case ORACLE_10: + return SqlLibrary.ORACLE; + default: + return SqlLibrary.STANDARD; + } + } + } diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java index 3a166fd..8cc4255 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java @@ -16,6 +16,8 @@ */ package org.apache.calcite.sql.validate; +import org.apache.calcite.sql.fun.SqlLibrary; + /** * Implementation of {@link SqlConformance} that delegates all methods to * another object. You can create a sub-class that overrides particular @@ -77,4 +79,8 @@ public class SqlDelegatingConformance extends SqlAbstractConformance { return delegate.allowAliasUnnestItems(); } + @Override public SqlLibrary semantics() { + return delegate.semantics(); + } + } diff --git a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java index 51df784..1d23d8a 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java @@ -31,6 +31,7 @@ import org.apache.calcite.rex.RexRangeRef; import org.apache.calcite.rex.RexUtil; import org.apache.calcite.runtime.SqlFunctions; import org.apache.calcite.sql.SqlAggFunction; +import org.apache.calcite.sql.SqlBasicCall; import org.apache.calcite.sql.SqlBinaryOperator; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlDataTypeSpec; @@ -54,6 +55,7 @@ import org.apache.calcite.sql.fun.SqlCase; import org.apache.calcite.sql.fun.SqlDatetimeSubtractionOperator; import org.apache.calcite.sql.fun.SqlExtractFunction; import org.apache.calcite.sql.fun.SqlJsonValueFunction; +import org.apache.calcite.sql.fun.SqlLibrary; import org.apache.calcite.sql.fun.SqlLibraryOperators; import org.apache.calcite.sql.fun.SqlLiteralChainOperator; import org.apache.calcite.sql.fun.SqlMapValueConstructor; @@ -63,6 +65,7 @@ import org.apache.calcite.sql.fun.SqlOverlapsOperator; import org.apache.calcite.sql.fun.SqlRowOperator; import org.apache.calcite.sql.fun.SqlSequenceValueOperator; import org.apache.calcite.sql.fun.SqlStdOperatorTable; +import org.apache.calcite.sql.fun.SqlSubstringFunction; import org.apache.calcite.sql.fun.SqlTrimFunction; import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.type.SqlOperandTypeChecker; @@ -74,6 +77,7 @@ import org.apache.calcite.sql.validate.SqlValidatorImpl; import org.apache.calcite.util.Pair; import org.apache.calcite.util.Util; +import com.google.common.base.Preconditions; import com.google.common.collect.ImmutableList; import org.checkerframework.checker.initialization.qual.UnknownInitialization; @@ -145,7 +149,14 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { registerOp(SqlLibraryOperators.GREATEST, new GreatestConvertlet()); registerOp(SqlLibraryOperators.LEAST, new GreatestConvertlet()); - registerOp(SqlLibraryOperators.ORACLE_SUBSTR, new OracleSubstrConvertlet()); + registerOp(SqlLibraryOperators.SUBSTR_BIG_QUERY, + new SubstrConvertlet(SqlLibrary.BIG_QUERY)); + registerOp(SqlLibraryOperators.SUBSTR_MYSQL, + new SubstrConvertlet(SqlLibrary.MYSQL)); + registerOp(SqlLibraryOperators.SUBSTR_ORACLE, + new SubstrConvertlet(SqlLibrary.ORACLE)); + registerOp(SqlLibraryOperators.SUBSTR_POSTGRESQL, + new SubstrConvertlet(SqlLibrary.POSTGRESQL)); registerOp(SqlLibraryOperators.NVL, StandardConvertletTable::convertNvl); registerOp(SqlLibraryOperators.DECODE, @@ -1046,6 +1057,38 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { } /** + * Converts a SUBSTRING expression. + * + * <p>Called automatically via reflection. + */ + public RexNode convertSubstring( + SqlRexContext cx, + SqlSubstringFunction op, + SqlCall call) { + final SqlLibrary library = + cx.getValidator().config().sqlConformance().semantics(); + final SqlBasicCall basicCall = (SqlBasicCall) call; + switch (library) { + case BIG_QUERY: + return toRex(cx, basicCall, SqlLibraryOperators.SUBSTR_BIG_QUERY); + case MYSQL: + return toRex(cx, basicCall, SqlLibraryOperators.SUBSTR_MYSQL); + case ORACLE: + return toRex(cx, basicCall, SqlLibraryOperators.SUBSTR_ORACLE); + case POSTGRESQL: + default: + return convertFunction(cx, op, call); + } + } + + private RexNode toRex(SqlRexContext cx, SqlBasicCall call, SqlFunction f) { + final SqlCall call2 = + new SqlBasicCall(f, call.operands, call.getParserPosition()); + final SqlRexConvertlet convertlet = requireNonNull(get(call2)); + return convertlet.convertCall(cx, call2); + } + + /** * Converts a LiteralChain expression: that is, concatenates the operands * immediately, to produce a single literal string. * @@ -1543,20 +1586,67 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { } } - /** Convertlet that handles Oracle's {@code SUBSTR} function. */ - private static class OracleSubstrConvertlet implements SqlRexConvertlet { + /** Convertlet that handles the {@code SUBSTR} function; various dialects + * have slightly different specifications. PostgreSQL seems to comply with + * the ISO standard for the {@code SUBSTRING} function, and therefore + * Calcite's default behavior matches PostgreSQL. */ + private static class SubstrConvertlet implements SqlRexConvertlet { + private final SqlLibrary library; + + SubstrConvertlet(SqlLibrary library) { + this.library = library; + Preconditions.checkArgument(library == SqlLibrary.ORACLE + || library == SqlLibrary.MYSQL + || library == SqlLibrary.BIG_QUERY + || library == SqlLibrary.POSTGRESQL); + } + @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) { // Translate // SUBSTR(value, start, length) - // to + // + // to the following if we want PostgreSQL semantics: + // SUBSTRING(value, start, length) + // + // to the following if we want Oracle semantics: + // SUBSTRING( + // value + // FROM CASE + // WHEN start = 0 + // THEN 1 + // WHEN start + (length(value) + 1) < 1 + // THEN length(value) + 1 + // WHEN start < 0 + // THEN start + (length(value) + 1) + // ELSE start) + // FOR CASE WHEN length < 0 THEN 0 ELSE length END) + // + // to the following in MySQL: // SUBSTRING( // value // FROM CASE - // WHEN start = 0 THEN 1 - // WHEN start + length(value) < 0 + // WHEN start = 0 + // THEN length(value) + 1 -- different from Oracle + // WHEN start + (length(value) + 1) < 1 // THEN length(value) + 1 + // WHEN start < 0 + // THEN start + length(value) + 1 // ELSE start) // FOR CASE WHEN length < 0 THEN 0 ELSE length END) + // + // to the following if we want BigQuery semantics: + // CASE + // WHEN start + (length(value) + 1) < 1 + // THEN value + // ELSE SUBSTRING( + // value + // FROM CASE + // WHEN start = 0 + // THEN 1 + // WHEN start < 0 + // THEN start + length(value) + 1 + // ELSE start) + // FOR CASE WHEN length < 0 THEN 0 ELSE length END) final RexBuilder rexBuilder = cx.getRexBuilder(); final List<RexNode> exprs = @@ -1566,37 +1656,86 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { final RelDataType startType = start.getType(); final RexNode zeroLiteral = rexBuilder.makeLiteral(0, startType, false); final RexNode oneLiteral = rexBuilder.makeLiteral(1, startType, false); - final RexNode valueLength = - rexBuilder.makeCall(SqlStdOperatorTable.CHAR_LENGTH, value); - final RexNode newStart = - rexBuilder.makeCall(SqlStdOperatorTable.CASE, - rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, start, - zeroLiteral), - oneLiteral, - rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN, - rexBuilder.makeCall(SqlStdOperatorTable.PLUS, start, - valueLength), - zeroLiteral), - rexBuilder.makeCall(SqlStdOperatorTable.PLUS, valueLength, + SqlTypeUtil.isBinary(value.getType()) + ? rexBuilder.makeCall(SqlStdOperatorTable.OCTET_LENGTH, value) + : rexBuilder.makeCall(SqlStdOperatorTable.CHAR_LENGTH, value); + final RexNode valueLengthPlusOne = + rexBuilder.makeCall(SqlStdOperatorTable.PLUS, valueLength, + oneLiteral); + + final RexNode newStart; + switch (library) { + case POSTGRESQL: + if (call.operandCount() == 2) { + newStart = rexBuilder.makeCall(SqlStdOperatorTable.CASE, + rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN, start, oneLiteral), - start); + oneLiteral, start); + } else { + newStart = start; + } + break; + case BIG_QUERY: + newStart = rexBuilder.makeCall(SqlStdOperatorTable.CASE, + rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, start, + zeroLiteral), + oneLiteral, + rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN, start, + zeroLiteral), + rexBuilder.makeCall(SqlStdOperatorTable.PLUS, start, + valueLengthPlusOne), + start); + break; + default: + newStart = rexBuilder.makeCall(SqlStdOperatorTable.CASE, + rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, start, + zeroLiteral), + library == SqlLibrary.MYSQL ? valueLengthPlusOne : oneLiteral, + rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN, + rexBuilder.makeCall(SqlStdOperatorTable.PLUS, start, + valueLengthPlusOne), + oneLiteral), + valueLengthPlusOne, + rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN, start, + zeroLiteral), + rexBuilder.makeCall(SqlStdOperatorTable.PLUS, start, + valueLengthPlusOne), + start); + break; + } - switch (call.operandCount()) { - case 2: + if (call.operandCount() == 2) { return rexBuilder.makeCall(SqlStdOperatorTable.SUBSTRING, value, newStart); - case 3: - final RexNode length = exprs.get(2); - final RexNode newLength = + } + + assert call.operandCount() == 3; + final RexNode length = exprs.get(2); + final RexNode newLength; + switch (library) { + case POSTGRESQL: + newLength = length; + break; + default: + newLength = rexBuilder.makeCall(SqlStdOperatorTable.CASE, rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN, length, zeroLiteral), zeroLiteral, length); - return rexBuilder.makeCall(SqlStdOperatorTable.SUBSTRING, value, - newStart, newLength); + } + final RexNode substringCall = + rexBuilder.makeCall(SqlStdOperatorTable.SUBSTRING, value, newStart, + newLength); + switch (library) { + case BIG_QUERY: + return rexBuilder.makeCall(SqlStdOperatorTable.CASE, + rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN, + rexBuilder.makeCall(SqlStdOperatorTable.PLUS, start, + valueLengthPlusOne), oneLiteral), + value, substringCall); default: - throw new AssertionError(); + return substringCall; } } } diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java index 9f7ad50..38f42cd 100644 --- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java +++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java @@ -26,6 +26,7 @@ import org.apache.calcite.adapter.enumerable.MatchUtils; import org.apache.calcite.adapter.enumerable.SourceSorter; import org.apache.calcite.adapter.java.ReflectiveSchema; import org.apache.calcite.adapter.jdbc.JdbcSchema; +import org.apache.calcite.avatica.util.ByteString; import org.apache.calcite.avatica.util.DateTimeUtils; import org.apache.calcite.avatica.util.TimeUnitRange; import org.apache.calcite.interpreter.Context; @@ -384,6 +385,7 @@ public enum BuiltInMethod { INITCAP(SqlFunctions.class, "initcap", String.class), SUBSTRING(SqlFunctions.class, "substring", String.class, int.class, int.class), + OCTET_LENGTH(SqlFunctions.class, "octetLength", ByteString.class), CHAR_LENGTH(SqlFunctions.class, "charLength", String.class), STRING_CONCAT(SqlFunctions.class, "concat", String.class, String.class), MULTI_STRING_CONCAT(SqlFunctions.class, "concatMulti", String[].class), diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index a9899f1..aeb5cff 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -28,6 +28,7 @@ import org.apache.calcite.runtime.Hook; import org.apache.calcite.sql.SqlAggFunction; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlCallBinding; +import org.apache.calcite.sql.SqlFunction; import org.apache.calcite.sql.SqlIdentifier; import org.apache.calcite.sql.SqlJdbcFunctionCall; import org.apache.calcite.sql.SqlLiteral; @@ -307,6 +308,17 @@ public abstract class SqlOperatorBaseTest { // same with tester but without implicit type coercion. protected final SqlTester strictTester; + /** Function object that returns a string with 2 copies of each character. + * For example, {@code DOUBLER.apply("xy")} returns {@code "xxyy"}. */ + private static final UnaryOperator<String> DOUBLER = + new UnaryOperator<String>() { + final Pattern pattern = Pattern.compile("(.)"); + + @Override public String apply(String s) { + return pattern.matcher(s).replaceAll("$1$1"); + } + }; + /** * Creates a SqlOperatorBaseTest. * @@ -4357,6 +4369,12 @@ public abstract class SqlOperatorBaseTest { tester.checkNull("CHARACTER_LENGTH(cast(null as varchar(1)))"); } + @Test void testOctetLengthFunc() { + tester.setFor(SqlStdOperatorTable.OCTET_LENGTH); + tester.checkScalarExact("OCTET_LENGTH(x'aabbcc')", "3"); + tester.checkNull("OCTET_LENGTH(cast(null as varbinary(1)))"); + } + @Test void testAsciiFunc() { tester.setFor(SqlStdOperatorTable.ASCII); tester.checkScalarExact("ASCII('')", "0"); @@ -6636,73 +6654,43 @@ public abstract class SqlOperatorBaseTest { tester.checkNull("last_day(cast(null as timestamp))"); } + /** Tests the {@code SUBSTRING} operator. Many test cases that used to be + * have been moved to {@link SubFunChecker#assertSubFunReturns}, and are + * called for both {@code SUBSTRING} and {@code SUBSTR}. */ @Test void testSubstringFunction() { + checkSubstringFunction(tester); + checkSubstringFunction( + tester.withConformance(SqlConformanceEnum.BIG_QUERY)); + } + + void checkSubstringFunction(SqlTester tester) { tester.setFor(SqlStdOperatorTable.SUBSTRING); tester.checkString( "substring('abc' from 1 for 2)", "ab", "VARCHAR(3) NOT NULL"); tester.checkString( - "substring('abc' from 2 for 8)", - "bc", - "VARCHAR(3) NOT NULL"); - tester.checkString( - "substring('abc' from 0 for 2)", - "a", - "VARCHAR(3) NOT NULL"); - tester.checkString( - "substring('abc' from 0 for 0)", - "", - "VARCHAR(3) NOT NULL"); - tester.checkString( - "substring('abc' from 8 for 2)", - "", - "VARCHAR(3) NOT NULL"); - tester.checkFails( - "substring('abc' from 1 for -1)", - "Substring error: negative substring length not allowed", - true); - tester.checkString( - "substring('abc' from 2)", "bc", "VARCHAR(3) NOT NULL"); - tester.checkString( - "substring('abc' from 0)", "abc", "VARCHAR(3) NOT NULL"); - tester.checkString( - "substring('abc' from 8)", "", "VARCHAR(3) NOT NULL"); - tester.checkString( - "substring('abc' from -2)", "bc", "VARCHAR(3) NOT NULL"); - - tester.checkString( "substring(x'aabbcc' from 1 for 2)", "aabb", "VARBINARY(3) NOT NULL"); - tester.checkString( - "substring(x'aabbcc' from 2 for 8)", - "bbcc", - "VARBINARY(3) NOT NULL"); - tester.checkString( - "substring(x'aabbcc' from 0 for 2)", - "aa", - "VARBINARY(3) NOT NULL"); - tester.checkString( - "substring(x'aabbcc' from 0 for 0)", - "", - "VARBINARY(3) NOT NULL"); - tester.checkString( - "substring(x'aabbcc' from 8 for 2)", - "", - "VARBINARY(3) NOT NULL"); - tester.checkFails( - "substring(x'aabbcc' from 1 for -1)", - "Substring error: negative substring length not allowed", - true); - tester.checkString( - "substring(x'aabbcc' from 2)", "bbcc", "VARBINARY(3) NOT NULL"); - tester.checkString( - "substring(x'aabbcc' from 0)", "aabbcc", "VARBINARY(3) NOT NULL"); - tester.checkString( - "substring(x'aabbcc' from 8)", "", "VARBINARY(3) NOT NULL"); - tester.checkString( - "substring(x'aabbcc' from -2)", "bbcc", "VARBINARY(3) NOT NULL"); + + switch (tester.getConformance().semantics()) { + case BIG_QUERY: + tester.checkString("substring('abc' from 1 for -1)", "", + "VARCHAR(3) NOT NULL"); + tester.checkString("substring(x'aabbcc' from 1 for -1)", "", + "VARBINARY(3) NOT NULL"); + break; + default: + tester.checkFails( + "substring('abc' from 1 for -1)", + "Substring error: negative substring length not allowed", + true); + tester.checkFails( + "substring(x'aabbcc' from 1 for -1)", + "Substring error: negative substring length not allowed", + true); + } if (Bug.FRG296_FIXED) { // substring regexp not supported yet @@ -6712,78 +6700,248 @@ public abstract class SqlOperatorBaseTest { "xx"); } tester.checkNull("substring(cast(null as varchar(1)),1,2)"); + tester.checkNull("substring(cast(null as varchar(1)) FROM 1 FOR 2)"); + tester.checkNull("substring('abc' FROM cast(null as integer) FOR 2)"); + tester.checkNull("substring('abc' FROM cast(null as integer))"); + tester.checkNull("substring('abc' FROM 2 FOR cast(null as integer))"); + } + + /** Tests the non-standard SUBSTR function, that has syntax + * "SUBSTR(value, start [, length ])", as used in BigQuery. */ + @Test void testBigQuerySubstrFunction() { + substrChecker(SqlLibrary.BIG_QUERY, SqlLibraryOperators.SUBSTR_BIG_QUERY) + .check(); + } + + /** Tests the non-standard SUBSTR function, that has syntax + * "SUBSTR(value, start [, length ])", as used in Oracle. */ + @Test void testMysqlSubstrFunction() { + substrChecker(SqlLibrary.MYSQL, SqlLibraryOperators.SUBSTR_MYSQL) + .check(); } /** Tests the non-standard SUBSTR function, that has syntax * "SUBSTR(value, start [, length ])", as used in Oracle. */ @Test void testOracleSubstrFunction() { - checkSubstrFunction(SqlLibrary.ORACLE); - } - - void checkSubstrFunction(SqlLibrary library) { - SqlTester t = tester(library); - t.setFor(SqlLibraryOperators.ORACLE_SUBSTR); - // The following tests have been checked on Oracle 11g R2, PostgreSQL 9.6, - // MySQL 5.6. The SUBSTR function is currently only enabled in the Oracle - // library. - assertSubstrReturns(t, "abc", 1, "abc"); - assertSubstrReturns(t, "abc", 2, "bc"); - assertSubstrReturns(t, "abc", 3, "c"); - assertSubstrReturns(t, "abc", 4, ""); - assertSubstrReturns(t, "abc", 5, ""); - - switch (library) { - case ORACLE: - assertSubstrReturns(t, "abc", 0, "abc"); - assertSubstrReturns(t, "abc", 0, 5, "abc"); - assertSubstrReturns(t, "abc", 0, 4, "abc"); - assertSubstrReturns(t, "abc", 0, 3, "abc"); - assertSubstrReturns(t, "abc", 0, 2, "ab"); - break; - case POSTGRESQL: - assertSubstrReturns(t, "abc", 0, "abc"); - assertSubstrReturns(t, "abc", 0, 5, "abc"); - assertSubstrReturns(t, "abc", 0, 4, "abc"); - assertSubstrReturns(t, "abc", 0, 3, "ab"); - assertSubstrReturns(t, "abc", 0, 2, "a"); - break; - case MYSQL: - assertSubstrReturns(t, "abc", 0, ""); - assertSubstrReturns(t, "abc", 0, 5, ""); - assertSubstrReturns(t, "abc", 0, 4, ""); - assertSubstrReturns(t, "abc", 0, 3, ""); - assertSubstrReturns(t, "abc", 0, 2, ""); - break; - } - assertSubstrReturns(t, "abc", 1, 2, "ab"); - assertSubstrReturns(t, "abc", 1, 3, "abc"); - assertSubstrReturns(t, "abc", 4, 3, ""); - assertSubstrReturns(t, "abc", 4, 4, ""); + substrChecker(SqlLibrary.ORACLE, SqlLibraryOperators.SUBSTR_ORACLE) + .check(); + } - assertSubstrReturns(t, "abc", 1, 0, ""); - assertSubstrReturns(t, "abc", 1, -1, ""); - assertSubstrReturns(t, "abc", 4, -1, ""); - assertSubstrReturns(t, "abc", -2, "bc"); - assertSubstrReturns(t, "abc", -1, "c"); + /** Tests the non-standard SUBSTR function, that has syntax + * "SUBSTR(value, start [, length ])", as used in PostgreSQL. */ + @Test void testPostgresqlSubstrFunction() { + substrChecker(SqlLibrary.POSTGRESQL, SqlLibraryOperators.SUBSTR_POSTGRESQL) + .check(); + } - assertSubstrReturns(t, "abc", -3, 3, "abc"); - assertSubstrReturns(t, "abc", -3, 8, "abc"); - assertSubstrReturns(t, "abc", -2, 3, "bc"); - assertSubstrReturns(t, "abc", -1, 4, "c"); - assertSubstrReturns(t, "abc", -4, 3, ""); - assertSubstrReturns(t, "abc", -5, 1, ""); - assertSubstrReturns(t, "abc", -500, 1, ""); + /** Tests the standard {@code SUBSTRING} function in the mode that has + * BigQuery's non-standard semantics. */ + @Test void testBigQuerySubstringFunction() { + substringChecker(SqlConformanceEnum.BIG_QUERY, SqlLibrary.BIG_QUERY) + .check(); } - void assertSubstrReturns(SqlTester t, String s, int start, String expected) { - assertSubstrReturns(t, s, start, null, expected); + /** Tests the standard {@code SUBSTRING} function in ISO standard + * semantics. */ + @Test void testStandardSubstringFunction() { + substringChecker(SqlConformanceEnum.STRICT_2003, SqlLibrary.POSTGRESQL) + .check(); } - void assertSubstrReturns(SqlTester t, String s, int start, - @Nullable Integer end, String expected) { - final String type = "VARCHAR(" + s.length() + ") NOT NULL"; - t.checkString("substr(CAST('" + s + "' AS varchar(" + s.length() + ")), " - + start + (end == null ? "" : ", " + end) + ")", expected, type); + SubFunChecker substringChecker(SqlConformanceEnum conformance, + SqlLibrary library) { + return new SubFunChecker( + tester.withConnectionFactory( + CalciteAssert.EMPTY_CONNECTION_FACTORY + .with( + new CalciteAssert.AddSchemaSpecPostProcessor( + CalciteAssert.SchemaSpec.HR)) + .with(CalciteConnectionProperty.CONFORMANCE, conformance)), + library, + SqlStdOperatorTable.SUBSTRING); + } + + SubFunChecker substrChecker(SqlLibrary library, SqlFunction function) { + return new SubFunChecker(tester(library), library, function); + } + + /** Tests various configurations of {@code SUBSTR} and {@code SUBSTRING} + * functions. */ + static class SubFunChecker { + final SqlTester t; + final SqlLibrary library; + final SqlFunction function; + + SubFunChecker(SqlTester t, SqlLibrary library, SqlFunction function) { + this.t = t; + t.setFor(function); + this.library = library; + this.function = function; + } + + void check() { + // The following tests have been checked on Oracle 11g R2, PostgreSQL 9.6, + // MySQL 5.6, Google BigQuery. + // + // PostgreSQL and MySQL have a standard SUBSTRING(x FROM s [FOR l]) + // operator, and its behavior is identical to their SUBSTRING(x, s [, l]). + // Oracle and BigQuery do not have SUBSTRING. + assertReturns("abc", 1, "abc"); + assertReturns("abc", 2, "bc"); + assertReturns("abc", 3, "c"); + assertReturns("abc", 4, ""); + assertReturns("abc", 5, ""); + + switch (library) { + case BIG_QUERY: + case ORACLE: + assertReturns("abc", 0, "abc"); + assertReturns("abc", 0, 5, "abc"); + assertReturns("abc", 0, 4, "abc"); + assertReturns("abc", 0, 3, "abc"); + assertReturns("abc", 0, 2, "ab"); + break; + case POSTGRESQL: + assertReturns("abc", 0, "abc"); + assertReturns("abc", 0, 5, "abc"); + assertReturns("abc", 0, 4, "abc"); + assertReturns("abc", 0, 3, "ab"); + assertReturns("abc", 0, 2, "a"); + break; + case MYSQL: + assertReturns("abc", 0, ""); + assertReturns("abc", 0, 5, ""); + assertReturns("abc", 0, 4, ""); + assertReturns("abc", 0, 3, ""); + assertReturns("abc", 0, 2, ""); + break; + } + assertReturns("abc", 0, 0, ""); + assertReturns("abc", 2, 8, "bc"); + assertReturns("abc", 1, 0, ""); + assertReturns("abc", 1, 2, "ab"); + assertReturns("abc", 1, 3, "abc"); + assertReturns("abc", 4, 3, ""); + assertReturns("abc", 4, 4, ""); + assertReturns("abc", 8, 2, ""); + + switch (library) { + case POSTGRESQL: + assertReturns("abc", 1, -1, null); + assertReturns("abc", 4, -1, null); + break; + default: + assertReturns("abc", 1, -1, ""); + assertReturns("abc", 4, -1, ""); + break; + } + + // For negative start, BigQuery matches Oracle. + switch (library) { + case BIG_QUERY: + case MYSQL: + case ORACLE: + assertReturns("abc", -2, "bc"); + assertReturns("abc", -1, "c"); + assertReturns("abc", -2, 1, "b"); + assertReturns("abc", -2, 2, "bc"); + assertReturns("abc", -2, 3, "bc"); + assertReturns("abc", -2, 4, "bc"); + assertReturns("abc", -2, 5, "bc"); + assertReturns("abc", -2, 6, "bc"); + assertReturns("abc", -2, 7, "bc"); + assertReturns("abcde", -3, 2, "cd"); + assertReturns("abc", -3, 3, "abc"); + assertReturns("abc", -3, 8, "abc"); + assertReturns("abc", -1, 4, "c"); + break; + case POSTGRESQL: + assertReturns("abc", -2, "abc"); + assertReturns("abc", -1, "abc"); + assertReturns("abc", -2, 1, ""); + assertReturns("abc", -2, 2, ""); + assertReturns("abc", -2, 3, ""); + assertReturns("abc", -2, 4, "a"); + assertReturns("abc", -2, 5, "ab"); + assertReturns("abc", -2, 6, "abc"); + assertReturns("abc", -2, 7, "abc"); + assertReturns("abcde", -3, 2, ""); + assertReturns("abc", -3, 3, ""); + assertReturns("abc", -3, 8, "abc"); + assertReturns("abc", -1, 4, "ab"); + break; + } + + // For negative start and start + length between 0 and actual-length, + // confusion reigns. + switch (library) { + case BIG_QUERY: + assertReturns("abc", -4, 6, "abc"); + break; + case MYSQL: + case ORACLE: + assertReturns("abc", -4, 6, ""); + break; + case POSTGRESQL: + assertReturns("abc", -4, 6, "a"); + break; + } + // For very negative start, BigQuery differs from Oracle and PostgreSQL. + switch (library) { + case BIG_QUERY: + assertReturns("abc", -4, 3, "abc"); + assertReturns("abc", -5, 1, "abc"); + assertReturns("abc", -10, 2, "abc"); + assertReturns("abc", -500, 1, "abc"); + break; + case MYSQL: + case ORACLE: + case POSTGRESQL: + assertReturns("abc", -4, 3, ""); + assertReturns("abc", -5, 1, ""); + assertReturns("abc", -10, 2, ""); + assertReturns("abc", -500, 1, ""); + break; + } + } + + void assertReturns(String s, int start, String expected) { + assertSubFunReturns(false, s, start, null, expected); + assertSubFunReturns(true, s, start, null, expected); + } + + void assertReturns(String s, int start, @Nullable Integer end, + @Nullable String expected) { + assertSubFunReturns(false, s, start, end, expected); + assertSubFunReturns(true, s, start, end, expected); + } + + void assertSubFunReturns(boolean binary, String s, int start, + @Nullable Integer end, @Nullable String expected) { + final String v = binary + ? "x'" + DOUBLER.apply(s) + "'" + : "'" + s + "'"; + final String type = + (binary ? "VARBINARY" : "VARCHAR") + "(" + s.length() + ")"; + final String value = "CAST(" + v + " AS " + type + ")"; + final String expression; + if (function == SqlStdOperatorTable.SUBSTRING) { + expression = "substring(" + value + " FROM " + start + + (end == null ? "" : (" FOR " + end)) + ")"; + } else { + expression = "substr(" + value + ", " + start + + (end == null ? "" : (", " + end)) + ")"; + } + if (expected == null) { + t.checkFails(expression, + "Substring error: negative substring length not allowed", true); + } else { + if (binary) { + expected = DOUBLER.apply(expected); + } + t.checkString(expression, expected, type + " NOT NULL"); + } + } } @Test void testTrimFunc() { diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 260f8a3..5965560 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -1302,6 +1302,7 @@ Not implemented: | Operator syntax | Description |:--------------- |:----------- | binary || binary | Concatenates two binary strings +| OCTET_LENGTH(binary) | Returns the number of bytes in *binary* | POSITION(binary1 IN binary2) | Returns the position of the first occurrence of *binary1* in *binary2* | POSITION(binary1 IN binary2 FROM integer) | Returns the position of the first occurrence of *binary1* in *binary2* starting at a given point (not standard SQL) | OVERLAY(binary1 PLACING binary2 FROM integer [ FOR integer2 ]) | Replaces a substring of *binary1* with *binary2* @@ -2476,7 +2477,7 @@ semantics. | o | SINH(numeric) | Returns the hyperbolic sine of *numeric* | m o p | SOUNDEX(string) | Returns the phonetic representation of *string*; throws if *string* is encoded with multi-byte encoding such as UTF-8 | m | SPACE(integer) | Returns a string of *integer* spaces; returns an empty string if *integer* is less than 1 -| o | SUBSTR(string, position [, substringLength ]) | Returns a portion of *string*, beginning at character *position*, *substringLength* characters long. SUBSTR calculates lengths using characters as defined by the input character set +| b m o p | SUBSTR(string, position [, substringLength ]) | Returns a portion of *string*, beginning at character *position*, *substringLength* characters long. SUBSTR calculates lengths using characters as defined by the input character set | m | STRCMP(string, string) | Returns 0 if both of the strings are same and returns -1 when the first argument is smaller than the second and 1 when the second one is smaller than the first one | o | TANH(numeric) | Returns the hyperbolic tangent of *numeric* | b | TIMESTAMP_MICROS(integer) | Returns the TIMESTAMP that is *integer* microseconds after 1970-01-01 00:00:00
