You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by my...@apache.org on 2008/11/19 15:45:24 UTC
svn commit: r718975 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/joins.out tests/lang/joins.sql
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;