calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject [1/7] calcite git commit: [CALCITE-1666] Support for modifiable views with extended columns (Kevin Liew)
Date Wed, 15 Mar 2017 15:58:28 GMT
Repository: calcite
Updated Branches:
  refs/heads/master 55ffb5edd -> ca48431ee


http://git-wip-us.apache.org/repos/asf/calcite/blob/e0a1f7d3/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
----------------------------------------------------------------------
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 d1200e6..242226d 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -742,11 +742,56 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
     sql(sql).ok();
   }
 
+  @Test public void testTableSubset() {
+    final String sql = "select deptno, name from dept";
+    sql(sql).ok();
+  }
+
+  @Test public void testTableExpression() {
+    final String sql = "select deptno + deptno from dept";
+    sql(sql).ok();
+  }
+
   @Test public void testTableExtend() {
     final String sql = "select * from dept extend (x varchar(5) not null)";
     sql(sql).ok();
   }
 
+  @Test public void testTableExtendSubset() {
+    final String sql = "select deptno, x from dept extend (x int)";
+    sql(sql).ok();
+  }
+
+  @Test public void testTableExtendExpression() {
+    final String sql = "select deptno + x from dept extend (x int not null)";
+    sql(sql).ok();
+  }
+
+  @Test public void testModifiableViewExtend() {
+    final String sql = "select * from EMP_MODIFIABLEVIEW extend (x varchar(5) not null)";
+    sql(sql).ok();
+  }
+
+  @Test public void testModifiableViewExtendSubset() {
+    final String sql = "select x, empno from EMP_MODIFIABLEVIEW extend (x varchar(5) not
null)";
+    sql(sql).ok();
+  }
+
+  @Test public void testModifiableViewExtendExpression() {
+    final String sql = "select empno + x from EMP_MODIFIABLEVIEW extend (x int not null)";
+    sql(sql).ok();
+  }
+
+  @Test public void testSelectModifiableViewConstraint() {
+    final String sql = "select deptno from EMP_MODIFIABLEVIEW2 where deptno = ?";
+    sql(sql).ok();
+  }
+
+  @Test public void testModifiableViewDDLExtend() {
+    final String sql = "select extra from EMP_MODIFIABLEVIEW2";
+    sql(sql).ok();
+  }
+
   @Test public void testExplicitTable() {
     sql("table emp").ok();
   }
@@ -1590,6 +1635,30 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
     sql(sql).ok();
   }
 
+  @Test public void testInsertModifiableView() {
+    final String sql = "insert into EMP_MODIFIABLEVIEW (EMPNO, ENAME, JOB)"
+        + " values (34625, 'nom', 'accountant')";
+    sql(sql).ok();
+  }
+
+  @Test public void testInsertSubsetModifiableView() {
+    final String sql = "insert into EMP_MODIFIABLEVIEW "
+        + "values (10, 'Fred')";
+    sql(sql).conformance(SqlConformanceEnum.PRAGMATIC_2003).ok();
+  }
+
+  @Test public void testInsertBindModifiableView() {
+    final String sql = "insert into EMP_MODIFIABLEVIEW (empno, job)"
+        + " values (?, ?)";
+    sql(sql).ok();
+  }
+
+  @Test public void testInsertBindSubsetModifiableView() {
+    final String sql = "insert into EMP_MODIFIABLEVIEW"
+        + " values (?, ?)";
+    sql(sql).conformance(SqlConformanceEnum.PRAGMATIC_2003).ok();
+  }
+
   @Test public void testInsertWithCustomColumnResolving() {
     final String sql = "insert into struct.t values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
     sql(sql).ok();

http://git-wip-us.apache.org/repos/asf/calcite/blob/e0a1f7d3/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
----------------------------------------------------------------------
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 9749461..a2662e2 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -8181,6 +8181,23 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
         + "values (1, 'Karl')");
   }
 
+  @Test public void testInsertModifiableView() {
+    tester.checkQuery("insert into EMP_MODIFIABLEVIEW (empno, ename, job)\n"
+        + "values (1, 'Arthur', 'clown')");
+    tester.checkQuery("insert into EMP_MODIFIABLEVIEW2 (empno, ename, job, extra)\n"
+        + "values (1, 'Arthur', 'clown', true)");
+  }
+
+  @Test public void testInsertSubsetModifiableView() {
+    final SqlTester pragmaticTester =
+        tester.withConformance(SqlConformanceEnum.PRAGMATIC_2003);
+    pragmaticTester.checkQuery("insert into EMP_MODIFIABLEVIEW2\n"
+        + "values ('Arthur', 1)");
+    tester.checkQuery("insert into EMP_MODIFIABLEVIEW2\n"
+        + "values ('Arthur', 1, 'Knight', 20, false, 99999, true, timestamp '1370-01-01 00:00:00',"
+        + " 1, 100)");
+  }
+
   @Test public void testInsertBind() {
     // VALUES
     final String sql0 = "insert into empnullables (empno, ename, deptno)\n"
@@ -8265,6 +8282,63 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
     sql(sql2).tester(pragmaticTester).ok().bindType(expected2);
   }
 
+  @Test public void testInsertBindView() {
+    final String sql = "insert into EMP_MODIFIABLEVIEW (mgr, empno, ename)"
+        + " values (?, ?, ?)";
+    sql(sql).ok().bindType("RecordType(INTEGER ?0, INTEGER ?1, VARCHAR(20) ?2)");
+  }
+
+  @Test public void testInsertModifiableViewPassConstraint() {
+    sql("insert into EMP_MODIFIABLEVIEW2 (deptno, empno, ename, extra)"
+        + " values (20, 100, 'Lex', true)").ok();
+    sql("insert into EMP_MODIFIABLEVIEW2 (empno, ename, extra)"
+        + " values (100, 'Lex', true)").ok();
+    sql("insert into EMP_MODIFIABLEVIEW2 values ('Edward', 20)")
+        .tester(tester.withConformance(SqlConformanceEnum.PRAGMATIC_2003)).ok();
+  }
+
+  @Test public void testInsertModifiableViewFailConstraint() {
+    tester.checkQueryFails(
+        "insert into EMP_MODIFIABLEVIEW2 (deptno, empno, ename)"
+            + " values (^21^, 100, 'Lex')",
+        "Modifiable view constraint is not satisfied"
+            + " for column 'DEPTNO' of base table 'EMP_MODIFIABLEVIEW2'");
+    tester.checkQueryFails(
+        "insert into EMP_MODIFIABLEVIEW2 (deptno, empno, ename)"
+            + " values (^19+1^, 100, 'Lex')",
+        "Modifiable view constraint is not satisfied"
+            + " for column 'DEPTNO' of base table 'EMP_MODIFIABLEVIEW2'");
+    tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2\n"
+        + "values ('Arthur', 1, 'Knight', ^27^, false, 99999, true,"
+            + "timestamp '1370-01-01 00:00:00', 1, 100)",
+        "Modifiable view constraint is not satisfied"
+            + " for column 'DEPTNO' of base table 'EMP_MODIFIABLEVIEW2'");
+  }
+
+  @Test public void testUpdateModifiableViewPassConstraint() {
+    sql("update EMP_MODIFIABLEVIEW2"
+        + " set deptno = 20, empno = 99"
+        + " where ename = 'Lex'").ok();
+    sql("update EMP_MODIFIABLEVIEW2"
+        + " set empno = 99"
+        + " where ename = 'Lex'").ok();
+  }
+
+  @Test public void testUpdateModifiableViewFailConstraint() {
+    tester.checkQueryFails(
+        "update EMP_MODIFIABLEVIEW2"
+            + " set deptno = ^21^, empno = 99"
+            + " where ename = 'Lex'",
+        "Modifiable view constraint is not satisfied"
+            + " for column 'DEPTNO' of base table 'EMP_MODIFIABLEVIEW2'");
+    tester.checkQueryFails(
+        "update EMP_MODIFIABLEVIEW2"
+            + " set deptno = ^19 + 1^, empno = 99"
+            + " where ename = 'Lex'",
+        "Modifiable view constraint is not satisfied"
+            + " for column 'DEPTNO' of base table 'EMP_MODIFIABLEVIEW2'");
+  }
+
   @Test public void testInsertFailNullability() {
     tester.checkQueryFails(
         "insert into ^empnullables^ (ename) values ('Kevin')",
@@ -8358,6 +8432,24 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
         "Number of INSERT target columns \\(9\\) does not equal number of source items \\(2\\)");
   }
 
+  @Test public void testSelectViewFailExcludedColumn() {
+    tester.checkQueryFails("select ^deptno^, empno from EMP_MODIFIABLEVIEW",
+        "Column 'DEPTNO' not found in any table");
+  }
+
+  @Test public void testInsertFailExcludedColumn() {
+    tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW (empno, ename, ^deptno^)"
+        + " values (45, 'Jake', 5)",
+        "Unknown target column 'DEPTNO'");
+  }
+
+  @Test public void testInsertBindViewFailExcludedColumn() {
+    final String sql = "insert into EMP_MODIFIABLEVIEW (empno, ename, ^deptno^)"
+        + " values (?, ?, ?)";
+    tester.checkQueryFails(sql,
+        "Unknown target column 'DEPTNO'");
+  }
+
   @Test public void testInsertWithCustomInitializerExpressionFactory() {
     tester.checkQuery("insert into empdefaults (deptno) values (1)");
     tester.checkQuery("insert into empdefaults (ename, empno) values ('Quan', 50)");

http://git-wip-us.apache.org/repos/asf/calcite/blob/e0a1f7d3/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
----------------------------------------------------------------------
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 20c19bd..9273138 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2249,6 +2249,28 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$
 ]]>
         </Resource>
     </TestCase>
+    <TestCase name="testTableSubset">
+        <Resource name="sql">
+            <![CDATA[select deptno, name from dept]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1])
+  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testTableExpression">
+        <Resource name="sql">
+            <![CDATA[select deptno + deptno from dept]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EXPR$0=[+($0, $0)])
+  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+        </Resource>
+    </TestCase>
     <TestCase name="testTableExtend">
         <Resource name="sql">
             <![CDATA[select * from dept extend (x varchar(5) not null)]]>
@@ -2260,6 +2282,84 @@ LogicalProject(DEPTNO=[$0], NAME=[$1], X=[$2])
 ]]>
         </Resource>
     </TestCase>
+    <TestCase name="testTableExtendSubset">
+        <Resource name="sql">
+            <![CDATA[select deptno, x from dept extend (x int)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(DEPTNO=[$0], X=[$2])
+  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testTableExtendExpression">
+        <Resource name="sql">
+            <![CDATA[select deptno + x from dept extend (x int not null)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EXPR$0=[+($0, $2)])
+  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testModifiableViewExtend">
+        <Resource name="sql">
+            <![CDATA[select * from EMP_MODIFIABLEVIEW extend (x varchar(5) not null)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6],
SLACKER=[$8], X=[$9])
+  LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testModifiableViewExtendSubset">
+        <Resource name="sql">
+            <![CDATA[select x, empno from EMP_MODIFIABLEVIEW extend (x varchar(5) not
null)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(X=[$9], EMPNO=[$0])
+  LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testModifiableViewExtendExpression">
+        <Resource name="sql">
+            <![CDATA[select empno + x from EMP_MODIFIABLEVIEW extend (x int not null)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EXPR$0=[+($0, $9)])
+  LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testSelectModifiableViewConstraint">
+        <Resource name="sql">
+            <![CDATA[select deptno from EMP_MODIFIABLEVIEW2 where deptno = ?]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(DEPTNO=[$3])
+  LogicalFilter(condition=[=($3, ?0)])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testModifiableViewDDLExtend">
+        <Resource name="sql">
+            <![CDATA[select extra from EMP_MODIFIABLEVIEW2]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EXTRA=[$6])
+  LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]])
+]]>
+        </Resource>
+    </TestCase>
     <TestCase name="testGroupByCaseSubQuery">
         <Resource name="sql">
             <![CDATA[SELECT CASE WHEN emp.empno IN (3) THEN 0 ELSE 1 END
@@ -2708,6 +2808,60 @@ LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[INSERT],
f
 ]]>
         </Resource>
     </TestCase>
+    <TestCase name="testInsertModifiableView">
+        <Resource name="sql">
+            <![CDATA[insert into EMP_MODIFIABLEVIEW (EMPNO, ENAME, JOB) values (34625,
'nom', 'accountant')]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalTableModify(table=[[SALES, EMPDEFAULTS]], operation=[INSERT], flattened=[true])
+  LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[CAST($5):INTEGER],
COMM=[$6], DEPTNO=[20], SLACKER=[$7])
+    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[null], HIREDATE=[null], SAL=[555],
COMM=[null], SLACKER=[null])
+      LogicalValues(tuples=[[{ 34625, 'nom', 'accountant' }]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testInsertSubsetModifiableView">
+        <Resource name="sql">
+            <![CDATA[insert into EMP_MODIFIABLEVIEW values (10, 'Fred')]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalTableModify(table=[[SALES, EMPDEFAULTS]], operation=[INSERT], flattened=[true])
+  LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[CAST($5):INTEGER],
COMM=[$6], DEPTNO=[20], SLACKER=[$7])
+    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[null], MGR=[null], HIREDATE=[null], SAL=[555],
COMM=[null], SLACKER=[null])
+      LogicalValues(tuples=[[{ 10, 'Fred' }]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testInsertBindModifiableView">
+        <Resource name="sql">
+            <![CDATA[insert into EMP_MODIFIABLEVIEW (empno, job) values (?, ?)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalTableModify(table=[[SALES, EMPDEFAULTS]], operation=[INSERT], flattened=[true])
+  LogicalProject(EMPNO=[CAST($0):INTEGER NOT NULL], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[CAST($5):INTEGER], COMM=[$6], DEPTNO=[20], SLACKER=[$7])
+    LogicalProject(EMPNO=[$0], ENAME=['Bob'], JOB=[$1], MGR=[null], HIREDATE=[null], SAL=[555],
COMM=[null], SLACKER=[null])
+      LogicalProject(EXPR$0=[?0], EXPR$1=[?1])
+        LogicalValues(tuples=[[{ 0 }]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testInsertBindSubsetModifiableView">
+        <Resource name="sql">
+            <![CDATA[insert into EMP_MODIFIABLEVIEW values (?, ?)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalTableModify(table=[[SALES, EMPDEFAULTS]], operation=[INSERT], flattened=[true])
+  LogicalProject(EMPNO=[CAST($0):INTEGER NOT NULL], ENAME=[CAST($1):VARCHAR(20) CHARACTER
SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[CAST($5):INTEGER], COMM=[$6], DEPTNO=[20], SLACKER=[$7])
+    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[null], MGR=[null], HIREDATE=[null], SAL=[555],
COMM=[null], SLACKER=[null])
+      LogicalProject(EXPR$0=[?0], EXPR$1=[?1])
+        LogicalValues(tuples=[[{ 0 }]])
+]]>
+        </Resource>
+    </TestCase>
     <TestCase name="testInsertWithCustomInitializerExpressionFactory">
         <Resource name="sql">
             <![CDATA[insert into empdefaults (deptno) values (300)]]>


Mime
View raw message