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 8f660aac53 IGNITE-19068 Sql. Support sql engine representation for kv
api timestamp type - Fixes #1905.
8f660aac53 is described below
commit 8f660aac53560c3a89306bce2823a567e4451092
Author: zstan <[email protected]>
AuthorDate: Mon Apr 10 09:27:13 2023 +0300
IGNITE-19068 Sql. Support sql engine representation for kv api timestamp
type - Fixes #1905.
Signed-off-by: zstan <[email protected]>
---
.../ignite/internal/sql/api/ItCommonApiTest.java | 72 ++++++++++++++++
.../internal/sql/engine/ItDataTypesTest.java | 26 +++---
.../sql/types/timestamp/test_timestamp.test | 97 ++++++++++++++++++++++
modules/sql-engine/src/main/codegen/config.fmpp | 2 +-
.../ignite/internal/sql/engine/util/TypeUtils.java | 7 ++
5 files changed, 190 insertions(+), 14 deletions(-)
diff --git
a/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/api/ItCommonApiTest.java
b/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/api/ItCommonApiTest.java
index d5ea1dfe52..cb9046be23 100644
---
a/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/api/ItCommonApiTest.java
+++
b/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/api/ItCommonApiTest.java
@@ -18,20 +18,33 @@
package org.apache.ignite.internal.sql.api;
import static
org.apache.ignite.internal.testframework.IgniteTestUtils.assertThrowsWithCause;
+import static org.apache.ignite.internal.testframework.IgniteTestUtils.await;
import static
org.apache.ignite.internal.testframework.IgniteTestUtils.waitForCondition;
import static org.apache.ignite.lang.ErrorGroups.Sql;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertThrows;
+import java.time.Instant;
+import java.time.LocalDateTime;
import java.util.concurrent.TimeUnit;
+import org.apache.ignite.Ignite;
import org.apache.ignite.internal.app.IgniteImpl;
+import
org.apache.ignite.internal.schema.testutils.SchemaConfigurationConverter;
+import org.apache.ignite.internal.schema.testutils.builder.SchemaBuilders;
+import org.apache.ignite.internal.schema.testutils.definition.ColumnType;
+import
org.apache.ignite.internal.schema.testutils.definition.ColumnType.TemporalColumnType;
+import org.apache.ignite.internal.schema.testutils.definition.TableDefinition;
import org.apache.ignite.internal.sql.engine.ClusterPerClassIntegrationTest;
import org.apache.ignite.internal.sql.engine.SqlQueryProcessor;
import org.apache.ignite.internal.sql.engine.exec.ExecutionCancelledException;
+import org.apache.ignite.internal.table.distributed.TableManager;
import org.apache.ignite.sql.IgniteSql;
import org.apache.ignite.sql.ResultSet;
import org.apache.ignite.sql.Session;
import org.apache.ignite.sql.SqlException;
+import org.apache.ignite.sql.SqlRow;
+import org.apache.ignite.table.Table;
+import org.apache.ignite.table.Tuple;
import org.junit.jupiter.api.Test;
/** Test common SQL API. */
@@ -95,4 +108,63 @@ public class ItCommonApiTest extends
ClusterPerClassIntegrationTest {
// second session could start new query
ses2.execute(null, "SELECT 2 + 2").close();
}
+
+ /** Check timestamp type operations correctness using sql and kv api. */
+ @Test
+ public void checkTimestampOperations() {
+ String kvTblName = "tbl_all_columns_sql";
+ String schemaName = "PUBLIC";
+ String keyCol = "key";
+ int maxTimePrecision = TemporalColumnType.MAX_TIME_PRECISION;
+
+ Ignite node = CLUSTER_NODES.get(0);
+
+ // TODO: https://issues.apache.org/jira/browse/IGNITE-19162 Trim all
less than millisecond information from timestamp
+ //String tsStr = "2023-03-29T08:22:33.005007Z";
+ String tsStr = "2023-03-29T08:22:33.005Z";
+
+ Instant ins = Instant.parse(tsStr);
+
+ sql("CREATE TABLE timestamps(id INTEGER PRIMARY KEY, i TIMESTAMP(9))");
+
+ TableDefinition schTblAllSql = SchemaBuilders.tableBuilder(schemaName,
kvTblName).columns(
+ SchemaBuilders.column(keyCol, ColumnType.INT64).build(),
+ SchemaBuilders.column("time",
ColumnType.time(maxTimePrecision)).asNullable(true).build(),
+ SchemaBuilders.column("timestamp",
ColumnType.timestamp(maxTimePrecision)).asNullable(true).build(),
+ SchemaBuilders.column("datetime",
ColumnType.datetime(maxTimePrecision)).asNullable(true).build()
+ ).withPrimaryKey(keyCol).build();
+
+ await(((TableManager)
node.tables()).createTableAsync(schTblAllSql.name(), tblCh ->
+ SchemaConfigurationConverter.convert(schTblAllSql, tblCh)
+ ));
+
+ Table tbl = node.tables().table(kvTblName);
+
+ Tuple rec = Tuple.create()
+ .set("KEY", 1L)
+ .set("TIMESTAMP", ins)
+ .set("DATETIME", LocalDateTime.of(2023, 1, 18, 18, 9, 29));
+
+ tbl.recordView().insert(null, rec);
+
+ // TODO: https://issues.apache.org/jira/browse/IGNITE-19161 Can`t
insert timestamp representing in ISO_INSTANT format
+ tsStr = tsStr.replace("T", " ").substring(0, tsStr.length() - 1);
+
+ sql("INSERT INTO timestamps VALUES (101, TIMESTAMP '" + tsStr + "')");
+
+ try (Session ses = node.sql().createSession()) {
+ // for projection pop up
+ ResultSet<SqlRow> res = ses.execute(null, "SELECT i, id FROM
timestamps");
+
+ String srtRepr = ins.toString();
+
+ assertEquals(srtRepr.substring(0, srtRepr.length() - 1),
res.next().datetimeValue(0).toString());
+
+ String query = "select \"KEY\", \"TIME\", \"DATETIME\",
\"TIMESTAMP\" from TBL_ALL_COLUMNS_SQL ORDER BY KEY";
+
+ res = ses.execute(null, query);
+
+ assertEquals(ins, res.next().timestampValue(3));
+ }
+ }
}
diff --git
a/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItDataTypesTest.java
b/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItDataTypesTest.java
index b9ad11ace9..dc0194edd2 100644
---
a/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItDataTypesTest.java
+++
b/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItDataTypesTest.java
@@ -53,7 +53,7 @@ public class ItDataTypesTest extends
ClusterPerClassIntegrationTest {
public void testUnicodeStrings() {
sql("CREATE TABLE string_table(key int primary key, val varchar)");
- String[] values = new String[]{"Кирилл", "Müller", "我是谁", "ASCII"};
+ String[] values = {"Кирилл", "Müller", "我是谁", "ASCII"};
int key = 0;
@@ -170,9 +170,9 @@ public class ItDataTypesTest extends
ClusterPerClassIntegrationTest {
*/
@Test
public void testDateTime() {
- assertQuery("select date
'1992-01-19'").returns(sqlDate("1992-01-19")).check();
- assertQuery("select date '1992-01-18' + interval (1)
days").returns(sqlDate("1992-01-19")).check();
- assertQuery("select date '1992-01-18' + interval (24)
hours").returns(sqlDate("1992-01-19")).check();
+ assertQuery("SELECT date
'1992-01-19'").returns(sqlDate("1992-01-19")).check();
+ assertQuery("SELECT date '1992-01-18' + interval (1)
days").returns(sqlDate("1992-01-19")).check();
+ assertQuery("SELECT date '1992-01-18' + interval (24)
hours").returns(sqlDate("1992-01-19")).check();
assertQuery("SELECT timestamp '1992-01-18 02:30:00' + interval (25)
hours")
.returns(sqlDateTime("1992-01-19T03:30:00")).check();
assertQuery("SELECT timestamp '1992-01-18 02:30:00' + interval (23)
hours")
@@ -180,9 +180,9 @@ public class ItDataTypesTest extends
ClusterPerClassIntegrationTest {
assertQuery("SELECT timestamp '1992-01-18 02:30:00' + interval (24)
hours")
.returns(sqlDateTime("1992-01-19T02:30:00.000")).check();
- assertQuery("select date
'1992-03-29'").returns(sqlDate("1992-03-29")).check();
- assertQuery("select date '1992-03-28' + interval (1)
days").returns(sqlDate("1992-03-29")).check();
- assertQuery("select date '1992-03-28' + interval (24)
hours").returns(sqlDate("1992-03-29")).check();
+ assertQuery("SELECT date
'1992-03-29'").returns(sqlDate("1992-03-29")).check();
+ assertQuery("SELECT date '1992-03-28' + interval (1)
days").returns(sqlDate("1992-03-29")).check();
+ assertQuery("SELECT date '1992-03-28' + interval (24)
hours").returns(sqlDate("1992-03-29")).check();
assertQuery("SELECT timestamp '1992-03-28 02:30:00' + interval (25)
hours")
.returns(sqlDateTime("1992-03-29T03:30:00.000")).check();
assertQuery("SELECT timestamp '1992-03-28 02:30:00' + interval (23)
hours")
@@ -190,9 +190,9 @@ public class ItDataTypesTest extends
ClusterPerClassIntegrationTest {
assertQuery("SELECT timestamp '1992-03-28 02:30:00' + interval (24)
hours")
.returns(sqlDateTime("1992-03-29T02:30:00.000")).check();
- assertQuery("select date
'1992-09-27'").returns(sqlDate("1992-09-27")).check();
- assertQuery("select date '1992-09-26' + interval (1)
days").returns(sqlDate("1992-09-27")).check();
- assertQuery("select date '1992-09-26' + interval (24)
hours").returns(sqlDate("1992-09-27")).check();
+ assertQuery("SELECT date
'1992-09-27'").returns(sqlDate("1992-09-27")).check();
+ assertQuery("SELECT date '1992-09-26' + interval (1)
days").returns(sqlDate("1992-09-27")).check();
+ assertQuery("SELECT date '1992-09-26' + interval (24)
hours").returns(sqlDate("1992-09-27")).check();
assertQuery("SELECT timestamp '1992-09-26 02:30:00' + interval (25)
hours")
.returns(sqlDateTime("1992-09-27T03:30:00.000")).check();
assertQuery("SELECT timestamp '1992-09-26 02:30:00' + interval (23)
hours")
@@ -200,9 +200,9 @@ public class ItDataTypesTest extends
ClusterPerClassIntegrationTest {
assertQuery("SELECT timestamp '1992-09-26 02:30:00' + interval (24)
hours")
.returns(sqlDateTime("1992-09-27T02:30:00.000")).check();
- assertQuery("select date
'2021-11-07'").returns(sqlDate("2021-11-07")).check();
- assertQuery("select date '2021-11-06' + interval (1)
days").returns(sqlDate("2021-11-07")).check();
- assertQuery("select date '2021-11-06' + interval (24)
hours").returns(sqlDate("2021-11-07")).check();
+ assertQuery("SELECT date
'2021-11-07'").returns(sqlDate("2021-11-07")).check();
+ assertQuery("SELECT date '2021-11-06' + interval (1)
days").returns(sqlDate("2021-11-07")).check();
+ assertQuery("SELECT date '2021-11-06' + interval (24)
hours").returns(sqlDate("2021-11-07")).check();
assertQuery("SELECT timestamp '2021-11-06 01:30:00' + interval (25)
hours")
.returns(sqlDateTime("2021-11-07T02:30:00.000")).check();
// Check string representation here, since after timestamp calculation
we have '2021-11-07T01:30:00.000-0800'
diff --git
a/modules/runner/src/integrationTest/sql/types/timestamp/test_timestamp.test
b/modules/runner/src/integrationTest/sql/types/timestamp/test_timestamp.test
new file mode 100644
index 0000000000..e0f3278abd
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/types/timestamp/test_timestamp.test
@@ -0,0 +1,97 @@
+# name: test/sql/types/timestamp/test_timestamp.test
+# description: Test TIMESTAMP type
+# group: [timestamp]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE IF NOT EXISTS timestamp (t TIMESTAMP);
+
+statement ok
+INSERT INTO timestamp VALUES ('2008-01-01 00:00:01'), ('2007-01-01 00:00:01'),
('2008-02-01 00:00:01'), ('2008-01-02 00:00:01'), ('2008-01-01 10:00:00'),
('2008-01-01 00:10:00'), ('2008-01-01 00:00:10')
+
+query T
+SELECT timestamp '2017-07-23 13:10:11';
+----
+2017-07-23T13:10:11
+
+# other trailing, preceding, or middle gunk is not accepted
+statement error
+SELECT timestamp ' 2017-07-23 13:10:11 AA';
+
+statement error
+SELECT timestamp 'AA2017-07-23 13:10:11';
+
+statement error
+SELECT timestamp '2017-07-23A13:10:11';
+
+query T
+SELECT t FROM timestamp ORDER BY t LIMIT 1;
+----
+2007-01-01T00:00:01
+
+query T
+SELECT MIN(t) FROM timestamp;
+----
+2007-01-01T00:00:01
+
+query T
+SELECT MAX(t) FROM timestamp;
+----
+2008-02-01T00:00:01
+
+statement error
+SELECT SUM(t) FROM timestamp
+
+statement error
+SELECT AVG(t) FROM timestamp
+
+statement error
+SELECT t+t FROM timestamp
+
+statement error
+SELECT t*t FROM timestamp
+
+statement error
+SELECT t/t FROM timestamp
+
+statement error
+SELECT t%t FROM timestamp
+
+query I
+SELECT YEAR(TIMESTAMP '1992-01-01 01:01:01');
+----
+1992
+
+query I
+SELECT YEAR(TIMESTAMP '1992-01-01 01:01:01'::DATE);
+----
+1992
+
+query T
+SELECT (TIMESTAMP '1992-01-01 01:01:01')::DATE;
+----
+1992-01-01
+
+query T
+SELECT (TIMESTAMP '1992-01-01 01:01:01')::TIME;
+----
+01:01:01
+
+query T
+SELECT t::DATE FROM timestamp WHERE EXTRACT(YEAR from t)=2007 ORDER BY 1
+----
+2007-01-01
+
+query T
+SELECT t::TIME FROM timestamp WHERE EXTRACT(YEAR from t)=2007 ORDER BY 1
+----
+00:00:01
+
+query T
+SELECT (DATE '1992-01-01')::TIMESTAMP;
+----
+1992-01-01T00:00
+
+
diff --git a/modules/sql-engine/src/main/codegen/config.fmpp
b/modules/sql-engine/src/main/codegen/config.fmpp
index 5ae4d19b38..10d3ee9b57 100644
--- a/modules/sql-engine/src/main/codegen/config.fmpp
+++ b/modules/sql-engine/src/main/codegen/config.fmpp
@@ -511,7 +511,7 @@ data: {
"TEMPORARY"
# "THEN"
# "TIME"
-# "TIMESTAMP"
+ "TIMESTAMP"
"TIMEZONE_HOUR"
"TIMEZONE_MINUTE"
"TINYINT"
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 04a85cfe89..8d375052bd 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
@@ -22,6 +22,7 @@ import static
org.apache.ignite.internal.sql.engine.util.Commons.transform;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.time.Duration;
+import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
@@ -264,6 +265,10 @@ public class TypeUtils {
var dt = (LocalDateTime) val;
return TimeUnit.SECONDS.toMillis(dt.toEpochSecond(ZoneOffset.UTC))
+ TimeUnit.NANOSECONDS.toMillis(dt.getNano());
+ } else if (storageType == Instant.class) {
+ var timeStamp = (Instant) val;
+
+ return timeStamp.toEpochMilli();
} else if (storageType == Duration.class) {
return TimeUnit.SECONDS.toMillis(((Duration) val).getSeconds())
+ TimeUnit.NANOSECONDS.toMillis(((Duration)
val).getNano());
@@ -312,6 +317,8 @@ public class TypeUtils {
} else if (storageType == LocalDateTime.class && (val instanceof
Long)) {
return
LocalDateTime.ofEpochSecond(TimeUnit.MILLISECONDS.toSeconds((Long) val),
(int) TimeUnit.MILLISECONDS.toNanos((Long) val % 1000),
ZoneOffset.UTC);
+ } else if (storageType == Instant.class && val instanceof Long) {
+ return Instant.ofEpochMilli((long) val);
} else if (storageType == Duration.class && val instanceof Long) {
return Duration.ofMillis((Long) val);
} else if (storageType == Period.class && val instanceof Integer) {
