Repository: incubator-trafodion Updated Branches: refs/heads/master 7acdca64d -> f71c719da
[TRAFODION-2843] Fix outer join on aggregate bug Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/ccb17c28 Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/ccb17c28 Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/ccb17c28 Branch: refs/heads/master Commit: ccb17c28e7fa74b5c58dbf37a02239316184bdd5 Parents: 03ffdb4 Author: Dave Birdsall <[email protected]> Authored: Wed Dec 13 21:04:44 2017 +0000 Committer: Dave Birdsall <[email protected]> Committed: Wed Dec 13 21:04:44 2017 +0000 ---------------------------------------------------------------------- core/sql/generator/GenPreCode.cpp | 8 +- core/sql/regress/executor/EXPECTED002.SB | 207 ++++++++++++++++++++++---- core/sql/regress/executor/TEST002 | 94 ++++++++++++ 3 files changed, 278 insertions(+), 31 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/ccb17c28/core/sql/generator/GenPreCode.cpp ---------------------------------------------------------------------- diff --git a/core/sql/generator/GenPreCode.cpp b/core/sql/generator/GenPreCode.cpp index 40baf03..96b7a6a 100644 --- a/core/sql/generator/GenPreCode.cpp +++ b/core/sql/generator/GenPreCode.cpp @@ -2432,12 +2432,14 @@ RelExpr * Join::preCodeGen(Generator * generator, if (!(getEquiJoinPredicates().isEmpty() || getJoinPred().isEmpty() || isAntiSemiJoin())) { - ValueIdSet dummy1, dummy2, dummy3, uncoveredPreds ; + ValueIdSet coveredPreds, dummy2, dummy3, uncoveredPreds ; child(0)->getGroupAttr()->coverTest(getJoinPred(), getGroupAttr()->getCharacteristicInputs(), - dummy1, dummy2, NULL, + coveredPreds, dummy2, NULL, &uncoveredPreds); - if (uncoveredPreds.isEmpty()) + // set the flag only if all the non-equi-join preds are covered + if ((getJoinPred().entries() == coveredPreds.entries()) && + uncoveredPreds.isEmpty()) setBeforeJoinPredOnOuterOnly(); } http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/ccb17c28/core/sql/regress/executor/EXPECTED002.SB ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/EXPECTED002.SB b/core/sql/regress/executor/EXPECTED002.SB index bc4ce8a..c6fe1bf 100644 --- a/core/sql/regress/executor/EXPECTED002.SB +++ b/core/sql/regress/executor/EXPECTED002.SB @@ -38,6 +38,41 @@ --- SQL operation complete. >> +>>-- added for JIRA TRAFODION-2843 +>>Create table D03s ++> ( ++> pk int not null not droppable primary key ++> , val01 int ++> , val02 int ++> ); + +--- SQL operation complete. +>> +>>Create table F01s ++> ( ++> pk int not null not droppable primary key ++> , fk_d01 int not null ++> , fk_d02 int not null ++> , fk_d03 int not null ++> , fk_d04 int not null ++> , fk_d05 int not null ++> , fk_d06 int not null ++> , fk_d07 int not null ++> , fk_d08 int not null ++> , fk_d09 int not null ++> , fk_d10 int not null ++> , val01 int ++> , val02 int ++> , val01_d01 int ++> , val02_d01 int ++> , val01_d02 int ++> , val02_d02 int ++> , val01_d03 int ++> , val02_d03 int ++> ) salt using 8 partitions; + +--- SQL operation complete. +>> >>?section Genesis_10_970911_6859 >>?ifMX >>create view t002v(w,x) as values(1,11); @@ -6645,9 +6680,9 @@ A B C E STUDENT_NAME (EXPR) ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------- -s1 40-50-60-79-88-100-101 -s2 18-58-88-188 -s3 40-90-100 +s1 40-50-60-79-88-100-101 +s2 18-58-88-188 +s3 40-90-100 --- 3 row(s) selected. >> @@ -6660,9 +6695,9 @@ s3 40-90-100 STUDENT_NAME (EXPR) ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------- -s1 40-50-60-79-88-88-100-101 -s2 18-58-88-88-188 -s3 40-40-90-100 +s1 40-50-60-79-88-88-100-101 +s2 18-58-88-88-188 +s3 40-40-90-100 --- 3 row(s) selected. >> @@ -6699,9 +6734,9 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD STUDENT_NAME (EXPR) ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------- -s1 101-100-88-79-60-50-40 -s2 188-88-58-18 -s3 100-90-40 +s1 101-100-88-79-60-50-40 +s2 188-88-58-18 +s3 100-90-40 --- 3 row(s) selected. >>control query shape cut; @@ -6728,7 +6763,7 @@ s3 100-90-40 >>insert into regexp_test values( 'english' ); --- 1 row(s) inserted. ->>insert into regexp_test values( '[email protected]' ); +>>insert into regexp_test values( '[email protected]' ); --- 1 row(s) inserted. >>insert into regexp_test values( '127.0.0.1' ); @@ -6744,54 +6779,54 @@ s3 100-90-40 >>-- only number >>select * from regexp_test where c1 regexp '^[0-9]*\s*$'; -C1 ----------- +C1 +-------------------------------- -123 +123 --- 1 row(s) selected. >>select * from regexp_test where c1 regexp '^[[:digit:]]*\s*$'; -C1 ----------- +C1 +-------------------------------- -123 +123 --- 1 row(s) selected. >>-- only english >>select * from regexp_test where c1 regexp '^.[A-Za-z]+\s*$'; -C1 ----------- +C1 +-------------------------------- -english +english --- 1 row(s) selected. >>-- valid email address >>select * from regexp_test where c1 regexp >>'\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*\s*'; -C1 ----------- +C1 +-------------------------------- [email protected] [email protected] --- 1 row(s) selected. >>-- valid ip address >>select * from regexp_test where c1 regexp >>'^(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9])\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9]|0)\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9]|0)\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[0-9])\s*$'; -C1 ----------- +C1 +-------------------------------- -127.0.0.1 +127.0.0.1 --- 1 row(s) selected. >>-- utf-8 code >>select * from regexp_test where c1 regexp '(ä¸ææµè¯)'; -C1 ----------- +C1 +-------------------------------- -ä¸ææµè¯ +ä¸ææµè¯ --- 1 row(s) selected. >>select * from regexp_test where c1 regexp '[^\'; @@ -6803,6 +6838,122 @@ C1 --- SQL operation complete. >>------------------------------------------------------------------------ +>>-- added for JIRA TRAFODION-2843 +>> +>>insert into D03s ++> select c1+c2*10+c3*100, c1, c1+c2*10 ++> from (values(1)) T ++> transpose 0,1 as c1 ++> transpose 0,1 as c2 ++> transpose 0,1 as c3; + +--- 8 row(s) inserted. +>> +>>insert with no rollback into F01s ++> select c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 ++> ,c1 ++> ,c1+c2*10 ++> ,c1+c2*10+c3*100 ++> ,c1 ++> ,c1+c2*10 ++> ,c1+c2*10+c3*100 ++> ,c1 ++> ,c1+c2*10 ++> ,c1+c2*10+c3*100 ++> ,c1 ++> ,c1+c2*10 ++> ,mod(c1+c2*100+c3*100,200) ++> ,mod(c1,3) ++> ,mod(c1,6) ++> ,mod(c1+c2*10,5) ++> ,c1 ++> ,c1 ++> ,c1+c2*10 ++> from (values(1)) T ++> transpose 0,1 as c1 ++> transpose 0,1 as c2 ++> transpose 0,1 as c3 ++> transpose 0,1 as c4 ++> transpose 0 as c5 ++> transpose 0 as c6 ++> ; + +--- 16 row(s) inserted. +>> +>>prepare x1 from ++> select F01s.val01, TD03.val01 ++> From F01s ++> full outer join ++> (select D03s.val01,count(D03s.pk) ++> from D03s ++> group by D03s.val01) as TD03(val01,pk) ++> on (TD03.pk=F01s.fk_d03 ++> AND TD03.pk>0 ); + +--- SQL command prepared. +>> +>>execute x1; + +VAL01 VAL01 +----------- ----------- + + 0 ? + 10 ? + 10 ? + 1 ? + 0 ? + 1 ? + 0 ? + 1 ? + 11 ? + 11 ? + 10 ? + 1 ? + 0 ? + 11 ? + 10 ? + 11 ? + ? 1 + ? 0 + +--- 18 row(s) selected. +>> +>>prepare x1v from ++> select F01s.val01, F01s.fk_d03, TD03.pk, TD03.val01 ++> From F01s ++> full outer join ++> (select D03s.val01,count(D03s.pk) ++> from D03s ++> group by D03s.val01) as TD03(val01,pk) ++> on (TD03.pk=F01s.fk_d03); + +--- SQL command prepared. +>> +>>execute x1v; + +VAL01 FK_D03 PK VAL01 +----------- ----------- -------------------- ----------- + + 0 100 ? ? + 10 110 ? ? + 10 10 ? ? + 1 101 ? ? + 0 100 ? ? + 1 101 ? ? + 0 0 ? ? + 1 1 ? ? + 11 111 ? ? + 11 111 ? ? + 10 110 ? ? + 1 1 ? ? + 0 0 ? ? + 11 11 ? ? + 10 10 ? ? + 11 11 ? ? + ? ? 4 1 + ? ? 4 0 + +--- 18 row(s) selected. >> >>obey TEST002(BR0198_MULTI); >>select count(*) from T002T1; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/ccb17c28/core/sql/regress/executor/TEST002 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/TEST002 b/core/sql/regress/executor/TEST002 index a043b82..07a4122 100755 --- a/core/sql/regress/executor/TEST002 +++ b/core/sql/regress/executor/TEST002 @@ -65,6 +65,37 @@ create table t002t10 ( col4 int ); create table t002tab2 (char_1 CHAR(1), numeric_1 NUMERIC(4, 0)); +-- added for JIRA TRAFODION-2843 +Create table D03s + ( + pk int not null not droppable primary key + , val01 int + , val02 int + ); + +Create table F01s + ( + pk int not null not droppable primary key + , fk_d01 int not null + , fk_d02 int not null + , fk_d03 int not null + , fk_d04 int not null + , fk_d05 int not null + , fk_d06 int not null + , fk_d07 int not null + , fk_d08 int not null + , fk_d09 int not null + , fk_d10 int not null + , val01 int + , val02 int + , val01_d01 int + , val02_d01 int + , val01_d02 int + , val02_d02 int + , val01_d03 int + , val02_d03 int + ) salt using 8 partitions; + ?section Genesis_10_970911_6859 ?ifMX create view t002v(w,x) as values(1,11); -- should work @@ -1155,6 +1186,66 @@ select * from regexp_test where c1 regexp '(ä¸ææµè¯)'; select * from regexp_test where c1 regexp '[^\'; drop table regexp_test; ------------------------------------------------------------------------ +-- added for JIRA TRAFODION-2843 + +insert into D03s + select c1+c2*10+c3*100, c1, c1+c2*10 + from (values(1)) T + transpose 0,1 as c1 + transpose 0,1 as c2 + transpose 0,1 as c3; + +insert with no rollback into F01s + select c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 + ,c1 + ,c1+c2*10 + ,c1+c2*10+c3*100 + ,c1 + ,c1+c2*10 + ,c1+c2*10+c3*100 + ,c1 + ,c1+c2*10 + ,c1+c2*10+c3*100 + ,c1 + ,c1+c2*10 + ,mod(c1+c2*100+c3*100,200) + ,mod(c1,3) + ,mod(c1,6) + ,mod(c1+c2*10,5) + ,c1 + ,c1 + ,c1+c2*10 + from (values(1)) T + transpose 0,1 as c1 + transpose 0,1 as c2 + transpose 0,1 as c3 + transpose 0,1 as c4 + transpose 0 as c5 + transpose 0 as c6 + ; + +prepare x1 from + select F01s.val01, TD03.val01 + From F01s + full outer join + (select D03s.val01,count(D03s.pk) + from D03s + group by D03s.val01) as TD03(val01,pk) + on (TD03.pk=F01s.fk_d03 + AND TD03.pk>0 ); + +execute x1; + +prepare x1v from + select F01s.val01, F01s.fk_d03, TD03.pk, TD03.val01 + From F01s + full outer join + (select D03s.val01,count(D03s.pk) + from D03s + group by D03s.val01) as TD03(val01,pk) + on (TD03.pk=F01s.fk_d03); + +execute x1v; obey TEST002(BR0198_MULTI); obey TEST002(BR0198_EMPTY); @@ -1212,6 +1303,9 @@ drop table t002_outer_lower_hj ; drop table t002_inner_lower_hj ; drop table t002_inner_upper_hj ; +drop table F01s; +drop table D03s; + ?section clnup_end
