This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 9ea862478d [CALCITE-3959] Add INSTR function (enabled in BigQuery,
MySQL, Oracle libraries)
9ea862478d is described below
commit 9ea862478d88b6163e47c57521e222ea5e582e47
Author: jhugomoore <[email protected]>
AuthorDate: Thu Apr 20 13:15:35 2023 -0700
[CALCITE-3959] Add INSTR function (enabled in BigQuery, MySQL, Oracle
libraries)
Close apache/calcite#3184
---
.../apache/calcite/runtime/CalciteResource.java | 6 +
.../org/apache/calcite/runtime/SqlFunctions.java | 148 +++++++++++++++++++--
.../calcite/sql/dialect/BigQuerySqlDialect.java | 36 ++++-
.../calcite/sql/dialect/MysqlSqlDialect.java | 8 ++
.../calcite/sql/dialect/OracleSqlDialect.java | 18 +++
.../calcite/sql/fun/SqlLibraryOperators.java | 4 +
.../calcite/sql/fun/SqlPositionFunction.java | 35 ++++-
.../calcite/sql2rel/StandardConvertletTable.java | 39 ++++++
.../calcite/runtime/CalciteResource.properties | 2 +
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 33 ++++-
.../org/apache/calcite/test/SqlFunctionsTest.java | 55 ++++++++
site/_docs/reference.md | 12 +-
.../org/apache/calcite/test/SqlOperatorTest.java | 35 ++++-
13 files changed, 402 insertions(+), 29 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index 4bf68b3771..7695ad8364 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -1020,6 +1020,12 @@ public interface CalciteResource {
@BaseMessage("No operator for ''{0}'' with kind: ''{1}'', syntax: ''{2}''
during JSON deserialization")
ExInst<CalciteException> noOperator(String name, String kind, String syntax);
+ @BaseMessage("Invalid input for POSITION function: from operand value must
not be zero")
+ ExInst<CalciteException> fromNotZero();
+
+ @BaseMessage("Invalid input for POSITION function: occurrence operand value
must be positive")
+ ExInst<CalciteException> occurrenceNotZero();
+
@BaseMessage("Only tables with set semantics may be partitioned. Invalid
PARTITION BY clause in the {0,number,#}-th operand of table function ''{1}''")
ExInst<SqlValidatorException> invalidPartitionKeys(int idx, String funcName);
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 f44195b491..0e5a12dc56 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -3186,23 +3186,155 @@ public class SqlFunctions {
/** SQL {@code POSITION(seek IN string FROM integer)} function. */
public static int position(String seek, String s, int from) {
- final int from0 = from - 1; // 0-based
- if (from0 > s.length() || from0 < 0) {
- return 0;
+ if (from == 0) {
+ throw RESOURCE.fromNotZero().ex();
+ }
+ if (from > 0) {
+ return positionForwards(seek, s, from);
+ } else {
+ from += s.length(); // convert negative position to positive index
+ return positionBackwards(seek, s, from);
}
-
- return s.indexOf(seek, from0) + 1;
}
/** SQL {@code POSITION(seek IN string FROM integer)} function for byte
* strings. */
public static int position(ByteString seek, ByteString s, int from) {
- final int from0 = from - 1;
- if (from0 > s.length() || from0 < 0) {
+ if (from == 0) {
+ throw RESOURCE.fromNotZero().ex();
+ }
+ if (from > 0) {
+ return positionForwards(seek, s, from);
+ } else {
+ from += s.length(); // convert negative position to 0-based index
+ return positionBackwards(seek, s, from);
+ }
+ }
+
+ /** Returns the position (1-based) of {@code seek} in string {@code s}
+ * seeking forwards from {@code from} (1-based). */
+ private static int positionForwards(String seek, String s, int from) {
+ final int from0 = from - 1; // 0-based
+ if (from0 >= s.length()) {
return 0;
+ } else {
+ return s.indexOf(seek, from0) + 1;
}
+ }
+
+ /** Returns the position (1-based) of {@code seek} in byte string {@code s}
+ * seeking forwards from {@code from} (1-based). */
+ private static int positionForwards(ByteString seek, ByteString s,
+ int from) {
+ final int from0 = from - 1; // 0-based
+ if (from0 >= s.length()) {
+ return 0;
+ } else {
+ return s.indexOf(seek, from0) + 1;
+ }
+ }
+
+ /** Returns the position (1-based) of {@code seek} in string {@code s}
+ * seeking backwards from {@code rightIndex} (0-based). */
+ private static int positionBackwards(String seek, String s, int rightIndex) {
+ if (rightIndex <= 0) {
+ return 0;
+ }
+ int lastIndex = s.lastIndexOf(seek) + 1;
+ while (lastIndex > rightIndex + 1) {
+ lastIndex = s.substring(0, lastIndex - 1).lastIndexOf(seek) + 1;
+ if (lastIndex == 0) {
+ return 0;
+ }
+ }
+ return lastIndex;
+ }
- return s.indexOf(seek, from0) + 1;
+ /** Returns the position (1-based) of {@code seek} in byte string {@code s}
+ * seeking backwards from {@code rightIndex} (0-based). */
+ private static int positionBackwards(ByteString seek, ByteString s,
+ int rightIndex) {
+ if (rightIndex <= 0) {
+ return 0;
+ }
+ int lastIndex = 0;
+ while (lastIndex < rightIndex) {
+ // NOTE: When [CALCITE-5682] is fixed, use ByteString.lastIndexOf
+ int indexOf = s.substring(lastIndex).indexOf(seek) + 1;
+ if (indexOf == 0 || lastIndex + indexOf > rightIndex + 1) {
+ break;
+ }
+ lastIndex += indexOf;
+ }
+ return lastIndex;
+ }
+
+ /** SQL {@code POSITION(seek, string, from, occurrence)} function. */
+ public static int position(String seek, String s, int from, int occurrence) {
+ if (from == 0) {
+ throw RESOURCE.fromNotZero().ex();
+ }
+ if (occurrence == 0) {
+ throw RESOURCE.occurrenceNotZero().ex();
+ }
+ if (from > 0) {
+ // Forwards
+ --from; // compensate for the '++from' 2 lines down
+ for (int i = 0; i < occurrence; i++) {
+ ++from; // move on to next occurrence
+ from = positionForwards(seek, s, from);
+ if (from == 0) {
+ return 0;
+ }
+ }
+ } else {
+ // Backwards
+ from += s.length() + 1; // convert negative position to positive index
+ ++from; // compensate for the '--from' 2 lines down
+ for (int i = 0; i < occurrence; i++) {
+ --from; // move on to next occurrence
+ from = positionBackwards(seek, s, from - 1);
+ if (from == 0) {
+ return 0;
+ }
+ }
+ }
+ return from;
+ }
+
+ /** SQL {@code POSITION(seek, string, from, occurrence)} function for byte
+ * strings. */
+ public static int position(ByteString seek, ByteString s, int from,
+ int occurrence) {
+ if (from == 0) {
+ throw RESOURCE.fromNotZero().ex();
+ }
+ if (occurrence == 0) {
+ throw RESOURCE.occurrenceNotZero().ex();
+ }
+ if (from > 0) {
+ // Forwards
+ --from; // compensate for the '++from' 2 lines down
+ for (int i = 0; i < occurrence; i++) {
+ ++from; // move on to next occurrence
+ from = positionForwards(seek, s, from);
+ if (from == 0) {
+ return 0;
+ }
+ }
+ } else {
+ // Backwards
+ from += s.length() + 1; // convert negative position to positive index
+ ++from; // compensate for the '--from' 2 lines down
+ for (int i = 0; i < occurrence; i++) {
+ --from; // move on to next occurrence
+ from = positionBackwards(seek, s, from - 1);
+ if (from == 0) {
+ return 0;
+ }
+ }
+ }
+ return from;
}
/** Helper for rounding. Truncate(12345, 1000) returns 12000. */
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 f5d963a4c1..3ccb062219 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
@@ -150,13 +150,35 @@ public class BigQuerySqlDialect extends SqlDialect {
final int rightPrec) {
switch (call.getKind()) {
case POSITION:
- final SqlWriter.Frame frame = writer.startFunCall("STRPOS");
- writer.sep(",");
- call.operand(1).unparse(writer, leftPrec, rightPrec);
- writer.sep(",");
- call.operand(0).unparse(writer, leftPrec, rightPrec);
- if (3 == call.operandCount()) {
- throw new RuntimeException("3rd operand Not Supported for Function
STRPOS in Big Query");
+ final SqlWriter.Frame frame = writer.startFunCall("INSTR");
+ switch (call.operandCount()) {
+ case 2:
+ writer.sep(",");
+ call.operand(1).unparse(writer, leftPrec, rightPrec);
+ writer.sep(",");
+ call.operand(0).unparse(writer, leftPrec, rightPrec);
+ break;
+ case 3:
+ writer.sep(",");
+ call.operand(1).unparse(writer, leftPrec, rightPrec);
+ writer.sep(",");
+ call.operand(0).unparse(writer, leftPrec, rightPrec);
+ writer.sep(",");
+ call.operand(2).unparse(writer, leftPrec, rightPrec);
+ break;
+ case 4:
+ writer.sep(",");
+ call.operand(1).unparse(writer, leftPrec, rightPrec);
+ writer.sep(",");
+ call.operand(0).unparse(writer, leftPrec, rightPrec);
+ writer.sep(",");
+ call.operand(2).unparse(writer, leftPrec, rightPrec);
+ writer.sep(",");
+ call.operand(3).unparse(writer, leftPrec, rightPrec);
+ break;
+ default:
+ throw new RuntimeException("BigQuery does not support " +
call.operandCount()
+ + " operands in the position function");
}
writer.endFunCall(frame);
break;
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
index fb3cf64c25..041bfd865a 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
@@ -218,6 +218,14 @@ public class MysqlSqlDialect extends SqlDialect {
@Override public void unparseCall(SqlWriter writer, SqlCall call,
int leftPrec, int rightPrec) {
switch (call.getKind()) {
+ case POSITION:
+ final SqlWriter.Frame frame = writer.startFunCall("INSTR");
+ writer.sep(",");
+ call.operand(1).unparse(writer, leftPrec, rightPrec);
+ writer.sep(",");
+ call.operand(0).unparse(writer, leftPrec, rightPrec);
+ writer.endFunCall(frame);
+ break;
case FLOOR:
if (call.operandCount() != 2) {
super.unparseCall(writer, call, leftPrec, rightPrec);
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 790028ea91..fbd9aef93b 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
@@ -150,6 +150,24 @@ public class OracleSqlDialect extends SqlDialect {
call, false);
} else {
switch (call.getKind()) {
+ case POSITION:
+ final SqlWriter.Frame frame = writer.startFunCall("INSTR");
+ writer.sep(",");
+ call.operand(1).unparse(writer, leftPrec, rightPrec);
+ writer.sep(",");
+ call.operand(0).unparse(writer, leftPrec, rightPrec);
+ if (3 == call.operandCount()) {
+ writer.sep(",");
+ call.operand(2).unparse(writer, leftPrec, rightPrec);
+ }
+ if (4 == call.operandCount()) {
+ writer.sep(",");
+ call.operand(2).unparse(writer, leftPrec, rightPrec);
+ writer.sep(",");
+ call.operand(3).unparse(writer, leftPrec, rightPrec);
+ }
+ writer.endFunCall(frame);
+ break;
case FLOOR:
if (call.operandCount() != 2) {
super.unparseCall(writer, call, leftPrec, rightPrec);
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 75a27f2c53..6c6a7b022b 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
@@ -332,6 +332,10 @@ public abstract class SqlLibraryOperators {
@LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL})
public static final SqlFunction STRPOS = new SqlPositionFunction("STRPOS");
+ /** The "INSTR(string, substring [, position [, occurrence]])" function. */
+ @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE})
+ public static final SqlFunction INSTR = new SqlPositionFunction("INSTR");
+
/** Generic "SUBSTR(string, position [, substringLength ])" function. */
private static final SqlBasicFunction SUBSTR =
SqlBasicFunction.create("SUBSTR", ReturnTypes.ARG0_NULLABLE_VARYING,
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlPositionFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlPositionFunction.java
index 753ba93f8c..4a625fc52d 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlPositionFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlPositionFunction.java
@@ -38,7 +38,11 @@ public class SqlPositionFunction extends SqlFunction {
private static final SqlOperandTypeChecker OTC_CUSTOM =
OperandTypes.STRING_SAME_SAME
- .or(OperandTypes.STRING_SAME_SAME_INTEGER);
+ .or(OperandTypes.STRING_SAME_SAME_INTEGER)
+ .or(
+ OperandTypes.sequence("INSTR(<STRING>, <STRING>, <INTEGER>,
<INTEGER>)",
+ OperandTypes.STRING, OperandTypes.STRING, OperandTypes.INTEGER,
+ OperandTypes.INTEGER));
public SqlPositionFunction(String name) {
super(name, SqlKind.POSITION, ReturnTypes.INTEGER_NULLABLE, null,
@@ -53,12 +57,23 @@ public class SqlPositionFunction extends SqlFunction {
int leftPrec,
int rightPrec) {
final SqlWriter.Frame frame = writer.startFunCall(getName());
- call.operand(0).unparse(writer, leftPrec, rightPrec);
- writer.sep("IN");
- call.operand(1).unparse(writer, leftPrec, rightPrec);
- if (3 == call.operandCount()) {
- writer.sep("FROM");
+ if (call.operandCount() == 2 || call.operandCount() == 3) {
+ call.operand(0).unparse(writer, leftPrec, rightPrec);
+ writer.sep("IN");
+ call.operand(1).unparse(writer, leftPrec, rightPrec);
+ if (call.operandCount() == 3) {
+ writer.sep("FROM");
+ call.operand(2).unparse(writer, leftPrec, rightPrec);
+ }
+ }
+ if (call.operandCount() == 4) {
+ call.operand(0).unparse(writer, leftPrec, rightPrec);
+ writer.sep(",");
+ call.operand(1).unparse(writer, leftPrec, rightPrec);
+ writer.sep(",");
call.operand(2).unparse(writer, leftPrec, rightPrec);
+ writer.sep(",");
+ call.operand(3).unparse(writer, leftPrec, rightPrec);
}
writer.endFunCall(frame);
}
@@ -69,6 +84,8 @@ public class SqlPositionFunction extends SqlFunction {
return "{0}({1} IN {2})";
case 3:
return "{0}({1} IN {2} FROM {3})";
+ case 4:
+ return "{0}({1}, {2}, {3}, {4})";
default:
throw new AssertionError();
}
@@ -88,6 +105,12 @@ public class SqlPositionFunction extends SqlFunction {
return OperandTypes.SAME_SAME_INTEGER.checkOperandTypes(
callBinding, throwOnFailure)
&& super.checkOperandTypes(callBinding, throwOnFailure);
+ case 4:
+ return OperandTypes.sequence("INSTR(<STRING>, <STRING>, <INTEGER>,
<INTEGER>)",
+ OperandTypes.STRING, OperandTypes.STRING, OperandTypes.INTEGER,
+ OperandTypes.INTEGER).checkOperandTypes(
+ callBinding, throwOnFailure)
+ && super.checkOperandTypes(callBinding, throwOnFailure);
default:
throw new AssertionError();
}
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 a8288700cc..60718dff6e 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -280,6 +280,11 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
SqlStdOperatorTable.POSITION.createCall(SqlParserPos.ZERO,
call.operand(1), call.operand(0))));
+ // "INSTR(string, substring, position, occurrence) is equivalent to
+ // "POSITION(substring, string, position, occurrence)"
+ registerOp(SqlLibraryOperators.INSTR,
StandardConvertletTable::convertInstr);
+
+
// REVIEW jvs 24-Apr-2006: This only seems to be working from within a
// windowed agg. I have added an optimizer rule
// org.apache.calcite.rel.rules.AggregateReduceFunctionsRule which handles
@@ -403,6 +408,40 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
operand1)));
}
+ /** Converts a call to the INSTR function.
+ * INSTR(string, substring, position, occurrence) is equivalent to
+ * POSITION(substring, string, position, occurrence) */
+ private static RexNode convertInstr(SqlRexContext cx, SqlCall call) {
+ final RexBuilder rexBuilder = cx.getRexBuilder();
+ final List<RexNode> operands =
+ convertOperands(cx, call, SqlOperandTypeChecker.Consistency.NONE);
+ final RelDataType type =
+ cx.getValidator().getValidatedNodeType(call);
+ final List<RexNode> exprs = new ArrayList<>();
+ switch (call.operandCount()) {
+ // Must reverse order of first 2 operands.
+ case 2:
+ exprs.add(operands.get(1)); // Substring
+ exprs.add(operands.get(0)); // String
+ break;
+ case 3:
+ exprs.add(operands.get(1)); // Substring
+ exprs.add(operands.get(0)); // String
+ exprs.add(operands.get(2)); // Position
+ break;
+ case 4:
+ exprs.add(operands.get(1)); // Substring
+ exprs.add(operands.get(0)); // String
+ exprs.add(operands.get(2)); // Position
+ exprs.add(operands.get(3)); // Occurrence
+ break;
+ default:
+ throw new UnsupportedOperationException("Position does not accept "
+ + call.operandCount() + " operands");
+ }
+ return rexBuilder.makeCall(type, SqlStdOperatorTable.POSITION, exprs);
+ }
+
/** Converts a call to the DECODE function. */
private static RexNode convertDecode(SqlRexContext cx, SqlCall call) {
final RexBuilder rexBuilder = cx.getRexBuilder();
diff --git
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index 5c604ca60a..1f7f847eba 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -332,6 +332,8 @@ InvalidInputForExtractValue=Invalid input for EXTRACTVALUE:
xml: ''{0}'', xpath
InvalidInputForExtractXml=Invalid input for EXTRACT xpath: ''{0}'', namespace:
''{1}''
InvalidInputForExistsNode=Invalid input for EXISTSNODE xpath: ''{0}'',
namespace: ''{1}''
DifferentLengthForBitwiseOperands=Different length for bitwise operands: the
first: {0,number,#}, the second: {1,number,#}
+FromNotZero=Invalid input for POSITION function: from operand value must not
be zero
+OccurrenceNotZero=Invalid input for POSITION function: occurrence operand
value must be positive
InvalidPartitionKeys=Only tables with set semantics may be partitioned.
Invalid PARTITION BY clause in the {0,number,#}-th operand of table function
''{1}''
InvalidOrderBy=Only tables with set semantics may be ordered. Invalid ORDER BY
clause in the {0,number,#}-th operand of table function ''{1}''
MultipleRowSemanticsTables=A table function at most has one input table with
row semantics. Table function ''{0}'' has multiple input tables with row
semantics
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 1d7431b922..8193d0ab0c 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
@@ -2325,13 +2325,44 @@ class RelToSqlConverterTest {
sql(query).withHive().ok(expected);
}
+ @Test void testPositionFunctionForMySql() {
+ final String query = "select position('A' IN 'ABC') from \"product\"";
+ final String expected = "SELECT INSTR('ABC', 'A')\n"
+ + "FROM `foodmart`.`product`";
+ sql(query).withMysql().ok(expected);
+ }
+
@Test void testPositionFunctionForBigQuery() {
final String query = "select position('A' IN 'ABC') from \"product\"";
- final String expected = "SELECT STRPOS('ABC', 'A')\n"
+ final String expected = "SELECT INSTR('ABC', 'A')\n"
+ "FROM foodmart.product";
sql(query).withBigQuery().ok(expected);
}
+ @Test void testInstrFunction4Operands() {
+ final String query = "SELECT INSTR('ABC', 'A', 1, 1) from \"product\"";
+ final String expectedBQ = "SELECT INSTR('ABC', 'A', 1, 1)\n"
+ + "FROM foodmart.product";
+ final String expected_oracle = "SELECT INSTR('ABC', 'A', 1, 1)\n"
+ + "FROM \"foodmart\".\"product\"";
+ final Sql sqlOracle =
fixture().withOracle().withLibrary(SqlLibrary.ORACLE);
+ sqlOracle.withSql(query).withOracle().ok(expected_oracle);
+ final Sql sqlBQ =
fixture().withBigQuery().withLibrary(SqlLibrary.BIG_QUERY);
+ sqlBQ.withSql(query).withBigQuery().ok(expectedBQ);
+ }
+
+ @Test void testInstrFunction3Operands() {
+ final String query = "SELECT INSTR('ABC', 'A', 1) from \"product\"";
+ final String expectedBQ = "SELECT INSTR('ABC', 'A', 1)\n"
+ + "FROM foodmart.product";
+ final String expectedOracle = "SELECT INSTR('ABC', 'A', 1)\n"
+ + "FROM \"foodmart\".\"product\"";
+ final Sql sqlOracle =
fixture().withOracle().withLibrary(SqlLibrary.ORACLE);
+ sqlOracle.withSql(query).withOracle().ok(expectedOracle);
+ final Sql sqlBQ =
fixture().withBigQuery().withLibrary(SqlLibrary.BIG_QUERY);
+ sqlBQ.withSql(query).withBigQuery().ok(expectedBQ);
+ }
+
/** 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 void testCharLiteralForBigQuery() {
diff --git a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
index 2d44371722..60c9895b84 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -53,6 +53,7 @@ import static org.apache.calcite.runtime.SqlFunctions.lesser;
import static org.apache.calcite.runtime.SqlFunctions.lower;
import static org.apache.calcite.runtime.SqlFunctions.ltrim;
import static org.apache.calcite.runtime.SqlFunctions.md5;
+import static org.apache.calcite.runtime.SqlFunctions.position;
import static org.apache.calcite.runtime.SqlFunctions.posixRegex;
import static org.apache.calcite.runtime.SqlFunctions.regexpReplace;
import static org.apache.calcite.runtime.SqlFunctions.rtrim;
@@ -1055,6 +1056,60 @@ class SqlFunctionsTest {
}
}
+ @Test void testPosition() {
+ assertThat(position("c", "abcdec"), is(3));
+ assertThat(position("c", "abcdec", 2), is(3));
+ assertThat(position("c", "abcdec", -2), is(3));
+ assertThat(position("c", "abcdec", 4), is(6));
+ assertThat(position("c", "abcdec", 1, 2), is(6));
+ assertThat(position("cde", "abcdecde", -2, 1), is(6));
+ assertThat(position("c", "abcdec", -1, 2), is(3));
+ assertThat(position("f", "abcdec", 1, 1), is(0));
+ assertThat(position("c", "abcdec", 1, 3), is(0));
+ try {
+ int i = position("c", "abcdec", 0, 1);
+ fail("expected error, got: " + i);
+ } catch (CalciteException e) {
+ assertThat(e.getMessage(),
+ is("Invalid input for POSITION function: from operand value must not
be zero"));
+ }
+ try {
+ int i = position("c", "abcdec", 1, 0);
+ fail("expected error, got: " + i);
+ } catch (CalciteException e) {
+ assertThat(e.getMessage(),
+ is("Invalid input for POSITION function: occurrence operand value
must be positive"));
+ }
+ final ByteString abcdec = ByteString.of("aabbccddeecc", 16);
+ final ByteString c = ByteString.of("cc", 16);
+ final ByteString dec = ByteString.of("ddeecc", 16);
+ final ByteString f = ByteString.of("ff", 16);
+ assertThat(position(c, abcdec), is(3));
+ assertThat(position(c, abcdec, 2), is(3));
+ assertThat(position(c, abcdec, -2), is(3));
+ assertThat(position(c, abcdec, 4), is(6));
+ assertThat(position(dec, abcdec, -2), is(4));
+ assertThat(position(c, abcdec, 1, 2), is(6));
+ assertThat(position(c, abcdec, -1, 2), is(3));
+ assertThat(position(f, abcdec, 1, 1), is(0));
+ assertThat(position(c, abcdec, 1, 3), is(0));
+ try {
+ int i = position(c, abcdec, 0, 1);
+ fail("expected error, got: " + i);
+ } catch (CalciteException e) {
+ assertThat(e.getMessage(),
+ is("Invalid input for POSITION function: from operand value must not
be zero"));
+ }
+ try {
+ int i = position(c, abcdec, 1, 0);
+ fail("expected error, got: " + i);
+ } catch (CalciteException e) {
+ assertThat(e.getMessage(),
+ is("Invalid input for POSITION function: occurrence operand value
must be positive"));
+ }
+ }
+
+
/**
* Tests that a date in the local time zone converts to a Unix timestamp in
* UTC.
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 6c3cfcf807..9d8d11beb3 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2685,7 +2685,7 @@ BigQuery's type system uses confusingly different names
for types and functions:
| o | DECODE(value, value1, result1 [, valueN, resultN ]* [, default ]) |
Compares *value* to each *valueN* value one by one; if *value* is equal to a
*valueN*, returns the corresponding *resultN*, else returns *default*, or NULL
if *default* is not specified
| p | DIFFERENCE(string, string) | Returns a measure of
the similarity of two strings, namely the number of character positions that
their `SOUNDEX` values have in common: 4 if the `SOUNDEX` values are same and 0
if the `SOUNDEX` values are totally different
| b | ENDS_WITH(string1, string2) | Returns whether
*string2* is a suffix of *string1*
-| o | EXTRACT(xml, xpath, [, namespaces ]) | Returns the xml
fragment of the element or elements matched by the XPath expression. The
optional namespace value that specifies a default mapping or namespace mapping
for prefixes, which is used when evaluating the XPath expression
+| o | EXTRACT(xml, xpath, [, namespaces ]) | Returns the XML
fragment of the element or elements matched by the XPath expression. The
optional namespace value that specifies a default mapping or namespace mapping
for prefixes, which is used when evaluating the XPath expression
| o | EXISTSNODE(xml, xpath, [, namespaces ]) | Determines whether
traversal of a XML document using a specified xpath results in any nodes.
Returns 0 if no nodes remain after applying the XPath traversal on the document
fragment of the element or elements matched by the XPath expression. Returns 1
if any nodes remain. The optional namespace value that specifies a default
mapping or namespace mapping for prefixes, which is used when evaluating the
XPath expression.
| m | EXTRACTVALUE(xml, xpathExpr)) | Returns the text of the
first text node which is a child of the element or elements matched by the
XPath expression.
| b | FORMAT_DATE(string, date) | Formats *date*
according to the specified format *string*
@@ -2695,15 +2695,17 @@ BigQuery's type system uses confusingly different names
for types and functions:
| b o | GREATEST(expr [, expr ]*) | Returns the greatest of
the expressions
| b h s | IF(condition, value1, value2) | Returns *value1* if
*condition* is TRUE, *value2* otherwise
| b | IFNULL(value1, value2) | Equivalent to
`NVL(value1, value2)`
+| b o | INSTR(string, substring [, from [, occurrence ] ]) | Returns the
position of *substring* in *string*, searching starting at *from* (default 1),
and until locating the nth *occurrence* (default 1) of *substring*
+| m | INSTR(string, substring) | Equivalent to
`POSITION(substring IN string)`
| p | string1 ILIKE string2 [ ESCAPE string3 ] | Whether *string1*
matches pattern *string2*, ignoring case (similar to `LIKE`)
| p | string1 NOT ILIKE string2 [ ESCAPE string3 ] | Whether *string1* does
not match pattern *string2*, ignoring case (similar to `NOT LIKE`)
| m | JSON_TYPE(jsonValue) | Returns a string value
indicating the type of *jsonValue*
| m | JSON_DEPTH(jsonValue) | Returns an integer
value indicating the depth of *jsonValue*
| m | JSON_PRETTY(jsonValue) | Returns a
pretty-printing of *jsonValue*
| m | JSON_LENGTH(jsonValue [, path ]) | Returns a integer
indicating the length of *jsonValue*
-| m | JSON_INSERT(jsonValue, path, val[, path, val]*) | Returns a JSON
document insert a data of *jsonValue*, *path*, *val*
+| m | JSON_INSERT(jsonValue, path, val [, path, val ]*) | Returns a JSON
document insert a data of *jsonValue*, *path*, *val*
| m | JSON_KEYS(jsonValue [, path ]) | Returns a string
indicating the keys of a JSON *jsonValue*
-| m | JSON_REMOVE(jsonValue, path[, path]) | Removes data from
*jsonValue* using a series of *path* expressions and returns the result
+| m | JSON_REMOVE(jsonValue, path [, path ]) | Removes data from
*jsonValue* using a series of *path* expressions and returns the result
| m | JSON_REPLACE(jsonValue, path, val[, path, val]*) | Returns a JSON
document replace a data of *jsonValue*, *path*, *val*
| m | JSON_SET(jsonValue, path, val[, path, val]*) | Returns a JSON document
set a data of *jsonValue*, *path*, *val*
| m | JSON_STORAGE_SIZE(jsonValue) | Returns the number of
bytes used to store the binary representation of *jsonValue*
@@ -2711,7 +2713,7 @@ BigQuery's type system uses confusingly different names
for types and functions:
| b m p | LEFT(string, length) | Returns the leftmost
*length* characters from the *string*
| b | LENGTH(string) | Equivalent to
`CHAR_LENGTH(string)`
| b | LOG(numeric1 [, numeric2 ]) | Returns the logarithm
of *numeric1* to base *numeric2*, or base e if *numeric2* is not present
-| b o | LPAD(string, length[, pattern ]) | Returns a string or
bytes value that consists of *string* prepended to *length* with *pattern*
+| b o | LPAD(string, length [, pattern ]) | Returns a string or
bytes value that consists of *string* prepended to *length* with *pattern*
| m | TO_BASE64(string) | Converts the *string*
to base-64 encoded form and returns a encoded string
| b m | FROM_BASE64(string) | Returns the decoded
result of a base-64 *string* as a string
| b o | LTRIM(string) | Returns *string* with
all blanks removed from the start
@@ -2723,7 +2725,7 @@ BigQuery's type system uses confusingly different names
for types and functions:
| b | PARSE_TIME(format, string) | Uses format specified
by *format* to convert *string* representation of time to a TIME value
| b | PARSE_TIMESTAMP(format, string[, timeZone]) | Uses format specified
by *format* to convert *string* representation of timestamp to a TIMESTAMP WITH
LOCAL TIME ZONE value in *timeZone*
| b | POW(numeric1, numeric2) | Returns *numeric1*
raised to the power *numeric2*
-| m o | REGEXP_REPLACE(string, regexp, rep, [, pos [, occurrence [,
matchType]]]) | Replaces all substrings of *string* that match *regexp* with
*rep* at the starting *pos* in expr (if omitted, the default is 1),
*occurrence* means which occurrence of a match to search for (if omitted, the
default is 1), *matchType* specifies how to perform matching
+| m o | REGEXP_REPLACE(string, regexp, rep [, pos [, occurrence [,
matchType]]]) | Replaces all substrings of *string* that match *regexp* with
*rep* at the starting *pos* in expr (if omitted, the default is 1),
*occurrence* means which occurrence of a match to search for (if omitted, the
default is 1), *matchType* specifies how to perform matching
| b m p | REPEAT(string, integer) | Returns a string
consisting of *string* repeated of *integer* times; returns an empty string if
*integer* is less than 1
| b m | REVERSE(string) | Returns *string* with
the order of the characters reversed
| b m p | RIGHT(string, length) | Returns the rightmost
*length* characters from the *string*
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 45e9d98ab7..b2f6f468a5 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -3761,7 +3761,7 @@ public class SqlOperatorTest {
f.checkScalarExact("position('b' in 'abcabc' FROM 3)", 5);
f.checkScalarExact("position('b' in 'abcabc' FROM 5)", 5);
f.checkScalarExact("position('b' in 'abcabc' FROM 6)", 0);
- f.checkScalarExact("position('b' in 'abcabc' FROM -5)", 0);
+ f.checkScalarExact("position('b' in 'abcabc' FROM -5)", 2);
f.checkScalarExact("position('' in 'abc' FROM 3)", 3);
f.checkScalarExact("position('' in 'abc' FROM 10)", 0);
@@ -3770,7 +3770,7 @@ public class SqlOperatorTest {
f.checkScalarExact("position(x'bb' in x'aabbccaabbcc' FROM 3)", 5);
f.checkScalarExact("position(x'bb' in x'aabbccaabbcc' FROM 5)", 5);
f.checkScalarExact("position(x'bb' in x'aabbccaabbcc' FROM 6)", 0);
- f.checkScalarExact("position(x'bb' in x'aabbccaabbcc' FROM -5)", 0);
+ f.checkScalarExact("position(x'bb' in x'aabbccaabbcc' FROM -5)", 2);
f.checkScalarExact("position(x'cc' in x'aabbccdd' FROM 2)", 3);
f.checkScalarExact("position(x'' in x'aabbcc' FROM 3)", 3);
f.checkScalarExact("position(x'' in x'aabbcc' FROM 10)", 0);
@@ -6308,6 +6308,37 @@ public class SqlOperatorTest {
f.checkNull("STRPOS(x'', null)");
}
+ @Test void testInstrFunction() {
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.CHR, VM_FENNEL, VM_JAVA);
+ f0.checkFails("^INSTR('abc', 'a', 1, 1)^",
+ "No match found for function signature INSTR\\(<CHARACTER>,
<CHARACTER>,"
+ + " <NUMERIC>, <NUMERIC>\\)", false);
+
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ // test for CHAR
+ f.checkScalar("INSTR('abc', 'a', 1, 1)", "1", "INTEGER NOT NULL");
+ f.checkScalar("INSTR('abcabc', 'bc', 1, 2)", "5", "INTEGER NOT NULL");
+ f.checkScalar("INSTR('abcabc', 'd', 1, 1)", "0", "INTEGER NOT NULL");
+ f.checkScalar("INSTR('dabcabcd', 'd', 4, 1)", "8", "INTEGER NOT NULL");
+ f.checkScalar("INSTR('abc', '', 1, 1)", "1", "INTEGER NOT NULL");
+ f.checkScalar("INSTR('', 'a', 1, 1)", "0", "INTEGER NOT NULL");
+ f.checkNull("INSTR(null, 'a', 1, 1)");
+ f.checkNull("INSTR('a', null, 1, 1)");
+
+ // test for BINARY
+ f.checkScalar("INSTR(x'2212', x'12', -1, 1)", "2", "INTEGER NOT NULL");
+ f.checkScalar("INSTR(x'2122', x'12', 1, 1)", "0", "INTEGER NOT NULL");
+ f.checkScalar("INSTR(x'122212', x'12', -1, 2)", "1", "INTEGER NOT NULL");
+ f.checkScalar("INSTR(x'1111', x'22', 1, 1)", "0", "INTEGER NOT NULL");
+ f.checkScalar("INSTR(x'2122', x'', 1, 1)", "1", "INTEGER NOT NULL");
+ f.checkScalar("INSTR(x'', x'12', 1, 1)", "0", "INTEGER NOT NULL");
+ f.checkNull("INSTR(null, x'', 1, 1)");
+ f.checkNull("INSTR(x'', null, 1, 1)");
+ };
+ f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE), consumer);
+ }
+
@Test void testStartsWithFunction() {
final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.BIG_QUERY);
f.setFor(SqlLibraryOperators.STARTS_WITH);
