Repository: incubator-trafodion Updated Branches: refs/heads/master 7fe96649f -> 98b00b625
[TRAFODION-2047] add support of SQL syntax INTERSECT Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/23fe6d5a Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/23fe6d5a Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/23fe6d5a Branch: refs/heads/master Commit: 23fe6d5a6c5c9832bf85bb2348bba5d75f91c8ea Parents: f9eaa76 Author: Liu Ming <[email protected]> Authored: Thu Jul 28 12:58:20 2016 -0400 Committer: Liu Ming <[email protected]> Committed: Thu Jul 28 12:58:20 2016 -0400 ---------------------------------------------------------------------- core/sqf/src/seatrans/tm/macros.gmk | 2 +- core/sql/optimizer/BindRelExpr.cpp | 15 ++-- core/sql/parser/sqlparser.y | 18 +++- core/sql/regress/compGeneral/EXPECTED001.SB | 103 ++++++++++++++++++++++- core/sql/regress/compGeneral/TEST001 | 51 ++++++++++- 5 files changed, 176 insertions(+), 13 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/23fe6d5a/core/sqf/src/seatrans/tm/macros.gmk ---------------------------------------------------------------------- diff --git a/core/sqf/src/seatrans/tm/macros.gmk b/core/sqf/src/seatrans/tm/macros.gmk index 746323c..fdfc8f8 100644 --- a/core/sqf/src/seatrans/tm/macros.gmk +++ b/core/sqf/src/seatrans/tm/macros.gmk @@ -86,7 +86,7 @@ ifeq ($(SQ_USE_INTC),0) HPCWARN = -Wno-unused-parameter HPCXXWARN = -Wno-unused-parameter -Wno-non-virtual-dtor endif -INCLUDES = -I$(INCEXPDIR) -I$(INCMONDIR) -I$(TMINC) -I$(MY_SQROOT)/export/include -I$(MY_SPROOT)/export/include -I$(MY_SPROOT)/source/publications -I$(PROTOBUFS_INC) -I$(INC_JAVA) -I$(INC_JAVALINUX) -I$(MY_SQROOT)/commonLogger +INCLUDES = -I$(INCEXPDIR) -I$(INCMONDIR) -I$(TMINC) -I$(MY_SQROOT)/export/include -I$(MY_SPROOT)/export/include -I$(MY_SPROOT)/source/publications -I$(PROTOBUFS_INC) -I$(INC_JAVA) -I$(INC_JAVALINUX) -I$(MY_SQROOT)/commonLogger -I$(LOG4CXX_INC_DIR) -I$(LOG4CXX_INC_DIR)/lib4cxx LIBSSB = -L$(JAVA_HOME)/jre/lib/i386/client -L$(JAVA_HOME)/jre/lib/amd64/server -L$(LIBEXPDIR) -ltmjni -ljvm http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/23fe6d5a/core/sql/optimizer/BindRelExpr.cpp ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/BindRelExpr.cpp b/core/sql/optimizer/BindRelExpr.cpp index 94cd2f1..e2960f9 100644 --- a/core/sql/optimizer/BindRelExpr.cpp +++ b/core/sql/optimizer/BindRelExpr.cpp @@ -717,7 +717,7 @@ static ItemExpr *intersectColumns(const RETDesc &leftTable, ItemExpr *leftExpr = leftTable.getValueId(i).getItemExpr(); ItemExpr *rightExpr = rightTable.getValueId(i).getItemExpr(); BiRelat *compare = new (bindWA->wHeap()) - BiRelat(ITM_EQUAL, leftExpr, rightExpr); + BiRelat(ITM_EQUAL, leftExpr, rightExpr, TRUE); if (predicate) predicate = new (bindWA->wHeap()) BiLogic(ITM_AND, predicate, compare); else @@ -2971,12 +2971,15 @@ RelExpr *Intersect::bindNode(BindWA *bindWA) return this; } - // Join the columns of both sides. This is wrong semantics tho! ## + // Join the columns of both sides. // - *CmpCommon::diags() << DgSqlCode(-3022) // ## INTERSECT not yet supported - << DgString0("INTERSECT"); // ## - bindWA->setErrStatus(); // ## - if (bindWA->errStatus()) return NULL; // ## + if(CmpCommon::getDefault(MODE_SPECIAL_4) != DF_ON) + { + *CmpCommon::diags() << DgSqlCode(-3022) // ## INTERSECT not yet supported + << DgString0("INTERSECT"); // ## + bindWA->setErrStatus(); // ## + if (bindWA->errStatus()) return NULL; // ## + } // ItemExpr *predicate = intersectColumns(leftTable, rightTable, bindWA); RelExpr *join = new (bindWA->wHeap()) http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/23fe6d5a/core/sql/parser/sqlparser.y ---------------------------------------------------------------------- diff --git a/core/sql/parser/sqlparser.y b/core/sql/parser/sqlparser.y index 9dfdb2b..733f9c5 100755 --- a/core/sql/parser/sqlparser.y +++ b/core/sql/parser/sqlparser.y @@ -18354,11 +18354,23 @@ non_join_query_term : non_join_query_primary ColRefName(TRUE, PARSERHEAP()) ))); } + | query_term TOK_INTERSECT TOK_DISTINCT query_primary + { + $$ = new (PARSERHEAP()) + RelRoot(new (PARSERHEAP()) + GroupByAgg( + new (PARSERHEAP()) + Intersect($1,$4), + REL_GROUPBY, + new (PARSERHEAP()) + ColReference(new (PARSERHEAP()) + ColRefName(TRUE, PARSERHEAP()) + ))); + } | query_term TOK_INTERSECT TOK_ALL query_primary { - $$ = new (PARSERHEAP()) - RelRoot(new (PARSERHEAP()) - Intersect($1, $4)); + *SqlParser_Diags << DgSqlCode(-3022) << DgString0("INTERSECT ALL"); + YYERROR; } /* type relx */ http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/23fe6d5a/core/sql/regress/compGeneral/EXPECTED001.SB ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/EXPECTED001.SB b/core/sql/regress/compGeneral/EXPECTED001.SB index fc0fd3a..84881b8 100644 --- a/core/sql/regress/compGeneral/EXPECTED001.SB +++ b/core/sql/regress/compGeneral/EXPECTED001.SB @@ -13,7 +13,19 @@ +> primary key (dno) ); --- SQL operation complete. ->> +>>create table t021(eno int not null , ++> ename char(20), ++> dno int, ++> salary numeric(8,2), ++> primary key(eno) ); + +--- SQL operation complete. +>>create table t022(eno int , ++> ename char(20) , ++> dno int ++> ); + +--- SQL operation complete. >>#ifMX >>create table t01sample (l_partkey int not null not droppable, +>l_suppkey int not null not droppable, @@ -69,7 +81,33 @@ +> (55, 'Production', 2); --- 3 row(s) inserted. +>>insert into t021 values (1, 'Smith', 33, 2000); + +--- 1 row(s) inserted. +>>insert into t021 values (2, 'Jones', 33, 3000); + +--- 1 row(s) inserted. +>>insert into t021 values (3, 'Gray', 44, 4000); + +--- 1 row(s) inserted. +>>insert into t021 values (4, 'Miller', 55, 1000); + +--- 1 row(s) inserted. +>>insert into t021 values (5, 'Miller', null, null); + +--- 1 row(s) inserted. +>>insert into t021 values (6, 'Miller', null, null); + +--- 1 row(s) inserted. >> +>>insert into t022 values (1, 'Simith', 33), ++> (2, 'Jones', 33), ++> (3, 'Gray', 44), ++> (3, 'Gray', 44), ++> (4, 'Miller', null), ++> (6, 'Miller', null); + +--- 6 row(s) inserted. >>-- simulate a full outer join >>select * from t01emp natural left join t01dept +> union @@ -85,6 +123,69 @@ DNO ENO ENAME SALARY DNAME --- 4 row(s) selected. >> +>>-- do a intersect +>>cqd mode_special_4 'on'; + +--- SQL operation complete. +>>select eno, ename, dno from t021 ++> intersect ++>select eno, ename, dno from t022; + +ENO ENAME DNO +----------- -------------------- ----------- + + 2 Jones 33 + 3 Gray 44 + 6 Miller ? + +--- 3 row(s) selected. +>> +>>select eno, ename, dno from t021 ++> intersect all ++>select eno, ename, dno from t022; + + *** ERROR[3022] The INTERSECT ALL operator is not yet supported. + *** ERROR[8822] The statement was not prepared. +>> +>>select eno, ename, dno from t021 where dno >10 ++> intersect ++>select eno, ename, dno from t022 where dno <50; + +ENO ENAME DNO +----------- -------------------- ----------- + + 2 Jones 33 + 3 Gray 44 + +--- 2 row(s) selected. +>> +>>select * from t021 ++> intersect ++>select * from t022; + +*** ERROR[4014] The operands of an INTERSECT must be of equal degree. + +*** ERROR[8822] The statement was not prepared. + +>> +>>select * from ++>( ++>select eno, ename, dno from t021 ++> intersect ++>select eno, ename, dno from t022 ++>) ; + +ENO ENAME DNO +----------- -------------------- ----------- + + 2 Jones 33 + 3 Gray 44 + 6 Miller ? + +--- 3 row(s) selected. +>>cqd mode_special_4 reset; + +--- SQL operation complete. >>-- groupby can be pushed down >>-- add showshape statement to ensure that the group by is >>-- actually pushed down http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/23fe6d5a/core/sql/regress/compGeneral/TEST001 ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/TEST001 b/core/sql/regress/compGeneral/TEST001 index 277902f..9cfd9e7 100755 --- a/core/sql/regress/compGeneral/TEST001 +++ b/core/sql/regress/compGeneral/TEST001 @@ -35,6 +35,8 @@ ?section ddl drop table t01emp; drop table t01dept; +drop table t021; +drop table t022; #ifMX drop table t01sample; drop table t02sample; @@ -53,7 +55,15 @@ create table t01dept(dno int not null , dname char(20) not null, dregion int not null, primary key (dno) ); - +create table t021(eno int not null , + ename char(20), + dno int, + salary numeric(8,2), + primary key(eno) ); +create table t022(eno int , + ename char(20) , + dno int + ); #ifMX create table t01sample (l_partkey int not null not droppable, l_suppkey int not null not droppable, @@ -95,12 +105,49 @@ insert into t01emp values (4, 'Miller', 55, 1000); insert into t01dept values (33, 'Sales', 1), (44, 'Marketing', 1), (55, 'Production', 2); - +insert into t021 values (1, 'Smith', 33, 2000); +insert into t021 values (2, 'Jones', 33, 3000); +insert into t021 values (3, 'Gray', 44, 4000); +insert into t021 values (4, 'Miller', 55, 1000); +insert into t021 values (5, 'Miller', null, null); +insert into t021 values (6, 'Miller', null, null); + +insert into t022 values (1, 'Simith', 33), + (2, 'Jones', 33), + (3, 'Gray', 44), + (3, 'Gray', 44), + (4, 'Miller', null), + (6, 'Miller', null); -- simulate a full outer join select * from t01emp natural left join t01dept union select * from t01emp natural right join t01dept; +-- do a intersect +cqd mode_special_4 'on'; +select eno, ename, dno from t021 + intersect +select eno, ename, dno from t022; + +select eno, ename, dno from t021 + intersect all +select eno, ename, dno from t022; + +select eno, ename, dno from t021 where dno >10 + intersect +select eno, ename, dno from t022 where dno <50; + +select * from t021 + intersect +select * from t022; + +select * from +( +select eno, ename, dno from t021 + intersect +select eno, ename, dno from t022 +) ; +cqd mode_special_4 reset; -- groupby can be pushed down -- add showshape statement to ensure that the group by is -- actually pushed down
