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 2015/04/03 15:02:41 UTC
[08/10] incubator-calcite git commit: [CALCITE-613] Implicitly
convert character values in comparisons
[CALCITE-613] Implicitly convert character values in comparisons
Add SqlOperandTypeChecker.getConsistency()
Make "scott" data set available in Quidem (.oq) tests
For "precision" of TIME and TIMESTAMP columns read from JDBC, use "scale" not "size"
Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/272e6040
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/272e6040
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/272e6040
Branch: refs/heads/master
Commit: 272e604082bbaf27a93e5c4f04a2eb1c951a99f4
Parents: 5648083
Author: Julian Hyde <jh...@apache.org>
Authored: Wed Mar 25 11:29:27 2015 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Fri Apr 3 01:10:06 2015 -0700
----------------------------------------------------------------------
.../apache/calcite/adapter/jdbc/JdbcSchema.java | 22 +++-
.../java/org/apache/calcite/rex/RexUtil.java | 24 ++++
.../org/apache/calcite/sql/SqlTimeLiteral.java | 18 +--
.../apache/calcite/sql/SqlTimestampLiteral.java | 33 +----
.../calcite/sql/fun/SqlBetweenOperator.java | 5 +-
.../sql/type/AssignableOperandTypeChecker.java | 7 +-
.../sql/type/ComparableOperandTypeChecker.java | 18 ++-
.../sql/type/CompositeOperandTypeChecker.java | 4 +
.../sql/type/FamilyOperandTypeChecker.java | 8 +-
.../sql/type/LiteralOperandTypeChecker.java | 4 +
.../sql/type/MultisetOperandTypeChecker.java | 4 +
.../apache/calcite/sql/type/OperandTypes.java | 21 ++-
.../sql/type/SameOperandTypeChecker.java | 5 +-
.../sql/type/SetopOperandTypeChecker.java | 6 +-
.../calcite/sql/type/SqlOperandTypeChecker.java | 15 +++
.../apache/calcite/sql/type/SqlTypeUtil.java | 30 +++++
.../calcite/sql2rel/SqlToRelConverter.java | 7 +-
.../sql2rel/StandardConvertletTable.java | 128 ++++++++++++-------
.../org/apache/calcite/test/CalciteAssert.java | 27 +++-
.../java/org/apache/calcite/test/JdbcTest.java | 5 +
.../apache/calcite/test/SqlValidatorTest.java | 97 +++++++++-----
core/src/test/resources/sql/misc.oq | 114 +++++++++++++++++
22 files changed, 461 insertions(+), 141 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcSchema.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcSchema.java b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcSchema.java
index 7db25ae..de97237 100644
--- a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcSchema.java
+++ b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcSchema.java
@@ -16,6 +16,7 @@
*/
package org.apache.calcite.adapter.jdbc;
+import org.apache.calcite.avatica.SqlType;
import org.apache.calcite.linq4j.tree.Expression;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
@@ -128,8 +129,8 @@ public class JdbcSchema implements Schema {
}
String jdbcCatalog = (String) operand.get("jdbcCatalog");
String jdbcSchema = (String) operand.get("jdbcSchema");
- return JdbcSchema.create(parentSchema, name, dataSource, jdbcCatalog,
- jdbcSchema);
+ return JdbcSchema.create(
+ parentSchema, name, dataSource, jdbcCatalog, jdbcSchema);
}
/** Returns a suitable SQL dialect for the given data source. */
@@ -256,10 +257,21 @@ public class JdbcSchema implements Schema {
final String columnName = resultSet.getString(4);
final int dataType = resultSet.getInt(5);
final String typeString = resultSet.getString(6);
- final int size = resultSet.getInt(7);
- final int scale = resultSet.getInt(9);
+ final int precision;
+ final int scale;
+ switch (SqlType.valueOf(dataType)) {
+ case TIMESTAMP:
+ case TIME:
+ precision = resultSet.getInt(9); // SCALE
+ scale = 0;
+ break;
+ default:
+ precision = resultSet.getInt(7); // SIZE
+ scale = resultSet.getInt(9); // SCALE
+ break;
+ }
RelDataType sqlType =
- sqlType(typeFactory, dataType, size, scale, typeString);
+ sqlType(typeFactory, dataType, precision, scale, typeString);
boolean nullable = resultSet.getBoolean(11);
fieldInfo.add(columnName, sqlType).nullable(nullable);
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/rex/RexUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rex/RexUtil.java b/core/src/main/java/org/apache/calcite/rex/RexUtil.java
index 240f895..d470a4f 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexUtil.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexUtil.java
@@ -23,6 +23,7 @@ import org.apache.calcite.rel.RelCollations;
import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
+import org.apache.calcite.rel.type.RelDataTypeFamily;
import org.apache.calcite.rel.type.RelDataTypeField;
import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlKind;
@@ -75,6 +76,20 @@ public class RexUtil {
}
};
+ private static final Function<RexNode, RelDataType> TYPE_FN =
+ new Function<RexNode, RelDataType>() {
+ public RelDataType apply(RexNode input) {
+ return input.getType();
+ }
+ };
+
+ private static final Function<RelDataType, RelDataTypeFamily> FAMILY_FN =
+ new Function<RelDataType, RelDataTypeFamily>() {
+ public RelDataTypeFamily apply(RelDataType input) {
+ return input.getFamily();
+ }
+ };
+
private RexUtil() {
}
@@ -1132,6 +1147,15 @@ public class RexUtil {
}.apply(nodes);
}
+ /** Transforms a list of expressions into a list of their types. */
+ public static List<RelDataType> types(List<? extends RexNode> nodes) {
+ return Lists.transform(nodes, TYPE_FN);
+ }
+
+ public static List<RelDataTypeFamily> families(List<RelDataType> types) {
+ return Lists.transform(types, FAMILY_FN);
+ }
+
//~ Inner Classes ----------------------------------------------------------
/**
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql/SqlTimeLiteral.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlTimeLiteral.java b/core/src/main/java/org/apache/calcite/sql/SqlTimeLiteral.java
index 31572e1..79166c3 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlTimeLiteral.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlTimeLiteral.java
@@ -20,6 +20,8 @@ import org.apache.calcite.avatica.util.DateTimeUtils;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.SqlTypeName;
+import com.google.common.base.Preconditions;
+
import java.util.Calendar;
/**
@@ -34,23 +36,19 @@ public class SqlTimeLiteral extends SqlAbstractDateTimeLiteral {
SqlTimeLiteral(
Calendar t,
int precision,
- boolean hasTZ,
+ boolean hasTimeZone,
SqlParserPos pos) {
- super(
- t,
- hasTZ,
- SqlTypeName.TIME,
- precision, DateTimeUtils.TIME_FORMAT_STRING,
- pos);
+ this(t, precision, hasTimeZone, DateTimeUtils.TIME_FORMAT_STRING, pos);
}
SqlTimeLiteral(
Calendar t,
int precision,
- boolean hasTZ,
+ boolean hasTimeZone,
String format,
SqlParserPos pos) {
- super(t, hasTZ, SqlTypeName.TIME, precision, format, pos);
+ super(t, hasTimeZone, SqlTypeName.TIME, precision, format, pos);
+ Preconditions.checkArgument(this.precision >= 0 && this.precision <= 3);
}
//~ Methods ----------------------------------------------------------------
@@ -75,8 +73,6 @@ public class SqlTimeLiteral extends SqlAbstractDateTimeLiteral {
String result = getTime().toString(formatString);
final Calendar cal = getCal();
if (precision > 0) {
- assert precision <= 3;
-
// get the millisecond count. millisecond => at most 3 digits.
String digits = Long.toString(cal.getTimeInMillis());
result =
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql/SqlTimestampLiteral.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlTimestampLiteral.java b/core/src/main/java/org/apache/calcite/sql/SqlTimestampLiteral.java
index af31b42..a620595 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlTimestampLiteral.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlTimestampLiteral.java
@@ -20,6 +20,8 @@ import org.apache.calcite.avatica.util.DateTimeUtils;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.SqlTypeName;
+import com.google.common.base.Preconditions;
+
import java.util.Calendar;
/**
@@ -36,11 +38,7 @@ public class SqlTimestampLiteral extends SqlAbstractDateTimeLiteral {
int precision,
boolean hasTimeZone,
SqlParserPos pos) {
- super(
- cal,
- hasTimeZone,
- SqlTypeName.TIMESTAMP,
- precision, DateTimeUtils.TIMESTAMP_FORMAT_STRING,
+ this(cal, precision, hasTimeZone, DateTimeUtils.TIMESTAMP_FORMAT_STRING,
pos);
}
@@ -50,33 +48,12 @@ public class SqlTimestampLiteral extends SqlAbstractDateTimeLiteral {
boolean hasTimeZone,
String format,
SqlParserPos pos) {
- super(
- cal, hasTimeZone, SqlTypeName.TIMESTAMP, precision,
- format, pos);
+ super(cal, hasTimeZone, SqlTypeName.TIMESTAMP, precision, format, pos);
+ Preconditions.checkArgument(this.precision >= 0 && this.precision <= 3);
}
//~ Methods ----------------------------------------------------------------
-/*
- /**
- * Converts this literal to a {@link java.sql.Timestamp} object.
- o/
- public Timestamp getTimestamp() {
- return new Timestamp(getCal().getTimeInMillis());
- }
-*/
-
-/*
- /**
- * Converts this literal to a {@link java.sql.Time} object.
- o/
- public Time getTime() {
- long millis = getCal().getTimeInMillis();
- int tzOffset = Calendar.getInstance().getTimeZone().getOffset(millis);
- return new Time(millis - tzOffset);
- }
-*/
-
public SqlNode clone(SqlParserPos pos) {
return new SqlTimestampLiteral(
(Calendar) value,
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql/fun/SqlBetweenOperator.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlBetweenOperator.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlBetweenOperator.java
index 4b982ad..03b6646 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlBetweenOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlBetweenOperator.java
@@ -83,9 +83,8 @@ public class SqlBetweenOperator extends SqlInfixOperator {
* Custom operand-type checking strategy.
*/
private static final SqlOperandTypeChecker OTC_CUSTOM =
- new ComparableOperandTypeChecker(
- 3,
- RelDataTypeComparability.ALL);
+ new ComparableOperandTypeChecker(3, RelDataTypeComparability.ALL,
+ SqlOperandTypeChecker.Consistency.COMPARE);
private static final SqlWriter.FrameType FRAME_TYPE =
SqlWriter.FrameTypeEnum.create("BETWEEN");
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql/type/AssignableOperandTypeChecker.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/type/AssignableOperandTypeChecker.java b/core/src/main/java/org/apache/calcite/sql/type/AssignableOperandTypeChecker.java
index 3f9f9ba..7ee0a64 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/AssignableOperandTypeChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/AssignableOperandTypeChecker.java
@@ -52,12 +52,10 @@ public class AssignableOperandTypeChecker implements SqlOperandTypeChecker {
//~ Methods ----------------------------------------------------------------
- // implement SqlOperandTypeChecker
public SqlOperandCountRange getOperandCountRange() {
return SqlOperandCountRanges.of(paramTypes.size());
}
- // implement SqlOperandTypeChecker
public boolean checkOperandTypes(
SqlCallBinding callBinding,
boolean throwOnFailure) {
@@ -78,7 +76,6 @@ public class AssignableOperandTypeChecker implements SqlOperandTypeChecker {
return true;
}
- // implement SqlOperandTypeChecker
public String getAllowedSignatures(SqlOperator op, String opName) {
StringBuilder sb = new StringBuilder();
sb.append(opName);
@@ -94,6 +91,10 @@ public class AssignableOperandTypeChecker implements SqlOperandTypeChecker {
sb.append(")");
return sb.toString();
}
+
+ public Consistency getConsistency() {
+ return Consistency.NONE;
+ }
}
// End AssignableOperandTypeChecker.java
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql/type/ComparableOperandTypeChecker.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/type/ComparableOperandTypeChecker.java b/core/src/main/java/org/apache/calcite/sql/type/ComparableOperandTypeChecker.java
index 454897d..3370b5f 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/ComparableOperandTypeChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/ComparableOperandTypeChecker.java
@@ -23,6 +23,8 @@ import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlOperatorBinding;
import org.apache.calcite.sql.SqlUtil;
+import com.google.common.base.Preconditions;
+
import java.util.Collections;
/**
@@ -33,14 +35,21 @@ public class ComparableOperandTypeChecker extends SameOperandTypeChecker {
//~ Instance fields --------------------------------------------------------
private final RelDataTypeComparability requiredComparability;
+ private final Consistency consistency;
//~ Constructors -----------------------------------------------------------
- public ComparableOperandTypeChecker(
- int nOperands,
+ @Deprecated // to be removed before 2.0
+ public ComparableOperandTypeChecker(int nOperands,
RelDataTypeComparability requiredComparability) {
+ this(nOperands, requiredComparability, Consistency.NONE);
+ }
+
+ public ComparableOperandTypeChecker(int nOperands,
+ RelDataTypeComparability requiredComparability, Consistency consistency) {
super(nOperands);
this.requiredComparability = requiredComparability;
+ this.consistency = Preconditions.checkNotNull(consistency);
}
//~ Methods ----------------------------------------------------------------
@@ -107,11 +116,14 @@ public class ComparableOperandTypeChecker extends SameOperandTypeChecker {
return b;
}
- // implement SqlOperandTypeChecker
public String getAllowedSignatures(SqlOperator op, String opName) {
return SqlUtil.getAliasedSignature(op, opName,
Collections.nCopies(nOperands, "COMPARABLE_TYPE"));
}
+
+ @Override public Consistency getConsistency() {
+ return consistency;
+ }
}
// End ComparableOperandTypeChecker.java
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql/type/CompositeOperandTypeChecker.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/type/CompositeOperandTypeChecker.java b/core/src/main/java/org/apache/calcite/sql/type/CompositeOperandTypeChecker.java
index 712c3c4..6a77c95 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/CompositeOperandTypeChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/CompositeOperandTypeChecker.java
@@ -111,6 +111,10 @@ public class CompositeOperandTypeChecker implements SqlOperandTypeChecker {
return allowedRules;
}
+ public Consistency getConsistency() {
+ return Consistency.NONE;
+ }
+
public String getAllowedSignatures(SqlOperator op, String opName) {
if (allowedSignatures != null) {
return allowedSignatures;
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql/type/FamilyOperandTypeChecker.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/type/FamilyOperandTypeChecker.java b/core/src/main/java/org/apache/calcite/sql/type/FamilyOperandTypeChecker.java
index c421727..4bef483 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/FamilyOperandTypeChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/FamilyOperandTypeChecker.java
@@ -50,7 +50,6 @@ public class FamilyOperandTypeChecker implements SqlSingleOperandTypeChecker {
//~ Methods ----------------------------------------------------------------
- // implement SqlSingleOperandTypeChecker
public boolean checkSingleOperandType(
SqlCallBinding callBinding,
SqlNode node,
@@ -89,7 +88,6 @@ public class FamilyOperandTypeChecker implements SqlSingleOperandTypeChecker {
return true;
}
- // implement SqlOperandTypeChecker
public boolean checkOperandTypes(
SqlCallBinding callBinding,
boolean throwOnFailure) {
@@ -111,15 +109,17 @@ public class FamilyOperandTypeChecker implements SqlSingleOperandTypeChecker {
return true;
}
- // implement SqlOperandTypeChecker
public SqlOperandCountRange getOperandCountRange() {
return SqlOperandCountRanges.of(families.size());
}
- // implement SqlOperandTypeChecker
public String getAllowedSignatures(SqlOperator op, String opName) {
return SqlUtil.getAliasedSignature(op, opName, families);
}
+
+ public Consistency getConsistency() {
+ return Consistency.NONE;
+ }
}
// End FamilyOperandTypeChecker.java
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql/type/LiteralOperandTypeChecker.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/type/LiteralOperandTypeChecker.java b/core/src/main/java/org/apache/calcite/sql/type/LiteralOperandTypeChecker.java
index 83161d1..ac94f14 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/LiteralOperandTypeChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/LiteralOperandTypeChecker.java
@@ -91,6 +91,10 @@ public class LiteralOperandTypeChecker implements SqlSingleOperandTypeChecker {
public String getAllowedSignatures(SqlOperator op, String opName) {
return "<LITERAL>";
}
+
+ public Consistency getConsistency() {
+ return Consistency.NONE;
+ }
}
// End LiteralOperandTypeChecker.java
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql/type/MultisetOperandTypeChecker.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/type/MultisetOperandTypeChecker.java b/core/src/main/java/org/apache/calcite/sql/type/MultisetOperandTypeChecker.java
index 5b33fce..2d04c46 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/MultisetOperandTypeChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/MultisetOperandTypeChecker.java
@@ -89,6 +89,10 @@ public class MultisetOperandTypeChecker implements SqlOperandTypeChecker {
public String getAllowedSignatures(SqlOperator op, String opName) {
return "<MULTISET> " + opName + " <MULTISET>";
}
+
+ public Consistency getConsistency() {
+ return Consistency.NONE;
+ }
}
// End MultisetOperandTypeChecker.java
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
----------------------------------------------------------------------
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 10bccbd..0d57234 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
@@ -158,6 +158,10 @@ public abstract class OperandTypes {
public String getAllowedSignatures(SqlOperator op, String opName) {
return opName + "(...)";
}
+
+ public Consistency getConsistency() {
+ return Consistency.NONE;
+ }
};
}
@@ -315,7 +319,8 @@ public abstract class OperandTypes {
*/
public static final SqlOperandTypeChecker
COMPARABLE_ORDERED_COMPARABLE_ORDERED =
- new ComparableOperandTypeChecker(2, RelDataTypeComparability.ALL);
+ new ComparableOperandTypeChecker(2, RelDataTypeComparability.ALL,
+ SqlOperandTypeChecker.Consistency.COMPARE);
/**
* Operand type-checking strategy where operand type must allow ordered
@@ -323,7 +328,8 @@ public abstract class OperandTypes {
* functions
*/
public static final SqlOperandTypeChecker COMPARABLE_ORDERED =
- new ComparableOperandTypeChecker(1, RelDataTypeComparability.ALL);
+ new ComparableOperandTypeChecker(1, RelDataTypeComparability.ALL,
+ SqlOperandTypeChecker.Consistency.NONE);
/**
* Operand type-checking strategy where operand types must allow unordered
@@ -331,7 +337,8 @@ public abstract class OperandTypes {
*/
public static final SqlOperandTypeChecker
COMPARABLE_UNORDERED_COMPARABLE_UNORDERED =
- new ComparableOperandTypeChecker(2, RelDataTypeComparability.UNORDERED);
+ new ComparableOperandTypeChecker(2, RelDataTypeComparability.UNORDERED,
+ SqlOperandTypeChecker.Consistency.LEAST_RESTRICTIVE);
/**
* Operand type-checking strategy where two operands must both be in the
@@ -479,6 +486,10 @@ public abstract class OperandTypes {
public String getAllowedSignatures(SqlOperator op, String opName) {
return "UNNEST(<MULTISET>)";
}
+
+ public Consistency getConsistency() {
+ return Consistency.NONE;
+ }
};
/** Checker that returns whether a value is a collection (multiset or array)
@@ -539,6 +550,10 @@ public abstract class OperandTypes {
return SqlUtil.getAliasedSignature(op, opName,
ImmutableList.of("RECORDTYPE(SINGLE FIELD)"));
}
+
+ public Consistency getConsistency() {
+ return Consistency.NONE;
+ }
};
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql/type/SameOperandTypeChecker.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/type/SameOperandTypeChecker.java b/core/src/main/java/org/apache/calcite/sql/type/SameOperandTypeChecker.java
index 5f853b8..dbbd393 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/SameOperandTypeChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/SameOperandTypeChecker.java
@@ -49,7 +49,10 @@ public class SameOperandTypeChecker implements SqlSingleOperandTypeChecker {
//~ Methods ----------------------------------------------------------------
- // implement SqlOperandTypeChecker
+ public Consistency getConsistency() {
+ return Consistency.NONE;
+ }
+
public boolean checkOperandTypes(
SqlCallBinding callBinding,
boolean throwOnFailure) {
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql/type/SetopOperandTypeChecker.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/type/SetopOperandTypeChecker.java b/core/src/main/java/org/apache/calcite/sql/type/SetopOperandTypeChecker.java
index 04c812b..a38b682 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/SetopOperandTypeChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/SetopOperandTypeChecker.java
@@ -123,7 +123,11 @@ public class SetopOperandTypeChecker implements SqlOperandTypeChecker {
}
public String getAllowedSignatures(SqlOperator op, String opName) {
- return "{0} " + opName + " {1}"; // todo: Wael, please review.
+ return "{0} " + opName + " {1}";
+ }
+
+ public Consistency getConsistency() {
+ return Consistency.NONE;
}
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql/type/SqlOperandTypeChecker.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlOperandTypeChecker.java b/core/src/main/java/org/apache/calcite/sql/type/SqlOperandTypeChecker.java
index b6a21f6..5a9eea4 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/SqlOperandTypeChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/SqlOperandTypeChecker.java
@@ -55,6 +55,21 @@ public interface SqlOperandTypeChecker {
* @return generated string
*/
String getAllowedSignatures(SqlOperator op, String opName);
+
+ /** Returns the strategy for making the arguments have consistency types. */
+ Consistency getConsistency();
+
+ /** Strategy used to make arguments consistent. */
+ enum Consistency {
+ /** Do not try to make arguments consistent. */
+ NONE,
+ /** Make arguments of consistent type using comparison semantics.
+ * Character values are implicitly converted to numeric, date-time, interval
+ * or boolean. */
+ COMPARE,
+ /** Convert all arguments to the least restrictive type. */
+ LEAST_RESTRICTIVE
+ }
}
// End SqlOperandTypeChecker.java
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
index 39c9ded..658acd4 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
@@ -1217,6 +1217,36 @@ public abstract class SqlTypeUtil {
return true;
}
+ // We can implicitly convert from character to date
+ if (family1 == SqlTypeFamily.CHARACTER
+ && canConvertStringInCompare(family2)
+ || family2 == SqlTypeFamily.CHARACTER
+ && canConvertStringInCompare(family1)) {
+ return true;
+ }
+
+ return false;
+ }
+
+ /** Returns whether a character data type can be implicitly converted to a
+ * given family in a compare operation. */
+ private static boolean canConvertStringInCompare(RelDataTypeFamily family) {
+ if (family instanceof SqlTypeFamily) {
+ SqlTypeFamily sqlTypeFamily = (SqlTypeFamily) family;
+ switch (sqlTypeFamily) {
+ case DATE:
+ case TIME:
+ case TIMESTAMP:
+ case INTERVAL_DAY_TIME:
+ case INTERVAL_YEAR_MONTH:
+ case NUMERIC:
+ case APPROXIMATE_NUMERIC:
+ case EXACT_NUMERIC:
+ case INTEGER:
+ case BOOLEAN:
+ return true;
+ }
+ }
return false;
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 7fadfa0..4247083 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -1308,7 +1308,8 @@ public class SqlToRelConverter {
rexBuilder.makeCall(
SqlStdOperatorTable.EQUALS,
leftKeys.get(0),
- bb.convertExpression(rightVals));
+ rexBuilder.ensureType(leftKeys.get(0).getType(),
+ bb.convertExpression(rightVals), true));
} else {
assert rightVals instanceof SqlCall;
final SqlBasicCall call = (SqlBasicCall) rightVals;
@@ -1322,7 +1323,9 @@ public class SqlToRelConverter {
new Function<Pair<RexNode, SqlNode>, RexNode>() {
public RexNode apply(Pair<RexNode, SqlNode> pair) {
return rexBuilder.makeCall(SqlStdOperatorTable.EQUALS,
- pair.left, bb.convertExpression(pair.right));
+ pair.left,
+ rexBuilder.ensureType(pair.left.getType(),
+ bb.convertExpression(pair.right), true));
}
}),
false);
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index 850d798..8bf28e0 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -22,6 +22,7 @@ import org.apache.calcite.avatica.util.TimeUnitRange;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
+import org.apache.calcite.rel.type.RelDataTypeFamily;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexCallBinding;
@@ -62,17 +63,20 @@ import org.apache.calcite.sql.fun.SqlRowOperator;
import org.apache.calcite.sql.fun.SqlSequenceValueOperator;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
-import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.SqlOperandTypeChecker;
+import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.util.Util;
import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
import java.math.BigDecimal;
import java.math.MathContext;
-import java.util.AbstractList;
import java.util.ArrayList;
import java.util.List;
+import java.util.Set;
/**
* Standard implementation of {@link SqlRexConvertletTable}.
@@ -513,7 +517,8 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
SqlCall call) {
final RexBuilder rexBuilder = cx.getRexBuilder();
final List<SqlNode> operands = call.getOperandList();
- final List<RexNode> exprs = convertExpressionList(cx, operands);
+ final List<RexNode> exprs = convertExpressionList(cx, operands,
+ SqlOperandTypeChecker.Consistency.NONE);
// TODO: Will need to use decimal type for seconds with precision
RelDataType resType =
@@ -626,7 +631,8 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
// Rewrite datetime minus
final RexBuilder rexBuilder = cx.getRexBuilder();
final List<SqlNode> operands = call.getOperandList();
- final List<RexNode> exprs = convertExpressionList(cx, operands);
+ final List<RexNode> exprs = convertExpressionList(cx, operands,
+ SqlOperandTypeChecker.Consistency.NONE);
// TODO: Handle year month interval (represented in months)
for (RexNode expr : exprs) {
@@ -668,7 +674,8 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
SqlFunction fun,
SqlCall call) {
final List<SqlNode> operands = call.getOperandList();
- final List<RexNode> exprs = convertExpressionList(cx, operands);
+ final List<RexNode> exprs = convertExpressionList(cx, operands,
+ SqlOperandTypeChecker.Consistency.NONE);
if (fun.getFunctionType() == SqlFunctionCategory.USER_DEFINED_CONSTRUCTOR) {
return makeConstructorCall(cx, fun, exprs);
}
@@ -703,7 +710,8 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
if (call.isCountStar()) {
exprs = ImmutableList.of();
} else {
- exprs = convertExpressionList(cx, operands);
+ exprs = convertExpressionList(cx, operands,
+ SqlOperandTypeChecker.Consistency.NONE);
}
RelDataType returnType =
cx.getValidator().getValidatedNodeTypeIfKnown(call);
@@ -769,11 +777,12 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
SqlOperator op) {
final List<SqlNode> operands = call.getOperandList();
final RexBuilder rexBuilder = cx.getRexBuilder();
- final List<RexNode> exprs = convertExpressionList(cx, operands);
- if (op.getOperandTypeChecker()
- == OperandTypes.COMPARABLE_UNORDERED_COMPARABLE_UNORDERED) {
- ensureSameType(cx, exprs);
- }
+ final SqlOperandTypeChecker.Consistency consistency =
+ op.getOperandTypeChecker() == null
+ ? SqlOperandTypeChecker.Consistency.NONE
+ : op.getOperandTypeChecker().getConsistency();
+ final List<RexNode> exprs =
+ convertExpressionList(cx, operands, consistency);
RelDataType type = rexBuilder.deriveReturnType(op, exprs);
return rexBuilder.makeCall(type, op, RexUtil.flatten(exprs, op));
}
@@ -793,35 +802,68 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
return list;
}
- private void ensureSameType(SqlRexContext cx, final List<RexNode> exprs) {
- RelDataType type =
- cx.getTypeFactory().leastRestrictive(
- new AbstractList<RelDataType>() {
- public RelDataType get(int index) {
- return exprs.get(index).getType();
- }
-
- public int size() {
- return exprs.size();
- }
- });
- for (int i = 0; i < exprs.size(); i++) {
- // REVIEW: assigning to a list that may be immutable?
- exprs.set(
- i, cx.getRexBuilder().ensureType(type, exprs.get(i), true));
- }
- }
-
- private static List<RexNode> convertExpressionList(
- SqlRexContext cx,
- List<SqlNode> nodes) {
- final ArrayList<RexNode> exprs = new ArrayList<RexNode>();
+ private static List<RexNode> convertExpressionList(SqlRexContext cx,
+ List<SqlNode> nodes, SqlOperandTypeChecker.Consistency consistency) {
+ final List<RexNode> exprs = Lists.newArrayList();
for (SqlNode node : nodes) {
exprs.add(cx.convertExpression(node));
}
+ if (exprs.size() > 1) {
+ final RelDataType type =
+ consistentType(cx, consistency, RexUtil.types(exprs));
+ if (type != null) {
+ final List<RexNode> oldExprs = Lists.newArrayList(exprs);
+ exprs.clear();
+ for (RexNode expr : oldExprs) {
+ exprs.add(cx.getRexBuilder().ensureType(type, expr, true));
+ }
+ }
+ }
return exprs;
}
+ private static RelDataType consistentType(SqlRexContext cx,
+ SqlOperandTypeChecker.Consistency consistency, List<RelDataType> types) {
+ switch (consistency) {
+ case COMPARE:
+ final Set<RelDataTypeFamily> families =
+ Sets.newHashSet(RexUtil.families(types));
+ if (families.size() < 2) {
+ // All arguments are of same family. No need for explicit casts.
+ return null;
+ }
+ final List<RelDataType> nonCharacterTypes = Lists.newArrayList();
+ for (RelDataType type : types) {
+ if (type.getFamily() != SqlTypeFamily.CHARACTER) {
+ nonCharacterTypes.add(type);
+ }
+ }
+ if (!nonCharacterTypes.isEmpty()) {
+ final int typeCount = types.size();
+ types = nonCharacterTypes;
+ if (nonCharacterTypes.size() < typeCount) {
+ final RelDataTypeFamily family =
+ nonCharacterTypes.get(0).getFamily();
+ if (family instanceof SqlTypeFamily) {
+ // The character arguments might be larger than the numeric
+ // argument. Give ourselves some headroom.
+ switch ((SqlTypeFamily) family) {
+ case INTEGER:
+ case NUMERIC:
+ nonCharacterTypes.add(
+ cx.getTypeFactory().createSqlType(SqlTypeName.BIGINT));
+ }
+ }
+ }
+ }
+ // fall through
+ case LEAST_RESTRICTIVE:
+ return cx.getTypeFactory().leastRestrictive(types);
+ default:
+ return null;
+ }
+ }
+
private RexNode convertPlus(SqlRexContext cx, SqlCall call) {
final RexNode rex = convertCall(cx, call);
switch (rex.getType().getSqlTypeName()) {
@@ -853,20 +895,17 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
SqlRexContext cx,
SqlBetweenOperator op,
SqlCall call) {
- final SqlNode value = call.operand(SqlBetweenOperator.VALUE_OPERAND);
- RexNode x = cx.convertExpression(value);
- final SqlBetweenOperator.Flag symmetric = op.flag;
- final SqlNode lower = call.operand(SqlBetweenOperator.LOWER_OPERAND);
- RexNode y = cx.convertExpression(lower);
- final SqlNode upper = call.operand(SqlBetweenOperator.UPPER_OPERAND);
- RexNode z = cx.convertExpression(upper);
+ final List<RexNode> list =
+ convertExpressionList(cx, call.getOperandList(),
+ op.getOperandTypeChecker().getConsistency());
+ final RexNode x = list.get(SqlBetweenOperator.VALUE_OPERAND);
+ final RexNode y = list.get(SqlBetweenOperator.LOWER_OPERAND);
+ final RexNode z = list.get(SqlBetweenOperator.UPPER_OPERAND);
final RexBuilder rexBuilder = cx.getRexBuilder();
RexNode ge1 =
rexBuilder.makeCall(
- SqlStdOperatorTable.GREATER_THAN_OR_EQUAL,
- x,
- y);
+ SqlStdOperatorTable.GREATER_THAN_OR_EQUAL, x, y);
RexNode le1 =
rexBuilder.makeCall(
SqlStdOperatorTable.LESS_THAN_OR_EQUAL,
@@ -879,6 +918,7 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
le1);
RexNode res;
+ final SqlBetweenOperator.Flag symmetric = op.flag;
switch (symmetric) {
case ASYMMETRIC:
res = and1;
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/test/java/org/apache/calcite/test/CalciteAssert.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/CalciteAssert.java b/core/src/test/java/org/apache/calcite/test/CalciteAssert.java
index 298f8d6..9ebb85a 100644
--- a/core/src/test/java/org/apache/calcite/test/CalciteAssert.java
+++ b/core/src/test/java/org/apache/calcite/test/CalciteAssert.java
@@ -629,13 +629,22 @@ public class CalciteAssert {
public static SchemaPlus addSchema(SchemaPlus rootSchema, SchemaSpec schema) {
SchemaPlus foodmart;
+ SchemaPlus jdbcScott;
+ final ConnectionSpec cs;
+ final DataSource dataSource;
switch (schema) {
case REFLECTIVE_FOODMART:
return rootSchema.add("foodmart",
new ReflectiveSchema(new JdbcTest.FoodmartSchema()));
+ case JDBC_SCOTT:
+ cs = DatabaseInstance.HSQLDB.scott;
+ dataSource = JdbcSchema.dataSource(cs.url, cs.driver, cs.username,
+ cs.password);
+ return rootSchema.add("jdbc_scott",
+ JdbcSchema.create(rootSchema, "jdbc_scott", dataSource, null, null));
case JDBC_FOODMART:
- final ConnectionSpec cs = DB.foodmart;
- final DataSource dataSource =
+ cs = DB.foodmart;
+ dataSource =
JdbcSchema.dataSource(cs.url, cs.driver, cs.username, cs.password);
return rootSchema.add("foodmart",
JdbcSchema.create(rootSchema, "foodmart", dataSource, null,
@@ -655,6 +664,13 @@ public class CalciteAssert {
+ "join \"foodmart\".\"product_class\" as pc on p.\"product_class_id\" = pc.\"product_class_id\"",
true));
return foodmart;
+ case SCOTT:
+ jdbcScott = rootSchema.getSubSchema("jdbc_scott");
+ if (jdbcScott == null) {
+ jdbcScott =
+ CalciteAssert.addSchema(rootSchema, SchemaSpec.JDBC_SCOTT);
+ }
+ return rootSchema.add("scott", new CloneSchema(jdbcScott));
case CLONE_FOODMART:
foodmart = rootSchema.getSubSchema("foodmart");
if (foodmart == null) {
@@ -747,6 +763,8 @@ public class CalciteAssert {
return with(SchemaSpec.CLONE_FOODMART);
case JDBC_FOODMART_WITH_LATTICE:
return with(SchemaSpec.JDBC_FOODMART_WITH_LATTICE);
+ case SCOTT:
+ return with(SchemaSpec.SCOTT);
default:
throw Util.unexpected(config);
}
@@ -1378,6 +1396,9 @@ public class CalciteAssert {
/** Configuration that includes the metadata schema. */
REGULAR_PLUS_METADATA,
+ /** Configuration that loads the "scott/tiger" database. */
+ SCOTT,
+
/** Configuration that loads Spark. */
SPARK,
}
@@ -1462,6 +1483,8 @@ public class CalciteAssert {
CLONE_FOODMART,
JDBC_FOODMART_WITH_LATTICE,
HR,
+ JDBC_SCOTT,
+ SCOTT,
LINGUAL,
POST
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/test/java/org/apache/calcite/test/JdbcTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 7af8448..9ea3d08 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -4406,6 +4406,11 @@ public class JdbcTest {
.with(CalciteAssert.Config.FOODMART_CLONE)
.connect();
}
+ if (name.equals("scott")) {
+ return CalciteAssert.that()
+ .with(CalciteAssert.Config.SCOTT)
+ .connect();
+ }
if (name.equals("post")) {
return CalciteAssert.that()
.with(CalciteAssert.Config.REGULAR)
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 6718df4..ec3fed1 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -364,24 +364,14 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
}
@Test public void testEqualNotEqualFails() {
- checkExpFails(
- "^''<>1^",
- "(?s).*Cannot apply '<>' to arguments of type '<CHAR.0.> <> <INTEGER>'.*");
- checkExpFails(
- "^'1'>=1^",
- "(?s).*Cannot apply '>=' to arguments of type '<CHAR.1.> >= <INTEGER>'.*");
- checkExpFails(
- "^1<>n'abc'^",
- "(?s).*Cannot apply '<>' to arguments of type '<INTEGER> <> <CHAR.3.>'.*");
- checkExpFails(
- "^''=.1^",
- "(?s).*Cannot apply '=' to arguments of type '<CHAR.0.> = <DECIMAL.1..1.>'.*");
+ checkExp("''<>1"); // compare CHAR, INTEGER ok; implicitly convert CHAR
+ checkExp("'1'>=1");
+ checkExp("1<>n'abc'"); // compare INTEGER, NCHAR ok
+ checkExp("''=.1"); // compare CHAR, DECIMAL ok
checkExpFails(
"^true<>1e-1^",
"(?s).*Cannot apply '<>' to arguments of type '<BOOLEAN> <> <DOUBLE>'.*");
- checkExpFails(
- "^false=''^",
- "(?s).*Cannot apply '=' to arguments of type '<BOOLEAN> = <CHAR.0.>'.*");
+ checkExp("false=''"); // compare BOOLEAN, CHAR ok
checkExpFails(
"^x'a4'=0.01^",
"(?s).*Cannot apply '=' to arguments of type '<BINARY.1.> = <DECIMAL.3, 2.>'.*");
@@ -628,8 +618,8 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
@Test public void testBetween() {
checkExp("1 between 2 and 3");
checkExp("'a' between 'b' and 'c'");
- checkWholeExpFails(
- "'' between 2 and 3",
+ checkExp("'' between 2 and 3"); // can implicitly convert CHAR to INTEGER
+ checkWholeExpFails("date '2012-02-03' between 2 and 3",
"(?s).*Cannot apply 'BETWEEN' to arguments of type.*");
}
@@ -4486,7 +4476,7 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
*
* <p>See also
* <a href="https://issues.apache.org/jira/browse/CALCITE-546">[CALCITE-546]
- * "Allow table, column and field called '*'"</a> (not yet fixed).
+ * Allow table, column and field called '*'</a> (not yet fixed).
*/
@Test public void testStarInFromFails() {
sql("select emp.empno AS x from ^sales.*^")
@@ -4570,8 +4560,8 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
checkExpFails(
"1 in ^((2), (3,4))^",
ERR_IN_VALUES_INCOMPATIBLE);
- checkExpFails(
- "false and ^1 in ('b', 'c')^",
+ checkExp("false and ^1 in ('b', 'c')^");
+ checkExpFails("false and ^1 in (date '2012-01-02', date '2012-01-04')^",
ERR_IN_OPERANDS_INCOMPATIBLE);
checkExpFails(
"1 > 5 ^or (1, 2) in (3, 4)^",
@@ -5149,10 +5139,15 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
}
@Test public void testNaturalJoinIncompatibleDatatype() {
- checkFails(
- "select * from emp natural ^join^\n"
+ checkFails("select *\n"
+ + "from (select ename as name, hiredate as deptno from emp)\n"
+ + "natural ^join^\n"
+ "(select deptno, name as sal from dept)",
- "Column 'SAL' matched using NATURAL keyword or USING clause has incompatible types: cannot compare 'INTEGER' to 'VARCHAR\\(10\\)'");
+ "Column 'DEPTNO' matched using NATURAL keyword or USING clause has incompatible types: cannot compare 'TIMESTAMP\\(0\\)' to 'INTEGER'");
+
+ // INTEGER and VARCHAR are comparable: VARCHAR implicit converts to INTEGER
+ check("select * from emp natural ^join^\n"
+ + "(select deptno, name as sal from dept)");
// make sal occur more than once on rhs, it is ignored and therefore
// there is no error about incompatible types
@@ -5161,9 +5156,14 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
}
@Test public void testJoinUsingIncompatibleDatatype() {
- checkFails(
- "select * from emp join (select deptno, name as sal from dept) using (deptno, ^sal^)",
- "Column 'SAL' matched using NATURAL keyword or USING clause has incompatible types: cannot compare 'INTEGER' to 'VARCHAR\\(10\\)'");
+ checkFails("select *\n"
+ + "from (select ename as name, hiredate as deptno from emp)\n"
+ + "join (select deptno, name as sal from dept) using (^deptno^, sal)",
+ "Column 'DEPTNO' matched using NATURAL keyword or USING clause has incompatible types: cannot compare 'TIMESTAMP\\(0\\)' to 'INTEGER'");
+
+ // INTEGER and VARCHAR are comparable: VARCHAR implicit converts to INTEGER
+ check("select * from emp\n"
+ + "join (select deptno, name as sal from dept) using (deptno, sal)");
}
@Test public void testJoinUsingInvalidColsFails() {
@@ -5624,7 +5624,7 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
}
/** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-633">[CALCITE-633],
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-633">[CALCITE-633]
* WITH ... ORDER BY cannot find table</a>. */
@Test public void testWithOrder() {
sql("with e as (select * from emp)\n"
@@ -6183,6 +6183,41 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
"(?s).*Cannot apply '=' to arguments of type '<INTERVAL MONTH> = <INTERVAL DAY>'.*");
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-613">[CALCITE-613]
+ * Implicitly convert strings in comparisons</a>. */
+ @Test public void testDateCompare() {
+ // can convert character value to date, time, timestamp, interval
+ // provided it is on one side of a comparison operator (=, <, >, BETWEEN)
+ checkExpType("date '2015-03-17' < '2015-03-18'", "BOOLEAN NOT NULL");
+ checkExpType("date '2015-03-17' > '2015-03-18'", "BOOLEAN NOT NULL");
+ checkExpType("date '2015-03-17' = '2015-03-18'", "BOOLEAN NOT NULL");
+ checkExpType("'2015-03-17' < date '2015-03-18'", "BOOLEAN NOT NULL");
+ checkExpType("date '2015-03-17' between '2015-03-16' and '2015-03-19'",
+ "BOOLEAN NOT NULL");
+ checkExpType("date '2015-03-17' between '2015-03-16' and '2015-03'||'-19'",
+ "BOOLEAN NOT NULL");
+ checkExpType("'2015-03-17' between date '2015-03-16' and date '2015-03-19'",
+ "BOOLEAN NOT NULL");
+ checkExpType("date '2015-03-17' between date '2015-03-16' and '2015-03-19'",
+ "BOOLEAN NOT NULL");
+ checkExpType("date '2015-03-17' between '2015-03-16' and date '2015-03-19'",
+ "BOOLEAN NOT NULL");
+ checkExpType("time '12:34:56' < '12:34:57'", "BOOLEAN NOT NULL");
+ checkExpType("timestamp '2015-03-17 12:34:56' < '2015-03-17 12:34:57'",
+ "BOOLEAN NOT NULL");
+ checkExpType("interval '2' hour < '2:30'", "BOOLEAN NOT NULL");
+
+ // can convert to exact and approximate numeric
+ checkExpType("123 > '72'", "BOOLEAN NOT NULL");
+ checkExpType("12.3 > '7.2'", "BOOLEAN NOT NULL");
+
+ // can convert to boolean
+ checkExpType("true = 'true'", "BOOLEAN NOT NULL");
+ checkExpFails("^true and 'true'^",
+ "Cannot apply 'AND' to arguments of type '<BOOLEAN> AND <CHAR\\(4\\)>'\\..*");
+ }
+
@Test public void testOverlaps() {
checkExpType(
"(date '1-2-3', date '1-2-3') overlaps (date '1-2-3', date '1-2-3')",
@@ -6549,8 +6584,8 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
}
/** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-xxx">CALCITE-xxx,
- * "Unexpected upper-casing of keywords when using java lexer"</a>. */
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-497">[CALCITE-497]
+ * Support optional qualifier for column name references</a>. */
@Test public void testRecordTypeElided() {
checkResultType(
"SELECT contact.x, contact.coord.y FROM customer.contact",
@@ -6811,8 +6846,8 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
}
/** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-145">CALCITE-145,
- * "Unexpected upper-casing of keywords when using java lexer"</a>. */
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-145">[CALCITE-145]
+ * Unexpected upper-casing of keywords when using java lexer</a>. */
@Test public void testLexJavaKeyword() {
final SqlTester tester1 = tester.withLex(Lex.JAVA);
tester1.checkResultType(
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/272e6040/core/src/test/resources/sql/misc.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/misc.oq b/core/src/test/resources/sql/misc.oq
index 7b8ccb7..63422cf 100644
--- a/core/src/test/resources/sql/misc.oq
+++ b/core/src/test/resources/sql/misc.oq
@@ -826,4 +826,118 @@ select distinct gender, sum(deptno) as s from emp group by gender;
#Expression 'DEPTNO' is not being grouped
#!error
+!use scott
+
+# [CALCITE-613] Implicitly convert strings in comparisons
+select * from "scott".emp where hiredate < '1981-01-02';
++-------+-------+-------+------+------------+--------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-------+------+------------+--------+------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
++-------+-------+-------+------+------------+--------+------+--------+
+(1 row)
+
+!ok
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=['1981-01-02'], expr#9=[CAST($t8):DATE NOT NULL], expr#10=[<($t4, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+select * from "scott".emp where '1981-01-02' > hiredate;
++-------+-------+-------+------+------------+--------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-------+------+------------+--------+------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
++-------+-------+-------+------+------------+--------+------+--------+
+(1 row)
+
+!ok
+select * from "scott".emp where hiredate between '1981-01-02' and '1981-06-01';
++-------+-------+----------+------+------------+---------+--------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+----------+------+------------+---------+--------+--------+
+| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 |
++-------+-------+----------+------+------------+---------+--------+--------+
+(4 rows)
+
+!ok
+select * from "scott".emp where hiredate > '1986-01-02';
++-------+-------+---------+------+------------+---------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+---------+------+------------+---------+------+--------+
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
++-------+-------+---------+------+------------+---------+------+--------+
+(2 rows)
+
+!ok
+select * from "scott".emp where '1986-01-02' < hiredate;
++-------+-------+---------+------+------------+---------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+---------+------+------------+---------+------+--------+
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
++-------+-------+---------+------+------------+---------+------+--------+
+(2 rows)
+
+!ok
+select * from "scott".emp where '1986-' || '01-02' < hiredate;
++-------+-------+---------+------+------------+---------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+---------+------+------------+---------+------+--------+
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
++-------+-------+---------+------+------------+---------+------+--------+
+(2 rows)
+
+!ok
+select * from "scott".emp where sal < '1100';
++-------+-------+-------+------+------------+--------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-------+------+------------+--------+------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
+| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 |
++-------+-------+-------+------+------------+--------+------+--------+
+(2 rows)
+
+!ok
+select * from "scott".emp where empno in ('7369', '7876');
++-------+-------+-------+------+------------+---------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-------+------+------------+---------+------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
++-------+-------+-------+------+------------+---------+------+--------+
+(2 rows)
+
+!ok
+select * from "scott".emp where empno between '7500' and '07600';
++-------+-------+----------+------+------------+---------+--------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+----------+------+------------+---------+--------+--------+
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 |
++-------+-------+----------+------+------------+---------+--------+--------+
+(2 rows)
+
+!ok
+select * from "scott".emp where deptno between '7369' and '7876';
++-------+-------+-----+-----+----------+-----+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----+-----+----------+-----+------+--------+
++-------+-------+-----+-----+----------+-----+------+--------+
+(0 rows)
+
+!ok
+select * from "scott".emp where '7369' between empno and '7876';
++-------+-------+-------+------+------------+--------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-------+------+------------+--------+------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
++-------+-------+-------+------+------------+--------+------+--------+
+(1 row)
+
+!ok
+
# End misc.oq