You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by pp...@apache.org on 2025/05/06 16:35:12 UTC
(ignite-3) branch main updated: IGNITE-25154 Sql. Fixed TIMESTAMP_WITH_LOCAL_TIME_ZONE interval arithmetic (#5734)
This is an automated email from the ASF dual-hosted git repository.
ppa pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new 61f747c1890 IGNITE-25154 Sql. Fixed TIMESTAMP_WITH_LOCAL_TIME_ZONE interval arithmetic (#5734)
61f747c1890 is described below
commit 61f747c1890f975b0c17f738862b27565212a5a5
Author: Pavel Pereslegin <xx...@gmail.com>
AuthorDate: Tue May 6 19:35:07 2025 +0300
IGNITE-25154 Sql. Fixed TIMESTAMP_WITH_LOCAL_TIME_ZONE interval arithmetic (#5734)
---
.../ignite/internal/sql/engine/ItIntervalTest.java | 47 +++++----------
.../types/timestamp/test_incorrect_timestamp.test | 15 +++++
.../timestamp/test_incorrect_timestamp_ltz.test | 19 +++++--
.../internal/sql/engine/exec/exp/RexImpTable.java | 5 +-
.../sql/engine/prepare/IgniteConvertletTable.java | 66 ++++++++++++++++++++++
5 files changed, 113 insertions(+), 39 deletions(-)
diff --git a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItIntervalTest.java b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItIntervalTest.java
index 7a05fdf4239..4e20ff284ec 100644
--- a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItIntervalTest.java
+++ b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItIntervalTest.java
@@ -31,6 +31,7 @@ import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.Period;
import java.time.ZoneId;
+import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoField;
import java.time.temporal.ChronoUnit;
@@ -59,7 +60,6 @@ import org.apache.ignite.internal.sql.engine.sql.IgniteSqlParser;
import org.apache.ignite.internal.sql.engine.util.Commons;
import org.apache.ignite.lang.ErrorGroups.Sql;
import org.apache.ignite.lang.IgniteException;
-import org.junit.jupiter.api.Assumptions;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Test;
@@ -317,10 +317,6 @@ public class ItIntervalTest extends BaseSqlIntegrationTest {
Interval intervalVal = testCase.intervalVal;
SqlTypeName typeName = testCase.type.getSqlTypeName();
- // TODO Remove after https://issues.apache.org/jira/browse/IGNITE-21557
- Assumptions.assumeFalse(typeName == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE
- && (intervalVal.hasType(TimeUnit.MONTH) || intervalVal.hasType(TimeUnit.YEAR)));
-
String query;
if (intervalVal.sign() > 0) {
query = format("SELECT {} '{}' + {}", typeName.getSpaceName(), testCase.sqlDateLiteral(), intervalVal.toLiteral());
@@ -338,11 +334,6 @@ public class ItIntervalTest extends BaseSqlIntegrationTest {
@MethodSource("dateTimeIntervalTestCases")
public void testDateTimeDynamicParamIntervalArithmetic(DateTimeIntervalBasicTestCase testCase) {
Interval intervalVal = testCase.intervalVal;
- SqlTypeName typeName = testCase.type.getSqlTypeName();
-
- // TODO Remove after https://issues.apache.org/jira/browse/IGNITE-21557
- Assumptions.assumeFalse(typeName == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE
- && (intervalVal.hasType(TimeUnit.MONTH) || intervalVal.hasType(TimeUnit.YEAR)));
String query;
if (intervalVal.sign() > 0) {
@@ -364,10 +355,6 @@ public class ItIntervalTest extends BaseSqlIntegrationTest {
Interval intervalVal = testCase.intervalVal;
SqlTypeName typeName = testCase.type.getSqlTypeName();
- // TODO Remove after https://issues.apache.org/jira/browse/IGNITE-21557
- Assumptions.assumeFalse(typeName == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE
- && (intervalVal.hasType(TimeUnit.MONTH) || intervalVal.hasType(TimeUnit.YEAR)));
-
String colName = typeName.getName() + testCase.type.getPrecision() + "_col";
String dml = format("UPDATE datetime_cols SET {} = {} '{}' WHERE id = 1",
@@ -537,11 +524,7 @@ public class ItIntervalTest extends BaseSqlIntegrationTest {
timestampChecker.accept("SELECT {} '2021-01-01 00:00:00' + INTERVAL '1.123' SECOND", "2021-01-01T00:00:01.123");
timestampChecker.accept("SELECT {} '2021-01-01 00:00:00.123' + INTERVAL '1.123' SECOND", "2021-01-01T00:00:01.246");
timestampChecker.accept("SELECT {} '2021-01-01 00:00:00' + INTERVAL '1 1:1:1.123' DAY TO SECOND", "2021-01-02T01:01:01.123");
-
- // TODO Enable this case after https://issues.apache.org/jira/browse/IGNITE-21557
- if (sqlTypeName != SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE) {
- timestampChecker.accept("SELECT {} '2021-01-01 01:01:01.123' + INTERVAL '1-1' YEAR TO MONTH", "2022-02-01T01:01:01.123");
- }
+ timestampChecker.accept("SELECT {} '2021-01-01 01:01:01.123' + INTERVAL '1-1' YEAR TO MONTH", "2022-02-01T01:01:01.123");
}
@Test
@@ -904,34 +887,32 @@ public class ItIntervalTest extends BaseSqlIntegrationTest {
}
private static class SqlTimestampLtzIntervalIntervalTestCase extends DateTimeIntervalBasicTestCase {
-
- private final Instant timestampLtz;
+ final Instant initial;
+ final Instant result;
private SqlTimestampLtzIntervalIntervalTestCase(RelDataType type, Interval value) {
super(type, value);
int precision = type.getPrecision();
- Instant instant = testLocalDate.atZone(TIME_ZONE_ID).toInstant();
- long nanos = adjustNanos(instant.getNano(), precision, 3);
+ long nanos = adjustNanos(testLocalDate.getNano(), precision, 3);
+
+ // Instant only supports Month in jdk21+, so we do all calculations using
+ // LocalDateTime and adjust the result according to the required time zone.
+ LocalDateTime timestamp = testLocalDate.with(ChronoField.NANO_OF_SECOND, nanos);
- timestampLtz = instant.with(ChronoField.NANO_OF_SECOND, nanos);
+ initial = timestamp.atZone(TIME_ZONE_ID).toInstant();
+ result = LocalDateTime.ofInstant(initial, ZoneOffset.UTC)
+ .plus(intervalVal.toTemporalAmount()).toInstant(ZoneOffset.UTC);
}
@Override
public Temporal expected() {
- TemporalAmount temporalAmount = intervalVal.toTemporalAmount();
- // Instant does not support year/month and calcite's runtime converts each interval unit into milliseconds.
- if (temporalAmount instanceof Period) {
- Period period = (Period) temporalAmount;
- int days = period.getYears() * 365 + period.getMonths() * 31 + period.getDays();
- temporalAmount = Duration.ofDays(days);
- }
- return timestampLtz.plus(temporalAmount);
+ return result;
}
@Override
public Temporal dateTimeValue() {
- return timestampLtz;
+ return initial;
}
}
diff --git a/modules/sql-engine/src/integrationTest/sql/group1/types/timestamp/test_incorrect_timestamp.test b/modules/sql-engine/src/integrationTest/sql/group1/types/timestamp/test_incorrect_timestamp.test
index f07ef39428b..f4ce68f0b4e 100644
--- a/modules/sql-engine/src/integrationTest/sql/group1/types/timestamp/test_incorrect_timestamp.test
+++ b/modules/sql-engine/src/integrationTest/sql/group1/types/timestamp/test_incorrect_timestamp.test
@@ -153,5 +153,20 @@ INSERT INTO timestamp_t VALUES (TIMESTAMP '9999-12-31 06:00:00')
statement error: Invalid default value for column 'TS'
CREATE TABLE default_overflow(ts TIMESTAMP DEFAULT '9999-12-31 06:00:00');
+statement error: TIMESTAMP out of range
+INSERT INTO timestamp_t VALUES (TIMESTAMP '2000-01-01 00:00:00' + INTERVAL 2922000 DAYS)
+
+statement error: TIMESTAMP out of range
+INSERT INTO timestamp_t VALUES (TIMESTAMP '2000-01-01 00:00:00' - INTERVAL 730500 DAYS)
+
+statement error: TIMESTAMP out of range
+INSERT INTO timestamp_t VALUES (TIMESTAMP '2000-01-01 00:00:00' + INTERVAL 70128000 HOURS)
+
+statement error: TIMESTAMP out of range
+INSERT INTO timestamp_t VALUES (TIMESTAMP '2000-01-01 00:00:00' - INTERVAL 17532000 HOURS)
+
statement error: TIMESTAMP out of range
INSERT INTO timestamp_t VALUES (LOCALTIMESTAMP + INTERVAL 7999 YEARS)
+
+statement error: TIMESTAMP out of range
+INSERT INTO timestamp_t VALUES (LOCALTIMESTAMP - INTERVAL 7999 YEARS)
diff --git a/modules/sql-engine/src/integrationTest/sql/group1/types/timestamp/test_incorrect_timestamp_ltz.test b/modules/sql-engine/src/integrationTest/sql/group1/types/timestamp/test_incorrect_timestamp_ltz.test
index cc518e1c0a5..1eae211d823 100644
--- a/modules/sql-engine/src/integrationTest/sql/group1/types/timestamp/test_incorrect_timestamp_ltz.test
+++ b/modules/sql-engine/src/integrationTest/sql/group1/types/timestamp/test_incorrect_timestamp_ltz.test
@@ -135,8 +135,6 @@ skipif ignite3
statement error: Invalid default value for column 'TS'
CREATE TABLE default_overflow(ts TIMESTAMP WITH LOCAL TIME ZONE DEFAULT '0001-01-01 17:59:59.999999999');
-# TODO https://issues.apache.org/jira/browse/IGNITE-21557
-skipif ignite3
statement error: TIMESTAMP_WITH_LOCAL_TIME_ZONE out of range
INSERT INTO timestamp_t VALUES (CURRENT_TIMESTAMP - INTERVAL 7999 YEARS)
@@ -153,12 +151,25 @@ INSERT INTO timestamp_t VALUES ('9999-12-31 06:00:00'::TIMESTAMP WITH LOCAL TIME
statement error: TIMESTAMP_WITH_LOCAL_TIME_ZONE out of range
INSERT INTO timestamp_t VALUES (TIMESTAMP WITH LOCAL TIME ZONE '9999-12-31 06:00:00')
+statement error: TIMESTAMP_WITH_LOCAL_TIME_ZONE out of range
+INSERT INTO timestamp_t VALUES (TIMESTAMP WITH LOCAL TIME ZONE '2000-01-01 00:00:00' + INTERVAL 2922000 DAYS)
+
+statement error: TIMESTAMP_WITH_LOCAL_TIME_ZONE out of range
+INSERT INTO timestamp_t VALUES (TIMESTAMP WITH LOCAL TIME ZONE '2000-01-01 00:00:00' - INTERVAL 730500 DAYS)
+
+statement error: TIMESTAMP_WITH_LOCAL_TIME_ZONE out of range
+INSERT INTO timestamp_t VALUES (TIMESTAMP WITH LOCAL TIME ZONE '2000-01-01 00:00:00' + INTERVAL 70128000 HOURS)
+
+statement error: TIMESTAMP_WITH_LOCAL_TIME_ZONE out of range
+INSERT INTO timestamp_t VALUES (TIMESTAMP WITH LOCAL TIME ZONE '2000-01-01 00:00:00' - INTERVAL 17532000 HOURS)
+
# TODO https://issues.apache.org/jira/browse/IGNITE-17376
skipif ignite3
statement error: Invalid default value for column 'TS'
CREATE TABLE default_overflow(ts TIMESTAMP WITH LOCAL TIME ZONE DEFAULT '9999-12-31 06:00:00');
-# TODO https://issues.apache.org/jira/browse/IGNITE-21557
-skipif ignite3
statement error: TIMESTAMP_WITH_LOCAL_TIME_ZONE out of range
INSERT INTO timestamp_t VALUES (CURRENT_TIMESTAMP + INTERVAL 7999 YEARS)
+
+statement error: TIMESTAMP_WITH_LOCAL_TIME_ZONE out of range
+INSERT INTO timestamp_t VALUES (CURRENT_TIMESTAMP - INTERVAL 7999 YEARS)
diff --git a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/RexImpTable.java b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/RexImpTable.java
index cb14dfb758e..86016e4a416 100644
--- a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/RexImpTable.java
+++ b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/RexImpTable.java
@@ -403,9 +403,9 @@ import static org.apache.calcite.sql.fun.SqlStdOperatorTable.UNARY_PLUS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.UPPER;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.USER;
import static org.apache.calcite.util.ReflectUtil.isStatic;
+import static org.apache.ignite.internal.sql.engine.sql.fun.IgniteSqlOperatorTable.CURRENT_TIMESTAMP;
import static org.apache.ignite.internal.sql.engine.sql.fun.IgniteSqlOperatorTable.SUBSTR;
import static org.apache.ignite.internal.sql.engine.sql.fun.IgniteSqlOperatorTable.TYPEOF;
-import static org.apache.ignite.internal.sql.engine.sql.fun.IgniteSqlOperatorTable.CURRENT_TIMESTAMP;
import static org.apache.ignite.internal.sql.engine.util.IgniteMethod.GREATEST2;
import static org.apache.ignite.internal.sql.engine.util.IgniteMethod.LEAST2;
import static org.apache.ignite.internal.sql.engine.util.IgniteMethod.LENGTH;
@@ -3399,7 +3399,7 @@ public class RexImpTable {
}
return IgniteExpressions.makeBinary(expressionType,
- argValueList.get(0), argValueList.get(1));
+ argValueList.get(0), argValueList.get(1));
}
/** Returns whether any of a call's operands have ANY type. */
@@ -4130,6 +4130,7 @@ public class RexImpTable {
default:
final BuiltInMethod method =
operand0.getType().getSqlTypeName() == SqlTypeName.TIMESTAMP
+ || operand0.getType().getSqlTypeName() == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE
? BuiltInMethod.ADD_MONTHS
: BuiltInMethod.ADD_MONTHS_INT;
return IgniteExpressions.addBoundsCheckIfNeeded(typeName, Expressions.call(method.method, trop0, trop1));
diff --git a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/IgniteConvertletTable.java b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/IgniteConvertletTable.java
index 80797818dfe..849b8bd49c4 100644
--- a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/IgniteConvertletTable.java
+++ b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/IgniteConvertletTable.java
@@ -17,6 +17,7 @@
package org.apache.ignite.internal.sql.engine.prepare;
+import com.google.common.collect.ImmutableList;
import java.math.BigDecimal;
import java.util.List;
import org.apache.calcite.avatica.util.DateTimeUtils;
@@ -50,6 +51,10 @@ public class IgniteConvertletTable extends ReflectiveConvertletTable {
// Replace Calcite's alias so it can pass "call to wrong operator" precondition in ReflectiveConvertletTable.addAlias
addAlias(IgniteSqlOperatorTable.PERCENT_REMAINDER, SqlStdOperatorTable.MOD);
+
+ // Replace plus/minus implementors, because Calcite missed the TIMESTAMP WITH LOCAL TIME ZONE data type.
+ registerOp(SqlStdOperatorTable.PLUS, this::convertPlus);
+ registerOp(SqlStdOperatorTable.MINUS, this::convertMinus);
}
/** {@inheritDoc} */
@@ -128,4 +133,65 @@ public class IgniteConvertletTable extends ReflectiveConvertletTable {
return builder.ensureType(type, builder.decodeIntervalOrDecimal(exp), false);
}
}
+
+ /** Convertlet that handles the {@link SqlTypeName#TIMESTAMP_WITH_LOCAL_TIME_ZONE} data type. */
+ private RexNode convertPlus(
+ IgniteConvertletTable this,
+ SqlRexContext cx, SqlCall call) {
+ final RexNode rex = StandardConvertletTable.INSTANCE.convertCall(cx, call);
+ switch (rex.getType().getSqlTypeName()) {
+ case DATE:
+ case TIME:
+ case TIMESTAMP:
+ case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
+ // Use special "+" operator for datetime + interval.
+ // Re-order operands, if necessary, so that interval is second.
+ final RexBuilder rexBuilder = cx.getRexBuilder();
+ List<RexNode> operands = ((RexCall) rex).getOperands();
+ if (operands.size() == 2) {
+ final SqlTypeName sqlTypeName = operands.get(0).getType().getSqlTypeName();
+ switch (sqlTypeName) {
+ 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:
+ operands = ImmutableList.of(operands.get(1), operands.get(0));
+ break;
+ default:
+ break;
+ }
+ }
+ return rexBuilder.makeCall(call.getParserPosition(), rex.getType(),
+ SqlStdOperatorTable.DATETIME_PLUS, operands);
+ default:
+ return rex;
+ }
+ }
+
+ /** Convertlet that handles the {@link SqlTypeName#TIMESTAMP_WITH_LOCAL_TIME_ZONE} data type. */
+ private RexNode convertMinus(
+ IgniteConvertletTable this,
+ SqlRexContext cx, SqlCall call) {
+ RexCall e =
+ (RexCall) StandardConvertletTable.INSTANCE.convertCall(cx, call);
+ switch (e.getOperands().get(0).getType().getSqlTypeName()) {
+ case DATE:
+ case TIME:
+ case TIMESTAMP:
+ case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
+ return StandardConvertletTable.INSTANCE.convertDatetimeMinus(cx, SqlStdOperatorTable.MINUS_DATE,
+ call);
+ default:
+ return e;
+ }
+ }
}