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-1453] Support ANY type with binary comparison and arithmetic operators (Jungtaek Lim)
Date Thu, 27 Oct 2016 00:08:52 GMT
[CALCITE-1453] Support ANY type with binary comparison and arithmetic operators (Jungtaek Lim)

ANY type is represented as Object. There's not enough overloaded
backup methods for comparison and arithmetic operators so if one or
both parameters have ANY type, Calcite cannot find the matched method.

This patch adds overloaded methods for comparison and arithmetic
operators whose parameters are (Object, Object). And if either
parameter is ANY, it boxes primitive type parameter so that it can be
matched to Object, Object.

Newly added overloaded methods are smart that they can handle
different Number types for parameters. However, the implementation has
some downsides:
1. requires converting to BigDecimal
2. the result of arithmetic is also BigDecimal

Close apache/calcite#311


Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/80240720
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/80240720
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/80240720

Branch: refs/heads/branch-avatica-1.9
Commit: 80240720ec7de7b68dec8741d2322d9cd7f989ed
Parents: 08eb16b
Author: Jungtaek Lim <kabhwan@gmail.com>
Authored: Tue Oct 18 11:10:16 2016 +0900
Committer: Julian Hyde <jhyde@apache.org>
Committed: Wed Oct 26 16:56:22 2016 -0700

----------------------------------------------------------------------
 .../calcite/adapter/enumerable/RexImpTable.java |  48 ++-
 .../apache/calcite/runtime/SqlFunctions.java    | 213 +++++++++++--
 .../org/apache/calcite/sql/SqlDataTypeSpec.java |   3 +-
 .../apache/calcite/test/CollectionTypeTest.java |  82 +++--
 .../apache/calcite/test/SqlFunctionsTest.java   | 304 ++++++++++++++++++-
 5 files changed, 580 insertions(+), 70 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/calcite/blob/80240720/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
----------------------------------------------------------------------
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 811103e..5984fca 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
@@ -1636,6 +1636,7 @@ public class RexImpTable {
             SqlStdOperatorTable.LESS_THAN_OR_EQUAL,
             SqlStdOperatorTable.GREATER_THAN,
             SqlStdOperatorTable.GREATER_THAN_OR_EQUAL);
+    public static final String METHOD_POSTFIX_FOR_ANY_TYPE = "Any";
 
     private final ExpressionType expressionType;
     private final String backupMethodName;
@@ -1664,16 +1665,21 @@ public class RexImpTable {
       //   ignore_null
       //     return x == null ? y : y == null ? x : x OP y
       if (backupMethodName != null) {
-        final Primitive primitive =
-            Primitive.ofBoxOr(expressions.get(0).getType());
+        // If one or both operands have ANY type, use the late-binding backup
+        // method.
+        if (anyAnyOperands(call)) {
+          return callBackupMethodAnyType(translator, call, expressions);
+        }
+
+        final Type type0 = expressions.get(0).getType();
+        final Type type1 = expressions.get(1).getType();
         final SqlBinaryOperator op = (SqlBinaryOperator) call.getOperator();
+        final Primitive primitive = Primitive.ofBoxOr(type0);
         if (primitive == null
-            || expressions.get(1).getType() == BigDecimal.class
+            || type1 == BigDecimal.class
             || COMPARISON_OPERATORS.contains(op)
             && !COMP_OP_TYPES.contains(primitive)) {
-          return Expressions.call(
-              SqlFunctions.class,
-              backupMethodName,
+          return Expressions.call(SqlFunctions.class, backupMethodName,
               expressions);
         }
       }
@@ -1684,6 +1690,36 @@ public class RexImpTable {
           Expressions.makeBinary(expressionType, expressions.get(0),
               expressions.get(1)));
     }
+
+    /** Returns whether any of a call's operands have ANY type. */
+    private boolean anyAnyOperands(RexCall call) {
+      for (RexNode operand : call.operands) {
+        if (operand.getType().getSqlTypeName() == SqlTypeName.ANY) {
+          return true;
+        }
+      }
+      return false;
+    }
+
+    private Expression callBackupMethodAnyType(RexToLixTranslator translator,
+        RexCall call, List<Expression> expressions) {
+      final String backupMethodNameForAnyType =
+          backupMethodName + METHOD_POSTFIX_FOR_ANY_TYPE;
+
+      // one or both of parameter(s) is(are) ANY type
+      final Expression expression0 = maybeBox(expressions.get(0));
+      final Expression expression1 = maybeBox(expressions.get(1));
+      return Expressions.call(SqlFunctions.class, backupMethodNameForAnyType,
+          expression0, expression1);
+    }
+
+    private Expression maybeBox(Expression expression) {
+      final Primitive primitive = Primitive.of(expression.getType());
+      if (primitive != null) {
+        expression = Expressions.box(expression, primitive);
+      }
+      return expression;
+    }
   }
 
   /** Implementor for unary operators. */

http://git-wip-us.apache.org/repos/asf/calcite/blob/80240720/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
----------------------------------------------------------------------
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 268b911..87b5528 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -348,118 +348,206 @@ public class SqlFunctions {
 
   // =
 
-  /** SQL = operator applied to Object values (including String; neither
-   * side may be null). */
+  /** SQL <code>=</code> operator applied to BigDecimal values (neither may be
+   * null). */
+  public static boolean eq(BigDecimal b0, BigDecimal b1) {
+    return b0.stripTrailingZeros().equals(b1.stripTrailingZeros());
+  }
+
+  /** SQL <code>=</code> operator applied to Object values (including String;
+   * neither side may be null). */
   public static boolean eq(Object b0, Object b1) {
     return b0.equals(b1);
   }
 
-  /** SQL = operator applied to BigDecimal values (neither may be null). */
-  public static boolean eq(BigDecimal b0, BigDecimal b1) {
-    return b0.stripTrailingZeros().equals(b1.stripTrailingZeros());
+  /** SQL <code>=</code> operator applied to Object values (at least one operand
+   * has ANY type; neither may be null). */
+  public static boolean eqAny(Object b0, Object b1) {
+    if (b0.getClass().equals(b1.getClass())) {
+      // The result of SqlFunctions.eq(BigDecimal, BigDecimal) makes more sense
+      // than BigDecimal.equals(BigDecimal). So if both of types are BigDecimal,
+      // we just use SqlFunctions.eq(BigDecimal, BigDecimal).
+      if (BigDecimal.class.isInstance(b0)) {
+        return eq((BigDecimal) b0, (BigDecimal) b1);
+      } else {
+        return b0.equals(b1);
+      }
+    } else if (allAssignable(Number.class, b0, b1)) {
+      return eq(toBigDecimal((Number) b0), toBigDecimal((Number) b1));
+    }
+    // We shouldn't rely on implementation even though overridden equals can
+    // handle other types which may create worse result: for example,
+    // a.equals(b) != b.equals(a)
+    return false;
+  }
+
+  /** Returns whether two objects can both be assigned to a given class. */
+  private static boolean allAssignable(Class clazz, Object o0, Object o1) {
+    return clazz.isInstance(o0) && clazz.isInstance(o1);
   }
 
   // <>
 
-  /** SQL &lt;&gt; operator applied to Object values (including String;
-   * neither side may be null). */
+  /** SQL <code>&lt;gt;</code> operator applied to BigDecimal values. */
+  public static boolean ne(BigDecimal b0, BigDecimal b1) {
+    return b0.compareTo(b1) != 0;
+  }
+
+  /** SQL <code>&lt;gt;</code> operator applied to Object values (including
+   * String; neither side may be null). */
   public static boolean ne(Object b0, Object b1) {
-    return !b0.equals(b1);
+    return !eq(b0, b1);
   }
 
-  /** SQL &lt;&gt; operator applied to BigDecimal values. */
-  public static boolean ne(BigDecimal b0, BigDecimal b1) {
-    return b0.compareTo(b1) != 0;
+  /** SQL <code>&lt;gt;</code> operator applied to Object values (at least
one
+   *  operand has ANY type, including String; neither may be null). */
+  public static boolean neAny(Object b0, Object b1) {
+    return !eqAny(b0, b1);
   }
 
   // <
 
-  /** SQL &lt; operator applied to boolean values. */
+  /** SQL <code>&lt;</code> operator applied to boolean values. */
   public static boolean lt(boolean b0, boolean b1) {
     return compare(b0, b1) < 0;
   }
 
-  /** SQL &lt; operator applied to String values. */
+  /** SQL <code>&lt;</code> operator applied to String values. */
   public static boolean lt(String b0, String b1) {
     return b0.compareTo(b1) < 0;
   }
 
-  /** SQL &lt; operator applied to ByteString values. */
+  /** SQL <code>&lt;</code> operator applied to ByteString values. */
   public static boolean lt(ByteString b0, ByteString b1) {
     return b0.compareTo(b1) < 0;
   }
 
-  /** SQL &lt; operator applied to BigDecimal values. */
+  /** SQL <code>&lt;</code> operator applied to BigDecimal values. */
   public static boolean lt(BigDecimal b0, BigDecimal b1) {
     return b0.compareTo(b1) < 0;
   }
 
+  /** SQL <code>&lt;</code> operator applied to Object values. */
+  public static boolean ltAny(Object b0, Object b1) {
+    if (b0.getClass().equals(b1.getClass())
+        && b0 instanceof Comparable) {
+      //noinspection unchecked
+      return ((Comparable) b0).compareTo(b1) < 0;
+    } else if (allAssignable(Number.class, b0, b1)) {
+      return lt(toBigDecimal((Number) b0), toBigDecimal((Number) b1));
+    }
+
+    throw notComparable("<", b0, b1);
+  }
+
   // <=
 
-  /** SQL &le; operator applied to boolean values. */
+  /** SQL <code>&le;</code> operator applied to boolean values. */
   public static boolean le(boolean b0, boolean b1) {
     return compare(b0, b1) <= 0;
   }
 
-  /** SQL &le; operator applied to String values. */
+  /** SQL <code>&le;</code> operator applied to String values. */
   public static boolean le(String b0, String b1) {
     return b0.compareTo(b1) <= 0;
   }
 
-  /** SQL &le; operator applied to ByteString values. */
+  /** SQL <code>&le;</code> operator applied to ByteString values. */
   public static boolean le(ByteString b0, ByteString b1) {
     return b0.compareTo(b1) <= 0;
   }
 
-  /** SQL &le; operator applied to BigDecimal values. */
+  /** SQL <code>&le;</code> operator applied to BigDecimal values. */
   public static boolean le(BigDecimal b0, BigDecimal b1) {
     return b0.compareTo(b1) <= 0;
   }
 
+  /** SQL <code>&le;</code> operator applied to Object values (at least one
+   * operand has ANY type; neither may be null). */
+  public static boolean leAny(Object b0, Object b1) {
+    if (b0.getClass().equals(b1.getClass())
+        && b0 instanceof Comparable) {
+      //noinspection unchecked
+      return ((Comparable) b0).compareTo(b1) <= 0;
+    } else if (allAssignable(Number.class, b0, b1)) {
+      return le(toBigDecimal((Number) b0), toBigDecimal((Number) b1));
+    }
+
+    throw notComparable("<=", b0, b1);
+  }
+
   // >
 
-  /** SQL &gt; operator applied to boolean values. */
+  /** SQL <code>&gt;</code> operator applied to boolean values. */
   public static boolean gt(boolean b0, boolean b1) {
     return compare(b0, b1) > 0;
   }
 
-  /** SQL &gt; operator applied to String values. */
+  /** SQL <code>&gt;</code> operator applied to String values. */
   public static boolean gt(String b0, String b1) {
     return b0.compareTo(b1) > 0;
   }
 
-  /** SQL &gt; operator applied to ByteString values. */
+  /** SQL <code>&gt;</code> operator applied to ByteString values. */
   public static boolean gt(ByteString b0, ByteString b1) {
     return b0.compareTo(b1) > 0;
   }
 
-  /** SQL &gt; operator applied to BigDecimal values. */
+  /** SQL <code>&gt;</code> operator applied to BigDecimal values. */
   public static boolean gt(BigDecimal b0, BigDecimal b1) {
     return b0.compareTo(b1) > 0;
   }
 
+  /** SQL <code>&gt;</code> operator applied to Object values (at least one
+   * operand has ANY type; neither may be null). */
+  public static boolean gtAny(Object b0, Object b1) {
+    if (b0.getClass().equals(b1.getClass())
+        && b0 instanceof Comparable) {
+      //noinspection unchecked
+      return ((Comparable) b0).compareTo(b1) > 0;
+    } else if (allAssignable(Number.class, b0, b1)) {
+      return gt(toBigDecimal((Number) b0), toBigDecimal((Number) b1));
+    }
+
+    throw notComparable(">", b0, b1);
+  }
+
   // >=
 
-  /** SQL &ge; operator applied to boolean values. */
+  /** SQL <code>&ge;</code> operator applied to boolean values. */
   public static boolean ge(boolean b0, boolean b1) {
     return compare(b0, b1) >= 0;
   }
 
-  /** SQL &ge; operator applied to String values. */
+  /** SQL <code>&ge;</code> operator applied to String values. */
   public static boolean ge(String b0, String b1) {
     return b0.compareTo(b1) >= 0;
   }
 
-  /** SQL &ge; operator applied to ByteString values. */
+  /** SQL <code>&ge;</code> operator applied to ByteString values. */
   public static boolean ge(ByteString b0, ByteString b1) {
     return b0.compareTo(b1) >= 0;
   }
 
-  /** SQL &ge; operator applied to BigDecimal values. */
+  /** SQL <code>&ge;</code> operator applied to BigDecimal values. */
   public static boolean ge(BigDecimal b0, BigDecimal b1) {
     return b0.compareTo(b1) >= 0;
   }
 
+  /** SQL <code>&ge;</code> operator applied to Object values (at least one
+   * operand has ANY type; neither may be null). */
+  public static boolean geAny(Object b0, Object b1) {
+    if (b0.getClass().equals(b1.getClass())
+        && b0 instanceof Comparable) {
+      //noinspection unchecked
+      return ((Comparable) b0).compareTo(b1) >= 0;
+    } else if (allAssignable(Number.class, b0, b1)) {
+      return ge(toBigDecimal((Number) b0), toBigDecimal((Number) b1));
+    }
+
+    throw notComparable(">=", b0, b1);
+  }
+
   // +
 
   /** SQL <code>+</code> operator applied to int values. */
@@ -503,6 +591,20 @@ public class SqlFunctions {
     return (b0 == null || b1 == null) ? null : b0.add(b1);
   }
 
+  /** SQL <code>+</code> operator applied to Object values (at least one operand
+   * has ANY type; either may be null). */
+  public static Object plusAny(Object b0, Object b1) {
+    if (b0 == null || b1 == null) {
+      return null;
+    }
+
+    if (allAssignable(Number.class, b0, b1)) {
+      return plus(toBigDecimal((Number) b0), toBigDecimal((Number) b1));
+    }
+
+    throw notArithmetic("+", b0, b1);
+  }
+
   // -
 
   /** SQL <code>-</code> operator applied to int values. */
@@ -546,6 +648,20 @@ public class SqlFunctions {
     return (b0 == null || b1 == null) ? null : b0.subtract(b1);
   }
 
+  /** SQL <code>-</code> operator applied to Object values (at least one operand
+   * has ANY type; either may be null). */
+  public static Object minusAny(Object b0, Object b1) {
+    if (b0 == null || b1 == null) {
+      return null;
+    }
+
+    if (allAssignable(Number.class, b0, b1)) {
+      return minus(toBigDecimal((Number) b0), toBigDecimal((Number) b1));
+    }
+
+    throw notArithmetic("-", b0, b1);
+  }
+
   // /
 
   /** SQL <code>/</code> operator applied to int values. */
@@ -591,6 +707,20 @@ public class SqlFunctions {
         : b0.divide(b1, MathContext.DECIMAL64);
   }
 
+  /** SQL <code>/</code> operator applied to Object values (at least one operand
+   * has ANY type; either may be null). */
+  public static Object divideAny(Object b0, Object b1) {
+    if (b0 == null || b1 == null) {
+      return null;
+    }
+
+    if (allAssignable(Number.class, b0, b1)) {
+      return divide(toBigDecimal((Number) b0), toBigDecimal((Number) b1));
+    }
+
+    throw notArithmetic("/", b0, b1);
+  }
+
   public static int divide(int b0, BigDecimal b1) {
     return BigDecimal.valueOf(b0)
         .divide(b1, BigDecimal.ROUND_HALF_DOWN).intValue();
@@ -644,6 +774,32 @@ public class SqlFunctions {
     return (b0 == null || b1 == null) ? null : b0.multiply(b1);
   }
 
+  /** SQL <code>*</code> operator applied to Object values (at least one operand
+   * has ANY type; either may be null). */
+  public static Object multiplyAny(Object b0, Object b1) {
+    if (b0 == null || b1 == null) {
+      return null;
+    }
+
+    if (allAssignable(Number.class, b0, b1)) {
+      return multiply(toBigDecimal((Number) b0), toBigDecimal((Number) b1));
+    }
+
+    throw notArithmetic("*", b0, b1);
+  }
+
+  private static IllegalArgumentException notArithmetic(String op, Object b0,
+      Object b1) {
+    return new IllegalArgumentException("Invalid types for arithmetic: "
+        + b0.getClass() + " " + op + " " + b1.getClass());
+  }
+
+  private static IllegalArgumentException notComparable(String op, Object b0,
+      Object b1) {
+    return new IllegalArgumentException("Invalid types for comparison: "
+        + b0.getClass() + " " + op + " " + b1.getClass());
+  }
+
   // EXP
 
   /** SQL <code>EXP</code> operator applied to double values. */
@@ -1431,7 +1587,8 @@ public class SqlFunctions {
     return (int) (localTimestamp(root) % DateTimeUtils.MILLIS_PER_DAY);
   }
 
-  /** SQL TRANSLATE(string, search_chars, replacement_chars) function. */
+  /** SQL {@code TRANSLATE(string, search_chars, replacement_chars)}
+   * function. */
   public static String translate3(String s, String search, String replacement) {
     return org.apache.commons.lang3.StringUtils.replaceChars(s, search, replacement);
   }

http://git-wip-us.apache.org/repos/asf/calcite/blob/80240720/core/src/main/java/org/apache/calcite/sql/SqlDataTypeSpec.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDataTypeSpec.java b/core/src/main/java/org/apache/calcite/sql/SqlDataTypeSpec.java
index d4bfc6c..c53b832 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDataTypeSpec.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDataTypeSpec.java
@@ -18,7 +18,6 @@ package org.apache.calcite.sql;
 
 import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.rel.type.RelDataTypeFactory;
-import org.apache.calcite.runtime.SqlFunctions;
 import org.apache.calcite.sql.parser.SqlParserPos;
 import org.apache.calcite.sql.type.SqlTypeName;
 import org.apache.calcite.sql.type.SqlTypeUtil;
@@ -162,7 +161,7 @@ public class SqlDataTypeSpec extends SqlNode {
   /** Returns a copy of this data type specification with a given
    * nullability. */
   public SqlDataTypeSpec withNullable(Boolean nullable) {
-    if (SqlFunctions.eq(nullable, this.nullable)) {
+    if (Objects.equals(nullable, this.nullable)) {
       return this;
     }
     return new SqlDataTypeSpec(collectionsTypeName, typeName, precision, scale,

http://git-wip-us.apache.org/repos/asf/calcite/blob/80240720/core/src/test/java/org/apache/calcite/test/CollectionTypeTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/CollectionTypeTest.java b/core/src/test/java/org/apache/calcite/test/CollectionTypeTest.java
index 7219aeb..2a2c52b 100644
--- a/core/src/test/java/org/apache/calcite/test/CollectionTypeTest.java
+++ b/core/src/test/java/org/apache/calcite/test/CollectionTypeTest.java
@@ -168,24 +168,52 @@ public class CollectionTypeTest {
 
     final Statement statement = connection.createStatement();
 
-    // Since the value type is ANY, we need to wrap the value to CAST in order to
-    // compare with literal. if it doesn't, Exception is thrown at Runtime.
-    // This is only occurred with primitive type because of providing overloaded methods
-    try {
-      final String sql = "select \"ID\", \"MAPFIELD\"['c'] AS \"MAPFIELD_C\","
-          + " \"NESTEDMAPFIELD\", \"ARRAYFIELD\" "
-          + "from \"s\".\"nested\" "
-          + "where \"NESTEDMAPFIELD\"['a']['b'] = 2 AND \"ARRAYFIELD\"[2] = 200";
-      statement.executeQuery(sql);
+    // placing literal earlier than ANY type is intended: do not modify
+    final String sql = "select \"ID\", \"MAPFIELD\"['c'] AS \"MAPFIELD_C\","
+        + " \"NESTEDMAPFIELD\", \"ARRAYFIELD\" "
+        + "from \"s\".\"nested\" "
+        + "where \"NESTEDMAPFIELD\"['a']['b'] = 2 AND 200.0 = \"ARRAYFIELD\"[2]";
 
-      fail("Without CAST, comparing result of ITEM() and primitive type should throw Exception
"
-          + "in Runtime");
-    } catch (SQLException e) {
-      Throwable e2 = e.getCause();
-      assertThat(e2, is(instanceOf(RuntimeException.class)));
-      Throwable e3 = e2.getCause();
-      assertThat(e3, is(instanceOf(NoSuchMethodException.class)));
-    }
+    final ResultSet resultSet = statement.executeQuery(sql);
+    final List<String> resultStrings = CalciteAssert.toList(resultSet);
+    assertThat(resultStrings.size(), is(1));
+
+    // JDBC doesn't support Map / Nested Map so just relying on string representation
+    String expectedRow = "ID=2; MAPFIELD_C=4; NESTEDMAPFIELD={a={b=2, c=4}}; "
+        + "ARRAYFIELD=[100, 200, 300]";
+    assertThat(resultStrings.get(0), is(expectedRow));
+  }
+
+
+  @Test public void testArithmeticToAnyTypeWithoutCast() throws Exception {
+    Connection connection = setupConnectionWithNestedAnyTypeTable();
+
+    final Statement statement = connection.createStatement();
+
+    // placing literal earlier than ANY type is intended: do not modify
+    final String sql = "select \"ID\", \"MAPFIELD\"['c'] AS \"MAPFIELD_C\","
+        + " \"NESTEDMAPFIELD\", \"ARRAYFIELD\" "
+        + "from \"s\".\"nested\" "
+        + "where \"NESTEDMAPFIELD\"['a']['b'] + 1.0 = 3 "
+        + "AND \"NESTEDMAPFIELD\"['a']['b'] * 2.0 = 4 "
+        + "AND \"NESTEDMAPFIELD\"['a']['b'] > 1"
+        + "AND \"NESTEDMAPFIELD\"['a']['b'] >= 2"
+        + "AND 100.1 <> \"ARRAYFIELD\"[2] - 100.0"
+        + "AND 100.0 = \"ARRAYFIELD\"[2] / 2"
+        + "AND 99.9 < \"ARRAYFIELD\"[2] / 2"
+        + "AND 100.0 <= \"ARRAYFIELD\"[2] / 2"
+        + "AND '200' <> \"STRINGARRAYFIELD\"[1]"
+        + "AND '200' = \"STRINGARRAYFIELD\"[2]"
+        + "AND '100' < \"STRINGARRAYFIELD\"[2]";
+
+    final ResultSet resultSet = statement.executeQuery(sql);
+    final List<String> resultStrings = CalciteAssert.toList(resultSet);
+    assertThat(resultStrings.size(), is(1));
+
+    // JDBC doesn't support Map / Nested Map so just relying on string representation
+    String expectedRow = "ID=2; MAPFIELD_C=4; NESTEDMAPFIELD={a={b=2, c=4}}; "
+        + "ARRAYFIELD=[100, 200, 300]";
+    assertThat(resultStrings.get(0), is(expectedRow));
   }
 
   @Test public void testAccessNonExistKeyFromMapWithAnyType() throws Exception {
@@ -313,6 +341,7 @@ public class CollectionTypeTest {
 
   private static Object[][] setupNestedRecords() {
     List<Integer> ints = Arrays.asList(100, 200, 300);
+    List<String> strings = Arrays.asList("100", "200", "300");
 
     Object[][] records = new Object[5][];
 
@@ -322,7 +351,7 @@ public class CollectionTypeTest {
       map.put("c", i * i);
       Map<String, Map<String, Integer>> mm = new HashMap<>();
       mm.put("a", map);
-      records[i] = new Object[] {i, map, mm, ints};
+      records[i] = new Object[] {i, map, mm, ints, strings};
     }
 
     return records;
@@ -333,24 +362,28 @@ public class CollectionTypeTest {
   public static class NestedCollectionTable implements ScannableTable {
     public RelDataType getRowType(RelDataTypeFactory typeFactory) {
 
-      RelDataType nullableKeyType = typeFactory
+      RelDataType nullableVarcharType = typeFactory
           .createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.VARCHAR), true);
-      RelDataType nullableValueType = typeFactory
+      RelDataType nullableIntegerType = typeFactory
           .createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.INTEGER), true);
       RelDataType nullableMapType = typeFactory
-          .createTypeWithNullability(typeFactory.createMapType(nullableKeyType, nullableValueType),
+          .createTypeWithNullability(
+              typeFactory.createMapType(nullableVarcharType, nullableIntegerType),
               true);
       return typeFactory.builder()
           .add("ID", SqlTypeName.INTEGER)
           .add("MAPFIELD",
               typeFactory.createTypeWithNullability(
-                typeFactory.createMapType(nullableKeyType, nullableValueType), true))
+                typeFactory.createMapType(nullableVarcharType, nullableIntegerType), true))
           .add("NESTEDMAPFIELD", typeFactory
               .createTypeWithNullability(
-                  typeFactory.createMapType(nullableKeyType, nullableMapType), true))
+                  typeFactory.createMapType(nullableVarcharType, nullableMapType), true))
           .add("ARRAYFIELD", typeFactory
               .createTypeWithNullability(
-                  typeFactory.createArrayType(nullableValueType, -1L), true))
+                  typeFactory.createArrayType(nullableIntegerType, -1L), true))
+          .add("STRINGARRAYFIELD", typeFactory
+              .createTypeWithNullability(
+                  typeFactory.createArrayType(nullableVarcharType, -1L), true))
           .build();
     }
 
@@ -380,6 +413,7 @@ public class CollectionTypeTest {
           .add("MAPFIELD", SqlTypeName.ANY)
           .add("NESTEDMAPFIELD", SqlTypeName.ANY)
           .add("ARRAYFIELD", SqlTypeName.ANY)
+          .add("STRINGARRAYFIELD", SqlTypeName.ANY)
           .build();
     }
 

http://git-wip-us.apache.org/repos/asf/calcite/blob/80240720/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
----------------------------------------------------------------------
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 e6b6d44..fa12f0c 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -599,20 +599,304 @@ public class SqlFunctionsTest {
     assertThat(floorDiv(-11, 3), equalTo(-4L));
     assertThat(floorDiv(0, 3), equalTo(0L));
     assertThat(floorDiv(1, 3), equalTo(0L));
-    assertThat(floorDiv(-1, 3), equalTo(-1L));
+    assertThat(floorDiv(-1, 3), is(-1L));
   }
 
   @Test public void testFloorMod() {
-    assertThat(floorMod(13, 3), equalTo(1L));
-    assertThat(floorMod(12, 3), equalTo(0L));
-    assertThat(floorMod(11, 3), equalTo(2L));
-    assertThat(floorMod(-13, 3), equalTo(2L));
-    assertThat(floorMod(-12, 3), equalTo(0L));
-    assertThat(floorMod(-11, 3), equalTo(1L));
-    assertThat(floorMod(0, 3), equalTo(0L));
-    assertThat(floorMod(1, 3), equalTo(1L));
-    assertThat(floorMod(-1, 3), equalTo(2L));
+    assertThat(floorMod(13, 3), is(1L));
+    assertThat(floorMod(12, 3), is(0L));
+    assertThat(floorMod(11, 3), is(2L));
+    assertThat(floorMod(-13, 3), is(2L));
+    assertThat(floorMod(-12, 3), is(0L));
+    assertThat(floorMod(-11, 3), is(1L));
+    assertThat(floorMod(0, 3), is(0L));
+    assertThat(floorMod(1, 3), is(1L));
+    assertThat(floorMod(-1, 3), is(2L));
+  }
+
+  @Test public void testEqWithAny() {
+    // Non-numeric same type equality check
+    assertThat(SqlFunctions.eqAny("hello", "hello"), is(true));
+
+    // Numeric types equality check
+    assertThat(SqlFunctions.eqAny(1, 1L), is(true));
+    assertThat(SqlFunctions.eqAny(1, 1.0D), is(true));
+    assertThat(SqlFunctions.eqAny(1L, 1.0D), is(true));
+    assertThat(SqlFunctions.eqAny(new BigDecimal(1L), 1), is(true));
+    assertThat(SqlFunctions.eqAny(new BigDecimal(1L), 1L), is(true));
+    assertThat(SqlFunctions.eqAny(new BigDecimal(1L), 1.0D), is(true));
+    assertThat(SqlFunctions.eqAny(new BigDecimal(1L), new BigDecimal(1.0D)),
+        is(true));
+
+    // Non-numeric different type equality check
+    assertThat(SqlFunctions.eqAny("2", 2), is(false));
+  }
+
+  @Test public void testNeWithAny() {
+    // Non-numeric same type inequality check
+    assertThat(SqlFunctions.neAny("hello", "world"), is(true));
+
+    // Numeric types inequality check
+    assertThat(SqlFunctions.neAny(1, 2L), is(true));
+    assertThat(SqlFunctions.neAny(1, 2.0D), is(true));
+    assertThat(SqlFunctions.neAny(1L, 2.0D), is(true));
+    assertThat(SqlFunctions.neAny(new BigDecimal(2L), 1), is(true));
+    assertThat(SqlFunctions.neAny(new BigDecimal(2L), 1L), is(true));
+    assertThat(SqlFunctions.neAny(new BigDecimal(2L), 1.0D), is(true));
+    assertThat(SqlFunctions.neAny(new BigDecimal(2L), new BigDecimal(1.0D)),
+        is(true));
+
+    // Non-numeric different type inequality check
+    assertThat(SqlFunctions.neAny("2", 2), is(true));
+  }
+
+  @Test public void testLtWithAny() {
+    // Non-numeric same type "less then" check
+    assertThat(SqlFunctions.ltAny("apple", "banana"), is(true));
+
+    // Numeric types "less than" check
+    assertThat(SqlFunctions.ltAny(1, 2L), is(true));
+    assertThat(SqlFunctions.ltAny(1, 2.0D), is(true));
+    assertThat(SqlFunctions.ltAny(1L, 2.0D), is(true));
+    assertThat(SqlFunctions.ltAny(new BigDecimal(1L), 2), is(true));
+    assertThat(SqlFunctions.ltAny(new BigDecimal(1L), 2L), is(true));
+    assertThat(SqlFunctions.ltAny(new BigDecimal(1L), 2.0D), is(true));
+    assertThat(SqlFunctions.ltAny(new BigDecimal(1L), new BigDecimal(2.0D)),
+        is(true));
+
+    // Non-numeric different type but both implements Comparable
+    // "less than" check
+    try {
+      assertThat(SqlFunctions.ltAny("1", 2L), is(false));
+      fail("'lt' on non-numeric different type is not possible");
+    } catch (IllegalArgumentException e) {
+      assertThat(e.getMessage(),
+          is("Invalid types for comparison: class java.lang.String < "
+              + "class java.lang.Long"));
+    }
   }
+
+  @Test public void testLeWithAny() {
+    // Non-numeric same type "less or equal" check
+    assertThat(SqlFunctions.leAny("apple", "banana"), is(true));
+    assertThat(SqlFunctions.leAny("apple", "apple"), is(true));
+
+    // Numeric types "less or equal" check
+    assertThat(SqlFunctions.leAny(1, 2L), is(true));
+    assertThat(SqlFunctions.leAny(1, 1L), is(true));
+    assertThat(SqlFunctions.leAny(1, 2.0D), is(true));
+    assertThat(SqlFunctions.leAny(1, 1.0D), is(true));
+    assertThat(SqlFunctions.leAny(1L, 2.0D), is(true));
+    assertThat(SqlFunctions.leAny(1L, 1.0D), is(true));
+    assertThat(SqlFunctions.leAny(new BigDecimal(1L), 2), is(true));
+    assertThat(SqlFunctions.leAny(new BigDecimal(1L), 1), is(true));
+    assertThat(SqlFunctions.leAny(new BigDecimal(1L), 2L), is(true));
+    assertThat(SqlFunctions.leAny(new BigDecimal(1L), 1L), is(true));
+    assertThat(SqlFunctions.leAny(new BigDecimal(1L), 2.0D), is(true));
+    assertThat(SqlFunctions.leAny(new BigDecimal(1L), 1.0D), is(true));
+    assertThat(SqlFunctions.leAny(new BigDecimal(1L), new BigDecimal(2.0D)),
+        is(true));
+    assertThat(SqlFunctions.leAny(new BigDecimal(1L), new BigDecimal(1.0D)),
+        is(true));
+
+    // Non-numeric different type but both implements Comparable
+    // "less or equal" check
+    try {
+      assertThat(SqlFunctions.leAny("2", 2L), is(false));
+      fail("'le' on non-numeric different type is not possible");
+    } catch (IllegalArgumentException e) {
+      assertThat(e.getMessage(),
+          is("Invalid types for comparison: class java.lang.String <= "
+              + "class java.lang.Long"));
+    }
+  }
+
+  @Test public void testGtWithAny() {
+    // Non-numeric same type "greater then" check
+    assertThat(SqlFunctions.gtAny("banana", "apple"), is(true));
+
+    // Numeric types "greater than" check
+    assertThat(SqlFunctions.gtAny(2, 1L), is(true));
+    assertThat(SqlFunctions.gtAny(2, 1.0D), is(true));
+    assertThat(SqlFunctions.gtAny(2L, 1.0D), is(true));
+    assertThat(SqlFunctions.gtAny(new BigDecimal(2L), 1), is(true));
+    assertThat(SqlFunctions.gtAny(new BigDecimal(2L), 1L), is(true));
+    assertThat(SqlFunctions.gtAny(new BigDecimal(2L), 1.0D), is(true));
+    assertThat(SqlFunctions.gtAny(new BigDecimal(2L), new BigDecimal(1.0D)),
+        is(true));
+
+    // Non-numeric different type but both implements Comparable
+    // "greater than" check
+    try {
+      assertThat(SqlFunctions.gtAny("2", 1L), is(false));
+      fail("'gt' on non-numeric different type is not possible");
+    } catch (IllegalArgumentException e) {
+      assertThat(e.getMessage(),
+          is("Invalid types for comparison: class java.lang.String > "
+              + "class java.lang.Long"));
+    }
+  }
+
+  @Test public void testGeWithAny() {
+    // Non-numeric same type "greater or equal" check
+    assertThat(SqlFunctions.geAny("banana", "apple"), is(true));
+    assertThat(SqlFunctions.geAny("apple", "apple"), is(true));
+
+    // Numeric types "greater or equal" check
+    assertThat(SqlFunctions.geAny(2, 1L), is(true));
+    assertThat(SqlFunctions.geAny(1, 1L), is(true));
+    assertThat(SqlFunctions.geAny(2, 1.0D), is(true));
+    assertThat(SqlFunctions.geAny(1, 1.0D), is(true));
+    assertThat(SqlFunctions.geAny(2L, 1.0D), is(true));
+    assertThat(SqlFunctions.geAny(1L, 1.0D), is(true));
+    assertThat(SqlFunctions.geAny(new BigDecimal(2L), 1), is(true));
+    assertThat(SqlFunctions.geAny(new BigDecimal(1L), 1), is(true));
+    assertThat(SqlFunctions.geAny(new BigDecimal(2L), 1L), is(true));
+    assertThat(SqlFunctions.geAny(new BigDecimal(1L), 1L), is(true));
+    assertThat(SqlFunctions.geAny(new BigDecimal(2L), 1.0D), is(true));
+    assertThat(SqlFunctions.geAny(new BigDecimal(1L), 1.0D), is(true));
+    assertThat(SqlFunctions.geAny(new BigDecimal(2L), new BigDecimal(1.0D)),
+        is(true));
+    assertThat(SqlFunctions.geAny(new BigDecimal(1L), new BigDecimal(1.0D)),
+        is(true));
+
+    // Non-numeric different type but both implements Comparable
+    // "greater or equal" check
+    try {
+      assertThat(SqlFunctions.geAny("2", 2L), is(false));
+      fail("'ge' on non-numeric different type is not possible");
+    } catch (IllegalArgumentException e) {
+      assertThat(e.getMessage(),
+          is("Invalid types for arithmetic: class java.lang.String >= "
+              + "class java.lang.Long"));
+    }
+  }
+
+  @Test public void testPlusAny() {
+    // null parameters
+    assertNull(SqlFunctions.plusAny(null, null));
+    assertNull(SqlFunctions.plusAny(null, 1));
+    assertNull(SqlFunctions.plusAny(1, null));
+
+    // Numeric types
+    assertThat(SqlFunctions.plusAny(2, 1L), is((Object) new BigDecimal(3)));
+    assertThat(SqlFunctions.plusAny(2, 1.0D), is((Object) new BigDecimal(3)));
+    assertThat(SqlFunctions.plusAny(2L, 1.0D), is((Object) new BigDecimal(3)));
+    assertThat(SqlFunctions.plusAny(new BigDecimal(2L), 1),
+        is((Object) new BigDecimal(3)));
+    assertThat(SqlFunctions.plusAny(new BigDecimal(2L), 1L),
+        is((Object) new BigDecimal(3)));
+    assertThat(SqlFunctions.plusAny(new BigDecimal(2L), 1.0D),
+        is((Object) new BigDecimal(3)));
+    assertThat(SqlFunctions.plusAny(new BigDecimal(2L), new BigDecimal(1.0D)),
+        is((Object) new BigDecimal(3)));
+
+    // Non-numeric type
+    try {
+      SqlFunctions.plusAny("2", 2L);
+      fail("'plus' on non-numeric type is not possible");
+    } catch (IllegalArgumentException e) {
+      assertThat(e.getMessage(),
+          is("Invalid types for arithmetic: class java.lang.String + "
+              + "class java.lang.Long"));
+    }
+  }
+
+  @Test public void testMinusAny() {
+    // null parameters
+    assertNull(SqlFunctions.minusAny(null, null));
+    assertNull(SqlFunctions.minusAny(null, 1));
+    assertNull(SqlFunctions.minusAny(1, null));
+
+    // Numeric types
+    assertThat(SqlFunctions.minusAny(2, 1L), is((Object) new BigDecimal(1)));
+    assertThat(SqlFunctions.minusAny(2, 1.0D), is((Object) new BigDecimal(1)));
+    assertThat(SqlFunctions.minusAny(2L, 1.0D), is((Object) new BigDecimal(1)));
+    assertThat(SqlFunctions.minusAny(new BigDecimal(2L), 1),
+        is((Object) new BigDecimal(1)));
+    assertThat(SqlFunctions.minusAny(new BigDecimal(2L), 1L),
+        is((Object) new BigDecimal(1)));
+    assertThat(SqlFunctions.minusAny(new BigDecimal(2L), 1.0D),
+        is((Object) new BigDecimal(1)));
+    assertThat(SqlFunctions.minusAny(new BigDecimal(2L), new BigDecimal(1.0D)),
+        is((Object) new BigDecimal(1)));
+
+    // Non-numeric type
+    try {
+      SqlFunctions.minusAny("2", 2L);
+      fail("'minus' on non-numeric type is not possible");
+    } catch (IllegalArgumentException e) {
+      assertThat(e.getMessage(),
+          is("Invalid types for arithmetic: class java.lang.String - "
+              + "class java.lang.Long"));
+    }
+  }
+
+  @Test public void testMultiplyAny() {
+    // null parameters
+    assertNull(SqlFunctions.multiplyAny(null, null));
+    assertNull(SqlFunctions.multiplyAny(null, 1));
+    assertNull(SqlFunctions.multiplyAny(1, null));
+
+    // Numeric types
+    assertThat(SqlFunctions.multiplyAny(2, 1L), is((Object) new BigDecimal(2)));
+    assertThat(SqlFunctions.multiplyAny(2, 1.0D),
+        is((Object) new BigDecimal(2)));
+    assertThat(SqlFunctions.multiplyAny(2L, 1.0D),
+        is((Object) new BigDecimal(2)));
+    assertThat(SqlFunctions.multiplyAny(new BigDecimal(2L), 1),
+        is((Object) new BigDecimal(2)));
+    assertThat(SqlFunctions.multiplyAny(new BigDecimal(2L), 1L),
+        is((Object) new BigDecimal(2)));
+    assertThat(SqlFunctions.multiplyAny(new BigDecimal(2L), 1.0D),
+        is((Object) new BigDecimal(2)));
+    assertThat(SqlFunctions.multiplyAny(new BigDecimal(2L), new BigDecimal(1.0D)),
+        is((Object) new BigDecimal(2)));
+
+    // Non-numeric type
+    try {
+      SqlFunctions.multiplyAny("2", 2L);
+      fail("'multiply' on non-numeric type is not possible");
+    } catch (IllegalArgumentException e) {
+      assertThat(e.getMessage(),
+          is("Invalid types for arithmetic: class java.lang.String * "
+              + "class java.lang.Long"));
+    }
+  }
+
+  @Test public void testDivideAny() {
+    // null parameters
+    assertNull(SqlFunctions.divideAny(null, null));
+    assertNull(SqlFunctions.divideAny(null, 1));
+    assertNull(SqlFunctions.divideAny(1, null));
+
+    // Numeric types
+    assertThat(SqlFunctions.divideAny(5, 2L),
+        is((Object) new BigDecimal("2.5")));
+    assertThat(SqlFunctions.divideAny(5, 2.0D),
+        is((Object) new BigDecimal("2.5")));
+    assertThat(SqlFunctions.divideAny(5L, 2.0D),
+        is((Object) new BigDecimal("2.5")));
+    assertThat(SqlFunctions.divideAny(new BigDecimal(5L), 2),
+        is((Object) new BigDecimal(2.5)));
+    assertThat(SqlFunctions.divideAny(new BigDecimal(5L), 2L),
+        is((Object) new BigDecimal(2.5)));
+    assertThat(SqlFunctions.divideAny(new BigDecimal(5L), 2.0D),
+        is((Object) new BigDecimal(2.5)));
+    assertThat(SqlFunctions.divideAny(new BigDecimal(5L), new BigDecimal(2.0D)),
+        is((Object) new BigDecimal(2.5)));
+
+    // Non-numeric type
+    try {
+      SqlFunctions.divideAny("5", 2L);
+      fail("'divide' on non-numeric type is not possible");
+    } catch (IllegalArgumentException e) {
+      assertThat(e.getMessage(),
+          is("Invalid types for arithmetic: class java.lang.String / "
+              + "class java.lang.Long"));
+    }
+  }
+
 }
 
 // End SqlFunctionsTest.java


Mime
View raw message