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 ea9db4421f [CALCITE-5660] Add array subscript operators OFFSET,
ORDINAL, SAFE_OFFSET, SAFE_ORDINAL (enabled for BigQuery)
ea9db4421f is described below
commit ea9db4421f572c083f4724324df226edf1206f31
Author: Tanner Clary <[email protected]>
AuthorDate: Wed Apr 19 10:12:17 2023 -0700
[CALCITE-5660] Add array subscript operators OFFSET, ORDINAL, SAFE_OFFSET,
SAFE_ORDINAL (enabled for BigQuery)
Close apache/calcite#3167
---
babel/src/test/resources/sql/big-query.iq | 57 ++++++++++++++++
core/src/main/codegen/templates/Parser.jj | 23 ++++++-
.../calcite/adapter/enumerable/RexImpTable.java | 48 +++++++++++---
.../apache/calcite/runtime/CalciteResource.java | 3 +
.../org/apache/calcite/runtime/SplitOperation.java | 2 +-
.../org/apache/calcite/runtime/SqlFunctions.java | 36 +++++++---
.../calcite/sql/dialect/BigQuerySqlDialect.java | 21 ++++++
.../apache/calcite/sql/fun/SqlItemOperator.java | 39 ++++++-----
.../calcite/sql/fun/SqlLibraryOperators.java | 24 +++++++
.../calcite/sql/fun/SqlStdOperatorTable.java | 3 +-
.../org/apache/calcite/sql/type/OperandTypes.java | 6 ++
.../calcite/sql/validate/SqlValidatorImpl.java | 2 +-
.../org/apache/calcite/util/BuiltInMethod.java | 3 +-
.../calcite/runtime/CalciteResource.properties | 1 +
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 18 +++++
site/_docs/reference.md | 7 ++
.../org/apache/calcite/test/SqlOperatorTest.java | 76 ++++++++++++++++++++++
17 files changed, 331 insertions(+), 38 deletions(-)
diff --git a/babel/src/test/resources/sql/big-query.iq
b/babel/src/test/resources/sql/big-query.iq
index 941c30657b..ae2ebbf3ef 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -730,6 +730,63 @@ SELECT SPLIT(x'abc2') as result;
Call to function 'SPLIT' with argument of type 'BINARY(2)' requires extra
delimiter argument
!error
+#####################################################################
+# ARRAY SUBSCRIPT OPERATORS
+#
+# OFFSET, ORDINAL, SAFE_OFFSET, SAFE_ORDINAL
+#
+# Gets a value from an array at a specific position.
+#
+# OFFSET(index): The index starts at zero. Produces an error if the index is
out of range.
+# SAFE_OFFSET(index): The index starts at zero. Returns NULL if the index is
out of range.
+# ORDINAL(index): The index starts at one. Produces an error if the index is
out of range.
+# SAFE_ORDINAL(index): The index starts at one. Returns NULL if the index is
out of range.
+
+SELECT
+ SPLIT('h,e,l,l,o')[OFFSET(2)] as offset_idx,
+ SPLIT('h,e,l,l,o')[ORDINAL(2)] as ordinal_idx,
+ SPLIT('h,e,l,l,o')[SAFE_OFFSET(2)] as safe_offset_idx,
+ SPLIT('h,e,l,l,o')[SAFE_ORDINAL(2)] as safe_ordinal_idx;
++------------+-------------+-----------------+------------------+
+| offset_idx | ordinal_idx | safe_offset_idx | safe_ordinal_idx |
++------------+-------------+-----------------+------------------+
+| l | e | l | e |
++------------+-------------+-----------------+------------------+
+(1 row)
+
+!ok
+
+SELECT SPLIT('h,e,l,l,o')[OFFSET(-1)] as offset_idx;
+Array index -1 is out of bounds
+!error
+
+SELECT SPLIT('h,e,l,l,o')[ORDINAL(7)] as ordinal_idx;
+Array index 7 is out of bounds
+!error
+
+SELECT SPLIT('h,e,l,l,o')[SAFE_OFFSET(-1)] as safe_offset_idx;
++-----------------+
+| safe_offset_idx |
++-----------------+
+| |
++-----------------+
+(1 row)
+
+!ok
+
+SELECT SPLIT('h,e,l,l,o')[SAFE_ORDINAL(7)] as safe_ordinal_idx;
++------------------+
+| safe_ordinal_idx |
++------------------+
+| |
++------------------+
+(1 row)
+
+!ok
+
+SELECT OFFSET(1);
+java.sql.SQLException: Error while executing SQL "SELECT OFFSET(1)": parse
failed: Incorrect syntax near the keyword 'OFFSET' at line 1, column 8.
+!error
#####################################################################
# LN
#
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 3c09e77887..d5d2db54cb 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -3589,6 +3589,7 @@ List<Object> Expression2(ExprContext exprContext) :
final List<Object> list3 = new ArrayList();
SqlNodeList nodeList;
SqlNode e;
+ SqlOperator itemOp;
SqlOperator op;
SqlIdentifier p;
final Span s = span();
@@ -3729,11 +3730,12 @@ List<Object> Expression2(ExprContext exprContext) :
AddExpression2b(list, ExprContext.ACCEPT_SUB_QUERY)
|
<LBRACKET>
+ itemOp = getItemOp()
e = Expression(ExprContext.ACCEPT_SUB_QUERY)
<RBRACKET> {
list.add(
new SqlParserUtil.ToTreeListItem(
- SqlStdOperatorTable.ITEM, getPos()));
+ itemOp, getPos()));
list.add(e);
}
(
@@ -3764,6 +3766,22 @@ List<Object> Expression2(ExprContext exprContext) :
)
}
+/** Returns the appropriate ITEM operator for indexing arrays. */
+SqlOperator getItemOp() :
+{
+}
+{
+ <OFFSET> { return SqlLibraryOperators.OFFSET; }
+|
+ <ORDINAL> { return SqlLibraryOperators.ORDINAL; }
+|
+ <SAFE_OFFSET> { return SqlLibraryOperators.SAFE_OFFSET; }
+|
+ <SAFE_ORDINAL> { return SqlLibraryOperators.SAFE_ORDINAL; }
+|
+ { return SqlStdOperatorTable.ITEM; }
+}
+
/** Parses a comparison operator inside a SOME / ALL predicate. */
SqlKind comp() :
{
@@ -8101,6 +8119,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < OR: "OR" >
| < ORDER: "ORDER" >
| < ORDERING: "ORDERING" >
+| < ORDINAL: "ORDINAL" >
| < ORDINALITY: "ORDINALITY" >
| < OTHERS: "OTHERS" >
| < OUT: "OUT" >
@@ -8205,6 +8224,8 @@ SqlPostfixOperator PostfixRowOperator() :
| < ROWS: "ROWS" >
| < RUNNING: "RUNNING" >
| < SAFE_CAST: "SAFE_CAST" >
+| < SAFE_OFFSET: "SAFE_OFFSET" >
+| < SAFE_ORDINAL: "SAFE_ORDINAL" >
| < SATURDAY: "SATURDAY" >
| < SAVEPOINT: "SAVEPOINT" >
| < SCALAR: "SCALAR" >
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 26746290c5..9ad452277c 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
@@ -59,6 +59,7 @@ import org.apache.calcite.sql.SqlMatchFunction;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlTypeConstructorFunction;
import org.apache.calcite.sql.SqlWindowTableFunction;
+import org.apache.calcite.sql.fun.SqlItemOperator;
import org.apache.calcite.sql.fun.SqlJsonArrayAggAggFunction;
import org.apache.calcite.sql.fun.SqlJsonObjectAggAggFunction;
import org.apache.calcite.sql.fun.SqlQuantifyOperator;
@@ -163,6 +164,8 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.MAX_BY;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.MD5;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.MIN_BY;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.MONTHNAME;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.OFFSET;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.ORDINAL;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.PARSE_DATE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.PARSE_DATETIME;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.PARSE_TIME;
@@ -175,6 +178,8 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.RIGHT;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.RLIKE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.RPAD;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_CAST;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_OFFSET;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_ORDINAL;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SECH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SHA1;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SHA256;
@@ -702,7 +707,15 @@ public class RexImpTable {
final RexCallImplementor value = new ValueConstructorImplementor();
map.put(MAP_VALUE_CONSTRUCTOR, value);
map.put(ARRAY_VALUE_CONSTRUCTOR, value);
+
+ // ITEM operator
map.put(ITEM, new ItemImplementor());
+ // BigQuery array subscript operators
+ final ArrayItemImplementor arrayItemImplementor = new
ArrayItemImplementor();
+ map.put(OFFSET, arrayItemImplementor);
+ map.put(ORDINAL, arrayItemImplementor);
+ map.put(SAFE_OFFSET, arrayItemImplementor);
+ map.put(SAFE_ORDINAL, arrayItemImplementor);
map.put(DEFAULT, new DefaultImplementor());
@@ -3082,27 +3095,46 @@ public class RexImpTable {
}
}
- /** Implementor for the {@code ITEM} SQL operator. */
+ /** Implementor for indexing an array using the {@code ITEM} SQL operator
+ * and the {@code OFFSET}, {@code ORDINAL}, {@code SAFE_OFFSET}, and
+ * {@code SAFE_ORDINAL} BigQuery operators. */
+ private static class ArrayItemImplementor extends AbstractRexCallImplementor
{
+ ArrayItemImplementor() {
+ super("array_item", NullPolicy.STRICT, false);
+ }
+
+ @Override Expression implementSafe(final RexToLixTranslator translator,
+ final RexCall call, final List<Expression> argValueList) {
+ final SqlItemOperator itemOperator = (SqlItemOperator)
call.getOperator();
+ return Expressions.call(BuiltInMethod.ARRAY_ITEM.method,
+ Expressions.list(argValueList)
+ .append(Expressions.constant(itemOperator.offset))
+ .append(Expressions.constant(itemOperator.safe)));
+ }
+ }
+
+ /** General implementor for indexing a collection using the {@code ITEM} SQL
operator. If the
+ * collection is an array, an instance of the ArrayItemImplementor is used
to handle
+ * additional offset and out-of-bounds behavior that is only applicable for
arrays. */
private static class ItemImplementor extends AbstractRexCallImplementor {
ItemImplementor() {
super("item", NullPolicy.STRICT, false);
}
- // Since we follow PostgreSQL's semantics that an out-of-bound reference
- // returns NULL, x[y] can return null even if x and y are both NOT NULL.
- // (In SQL standard semantics, an out-of-bound reference to an array
- // throws an exception.)
@Override Expression implementSafe(final RexToLixTranslator translator,
final RexCall call, final List<Expression> argValueList) {
- final MethodImplementor implementor =
+ final AbstractRexCallImplementor implementor =
getImplementor(call.getOperands().get(0).getType().getSqlTypeName());
return implementor.implementSafe(translator, call, argValueList);
}
- private MethodImplementor getImplementor(SqlTypeName sqlTypeName) {
+ // This helper returns the appropriate implementor based on the collection
type.
+ // Arrays use the specific ArrayItemImplementor while maps and other
collection types
+ // use the general MethodImplementor.
+ private AbstractRexCallImplementor getImplementor(SqlTypeName sqlTypeName)
{
switch (sqlTypeName) {
case ARRAY:
- return new MethodImplementor(BuiltInMethod.ARRAY_ITEM.method,
nullPolicy, false);
+ return new ArrayItemImplementor();
case MAP:
return new MethodImplementor(BuiltInMethod.MAP_ITEM.method,
nullPolicy, false);
default:
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 7695ad8364..451e769516 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -885,6 +885,9 @@ public interface CalciteResource {
@BaseMessage("Third argument (pad pattern) for LPAD/RPAD must not be empty")
ExInst<CalciteException> illegalEmptyPadPattern();
+ @BaseMessage("Array index {0,number,#} is out of bounds")
+ ExInst<CalciteException> arrayIndexOutOfBounds(int idx);
+
@BaseMessage("Substring error: negative substring length not allowed")
ExInst<CalciteException> illegalNegativeSubstringLength();
diff --git a/core/src/main/java/org/apache/calcite/runtime/SplitOperation.java
b/core/src/main/java/org/apache/calcite/runtime/SplitOperation.java
index ffd6660bd8..6bba69ed7d 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SplitOperation.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SplitOperation.java
@@ -153,7 +153,7 @@ public class SplitOperation {
return factory.buildGeometry(polygons);
}
- private Geometry split(MultiPolygon geometry, LineString blade) {
+ private static Geometry split(MultiPolygon geometry, LineString blade) {
GeometryFactory factory = geometry.getFactory();
List<Geometry> geometries = new ArrayList<>();
for (int i = 0; i < geometry.getNumGeometries(); i++) {
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 be211650a0..83a511ff12 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -3655,12 +3655,25 @@ public class SqlFunctions {
}
/** Helper for "array element reference". Caller has already ensured that
- * array and index are not null. Index is 1-based, per SQL. */
- public static @Nullable Object arrayItem(List list, int item) {
- if (item < 1 || item > list.size()) {
- return null;
+ * array and index are not null.
+ *
+ * <p>Index may be 0- or 1-based depending on which array subscript operator
+ * is being used. {@code ITEM}, {@code ORDINAL}, and {@code SAFE_ORDINAL}
+ * are 1-based, while {@code OFFSET} and {@code SAFE_OFFSET} are 0-based.
+ *
+ * <p>The {@code ITEM}, {@code SAFE_OFFSET}, and {@code SAFE_ORDINAL}
+ * operators return null if the index is out of bounds, while the others
+ * throw an error. */
+ public static @Nullable Object arrayItem(List list, int item, int offset,
+ boolean safe) {
+ if (item < offset || item > list.size() + 1 - offset) {
+ if (safe) {
+ return null;
+ } else {
+ throw RESOURCE.arrayIndexOutOfBounds(item).ex();
+ }
}
- return list.get(item - 1);
+ return list.get(item - offset);
}
/** Helper for "map element reference". Caller has already ensured that
@@ -3677,7 +3690,7 @@ public class SqlFunctions {
return mapItem((Map) object, index);
}
if (object instanceof List && index instanceof Number) {
- return arrayItem((List) object, ((Number) index).intValue());
+ return arrayItem((List) object, ((Number) index).intValue(), 1, true);
}
if (index instanceof Number) {
return structAccess(object, ((Number) index).intValue() - 1, null); // 1
indexed
@@ -3690,15 +3703,17 @@ public class SqlFunctions {
}
/** As {@link #arrayItem} method, but allows array to be nullable. */
- public static @Nullable Object arrayItemOptional(@Nullable List list, int
item) {
+ public static @Nullable Object arrayItemOptional(@Nullable List list,
+ int item, int offset, boolean safe) {
if (list == null) {
return null;
}
- return arrayItem(list, item);
+ return arrayItem(list, item, offset, safe);
}
/** As {@link #mapItem} method, but allows map to be nullable. */
- public static @Nullable Object mapItemOptional(@Nullable Map map, Object
item) {
+ public static @Nullable Object mapItemOptional(@Nullable Map map,
+ Object item) {
if (map == null) {
return null;
}
@@ -3706,7 +3721,8 @@ public class SqlFunctions {
}
/** As {@link #item} method, but allows object to be nullable. */
- public static @Nullable Object itemOptional(@Nullable Object object, Object
index) {
+ public static @Nullable Object itemOptional(@Nullable Object object,
+ Object index) {
if (object == null) {
return null;
}
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 3ccb062219..f2a3f7fc26 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
@@ -207,6 +207,13 @@ public class BigQuerySqlDialect extends SqlDialect {
case TRIM:
unparseTrim(writer, call, leftPrec, rightPrec);
break;
+ case ITEM:
+ if (call.getOperator().getName().equals("ITEM")) {
+ throw new RuntimeException("BigQuery requires an array subscript
operator"
+ + " to index an array");
+ }
+ unparseItem(writer, call, leftPrec);
+ break;
default:
super.unparseCall(writer, call, leftPrec, rightPrec);
}
@@ -276,6 +283,20 @@ public class BigQuerySqlDialect extends SqlDialect {
writer.endFunCall(trimFrame);
}
+ /** When indexing an array in BigQuery, an array subscript operator must
+ * surround the desired index. For the standard ITEM operator used by other
+ * dialects in Calcite, ITEM is not included in the unparsing. This helper
+ * ensures that the operator is preserved when being unparsed. */
+ private static void unparseItem(SqlWriter writer, SqlCall call, int
leftPrec) {
+ String operatorName = call.getOperator().getName();
+ call.operand(0).unparse(writer, leftPrec, 0);
+ final SqlWriter.Frame frame = writer.startList("[", "]");
+ final SqlWriter.Frame subscriptFrame = writer.startFunCall(operatorName);
+ call.operand(1).unparse(writer, 0, 0);
+ writer.endFunCall(subscriptFrame);
+ writer.endList(frame);
+ }
+
private static TimeUnit validate(TimeUnit timeUnit) {
switch (timeUnit) {
case MICROSECOND:
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlItemOperator.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlItemOperator.java
index 928563e570..627115f5a3 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlItemOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlItemOperator.java
@@ -47,15 +47,16 @@ import static java.util.Objects.requireNonNull;
* array, map or struct. For example, {@code myArray[3]}, {@code
"myMap['foo']"},
* {@code myStruct[2]} or {@code myStruct['fieldName']}.
*/
-class SqlItemOperator extends SqlSpecialOperator {
+public class SqlItemOperator extends SqlSpecialOperator {
+ public final int offset;
+ public final boolean safe;
- private static final SqlSingleOperandTypeChecker ARRAY_OR_MAP =
- OperandTypes.family(SqlTypeFamily.ARRAY)
- .or(OperandTypes.family(SqlTypeFamily.MAP))
- .or(OperandTypes.family(SqlTypeFamily.ANY));
-
- SqlItemOperator() {
- super("ITEM", SqlKind.ITEM, 100, true, null, null, null);
+ public SqlItemOperator(String name,
+ SqlSingleOperandTypeChecker operandTypeChecker,
+ int offset, boolean safe) {
+ super(name, SqlKind.ITEM, 100, true, null, null, operandTypeChecker);
+ this.offset = offset;
+ this.safe = safe;
}
@Override public ReduceResult reduceExpr(int ordinal,
@@ -85,12 +86,11 @@ class SqlItemOperator extends SqlSpecialOperator {
return SqlOperandCountRanges.of(2);
}
- @Override public boolean checkOperandTypes(
- SqlCallBinding callBinding,
+ @Override public boolean checkOperandTypes(SqlCallBinding callBinding,
boolean throwOnFailure) {
final SqlNode left = callBinding.operand(0);
final SqlNode right = callBinding.operand(1);
- if (!ARRAY_OR_MAP.checkSingleOperandType(callBinding, left, 0,
+ if (!getOperandTypeChecker().checkSingleOperandType(callBinding, left, 0,
throwOnFailure)) {
return false;
}
@@ -99,6 +99,11 @@ class SqlItemOperator extends SqlSpecialOperator {
throwOnFailure);
}
+ @Override public SqlSingleOperandTypeChecker getOperandTypeChecker() {
+ return (SqlSingleOperandTypeChecker)
+ requireNonNull(super.getOperandTypeChecker(), "operandTypeChecker");
+ }
+
private static SqlSingleOperandTypeChecker getChecker(SqlCallBinding
callBinding) {
final RelDataType operandType = callBinding.getOperandType(0);
switch (operandType.getSqlTypeName()) {
@@ -122,9 +127,13 @@ class SqlItemOperator extends SqlSpecialOperator {
}
@Override public String getAllowedSignatures(String name) {
- return "<ARRAY>[<INTEGER>]\n"
- + "<MAP>[<ANY>]\n"
- + "<ROW>[<CHARACTER>|<INTEGER>]";
+ if (name.equals("ITEM")) {
+ return "<ARRAY>[<INTEGER>]\n"
+ + "<MAP>[<ANY>]\n"
+ + "<ROW>[<CHARACTER>|<INTEGER>]";
+ } else {
+ return "<ARRAY>[" + name + "(<INTEGER>)]";
+ }
}
@Override public RelDataType inferReturnType(SqlOperatorBinding opBinding) {
@@ -164,7 +173,7 @@ class SqlItemOperator extends SqlSpecialOperator {
throw new AssertionError("Unsupported field identifier type: '"
+ indexType + "'");
}
- if (fieldType != null && operandType.isNullable()) {
+ if (operandType.isNullable()) {
fieldType = typeFactory.createTypeWithNullability(fieldType, true);
}
return fieldType;
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 492fb14493..b75dcab0fb 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
@@ -1316,6 +1316,30 @@ public abstract class SqlLibraryOperators {
public static final SqlFunction TRY_CAST =
new SqlCastFunction("TRY_CAST", SqlKind.SAFE_CAST);
+ /** The "OFFSET(index)" array subscript operator used by BigQuery. The index
+ * starts at 0 and produces an error if the index is out of range. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlOperator OFFSET =
+ new SqlItemOperator("OFFSET", OperandTypes.ARRAY, 0, false);
+
+ /** The "ORDINAL(index)" array subscript operator used by BigQuery. The index
+ * starts at 1 and produces an error if the index is out of range. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlOperator ORDINAL =
+ new SqlItemOperator("ORDINAL", OperandTypes.ARRAY, 1, false);
+
+ /** The "SAFE_OFFSET(index)" array subscript operator used by BigQuery. The
index
+ * starts at 0 and returns null if the index is out of range. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlOperator SAFE_OFFSET =
+ new SqlItemOperator("SAFE_OFFSET", OperandTypes.ARRAY, 0, true);
+
+ /** The "SAFE_ORDINAL(index)" array subscript operator used by BigQuery. The
index
+ * starts at 1 and returns null if the index is out of range. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlOperator SAFE_ORDINAL =
+ new SqlItemOperator("SAFE_ORDINAL", OperandTypes.ARRAY, 1, true);
+
/** NULL-safe "<=>" equal operator used by MySQL, for example
* {@code 1<=>NULL}. */
@LibraryOperator(libraries = { MYSQL })
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 c37d88efc5..9332de3c5b 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
@@ -2112,7 +2112,8 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
*
* <p>MAP is not standard SQL.</p>
*/
- public static final SqlOperator ITEM = new SqlItemOperator();
+ public static final SqlOperator ITEM =
+ new SqlItemOperator("ITEM", OperandTypes.ARRAY_OR_MAP, 1, true);
/**
* The ARRAY Value Constructor. e.g. "<code>ARRAY[1, 2, 3]</code>".
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 e8029903b7..ff14401bcf 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
@@ -465,6 +465,12 @@ public abstract class OperandTypes {
public static final SqlSingleOperandTypeChecker ARRAY =
family(SqlTypeFamily.ARRAY);
+
+ public static final SqlSingleOperandTypeChecker ARRAY_OR_MAP =
+ OperandTypes.family(SqlTypeFamily.ARRAY)
+ .or(OperandTypes.family(SqlTypeFamily.MAP))
+ .or(OperandTypes.family(SqlTypeFamily.ANY));
+
/** Checks that returns whether a value is a multiset or an array.
* Cf Java, where list and set are collections but a map is not. */
public static final SqlSingleOperandTypeChecker COLLECTION =
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 81ce2d7178..5ecb85591f 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -747,7 +747,7 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
}
}
- private int calculatePermuteOffset(List<SqlNode> selectItems) {
+ private static int calculatePermuteOffset(List<SqlNode> selectItems) {
for (int i = 0; i < selectItems.size(); i++) {
SqlNode selectItem = selectItems.get(i);
SqlNode col = SqlUtil.stripAs(selectItem);
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 4cc5dd3227..6b02218ec8 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -339,7 +339,8 @@ public enum BuiltInMethod {
Object.class, int.class, int.class, Function1.class, Comparator.class),
BINARY_SEARCH6_UPPER(BinarySearch.class, "upperBound", Object[].class,
Object.class, int.class, int.class, Function1.class, Comparator.class),
- ARRAY_ITEM(SqlFunctions.class, "arrayItemOptional", List.class, int.class),
+ ARRAY_ITEM(SqlFunctions.class, "arrayItemOptional", List.class, int.class,
+ int.class, boolean.class),
MAP_ITEM(SqlFunctions.class, "mapItemOptional", Map.class, Object.class),
ANY_ITEM(SqlFunctions.class, "itemOptional", Object.class, Object.class),
UPPER(SqlFunctions.class, "upper", String.class),
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 1f7f847eba..28d065e0d2 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -42,6 +42,7 @@ IllegalJoinExpression=Join expression encountered in illegal
context
ExpectedQueryOrJoinExpression=Expected query or join
IllegalBinaryString=Illegal binary string {0}
IllegalArrayExpression=Illegal array expression ''{0}''
+ArrayIndexOutOfBounds=Array index {0,number,#} is out of bounds
IllegalFromEmpty=''FROM'' without operands preceding it is illegal
IllegalRowExpression=ROW expression encountered in illegal context
IllegalColon=Illegal identifier '':''. Was expecting ''VALUE''
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 fc5ec3f03a..f9eaf8fe9a 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
@@ -6503,6 +6503,24 @@ class RelToSqlConverterTest {
.withRedshift().ok(expected);
}
+ @Test void testIndexOperatorsBigQuery() {
+ Consumer<String> consumer = operator -> {
+ String query = "SELECT SPLIT('h,e,l,l,o')[" + operator + "(1)] FROM
\"employee\"";
+ String expected = "SELECT SPLIT('h,e,l,l,o')[" + operator + "(1)]\nFROM
foodmart.employee";
+ sql(query).withBigQuery().withLibrary(SqlLibrary.BIG_QUERY).ok(expected);
+ };
+ consumer.accept("OFFSET");
+ consumer.accept("ORDINAL");
+ consumer.accept("SAFE_OFFSET");
+ consumer.accept("SAFE_ORDINAL");
+ }
+
+ @Test void testIndexWithoutOperatorBigQuery() {
+ String query = "SELECT SPLIT('h,e,l,l,o')[1] FROM \"employee\"";
+ String error = "BigQuery requires an array subscript operator to index an
array";
+ sql(query).withBigQuery().withLibrary(SqlLibrary.BIG_QUERY).throws_(error);
+ }
+
@Test void testDateLiteralOracle() {
String query = "SELECT DATE '1978-05-02' FROM \"employee\"";
String expected = "SELECT TO_DATE('1978-05-02', 'YYYY-MM-DD')\n"
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index e11d31fa89..52d8bfcb35 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -799,6 +799,7 @@ OPTIONS,
**OR**,
**ORDER**,
ORDERING,
+**ORDINAL**,
ORDINALITY,
OTHERS,
**OUT**,
@@ -903,6 +904,8 @@ ROW_COUNT,
**ROW_NUMBER**,
**RUNNING**,
**SAFE_CAST**,
+**SAFE_OFFSET**,
+**SAFE_ORDINAL**,
**SATURDAY**,
**SAVEPOINT**,
SCALAR,
@@ -2722,6 +2725,8 @@ BigQuery's type system uses confusingly different names
for types and functions:
| b m p | MD5(string) | Calculates an MD5
128-bit checksum of *string* and returns it as a hex string
| m | MONTHNAME(date) | Returns the name, in
the connection's locale, of the month in *datetime*; for example, it returns
'二月' for both DATE '2020-02-10' and TIMESTAMP '2020-02-10 10:10:10'
| o | NVL(value1, value2) | Returns *value1* if
*value1* is not null, otherwise *value2*
+| b | OFFSET(index) | When indexing an array,
wrapping *index* in `OFFSET` returns the value at the 0-based *index*; throws
error if *index* is out of bounds
+| b | ORDINAL(index) | Similar to `OFFSET`
except *index* begins at 1
| b | PARSE_DATE(format, string) | Uses format specified
by *format* to convert *string* representation of date to a DATE value
| b | PARSE_DATETIME(format, string) | Uses format specified
by *format* to convert *string* representation of datetime to a TIMESTAMP value
| b | PARSE_TIME(format, string) | Uses format specified
by *format* to convert *string* representation of time to a TIME value
@@ -2736,6 +2741,8 @@ BigQuery's type system uses confusingly different names
for types and functions:
| b o | RPAD(string, length[, pattern ]) | Returns a string or
bytes value that consists of *string* appended to *length* with *pattern*
| b o | RTRIM(string) | Returns *string* with
all blanks removed from the end
| b | SAFE_CAST(value AS type) | Converts *value* to
*type*, returning NULL if conversion fails
+| b | SAFE_OFFSET(index) | Similar to `OFFSET`
except null is returned if *index* is out of bounds
+| b | SAFE_ORDINAL(index) | Similar to `OFFSET`
except *index* begins at 1 and null is returned if *index* is out of bounds
| * | SECH(numeric) | Returns the hyperbolic
secant of *numeric*
| b m p | SHA1(string) | Calculates a SHA-1 hash
value of *string* and returns it as a hex string
| b p | SHA256(string) | Calculates a SHA-256
hash value of *string* and returns it as a hex 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 42e946a4ef..9339bf2d95 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -7950,6 +7950,82 @@ public class SqlOperatorTest {
+ "RecordType\\(INTEGER EXPR\\$0, INTEGER EXPR\\$1\\)", false);
}
+ @Test void testOffsetOperator() {
+ final SqlOperatorFixture f0 = fixture();
+ f0.setFor(SqlLibraryOperators.OFFSET);
+ f0.checkFails("^ARRAY[2,4,6][OFFSET(2)]^",
+ "No match found for function signature OFFSET", false);
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
+ f.checkScalar("ARRAY[2,4,6][OFFSET(2)]", "6", "INTEGER");
+ f.checkScalar("ARRAY[2,4,6][OFFSET(0)]", "2", "INTEGER");
+ f.checkScalar("ARRAY[2,4,6,8,10][OFFSET(1+2)]", "8", "INTEGER");
+ f.checkNull("ARRAY[2,4,6][OFFSET(null)]");
+ f.checkFails("ARRAY[2,4,6][OFFSET(-1)]",
+ "Array index -1 is out of bounds", true);
+ f.checkFails("ARRAY[2,4,6][OFFSET(5)]",
+ "Array index 5 is out of bounds", true);
+ f.checkFails("^map['foo', 3, 'bar', 7][offset('bar')]^",
+ "Cannot apply 'OFFSET' to arguments of type 'OFFSET\\(<\\(CHAR\\(3\\)"
+ + ", INTEGER\\) MAP>, <CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): "
+ + "<ARRAY>\\[OFFSET\\(<INTEGER>\\)\\]", false);
+ }
+
+ @Test void testOrdinalOperator() {
+ final SqlOperatorFixture f0 = fixture();
+ f0.setFor(SqlLibraryOperators.ORDINAL);
+ f0.checkFails("^ARRAY[2,4,6][ORDINAL(2)]^",
+ "No match found for function signature ORDINAL", false);
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
+ f.checkScalar("ARRAY[2,4,6][ORDINAL(3)]", "6", "INTEGER");
+ f.checkScalar("ARRAY[2,4,6][ORDINAL(1)]", "2", "INTEGER");
+ f.checkScalar("ARRAY[2,4,6,8,10][ORDINAL(1+2)]", "6", "INTEGER");
+ f.checkNull("ARRAY[2,4,6][ORDINAL(null)]");
+ f.checkFails("ARRAY[2,4,6][ORDINAL(-1)]",
+ "Array index -1 is out of bounds", true);
+ f.checkFails("ARRAY[2,4,6][ORDINAL(5)]",
+ "Array index 5 is out of bounds", true);
+ f.checkFails("^map['foo', 3, 'bar', 7][ordinal('bar')]^",
+ "Cannot apply 'ORDINAL' to arguments of type
'ORDINAL\\(<\\(CHAR\\(3\\)"
+ + ", INTEGER\\) MAP>, <CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): "
+ + "<ARRAY>\\[ORDINAL\\(<INTEGER>\\)\\]", false);
+ }
+
+ @Test void testSafeOffsetOperator() {
+ final SqlOperatorFixture f0 = fixture();
+ f0.setFor(SqlLibraryOperators.SAFE_OFFSET);
+ f0.checkFails("^ARRAY[2,4,6][SAFE_OFFSET(2)]^",
+ "No match found for function signature SAFE_OFFSET", false);
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
+ f.checkScalar("ARRAY[2,4,6][SAFE_OFFSET(2)]", "6", "INTEGER");
+ f.checkScalar("ARRAY[2,4,6][SAFE_OFFSET(0)]", "2", "INTEGER");
+ f.checkScalar("ARRAY[2,4,6,8,10][SAFE_OFFSET(1+2)]", "8", "INTEGER");
+ f.checkScalar("ARRAY[2,4,6][SAFE_OFFSET(-1)]", isNullValue(), "INTEGER");
+ f.checkScalar("ARRAY[2,4,6][SAFE_OFFSET(5)]", isNullValue(), "INTEGER");
+ f.checkNull("ARRAY[2,4,6][SAFE_OFFSET(null)]");
+ f.checkFails("^map['foo', 3, 'bar', 7][safe_offset('bar')]^",
+ "Cannot apply 'SAFE_OFFSET' to arguments of type
'SAFE_OFFSET\\(<\\(CHAR\\(3\\)"
+ + ", INTEGER\\) MAP>, <CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): "
+ + "<ARRAY>\\[SAFE_OFFSET\\(<INTEGER>\\)\\]", false);
+ }
+
+ @Test void testSafeOrdinalOperator() {
+ final SqlOperatorFixture f0 = fixture();
+ f0.setFor(SqlLibraryOperators.SAFE_ORDINAL);
+ f0.checkFails("^ARRAY[2,4,6][SAFE_ORDINAL(2)]^",
+ "No match found for function signature SAFE_ORDINAL", false);
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
+ f.checkScalar("ARRAY[2,4,6][SAFE_ORDINAL(3)]", "6", "INTEGER");
+ f.checkScalar("ARRAY[2,4,6][SAFE_ORDINAL(1)]", "2", "INTEGER");
+ f.checkScalar("ARRAY[2,4,6,8,10][SAFE_ORDINAL(1+2)]", "6", "INTEGER");
+ f.checkScalar("ARRAY[2,4,6][SAFE_ORDINAL(-1)]", isNullValue(), "INTEGER");
+ f.checkScalar("ARRAY[2,4,6][SAFE_ORDINAL(5)]", isNullValue(), "INTEGER");
+ f.checkNull("ARRAY[2,4,6][SAFE_ORDINAL(null)]");
+ f.checkFails("^map['foo', 3, 'bar', 7][safe_ordinal('bar')]^",
+ "Cannot apply 'SAFE_ORDINAL' to arguments of type
'SAFE_ORDINAL\\(<\\(CHAR\\(3\\)"
+ + ", INTEGER\\) MAP>, <CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): "
+ + "<ARRAY>\\[SAFE_ORDINAL\\(<INTEGER>\\)\\]", false);
+ }
+
@Test void testMapValueConstructor() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.MAP_VALUE_CONSTRUCTOR, VM_JAVA);
