You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by el...@apache.org on 2017/03/30 21:38:39 UTC
[49/50] [abbrv] calcite-avatica git commit: [CALCITE-1702] Support
extended columns in DML (Kevin Liew)
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/8a1a287d/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 a2662e2..ea2139a 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -8432,11 +8432,130 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
"Number of INSERT target columns \\(9\\) does not equal number of source items \\(2\\)");
}
+ @Test public void testSelectExtendedColumnDuplicate() {
+ sql("select deptno, extra from emp (extra int, \"extra\" boolean)").ok();
+ sql("select deptno, extra from emp (extra int, \"extra\" int)").ok();
+ tester.checkQueryFails("select deptno, extra from emp (extra int, ^extra^ int)",
+ "Duplicate name 'EXTRA' in column list");
+ tester.checkQueryFails("select deptno, extra from emp (extra int, ^extra^ boolean)",
+ "Duplicate name 'EXTRA' in column list");
+ tester.checkQueryFails("select deptno, extra from EMP_MODIFIABLEVIEW (extra int, ^extra^ int)",
+ "Duplicate name 'EXTRA' in column list");
+ tester.checkQueryFails("select deptno, extra from EMP_MODIFIABLEVIEW"
+ + " (extra int, ^extra^ boolean)",
+ "Duplicate name 'EXTRA' in column list");
+ }
+
@Test public void testSelectViewFailExcludedColumn() {
tester.checkQueryFails("select ^deptno^, empno from EMP_MODIFIABLEVIEW",
"Column 'DEPTNO' not found in any table");
}
+ @Test public void testSelectViewExtendedColumnCollision() {
+ sql("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR\n"
+ + " from EMP_MODIFIABLEVIEW3 extend (SAL int)\n"
+ + " where SAL = 20").ok();
+ sql("select ENAME, EMPNO, JOB, SLACKER, SAL, \"Sal\", HIREDATE, MGR\n"
+ + " from EMP_MODIFIABLEVIEW3 extend (\"Sal\" VARCHAR)\n"
+ + " where SAL = 20").ok();
+ }
+
+ @Test public void testSelectViewExtendedColumnExtendedCollision() {
+ sql("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, EXTRA\n"
+ + " from EMP_MODIFIABLEVIEW2 extend (EXTRA boolean)\n"
+ + " where SAL = 20").ok();
+ sql("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, EXTRA, \"EXtra\"\n"
+ + " from EMP_MODIFIABLEVIEW2 extend (\"EXtra\" VARCHAR)\n"
+ + " where SAL = 20").ok();
+ }
+
+ @Test public void testSelectViewExtendedColumnUnderlyingCollision() {
+ sql("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, COMM\n"
+ + " from EMP_MODIFIABLEVIEW3 extend (COMM int)\n"
+ + " where SAL = 20").ok();
+ sql("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, \"comM\"\n"
+ + " from EMP_MODIFIABLEVIEW3 extend (\"comM\" BOOLEAN)\n"
+ + " where SAL = 20").ok();
+ }
+
+ @Test public void testSelectExtendedColumnCollision() {
+ sql("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, COMM\n"
+ + " from EMPDEFAULTS extend (COMM int)\n"
+ + " where SAL = 20").ok();
+ sql("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, COMM, \"ComM\"\n"
+ + " from EMPDEFAULTS extend (\"ComM\" int)\n"
+ + " where SAL = 20").ok();
+ }
+
+ @Test public void testSelectExtendedColumnFailCollision() {
+ tester.checkQueryFails("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, COMM\n"
+ + " from EMPDEFAULTS extend (^COMM^ boolean)\n"
+ + " where SAL = 20",
+ "Cannot assign to target field 'COMM' of type INTEGER from source field 'COMM' of type BOOLEAN");
+ tester.checkQueryFails("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, COMM\n"
+ + " from EMPDEFAULTS extend (^EMPNO^ integer)\n"
+ + " where SAL = 20",
+ "Cannot assign to target field 'EMPNO' of type INTEGER NOT NULL from source field 'EMPNO' of type INTEGER");
+ tester.checkQueryFails("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, COMM\n"
+ + " from EMPDEFAULTS extend (^\"EMPNO\"^ integer)\n"
+ + " where SAL = 20",
+ "Cannot assign to target field 'EMPNO' of type INTEGER NOT NULL from source field 'EMPNO' of type INTEGER");
+ }
+
+ @Test public void testSelectViewExtendedColumnFailCollision() {
+ tester.checkQueryFails("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, EXTRA\n"
+ + " from EMP_MODIFIABLEVIEW2 extend (^SLACKER^ integer)\n"
+ + " where SAL = 20",
+ "Cannot assign to target field 'SLACKER' of type BOOLEAN from source field 'SLACKER' of type INTEGER");
+ tester.checkQueryFails("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, COMM\n"
+ + " from EMP_MODIFIABLEVIEW2 extend (^EMPNO^ integer)\n"
+ + " where SAL = 20",
+ "Cannot assign to target field 'EMPNO' of type INTEGER NOT NULL from source field 'EMPNO' of type INTEGER");
+ }
+
+ @Test public void testSelectViewExtendedColumnFailExtendedCollision() {
+ tester.checkQueryFails("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, EXTRA\n"
+ + " from EMP_MODIFIABLEVIEW2 extend (^EXTRA^ integer)\n"
+ + " where SAL = 20",
+ "Cannot assign to target field 'EXTRA' of type BOOLEAN from source field 'EXTRA' of type INTEGER");
+ tester.checkQueryFails("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, EXTRA\n"
+ + " from EMP_MODIFIABLEVIEW2 extend (^\"EXTRA\"^ integer)\n"
+ + " where SAL = 20",
+ "Cannot assign to target field 'EXTRA' of type BOOLEAN from source field 'EXTRA' of type INTEGER");
+ }
+
+ @Test public void testSelectViewExtendedColumnFailUnderlyingCollision() {
+ tester.checkQueryFails("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, COMM\n"
+ + " from EMP_MODIFIABLEVIEW3 extend (^COMM^ boolean)\n"
+ + " where SAL = 20",
+ "Cannot assign to target field 'COMM' of type INTEGER from source field 'COMM' of type BOOLEAN");
+ tester.checkQueryFails("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, COMM\n"
+ + " from EMP_MODIFIABLEVIEW3 extend (^\"COMM\"^ boolean)\n"
+ + " where SAL = 20",
+ "Cannot assign to target field 'COMM' of type INTEGER from source field 'COMM' of type BOOLEAN");
+ }
+
+ @Test public void testSelectFailCaseSensitivity() {
+ tester.checkQueryFails("select ^\"empno\"^, ename, deptno from EMP",
+ "Column 'empno' not found in any table; did you mean 'EMPNO'\\?");
+ tester.checkQueryFails("select ^\"extra\"^, ename, deptno from EMP (extra boolean)",
+ "Column 'extra' not found in any table; did you mean 'EXTRA'\\?");
+ tester.checkQueryFails("select ^extra^, ename, deptno from EMP (\"extra\" boolean)",
+ "Column 'EXTRA' not found in any table; did you mean 'extra'\\?");
+ }
+
+ @Test public void testInsertFailCaseSensitivity() {
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW (^\"empno\"^, ename, deptno)"
+ + " values (45, 'Jake', 5)",
+ "Unknown target column 'empno'");
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW (\"extra\" int) (^extra^, ename, deptno)"
+ + " values (45, 'Jake', 5)",
+ "Unknown target column 'EXTRA'");
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW (extra int) (^\"extra\"^, ename, deptno)"
+ + " values (45, 'Jake', 5)",
+ "Unknown target column 'extra'");
+ }
+
@Test public void testInsertFailExcludedColumn() {
tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW (empno, ename, ^deptno^)"
+ " values (45, 'Jake', 5)",
@@ -9141,6 +9260,395 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
sql(sql)
.fails("Invalid number of parameters to COUNT method");
}
+
+ @Test public void testInsertExtendedColumn() {
+ sql("insert into empdefaults(extra BOOLEAN, note VARCHAR)"
+ + " (deptno, empno, ename, extra, note) values (1, 10, '2', true, 'ok')").ok();
+ sql("insert into emp(\"rank\" INT, extra BOOLEAN)"
+ + " values (1, 'nom', 'job', 0, timestamp '1970-01-01 00:00:00', 1, 1,"
+ + " 1, false, 100, false)").ok();
+ }
+
+ @Test public void testInsertBindExtendedColumn() {
+ sql("insert into empdefaults(extra BOOLEAN, note VARCHAR)"
+ + " (deptno, empno, ename, extra, note) values (1, 10, '2', ?, 'ok')").ok();
+ sql("insert into emp(\"rank\" INT, extra BOOLEAN)"
+ + " values (1, 'nom', 'job', 0, timestamp '1970-01-01 00:00:00', 1, 1,"
+ + " 1, false, ?, ?)").ok();
+ }
+
+ @Test public void testInsertExtendedColumnModifiableView() {
+ sql("insert into EMP_MODIFIABLEVIEW2(extra2 BOOLEAN, note VARCHAR)"
+ + " (deptno, empno, ename, extra2, note) values (20, 10, '2', true, 'ok')").ok();
+ sql("insert into EMP_MODIFIABLEVIEW2(\"rank\" INT, extra2 BOOLEAN)"
+ + " values ('nom', 1, 'job', 20, true, 0, false, timestamp '1970-01-01 00:00:00', 1, 1,"
+ + " 1, false)").ok();
+ }
+
+ @Test public void testInsertBindExtendedColumnModifiableView() {
+ sql("insert into EMP_MODIFIABLEVIEW2(extra2 BOOLEAN, note VARCHAR)"
+ + " (deptno, empno, ename, extra2, note) values (20, 10, '2', true, ?)").ok();
+ sql("insert into EMP_MODIFIABLEVIEW2(\"rank\" INT, extra2 BOOLEAN)"
+ + " values ('nom', 1, 'job', 20, true, 0, false, timestamp '1970-01-01 00:00:00', 1, 1,"
+ + " ?, false)").ok();
+ }
+
+ @Test public void testInsertExtendedColumnModifiableViewFailConstraint() {
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2(extra2 BOOLEAN, note VARCHAR)"
+ + " (deptno, empno, ename, extra2, note) values (^1^, 10, '2', true, 'ok')",
+ "Modifiable view constraint is not satisfied"
+ + " for column 'DEPTNO' of base table 'EMP_MODIFIABLEVIEW2'");
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2(extra2 BOOLEAN, note VARCHAR)"
+ + " (deptno, empno, ename, extra2, note) values (^?^, 10, '2', true, 'ok')",
+ "Modifiable view constraint is not satisfied"
+ + " for column 'DEPTNO' of base table 'EMP_MODIFIABLEVIEW2'");
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2(\"rank\" INT, extra2 BOOLEAN)"
+ + " values ('nom', 1, 'job', ^0^, true, 0, false, timestamp '1970-01-01 00:00:00', 1, 1,"
+ + " 1, false)",
+ "Modifiable view constraint is not satisfied"
+ + " for column 'DEPTNO' of base table 'EMP_MODIFIABLEVIEW2'");
+ }
+
+ @Test public void testInsertExtendedColumnModifiableViewFailColumnCount() {
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2(\"rank\" INT, extra2 BOOLEAN^)^"
+ + " values ('nom', 1, 'job', 0, true, 0, false, timestamp '1970-01-01 00:00:00', 1, 1,"
+ + " 1)",
+ "Number of INSERT target columns \\(12\\) does not equal number of source items \\(11\\)");
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2(\"rank\" INT, extra2 BOOLEAN^)^"
+ + " (deptno, empno, ename, extra2, \"rank\") values (?, 10, '2', true)",
+ "Number of INSERT target columns \\(5\\) does not equal number of source items \\(4\\)");
+ }
+
+ @Test public void testInsertExtendedColumnFailDuplicate() {
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2(extcol INT, ^extcol^ BOOLEAN)"
+ + " values ('nom', 1, 'job', 0, true, 0, false, timestamp '1970-01-01 00:00:00', 1, 1,"
+ + " 1)",
+ "Duplicate name 'EXTCOL' in column list");
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2(extcol INT, ^extcol^ BOOLEAN)"
+ + " (extcol) values (1)",
+ "Duplicate name 'EXTCOL' in column list");
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2(extcol INT, ^extcol^ BOOLEAN)"
+ + " (extcol) values (false)",
+ "Duplicate name 'EXTCOL' in column list");
+ tester.checkQueryFails("insert into EMP(extcol INT, ^extcol^ BOOLEAN)"
+ + " (extcol) values (1)",
+ "Duplicate name 'EXTCOL' in column list");
+ tester.checkQueryFails("insert into EMP(extcol INT, ^extcol^ BOOLEAN)"
+ + " (extcol) values (false)",
+ "Duplicate name 'EXTCOL' in column list");
+ }
+
+ @Test public void testUpdateExtendedColumn() {
+ sql("update empdefaults(extra BOOLEAN, note VARCHAR)"
+ + " set deptno = 1, extra = true, empno = 20, ename = 'Bob', note = 'legion'"
+ + " where deptno = 10").ok();
+ sql("update empdefaults(extra BOOLEAN)"
+ + " set extra = true, deptno = 1, ename = 'Bob'"
+ + " where deptno = 10").ok();
+ sql("update empdefaults(\"empNo\" VARCHAR)"
+ + " set \"empNo\" = '5', deptno = 1, ename = 'Bob'"
+ + " where deptno = 10").ok();
+ }
+
+ @Test public void testInsertFailDataType() {
+ tester.withConformance(SqlConformanceEnum.PRAGMATIC_2003).checkQueryFails(
+ "insert into empnullables ^values ('5', 'bob')^",
+ "Cannot assign to target field 'EMPNO' of type INTEGER"
+ + " from source field 'EXPR\\$0' of type CHAR\\(1\\)");
+ tester.withConformance(SqlConformanceEnum.PRAGMATIC_2003).checkQueryFails(
+ "insert into empnullables (^empno^, ename) values ('5', 'bob')",
+ "Cannot assign to target field 'EMPNO' of type INTEGER"
+ + " from source field 'EXPR\\$0' of type CHAR\\(1\\)");
+ tester.withConformance(SqlConformanceEnum.PRAGMATIC_2003).checkQueryFails(
+ "insert into empnullables(extra BOOLEAN)"
+ + " (empno, ename, ^extra^) values (5, 'bob', 'true')",
+ "Cannot assign to target field 'EXTRA' of type BOOLEAN"
+ + " from source field 'EXPR\\$2' of type CHAR\\(4\\)");
+ }
+
+ @Ignore("CALCITE-1727")
+ @Test public void testUpdateFailDataType() {
+ tester.checkQueryFails("update emp"
+ + " set ^empNo^ = '5', deptno = 1, ename = 'Bob'"
+ + " where deptno = 10",
+ "Cannot assign to target field 'EMPNO' of type INTEGER"
+ + " from source field 'EXPR$0' of type CHAR(1)");
+ tester.checkQueryFails("update emp(extra boolean)"
+ + " set ^extra^ = '5', deptno = 1, ename = 'Bob'"
+ + " where deptno = 10",
+ "Cannot assign to target field 'EXTRA' of type BOOLEAN"
+ + " from source field 'EXPR$0' of type CHAR(1)");
+ }
+
+ @Ignore("CALCITE-1727")
+ @Test public void testUpdateFailCaseSensitivity() {
+ tester.checkQueryFails("update empdefaults"
+ + " set empNo = '5', deptno = 1, ename = 'Bob'"
+ + " where deptno = 10",
+ "Column 'empno' not found in any table; did you mean 'EMPNO'\\?");
+ }
+
+ @Test public void testUpdateExtendedColumnFailCaseSensitivity() {
+ tester.checkQueryFails("update empdefaults(\"extra\" BOOLEAN)"
+ + " set ^extra^ = true, deptno = 1, ename = 'Bob'"
+ + " where deptno = 10",
+ "Unknown target column 'EXTRA'");
+ tester.checkQueryFails("update empdefaults(extra BOOLEAN)"
+ + " set ^\"extra\"^ = true, deptno = 1, ename = 'Bob'"
+ + " where deptno = 10",
+ "Unknown target column 'extra'");
+ }
+
+ @Test public void testUpdateBindExtendedColumn() {
+ sql("update empdefaults(extra BOOLEAN, note VARCHAR)"
+ + " set deptno = 1, extra = true, empno = 20, ename = 'Bob', note = ?"
+ + " where deptno = 10").ok();
+ sql("update empdefaults(extra BOOLEAN)"
+ + " set extra = ?, deptno = 1, ename = 'Bob'"
+ + " where deptno = 10").ok();
+ }
+
+ @Test public void testUpdateExtendedColumnModifiableView() {
+ sql("update EMP_MODIFIABLEVIEW2(extra2 BOOLEAN, note VARCHAR)"
+ + " set deptno = 20, extra2 = true, empno = 20, ename = 'Bob', note = 'legion'"
+ + " where ename = 'Jane'").ok();
+ sql("update EMP_MODIFIABLEVIEW2(extra2 BOOLEAN)"
+ + " set extra2 = true, ename = 'Bob'"
+ + " where ename = 'Jane'").ok();
+ }
+
+ @Test public void testUpdateBindExtendedColumnModifiableView() {
+ sql("update EMP_MODIFIABLEVIEW2(extra2 BOOLEAN, note VARCHAR)"
+ + " set deptno = 20, extra2 = true, empno = 20, ename = 'Bob', note = ?"
+ + " where ename = 'Jane'").ok();
+ sql("update EMP_MODIFIABLEVIEW2(extra2 BOOLEAN)"
+ + " set extra2 = ?, ename = 'Bob'"
+ + " where ename = 'Jane'").ok();
+ }
+
+ @Test public void testUpdateExtendedColumnModifiableViewFailConstraint() {
+ tester.checkQueryFails("update EMP_MODIFIABLEVIEW2(extra2 BOOLEAN, note VARCHAR)"
+ + " set deptno = ^1^, extra2 = true, empno = 20, ename = 'Bob', note = 'legion'"
+ + " where ename = 'Jane'",
+ "Modifiable view constraint is not satisfied"
+ + " for column 'DEPTNO' of base table 'EMP_MODIFIABLEVIEW2'");
+ tester.checkQueryFails("update EMP_MODIFIABLEVIEW2(extra2 BOOLEAN)"
+ + " set extra2 = true, deptno = ^1^, ename = 'Bob'"
+ + " where ename = 'Jane'",
+ "Modifiable view constraint is not satisfied"
+ + " for column 'DEPTNO' of base table 'EMP_MODIFIABLEVIEW2'");
+ }
+
+ @Test public void testUpdateExtendedColumnCollision() {
+ sql("update empdefaults(empno INTEGER NOT NULL, deptno INTEGER)"
+ + " set deptno = 1, empno = 20, ename = 'Bob'"
+ + " where deptno = 10").ok();
+ }
+
+ @Test public void testUpdateExtendedColumnModifiableViewCollision() {
+ sql("update EMP_MODIFIABLEVIEW3(empno INTEGER NOT NULL, deptno INTEGER)"
+ + " set deptno = 20, empno = 20, ename = 'Bob'"
+ + " where empno = 10").ok();
+ sql("update EMP_MODIFIABLEVIEW3(empno INTEGER NOT NULL, \"deptno\" BOOLEAN)"
+ + " set \"deptno\" = true, empno = 20, ename = 'Bob'"
+ + " where empno = 10").ok();
+ }
+
+ @Test public void testUpdateExtendedColumnFailCollision() {
+ tester.checkQueryFails("update empdefaults(^empno^ BOOLEAN, deptno INTEGER)"
+ + " set deptno = 1, empno = false, ename = 'Bob'"
+ + " where deptno = 10",
+ "Cannot assign to target field 'EMPNO' of type INTEGER NOT NULL from source field 'EMPNO' of type BOOLEAN");
+ }
+
+ @Ignore("CALCITE-1727")
+ @Test public void testUpdateExtendedColumnFailCollision2() {
+ tester.checkQueryFails("update empdefaults(^\"deptno\"^ BOOLEAN)"
+ + " set \"deptno\" = 1, empno = 1, ename = 'Bob'"
+ + " where deptno = 10",
+ "Cannot assign to target field 'deptno' of type BOOLEAN NOT NULL from source field 'deptno' of type INTEGER");
+ }
+
+ @Test public void testUpdateExtendedColumnModifiableViewFailCollision() {
+ tester.checkQueryFails("update EMP_MODIFIABLEVIEW3(^empno^ BOOLEAN, deptno INTEGER)"
+ + " set deptno = 1, empno = false, ename = 'Bob'"
+ + " where deptno = 10",
+ "Cannot assign to target field 'EMPNO' of type INTEGER NOT NULL from source field 'EMPNO' of type BOOLEAN");
+ }
+
+ @Test public void testUpdateExtendedColumnModifiableViewFailExtendedCollision() {
+ tester.checkQueryFails("update EMP_MODIFIABLEVIEW2(^extra^ INTEGER, deptno INTEGER)"
+ + " set deptno = 20, empno = 20, ename = 'Bob', extra = 5"
+ + " where empno = 10",
+ "Cannot assign to target field 'EXTRA' of type BOOLEAN from source field 'EXTRA' of type INTEGER");
+ }
+
+ @Test public void testUpdateExtendedColumnModifiableViewFailUnderlyingCollision() {
+ tester.checkQueryFails("update EMP_MODIFIABLEVIEW3(^comm^ BOOLEAN, deptno INTEGER)"
+ + " set deptno = 1, empno = 20, ename = 'Bob', comm = true"
+ + " where deptno = 10",
+ "Cannot assign to target field 'COMM' of type INTEGER from source field 'COMM' of type BOOLEAN");
+ }
+
+ @Test public void testUpdateExtendedColumnFailDuplicate() {
+ tester.checkQueryFails("update emp(comm BOOLEAN, ^comm^ INTEGER)"
+ + " set deptno = 1, empno = 20, ename = 'Bob', comm = 1"
+ + " where deptno = 10",
+ "Duplicate name 'COMM' in column list");
+ tester.checkQueryFails("update EMP_MODIFIABLEVIEW3(comm BOOLEAN, ^comm^ INTEGER)"
+ + " set deptno = 1, empno = 20, ename = 'Bob', comm = true"
+ + " where deptno = 10",
+ "Duplicate name 'COMM' in column list");
+ }
+
+ @Test public void testInsertExtendedColumnCollision() {
+ sql("insert into EMPDEFAULTS(^comm^ INTEGER) (empno, ename, job, comm)\n"
+ + "values (1, 'Arthur', 'clown', 5)").ok();
+ }
+
+ @Test public void testInsertExtendedColumnModifiableViewCollision() {
+ sql("insert into EMP_MODIFIABLEVIEW3(^sal^ INTEGER) (empno, ename, job, sal)\n"
+ + "values (1, 'Arthur', 'clown', 5)").ok();
+ }
+
+ @Test public void testInsertExtendedColumnModifiableViewExtendedCollision() {
+ sql("insert into EMP_MODIFIABLEVIEW2(^extra^ BOOLEAN) (empno, ename, job, extra)\n"
+ + "values (1, 'Arthur', 'clown', true)").ok();
+ }
+
+ @Test public void testInsertExtendedColumnModifiableViewUnderlyingCollision() {
+ sql("insert into EMP_MODIFIABLEVIEW3(^comm^ INTEGER) (empno, ename, job, comm)\n"
+ + "values (1, 'Arthur', 'clown', 5)").ok();
+ }
+
+ @Test public void testInsertExtendedColumnFailCollision() {
+ tester.checkQueryFails("insert into EMPDEFAULTS(^comm^ BOOLEAN)"
+ + " (empno, ename, job, comm)\n"
+ + "values (1, 'Arthur', 'clown', true)",
+ "Cannot assign to target field 'COMM' of type INTEGER"
+ + " from source field 'COMM' of type BOOLEAN");
+ tester.checkQueryFails("insert into EMPDEFAULTS(\"comm\" BOOLEAN)"
+ + " (empno, ename, job, ^comm^)\n"
+ + "values (1, 'Arthur', 'clown', true)",
+ "Cannot assign to target field 'COMM' of type INTEGER"
+ + " from source field 'EXPR\\$3' of type BOOLEAN");
+ tester.checkQueryFails("insert into EMPDEFAULTS(\"comm\" BOOLEAN)"
+ + " (empno, ename, job, ^\"comm\"^)\n"
+ + "values (1, 'Arthur', 'clown', 1)",
+ "Cannot assign to target field 'comm' of type BOOLEAN"
+ + " from source field 'EXPR\\$3' of type INTEGER");
+ }
+
+ @Test public void testInsertExtendedColumnModifiableViewFailCollision() {
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2(^slacker^ INTEGER)"
+ + " (empno, ename, job, slacker) values (1, 'Arthur', 'clown', true)",
+ "Cannot assign to target field 'SLACKER' of type BOOLEAN from source field 'SLACKER' of type INTEGER");
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2(\"slacker\" INTEGER)"
+ + " (empno, ename, job, ^slacker^) values (1, 'Arthur', 'clown', 1)",
+ "Cannot assign to target field 'SLACKER' of type BOOLEAN from source field 'EXPR\\$3' of type INTEGER");
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2(\"slacker\" INTEGER)"
+ + " (empno, ename, job, ^\"slacker\"^) values (1, 'Arthur', 'clown', true)",
+ "Cannot assign to target field 'slacker' of type INTEGER from source field 'EXPR\\$3' of type BOOLEAN");
+ }
+
+ @Test public void testInsertExtendedColumnModifiableViewFailExtendedCollision() {
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2(^extra^ INTEGER)"
+ + " (empno, ename, job, extra) values (1, 'Arthur', 'clown', true)",
+ "Cannot assign to target field 'EXTRA' of type BOOLEAN from source field 'EXTRA' of type INTEGER");
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2(\"extra\" INTEGER)"
+ + " (empno, ename, job, ^extra^) values (1, 'Arthur', 'clown', 1)",
+ "Cannot assign to target field 'EXTRA' of type BOOLEAN from source field 'EXPR\\$3' of type INTEGER");
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW2(\"extra\" INTEGER)"
+ + " (empno, ename, job, ^\"extra\"^) values (1, 'Arthur', 'clown', true)",
+ "Cannot assign to target field 'extra' of type INTEGER from source field 'EXPR\\$3' of type BOOLEAN");
+ }
+
+ @Test public void testInsertExtendedColumnModifiableViewFailUnderlyingCollision() {
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW3(^comm^ BOOLEAN)"
+ + " (empno, ename, job, comm) values (1, 'Arthur', 'clown', true)",
+ "Cannot assign to target field 'COMM' of type INTEGER from source field 'COMM' of type BOOLEAN");
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW3(\"comm\" BOOLEAN)"
+ + " (empno, ename, job, ^comm^) values (1, 'Arthur', 'clown', 5)",
+ "Unknown target column 'COMM'");
+ tester.checkQueryFails("insert into EMP_MODIFIABLEVIEW3(\"comm\" BOOLEAN)"
+ + " (empno, ename, job, ^\"comm\"^) values (1, 'Arthur', 'clown', 1)",
+ "Cannot assign to target field 'comm' of type BOOLEAN from source field 'EXPR\\$3' of type INTEGER");
+ }
+
+ @Test public void testDelete() {
+ sql("delete from empdefaults where deptno = 10").ok();
+ }
+
+ @Test public void testDeleteExtendedColumn() {
+ sql("delete from empdefaults(extra BOOLEAN) where deptno = 10").ok();
+ sql("delete from empdefaults(extra BOOLEAN) where extra = false").ok();
+ }
+
+ @Test public void testDeleteBindExtendedColumn() {
+ sql("delete from empdefaults(extra BOOLEAN) where deptno = ?").ok();
+ sql("delete from empdefaults(extra BOOLEAN) where extra = ?").ok();
+ }
+
+ @Test public void testDeleteModifiableView() {
+ sql("delete from EMP_MODIFIABLEVIEW2 where deptno = 10").ok();
+ sql("delete from EMP_MODIFIABLEVIEW2 where deptno = 20").ok();
+ sql("delete from EMP_MODIFIABLEVIEW2 where empno = 30").ok();
+ }
+
+ @Test public void testDeleteExtendedColumnModifiableView() {
+ sql("delete from EMP_MODIFIABLEVIEW2(extra BOOLEAN) where sal > 10").ok();
+ sql("delete from EMP_MODIFIABLEVIEW2(note BOOLEAN) where note = 'fired'").ok();
+ }
+
+ @Test public void testDeleteExtendedColumnCollision() {
+ sql("delete from emp(empno INTEGER NOT NULL) where sal > 10").ok();
+ }
+
+ @Test public void testDeleteExtendedColumnModifiableViewCollision() {
+ sql("delete from EMP_MODIFIABLEVIEW2(empno INTEGER NOT NULL) where sal > 10").ok();
+ sql("delete from EMP_MODIFIABLEVIEW2(\"empno\" INTEGER) where sal > 10").ok();
+ sql("delete from EMP_MODIFIABLEVIEW2(extra BOOLEAN) where sal > 10").ok();
+ sql("delete from EMP_MODIFIABLEVIEW2(\"extra\" VARCHAR) where sal > 10").ok();
+ sql("delete from EMP_MODIFIABLEVIEW3(comm INTEGER) where sal > 10").ok();
+ sql("delete from EMP_MODIFIABLEVIEW3(\"comm\" BIGINT) where sal > 10").ok();
+ }
+
+ @Test public void testDeleteExtendedColumnFailCollision() {
+ tester.checkQueryFails("delete from EMP_MODIFIABLEVIEW2(^empno^ BOOLEAN) where sal > 10",
+ "Cannot assign to target field 'EMPNO' of type INTEGER NOT NULL from source field 'EMPNO' of type BOOLEAN");
+ tester.checkQueryFails("delete from EMP_MODIFIABLEVIEW2(^empno^ INTEGER) where sal > 10",
+ "Cannot assign to target field 'EMPNO' of type INTEGER NOT NULL from source field 'EMPNO' of type INTEGER");
+ tester.checkQueryFails("delete from EMP_MODIFIABLEVIEW2(^\"EMPNO\"^ INTEGER) where sal > 10",
+ "Cannot assign to target field 'EMPNO' of type INTEGER NOT NULL from source field 'EMPNO' of type INTEGER");
+ tester.checkQueryFails("delete from EMP_MODIFIABLEVIEW2(^empno^ INTEGER) where sal > 10",
+ "Cannot assign to target field 'EMPNO' of type INTEGER NOT NULL from source field 'EMPNO' of type INTEGER");
+ }
+
+ @Test public void testDeleteExtendedColumnModifiableViewFailCollision() {
+ tester.checkQueryFails("delete from EMP_MODIFIABLEVIEW(^deptno^ BOOLEAN) where sal > 10",
+ "Cannot assign to target field 'DEPTNO' of type INTEGER from source field 'DEPTNO' of type BOOLEAN");
+ tester.checkQueryFails("delete from EMP_MODIFIABLEVIEW(^\"DEPTNO\"^ BOOLEAN) where sal > 10",
+ "Cannot assign to target field 'DEPTNO' of type INTEGER from source field 'DEPTNO' of type BOOLEAN");
+ }
+
+ @Test public void testDeleteExtendedColumnModifiableViewFailExtendedCollision() {
+ tester.checkQueryFails("delete from EMP_MODIFIABLEVIEW(^slacker^ INTEGER) where sal > 10",
+ "Cannot assign to target field 'SLACKER' of type BOOLEAN from source field 'SLACKER' of type INTEGER");
+ tester.checkQueryFails("delete from EMP_MODIFIABLEVIEW(^\"SLACKER\"^ INTEGER) where sal > 10",
+ "Cannot assign to target field 'SLACKER' of type BOOLEAN from source field 'SLACKER' of type INTEGER");
+ }
+
+ @Test public void testDeleteExtendedColumnFailDuplicate() {
+ tester.checkQueryFails("delete from emp (extra VARCHAR, ^extra^ VARCHAR)",
+ "Duplicate name 'EXTRA' in column list");
+ tester.checkQueryFails("delete from EMP_MODIFIABLEVIEW (extra VARCHAR, ^extra^ VARCHAR)"
+ + " where extra = 'test'",
+ "Duplicate name 'EXTRA' in column list");
+ tester.checkQueryFails("delete from EMP_MODIFIABLEVIEW (extra VARCHAR, ^\"EXTRA\"^ VARCHAR)"
+ + " where extra = 'test'",
+ "Duplicate name 'EXTRA' in column list");
+ }
+
}
// End SqlValidatorTest.java
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/8a1a287d/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 9273138..2f5d308 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2310,7 +2310,7 @@ LogicalProject(EXPR$0=[+($0, $2)])
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8], X=[$9])
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$7], X=[$8])
LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW]])
]]>
</Resource>
@@ -2321,7 +2321,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(X=[$9], EMPNO=[$0])
+LogicalProject(X=[$8], EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW]])
]]>
</Resource>
@@ -2332,7 +2332,7 @@ LogicalProject(X=[$9], EMPNO=[$0])
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(EXPR$0=[+($0, $9)])
+LogicalProject(EXPR$0=[+($0, $8)])
LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW]])
]]>
</Resource>
@@ -2766,6 +2766,48 @@ LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[INSERT], f
]]>
</Resource>
</TestCase>
+ <TestCase name="testInsertBindExtendedColumn">
+ <Resource name="sql">
+ <![CDATA[insert into empdefaults(updated TIMESTAMP) (ename, deptno, empno, updated, sal) values ('Fred', 456, 44, ?, 999999)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMPDEFAULTS]], operation=[INSERT], flattened=[true])
+ LogicalProject(EMPNO=[$2], ENAME=[$0], JOB=[null], MGR=[null], HIREDATE=[null], SAL=[$4], COMM=[null], DEPTNO=[$1], SLACKER=[null], UPDATED=[$3])
+ LogicalProject(EXPR$0=['Fred'], EXPR$1=[456], EXPR$2=[44], EXPR$3=[?0], EXPR$4=[999999])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testInsertExtendedColumnModifiableView">
+ <Resource name="sql">
+ <![CDATA[insert into EMP_MODIFIABLEVIEW2(updated TIMESTAMP) (ename, deptno, empno, updated, sal) values ('Fred', 20, 44, timestamp '2017-03-12 13:03:05', 999999)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[SALES, EMPDEFAULTS]], operation=[INSERT], flattened=[true])
+ LogicalFilter(condition=[=($7, 20)])
+ LogicalProject(EMPNO=[$1], ENAME=[$0], JOB=[$2], MGR=[$8], HIREDATE=[$7], SAL=[$5], COMM=[$9], DEPTNO=[$3], SLACKER=[$4], EXTRA=[$6], UPDATED=[$10])
+ LogicalProject(ENAME=[$0], EMPNO=[$2], JOB=[null], DEPTNO=[$1], SLACKER=[null], SAL=[$4], EXTRA=[null], HIREDATE=[null], MGR=[null], COMM=[null], UPDATED=[$3])
+ LogicalValues(tuples=[[{ 'Fred', 20, 44, 2017-03-12 13:03:05, 999999 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testInsertBindExtendedColumnModifiableView">
+ <Resource name="sql">
+ <![CDATA[insert into EMP_MODIFIABLEVIEW2(updated TIMESTAMP) (ename, deptno, empno, updated, sal) values ('Fred', 20, 44, ?, 999999)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[SALES, EMPDEFAULTS]], operation=[INSERT], flattened=[true])
+ LogicalFilter(condition=[=($7, 20)])
+ LogicalProject(EMPNO=[$1], ENAME=[CAST($0):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], JOB=[$2], MGR=[$8], HIREDATE=[$7], SAL=[CAST($5):INTEGER], COMM=[$9], DEPTNO=[CAST($3):INTEGER], SLACKER=[$4], EXTRA=[$6], UPDATED=[$10])
+ LogicalProject(ENAME=[$0], EMPNO=[$2], JOB=[null], DEPTNO=[$1], SLACKER=[null], SAL=[$4], EXTRA=[null], HIREDATE=[null], MGR=[null], COMM=[null], UPDATED=[$3])
+ LogicalProject(EXPR$0=['Fred'], EXPR$1=[20], EXPR$2=[44], EXPR$3=[?0], EXPR$4=[999999])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testSelectView">
<Resource name="sql">
<![CDATA[select * from emp_20 where empno > 100]]>
@@ -2780,6 +2822,78 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
]]>
</Resource>
</TestCase>
+ <TestCase name="testSelectViewExtendedColumnCollision">
+ <Resource name="sql">
+ <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR from EMP_MODIFIABLEVIEW3 extend (SAL int) where SAL = 20]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(ENAME=[$1], EMPNO=[$0], JOB=[$2], SLACKER=[$6], SAL=[$5], HIREDATE=[$4], MGR=[$3])
+ LogicalFilter(condition=[=($5, 20)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW3]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSelectViewExtendedColumnCaseSensitiveCollision">
+ <Resource name="sql">
+ <![CDATA[select ENAME, EMPNO, JOB, SLACKER, "sal", HIREDATE, MGR from EMP_MODIFIABLEVIEW3 extend ("sal" boolean) where "sal" = true]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(ENAME=[$1], EMPNO=[$0], JOB=[$2], SLACKER=[$6], sal=[$7], HIREDATE=[$4], MGR=[$3])
+ LogicalFilter(condition=[=($7, true)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW3]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSelectViewExtendedColumnExtendedCollision">
+ <Resource name="sql">
+ <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, EXTRA from EMP_MODIFIABLEVIEW2 extend (EXTRA boolean) where SAL = 20]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(ENAME=[$0], EMPNO=[$1], JOB=[$2], SLACKER=[$4], SAL=[$5], HIREDATE=[$7], EXTRA=[$6])
+ LogicalFilter(condition=[=($5, 20)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSelectViewExtendedColumnCaseSensitiveExtendedCollision">
+ <Resource name="sql">
+ <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, extra from EMP_MODIFIABLEVIEW2 extend (extra boolean) where extra = false]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(ENAME=[$0], EMPNO=[$1], JOB=[$2], SLACKER=[$4], SAL=[$5], HIREDATE=[$7], extra=[$10])
+ LogicalFilter(condition=[=($10, false)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSelectViewExtendedColumnUnderlyingCollision">
+ <Resource name="sql">
+ <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, COMM from EMP_MODIFIABLEVIEW3 extend (COMM int) where SAL = 20]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(ENAME=[$1], EMPNO=[$0], JOB=[$2], SLACKER=[$6], SAL=[$5], HIREDATE=[$4], MGR=[$3], COMM=[$7])
+ LogicalFilter(condition=[=($5, 20)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW3]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSelectViewExtendedColumnCaseSensitiveUnderlyingCollision">
+ <Resource name="sql">
+ <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, "comm" from EMP_MODIFIABLEVIEW3 extend ("comm" int) where "comm" = 20]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(ENAME=[$1], EMPNO=[$0], JOB=[$2], SLACKER=[$6], SAL=[$5], HIREDATE=[$4], MGR=[$3], comm=[$7])
+ LogicalFilter(condition=[=($7, 20)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW3]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testInsertView">
<Resource name="sql">
<![CDATA[insert into empnullables_20 (empno, ename) values (150, 'Fred')]]>
@@ -3300,6 +3414,58 @@ LogicalTableModify(table=[[CATALOG, SALES, EMP]], operation=[DELETE], flattened=
]]>
</Resource>
</TestCase>
+ <TestCase name="testDeleteBind">
+ <Resource name="sql">
+ <![CDATA[delete from emp where deptno = ?]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMP]], operation=[DELETE], flattened=[true])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($7, ?0)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDeleteBindExtendedColumn">
+ <Resource name="sql">
+ <![CDATA[delete from emp(enddate TIMESTAMP) where enddate < ?]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMP]], operation=[DELETE], flattened=[true])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], ENDDATE=[$9])
+ LogicalFilter(condition=[<($9, ?0)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDeleteBindModifiableView">
+ <Resource name="sql">
+ <![CDATA[delete from EMP_MODIFIABLEVIEW2 where empno = ?]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]], operation=[DELETE], flattened=[true])
+ LogicalProject(ENAME=[$0], EMPNO=[$1], JOB=[$2], DEPTNO=[$3], SLACKER=[$4], SAL=[$5], EXTRA=[$6], HIREDATE=[$7], MGR=[$8], COMM=[$9])
+ LogicalFilter(condition=[=($1, ?0)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDeleteBindExtendedColumnModifiableView">
+ <Resource name="sql">
+ <![CDATA[delete from EMP_MODIFIABLEVIEW2(note VARCHAR) where note = ?]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]], operation=[DELETE], flattened=[true])
+ LogicalProject(ENAME=[$0], EMPNO=[$1], JOB=[$2], DEPTNO=[$3], SLACKER=[$4], SAL=[$5], EXTRA=[$6], HIREDATE=[$7], MGR=[$8], COMM=[$9], NOTE=[$10])
+ LogicalFilter(condition=[=($10, ?0)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testSelectWithoutFrom">
<Resource name="sql">
<![CDATA[select 2+2]]>
@@ -3324,6 +3490,161 @@ LogicalTableModify(table=[[CATALOG, SALES, EMP]], operation=[UPDATE], updateColu
]]>
</Resource>
</TestCase>
+ <TestCase name="testUpdateBind">
+ <Resource name="sql">
+ <![CDATA[update emp set sal = sal + ? where slacker = false]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMP]], operation=[UPDATE], updateColumnList=[[SAL]], sourceExpressionList=[[+($cor0.SAL, ?0)]], flattened=[true])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EXPR$0=[+($5, ?0)])
+ LogicalFilter(condition=[=($8, false)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUpdateExtendedColumnCollision">
+ <Resource name="sql">
+ <![CDATA[update empdefaults(empno INTEGER NOT NULL, deptno INTEGER) set deptno = 1, empno = 20, ename = 'Bob' where deptno = 10]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMPDEFAULTS]], operation=[UPDATE], updateColumnList=[[DEPTNO, EMPNO, ENAME]], sourceExpressionList=[[1, 20, 'Bob']], flattened=[true])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EXPR$0=[1], EXPR$1=[20], EXPR$2=['Bob'])
+ LogicalFilter(condition=[=($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPDEFAULTS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUpdateExtendedColumnCaseSensitiveCollision">
+ <Resource name="sql">
+ <![CDATA[update empdefaults("slacker" INTEGER, deptno INTEGER) set deptno = 1, "slacker" = 100 where ename = 'Bob']]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMPDEFAULTS]], operation=[UPDATE], updateColumnList=[[DEPTNO, slacker]], sourceExpressionList=[[1, 100]], flattened=[true])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], slacker=[$9], EXPR$0=[1], EXPR$1=[100])
+ LogicalFilter(condition=[=($1, 'Bob')])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPDEFAULTS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUpdateExtendedColumnModifiableViewCollision">
+ <Resource name="sql">
+ <![CDATA[update EMP_MODIFIABLEVIEW3(empno INTEGER NOT NULL, deptno INTEGER) set deptno = 20, empno = 20, ename = 'Bob' where empno = 10]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW3]], operation=[UPDATE], updateColumnList=[[DEPTNO, EMPNO, ENAME]], sourceExpressionList=[[20, 20, 'Bob']], flattened=[true])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], SLACKER=[$6], DEPTNO=[$7], EXPR$0=[20], EXPR$1=[20], EXPR$2=['Bob'])
+ LogicalFilter(condition=[=($0, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW3]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUpdateExtendedColumnModifiableViewCaseSensitiveCollision">
+ <Resource name="sql">
+ <![CDATA[update EMP_MODIFIABLEVIEW2("slacker" INTEGER, deptno INTEGER) set deptno = 20, \"slacker\" = 100 where ename = 'Bob']]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]], operation=[UPDATE], updateColumnList=[[DEPTNO, slacker]], sourceExpressionList=[[20, 100]], flattened=[true])
+ LogicalProject(ENAME=[$0], EMPNO=[$1], JOB=[$2], DEPTNO=[$3], SLACKER=[$4], SAL=[$5], EXTRA=[$6], HIREDATE=[$7], MGR=[$8], COMM=[$9], slacker=[$10], EXPR$0=[20], EXPR$1=[100])
+ LogicalFilter(condition=[=($0, 'Bob')])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUpdateExtendedColumnModifiableViewExtendedCollision">
+ <Resource name="sql">
+ <![CDATA[update EMP_MODIFIABLEVIEW2("slacker" INTEGER, extra BOOLEAN) set deptno = 20, \"slacker\" = 100, extra = true where ename = 'Bob']]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]], operation=[UPDATE], updateColumnList=[[DEPTNO, slacker, EXTRA]], sourceExpressionList=[[20, 100, true]], flattened=[true])
+ LogicalProject(ENAME=[$0], EMPNO=[$1], JOB=[$2], DEPTNO=[$3], SLACKER=[$4], SAL=[$5], EXTRA=[$6], HIREDATE=[$7], MGR=[$8], COMM=[$9], slacker=[$10], EXPR$0=[20], EXPR$1=[100], EXPR$2=[true])
+ LogicalFilter(condition=[=($0, 'Bob')])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUpdateExtendedColumnModifiableViewExtendedCaseSensitiveCollision">
+ <Resource name="sql">
+ <![CDATA[update EMP_MODIFIABLEVIEW2("extra" INTEGER, extra BOOLEAN) set deptno = 20, \"extra\" = 100, extra = true where ename = 'Bob']]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]], operation=[UPDATE], updateColumnList=[[DEPTNO, extra, EXTRA]], sourceExpressionList=[[20, 100, true]], flattened=[true])
+ LogicalProject(ENAME=[$0], EMPNO=[$1], JOB=[$2], DEPTNO=[$3], SLACKER=[$4], SAL=[$5], EXTRA=[$6], HIREDATE=[$7], MGR=[$8], COMM=[$9], extra=[$10], EXPR$0=[20], EXPR$1=[100], EXPR$2=[true])
+ LogicalFilter(condition=[=($0, 'Bob')])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUpdateExtendedColumnModifiableViewUnderlyingCollision">
+ <Resource name="sql">
+ <![CDATA[update EMP_MODIFIABLEVIEW3(extra BOOLEAN, comm INTEGER) set empno = 20, comm = true, extra = true where ename = 'Bob']]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW3]], operation=[UPDATE], updateColumnList=[[EMPNO, COMM, EXTRA]], sourceExpressionList=[[20, true, true]], flattened=[true])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], SLACKER=[$6], EXTRA=[$7], COMM=[$8], EXPR$0=[20], EXPR$1=[true], EXPR$2=[true])
+ LogicalFilter(condition=[=($1, 'Bob')])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW3]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testInsertExtendedColumn">
+ <Resource name="sql">
+ <![CDATA[insert into empdefaults(updated TIMESTAMP) (ename, deptno, empno, updated, sal) values ('Fred', 456, 44, timestamp '2017-03-12 13:03:05', 999999)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMPDEFAULTS]], operation=[INSERT], flattened=[true])
+ LogicalProject(EMPNO=[$2], ENAME=[$0], JOB=[null], MGR=[null], HIREDATE=[null], SAL=[$4], COMM=[null], DEPTNO=[$1], SLACKER=[null], UPDATED=[$3])
+ LogicalValues(tuples=[[{ 'Fred', 456, 44, 2017-03-12 13:03:05, 999999 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUpdateExtendedColumn">
+ <Resource name="sql">
+ <![CDATA[update empdefaults(updated TIMESTAMP) set deptno = 1, updated = timestamp '2017-03-12 13:03:05', empno = 20, ename = 'Bob' where deptno = 10]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMPDEFAULTS]], operation=[UPDATE], updateColumnList=[[DEPTNO, UPDATED, EMPNO, ENAME]], sourceExpressionList=[[1, 2017-03-12 13:03:05, 20, 'Bob']], flattened=[true])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], UPDATED=[$9], EXPR$0=[1], EXPR$1=[2017-03-12 13:03:05], EXPR$2=[20], EXPR$3=['Bob'])
+ LogicalFilter(condition=[=($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPDEFAULTS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUpdateModifiableView">
+ <Resource name="sql">
+ <![CDATA[update EMP_MODIFIABLEVIEW2 set sal = sal + 5000 where slacker = false]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]], operation=[UPDATE], updateColumnList=[[SAL]], sourceExpressionList=[[+($cor0.SAL, 5000)]], flattened=[true])
+ LogicalProject(ENAME=[$0], EMPNO=[$1], JOB=[$2], DEPTNO=[$3], SLACKER=[$4], SAL=[$5], EXTRA=[$6], HIREDATE=[$7], MGR=[$8], COMM=[$9], EXPR$0=[+($5, 5000)])
+ LogicalFilter(condition=[=($4, false)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUpdateExtendedColumnModifiableView">
+ <Resource name="sql">
+ <![CDATA[update EMP_MODIFIABLEVIEW2(updated TIMESTAMP) set updated = timestamp '2017-03-12 13:03:05', sal = sal + 5000 where slacker = false]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]], operation=[UPDATE], updateColumnList=[[UPDATED, SAL]], sourceExpressionList=[[2017-03-12 13:03:05, +($cor0.SAL, 5000)]], flattened=[true])
+ LogicalProject(ENAME=[$0], EMPNO=[$1], JOB=[$2], DEPTNO=[$3], SLACKER=[$4], SAL=[$5], EXTRA=[$6], HIREDATE=[$7], MGR=[$8], COMM=[$9], UPDATED=[$10], EXPR$0=[2017-03-12 13:03:05], EXPR$1=[+($5, 5000)])
+ LogicalFilter(condition=[=($4, false)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testDelete">
<Resource name="sql">
<![CDATA[delete from emp]]>