You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2017/07/02 08:47:28 UTC
[2/2] calcite git commit: [CALCITE-1834] Allow user-defined functions
to have arguments that are ARRAY or MULTISET (Ankit Singhal)
[CALCITE-1834] Allow user-defined functions to have arguments that are ARRAY or MULTISET (Ankit Singhal)
Implement precedence list for Array and Multiset
Close apache/calcite#469
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/319e888f
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/319e888f
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/319e888f
Branch: refs/heads/master
Commit: 319e888f792f1f416cbddbf77f02fd167195eef2
Parents: 5a91d0a
Author: Ankit Singhal <an...@gmail.com>
Authored: Fri Jun 23 20:11:48 2017 +0530
Committer: Julian Hyde <jh...@apache.org>
Committed: Fri Jun 30 13:52:18 2017 -0700
----------------------------------------------------------------------
.../apache/calcite/sql/type/ArraySqlType.java | 23 +++
.../sql/type/JavaToSqlTypeConversionRules.java | 1 +
.../calcite/sql/type/MultisetSqlType.java | 23 ++-
.../calcite/sql/type/SqlTypeFactoryTest.java | 48 ++++++
.../java/org/apache/calcite/test/UdfTest.java | 150 +++++++++++++++++++
.../java/org/apache/calcite/util/Smalls.java | 11 ++
6 files changed, 255 insertions(+), 1 deletion(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/319e888f/core/src/main/java/org/apache/calcite/sql/type/ArraySqlType.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/type/ArraySqlType.java b/core/src/main/java/org/apache/calcite/sql/type/ArraySqlType.java
index fdc3a6f..c3ae782 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/ArraySqlType.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/ArraySqlType.java
@@ -18,6 +18,7 @@ package org.apache.calcite.sql.type;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFamily;
+import org.apache.calcite.rel.type.RelDataTypePrecedenceList;
import com.google.common.base.Preconditions;
@@ -62,6 +63,28 @@ public class ArraySqlType extends AbstractSqlType {
public RelDataTypeFamily getFamily() {
return this;
}
+
+ @Override public RelDataTypePrecedenceList getPrecedenceList() {
+ return new RelDataTypePrecedenceList() {
+ public boolean containsType(RelDataType type) {
+ return type.getSqlTypeName() == getSqlTypeName()
+ && type.getComponentType() != null
+ && getComponentType().getPrecedenceList().containsType(
+ type.getComponentType());
+ }
+
+ public int compareTypePrecedence(RelDataType type1, RelDataType type2) {
+ if (!containsType(type1)) {
+ throw new IllegalArgumentException("must contain type: " + type1);
+ }
+ if (!containsType(type2)) {
+ throw new IllegalArgumentException("must contain type: " + type2);
+ }
+ return getComponentType().getPrecedenceList()
+ .compareTypePrecedence(type1.getComponentType(), type2.getComponentType());
+ }
+ };
+ }
}
// End ArraySqlType.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/319e888f/core/src/main/java/org/apache/calcite/sql/type/JavaToSqlTypeConversionRules.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/type/JavaToSqlTypeConversionRules.java b/core/src/main/java/org/apache/calcite/sql/type/JavaToSqlTypeConversionRules.java
index 7f06bb6..c096dae 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/JavaToSqlTypeConversionRules.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/JavaToSqlTypeConversionRules.java
@@ -73,6 +73,7 @@ public class JavaToSqlTypeConversionRules {
.put(ResultSet.class, SqlTypeName.CURSOR)
.put(ColumnList.class, SqlTypeName.COLUMN_LIST)
.put(ArrayImpl.class, SqlTypeName.ARRAY)
+ .put(List.class, SqlTypeName.ARRAY)
.build();
//~ Methods ----------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/319e888f/core/src/main/java/org/apache/calcite/sql/type/MultisetSqlType.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/type/MultisetSqlType.java b/core/src/main/java/org/apache/calcite/sql/type/MultisetSqlType.java
index eae3d62..f976716 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/MultisetSqlType.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/MultisetSqlType.java
@@ -18,6 +18,7 @@ package org.apache.calcite.sql.type;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFamily;
+import org.apache.calcite.rel.type.RelDataTypePrecedenceList;
/**
* MultisetSqlType represents a standard SQL2003 multiset type.
@@ -69,7 +70,27 @@ public class MultisetSqlType extends AbstractSqlType {
return this;
}
- // TODO jvs 25-Jan-2005: same goes for getPrecedenceList()
+ @Override public RelDataTypePrecedenceList getPrecedenceList() {
+ return new RelDataTypePrecedenceList() {
+ public boolean containsType(RelDataType type) {
+ return type.getSqlTypeName() == getSqlTypeName()
+ && type.getComponentType() != null
+ && getComponentType().getPrecedenceList().containsType(
+ type.getComponentType());
+ }
+
+ public int compareTypePrecedence(RelDataType type1, RelDataType type2) {
+ if (!containsType(type1)) {
+ throw new IllegalArgumentException("must contain type: " + type1);
+ }
+ if (!containsType(type2)) {
+ throw new IllegalArgumentException("must contain type: " + type2);
+ }
+ return getComponentType().getPrecedenceList()
+ .compareTypePrecedence(type1.getComponentType(), type2.getComponentType());
+ }
+ };
+ }
}
// End MultisetSqlType.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/319e888f/core/src/test/java/org/apache/calcite/sql/type/SqlTypeFactoryTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/type/SqlTypeFactoryTest.java b/core/src/test/java/org/apache/calcite/sql/type/SqlTypeFactoryTest.java
index 2b1a164..8b1a985 100644
--- a/core/src/test/java/org/apache/calcite/sql/type/SqlTypeFactoryTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/type/SqlTypeFactoryTest.java
@@ -25,6 +25,8 @@ import org.junit.Test;
import static org.hamcrest.core.Is.is;
import static org.junit.Assert.assertThat;
+import static org.junit.Assert.fail;
+
/**
* Test for {@link SqlTypeFactoryImpl}.
@@ -73,6 +75,34 @@ public class SqlTypeFactoryTest {
checkPrecision(un, un, un, 0);
}
+ /** Unit test for {@link ArraySqlType#getPrecedenceList()}. */
+ @Test public void testArrayPrecedenceList() {
+ Fixture f = new Fixture();
+ assertThat(checkPrecendenceList(f.arrayBigInt, f.arrayBigInt, f.arrayFloat),
+ is(3));
+ assertThat(
+ checkPrecendenceList(f.arrayOfArrayBigInt, f.arrayOfArrayBigInt,
+ f.arrayOfArrayFloat), is(3));
+ assertThat(checkPrecendenceList(f.sqlBigInt, f.sqlBigInt, f.sqlFloat),
+ is(3));
+ assertThat(
+ checkPrecendenceList(f.multisetBigInt, f.multisetBigInt,
+ f.multisetFloat), is(3));
+ assertThat(
+ checkPrecendenceList(f.arrayBigInt, f.arrayBigInt,
+ f.arrayBigIntNullable), is(0));
+ try {
+ int i = checkPrecendenceList(f.arrayBigInt, f.sqlBigInt, f.sqlInt);
+ fail("Expected assert, got " + i);
+ } catch (IllegalArgumentException e) {
+ assertThat(e.getMessage(), is("must contain type: BIGINT"));
+ }
+ }
+
+ private int checkPrecendenceList(RelDataType t, RelDataType type1, RelDataType type2) {
+ return t.getPrecedenceList().compareTypePrecedence(type1, type2);
+ }
+
private void checkPrecision(int p0, int p1, int expectedMax,
int expectedComparison) {
assertThat(SqlTypeUtil.maxPrecision(p0, p1), is(expectedMax));
@@ -89,6 +119,8 @@ public class SqlTypeFactoryTest {
SqlTypeFactoryImpl typeFactory = new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
final RelDataType sqlBigInt = typeFactory.createTypeWithNullability(
typeFactory.createSqlType(SqlTypeName.BIGINT), false);
+ final RelDataType sqlBigIntNullable = typeFactory.createTypeWithNullability(
+ typeFactory.createSqlType(SqlTypeName.BIGINT), true);
final RelDataType sqlInt = typeFactory.createTypeWithNullability(
typeFactory.createSqlType(SqlTypeName.INTEGER), false);
final RelDataType sqlVarcharNullable = typeFactory.createTypeWithNullability(
@@ -97,6 +129,22 @@ public class SqlTypeFactoryTest {
typeFactory.createSqlType(SqlTypeName.NULL), false);
final RelDataType sqlAny = typeFactory.createTypeWithNullability(
typeFactory.createSqlType(SqlTypeName.ANY), false);
+ final RelDataType sqlFloat = typeFactory.createTypeWithNullability(
+ typeFactory.createSqlType(SqlTypeName.FLOAT), false);
+ final RelDataType arrayFloat = typeFactory.createTypeWithNullability(
+ typeFactory.createArrayType(sqlFloat, -1), false);
+ final RelDataType arrayBigInt = typeFactory.createTypeWithNullability(
+ typeFactory.createArrayType(sqlBigIntNullable, -1), false);
+ final RelDataType multisetFloat = typeFactory.createTypeWithNullability(
+ typeFactory.createMultisetType(sqlFloat, -1), false);
+ final RelDataType multisetBigInt = typeFactory.createTypeWithNullability(
+ typeFactory.createMultisetType(sqlBigIntNullable, -1), false);
+ final RelDataType arrayBigIntNullable = typeFactory.createTypeWithNullability(
+ typeFactory.createArrayType(sqlBigIntNullable, -1), true);
+ final RelDataType arrayOfArrayBigInt = typeFactory.createTypeWithNullability(
+ typeFactory.createArrayType(arrayBigInt, -1), false);
+ final RelDataType arrayOfArrayFloat = typeFactory.createTypeWithNullability(
+ typeFactory.createArrayType(arrayFloat, -1), false);
}
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/319e888f/core/src/test/java/org/apache/calcite/test/UdfTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/UdfTest.java b/core/src/test/java/org/apache/calcite/test/UdfTest.java
index 774edad..3297c27 100644
--- a/core/src/test/java/org/apache/calcite/test/UdfTest.java
+++ b/core/src/test/java/org/apache/calcite/test/UdfTest.java
@@ -16,12 +16,27 @@
*/
package org.apache.calcite.test;
+import org.apache.calcite.adapter.enumerable.CallImplementor;
+import org.apache.calcite.adapter.enumerable.RexImpTable.NullAs;
+import org.apache.calcite.adapter.enumerable.RexToLixTranslator;
import org.apache.calcite.adapter.java.ReflectiveSchema;
import org.apache.calcite.jdbc.CalciteConnection;
+import org.apache.calcite.linq4j.Ord;
+import org.apache.calcite.linq4j.tree.Expression;
+import org.apache.calcite.linq4j.tree.Expressions;
+import org.apache.calcite.linq4j.tree.Types;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeFactory;
+import org.apache.calcite.rel.type.RelProtoDataType;
+import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.schema.FunctionParameter;
+import org.apache.calcite.schema.ImplementableFunction;
+import org.apache.calcite.schema.ScalarFunction;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.schema.impl.AbstractSchema;
import org.apache.calcite.schema.impl.ScalarFunctionImpl;
import org.apache.calcite.schema.impl.ViewTable;
+import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.util.Smalls;
import com.google.common.collect.ImmutableList;
@@ -29,10 +44,13 @@ import com.google.common.collect.ImmutableList;
import org.junit.Ignore;
import org.junit.Test;
+import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import static org.hamcrest.CoreMatchers.is;
@@ -736,6 +754,138 @@ public class UdfTest {
.returnsValue("0");
}
+ /**
+ * Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-1834">[CALCITE-1834]
+ * User-defined function for Arrays</a>.
+ */
+ @Test public void testArrayUserDefinedFunction() throws Exception {
+ try (Connection connection = DriverManager.getConnection("jdbc:calcite:")) {
+ CalciteConnection calciteConnection =
+ connection.unwrap(CalciteConnection.class);
+ SchemaPlus rootSchema = calciteConnection.getRootSchema();
+ rootSchema.add("hr", new ReflectiveSchema(new JdbcTest.HrSchema()));
+
+ SchemaPlus post = rootSchema.add("POST", new AbstractSchema());
+ post.add("ARRAY_APPEND", new ArrayAppendDoubleFunction());
+ post.add("ARRAY_APPEND", new ArrayAppendIntegerFunction());
+ final String sql = "select \"empid\" as EMPLOYEE_ID,\n"
+ + " \"name\" || ' ' || \"name\" as EMPLOYEE_NAME,\n"
+ + " \"salary\" as EMPLOYEE_SALARY,\n"
+ + " POST.ARRAY_APPEND(ARRAY[1,2,3], \"deptno\") as DEPARTMENTS\n"
+ + "from \"hr\".\"emps\"";
+
+ final String result = ""
+ + "EMPLOYEE_ID=100; EMPLOYEE_NAME=Bill Bill;"
+ + " EMPLOYEE_SALARY=10000.0; DEPARTMENTS=[1, 2, 3, 10]\n"
+ + "EMPLOYEE_ID=200; EMPLOYEE_NAME=Eric Eric;"
+ + " EMPLOYEE_SALARY=8000.0; DEPARTMENTS=[1, 2, 3, 20]\n"
+ + "EMPLOYEE_ID=150; EMPLOYEE_NAME=Sebastian Sebastian;"
+ + " EMPLOYEE_SALARY=7000.0; DEPARTMENTS=[1, 2, 3, 10]\n"
+ + "EMPLOYEE_ID=110; EMPLOYEE_NAME=Theodore Theodore;"
+ + " EMPLOYEE_SALARY=11500.0; DEPARTMENTS=[1, 2, 3, 10]\n";
+
+ try (Statement statement = connection.createStatement();
+ ResultSet resultSet = statement.executeQuery(sql)) {
+ assertThat(CalciteAssert.toString(resultSet), is(result));
+ }
+ connection.close();
+ }
+ }
+
+ /**
+ * Base class for functions that append arrays.
+ */
+ private abstract static class ArrayAppendScalarFunction
+ implements ScalarFunction, ImplementableFunction {
+ public List<FunctionParameter> getParameters() {
+ final List<FunctionParameter> parameters = new ArrayList<>();
+ for (final Ord<RelProtoDataType> type : Ord.zip(getParams())) {
+ parameters.add(
+ new FunctionParameter() {
+ public int getOrdinal() {
+ return type.i;
+ }
+
+ public String getName() {
+ return "arg" + type.i;
+ }
+
+ public RelDataType getType(RelDataTypeFactory typeFactory) {
+ return type.e.apply(typeFactory);
+ }
+
+ public boolean isOptional() {
+ return false;
+ }
+ });
+ }
+ return parameters;
+ }
+
+ protected abstract List<RelProtoDataType> getParams();
+
+ @Override public CallImplementor getImplementor() {
+ return new CallImplementor() {
+ public Expression implement(RexToLixTranslator translator, RexCall call, NullAs nullAs) {
+ Method lookupMethod =
+ Types.lookupMethod(Smalls.AllTypesFunction.class,
+ "arrayAppendFun", List.class, Integer.class);
+ return Expressions.call(lookupMethod,
+ translator.translateList(call.getOperands(), nullAs));
+ }
+ };
+ }
+ }
+
+ /** Function with signature "f(ARRAY OF INTEGER, INTEGER) returns ARRAY OF
+ * INTEGER". */
+ private class ArrayAppendIntegerFunction extends ArrayAppendScalarFunction {
+ @Override public RelDataType getReturnType(RelDataTypeFactory typeFactory) {
+ return typeFactory.createArrayType(
+ typeFactory.createSqlType(SqlTypeName.INTEGER), -1);
+ }
+
+ @Override public List<RelProtoDataType> getParams() {
+ return ImmutableList.of(
+ new RelProtoDataType() {
+ public RelDataType apply(RelDataTypeFactory typeFactory) {
+ return typeFactory.createArrayType(
+ typeFactory.createSqlType(SqlTypeName.INTEGER), -1);
+ }
+ },
+ new RelProtoDataType() {
+ public RelDataType apply(RelDataTypeFactory typeFactory) {
+ return typeFactory.createSqlType(SqlTypeName.INTEGER);
+ }
+ });
+ }
+ }
+
+ /** Function with signature "f(ARRAY OF DOUBLE, INTEGER) returns ARRAY OF
+ * DOUBLE". */
+ private class ArrayAppendDoubleFunction extends ArrayAppendScalarFunction {
+ public RelDataType getReturnType(RelDataTypeFactory typeFactory) {
+ return typeFactory.createArrayType(
+ typeFactory.createSqlType(SqlTypeName.DOUBLE), -1);
+ }
+
+ public List<RelProtoDataType> getParams() {
+ return ImmutableList.of(
+ new RelProtoDataType() {
+ public RelDataType apply(RelDataTypeFactory typeFactory) {
+ return typeFactory.createArrayType(
+ typeFactory.createSqlType(SqlTypeName.DOUBLE), -1);
+ }
+ },
+ new RelProtoDataType() {
+ public RelDataType apply(RelDataTypeFactory typeFactory) {
+ return typeFactory.createSqlType(SqlTypeName.INTEGER);
+ }
+ });
+ }
+ }
+
}
// End UdfTest.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/319e888f/core/src/test/java/org/apache/calcite/util/Smalls.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/util/Smalls.java b/core/src/test/java/org/apache/calcite/util/Smalls.java
index a2ac470..c10d119 100644
--- a/core/src/test/java/org/apache/calcite/util/Smalls.java
+++ b/core/src/test/java/org/apache/calcite/util/Smalls.java
@@ -55,6 +55,7 @@ import java.util.concurrent.atomic.AtomicInteger;
import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertThat;
+
/**
* Holder for various classes and functions used in tests as user-defined
* functions and so forth.
@@ -473,6 +474,16 @@ public class Smalls {
public static java.sql.Time toTimeFun(Long v) {
return v == null ? null : SqlFunctions.internalToTime(v.intValue());
}
+
+ public static List arrayAppendFun(List v, Integer i) {
+ if (v == null || i == null) {
+ return null;
+ } else {
+ v.add(i);
+ return v;
+ }
+ }
+
}
/** Example of a user-defined aggregate function (UDAF). */