Repository: incubator-trafodion Updated Branches: refs/heads/master 19f236f25 -> a837667d7
[TRAFODION-1896] CQD TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS for non-aligned format This CQD is now honored for non-aligned format by replacing the column value with default values for omitted columns and put into hbase table. Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/53dcf926 Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/53dcf926 Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/53dcf926 Branch: refs/heads/master Commit: 53dcf926625f645fa4c5a3ee1df6ce7ba7569717 Parents: 616961d Author: selvaganesang <[email protected]> Authored: Fri Mar 18 12:24:02 2016 +0000 Committer: selvaganesang <[email protected]> Committed: Fri Mar 18 12:24:02 2016 +0000 ---------------------------------------------------------------------- core/sql/generator/GenRelUpdate.cpp | 2 + core/sql/optimizer/BindRelExpr.cpp | 33 +++--- core/sql/regress/seabase/EXPECTED020 | 177 +++++++++++++++++++++++++++++- core/sql/regress/seabase/TEST020 | 49 +++++++++ 4 files changed, 240 insertions(+), 21 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/53dcf926/core/sql/generator/GenRelUpdate.cpp ---------------------------------------------------------------------- diff --git a/core/sql/generator/GenRelUpdate.cpp b/core/sql/generator/GenRelUpdate.cpp index ec5c3b4..e362d2b 100644 --- a/core/sql/generator/GenRelUpdate.cpp +++ b/core/sql/generator/GenRelUpdate.cpp @@ -2025,6 +2025,7 @@ short HbaseUpdate::codeGen(Generator * generator) col = tgtValueId.getNAColumn( TRUE ); if ((NOT isAlignedFormat) && + (CmpCommon::getDefault(TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS) == DF_OFF) && (((Assign*)assignExpr)->canBeSkipped()) && (NOT col->isSystemColumn()) && (NOT col->isClusteringKey()) && @@ -2341,6 +2342,7 @@ short HbaseInsert::codeGen(Generator *generator) col = tgtValueId.getNAColumn( TRUE ); if ((NOT isAlignedFormat) && + (CmpCommon::getDefault(TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS) == DF_OFF) && (((Assign*)assignExpr)->canBeSkipped()) && (NOT col->isSystemColumn()) && (NOT col->isClusteringKey()) && http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/53dcf926/core/sql/optimizer/BindRelExpr.cpp ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/BindRelExpr.cpp b/core/sql/optimizer/BindRelExpr.cpp index cbb460e..0d764de 100644 --- a/core/sql/optimizer/BindRelExpr.cpp +++ b/core/sql/optimizer/BindRelExpr.cpp @@ -10157,7 +10157,7 @@ RelExpr *Insert::bindNode(BindWA *bindWA) } // Insert::bindNode() /* Upsert into a table with an index is converted into a Merge to avoid -the problem described in LP 1460771. An upsert may overwrite an existing row +the problem described in Trafodion-14. An upsert may overwrite an existing row in the base table (identical to the update when matched clause of Merge) or it may insert a new row into the base table (identical to insert when not matched clause of merge). If the upsert caused a row to be updated in the @@ -10172,15 +10172,21 @@ omitted current timestamp cols in case of non-aligned row format NABoolean Insert::isUpsertThatNeedsMerge(NABoolean isAlignedRowFormat, NABoolean omittedDefaultCols, NABoolean omittedCurrentDefaultClassCols) const { + // The necessary conditions to convert upsert to merge and if (isUpsert() && (NOT getIsTrafLoadPrep()) && (NOT (getTableDesc()->isIdentityColumnGeneratedAlways() && getTableDesc()->hasIdentityColumnInClusteringKey())) && (NOT (getTableDesc()->getClusteringIndex()->getNAFileSet()->hasSyskey())) && - ((getTableDesc()->hasSecondaryIndexes()) || - (( NOT isAlignedRowFormat) && omittedCurrentDefaultClassCols) || - ((isAlignedRowFormat && omittedDefaultCols - && (CmpCommon::getDefault(TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS) == DF_OFF))) - )) + // table has secondary indexes or + (getTableDesc()->hasSecondaryIndexes() || + // CQD is set off and + ((CmpCommon::getDefault(TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS) == DF_OFF) && + // omitted current default columns with non-aligned row format tables + // or omitted default columns with aligned row format tables + (((NOT isAlignedRowFormat) && omittedCurrentDefaultClassCols) || + (isAlignedRowFormat && omittedDefaultCols))) + ) + ) return TRUE; else return FALSE; @@ -10272,20 +10278,15 @@ RelExpr* Insert::xformUpsertToMerge(BindWA *bindWA) else if (! col->isClusteringKey()) { - // In case of aligned format we need to bind in the new = old values + // We need to bind in the new = old values // in GenericUpdate::bindNode. So skip the columns that are not user // specified - if (isAlignedRowFormat) - { - if (assignExpr->isUserSpecified()) - copySetAssign = TRUE; - // copy the default value if the below CQD is set to ON - else if (CmpCommon::getDefault(TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS) == DF_ON) - copySetAssign = TRUE; - } - else + // if (assignExpr->isUserSpecified()) copySetAssign = TRUE; + // copy the default value if the below CQD is set to ON + else if (CmpCommon::getDefault(TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS) == DF_ON) + copySetAssign = TRUE; if (copySetAssign) { setAssignPrev = setAssign; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/53dcf926/core/sql/regress/seabase/EXPECTED020 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/EXPECTED020 b/core/sql/regress/seabase/EXPECTED020 index 44da9b3..0b4827e 100644 --- a/core/sql/regress/seabase/EXPECTED020 +++ b/core/sql/regress/seabase/EXPECTED020 @@ -6,7 +6,7 @@ >>invoke test020t1; -- Definition of Trafodion table TRAFODION.SCH.TEST020T1 --- Definition current Tue Mar 8 22:57:06 2016 +-- Definition current Fri Mar 18 11:37:39 2016 ( C1 INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -49,7 +49,7 @@ C1 C2 >>invoke test020t2; -- Definition of Trafodion table TRAFODION.SCH.TEST020T2 --- Definition current Tue Mar 8 22:57:08 2016 +-- Definition current Fri Mar 18 11:37:41 2016 ( C CHAR(15) CHARACTER SET ISO88591 COLLATE @@ -160,7 +160,7 @@ G0 >>invoke test020t5; -- Definition of Trafodion table TRAFODION.SCH.TEST020T5 --- Definition current Tue Mar 8 22:57:24 2016 +-- Definition current Fri Mar 18 11:37:50 2016 ( C INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -255,7 +255,7 @@ ALTER TABLE TRAFODION.SCH.TEST020T5 ADD CONSTRAINT TRAFODION.SCH.TEST020T5C1 >>invoke test020t6; -- Definition of Trafodion table TRAFODION.SCH.TEST020T6 --- Definition current Tue Mar 8 22:58:27 2016 +-- Definition current Fri Mar 18 11:38:28 2016 ( SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE @@ -3273,6 +3273,9 @@ C@ A --- SQL operation complete. >>--test for timestamp column default value +>>cqd traf_upsert_with_insert_default_semantics 'off'; + +--- SQL operation complete. >>cqd traf_aligned_row_format 'off' ; --- SQL operation complete. @@ -3389,10 +3392,92 @@ E@ A --- 5 row(s) selected. >> +>>-- With index it will be merge anyway, but default values needs to be +>>-- populated +>>cqd traf_upsert_with_insert_default_semantics 'on'; + +--- SQL operation complete. +>>delete from test020t41 ; + +--- 5 row(s) deleted. +>>insert into test020t41 (a,b) values (1,'a'), (2,'b'); + +--- 2 row(s) inserted. +>>upsert into test020t41 (a,b,e) values (1, 'c', 5); + +--- 1 row(s) inserted. +>>-- Should display a row with = 1 +>>select a,b,d,e from test020t41 where c > (select c from test020t41 where a = 2); + +A B D E +-------------------- ---------- ----------- ----------- + + 1 c ? 5 + +--- 1 row(s) selected. +>>upsert into test020t41 (a,b) values (1, 'd'); + +--- 1 row(s) inserted. +>>-- Should display a row with = 1 and e should be 3 +>>select a,b,d,e from test020t41 where c > (select c from test020t41 where a = 2); + +A B D E +-------------------- ---------- ----------- ----------- + + 1 d ? 3 + +--- 1 row(s) selected. +>>select * from table(index_table test020t41ix) ; + +E@ A +----------- -------------------- + + 3 1 + 3 2 + +--- 2 row(s) selected. +>>drop index test020t41ix ; + +--- SQL operation complete. +>>-- Without index +>>delete from test020t41 ; + +--- 2 row(s) deleted. +>>insert into test020t41 (a,b) values (1,'a'), (2,'b'); + +--- 2 row(s) inserted. +>>upsert into test020t41 (a,b,e) values (1, 'c', 5); + +--- 1 row(s) inserted. +>>-- Should display a row with = 1 +>>select a,b,d,e from test020t41 where c > (select c from test020t41 where a = 2); + +A B D E +-------------------- ---------- ----------- ----------- + + 1 c ? 5 + +--- 1 row(s) selected. +>>upsert into test020t41 (a,b) values (1, 'd'); + +--- 1 row(s) inserted. +>>-- Should display a row with = 1 and e should be 3 +>>select a,b,d,e from test020t41 where c > (select c from test020t41 where a = 2); + +A B D E +-------------------- ---------- ----------- ----------- + + 1 d ? 3 + +--- 1 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. +>>cqd traf_upsert_with_insert_default_semantics 'off'; + +--- SQL operation complete. >>-- check if the timestamp is inserted with the recent timestamp >>insert into test020t42 (a,b) values (1,'a'), (2,'b'); @@ -3502,9 +3587,91 @@ E@ A --- 5 row(s) selected. >> +>>-- With index it will be merge anyway, but default values needs to be +>>-- populated +>>cqd traf_upsert_with_insert_default_semantics 'on'; + +--- SQL operation complete. +>>delete from test020t42 ; + +--- 5 row(s) deleted. +>>insert into test020t42 (a,b) values (1,'a'), (2,'b'); + +--- 2 row(s) inserted. +>>upsert into test020t42 (a,b,e) values (1, 'c', 5); + +--- 1 row(s) inserted. +>>-- Should display a row with = 1 +>>select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2); + +A B D E +-------------------- ---------- ----------- ----------- + + 1 c ? 5 + +--- 1 row(s) selected. +>>upsert into test020t42 (a,b) values (1, 'd'); + +--- 1 row(s) inserted. +>>-- Should display a row with = 1 and e should be 3 +>>select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2); + +A B D E +-------------------- ---------- ----------- ----------- + + 1 d ? 3 + +--- 1 row(s) selected. +>>select * from table(index_table test020t42ix) ; + +E@ A +----------- -------------------- + + 3 1 + 3 2 + +--- 2 row(s) selected. +>>drop index test020t42ix ; + +--- SQL operation complete. +>>-- Without index +>>delete from test020t42 ; + +--- 2 row(s) deleted. +>>insert into test020t42 (a,b) values (1,'a'), (2,'b'); + +--- 2 row(s) inserted. +>>upsert into test020t42 (a,b,e) values (1, 'c', 5); + +--- 1 row(s) inserted. +>>-- Should display a row with = 1 +>>select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2); + +A B D E +-------------------- ---------- ----------- ----------- + + 1 c ? 5 + +--- 1 row(s) selected. +>>upsert into test020t42 (a,b) values (1, 'd'); + +--- 1 row(s) inserted. +>>-- Should display a row with = 1 and e should be 3 +>>select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2); + +A B D E +-------------------- ---------- ----------- ----------- + + 1 d ? 3 + +--- 1 row(s) selected. +>> >>create table test020t43(c1 int, c2 int ) attribute aligned format ; --- SQL operation complete. +>>cqd traf_upsert_with_insert_default_semantics 'off'; + +--- SQL operation complete. >>upsert into test020t43 values (1,1); --- 1 row(s) inserted. @@ -3513,7 +3680,7 @@ E@ A --- 1 row(s) inserted. >>select * from test020t43 ; -C1 C2 +C1 C2 ----------- ----------- 1 1 http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/53dcf926/core/sql/regress/seabase/TEST020 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/TEST020 b/core/sql/regress/seabase/TEST020 index bedab65..4b2f2c5 100755 --- a/core/sql/regress/seabase/TEST020 +++ b/core/sql/regress/seabase/TEST020 @@ -769,6 +769,7 @@ select * from table(index_table test020t40i1); ?section trafodion_1700_and_1847 set parserflags 1; --test for timestamp column default value +cqd traf_upsert_with_insert_default_semantics 'off'; 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); @@ -794,8 +795,32 @@ select * from table(index_table test020t41ix) ; upsert into test020t41 (a,b,e) values (1, 'c', 4); select * from table(index_table test020t41ix) ; +-- With index it will be merge anyway, but default values needs to be +-- populated +cqd traf_upsert_with_insert_default_semantics 'on'; +delete from test020t41 ; +insert into test020t41 (a,b) values (1,'a'), (2,'b'); +upsert into test020t41 (a,b,e) values (1, 'c', 5); +-- Should display a row with = 1 +select a,b,d,e from test020t41 where c > (select c from test020t41 where a = 2); +upsert into test020t41 (a,b) values (1, 'd'); +-- Should display a row with = 1 and e should be 3 +select a,b,d,e from test020t41 where c > (select c from test020t41 where a = 2); +select * from table(index_table test020t41ix) ; +drop index test020t41ix ; +-- Without index +delete from test020t41 ; +insert into test020t41 (a,b) values (1,'a'), (2,'b'); +upsert into test020t41 (a,b,e) values (1, 'c', 5); +-- Should display a row with = 1 +select a,b,d,e from test020t41 where c > (select c from test020t41 where a = 2); +upsert into test020t41 (a,b) values (1, 'd'); +-- Should display a row with = 1 and e should be 3 +select a,b,d,e from test020t41 where c > (select c from test020t41 where a = 2); + 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; +cqd traf_upsert_with_insert_default_semantics 'off'; -- 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); @@ -818,7 +843,31 @@ select * from table(index_table test020t42ix) ; upsert into test020t42 (a,b,e) values (1, 'c', 4); select * from table(index_table test020t42ix) ; +-- With index it will be merge anyway, but default values needs to be +-- populated +cqd traf_upsert_with_insert_default_semantics 'on'; +delete from test020t42 ; +insert into test020t42 (a,b) values (1,'a'), (2,'b'); +upsert into test020t42 (a,b,e) values (1, 'c', 5); +-- Should display a row with = 1 +select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2); +upsert into test020t42 (a,b) values (1, 'd'); +-- Should display a row with = 1 and e should be 3 +select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2); +select * from table(index_table test020t42ix) ; +drop index test020t42ix ; +-- Without index +delete from test020t42 ; +insert into test020t42 (a,b) values (1,'a'), (2,'b'); +upsert into test020t42 (a,b,e) values (1, 'c', 5); +-- Should display a row with = 1 +select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2); +upsert into test020t42 (a,b) values (1, 'd'); +-- Should display a row with = 1 and e should be 3 +select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2); + create table test020t43(c1 int, c2 int ) attribute aligned format ; +cqd traf_upsert_with_insert_default_semantics 'off'; upsert into test020t43 values (1,1); upsert into test020t43 (c1) values(1); select * from test020t43 ;
