This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new c56d556462 [CALCITE-5698] EXTRACT from INTERVAL should return negative
numbers if interval is negative
c56d556462 is described below
commit c56d5564628de6b3265f960764a6f6fc43935a75
Author: zstan <[email protected]>
AuthorDate: Fri May 12 14:20:52 2023 +0300
[CALCITE-5698] EXTRACT from INTERVAL should return negative numbers if
interval is negative
Close apache/calcite#3199
---
.../calcite/adapter/enumerable/RexImpTable.java | 20 +++--
core/src/test/resources/sql/functions.iq | 85 ++++++++++++++++++++++
.../org/apache/calcite/test/SqlOperatorTest.java | 5 ++
3 files changed, 105 insertions(+), 5 deletions(-)
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 2bda9908bb..1f833ad0a4 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
@@ -2777,6 +2777,7 @@ public class RexImpTable {
(TimeUnitRange) translator.getLiteralValue(argValueList.get(0));
final TimeUnit unit = requireNonNull(timeUnitRange,
"timeUnitRange").startUnit;
Expression operand = argValueList.get(1);
+ boolean isIntervalType =
SqlTypeUtil.isInterval(call.operands.get(1).getType());
final SqlTypeName sqlTypeName =
call.operands.get(1).getType().getSqlTypeName();
switch (unit) {
@@ -2832,7 +2833,7 @@ public class RexImpTable {
if (sqlTypeName == SqlTypeName.DATE) {
return Expressions.constant(0L);
}
- operand = mod(operand, TimeUnit.MINUTE.multiplier.longValue());
+ operand = mod(operand, TimeUnit.MINUTE.multiplier.longValue(),
!isIntervalType);
return Expressions.multiply(
operand, Expressions.constant((long) (1 /
unit.multiplier.doubleValue())));
case EPOCH:
@@ -2886,7 +2887,7 @@ public class RexImpTable {
break;
}
- operand = mod(operand, getFactor(unit));
+ operand = mod(operand, getFactor(unit), unit == TimeUnit.QUARTER ||
!isIntervalType);
if (unit == TimeUnit.QUARTER) {
operand = Expressions.subtract(operand, Expressions.constant(1L));
}
@@ -2900,12 +2901,21 @@ public class RexImpTable {
}
}
- private static Expression mod(Expression operand, long factor) {
+ /** Generates an expression for modulo (remainder).
+ *
+ * @param operand Operand expression
+ * @param factor Divisor
+ * @param floorMod Whether negative arguments should yield a negative result
+ */
+ private static Expression mod(Expression operand, long factor,
+ boolean floorMod) {
if (factor == 1L) {
return operand;
+ } else if (floorMod) {
+ return Expressions.call(BuiltInMethod.FLOOR_MOD.method, operand,
+ Expressions.constant(factor));
} else {
- return Expressions.call(BuiltInMethod.FLOOR_MOD.method,
- operand, Expressions.constant(factor));
+ return Expressions.modulo(operand, Expressions.constant(factor));
}
}
diff --git a/core/src/test/resources/sql/functions.iq
b/core/src/test/resources/sql/functions.iq
index a39f4c0bb5..9d828d2b58 100644
--- a/core/src/test/resources/sql/functions.iq
+++ b/core/src/test/resources/sql/functions.iq
@@ -144,6 +144,91 @@ limit 3;
!ok
+# -----------------------------------------------------------------------------
+# EXTRACT
+
+SELECT EXTRACT(DAY FROM DATE '2013-12-25') AS the_day;
++---------+
+| THE_DAY |
++---------+
+| 25 |
++---------+
+(1 row)
+
+!ok
+
+# [CALCITE-5698] EXTRACT from INTERVAL should return negative numbers if
+# interval is negative
+SELECT EXTRACT(MONTH FROM INTERVAL -1 MONTHS) as extr;
++------+
+| EXTR |
++------+
+| -1 |
++------+
+(1 row)
+
+!ok
+
+SELECT EXTRACT(YEAR FROM INTERVAL -34 MONTHS) as extr;
++------+
+| EXTR |
++------+
+| -2 |
++------+
+(1 row)
+
+!ok
+
+SELECT EXTRACT(decade FROM interval '-1400' months(4)) as extr;
++------+
+| EXTR |
++------+
+| -11 |
++------+
+(1 row)
+
+!ok
+
+SELECT EXTRACT(MONTH FROM INTERVAL -14 MONTHS) as extr;
++------+
+| EXTR |
++------+
+| -2 |
++------+
+(1 row)
+
+!ok
+
+SELECT EXTRACT(MINUTE FROM INTERVAL '-10:20' HOURS TO MINUTES) as extr;
++------+
+| EXTR |
++------+
+| -20 |
++------+
+(1 row)
+
+!ok
+
+SELECT EXTRACT(SECOND FROM INTERVAL '-1 2:3:4.567' DAY TO SECOND) as extr;
++------+
+| EXTR |
++------+
+| -4 |
++------+
+(1 row)
+
+!ok
+
+SELECT EXTRACT(MILLISECOND FROM INTERVAL '-2' SECONDS) as extr;
++-------+
+| EXTR |
++-------+
+| -2000 |
++-------+
+(1 row)
+
+!ok
+
# -----------------------------------------------------------------------------
!use oraclefunc
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 1d231fd4cb..346bbbc74c 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -7867,6 +7867,8 @@ public class SqlOperatorTest {
"'A' is not a valid time frame", false);
f.checkScalar("extract(day from interval '2 3:4:5.678' day to second)",
"2", "BIGINT NOT NULL");
+ f.checkScalar("extract(day from interval -'2 3:4:5.678' day to second)",
+ "-2", "BIGINT NOT NULL");
f.checkScalar("extract(day from interval '23456 3:4:5.678' day(5) to
second)",
"23456", "BIGINT NOT NULL");
f.checkScalar("extract(hour from interval '2 3:4:5.678' day to second)",
@@ -7883,6 +7885,9 @@ public class SqlOperatorTest {
f.checkScalar("extract(microsecond from"
+ " interval '2 3:4:5.678' day to second)",
"5678000", "BIGINT NOT NULL");
+ f.checkScalar("extract(microsecond from"
+ + " interval -'2 3:4:5.678' day to second)",
+ "-5678000", "BIGINT NOT NULL");
f.checkScalar("extract(nanosecond from"
+ " interval '2 3:4:5.678' day to second)",
"5678000000", "BIGINT NOT NULL");
