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
The following commit(s) were added to refs/heads/master by this push:
new 0327135 [CALCITE-4408] Implement Oracle SUBSTR function (James Starr)
0327135 is described below
commit 0327135376246eb58e1703fc13d555efaf389ada
Author: James Starr <[email protected]>
AuthorDate: Wed Nov 18 14:16:27 2020 -0800
[CALCITE-4408] Implement Oracle SUBSTR function (James Starr)
Add an operand type checker for the Oracle SUBSTR. Becauses
semantics are different to standard SUBSTRING, implement by
translating to SUBSTRING with a CASE expression to handle the
differences.
The implementation assumes that SUBSTRING is consistent with
BigQuery, not ISO Standard SQL, and may need rework when
[CALCITE-4427] Make SUBSTRING operator comply with ISO
standard SQL
is fixed.
Rework so that it gives the right result for very negative
start; add tests for MySQL and PostgreSQL variants of SUBSTR
(Julian Hyde).
Close apache/calcite#2271
---
.../calcite/sql/dialect/OracleSqlDialect.java | 3 +-
.../calcite/sql/fun/OracleSqlOperatorTable.java | 2 +-
.../calcite/sql/fun/SqlLibraryOperators.java | 19 ++++--
.../org/apache/calcite/sql/type/OperandTypes.java | 5 ++
.../calcite/sql2rel/StandardConvertletTable.java | 59 +++++++++++++++++
.../calcite/sql/test/SqlOperatorBaseTest.java | 73 ++++++++++++++++++++++
6 files changed, 155 insertions(+), 6 deletions(-)
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 7de88da..9b1f4e4 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,8 @@ 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.SUBSTR, writer, call,
false);
+ SqlUtil.unparseFunctionSyntax(SqlLibraryOperators.ORACLE_SUBSTR, writer,
+ call, false);
} else {
switch (call.getKind()) {
case FLOOR:
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 6dae93b..d39a1ad 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.SUBSTR;
+ public static final SqlFunction SUBSTR = SqlLibraryOperators.ORACLE_SUBSTR;
@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/SqlLibraryOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index 2f8b4cb..f42e05f 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
@@ -157,12 +157,23 @@ public abstract class SqlLibraryOperators {
/** Oracle's "SUBSTR(string, position [, substringLength ])" function.
*
- * <p>It has similar semantics to standard SQL's
- * {@link SqlStdOperatorTable#SUBSTRING} function but different syntax. */
+ * <p>It has different semantics to standard SQL's
+ * {@link SqlStdOperatorTable#SUBSTRING} function:
+ *
+ * <ul>
+ * <li>If {@code substringLength} ≤ 0, result is the empty string
+ * (Oracle would return null, because it treats the empty string as null,
+ * but Calcite does not have these semantics);
+ * <li>If {@code position} = 0, treat {@code position} as 1;
+ * <li>If {@code position} < 0, treat {@code position} as
+ * "length(string) + position + 1".
+ * </ul>
+ */
@LibraryOperator(libraries = {ORACLE})
- public static final SqlFunction SUBSTR =
+ public static final SqlFunction ORACLE_SUBSTR =
new SqlFunction("SUBSTR", SqlKind.OTHER_FUNCTION,
- ReturnTypes.ARG0_NULLABLE_VARYING, null, null,
+ ReturnTypes.ARG0_NULLABLE_VARYING, null,
+ OperandTypes.STRING_INTEGER_OPTIONAL_INTEGER,
SqlFunctionCategory.STRING);
/** The "GREATEST(value, value)" function. */
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 c78704a..4020de6 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,11 @@ public abstract class OperandTypes {
public static final SqlSingleOperandTypeChecker STRING_INTEGER =
family(SqlTypeFamily.STRING, SqlTypeFamily.INTEGER);
+ public static final SqlSingleOperandTypeChecker
STRING_INTEGER_OPTIONAL_INTEGER =
+ family(
+ ImmutableList.of(SqlTypeFamily.STRING, SqlTypeFamily.INTEGER,
+ SqlTypeFamily.INTEGER), i -> i == 2);
+
/** Operand type-checking strategy where the first operand is a character or
* binary string (CHAR, VARCHAR, BINARY or VARBINARY), and the second operand
* is INTEGER. */
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 f5ed894..6eff2b4 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -145,6 +145,7 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
registerOp(SqlLibraryOperators.GREATEST, new GreatestConvertlet());
registerOp(SqlLibraryOperators.LEAST, new GreatestConvertlet());
+ registerOp(SqlLibraryOperators.ORACLE_SUBSTR, new
OracleSubstrConvertlet());
registerOp(SqlLibraryOperators.NVL, StandardConvertletTable::convertNvl);
registerOp(SqlLibraryOperators.DECODE,
@@ -1542,6 +1543,64 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
}
}
+ /** Convertlet that handles Oracle's {@code SUBSTR} function. */
+ private static class OracleSubstrConvertlet implements SqlRexConvertlet {
+ @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
+ // Translate
+ // SUBSTR(value, start, length)
+ // to
+ // SUBSTRING(
+ // value
+ // FROM CASE
+ // WHEN start = 0 THEN 1
+ // WHEN start + length(value) < 0
+ // THEN length(value) + 1
+ // ELSE start)
+ // FOR CASE WHEN length < 0 THEN 0 ELSE length END)
+
+ final RexBuilder rexBuilder = cx.getRexBuilder();
+ final List<RexNode> exprs =
+ convertOperands(cx, call, SqlOperandTypeChecker.Consistency.NONE);
+ final RexNode value = exprs.get(0);
+ final RexNode start = exprs.get(1);
+ 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,
+ oneLiteral),
+ start);
+
+ switch (call.operandCount()) {
+ case 2:
+ return rexBuilder.makeCall(SqlStdOperatorTable.SUBSTRING, value,
+ newStart);
+ case 3:
+ final RexNode length = exprs.get(2);
+ final RexNode newLength =
+ rexBuilder.makeCall(SqlStdOperatorTable.CASE,
+ rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN, length,
+ zeroLiteral),
+ zeroLiteral, length);
+ return rexBuilder.makeCall(SqlStdOperatorTable.SUBSTRING, value,
+ newStart, newLength);
+ default:
+ throw new AssertionError();
+ }
+ }
+ }
+
/** Convertlet that handles the {@code TIMESTAMPADD} function. */
private static class TimestampAddConvertlet implements SqlRexConvertlet {
@Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
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 ab44440..a9899f1 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
@@ -65,6 +65,7 @@ import org.apache.calcite.util.trace.CalciteTrace;
import com.google.common.base.Throwables;
+import org.checkerframework.checker.nullness.qual.Nullable;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Tag;
@@ -6713,6 +6714,78 @@ public abstract class SqlOperatorBaseTest {
tester.checkNull("substring(cast(null as varchar(1)),1,2)");
}
+ /** 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, "");
+
+ 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");
+
+ 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, "");
+ }
+
+ void assertSubstrReturns(SqlTester t, String s, int start, String expected) {
+ assertSubstrReturns(t, s, start, null, expected);
+ }
+
+ 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);
+ }
+
@Test void testTrimFunc() {
tester.setFor(SqlStdOperatorTable.TRIM);
