You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by zs...@apache.org on 2025/05/30 05:22:29 UTC

(ignite-3) branch main updated: IGNITE-24367 Jdbc. Unable to read interval value (#5838)

This is an automated email from the ASF dual-hosted git repository.

zstan 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 9bd798483f1 IGNITE-24367 Jdbc. Unable to read interval value (#5838)
9bd798483f1 is described below

commit 9bd798483f113682da59b16f5f59146fcbf5b16b
Author: Evgeniy Stanilovskiy <st...@gmail.com>
AuthorDate: Fri May 30 08:22:25 2025 +0300

    IGNITE-24367 Jdbc. Unable to read interval value (#5838)
---
 .../apache/ignite/internal/lang/InternalTuple.java | 18 ++++++++++
 .../apache/ignite/internal/type/NativeTypes.java   | 10 ++++++
 .../ignite/jdbc/ItJdbcResultSetSelfTest.java       | 42 ++++++++++++++++++++++
 .../schema/registry/UpgradingRowAdapter.java       | 30 ++++++++++++++++
 .../internal/sql/engine/ItSqlOperatorsTest.java    |  2 ++
 .../internal/sql/engine/exec/SqlRowHandler.java    | 10 ++++++
 .../sql/engine/util/AbstractProjectedTuple.java    | 12 +++++++
 .../ignite/internal/sql/engine/util/Commons.java   |  2 ++
 .../ignite/internal/sql/engine/util/TypeUtils.java | 21 ++++++-----
 .../internal/sql/engine/util/TypeUtilsTest.java    |  8 ++---
 10 files changed, 140 insertions(+), 15 deletions(-)

diff --git a/modules/core/src/main/java/org/apache/ignite/internal/lang/InternalTuple.java b/modules/core/src/main/java/org/apache/ignite/internal/lang/InternalTuple.java
index f9d9e68f763..5dfda99ab79 100644
--- a/modules/core/src/main/java/org/apache/ignite/internal/lang/InternalTuple.java
+++ b/modules/core/src/main/java/org/apache/ignite/internal/lang/InternalTuple.java
@@ -19,10 +19,12 @@ package org.apache.ignite.internal.lang;
 
 import java.math.BigDecimal;
 import java.nio.ByteBuffer;
+import java.time.Duration;
 import java.time.Instant;
 import java.time.LocalDate;
 import java.time.LocalDateTime;
 import java.time.LocalTime;
+import java.time.Period;
 import java.util.UUID;
 
 /**
@@ -220,6 +222,22 @@ public interface InternalTuple {
      */
     Instant timestampValue(int col);
 
+    /**
+     * Reads value for specified column.
+     *
+     * @param col Column index.
+     * @return Column value.
+     */
+    Period periodValue(int col);
+
+    /**
+     * Reads value for specified column.
+     *
+     * @param col Column index.
+     * @return Column value.
+     */
+    Duration durationValue(int col);
+
     /**
      * Returns the representation of this tuple as a Byte Buffer.
      */
diff --git a/modules/core/src/main/java/org/apache/ignite/internal/type/NativeTypes.java b/modules/core/src/main/java/org/apache/ignite/internal/type/NativeTypes.java
index 093a18b7526..de044a6904c 100644
--- a/modules/core/src/main/java/org/apache/ignite/internal/type/NativeTypes.java
+++ b/modules/core/src/main/java/org/apache/ignite/internal/type/NativeTypes.java
@@ -90,6 +90,16 @@ public class NativeTypes {
     /** Timezone-free three-part value representing a year, month, and day. */
     public static final NativeType DATE = new NativeType(ColumnType.DATE, 3);
 
+    /**
+     * DURATION type.
+     */
+    public static final NativeType DURATION = new NativeType(ColumnType.DURATION, 8);
+
+    /**
+     * PERIOD type.
+     */
+    public static final NativeType PERIOD = new NativeType(ColumnType.PERIOD, 4);
+
     /** Don't allow to create an instance. */
     private NativeTypes() {
     }
diff --git a/modules/jdbc/src/integrationTest/java/org/apache/ignite/jdbc/ItJdbcResultSetSelfTest.java b/modules/jdbc/src/integrationTest/java/org/apache/ignite/jdbc/ItJdbcResultSetSelfTest.java
index 2c5f37e3f94..a9c1c85b1ad 100644
--- a/modules/jdbc/src/integrationTest/java/org/apache/ignite/jdbc/ItJdbcResultSetSelfTest.java
+++ b/modules/jdbc/src/integrationTest/java/org/apache/ignite/jdbc/ItJdbcResultSetSelfTest.java
@@ -36,8 +36,10 @@ import java.sql.SQLException;
 import java.sql.Statement;
 import java.sql.Time;
 import java.sql.Timestamp;
+import java.time.Duration;
 import java.time.Instant;
 import java.time.LocalDate;
+import java.time.Period;
 import java.util.GregorianCalendar;
 import java.util.UUID;
 import org.apache.ignite.internal.tostring.S;
@@ -96,6 +98,46 @@ public class ItJdbcResultSetSelfTest extends AbstractJdbcSelfTest {
         }
     }
 
+    @Test
+    public void testIntervalResult() throws SQLException {
+        assertEquals(Duration.ofDays(4), eval("INTERVAL 4 DAYS"));
+        assertEquals(Duration.ofSeconds(1), eval("INTERVAL 1 SECONDS"));
+        assertEquals(Duration.ofSeconds(-1), eval("INTERVAL -1 SECONDS"));
+        assertEquals(Duration.ofSeconds(123), eval("INTERVAL 123 SECONDS"));
+        assertEquals(Duration.ofSeconds(123), eval("INTERVAL '123' SECONDS(3)"));
+        assertEquals(Duration.ofMinutes(2), eval("INTERVAL 2 MINUTES"));
+        assertEquals(Duration.ofHours(3), eval("INTERVAL 3 HOURS"));
+        assertEquals(Duration.ofDays(4), eval("INTERVAL 4 DAYS"));
+        assertEquals(Period.ofMonths(5), eval("INTERVAL 5 MONTHS"));
+        assertEquals(Period.ofMonths(-5), eval("INTERVAL -5 MONTHS"));
+        assertEquals(Period.ofYears(6), eval("INTERVAL 6 YEARS"));
+        assertEquals(Period.of(1, 2, 0), eval("INTERVAL '1-2' YEAR TO MONTH"));
+        assertEquals(Duration.ofHours(25), eval("INTERVAL '1 1' DAY TO HOUR"));
+        assertEquals(Duration.ofMinutes(62), eval("INTERVAL '1:2' HOUR TO MINUTE"));
+        assertEquals(Duration.ofSeconds(63), eval("INTERVAL '1:3' MINUTE TO SECOND"));
+        assertEquals(Duration.ofSeconds(3723), eval("INTERVAL '1:2:3' HOUR TO SECOND"));
+        assertEquals(Duration.ofMillis(3723456), eval("INTERVAL '0 1:2:3.456' DAY TO SECOND"));
+        assertEquals(Duration.ofSeconds(123), eval("INTERVAL '123' SECONDS"));
+        assertEquals(Duration.ofMillis(123987), eval("INTERVAL '123.987' SECONDS"));
+    }
+
+    @Test
+    public void testTemporalArithmetic() throws SQLException {
+        assertEquals(LocalDate.parse("2021-01-02"), eval("DATE '2021-01-01' + interval (1) days"));
+        assertEquals(Period.ofMonths(2), eval("(DATE '2021-03-01' - DATE '2021-01-01') months"));
+        assertEquals(Duration.ofHours(24), eval("(DATE '2021-03-02' - DATE '2021-03-01') hours"));
+        assertEquals(Duration.ofHours(48), eval("INTERVAL (3) DAYS - INTERVAL (1) DAYS"));
+        assertEquals(Period.ofMonths(1), eval("INTERVAL 5 MONTHS - INTERVAL 4 MONTHS"));
+        assertEquals(Duration.ofSeconds(1), eval("INTERVAL 5 SECONDS - INTERVAL 4 SECONDS"));
+        assertEquals(Duration.ofSeconds(10), eval("INTERVAL 5 SECONDS * 2"));
+    }
+
+    private Object eval(String exp) throws SQLException {
+        ResultSet r = stmt.executeQuery("SELECT " + exp);
+        r.next();
+        return r.getObject(1);
+    }
+
     @Test
     public void testBoolean() throws Exception {
         ResultSet rs = stmt.executeQuery(SQL_SINGLE_RES);
diff --git a/modules/schema/src/main/java/org/apache/ignite/internal/schema/registry/UpgradingRowAdapter.java b/modules/schema/src/main/java/org/apache/ignite/internal/schema/registry/UpgradingRowAdapter.java
index a551ea908f3..6845752f644 100644
--- a/modules/schema/src/main/java/org/apache/ignite/internal/schema/registry/UpgradingRowAdapter.java
+++ b/modules/schema/src/main/java/org/apache/ignite/internal/schema/registry/UpgradingRowAdapter.java
@@ -22,10 +22,12 @@ import static org.apache.ignite.internal.lang.IgniteStringFormatter.format;
 
 import java.math.BigDecimal;
 import java.nio.ByteBuffer;
+import java.time.Duration;
 import java.time.Instant;
 import java.time.LocalDate;
 import java.time.LocalDateTime;
 import java.time.LocalTime;
+import java.time.Period;
 import java.util.UUID;
 import org.apache.ignite.internal.binarytuple.BinaryTupleBuilder;
 import org.apache.ignite.internal.schema.BinaryRowConverter;
@@ -422,6 +424,34 @@ public class UpgradingRowAdapter implements Row {
         return mappedId < 0 ? (Instant) column.defaultValue() : row.timestampValue(mappedId);
     }
 
+    /** {@inheritDoc} */
+    @Override
+    public Period periodValue(int colIdx) throws InvalidTypeException {
+        int mappedId = mapColumn(colIdx);
+
+        Column column = mappedId < 0 ? mapper.mappedColumn(colIdx) : row.schema().column(mappedId);
+
+        if (ColumnType.PERIOD != column.type().spec()) {
+            throw new SchemaException("Type conversion is not supported yet.");
+        }
+
+        return mappedId < 0 ? (Period) column.defaultValue() : row.periodValue(mappedId);
+    }
+
+    /** {@inheritDoc} */
+    @Override
+    public Duration durationValue(int colIdx) throws InvalidTypeException {
+        int mappedId = mapColumn(colIdx);
+
+        Column column = mappedId < 0 ? mapper.mappedColumn(colIdx) : row.schema().column(mappedId);
+
+        if (ColumnType.DURATION != column.type().spec()) {
+            throw new SchemaException("Type conversion is not supported yet.");
+        }
+
+        return mappedId < 0 ? (Duration) column.defaultValue() : row.durationValue(mappedId);
+    }
+
     @Override
     public boolean hasNullValue(int colIdx) {
         int mappedId = mapColumn(colIdx);
diff --git a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSqlOperatorsTest.java b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSqlOperatorsTest.java
index 9a8a91ebef3..c598efe1ccd 100644
--- a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSqlOperatorsTest.java
+++ b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSqlOperatorsTest.java
@@ -24,6 +24,7 @@ import com.fasterxml.jackson.databind.ObjectMapper;
 import java.io.IOException;
 import java.math.BigDecimal;
 import java.math.RoundingMode;
+import java.time.Duration;
 import java.time.LocalDate;
 import java.time.LocalDateTime;
 import java.time.Period;
@@ -217,6 +218,7 @@ public class ItSqlOperatorsTest extends BaseSqlIntegrationTest {
     public void testDateAndTime() {
         assertExpression("DATE '2021-01-01' + interval (1) days").returns(LocalDate.parse("2021-01-02")).check();
         assertExpression("(DATE '2021-03-01' - DATE '2021-01-01') months").returns(Period.ofMonths(2)).check();
+        assertExpression("(DATE '2021-03-02' - DATE '2021-03-01') hours").returns(Duration.ofHours(24)).check();
         assertExpression("EXTRACT(DAY FROM DATE '2021-01-15')").returns(15L).check();
         assertExpression("FLOOR(DATE '2021-01-15' TO MONTH)").returns(LocalDate.parse("2021-01-01")).check();
         assertExpression("CEIL(DATE '2021-01-15' TO MONTH)").returns(LocalDate.parse("2021-02-01")).check();
diff --git a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/SqlRowHandler.java b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/SqlRowHandler.java
index b8a18b3dace..c37540e83d5 100644
--- a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/SqlRowHandler.java
+++ b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/SqlRowHandler.java
@@ -21,10 +21,12 @@ import static org.apache.ignite.internal.lang.IgniteStringFormatter.format;
 import static org.apache.ignite.internal.sql.engine.util.Commons.readValue;
 
 import java.math.BigDecimal;
+import java.time.Duration;
 import java.time.Instant;
 import java.time.LocalDate;
 import java.time.LocalDateTime;
 import java.time.LocalTime;
+import java.time.Period;
 import java.util.UUID;
 import org.apache.calcite.avatica.util.ByteString;
 import org.apache.ignite.internal.binarytuple.BinaryTupleBuilder;
@@ -325,6 +327,14 @@ public class SqlRowHandler implements RowHandler<RowWrapper> {
                     builder.appendTimestampNotNull((Instant) value);
                     break;
 
+                case DURATION:
+                    builder.appendDuration((Duration) value);
+                    break;
+
+                case PERIOD:
+                    builder.appendPeriod((Period) value);
+                    break;
+
                 default:
                     throw new UnsupportedOperationException("Unknown type " + nativeType);
             }
diff --git a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/AbstractProjectedTuple.java b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/AbstractProjectedTuple.java
index e46690c3fd8..9dee65f465e 100644
--- a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/AbstractProjectedTuple.java
+++ b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/AbstractProjectedTuple.java
@@ -19,10 +19,12 @@ package org.apache.ignite.internal.sql.engine.util;
 
 import java.math.BigDecimal;
 import java.nio.ByteBuffer;
+import java.time.Duration;
 import java.time.Instant;
 import java.time.LocalDate;
 import java.time.LocalDateTime;
 import java.time.LocalTime;
+import java.time.Period;
 import java.util.UUID;
 import org.apache.ignite.internal.lang.InternalTuple;
 
@@ -186,6 +188,16 @@ abstract class AbstractProjectedTuple implements InternalTuple {
         return delegate.timestampValue(projection[col]);
     }
 
+    @Override
+    public Period periodValue(int col) {
+        return delegate.periodValue(projection[col]);
+    }
+
+    @Override
+    public Duration durationValue(int col) {
+        return delegate.durationValue(projection[col]);
+    }
+
     @Override
     public ByteBuffer byteBuffer() {
         normalizeIfNeeded();
diff --git a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/Commons.java b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/Commons.java
index 9a36582d3ea..87abc53e13f 100644
--- a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/Commons.java
+++ b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/Commons.java
@@ -521,6 +521,8 @@ public final class Commons {
             case TIME: return tuple.timeValue(fieldIndex);
             case DATETIME: return tuple.dateTimeValue(fieldIndex);
             case TIMESTAMP: return tuple.timestampValue(fieldIndex);
+            case PERIOD: return tuple.periodValue(fieldIndex);
+            case DURATION: return tuple.durationValue(fieldIndex);
             default: throw new InvalidTypeException("Unknown element type: " + nativeType);
         }
     }
diff --git a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/TypeUtils.java b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/TypeUtils.java
index 460a170a887..65312924553 100644
--- a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/TypeUtils.java
+++ b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/TypeUtils.java
@@ -413,15 +413,14 @@ public class TypeUtils {
                 return Instant.ofEpochMilli((Long) val);
             case BOOLEAN:
                 return val;
-            // TODO https://issues.apache.org/jira/browse/IGNITE-23295 Support native types for DURATION and PERIOD
-            // case DURATION: {
-            //     assert val instanceof Long;
-            //    return Duration.ofMillis((Long) val);
-            // }
-            // case PREIOD: {
-            //     assert val instanceof Integer;
-            //     return Period.of((Integer) val / 12, (Integer) val % 12, 0);
-            //     }
+            case DURATION: {
+                assert val instanceof Long;
+                return Duration.ofMillis((Long) val);
+            }
+            case PERIOD: {
+                assert val instanceof Integer;
+                return Period.of((Integer) val / 12, (Integer) val % 12, 0);
+            }
             default: {
                 return SafeCustomTypeInternalConversion.INSTANCE.tryConvertFromInternal(val, spec);
             }
@@ -804,10 +803,10 @@ public class TypeUtils {
 
             if (yearMonth) {
                 // YEAR MONTH interval is stored as number of days in ints.
-                return RowSchemaTypes.nativeTypeWithNullability(NativeTypes.INT32, nullable);
+                return RowSchemaTypes.nativeTypeWithNullability(NativeTypes.PERIOD, nullable);
             } else {
                 // DAY interval is stored as time as long.
-                return RowSchemaTypes.nativeTypeWithNullability(NativeTypes.INT64, nullable);
+                return RowSchemaTypes.nativeTypeWithNullability(NativeTypes.DURATION, nullable);
             }
         } else if (SqlTypeUtil.isRow(type)) {
             List<TypeSpec> fields = new ArrayList<>();
diff --git a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/util/TypeUtilsTest.java b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/util/TypeUtilsTest.java
index 3be12b1300d..41c3889f47c 100644
--- a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/util/TypeUtilsTest.java
+++ b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/util/TypeUtilsTest.java
@@ -441,16 +441,16 @@ public class TypeUtilsTest extends BaseIgniteAbstractTest {
         testCaseList.add(new RelToExecTestCase(SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE, 4,
                 RowSchemaTypes.nativeType(NativeTypes.timestamp(4))));
 
-        // Year intervals are stored as days (int)
         for (SqlTypeName interval : SqlTypeName.YEAR_INTERVAL_TYPES) {
             SqlIntervalQualifier yearMonth = new SqlIntervalQualifier(interval.getStartUnit(), interval.getEndUnit(), SqlParserPos.ZERO);
-            testCaseList.add(new RelToExecTestCase(TYPE_FACTORY.createSqlIntervalType(yearMonth), INT32));
+            testCaseList.add(new RelToExecTestCase(TYPE_FACTORY.createSqlIntervalType(yearMonth),
+                    RowSchemaTypes.nativeType(NativeTypes.PERIOD)));
         }
 
-        // Day intervals are stored as nanoseconds (long)
         for (SqlTypeName interval : SqlTypeName.DAY_INTERVAL_TYPES) {
             SqlIntervalQualifier dayTime = new SqlIntervalQualifier(interval.getStartUnit(), interval.getEndUnit(), SqlParserPos.ZERO);
-            testCaseList.add(new RelToExecTestCase(TYPE_FACTORY.createSqlIntervalType(dayTime), INT64));
+            testCaseList.add(new RelToExecTestCase(TYPE_FACTORY.createSqlIntervalType(dayTime),
+                    RowSchemaTypes.nativeType(NativeTypes.DURATION)));
         }
 
         testCaseList.add(new RelToExecTestCase(SqlTypeName.UUID, UUID));