db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From myrn...@apache.org
Subject svn commit: r718975 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/joins.out tests/lang/joins.sql
Date Wed, 19 Nov 2008 14:45:24 GMT
Author: myrnavl
Date: Wed Nov 19 06:45:23 2008
New Revision: 718975

URL: http://svn.apache.org/viewvc?rev=718975&view=rev
Log:
adding some queries as added regression test.

Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/joins.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/joins.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/joins.out?rev=718975&r1=718974&r2=718975&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/joins.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/joins.out
Wed Nov 19 06:45:23 2008
@@ -630,6 +630,170 @@
 -1         |-2         |-1         |1          
 -2         |-4         |NULL       |NULL       
 -3         |-9         |NULL       |NULL       
+ij> --- regression test for an old optimizer problem; when the bug occurred,
+-- hash join queries with 'or' would return incorrect results. 
+create table t5929_1 ( i int, j int, k int);
+0 rows inserted/updated/deleted
+ij> create table t5929_2 ( i int, j int, k int);
+0 rows inserted/updated/deleted
+ij> insert into t5929_1 values (1, 1, 1), (2, 1, 2);
+2 rows inserted/updated/deleted
+ij> insert into t5929_1 select i+2, j, k from t5929_1;
+2 rows inserted/updated/deleted
+ij> insert into t5929_1 select i+4, j, k from t5929_1;
+4 rows inserted/updated/deleted
+ij> insert into t5929_2 select * from t5929_1;
+8 rows inserted/updated/deleted
+ij> -- This query should return 32 rows, but it returned 64 before fix
+select * from --DERBY-PROPERTIES joinOrder=fixed 
+t5929_1, t5929_2 --DERBY-PROPERTIES joinStrategy=nestedLoop
+where t5929_1.j=t5929_2.j and (t5929_2.j=1 and (t5929_2.k=1 or t5929_2.j=4));
+I          |J          |K          |I          |J          |K          
+-----------------------------------------------------------------------
+1          |1          |1          |1          |1          |1          
+1          |1          |1          |3          |1          |1          
+1          |1          |1          |5          |1          |1          
+1          |1          |1          |7          |1          |1          
+2          |1          |2          |1          |1          |1          
+2          |1          |2          |3          |1          |1          
+2          |1          |2          |5          |1          |1          
+2          |1          |2          |7          |1          |1          
+3          |1          |1          |1          |1          |1          
+3          |1          |1          |3          |1          |1          
+3          |1          |1          |5          |1          |1          
+3          |1          |1          |7          |1          |1          
+4          |1          |2          |1          |1          |1          
+4          |1          |2          |3          |1          |1          
+4          |1          |2          |5          |1          |1          
+4          |1          |2          |7          |1          |1          
+5          |1          |1          |1          |1          |1          
+5          |1          |1          |3          |1          |1          
+5          |1          |1          |5          |1          |1          
+5          |1          |1          |7          |1          |1          
+6          |1          |2          |1          |1          |1          
+6          |1          |2          |3          |1          |1          
+6          |1          |2          |5          |1          |1          
+6          |1          |2          |7          |1          |1          
+7          |1          |1          |1          |1          |1          
+7          |1          |1          |3          |1          |1          
+7          |1          |1          |5          |1          |1          
+7          |1          |1          |7          |1          |1          
+8          |1          |2          |1          |1          |1          
+8          |1          |2          |3          |1          |1          
+8          |1          |2          |5          |1          |1          
+8          |1          |2          |7          |1          |1          
+ij> -- This query should return identical to the above query
+select * from --DERBY-PROPERTIES joinOrder=fixed 
+t5929_1, t5929_2 --DERBY-PROPERTIES joinStrategy=hash 
+where t5929_1.j=t5929_2.j and (t5929_2.j=1 and t5929_2.k=1);
+I          |J          |K          |I          |J          |K          
+-----------------------------------------------------------------------
+1          |1          |1          |1          |1          |1          
+1          |1          |1          |3          |1          |1          
+1          |1          |1          |5          |1          |1          
+1          |1          |1          |7          |1          |1          
+2          |1          |2          |1          |1          |1          
+2          |1          |2          |3          |1          |1          
+2          |1          |2          |5          |1          |1          
+2          |1          |2          |7          |1          |1          
+3          |1          |1          |1          |1          |1          
+3          |1          |1          |3          |1          |1          
+3          |1          |1          |5          |1          |1          
+3          |1          |1          |7          |1          |1          
+4          |1          |2          |1          |1          |1          
+4          |1          |2          |3          |1          |1          
+4          |1          |2          |5          |1          |1          
+4          |1          |2          |7          |1          |1          
+5          |1          |1          |1          |1          |1          
+5          |1          |1          |3          |1          |1          
+5          |1          |1          |5          |1          |1          
+5          |1          |1          |7          |1          |1          
+6          |1          |2          |1          |1          |1          
+6          |1          |2          |3          |1          |1          
+6          |1          |2          |5          |1          |1          
+6          |1          |2          |7          |1          |1          
+7          |1          |1          |1          |1          |1          
+7          |1          |1          |3          |1          |1          
+7          |1          |1          |5          |1          |1          
+7          |1          |1          |7          |1          |1          
+8          |1          |2          |1          |1          |1          
+8          |1          |2          |3          |1          |1          
+8          |1          |2          |5          |1          |1          
+8          |1          |2          |7          |1          |1          
+ij> select * from --DERBY-PROPERTIES joinOrder=fixed 
+t5929_1, t5929_2 --DERBY-PROPERTIES joinStrategy=hash 
+where t5929_1.j=t5929_2.j and (t5929_2.j=1 and (t5929_2.k=1 or t5929_2.j+1=5));
+I          |J          |K          |I          |J          |K          
+-----------------------------------------------------------------------
+1          |1          |1          |1          |1          |1          
+1          |1          |1          |3          |1          |1          
+1          |1          |1          |5          |1          |1          
+1          |1          |1          |7          |1          |1          
+2          |1          |2          |1          |1          |1          
+2          |1          |2          |3          |1          |1          
+2          |1          |2          |5          |1          |1          
+2          |1          |2          |7          |1          |1          
+3          |1          |1          |1          |1          |1          
+3          |1          |1          |3          |1          |1          
+3          |1          |1          |5          |1          |1          
+3          |1          |1          |7          |1          |1          
+4          |1          |2          |1          |1          |1          
+4          |1          |2          |3          |1          |1          
+4          |1          |2          |5          |1          |1          
+4          |1          |2          |7          |1          |1          
+5          |1          |1          |1          |1          |1          
+5          |1          |1          |3          |1          |1          
+5          |1          |1          |5          |1          |1          
+5          |1          |1          |7          |1          |1          
+6          |1          |2          |1          |1          |1          
+6          |1          |2          |3          |1          |1          
+6          |1          |2          |5          |1          |1          
+6          |1          |2          |7          |1          |1          
+7          |1          |1          |1          |1          |1          
+7          |1          |1          |3          |1          |1          
+7          |1          |1          |5          |1          |1          
+7          |1          |1          |7          |1          |1          
+8          |1          |2          |1          |1          |1          
+8          |1          |2          |3          |1          |1          
+8          |1          |2          |5          |1          |1          
+8          |1          |2          |7          |1          |1          
+ij> select * from --DERBY-PROPERTIES joinOrder=fixed 
+t5929_2, t5929_1 --DERBY-PROPERTIES joinStrategy=hash 
+where t5929_1.j=t5929_2.j and (t5929_2.j=1 and (t5929_2.k=1 or t5929_2.j=4));
+I          |J          |K          |I          |J          |K          
+-----------------------------------------------------------------------
+1          |1          |1          |1          |1          |1          
+1          |1          |1          |2          |1          |2          
+1          |1          |1          |3          |1          |1          
+1          |1          |1          |4          |1          |2          
+1          |1          |1          |5          |1          |1          
+1          |1          |1          |6          |1          |2          
+1          |1          |1          |7          |1          |1          
+1          |1          |1          |8          |1          |2          
+3          |1          |1          |1          |1          |1          
+3          |1          |1          |2          |1          |2          
+3          |1          |1          |3          |1          |1          
+3          |1          |1          |4          |1          |2          
+3          |1          |1          |5          |1          |1          
+3          |1          |1          |6          |1          |2          
+3          |1          |1          |7          |1          |1          
+3          |1          |1          |8          |1          |2          
+5          |1          |1          |1          |1          |1          
+5          |1          |1          |2          |1          |2          
+5          |1          |1          |3          |1          |1          
+5          |1          |1          |4          |1          |2          
+5          |1          |1          |5          |1          |1          
+5          |1          |1          |6          |1          |2          
+5          |1          |1          |7          |1          |1          
+5          |1          |1          |8          |1          |2          
+7          |1          |1          |1          |1          |1          
+7          |1          |1          |2          |1          |2          
+7          |1          |1          |3          |1          |1          
+7          |1          |1          |4          |1          |2          
+7          |1          |1          |5          |1          |1          
+7          |1          |1          |6          |1          |2          
+7          |1          |1          |7          |1          |1          
+7          |1          |1          |8          |1          |2          
 ij> -----------------------------------
 -- clean up
 ----------------------------------
@@ -661,4 +825,8 @@
 0 rows inserted/updated/deleted
 ij> drop table t3538;
 0 rows inserted/updated/deleted
+ij> drop table t5929_1;
+0 rows inserted/updated/deleted
+ij> drop table t5929_2;
+0 rows inserted/updated/deleted
 ij> 
\ No newline at end of file

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql?rev=718975&r1=718974&r2=718975&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql
Wed Nov 19 06:45:23 2008
@@ -363,6 +363,33 @@
     (select -1 a, 1 b from t3538) x0 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP
    on x0.a = t3538.i; 
 
+--- regression test for an old optimizer problem; when the bug occurred,
+-- hash join queries with 'or' would return incorrect results. 
+create table t5929_1 ( i int, j int, k int);
+create table t5929_2 ( i int, j int, k int);
+insert into t5929_1 values (1, 1, 1), (2, 1, 2);
+insert into t5929_1 select i+2, j, k from t5929_1;
+insert into t5929_1 select i+4, j, k from t5929_1;
+insert into t5929_2 select * from t5929_1;
+
+-- This query should return 32 rows, but it returned 64 before fix
+select * from --DERBY-PROPERTIES joinOrder=fixed 
+t5929_1, t5929_2 --DERBY-PROPERTIES joinStrategy=nestedLoop
+where t5929_1.j=t5929_2.j and (t5929_2.j=1 and (t5929_2.k=1 or t5929_2.j=4));
+
+-- This query should return identical to the above query
+select * from --DERBY-PROPERTIES joinOrder=fixed 
+t5929_1, t5929_2 --DERBY-PROPERTIES joinStrategy=hash 
+where t5929_1.j=t5929_2.j and (t5929_2.j=1 and t5929_2.k=1);
+
+select * from --DERBY-PROPERTIES joinOrder=fixed 
+t5929_1, t5929_2 --DERBY-PROPERTIES joinStrategy=hash 
+where t5929_1.j=t5929_2.j and (t5929_2.j=1 and (t5929_2.k=1 or t5929_2.j+1=5));
+
+select * from --DERBY-PROPERTIES joinOrder=fixed 
+t5929_2, t5929_1 --DERBY-PROPERTIES joinStrategy=hash 
+where t5929_1.j=t5929_2.j and (t5929_2.j=1 and (t5929_2.k=1 or t5929_2.j=4));
+
 -----------------------------------
 -- clean up
 ----------------------------------
@@ -380,3 +407,5 @@
 drop table j1089_source;
 drop table j1089_dest;
 drop table t3538;
+drop table t5929_1;
+drop table t5929_2;



Mime
View raw message