This is an automated email from the ASF dual-hosted git repository.
danny0405 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new b523007 [CALCITE-3855] Supports snapshot on table with virtual
columns during sql-to-rel conversion
b523007 is described below
commit b5230070e5f495e2c5a7e4baa726a9f7a8877630
Author: yuzhao.cyz <yuzhao....@gmail.com>
AuthorDate: Thu Mar 12 16:01:18 2020 +0800
[CALCITE-3855] Supports snapshot on table with virtual columns during
sql-to-rel conversion
In SqlToRelConverter#convertTemporalTable, we actually should not assume
that the rel to snapshot is always a TableScan.
---
.../apache/calcite/sql2rel/SqlToRelConverter.java | 4 +-
.../apache/calcite/test/SqlToRelConverterTest.java | 31 ++++++++++--
.../test/catalog/MockCatalogReaderExtended.java | 6 ++-
.../catalog/VirtualColumnsExpressionFactory.java | 18 +++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 58 ++++++++++++++++++++--
5 files changed, 106 insertions(+), 11 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 783e981..f249643 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -47,7 +47,6 @@ import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.core.RelFactories;
import org.apache.calcite.rel.core.Sample;
import org.apache.calcite.rel.core.Sort;
-import org.apache.calcite.rel.core.TableScan;
import org.apache.calcite.rel.core.Uncollect;
import org.apache.calcite.rel.core.Values;
import org.apache.calcite.rel.hint.HintStrategyTable;
@@ -2490,9 +2489,8 @@ public class SqlToRelConverter {
// convert inner query, could be a table name or a derived table
SqlNode expr = snapshot.getTableRef();
convertFrom(bb, expr);
- final TableScan scan = (TableScan) bb.root;
- final RelNode snapshotRel = relBuilder.push(scan).snapshot(period).build();
+ final RelNode snapshotRel =
relBuilder.push(bb.root).snapshot(period).build();
bb.setRoot(snapshotRel, false);
}
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 dc133ea..ac78d1b 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1121,13 +1121,20 @@ public class SqlToRelConverterTest extends
SqlToRelTestBase {
sql("select * from dept, lateral table(ramp(deptno))").ok();
}
- @Test public void testSnapshotOnTemporalTable() {
+ @Test public void testSnapshotOnTemporalTable1() {
final String sql = "select * from products_temporal "
+ "for system_time as of TIMESTAMP '2011-01-02 00:00:00'";
sql(sql).ok();
}
- @Test public void testJoinTemporalTableOnSpecificTime() {
+ @Test public void testSnapshotOnTemporalTable2() {
+ // Test temporal table with virtual columns.
+ final String sql = "select * from VIRTUALCOLUMNS.VC_T1 "
+ + "for system_time as of TIMESTAMP '2011-01-02 00:00:00'";
+ sql(sql).with(getExtendedTester()).ok();
+ }
+
+ @Test public void testJoinTemporalTableOnSpecificTime1() {
final String sql = "select stream *\n"
+ "from orders,\n"
+ " products_temporal for system_time as of\n"
@@ -1135,7 +1142,16 @@ public class SqlToRelConverterTest extends
SqlToRelTestBase {
sql(sql).ok();
}
- @Test public void testJoinTemporalTableOnColumnReference() {
+ @Test public void testJoinTemporalTableOnSpecificTime2() {
+ // Test temporal table with virtual columns.
+ final String sql = "select stream *\n"
+ + "from orders,\n"
+ + " VIRTUALCOLUMNS.VC_T1 for system_time as of\n"
+ + " TIMESTAMP '2011-01-02 00:00:00'";
+ sql(sql).with(getExtendedTester()).ok();
+ }
+
+ @Test public void testJoinTemporalTableOnColumnReference1() {
final String sql = "select stream *\n"
+ "from orders\n"
+ "join products_temporal for system_time as of orders.rowtime\n"
@@ -1143,6 +1159,15 @@ public class SqlToRelConverterTest extends
SqlToRelTestBase {
sql(sql).ok();
}
+ @Test public void testJoinTemporalTableOnColumnReference2() {
+ // Test temporal table with virtual columns.
+ final String sql = "select stream *\n"
+ + "from orders\n"
+ + "join VIRTUALCOLUMNS.VC_T1 for system_time as of orders.rowtime\n"
+ + "on orders.productid = VIRTUALCOLUMNS.VC_T1.a";
+ sql(sql).with(getExtendedTester()).ok();
+ }
+
/**
* Lateral join with temporal table, both snapshot's input scan
* and snapshot's period reference outer columns. Should not
diff --git
a/core/src/test/java/org/apache/calcite/test/catalog/MockCatalogReaderExtended.java
b/core/src/test/java/org/apache/calcite/test/catalog/MockCatalogReaderExtended.java
index b867eaa..a12cf3b 100644
---
a/core/src/test/java/org/apache/calcite/test/catalog/MockCatalogReaderExtended.java
+++
b/core/src/test/java/org/apache/calcite/test/catalog/MockCatalogReaderExtended.java
@@ -119,12 +119,14 @@ public class MockCatalogReaderExtended extends
MockCatalogReaderSimple {
registerSchema(virtualColumnsSchema);
final MockTable virtualColumnsTable1 =
MockTable.create(this, virtualColumnsSchema, "VC_T1", false, 100,
- null, new VirtualColumnsExpressionFactory(), false);
+ null, new VirtualColumnsExpressionFactory(), true);
virtualColumnsTable1.addColumn("A", f.intTypeNull);
virtualColumnsTable1.addColumn("B", f.bigintType);
virtualColumnsTable1.addColumn("C", f.varchar10Type);
virtualColumnsTable1.addColumn("D", f.intTypeNull);
- virtualColumnsTable1.addColumn("E", f.bigintType);
+ // Column E has the same type as column A because it's a virtual column
+ // with expression that references column A.
+ virtualColumnsTable1.addColumn("E", f.intTypeNull);
// Same schema with VC_T1 but with different table name.
final MockTable virtualColumnsTable2 =
MockTable.create(this, virtualColumnsSchema, "VC_T2", false, 100,
diff --git
a/core/src/test/java/org/apache/calcite/test/catalog/VirtualColumnsExpressionFactory.java
b/core/src/test/java/org/apache/calcite/test/catalog/VirtualColumnsExpressionFactory.java
index 2efbdcb..8e206d1 100644
---
a/core/src/test/java/org/apache/calcite/test/catalog/VirtualColumnsExpressionFactory.java
+++
b/core/src/test/java/org/apache/calcite/test/catalog/VirtualColumnsExpressionFactory.java
@@ -17,7 +17,11 @@
package org.apache.calcite.test.catalog;
import org.apache.calcite.plan.RelOptTable;
+import org.apache.calcite.rex.RexNode;
import org.apache.calcite.schema.ColumnStrategy;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.parser.SqlParser;
+import org.apache.calcite.sql2rel.InitializerContext;
import org.apache.calcite.sql2rel.NullInitializerExpressionFactory;
/** Define column strategies for the "VIRTUALCOLUMNS" table. */
@@ -32,4 +36,18 @@ public class VirtualColumnsExpressionFactory extends
NullInitializerExpressionFa
return super.generationStrategy(table, iColumn);
}
}
+
+ @Override public RexNode newColumnDefaultValue(
+ RelOptTable table, int iColumn, InitializerContext context) {
+ if (iColumn == 4) {
+ final SqlNode node = context.parseExpression(SqlParser.Config.DEFAULT,
"A + 1");
+ // Actually we should validate the node with physical schema,
+ // here full table schema(includes the virtual columns) also works
+ // because the expression "A + 1" does not reference any virtual column.
+ final SqlNode validated = context.validateExpression(table.getRowType(),
node);
+ return context.convertExpression(validated);
+ } else {
+ return super.newColumnDefaultValue(table, iColumn, context);
+ }
+ }
}
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 92ad19a..2f00acf 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -323,7 +323,7 @@ LogicalProject(DEPTNO=[$0], NAME=[$1], I=[$2])
]]>
</Resource>
</TestCase>
- <TestCase name="testSnapshotOnTemporalTable">
+ <TestCase name="testSnapshotOnTemporalTable1">
<Resource name="sql">
<![CDATA[select * from products_temporal for system_time as of
TIMESTAMP '2011-01-02 00:00:00']]>
</Resource>
@@ -335,7 +335,20 @@ LogicalProject(PRODUCTID=[$0], NAME=[$1], SUPPLIERID=[$2],
SYS_START=[$3], SYS_E
]]>
</Resource>
</TestCase>
- <TestCase name="testJoinTemporalTableOnSpecificTime">
+ <TestCase name="testSnapshotOnTemporalTable2">
+ <Resource name="sql">
+ <![CDATA[select * from VIRTUALCOLUMNS.VC_T1 for system_time as of
TIMESTAMP '2011-01-02 00:00:00']]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(A=[$0], B=[$1], C=[$2], D=[$3], E=[$4])
+ 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="testJoinTemporalTableOnSpecificTime1">
<Resource name="sql">
<![CDATA[select stream *
from orders,
@@ -353,7 +366,26 @@ LogicalDelta
]]>
</Resource>
</TestCase>
- <TestCase name="testJoinTemporalTableOnColumnReference">
+ <TestCase name="testJoinTemporalTableOnSpecificTime2">
+ <Resource name="sql">
+ <![CDATA[select stream *
+from orders,
+ VIRTUALCOLUMNS.VC_T1 for system_time as of
+ TIMESTAMP '2011-01-02 00:00:00']]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalDelta
+ LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], A=[$3], B=[$4],
C=[$5], D=[$6], E=[$7])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, ORDERS]])
+ 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="testJoinTemporalTableOnColumnReference1">
<Resource name="sql">
<![CDATA[select stream *
from orders
@@ -372,6 +404,26 @@ LogicalDelta
]]>
</Resource>
</TestCase>
+ <TestCase name="testJoinTemporalTableOnColumnReference2">
+ <Resource name="sql">
+ <![CDATA[select stream *
+from orders
+join VIRTUALCOLUMNS.VC_T1 for system_time as of orders.rowtime
+on orders.productid = VIRTUALCOLUMNS.VC_T1.a]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalDelta
+ LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], A=[$3], B=[$4],
C=[$5], D=[$6], E=[$7])
+ LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0, 1}])
+ LogicalTableScan(table=[[CATALOG, SALES, ORDERS]])
+ LogicalFilter(condition=[=($cor0.PRODUCTID, $0)])
+ LogicalSnapshot(period=[$cor0.ROWTIME])
+ LogicalProject(A=[$0], B=[$1], C=[$2], D=[$3], $f4=[+($0, 1)])
+ LogicalTableScan(table=[[CATALOG, VIRTUALCOLUMNS, VC_T1]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testCrossJoinTemporalTable1">
<Resource name="sql">
<