[CALCITE-2359] Inconsistent results casting intervals to integers (James Duong)
Ensure casting intervals to integers always returns the result in terms of the trailing unit. Previously we had the following behavior: - Casting a standalone day interval literal returned results in terms of the trailing unit. - Casting a standalone year interval literal returned results in terms of months. - Casting a day interval expression returned results in terms of milliseconds. - Casting a year interval expression returned results in terms of months. Close apache/calcite#730 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/184bb185 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/184bb185 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/184bb185 Branch: refs/heads/master Commit: 184bb1857fcb6df63b05410d25bc0078de0f71ab Parents: ccbc656 Author: James Duong <[email protected]> Authored: Tue Jun 12 10:44:48 2018 -0700 Committer: Julian Hyde <[email protected]> Committed: Fri Jun 29 01:27:43 2018 -0700 ---------------------------------------------------------------------- .../adapter/enumerable/RexToLixTranslator.java | 32 ++++++++++++- .../java/org/apache/calcite/rex/RexBuilder.java | 3 ++ .../calcite/sql/test/SqlOperatorBaseTest.java | 49 ++++++++++++++++++++ 3 files changed, 83 insertions(+), 1 deletion(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/184bb185/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java index e1c53b8..f131cf8 100644 --- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java +++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java @@ -592,7 +592,7 @@ public class RexToLixTranslator { convert = RexImpTable.multiplyDivide(convert, multiplier, divider); } } - return convert; + return scaleIntervalToNumber(sourceType, targetType, convert); } /** Adapts an expression with "normal" result to one that adheres to @@ -1257,6 +1257,36 @@ public class RexToLixTranslator { return root; } + private static Expression scaleIntervalToNumber( + RelDataType sourceType, + RelDataType targetType, + Expression operand) { + switch (targetType.getSqlTypeName().getFamily()) { + case NUMERIC: + switch (sourceType.getSqlTypeName()) { + case INTERVAL_YEAR: + case INTERVAL_YEAR_MONTH: + case INTERVAL_MONTH: + case INTERVAL_DAY: + case INTERVAL_DAY_HOUR: + case INTERVAL_DAY_MINUTE: + case INTERVAL_DAY_SECOND: + case INTERVAL_HOUR: + case INTERVAL_HOUR_MINUTE: + case INTERVAL_HOUR_SECOND: + case INTERVAL_MINUTE: + case INTERVAL_MINUTE_SECOND: + case INTERVAL_SECOND: + // Scale to the given field. + final BigDecimal multiplier = BigDecimal.ONE; + final BigDecimal divider = + sourceType.getSqlTypeName().getEndUnit().multiplier; + return RexImpTable.multiplyDivide(operand, multiplier, divider); + } + } + return operand; + } + /** Translates a field of an input to an expression. */ public interface InputGetter { Expression field(BlockBuilder list, int index, Type storageType); http://git-wip-us.apache.org/repos/asf/calcite/blob/184bb185/core/src/main/java/org/apache/calcite/rex/RexBuilder.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java index a04cbf0..5dcc85b 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java +++ b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java @@ -503,6 +503,9 @@ public class RexBuilder { SqlTypeName typeName = literal.getTypeName(); if (canRemoveCastFromLiteral(type, value, typeName)) { switch (typeName) { + case INTERVAL_YEAR: + case INTERVAL_YEAR_MONTH: + case INTERVAL_MONTH: case INTERVAL_DAY: case INTERVAL_DAY_HOUR: case INTERVAL_DAY_MINUTE: http://git-wip-us.apache.org/repos/asf/calcite/blob/184bb185/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index 0af5a95..afb60c1 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -863,6 +863,55 @@ public abstract class SqlOperatorBaseTest { "cast(INTERVAL '5' day as integer)", "INTEGER NOT NULL", "5"); + + tester.checkScalarExact( + "cast(INTERVAL '1' year as integer)", + "INTEGER NOT NULL", + "1"); + tester.checkScalarExact( + "cast((INTERVAL '1' year - INTERVAL '2' year) as integer)", + "INTEGER NOT NULL", + "-1"); + tester.checkScalarExact( + "cast(INTERVAL '1' month as integer)", + "INTEGER NOT NULL", + "1"); + tester.checkScalarExact( + "cast((INTERVAL '1' month - INTERVAL '2' month) as integer)", + "INTEGER NOT NULL", + "-1"); + tester.checkScalarExact( + "cast(INTERVAL '1' day as integer)", + "INTEGER NOT NULL", + "1"); + tester.checkScalarExact( + "cast((INTERVAL '1' day - INTERVAL '2' day) as integer)", + "INTEGER NOT NULL", + "-1"); + tester.checkScalarExact( + "cast(INTERVAL '1' hour as integer)", + "INTEGER NOT NULL", + "1"); + tester.checkScalarExact( + "cast((INTERVAL '1' hour - INTERVAL '2' hour) as integer)", + "INTEGER NOT NULL", + "-1"); + tester.checkScalarExact( + "cast(INTERVAL '1' hour as integer)", + "INTEGER NOT NULL", + "1"); + tester.checkScalarExact( + "cast((INTERVAL '1' minute - INTERVAL '2' minute) as integer)", + "INTEGER NOT NULL", + "-1"); + tester.checkScalarExact( + "cast(INTERVAL '1' minute as integer)", + "INTEGER NOT NULL", + "1"); + tester.checkScalarExact( + "cast((INTERVAL '1' second - INTERVAL '2' second) as integer)", + "INTEGER NOT NULL", + "-1"); } @Test public void testCastToInterval() {
