[TRAFODION-1700] [TRFODION-1847] Fixes - commit #3 Added new tests to exercise the change
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/a7063227 Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/a7063227 Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/a7063227 Branch: refs/heads/master Commit: a70632272f8ff632c85248dbc4a3759c27db2da8 Parents: 2fb283b Author: selvaganesang <selva.govindara...@esgyn.com> Authored: Sun Feb 28 20:19:13 2016 +0000 Committer: selvaganesang <selva.govindara...@esgyn.com> Committed: Sun Feb 28 20:19:13 2016 +0000 ---------------------------------------------------------------------- core/sql/regress/seabase/EXPECTED020 | 233 ++++++++++++++++++++++++++++++ core/sql/regress/seabase/TEST020 | 54 +++++++ 2 files changed, 287 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a7063227/core/sql/regress/seabase/EXPECTED020 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/EXPECTED020 b/core/sql/regress/seabase/EXPECTED020 index ac4c176..b1e8606 100644 --- a/core/sql/regress/seabase/EXPECTED020 +++ b/core/sql/regress/seabase/EXPECTED020 @@ -3263,4 +3263,237 @@ C@ A --- 3 row(s) selected. >> +>>obey TEST020(trafodion_1700_and_1847); +>>set parserflags 1; + +--- SQL operation complete. +>>--test for timestamp column default value +>>cqd traf_aligned_row_format 'off' ; + +--- SQL operation complete. +>>create table test020t41(a largeint not null primary key, b char(10), ++>c timestamp(6) default current , d int , e int default 3); + +--- SQL operation complete. +>>-- check if the timestamp is inserted with the recent timestamp +>>insert into test020t41 (a,b) values (1,'a'), (2,'b'); + +--- 2 row(s) inserted. +>>select a,b,d,e from test020t41 where current_timestamp-c < cast(10 as interval second); + +A B D E +-------------------- ---------- ----------- ----------- + + 1 a ? 3 + 2 b ? 3 + +--- 2 row(s) selected. +>>-- check to ensure the timestamp column is not updated with upsert +>>upsert into test020t41 (a,b,e) values (1, 'c', 5); + +--- 1 row(s) inserted. +>>select a,b,d,e from test020t41 where a = 1 and c = (select c from test020t41 where a = 2); + +A B D E +-------------------- ---------- ----------- ----------- + + 1 c ? 5 + +--- 1 row(s) selected. +>>-- check to ensure the value for column d is retained +>>upsert into test020t41 (a,b) values (1, 'd'); + +--- 1 row(s) inserted. +>>select a,b,d,e from test020t41 where a = 1 and c = (select c from test020t41 where a = 2); + +A B D E +-------------------- ---------- ----------- ----------- + + 1 d ? 5 + +--- 1 row(s) selected. +>>-- upsert with non-matching rows +>>upsert into test020t41 (a,b) values (3, 'e'), (4, 'f'); + +--- 2 row(s) inserted. +>>select a,b,d,e from test020t41 ; + +A B D E +-------------------- ---------- ----------- ----------- + + 1 d ? 5 + 2 b ? 3 + 3 e ? 3 + 4 f ? 3 + +--- 4 row(s) selected. +>>upsert into test020t41 (a,b) values (3, 'g'); + +--- 1 row(s) inserted. +>>select a,b,d,e from test020t41 where a = 3 and c = (select c from test020t41 where a = 4); + +A B D E +-------------------- ---------- ----------- ----------- + + 3 g ? 3 + +--- 1 row(s) selected. +>>create index test020t41ix on test020t41(e); + +--- SQL operation complete. +>>select * from table(index_table test020t41ix) ; + +E@ A +----------- -------------------- + + 3 2 + 3 3 + 3 4 + 5 1 + +--- 4 row(s) selected. +>>upsert into test020t41 (a,b,e) values (5,'h',6); + +--- 1 row(s) inserted. +>>select * from table(index_table test020t41ix) ; + +E@ A +----------- -------------------- + + 3 2 + 3 3 + 3 4 + 5 1 + 6 5 + +--- 5 row(s) selected. +>>-- check if the updated d column is reflected in the index +>>upsert into test020t41 (a,b,e) values (1, 'c', 4); + +--- 1 row(s) inserted. +>>select * from table(index_table test020t41ix) ; + +E@ A +----------- -------------------- + + 3 2 + 3 3 + 3 4 + 4 1 + 6 5 + +--- 5 row(s) selected. +>> +>>create table test020t42(a largeint not null primary key, b char(10), ++>c timestamp(6) default current , d int , e int default 3) attribute aligned format; + +--- SQL operation complete. +>>-- check if the timestamp is inserted with the recent timestamp +>>insert into test020t42 (a,b) values (1,'a'), (2,'b'); + +--- 2 row(s) inserted. +>>select a,b,d,e from test020t42 where current_timestamp-c < cast(10 as interval second); + +A B D E +-------------------- ---------- ----------- ----------- + + 1 a ? 3 + 2 b ? 3 + +--- 2 row(s) selected. +>>-- check to ensure the timestamp column is not updated with upsert +>>upsert into test020t42 (a,b,e) values (1, 'c', 5); + +--- 1 row(s) inserted. +>>select a,b,d,e from test020t42 where a = 1 and c = (select c from test020t42 where a = 2); + +A B D E +-------------------- ---------- ----------- ----------- + + 1 c ? 5 + +--- 1 row(s) selected. +>>-- check to ensure the value for column d is retained +>>upsert into test020t42 (a,b) values (1, 'd'); + +--- 1 row(s) inserted. +>>select a,b,d,e from test020t42 where a = 1 and c = (select c from test020t42 where a = 2); + +A B D E +-------------------- ---------- ----------- ----------- + + 1 d ? 5 + +--- 1 row(s) selected. +>>-- upsert with non-matching rows +>>upsert into test020t42 (a,b) values (3, 'e'), (4, 'f'); + +--- 2 row(s) inserted. +>>select a,b,d,e from test020t42 ; + +A B D E +-------------------- ---------- ----------- ----------- + + 1 d ? 5 + 2 b ? 3 + 3 e ? 3 + 4 f ? 3 + +--- 4 row(s) selected. +>>upsert into test020t42 (a,b) values (3, 'g'); + +--- 1 row(s) inserted. +>>select a,b,d,e from test020t42 where a = 3 and c = (select c from test020t42 where a = 4); + +A B D E +-------------------- ---------- ----------- ----------- + + 3 g ? 3 + +--- 1 row(s) selected. +>>create index test020t42ix on test020t42(e); + +--- SQL operation complete. +>>select * from table(index_table test020t42ix) ; + +E@ A +----------- -------------------- + + 3 2 + 3 3 + 3 4 + 5 1 + +--- 4 row(s) selected. +>>upsert into test020t42 (a,b,e) values (5,'h',6); + +--- 1 row(s) inserted. +>>select * from table(index_table test020t42ix) ; + +E@ A +----------- -------------------- + + 3 2 + 3 3 + 3 4 + 5 1 + 6 5 + +--- 5 row(s) selected. +>>-- check if the updated d column is reflected in the index +>>upsert into test020t42 (a,b,e) values (1, 'c', 4); + +--- 1 row(s) inserted. +>>select * from table(index_table test020t42ix) ; + +E@ A +----------- -------------------- + + 3 2 + 3 3 + 3 4 + 4 1 + 6 5 + +--- 5 row(s) selected. >>log; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a7063227/core/sql/regress/seabase/TEST020 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/TEST020 b/core/sql/regress/seabase/TEST020 index 058c8ad..64afa3c 100755 --- a/core/sql/regress/seabase/TEST020 +++ b/core/sql/regress/seabase/TEST020 @@ -39,6 +39,7 @@ obey TEST020(tests); obey TEST020(test_10_020913_3920); obey TEST020(test_10_030916_9668); obey TEST020(test_LP_1360493); +obey TEST020(trafodion_1700_and_1847); log; obey TEST020(clean_up); exit; @@ -87,6 +88,8 @@ drop table test020_LP_1360493; drop table test020_t34 cascade; drop table test020_t33 cascade; drop table test020t40 cascade; +drop table test020t41 cascade; +drop table test020t42 cascade; ?section tests create table test020t1 (c1 int not null primary key, @@ -761,3 +764,54 @@ insert into test020t40 values (3,3); select * from test020t40; select * from table(index_table test020t40i1); +?section trafodion_1700_and_1847 +set parserflags 1; +--test for timestamp column default value +cqd traf_aligned_row_format 'off' ; +create table test020t41(a largeint not null primary key, b char(10), +c timestamp(6) default current , d int , e int default 3); +-- check if the timestamp is inserted with the recent timestamp +insert into test020t41 (a,b) values (1,'a'), (2,'b'); +select a,b,d,e from test020t41 where current_timestamp-c < cast(10 as interval second); +-- check to ensure the timestamp column is not updated with upsert +upsert into test020t41 (a,b,e) values (1, 'c', 5); +select a,b,d,e from test020t41 where a = 1 and c = (select c from test020t41 where a = 2); +-- check to ensure the value for column d is retained +upsert into test020t41 (a,b) values (1, 'd'); +select a,b,d,e from test020t41 where a = 1 and c = (select c from test020t41 where a = 2); +-- upsert with non-matching rows +upsert into test020t41 (a,b) values (3, 'e'), (4, 'f'); +select a,b,d,e from test020t41 ; +upsert into test020t41 (a,b) values (3, 'g'); +select a,b,d,e from test020t41 where a = 3 and c = (select c from test020t41 where a = 4); +create index test020t41ix on test020t41(e); +select * from table(index_table test020t41ix) ; +upsert into test020t41 (a,b,e) values (5,'h',6); +select * from table(index_table test020t41ix) ; +-- check if the updated d column is reflected in the index +upsert into test020t41 (a,b,e) values (1, 'c', 4); +select * from table(index_table test020t41ix) ; + +create table test020t42(a largeint not null primary key, b char(10), +c timestamp(6) default current , d int , e int default 3) attribute aligned format; +-- check if the timestamp is inserted with the recent timestamp +insert into test020t42 (a,b) values (1,'a'), (2,'b'); +select a,b,d,e from test020t42 where current_timestamp-c < cast(10 as interval second); +-- check to ensure the timestamp column is not updated with upsert +upsert into test020t42 (a,b,e) values (1, 'c', 5); +select a,b,d,e from test020t42 where a = 1 and c = (select c from test020t42 where a = 2); +-- check to ensure the value for column d is retained +upsert into test020t42 (a,b) values (1, 'd'); +select a,b,d,e from test020t42 where a = 1 and c = (select c from test020t42 where a = 2); +-- upsert with non-matching rows +upsert into test020t42 (a,b) values (3, 'e'), (4, 'f'); +select a,b,d,e from test020t42 ; +upsert into test020t42 (a,b) values (3, 'g'); +select a,b,d,e from test020t42 where a = 3 and c = (select c from test020t42 where a = 4); +create index test020t42ix on test020t42(e); +select * from table(index_table test020t42ix) ; +upsert into test020t42 (a,b,e) values (5,'h',6); +select * from table(index_table test020t42ix) ; +-- check if the updated d column is reflected in the index +upsert into test020t42 (a,b,e) values (1, 'c', 4); +select * from table(index_table test020t42ix) ;
