add regression test for WITH support jira
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/79b421ff Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/79b421ff Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/79b421ff Branch: refs/heads/master Commit: 79b421ffdce28f7d151605bd4f1e64c308175b20 Parents: 947797d Author: Liu Ming <[email protected]> Authored: Wed Jul 6 15:19:22 2016 +0000 Committer: Liu Ming <[email protected]> Committed: Wed Jul 6 15:19:22 2016 +0000 ---------------------------------------------------------------------- core/sql/bin/SqlciErrors.txt | 1 + core/sql/regress/compGeneral/EXPECTED005 | 99 ++++++++++++++++++++++++++- core/sql/regress/compGeneral/TEST005 | 33 ++++++++- 3 files changed, 129 insertions(+), 4 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/79b421ff/core/sql/bin/SqlciErrors.txt ---------------------------------------------------------------------- diff --git a/core/sql/bin/SqlciErrors.txt b/core/sql/bin/SqlciErrors.txt index 6635d00..86b753e 100644 --- a/core/sql/bin/SqlciErrors.txt +++ b/core/sql/bin/SqlciErrors.txt @@ -410,6 +410,7 @@ 1430 3F000 99999 BEGINNER MAJOR DBADMIN A schema name that starts and ends with an "_"(underscore) is reserved for internal usage. It cannot be used to create a user schema. 1431 ZZZZZ 99999 BEGINNER MINOR DBADMIN Object $0~String0 exists in HBase. This could be due to a concurrent transactional ddl operation in progress on this table. 1432 ZZZZZ 99999 BEGINNER MINOR DBADMIN Input LOB type $0~Int0 does not match column's storage type: $1~Int1 Column name: $0~String0 . +1433 ZZZZZ 99999 BEGINNER MINOR DBADMIN WITH clause redefined. WITH name $0~String0 . 1500 ZZZZZ 99999 ADVANCED CRTCL DIALOUT The CATSYS - CAT_REFERENCES system schema relationship for catalog $0~CatalogName might be corrupt. 1501 ZZZZZ 99999 UUUUUUUU UUUUU UUUUUUU --- unused as of 5/7/12 --- 1502 ZZZZZ 99999 ADVANCED CRTCL DIALOUT The OBJECTS - REPLICAS definition schema relationship for $0~String0 $1~TableName might be corrupt. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/79b421ff/core/sql/regress/compGeneral/EXPECTED005 ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/EXPECTED005 b/core/sql/regress/compGeneral/EXPECTED005 index e9b12ce..e9e8d0a 100755 --- a/core/sql/regress/compGeneral/EXPECTED005 +++ b/core/sql/regress/compGeneral/EXPECTED005 @@ -71,6 +71,12 @@ --- SQL operation complete. >> +>>create table witht1 (c1 int, c2 int); + +--- SQL operation complete. +>>create table witht2 (c1 int, c2 int); + +--- SQL operation complete. >>?section populate_tables >>-- Populate t005t01 >>insert into t005t01 values (30, 33); @@ -194,7 +200,36 @@ --- 1 row(s) inserted. >> ->> +>>insert into witht1 values(1,1); + +--- 1 row(s) inserted. +>>insert into witht1 values(2,2); + +--- 1 row(s) inserted. +>>insert into witht1 values(3,3); + +--- 1 row(s) inserted. +>>insert into witht1 values(4,4); + +--- 1 row(s) inserted. +>>insert into witht1 values(5,5); + +--- 1 row(s) inserted. +>>insert into witht2 values(3,3); + +--- 1 row(s) inserted. +>>insert into witht2 values(4,4); + +--- 1 row(s) inserted. +>>insert into witht2 values(5,5); + +--- 1 row(s) inserted. +>>insert into witht2 values(6,6); + +--- 1 row(s) inserted. +>>insert into witht2 values(7,7); + +--- 1 row(s) inserted. >> >>?section prep >>control query default query_cache '0'; @@ -1018,6 +1053,61 @@ DNO DNAME ENO DNO *** ERROR[8822] The statement was not prepared. >> +>>cqd mode_special_4 'on'; + +--- SQL operation complete. +>>with w1 as (select * from witht1), ++>w2 as (select * from w1) ++>select * from w2; + +C1 C2 +---------- + + 1 1 + 2 2 + 3 3 + 4 4 + 5 5 + +--- SQL operation complete. +>> +>>with w1 as (select c1, c2 from witht1), ++>w2 as (select c1,c2 from witht2) ++>select * from w1 , w2 where w1.c1 = w2.c1; + +C1 C2 C1 C2 +---------- + + 3 3 3 3 + 4 4 4 4 + 5 5 5 5 + +--- SQL operation complete. +>> +>>with w1 as (select * from witht1) ++>select * from w1 ++>union all ++>select * from w1; + +C1 C2 +---------- + + 1 1 + 1 1 + 2 2 + 2 2 + 3 3 + 3 3 + 4 4 + 4 4 + 5 5 + 5 5 + +--- SQL operation complete. +>> +>>cqd mode_special_4 reset; + +--- SQL operation complete. >> >>?section cleanup >> @@ -1051,7 +1141,12 @@ DNO DNAME ENO DNO >>drop table t005_fx; --- SQL operation complete. ->> +>>drop table witht1; + +--- SQL operation complete. +>>drop table witht2; + +--- SQL operation complete. >>exit; End of MXCI Session http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/79b421ff/core/sql/regress/compGeneral/TEST005 ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/TEST005 b/core/sql/regress/compGeneral/TEST005 index 12c90be..b98297e 100755 --- a/core/sql/regress/compGeneral/TEST005 +++ b/core/sql/regress/compGeneral/TEST005 @@ -41,6 +41,8 @@ drop table t005_ex; drop table t005_gx; drop table t005_hx; drop table t005_fx; +drop table witht1; +drop table witht2; log LOG005 clear; @@ -98,6 +100,8 @@ create table t005_hx ( h3 decimal(10,0) not null ) no partition; +create table witht1 (c1 int, c2 int); +create table witht2 (c1 int, c2 int); ?section populate_tables -- Populate t005t01 insert into t005t01 values (30, 33); @@ -147,7 +151,16 @@ insert into t005_hx values(2,2,1); insert into t005_hx values(2,2,2); insert into t005_hx values(2,2,3); - +insert into witht1 values(1,1); +insert into witht1 values(2,2); +insert into witht1 values(3,3); +insert into witht1 values(4,4); +insert into witht1 values(5,5); +insert into witht2 values(3,3); +insert into witht2 values(4,4); +insert into witht2 values(5,5); +insert into witht2 values(6,6); +insert into witht2 values(7,7); ?section prep control query default query_cache '0'; @@ -476,6 +489,21 @@ create materialized view T_MV1 select A.dno,B.eno from t005t02 A full outer join t005t01 B on A.dno = B.dno; +cqd mode_special_4 'on'; +with w1 as (select * from witht1), +w2 as (select * from w1) +select * from w2; + +with w1 as (select c1, c2 from witht1), +w2 as (select c1,c2 from witht2) +select * from w1 , w2 where w1.c1 = w2.c1; + +with w1 as (select * from witht1) +select * from w1 +union all +select * from w1; + +cqd mode_special_4 reset; ?section cleanup @@ -491,4 +519,5 @@ drop table t005_ex; drop table t005_gx; drop table t005_hx; drop table t005_fx; - +drop table witht1; +drop table witht2;
