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 d9480d7e38 IGNITE-21567 Sql. Fixed precision when converting from
TIME/TIMESTAMP to VARCHAR (#3513)
d9480d7e38 is described below
commit d9480d7e38d62b1234ad2fbf1c7def596c0f9d15
Author: Pavel Pereslegin <xxt...@gmail.com>
AuthorDate: Wed Apr 3 12:44:41 2024 +0300
IGNITE-21567 Sql. Fixed precision when converting from TIME/TIMESTAMP to
VARCHAR (#3513)
---
.../internal/sql/engine/ItDataTypesTest.java | 4 +-
.../sql/types/date/date_parsing.test | 56 ++++++++++++++++++++++
.../sql/types/date/date_parsing.test_ignore | 1 +
.../integrationTest/sql/types/date/test_date.test | 38 +++++++++++++++
.../sql/types/date/test_date.test_ignore | 2 +
.../time/{test_time.test_ignore => test_time.test} | 23 +++++----
.../sql/types/time/time_parsing.test | 29 +++++++++++
.../sql/types/time/time_parsing.test_ignore | 2 +-
.../sql/types/timestamp/bc_timestamp.test_ignore | 3 +-
.../sql/types/timestamp/test_timestamp.test | 25 ++++++++++
.../sql/engine/exec/exp/RexToLixTranslator.java | 16 ++++---
.../internal/sql/engine/util/IgniteMethod.java | 11 +++++
12 files changed, 189 insertions(+), 21 deletions(-)
diff --git
a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItDataTypesTest.java
b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItDataTypesTest.java
index b8eccea209..b968695bf0 100644
---
a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItDataTypesTest.java
+++
b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItDataTypesTest.java
@@ -31,7 +31,6 @@ import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneId;
-import java.time.temporal.ChronoUnit;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;
@@ -263,8 +262,7 @@ public class ItDataTypesTest extends BaseSqlIntegrationTest
{
.check();
assertQuery("SELECT ts, ts_tz FROM timestamps WHERE id=?")
.withParam(3)
- // TODO Conversion loses precision
https://issues.apache.org/jira/browse/IGNITE-21567
- .returns(localDateTime,
instant.truncatedTo(ChronoUnit.SECONDS))
+ .returns(localDateTime, instant)
.check();
}
diff --git
a/modules/sql-engine/src/integrationTest/sql/types/date/date_parsing.test
b/modules/sql-engine/src/integrationTest/sql/types/date/date_parsing.test
new file mode 100644
index 0000000000..0ccf0a7e91
--- /dev/null
+++ b/modules/sql-engine/src/integrationTest/sql/types/date/date_parsing.test
@@ -0,0 +1,56 @@
+# name: test/sql/types/date/date_parsing.test
+# description: Test date parsing
+# group: [date]
+
+statement ok
+PRAGMA enable_verification
+
+query I
+SELECT '1992-01-01'::DATE::VARCHAR = '1992-01-01'
+----
+1
+
+query I
+SELECT '1992-09-20'::DATE::VARCHAR = '1992-09-20'
+----
+1
+
+query I
+SELECT '1992-02-29'::DATE::VARCHAR = '1992-02-29'
+----
+1
+
+query I
+SELECT '3600-02-29'::DATE::VARCHAR = '3600-02-29'
+----
+1
+
+query I
+SELECT '0030-01-01'::DATE::VARCHAR = '0030-01-01'
+----
+1
+
+query I
+SELECT '1969-01-01'::DATE::VARCHAR = '1969-01-01'
+----
+1
+
+query I
+SELECT '1970-01-01'::DATE::VARCHAR = '1970-01-01'
+----
+1
+
+query I
+SELECT '2369-01-01'::DATE::VARCHAR = '2369-01-01'
+----
+1
+
+query I
+SELECT '2370-01-01'::DATE::VARCHAR = '2370-01-01'
+----
+1
+
+query I
+SELECT '2371-01-01'::DATE::VARCHAR = '2371-01-01'
+----
+1
diff --git
a/modules/sql-engine/src/integrationTest/sql/types/date/date_parsing.test_ignore
b/modules/sql-engine/src/integrationTest/sql/types/date/date_parsing.test_ignore
index 6f45c20952..70b5f37fed 100644
---
a/modules/sql-engine/src/integrationTest/sql/types/date/date_parsing.test_ignore
+++
b/modules/sql-engine/src/integrationTest/sql/types/date/date_parsing.test_ignore
@@ -1,6 +1,7 @@
# name: test/sql/types/date/date_parsing.test
# description: Test date parsing
# group: [date]
+# Ignore https://issues.apache.org/jira/browse/IGNITE-14602
# Ignore https://issues.apache.org/jira/browse/IGNITE-15619
statement ok
diff --git
a/modules/sql-engine/src/integrationTest/sql/types/date/test_date.test
b/modules/sql-engine/src/integrationTest/sql/types/date/test_date.test
new file mode 100644
index 0000000000..4aef7b6dea
--- /dev/null
+++ b/modules/sql-engine/src/integrationTest/sql/types/date/test_date.test
@@ -0,0 +1,38 @@
+# name: test/sql/types/date/test_date.test
+# description: Test basic DATE functionality
+# group: [date]
+
+statement ok
+PRAGMA enable_verification
+
+# create and insert into table
+statement ok
+CREATE TABLE dates(i DATE)
+
+statement ok
+INSERT INTO dates VALUES ('1993-08-14')
+
+# TODO https://issues.apache.org/jira/browse/IGNITE-15123 Multi-tuple insert
fails on validation
+statement ok
+INSERT INTO dates VALUES (NULL)
+
+# check that we can select dates
+query T rowsort
+SELECT * FROM dates
+----
+1993-08-14
+NULL
+
+# YEAR function
+query I rowsort
+SELECT year(i) FROM dates
+----
+1993
+NULL
+
+# check that we can convert dates to string
+query T rowsort
+SELECT cast(i AS VARCHAR) FROM dates
+----
+1993-08-14
+NULL
diff --git
a/modules/sql-engine/src/integrationTest/sql/types/date/test_date.test_ignore
b/modules/sql-engine/src/integrationTest/sql/types/date/test_date.test_ignore
index bf207cff48..8d21b405b8 100644
---
a/modules/sql-engine/src/integrationTest/sql/types/date/test_date.test_ignore
+++
b/modules/sql-engine/src/integrationTest/sql/types/date/test_date.test_ignore
@@ -2,6 +2,8 @@
# description: Test basic DATE functionality
# group: [date]
# Ignore https://issues.apache.org/jira/browse/IGNITE-15619
+# Note: these tests fail because AI3 does not allow <datetime> +/- <number>
arithmetic.
+# According to the standard only <datetime> +/- <interval> arithmetic is
allowed.
statement ok
PRAGMA enable_verification
diff --git
a/modules/sql-engine/src/integrationTest/sql/types/time/test_time.test_ignore
b/modules/sql-engine/src/integrationTest/sql/types/time/test_time.test
similarity index 58%
rename from
modules/sql-engine/src/integrationTest/sql/types/time/test_time.test_ignore
rename to modules/sql-engine/src/integrationTest/sql/types/time/test_time.test
index a1feccfb9f..a9194c0036 100644
---
a/modules/sql-engine/src/integrationTest/sql/types/time/test_time.test_ignore
+++ b/modules/sql-engine/src/integrationTest/sql/types/time/test_time.test
@@ -1,32 +1,35 @@
# name: test/sql/types/time/test_time.test
# description: Test basic TIME functionality
# group: [time]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15619
statement ok
PRAGMA enable_verification
statement ok
-CREATE TABLE times(i TIME)
+CREATE TABLE times(i TIME(3))
statement ok
-INSERT INTO times VALUES ('00:01:20'), ('20:08:10.998'), ('20:08:10.33'),
('20:08:10.001'), (NULL)
+INSERT INTO times VALUES ('00:01:20'), ('20:08:10.998'), ('20:08:10.33'),
('20:08:10.001')
-query T
+# TODO https://issues.apache.org/jira/browse/IGNITE-15123 Multi-tuple insert
fails on validation
+statement ok
+INSERT INTO times VALUES (NULL)
+
+query T rowsort
SELECT * FROM times
----
00:01:20
-20:08:10.998
-20:08:10.33
20:08:10.001
+20:08:10.330
+20:08:10.998
NULL
-query T
+query T rowsort
SELECT cast(i AS VARCHAR) FROM times
----
-00:01:20
-20:08:10.998
-20:08:10.33
+00:01:20.000
20:08:10.001
+20:08:10.330
+20:08:10.998
NULL
diff --git
a/modules/sql-engine/src/integrationTest/sql/types/time/time_parsing.test
b/modules/sql-engine/src/integrationTest/sql/types/time/time_parsing.test
new file mode 100644
index 0000000000..9dbb1523d5
--- /dev/null
+++ b/modules/sql-engine/src/integrationTest/sql/types/time/time_parsing.test
@@ -0,0 +1,29 @@
+# name: test/sql/types/time/time_parsing.test
+# description: Test time parsing
+# group: [time]
+# note: according to SQL standard default TIME precision is 0 (Sql 2011 Part 2
Section 6.1 syntax rule 34)
+
+statement ok
+PRAGMA enable_verification
+
+query I
+SELECT '14:42:04'::TIME::VARCHAR
+----
+14:42:04
+
+# milliseconds
+query I
+SELECT '14:42:04.35'::TIME(2)::VARCHAR
+----
+14:42:04.35
+
+query I
+SELECT '14:42:04.999'::TIME(3)::VARCHAR
+----
+14:42:04.999
+
+query I
+SELECT '14:42:04.999'::TIME(6)::VARCHAR
+----
+14:42:04.999000
+
diff --git
a/modules/sql-engine/src/integrationTest/sql/types/time/time_parsing.test_ignore
b/modules/sql-engine/src/integrationTest/sql/types/time/time_parsing.test_ignore
index cf074966f0..fde5670903 100644
---
a/modules/sql-engine/src/integrationTest/sql/types/time/time_parsing.test_ignore
+++
b/modules/sql-engine/src/integrationTest/sql/types/time/time_parsing.test_ignore
@@ -2,7 +2,7 @@
# description: Test time parsing
# group: [time]
# Ignore https://issues.apache.org/jira/browse/IGNITE-15619
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15622
+# Ignore https://issues.apache.org/jira/browse/IGNITE-19162
statement ok
PRAGMA enable_verification
diff --git
a/modules/sql-engine/src/integrationTest/sql/types/timestamp/bc_timestamp.test_ignore
b/modules/sql-engine/src/integrationTest/sql/types/timestamp/bc_timestamp.test_ignore
index 0da654341c..ae8c93dea8 100644
---
a/modules/sql-engine/src/integrationTest/sql/types/timestamp/bc_timestamp.test_ignore
+++
b/modules/sql-engine/src/integrationTest/sql/types/timestamp/bc_timestamp.test_ignore
@@ -1,7 +1,8 @@
# name: test/sql/types/timestamp/bc_timestamp.test
# description: Test BC timestamps
# group: [timestamp]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15622
+# Ignore https://issues.apache.org/jira/browse/IGNITE-15619
+# Ignore https://issues.apache.org/jira/browse/IGNITE-19162
query I
SELECT '1969-01-01 01:03:20.45432'::TIMESTAMP::VARCHAR
diff --git
a/modules/sql-engine/src/integrationTest/sql/types/timestamp/test_timestamp.test
b/modules/sql-engine/src/integrationTest/sql/types/timestamp/test_timestamp.test
index d07d23050d..d96a48a8d2 100644
---
a/modules/sql-engine/src/integrationTest/sql/types/timestamp/test_timestamp.test
+++
b/modules/sql-engine/src/integrationTest/sql/types/timestamp/test_timestamp.test
@@ -94,4 +94,29 @@ SELECT (DATE '1992-01-01')::TIMESTAMP;
----
1992-01-01T00:00
+query T
+SELECT TIMESTAMP '2008-01-01 00:00:01.5'::VARCHAR
+----
+2008-01-01 00:00:01.5
+query T
+SELECT TIMESTAMP '2008-01-01 00:00:01.999'::VARCHAR
+----
+2008-01-01 00:00:01.999
+
+query T
+SELECT '2008-01-01 00:00:01.999'::TIMESTAMP::VARCHAR
+----
+2008-01-01 00:00:01.999000
+
+# Value must be rounded up.
+query T
+SELECT '2008-01-01 00:00:01.9995'::TIMESTAMP(3)::VARCHAR
+----
+2008-01-01 00:00:02.000
+
+# Value must be rounded down.
+query T
+SELECT '2008-01-01 00:00:01.9994'::TIMESTAMP(3)::VARCHAR
+----
+2008-01-01 00:00:01.999
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/RexToLixTranslator.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/RexToLixTranslator.java
index 1f8b00138e..6ed985d7d2 100644
---
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/RexToLixTranslator.java
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/RexToLixTranslator.java
@@ -450,8 +450,9 @@ public class RexToLixTranslator implements
RexVisitor<RexToLixTranslator.Result>
BuiltInMethod.TIMESTAMP_STRING_TO_TIMESTAMP_WITH_LOCAL_TIME_ZONE.method,
RexImpTable.optimize2(operand,
Expressions.call(
-
BuiltInMethod.UNIX_TIMESTAMP_TO_STRING.method,
- operand)),
+
IgniteMethod.UNIX_TIMESTAMP_TO_STRING_PRECISION_AWARE.method(),
+ operand,
+
Expressions.constant(targetType.getPrecision()))),
Expressions.call(BuiltInMethod.TIME_ZONE.method, root));
break;
default:
@@ -483,8 +484,9 @@ public class RexToLixTranslator implements
RexVisitor<RexToLixTranslator.Result>
case TIME:
convert =
RexImpTable.optimize2(operand,
-
Expressions.call(BuiltInMethod.UNIX_TIME_TO_STRING.method,
- operand));
+
Expressions.call(IgniteMethod.UNIX_TIME_TO_STRING_PRECISION_AWARE.method(),
+ operand,
+
Expressions.constant(sourceType.getPrecision())));
break;
case TIME_WITH_LOCAL_TIME_ZONE:
convert =
@@ -497,8 +499,10 @@ public class RexToLixTranslator implements
RexVisitor<RexToLixTranslator.Result>
case TIMESTAMP:
convert =
RexImpTable.optimize2(operand,
-
Expressions.call(BuiltInMethod.UNIX_TIMESTAMP_TO_STRING.method,
- operand));
+ Expressions.call(
+
IgniteMethod.UNIX_TIMESTAMP_TO_STRING_PRECISION_AWARE.method(),
+ operand,
+
Expressions.constant(sourceType.getPrecision())));
break;
case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
convert =
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/IgniteMethod.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/IgniteMethod.java
index 0ac553b9ec..c72481024f 100644
---
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/IgniteMethod.java
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/IgniteMethod.java
@@ -28,6 +28,7 @@ import java.util.TimeZone;
import java.util.UUID;
import org.apache.calcite.DataContext;
import org.apache.calcite.avatica.util.ByteString;
+import org.apache.calcite.avatica.util.DateTimeUtils;
import org.apache.calcite.linq4j.tree.Types;
import org.apache.calcite.runtime.SqlFunctions;
import org.apache.calcite.sql.SqlIntervalQualifier;
@@ -133,6 +134,16 @@ public enum IgniteMethod {
* See {@link IgniteSqlFunctions#decimalDivide(BigDecimal, BigDecimal,
int, int)}.
*/
DECIMAL_DIVIDE(IgniteSqlFunctions.class, "decimalDivide",
BigDecimal.class, BigDecimal.class, int.class, int.class),
+
+ /**
+ * Conversion of timestamp to string (precision aware).
+ */
+ UNIX_TIMESTAMP_TO_STRING_PRECISION_AWARE(DateTimeUtils.class,
"unixTimestampToString", long.class, int.class),
+
+ /**
+ * Conversion of time to string (precision aware).
+ */
+ UNIX_TIME_TO_STRING_PRECISION_AWARE(DateTimeUtils.class,
"unixTimeToString", int.class, int.class),
;
private final Method method;
