trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dbirds...@apache.org
Subject [1/2] incubator-trafodion git commit: [TRAFODION-2843] Fix outer join on aggregate bug
Date Thu, 14 Dec 2017 17:27:59 GMT
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 <dbirdsall@apache.org>
Authored: Wed Dec 13 21:04:44 2017 +0000
Committer: Dave Birdsall <dbirdsall@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
 
 


Mime
View raw message