calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject [2/2] calcite git commit: [CALCITE-1834] Allow user-defined functions to have arguments that are ARRAY or MULTISET (Ankit Singhal)
Date Sun, 02 Jul 2017 08:47:28 GMT
[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 <ankitsinghal59@gmail.com>
Authored: Fri Jun 23 20:11:48 2017 +0530
Committer: Julian Hyde <jhyde@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). */


Mime
View raw message