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 <db...@apache.org>
Authored: Wed Dec 13 21:04:44 2017 +0000
Committer: Dave Birdsall <db...@apache.org>
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( 'dev@TRAFODION.org' );
+>>insert into regexp_test values( 'dev@trafodion.org' );
--- 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
+--------------------------------
-dev@TRAFODION.org
+dev@trafodion.org
--- 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
Merge [TRAFODION-2843] PR 1340 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/f71c719d Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/f71c719d Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/f71c719d Branch: refs/heads/master Commit: f71c719daf299b4eba61bc1965e248bb840d586e Parents: 7acdca6 ccb17c2 Author: Dave Birdsall <db...@apache.org> Authored: Thu Dec 14 17:27:06 2017 +0000 Committer: Dave Birdsall <db...@apache.org> Committed: Thu Dec 14 17:27:06 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/f71c719d/core/sql/generator/GenPreCode.cpp ----------------------------------------------------------------------