This is an automated email from the ASF dual-hosted git repository.
snuyanzin 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 bf8e1142c8 [CALCITE-4554] Support TIMESTAMP WITH LOCAL TIME ZONE for
Snapshot and MatchRecognize
bf8e1142c8 is described below
commit bf8e1142c8635823a45117261c836c95a3994668
Author: Sergey Nuyanzin <[email protected]>
AuthorDate: Sun Mar 12 14:41:43 2023 +0100
[CALCITE-4554] Support TIMESTAMP WITH LOCAL TIME ZONE for Snapshot and
MatchRecognize
Close apache/calcite#3106
---
.../java/org/apache/calcite/rel/core/Snapshot.java | 4 +--
.../calcite/sql/validate/SqlValidatorImpl.java | 4 +--
.../org/apache/calcite/test/RelBuilderTest.java | 25 ++++++++++++++
.../apache/calcite/test/SqlToRelConverterTest.java | 8 +++++
.../apache/calcite/test/SqlValidatorMatchTest.kt | 16 +++++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 5 +++
.../apache/calcite/test/SqlToRelConverterTest.xml | 38 ++++++++++++++++------
.../org/apache/calcite/test/catalog/Fixture.java | 4 +++
.../test/catalog/MockCatalogReaderSimple.java | 4 +++
9 files changed, 94 insertions(+), 14 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rel/core/Snapshot.java
b/core/src/main/java/org/apache/calcite/rel/core/Snapshot.java
index 317314d0ad..7a60b9d2f4 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/Snapshot.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/Snapshot.java
@@ -27,7 +27,7 @@ import org.apache.calcite.rel.hint.RelHint;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexShuttle;
-import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.util.Litmus;
import com.google.common.collect.ImmutableList;
@@ -131,7 +131,7 @@ public abstract class Snapshot extends SingleRel implements
Hintable {
@Override public boolean isValid(Litmus litmus, @Nullable Context context) {
RelDataType dataType = period.getType();
- if (dataType.getSqlTypeName() != SqlTypeName.TIMESTAMP) {
+ if (!SqlTypeUtil.isTimestamp(dataType)) {
return litmus.fail("The system time period specification expects
Timestamp type but is '"
+ dataType.getSqlTypeName() + "'");
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 5ecb85591f..a16d30862f 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -5323,7 +5323,7 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
SqlSnapshot snapshot = (SqlSnapshot) node;
SqlNode period = snapshot.getPeriod();
RelDataType dataType = deriveType(requireNonNull(scope, "scope"),
period);
- if (dataType.getSqlTypeName() != SqlTypeName.TIMESTAMP) {
+ if (!SqlTypeUtil.isTimestamp(dataType)) {
throw newValidationError(period,
Static.RESOURCE.illegalExpressionForTemporal(dataType.getSqlTypeName().getName()));
}
@@ -5671,7 +5671,7 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
identifier = (SqlIdentifier) requireNonNull(firstOrderByColumn,
"firstOrderByColumn");
}
RelDataType firstOrderByColumnType = deriveType(scope, identifier);
- if (firstOrderByColumnType.getSqlTypeName() != SqlTypeName.TIMESTAMP) {
+ if (!SqlTypeUtil.isTimestamp(firstOrderByColumnType)) {
throw newValidationError(interval,
RESOURCE.firstColumnOfOrderByMustBeTimestamp());
}
diff --git a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
index b47b101877..0a487b9e7d 100644
--- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
@@ -495,6 +495,31 @@ public class RelBuilderTest {
assertThat(root, hasTree(expected));
}
+ @Test void testJoinTemporalTableForTimestampWithLocalTimezone() {
+ // Equivalent SQL:
+ // SELECT *
+ // FROM orders
+ // JOIN products_temporal FOR SYSTEM_TIME
+ // AS OF TIMESTAMP WITH LOCAL TIME ZONE '2011-07-20 12:34:56'
+ // ON orders.product = products_temporal.id
+ final RelBuilder builder = RelBuilder.create(config().build());
+ RelNode root =
+ builder.scan("orders")
+ .scan("products_temporal")
+ .snapshot(
+ builder.getRexBuilder().makeTimestampWithLocalTimeZoneLiteral(
+ new TimestampString("2011-07-20 12:34:56"), 0))
+ .join(JoinRelType.INNER,
+ builder.equals(builder.field(2, 0, "PRODUCT"),
+ builder.field(2, 1, "ID")))
+ .build();
+ final String expected = "LogicalJoin(condition=[=($2, $4)],
joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, orders]])\n"
+ + " LogicalSnapshot(period=[2011-07-20
12:34:56:TIMESTAMP_WITH_LOCAL_TIME_ZONE(0)])\n"
+ + " LogicalTableScan(table=[[scott, products_temporal]])\n";
+ assertThat(root, hasTree(expected));
+ }
+
/** Tests that {@link RelBuilder#project} simplifies expressions if and only
if
* {@link RelBuilder.Config#simplify}. */
@Test void testSimplify() {
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 9326ae9fc5..3d36135ec6 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1246,6 +1246,14 @@ class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).ok();
}
+ @Test void testJoinTemporalTableOnSpecificTimestampWithLocalTimeZone() {
+ final String sql = "select stream *\n"
+ + "from orders,\n"
+ + " products_temporal for system_time as of\n"
+ + " TIMESTAMP WITH LOCAL TIME ZONE '2011-01-02 00:00:00'";
+ sql(sql).ok();
+ }
+
@Test void testJoinTemporalTableOnSpecificTime2() {
// Test temporal table with virtual columns.
final String sql = "select stream *\n"
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlValidatorMatchTest.kt
b/core/src/test/java/org/apache/calcite/test/SqlValidatorMatchTest.kt
index 35bdb99979..678d4f64a9 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorMatchTest.kt
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorMatchTest.kt
@@ -293,4 +293,20 @@ class SqlValidatorMatchTest : SqlValidatorTestCase() {
""".trimIndent()
).fails("First column of ORDER BY must be of type TIMESTAMP")
}
+
+ @Test
+ fun `match recognize within order by timestamp with local time zone`() {
+ sql(
+ """
+ select *
+ from products_temporal match_recognize (
+ order by sys_start_local_timestamp
+ pattern (strt down+ up+) within ^interval '3:10' minute to
second^
+ define
+ down as down.supplierid < PREV(down.supplierid),
+ up as up.supplierid > prev(up.supplierid)
+ ) mr
+ """.trimIndent()
+ ).ok()
+ }
}
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 2ec6d059f8..acd8e3d877 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -7605,6 +7605,11 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
.fails("Table 'PRODUCTS' is not a temporal table, "
+ "can not be queried in system time period specification");
+ sql("select stream * from orders, ^products^ for system_time as of"
+ + " TIMESTAMP WITH LOCAL TIME ZONE '2011-01-02 00:00:00'")
+ .fails("Table 'PRODUCTS' is not a temporal table, "
+ + "can not be queried in system time period specification");
+
sql("select stream * from orders, products_temporal "
+ "for system_time as of ^'2011-01-02 00:00:00'^")
.fails("The system time period specification expects Timestamp type
but is 'CHAR'");
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 450421a3c3..d1581c2922 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -1360,10 +1360,10 @@ cross join lateral (
<Resource name="plan">
<![CDATA[
LogicalDelta
- LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], PRODUCTID0=[$3],
NAME=[$4], SUPPLIERID=[$5], SYS_START=[$6], SYS_END=[$7])
+ LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], PRODUCTID0=[$3],
NAME=[$4], SUPPLIERID=[$5], SYS_START=[$6], SYS_END=[$7],
SYS_START_LOCAL_TIMESTAMP=[$8], SYS_END_LOCAL_TIMESTAMP=[$9])
LogicalCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{0, 1}])
LogicalTableScan(table=[[CATALOG, SALES, ORDERS]])
- LogicalProject(PRODUCTID=[$0], NAME=[$1], SUPPLIERID=[$2],
SYS_START=[$3], SYS_END=[$4])
+ LogicalProject(PRODUCTID=[$0], NAME=[$1], SUPPLIERID=[$2],
SYS_START=[$3], SYS_END=[$4], SYS_START_LOCAL_TIMESTAMP=[$5],
SYS_END_LOCAL_TIMESTAMP=[$6])
LogicalFilter(condition=[=($cor1.PRODUCTID, $0)])
LogicalSnapshot(period=[$cor1.ROWTIME])
LogicalTableScan(table=[[CATALOG, SALES, PRODUCTS_TEMPORAL]])
@@ -1383,10 +1383,10 @@ cross join lateral (
<Resource name="plan">
<![CDATA[
LogicalDelta
- LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], PRODUCTID0=[$3],
NAME=[$4], SUPPLIERID=[$5], SYS_START=[$6], SYS_END=[$7])
- LogicalJoin(condition=[=($1, $8)], joinType=[inner])
+ LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], PRODUCTID0=[$3],
NAME=[$4], SUPPLIERID=[$5], SYS_START=[$6], SYS_END=[$7],
SYS_START_LOCAL_TIMESTAMP=[$8], SYS_END_LOCAL_TIMESTAMP=[$9])
+ LogicalJoin(condition=[=($1, $10)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, ORDERS]])
- LogicalProject(PRODUCTID=[$0], NAME=[$1], SUPPLIERID=[$2],
SYS_START=[$3], SYS_END=[$4], PRODUCTID5=[$0])
+ LogicalProject(PRODUCTID=[$0], NAME=[$1], SUPPLIERID=[$2],
SYS_START=[$3], SYS_END=[$4], SYS_START_LOCAL_TIMESTAMP=[$5],
SYS_END_LOCAL_TIMESTAMP=[$6], PRODUCTID7=[$0])
LogicalSnapshot(period=[2011-01-02 00:00:00])
LogicalTableScan(table=[[CATALOG, SALES, PRODUCTS_TEMPORAL]])
]]>
@@ -1405,10 +1405,10 @@ cross join lateral (
<Resource name="plan">
<![CDATA[
LogicalDelta
- LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], PRODUCTID0=[$3],
NAME=[$4], SUPPLIERID=[$5], SYS_START=[$6], SYS_END=[$7])
+ LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], PRODUCTID0=[$3],
NAME=[$4], SUPPLIERID=[$5], SYS_START=[$6], SYS_END=[$7],
SYS_START_LOCAL_TIMESTAMP=[$8], SYS_END_LOCAL_TIMESTAMP=[$9])
LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])
LogicalTableScan(table=[[CATALOG, SALES, ORDERS]])
- LogicalProject(PRODUCTID=[$0], NAME=[$1], SUPPLIERID=[$2],
SYS_START=[$3], SYS_END=[$4])
+ LogicalProject(PRODUCTID=[$0], NAME=[$1], SUPPLIERID=[$2],
SYS_START=[$3], SYS_END=[$4], SYS_START_LOCAL_TIMESTAMP=[$5],
SYS_END_LOCAL_TIMESTAMP=[$6])
LogicalFilter(condition=[>($0, 1)])
LogicalSnapshot(period=[$cor0.ROWTIME])
LogicalTableScan(table=[[CATALOG, SALES, PRODUCTS_TEMPORAL]])
@@ -3488,7 +3488,7 @@ on orders.productid = products_temporal.productid]]>
<Resource name="plan">
<![CDATA[
LogicalDelta
- LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], PRODUCTID0=[$3],
NAME=[$4], SUPPLIERID=[$5], SYS_START=[$6], SYS_END=[$7])
+ LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], PRODUCTID0=[$3],
NAME=[$4], SUPPLIERID=[$5], SYS_START=[$6], SYS_END=[$7],
SYS_START_LOCAL_TIMESTAMP=[$8], SYS_END_LOCAL_TIMESTAMP=[$9])
LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0, 1}])
LogicalTableScan(table=[[CATALOG, SALES, ORDERS]])
LogicalFilter(condition=[=($cor0.PRODUCTID, $0)])
@@ -3527,7 +3527,7 @@ from orders,
<Resource name="plan">
<![CDATA[
LogicalDelta
- LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], PRODUCTID0=[$3],
NAME=[$4], SUPPLIERID=[$5], SYS_START=[$6], SYS_END=[$7])
+ LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], PRODUCTID0=[$3],
NAME=[$4], SUPPLIERID=[$5], SYS_START=[$6], SYS_END=[$7],
SYS_START_LOCAL_TIMESTAMP=[$8], SYS_END_LOCAL_TIMESTAMP=[$9])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, ORDERS]])
LogicalSnapshot(period=[2011-01-02 00:00:00])
@@ -3551,6 +3551,24 @@ LogicalDelta
LogicalSnapshot(period=[2011-01-02 00:00:00])
LogicalProject(A=[$0], B=[$1], C=[$2], D=[$3], $f4=[+($0, 1)])
LogicalTableScan(table=[[CATALOG, VIRTUALCOLUMNS, VC_T1]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinTemporalTableOnSpecificTimestampWithLocalTimeZone">
+ <Resource name="sql">
+ <![CDATA[select stream *
+from orders,
+ products_temporal for system_time as of
+ TIMESTAMP WITH LOCAL TIME ZONE '2011-01-02 00:00:00']]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalDelta
+ LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], PRODUCTID0=[$3],
NAME=[$4], SUPPLIERID=[$5], SYS_START=[$6], SYS_END=[$7],
SYS_START_LOCAL_TIMESTAMP=[$8], SYS_END_LOCAL_TIMESTAMP=[$9])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, ORDERS]])
+ LogicalSnapshot(period=[2011-01-02
00:00:00:TIMESTAMP_WITH_LOCAL_TIME_ZONE(0)])
+ LogicalTableScan(table=[[CATALOG, SALES, PRODUCTS_TEMPORAL]])
]]>
</Resource>
</TestCase>
@@ -6715,7 +6733,7 @@ LogicalProject(EXPR$0=[$1])
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(PRODUCTID=[$0], NAME=[$1], SUPPLIERID=[$2], SYS_START=[$3],
SYS_END=[$4])
+LogicalProject(PRODUCTID=[$0], NAME=[$1], SUPPLIERID=[$2], SYS_START=[$3],
SYS_END=[$4], SYS_START_LOCAL_TIMESTAMP=[$5], SYS_END_LOCAL_TIMESTAMP=[$6])
LogicalSnapshot(period=[2011-01-02 00:00:00])
LogicalTableScan(table=[[CATALOG, SALES, PRODUCTS_TEMPORAL]])
]]>
diff --git a/testkit/src/main/java/org/apache/calcite/test/catalog/Fixture.java
b/testkit/src/main/java/org/apache/calcite/test/catalog/Fixture.java
index f1e97dc469..d0df596d2d 100644
--- a/testkit/src/main/java/org/apache/calcite/test/catalog/Fixture.java
+++ b/testkit/src/main/java/org/apache/calcite/test/catalog/Fixture.java
@@ -42,7 +42,11 @@ final class Fixture extends AbstractFixture {
final RelDataType varchar20Type = sqlType(SqlTypeName.VARCHAR, 20);
final RelDataType varchar20TypeNull = nullable(varchar20Type);
final RelDataType timestampType = sqlType(SqlTypeName.TIMESTAMP);
+ final RelDataType timestampTypeWithLocalTimeZone =
+ sqlType(SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE);
final RelDataType timestampTypeNull = nullable(timestampType);
+ final RelDataType timestampTypeWithLocalTimeZoneNull =
+ nullable(timestampTypeWithLocalTimeZone);
final RelDataType dateType = sqlType(SqlTypeName.DATE);
final RelDataType booleanType = sqlType(SqlTypeName.BOOLEAN);
final RelDataType booleanTypeNull = nullable(booleanType);
diff --git
a/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java
b/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java
index ac201378e6..e34173e250 100644
---
a/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java
+++
b/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java
@@ -286,6 +286,10 @@ public class MockCatalogReaderSimple extends
MockCatalogReader {
productsTemporalTable.addColumn("SUPPLIERID", fixture.intType);
productsTemporalTable.addColumn("SYS_START", fixture.timestampType);
productsTemporalTable.addColumn("SYS_END", fixture.timestampType);
+ productsTemporalTable.addColumn(
+ "SYS_START_LOCAL_TIMESTAMP", fixture.timestampTypeWithLocalTimeZone);
+ productsTemporalTable.addColumn(
+ "SYS_END_LOCAL_TIMESTAMP", fixture.timestampTypeWithLocalTimeZone);
registerTable(productsTemporalTable);
}
