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 ab...@apache.org on 2008/01/16 17:46:47 UTC
svn commit: r612504 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/ColumnReference.java
testing/org/apache/derbyTesting/functionTests/master/joins.out
testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql
Author: abrown
Date: Wed Jan 16 08:46:45 2008
New Revision: 612504
URL: http://svn.apache.org/viewvc?rev=612504&view=rev
Log:
DERBY-3023: When remapping column references to their underlying expressions
during Derby preprocessing, make sure that a ColumnReference whose source
expression is a virtual column sets its column position based on the virtual
column id (instead of basing it on the column's position in the base table).
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java
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/engine/org/apache/derby/impl/sql/compile/ColumnReference.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java?rev=612504&r1=612503&r2=612504&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java Wed Jan 16 08:46:45 2008
@@ -844,7 +844,13 @@
"RCL for '" + ft.getTableName() + "'.");
}
- columnNumber = ftRC.getColumnPosition();
+ /* Use the virtual column id if the ResultColumn's expression
+ * is a virtual column (DERBY-3023).
+ */
+ columnNumber =
+ (ftRC.getExpression() instanceof VirtualColumnNode)
+ ? ftRC.getVirtualColumnId()
+ : ftRC.getColumnPosition();
}
else
{
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=612504&r1=612503&r2=612504&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 Jan 16 08:46:45 2008
@@ -443,6 +443,108 @@
0 rows inserted/updated/deleted
ij> drop table xx;
0 rows inserted/updated/deleted
+ij> -- DERBY-3023: join node flattening leads to incorrect search transitive
+-- closure, which in turn leads to incorrect results.
+
+CREATE TABLE d3023_t1 (A INTEGER, B INTEGER);
+0 rows inserted/updated/deleted
+ij> insert into d3023_t1 values (1, 1), (-2, 2), (3, 3);
+3 rows inserted/updated/deleted
+ij> CREATE TABLE d3023_t2 (C INTEGER, D INTEGER);
+0 rows inserted/updated/deleted
+ij> insert into d3023_t2 values (1, -1), (2, -2), (3, -3);
+3 rows inserted/updated/deleted
+ij> CREATE TABLE d3023_t3 (I INTEGER, J INTEGER);
+0 rows inserted/updated/deleted
+ij> insert into d3023_t3 values (-2, 1), (-3, -2);
+2 rows inserted/updated/deleted
+ij> CREATE TABLE d3023_t4 (X INTEGER, Y INTEGER);
+0 rows inserted/updated/deleted
+ij> insert into d3023_t4 values (1, 1), (2, 2), (3, 3);
+3 rows inserted/updated/deleted
+ij> -- Incremental queries building up to the query in question...
+
+select distinct * from
+ d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d;
+A |B |C |D
+-----------------------------------------------
+-2 |2 |2 |-2
+1 |1 |NULL |NULL
+3 |3 |NULL |NULL
+ij> select distinct * from
+ d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d
+ where d3023_t1.a = -2;
+A |B |C |D
+-----------------------------------------------
+-2 |2 |2 |-2
+ij> select distinct * from
+ d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d
+ inner join d3023_t3 on d3023_t1.a = d3023_t3.j;
+A |B |C |D |I |J
+-----------------------------------------------------------------------
+-2 |2 |2 |-2 |-3 |-2
+1 |1 |NULL |NULL |-2 |1
+ij> select distinct * from
+ d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d
+ inner join d3023_t3 on d3023_t1.a = d3023_t3.j
+ where d3023_t1.a = -2;
+A |B |C |D |I |J
+-----------------------------------------------------------------------
+-2 |2 |2 |-2 |-3 |-2
+ij> -- This query only returns a single row, even without the
+-- explicit search predicate.
+select distinct * from
+ d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d
+ inner join d3023_t3 on d3023_t1.a = d3023_t3.j
+ inner join d3023_t4 on d3023_t2.c = d3023_t4.x;
+A |B |C |D |I |J |X |Y
+-----------------------------------------------------------------------------------------------
+-2 |2 |2 |-2 |-3 |-2 |2 |2
+ij> -- Slight variation of the same query. Add a search predicate
+-- enforcing "d3023_t1.a = -2" to the join condition. Since the
+-- row we saw in the previous query satisifies that predicate,
+-- we should see the same row again.
+select distinct * from
+ d3023_t1 left outer join d3023_t2
+ on d3023_t1.a = d3023_t2.d AND d3023_t1.a = -2
+ inner join d3023_t3 on d3023_t1.a = d3023_t3.j
+ inner join d3023_t4 on d3023_t2.c = d3023_t4.x;
+A |B |C |D |I |J |X |Y
+-----------------------------------------------------------------------------------------------
+-2 |2 |2 |-2 |-3 |-2 |2 |2
+ij> -- Same query as above, but with the predicate "d3023_t1.a = -2"
+-- sitting at the top-most (outer) SELECT. That makes the predicate
+-- available for inclusion in the "search transitive closure" logic
+-- for the outer SELECT. That said, prior to the fix for DERBY-3023,
+-- search transitive closure was incorrectly adding a new predicate,
+-- d3023_t4.x = -2, to the query. This was because two different
+-- column references were incorrectly mapped to the same column
+-- position w.r.t. the outer join: i.e. "d3023_t1.a" in the search
+-- predicate "d3023_t1.a = -2" AND "d3023_t2.c" in the join predicate
+-- "d3023_t2.c = d3023_t4.x" were BOTH referencing the first column
+-- in the HalfOuterJoinNode. As a result, the search transitive
+-- closure logic thought that there was transitive equality between
+-- the two predicates, which was incorrect. That in turn caused the
+-- query to return incorrect results (no rows). With the fix for
+-- DERBY-3023, this query should now return a single row.
+
+select distinct * from
+ d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d
+ inner join d3023_t3 on d3023_t1.a = d3023_t3.j
+ inner join d3023_t4 on d3023_t2.c = d3023_t4.x
+ where d3023_t1.a = -2;
+A |B |C |D |I |J |X |Y
+-----------------------------------------------------------------------------------------------
+-2 |2 |2 |-2 |-3 |-2 |2 |2
+ij> -- Cleanup.
+drop table d3023_t1;
+0 rows inserted/updated/deleted
+ij> drop table d3023_t2;
+0 rows inserted/updated/deleted
+ij> drop table d3023_t3;
+0 rows inserted/updated/deleted
+ij> drop table d3023_t4;
+0 rows inserted/updated/deleted
ij> -- Beetle task 5000. Bug found by Websphere. Should not return any rows.
select t1_c1, t1_c2, t2_c1, t2_c2
from t1, t2
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=612504&r1=612503&r2=612504&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 Jan 16 08:46:45 2008
@@ -222,6 +222,84 @@
drop table b2;
drop table xx;
+-- DERBY-3023: join node flattening leads to incorrect search transitive
+-- closure, which in turn leads to incorrect results.
+
+CREATE TABLE d3023_t1 (A INTEGER, B INTEGER);
+insert into d3023_t1 values (1, 1), (-2, 2), (3, 3);
+
+CREATE TABLE d3023_t2 (C INTEGER, D INTEGER);
+insert into d3023_t2 values (1, -1), (2, -2), (3, -3);
+
+CREATE TABLE d3023_t3 (I INTEGER, J INTEGER);
+insert into d3023_t3 values (-2, 1), (-3, -2);
+
+CREATE TABLE d3023_t4 (X INTEGER, Y INTEGER);
+insert into d3023_t4 values (1, 1), (2, 2), (3, 3);
+
+-- Incremental queries building up to the query in question...
+
+select distinct * from
+ d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d;
+
+select distinct * from
+ d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d
+ where d3023_t1.a = -2;
+
+select distinct * from
+ d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d
+ inner join d3023_t3 on d3023_t1.a = d3023_t3.j;
+
+select distinct * from
+ d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d
+ inner join d3023_t3 on d3023_t1.a = d3023_t3.j
+ where d3023_t1.a = -2;
+
+-- This query only returns a single row, even without the
+-- explicit search predicate.
+select distinct * from
+ d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d
+ inner join d3023_t3 on d3023_t1.a = d3023_t3.j
+ inner join d3023_t4 on d3023_t2.c = d3023_t4.x;
+
+-- Slight variation of the same query. Add a search predicate
+-- enforcing "d3023_t1.a = -2" to the join condition. Since the
+-- row we saw in the previous query satisifies that predicate,
+-- we should see the same row again.
+select distinct * from
+ d3023_t1 left outer join d3023_t2
+ on d3023_t1.a = d3023_t2.d AND d3023_t1.a = -2
+ inner join d3023_t3 on d3023_t1.a = d3023_t3.j
+ inner join d3023_t4 on d3023_t2.c = d3023_t4.x;
+
+-- Same query as above, but with the predicate "d3023_t1.a = -2"
+-- sitting at the top-most (outer) SELECT. That makes the predicate
+-- available for inclusion in the "search transitive closure" logic
+-- for the outer SELECT. That said, prior to the fix for DERBY-3023,
+-- search transitive closure was incorrectly adding a new predicate,
+-- d3023_t4.x = -2, to the query. This was because two different
+-- column references were incorrectly mapped to the same column
+-- position w.r.t. the outer join: i.e. "d3023_t1.a" in the search
+-- predicate "d3023_t1.a = -2" AND "d3023_t2.c" in the join predicate
+-- "d3023_t2.c = d3023_t4.x" were BOTH referencing the first column
+-- in the HalfOuterJoinNode. As a result, the search transitive
+-- closure logic thought that there was transitive equality between
+-- the two predicates, which was incorrect. That in turn caused the
+-- query to return incorrect results (no rows). With the fix for
+-- DERBY-3023, this query should now return a single row.
+
+select distinct * from
+ d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d
+ inner join d3023_t3 on d3023_t1.a = d3023_t3.j
+ inner join d3023_t4 on d3023_t2.c = d3023_t4.x
+ where d3023_t1.a = -2;
+
+-- Cleanup.
+drop table d3023_t1;
+drop table d3023_t2;
+drop table d3023_t3;
+drop table d3023_t4;
+
-- Beetle task 5000. Bug found by Websphere. Should not return any rows.
select t1_c1, t1_c2, t2_c1, t2_c2
from t1, t2