You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by su...@apache.org on 2016/04/01 00:37:18 UTC
[1/4] incubator-trafodion git commit: [TRAFODION-1896] CQD
TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS for non-aligned format
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 <se...@esgyn.com>
Authored: Fri Mar 18 12:24:02 2016 +0000
Committer: selvaganesang <se...@esgyn.com>
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 ;
[3/4] incubator-trafodion git commit: Merge remote branch
'origin/master' into trafodion-1896
Posted by su...@apache.org.
Merge remote branch 'origin/master' into trafodion-1896
Conflicts:
core/sql/regress/seabase/EXPECTED020
core/sql/sqlcomp/DefaultConstants.h
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/99224ddd
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/99224ddd
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/99224ddd
Branch: refs/heads/master
Commit: 99224dddd97ac3910a8b25a7d765e8be50695c1e
Parents: 445f690 f918a26
Author: selvaganesang <se...@esgyn.com>
Authored: Wed Mar 30 16:30:36 2016 +0000
Committer: selvaganesang <se...@esgyn.com>
Committed: Wed Mar 30 16:30:36 2016 +0000
----------------------------------------------------------------------
.../java/org/trafodion/jdbc/t2/BaseRow.java | 67 -
.../java/org/trafodion/jdbc/t2/InsertRow.java | 89 -
.../main/java/org/trafodion/jdbc/t2/Row.java | 275 --
core/conn/odbc/src/odbc/nsksrvr/Makefile | 2 +
.../odbc/src/odbc/nsksrvrcore/csrvrstmt.cpp | 7 +-
core/sqf/monitor/linux/cluster.cxx | 11 +
core/sqf/monitor/linux/cmsh.cxx | 29 +-
core/sqf/monitor/linux/config.cxx | 1 +
core/sqf/monitor/linux/internal.h | 9 +
core/sqf/monitor/linux/makefile | 5 +-
core/sqf/monitor/linux/mlio.cxx | 6 +-
core/sqf/monitor/linux/monitor.cxx | 3 +
core/sqf/monitor/linux/msgdef.h | 17 +
core/sqf/monitor/linux/phnode.h | 2 +
core/sqf/monitor/linux/pnode.cxx | 10 +
core/sqf/monitor/linux/pnode.h | 1 +
core/sqf/monitor/linux/pnodeconfig.cxx | 6 +
core/sqf/monitor/linux/pnodeconfig.h | 2 +-
core/sqf/monitor/linux/replicate.cxx | 56 +-
core/sqf/monitor/linux/replicate.h | 14 +
core/sqf/monitor/linux/reqnodename.cxx | 114 +
core/sqf/monitor/linux/reqqueue.cxx | 60 +-
core/sqf/monitor/linux/reqqueue.h | 32 +-
core/sqf/monitor/linux/shell.cxx | 177 +-
core/sqf/monitor/linux/testpoint.h | 2 +-
core/sqf/sql/scripts/ilh_cleanhb | 106 +
core/sqf/sql/scripts/ilh_hbase_repair | 65 +
core/sqf/sql/scripts/ilh_hbcheck | 72 +
core/sqf/sql/scripts/ilh_traf_restart | 117 +
core/sqf/sql/scripts/ilh_trafinit | 108 +
.../sql/scripts/install_hadoop_regr_test_env | 4 +-
core/sqf/sql/scripts/install_local_hadoop | 5 +
core/sqf/sql/scripts/trafnodestatus | 105 +
core/sqf/sql/scripts/trafnodestatus.awk | 76 +
core/sqf/src/seabed/src/sautil.cpp | 5 -
core/sqf/src/seabed/src/stream.cpp | 2 -
core/sqf/tools/sqtools.sh | 56 +
core/sql/bin/SqlciErrors.txt | 7 +-
core/sql/comexe/ComTdbHbaseAccess.h | 27 +-
core/sql/common/ComMisc.cpp | 14 +
core/sql/common/ComMisc.h | 8 +
core/sql/common/ComSmallDefs.h | 5 +
core/sql/executor/ExHbaseSelect.cpp | 19 +-
core/sql/exp/exp_conv.cpp | 1 +
core/sql/exp/exp_datetime.cpp | 11 +-
core/sql/exp/exp_datetime.h | 1 +
core/sql/generator/GenPreCode.cpp | 5 +-
core/sql/generator/GenRelScan.cpp | 2 +-
core/sql/generator/Generator.cpp | 8 +-
core/sql/optimizer/BindItemExpr.cpp | 2 +-
core/sql/optimizer/NATable.cpp | 4 +-
core/sql/optimizer/SynthType.cpp | 14 +-
core/sql/parser/ElemDDLCol.cpp | 542 ++--
core/sql/parser/ElemDDLColDef.h | 14 +-
core/sql/parser/StmtDDLCreate.cpp | 7 +-
core/sql/parser/sqlparser.y | 48 +-
core/sql/regress/charsets/EXPECTED002 | 6 +-
core/sql/regress/charsets/EXPECTED310 | 198 +-
core/sql/regress/charsets/EXPECTED312 | 198 +-
core/sql/regress/charsets/EXPECTED313 | 286 ++-
.../sql/regress/compGeneral/DIFF006.KNOWN.SB.OS | 2 +-
core/sql/regress/compGeneral/EXPECTED005 | 10 +-
core/sql/regress/compGeneral/EXPECTED006.SB | 22 +-
core/sql/regress/compGeneral/EXPECTED042 | 8 +-
core/sql/regress/core/EXPECTED131 | 89 +-
core/sql/regress/core/TEST131 | 40 +
core/sql/regress/privs1/EXPECTED133 | Bin 22984 -> 26383 bytes
core/sql/regress/privs1/TEST133 | 97 +-
core/sql/regress/seabase/EXPECTED010 | 2 +-
core/sql/regress/seabase/EXPECTED020 | 15 +-
core/sql/regress/seabase/EXPECTED027 | 219 +-
core/sql/regress/seabase/EXPECTED030 | 49 +
core/sql/regress/seabase/EXPECTED031 | 214 ++
core/sql/regress/seabase/TEST010 | 2 +-
core/sql/regress/seabase/TEST020 | 7 +-
core/sql/regress/seabase/TEST027 | 35 +
core/sql/regress/seabase/TEST030 | 7 +
core/sql/regress/seabase/TEST031 | 108 +
core/sql/regress/tools/runregr_udr.ksh | 8 +-
core/sql/regress/udr/EXPECTED103 | 529 ++++
core/sql/regress/udr/TEST103 | 246 ++
core/sql/regress/udr/TEST103_functions.cpp | 191 ++
core/sql/regress/udr/TEST103_procs.java | 224 ++
core/sql/sqlcomp/CmpSeabaseDDL.h | 1 +
core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp | 12 +-
core/sql/sqlcomp/CmpSeabaseDDLroutine.cpp | 23 +-
core/sql/sqlcomp/CmpSeabaseDDLschema.cpp | 101 +-
core/sql/sqlcomp/CmpSeabaseDDLtable.cpp | 114 +-
core/sql/sqlcomp/CmpSeabaseDDLview.cpp | 1 -
core/sql/sqlcomp/DefaultConstants.h | 6 +
core/sql/sqlcomp/nadefaults.cpp | 4 +-
core/sql/ustat/hs_globals.cpp | 24 +-
.../src/asciidoc/_chapters/SQuirrel.adoc | 8 +-
.../src/asciidoc/_chapters/about.adoc | 11 +-
.../src/asciidoc/_chapters/dbviz.adoc | 17 +-
.../src/asciidoc/_chapters/introduction.adoc | 130 +-
.../src/asciidoc/_chapters/jdbct4.adoc | 353 ++-
.../src/asciidoc/_chapters/odb.adoc | 49 +-
.../src/asciidoc/_chapters/odbc_linux.adoc | 82 +-
.../src/asciidoc/_chapters/odbc_windows.adoc | 176 +-
.../src/asciidoc/_chapters/sample_prog.adoc | 6 +-
.../src/asciidoc/_chapters/trafci.adoc | 323 ++-
docs/client_install/src/asciidoc/index.adoc | 11 +-
.../src/images/win10_edit_path.jpg | Bin 0 -> 65094 bytes
.../src/images/win10_select_java.jpg | Bin 0 -> 22758 bytes
.../src/asciidoc/_chapters/about.adoc | 4 +-
.../src/asciidoc/_chapters/commands.adoc | 8 +-
.../src/asciidoc/_chapters/install.adoc | 70 +-
.../src/asciidoc/_chapters/interactive.adoc | 56 +-
.../src/asciidoc/_chapters/introduction.adoc | 10 +-
.../src/asciidoc/_chapters/launch.adoc | 148 +-
.../src/asciidoc/_chapters/perlpython.adoc | 72 +-
.../src/asciidoc/_chapters/scripts.adoc | 24 +-
docs/command_interface/src/asciidoc/index.adoc | 4 +
.../src/resources/source/sample.pl | 19 +
.../src/resources/source/sample.py | 29 +
docs/spj_guide/pom.xml | 289 +++
.../spj_guide/src/asciidoc/_chapters/about.adoc | 200 ++
.../src/asciidoc/_chapters/create_spjs.adoc | 508 ++++
.../src/asciidoc/_chapters/deploy_spjs.adoc | 557 ++++
.../src/asciidoc/_chapters/develop_spjs.adoc | 899 +++++++
.../src/asciidoc/_chapters/execute_spjs.adoc | 730 ++++++
.../src/asciidoc/_chapters/get_started.adoc | 121 +
.../asciidoc/_chapters/grant_privileges.adoc | 312 +++
.../src/asciidoc/_chapters/introduction.adoc | 255 ++
.../_chapters/performance_troubleshooting.adoc | 416 +++
.../src/asciidoc/_chapters/sample_database.adoc | 816 ++++++
.../src/asciidoc/_chapters/sample_spjs.adoc | 2386 ++++++++++++++++++
docs/spj_guide/src/asciidoc/index.adoc | 113 +
docs/src/site/markdown/documentation.md | 4 +
docs/src/site/markdown/download.md | 2 +-
install/installer/rest_installer | 11 +-
install/installer/traf_cloudera_mods98 | 64 +-
install/installer/traf_config_check | 7 +-
install/installer/traf_createPasswordLessSSH | 6 +-
install/installer/traf_getHadoopNodes | 105 +-
install/installer/traf_hortonworks_mods98 | 101 +-
.../installer/trafodion_apache_hadoop_install | 12 +-
install/installer/trafodion_install | 12 -
install/installer/trafodion_uninstaller | 10 +-
pom.xml | 4 +-
141 files changed, 12949 insertions(+), 2156 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/99224ddd/core/sql/regress/seabase/EXPECTED020
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/99224ddd/core/sql/regress/seabase/TEST020
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/99224ddd/core/sql/sqlcomp/DefaultConstants.h
----------------------------------------------------------------------
diff --cc core/sql/sqlcomp/DefaultConstants.h
index c212256,7e20226..479c2a5
--- a/core/sql/sqlcomp/DefaultConstants.h
+++ b/core/sql/sqlcomp/DefaultConstants.h
@@@ -3801,8 -3801,16 +3801,14 @@@ enum DefaultConstant
// If this cqd is on, then other alters (name, datatype) are also supported.
TRAF_ALTER_COL_ATTRS,
++ // Controls the behavior of upsert - MERGE, REPLACE, OPTIMAL
+ TRAF_UPSERT_MODE,
+ // if set, let users create system reserved names. Default is OFF.
+ // This cqd should only be used to debug or if system column names are
+ // REALLY needed by users.
+ // Currently syskey, _salt_, _division_.
+ TRAF_ALLOW_RESERVED_COLNAMES,
- // if ON, upsert into the table will use the default value for the omitted columns
- // with default value
- TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS,
-
// This enum constant must be the LAST one in the list; it's a count,
// not an Attribute (it's not IN DefaultDefaults; it's the SIZE of it)!
__NUM_DEFAULT_ATTRIBUTES
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/99224ddd/core/sql/sqlcomp/nadefaults.cpp
----------------------------------------------------------------------
[4/4] incubator-trafodion git commit: Merge [TRAFODION-1896] PR-393
CQD TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS
Posted by su...@apache.org.
Merge [TRAFODION-1896] PR-393 CQD TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/a837667d
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/a837667d
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/a837667d
Branch: refs/heads/master
Commit: a837667d701c3c621e4b8df7c39ed0f1753cbf95
Parents: 19f236f 99224dd
Author: Suresh Subbiah <su...@apache.org>
Authored: Thu Mar 31 22:36:36 2016 +0000
Committer: Suresh Subbiah <su...@apache.org>
Committed: Thu Mar 31 22:36:36 2016 +0000
----------------------------------------------------------------------
core/sql/generator/GenRelUpdate.cpp | 6 +-
core/sql/optimizer/BindRelExpr.cpp | 48 ++--
core/sql/regress/seabase/EXPECTED020 | 374 +++++++++++++++++++++++++++++-
core/sql/regress/seabase/TEST020 | 99 +++++++-
core/sql/sqlcomp/DefaultConstants.h | 9 +-
core/sql/sqlcomp/nadefaults.cpp | 10 +-
6 files changed, 513 insertions(+), 33 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a837667d/core/sql/sqlcomp/nadefaults.cpp
----------------------------------------------------------------------
[2/4] incubator-trafodion git commit: The CQD
TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS is replaced with TRAF_UPSERT_MODE.
This CQD takes 3 values
Posted by su...@apache.org.
The CQD TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS is replaced with TRAF_UPSERT_MODE.
This CQD takes 3 values
MERGE - If the row exists, use the old values for omitted columns. This
doesn't necessarily mean that MERGE command is used
REPLACE - Always replace the column with default values for omitted columns
OPTIMAL - Chooses MERGE like concept for non-aligned format table
REPLACE for aligned format table
MERGE is the default.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/445f6902
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/445f6902
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/445f6902
Branch: refs/heads/master
Commit: 445f6902b632386f3b7aebedda86baea9d7267cd
Parents: 53dcf92
Author: selvaganesang <se...@esgyn.com>
Authored: Thu Mar 24 21:27:31 2016 +0000
Committer: selvaganesang <se...@esgyn.com>
Committed: Thu Mar 24 21:27:31 2016 +0000
----------------------------------------------------------------------
core/sql/generator/GenRelUpdate.cpp | 8 +-
core/sql/optimizer/BindRelExpr.cpp | 27 ++--
core/sql/regress/seabase/EXPECTED020 | 217 ++++++++++++++++++++++++++++--
core/sql/regress/seabase/TEST020 | 60 ++++++++-
core/sql/sqlcomp/DefaultConstants.h | 7 +-
core/sql/sqlcomp/nadefaults.cpp | 10 +-
6 files changed, 295 insertions(+), 34 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/445f6902/core/sql/generator/GenRelUpdate.cpp
----------------------------------------------------------------------
diff --git a/core/sql/generator/GenRelUpdate.cpp b/core/sql/generator/GenRelUpdate.cpp
index e362d2b..8b35d11 100644
--- a/core/sql/generator/GenRelUpdate.cpp
+++ b/core/sql/generator/GenRelUpdate.cpp
@@ -2025,8 +2025,9 @@ 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()) &&
+ ((CmpCommon::getDefault(TRAF_UPSERT_MODE) == DF_MERGE) ||
+ (CmpCommon::getDefault(TRAF_UPSERT_MODE) == DF_OPTIMAL)) &&
+ (((Assign*)assignExpr)->canBeSkipped()) &&
(NOT col->isSystemColumn()) &&
(NOT col->isClusteringKey()) &&
(NOT col->isIdentityColumn()))
@@ -2342,7 +2343,8 @@ short HbaseInsert::codeGen(Generator *generator)
col = tgtValueId.getNAColumn( TRUE );
if ((NOT isAlignedFormat) &&
- (CmpCommon::getDefault(TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS) == DF_OFF) &&
+ ((CmpCommon::getDefault(TRAF_UPSERT_MODE) == DF_MERGE) ||
+ (CmpCommon::getDefault(TRAF_UPSERT_MODE) == DF_OPTIMAL)) &&
(((Assign*)assignExpr)->canBeSkipped()) &&
(NOT col->isSystemColumn()) &&
(NOT col->isClusteringKey()) &&
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/445f6902/core/sql/optimizer/BindRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindRelExpr.cpp b/core/sql/optimizer/BindRelExpr.cpp
index 0d764de..5a85434 100644
--- a/core/sql/optimizer/BindRelExpr.cpp
+++ b/core/sql/optimizer/BindRelExpr.cpp
@@ -10165,8 +10165,8 @@ base table then the old version of the row will have to be deleted from
indexes, and a new version inserted. Upsert is being transformed to merge
so that we can delete the old version of an updated row from the index.
-Upsert is also converted into merge when there are omitted cols with default values and
-TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS is set to OFF in case of aligned format table or
+Upsert is also converted into merge when TRAF_UPSERT_MODE is set to MERGE and
+there are omitted cols with default values in case of aligned format table or
omitted current timestamp cols in case of non-aligned row format
*/
NABoolean Insert::isUpsertThatNeedsMerge(NABoolean isAlignedRowFormat, NABoolean omittedDefaultCols,
@@ -10179,12 +10179,19 @@ NABoolean Insert::isUpsertThatNeedsMerge(NABoolean isAlignedRowFormat, NABoolean
(NOT (getTableDesc()->getClusteringIndex()->getNAFileSet()->hasSyskey())) &&
// table has secondary indexes or
(getTableDesc()->hasSecondaryIndexes() ||
- // CQD is set off and
- ((CmpCommon::getDefault(TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS) == DF_OFF) &&
+ // CQD is set to MERGE
+ ((CmpCommon::getDefault(TRAF_UPSERT_MODE) == DF_MERGE) &&
// omitted current default columns with non-aligned row format tables
// or omitted default columns with aligned row format tables
- (((NOT isAlignedRowFormat) && omittedCurrentDefaultClassCols) ||
- (isAlignedRowFormat && omittedDefaultCols)))
+ (((NOT isAlignedRowFormat) && omittedCurrentDefaultClassCols) ||
+ (isAlignedRowFormat && omittedDefaultCols))) ||
+ // CQD is set to Optimal, for non-aligned row format with omitted
+ // current columns, it is converted into merge though it is not
+ // optimal for performance - This is done to ensure that when the
+ // CQD is set to optimal, non-aligned format would behave like
+ // merge when any column is omitted
+ ((CmpCommon::getDefault(TRAF_UPSERT_MODE) == DF_OPTIMAL) &&
+ ((NOT isAlignedRowFormat) && omittedCurrentDefaultClassCols))
)
)
return TRUE;
@@ -10280,12 +10287,14 @@ RelExpr* Insert::xformUpsertToMerge(BindWA *bindWA)
{
// We need to bind in the new = old values
// in GenericUpdate::bindNode. So skip the columns that are not user
- // specified
+ // specified and
//
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)
+ // If copy the Default values in case of replace mode or optiomal mode with
+ // aligned row tables
+ else if ((CmpCommon::getDefault(TRAF_UPSERT_MODE) == DF_REPLACE) ||
+ (isAlignedRowFormat && CmpCommon::getDefault(TRAF_UPSERT_MODE) == DF_OPTIMAL))
copySetAssign = TRUE;
if (copySetAssign)
{
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/445f6902/core/sql/regress/seabase/EXPECTED020
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/EXPECTED020 b/core/sql/regress/seabase/EXPECTED020
index 0b4827e..41c1ea1 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 Fri Mar 18 11:37:39 2016
+-- Definition current Thu Mar 24 20:57:19 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 Fri Mar 18 11:37:41 2016
+-- Definition current Thu Mar 24 20:57:21 2016
(
C CHAR(15) CHARACTER SET ISO88591 COLLATE
@@ -160,7 +160,7 @@ G0
>>invoke test020t5;
-- Definition of Trafodion table TRAFODION.SCH.TEST020T5
--- Definition current Fri Mar 18 11:37:50 2016
+-- Definition current Thu Mar 24 20:57:29 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 Fri Mar 18 11:38:28 2016
+-- Definition current Thu Mar 24 20:58:14 2016
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -3273,7 +3273,7 @@ C@ A
--- SQL operation complete.
>>--test for timestamp column default value
->>cqd traf_upsert_with_insert_default_semantics 'off';
+>>cqd traf_upsert_mode 'merge';
--- SQL operation complete.
>>cqd traf_aligned_row_format 'off' ;
@@ -3308,7 +3308,7 @@ A B D E
1 c ? 5
--- 1 row(s) selected.
->>-- check to ensure the value for column d is retained
+>>-- check to ensure the value for column e is retained
>>upsert into test020t41 (a,b) values (1, 'd');
--- 1 row(s) inserted.
@@ -3375,7 +3375,7 @@ E@ A
6 5
--- 5 row(s) selected.
->>-- check if the updated d column is reflected in the index
+>>-- check if the updated e column is reflected in the index
>>upsert into test020t41 (a,b,e) values (1, 'c', 4);
--- 1 row(s) inserted.
@@ -3394,7 +3394,7 @@ E@ A
>>
>>-- With index it will be merge anyway, but default values needs to be
>>-- populated
->>cqd traf_upsert_with_insert_default_semantics 'on';
+>>cqd traf_upsert_mode 'replace';
--- SQL operation complete.
>>delete from test020t41 ;
@@ -3471,11 +3471,126 @@ A B D E
--- 1 row(s) selected.
>>
+>>cqd traf_upsert_mode 'optimal' ;
+
+--- SQL operation complete.
+>>-- check if the timestamp is inserted with the recent timestamp
+>>delete from test020t41 ;
+
+--- 2 row(s) deleted.
+>>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 e 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 e 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.
->>cqd traf_upsert_with_insert_default_semantics 'off';
+>>cqd traf_upsert_mode 'merge';
--- SQL operation complete.
>>-- check if the timestamp is inserted with the recent timestamp
@@ -3589,7 +3704,7 @@ E@ A
>>
>>-- With index it will be merge anyway, but default values needs to be
>>-- populated
->>cqd traf_upsert_with_insert_default_semantics 'on';
+>>cqd traf_upsert_mode 'replace';
--- SQL operation complete.
>>delete from test020t42 ;
@@ -3666,10 +3781,90 @@ A B D E
--- 1 row(s) selected.
>>
+>>cqd traf_upsert_mode 'optimal';
+
+--- SQL operation complete.
+>>delete from test020t42 ;
+
+--- 2 row(s) deleted.
+>>create index test020t42ix on test020t42(e);
+
+--- SQL operation complete.
+>>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';
+>>cqd traf_upsert_mode 'merge';
--- SQL operation complete.
>>upsert into test020t43 values (1,1);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/445f6902/core/sql/regress/seabase/TEST020
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/TEST020 b/core/sql/regress/seabase/TEST020
index 4b2f2c5..fefaf24 100755
--- a/core/sql/regress/seabase/TEST020
+++ b/core/sql/regress/seabase/TEST020
@@ -769,7 +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_upsert_mode 'merge';
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);
@@ -779,7 +779,7 @@ select a,b,d,e from test020t41 where current_timestamp-c < cast(10 as interval s
-- 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
+-- check to ensure the value for column e 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
@@ -791,13 +791,13 @@ 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
+-- check if the updated e column is reflected in the index
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';
+cqd traf_upsert_mode 'replace';
delete from test020t41 ;
insert into test020t41 (a,b) values (1,'a'), (2,'b');
upsert into test020t41 (a,b,e) values (1, 'c', 5);
@@ -818,9 +818,33 @@ 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);
+cqd traf_upsert_mode 'optimal' ;
+-- check if the timestamp is inserted with the recent timestamp
+delete from test020t41 ;
+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 e 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 e 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;
-cqd traf_upsert_with_insert_default_semantics 'off';
+cqd traf_upsert_mode 'merge';
-- 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);
@@ -845,7 +869,18 @@ 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';
+cqd traf_upsert_mode 'replace';
+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);
@@ -854,6 +889,17 @@ 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);
+
+cqd traf_upsert_mode 'optimal';
+delete from test020t42 ;
+create index test020t42ix on test020t42(e);
+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
@@ -867,7 +913,7 @@ upsert into test020t42 (a,b) values (1, 'd');
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';
+cqd traf_upsert_mode 'merge';
upsert into test020t43 values (1,1);
upsert into test020t43 (c1) values(1);
select * from test020t43 ;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/445f6902/core/sql/sqlcomp/DefaultConstants.h
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/DefaultConstants.h b/core/sql/sqlcomp/DefaultConstants.h
index f78bfae..c212256 100644
--- a/core/sql/sqlcomp/DefaultConstants.h
+++ b/core/sql/sqlcomp/DefaultConstants.h
@@ -3801,9 +3801,7 @@ enum DefaultConstants
// If this cqd is on, then other alters (name, datatype) are also supported.
TRAF_ALTER_COL_ATTRS,
- // if ON, upsert into the table will use the default value for the omitted columns
- // with default value
- TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS,
+ TRAF_UPSERT_MODE,
// This enum constant must be the LAST one in the list; it's a count,
// not an Attribute (it's not IN DefaultDefaults; it's the SIZE of it)!
@@ -3895,6 +3893,7 @@ enum DefaultToken {
DF_MEASURE,
DF_MEDIUM,
DF_MEDIUM_LOW,
+ DF_MERGE,
DF_MINIMUM,
DF_MMAP,
DF_MULTI_NODE,
@@ -3905,6 +3904,7 @@ enum DefaultToken {
DF_ON,
DF_OPENS_FOR_WRITE,
DF_OPERATOR,
+ DF_OPTIMAL,
DF_ORDERED,
DF_PERTABLE,
DF_PRINT,
@@ -3916,6 +3916,7 @@ enum DefaultToken {
DF_RELEASE,
DF_REMOTE,
DF_REPEATABLE_READ,
+ DF_REPLACE,
DF_REPSEL,
DF_RESOURCES,
DF_RETURN,
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/445f6902/core/sql/sqlcomp/nadefaults.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/nadefaults.cpp b/core/sql/sqlcomp/nadefaults.cpp
index 49fff32..dcd4dbb 100644
--- a/core/sql/sqlcomp/nadefaults.cpp
+++ b/core/sql/sqlcomp/nadefaults.cpp
@@ -3405,8 +3405,8 @@ XDDkwd__(SUBQUERY_UNNESTING, "ON"),
DDkwd__(TRAF_UNLOAD_SKIP_WRITING_TO_FILES, "OFF"),
DDkwd__(TRAF_UPSERT_ADJUST_PARAMS, "OFF"),
DDkwd__(TRAF_UPSERT_AUTO_FLUSH, "OFF"),
+ DDkwd__(TRAF_UPSERT_MODE, "MERGE"),
DDint__(TRAF_UPSERT_WB_SIZE, "2097152"),
- DDkwd__(TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS, "OFF"),
DDkwd__(TRAF_UPSERT_WRITE_TO_WAL, "OFF"),
DDkwd__(TRAF_USE_RWRS_FOR_MD_INSERT, "ON"),
@@ -6317,6 +6317,7 @@ const char *NADefaults::keywords_[DF_lastToken] = {
"MEASURE",
"MEDIUM",
"MEDIUM_LOW",
+ "MERGE",
"MINIMUM",
"MMAP",
"MULTI_NODE",
@@ -6327,6 +6328,7 @@ const char *NADefaults::keywords_[DF_lastToken] = {
"ON",
"OPENS_FOR_WRITE",
"OPERATOR",
+ "OPTIMAL",
"ORDERED",
"PERTABLE",
"PRINT",
@@ -6338,6 +6340,7 @@ const char *NADefaults::keywords_[DF_lastToken] = {
"RELEASE",
"REMOTE",
"REPEATABLE_READ",
+ "REPLACE",
"REPSEL",
"RESOURCES",
"RETURN",
@@ -7000,6 +7003,11 @@ DefaultToken NADefaults::token(Int32 attrEnum,
if (tok == DF_OFF || tok == DF_MINIMUM ||
tok == DF_MEDIUM || tok == DF_MAXIMUM || tok == DF_ON)
isValid = TRUE;
+ break;
+ case TRAF_UPSERT_MODE:
+ if (tok == DF_MERGE || tok == DF_REPLACE || tok == DF_OPTIMAL)
+ isValid = TRUE;
+ break;
// Nothing needs to be added here for ON/OFF/SYSTEM keywords --
// instead, add to DEFAULT_ALLOWS_SEPARATE_SYSTEM code in the ctor.