db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From abr...@apache.org
Subject svn commit: r614046 - in /db/derby/code/branches/10.3/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
Date Mon, 21 Jan 2008 23:20:19 GMT
Author: abrown
Date: Mon Jan 21 15:20:16 2008
New Revision: 614046

URL: http://svn.apache.org/viewvc?rev=614046&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).

Port from trunk (svn # 612504) to 10.3 branch with following command:

  svn merge -r 612503:612504 https://svn.apache.org/repos/asf/db/derby/code/trunk

No additional changes required.

Modified:
    db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java
    db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/joins.out
    db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql

Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java?rev=614046&r1=614045&r2=614046&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java
(original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java
Mon Jan 21 15:20:16 2008
@@ -855,7 +855,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/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/joins.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/joins.out?rev=614046&r1=614045&r2=614046&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/joins.out
(original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/joins.out
Mon Jan 21 15:20:16 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/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql?rev=614046&r1=614045&r2=614046&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql
(original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql
Mon Jan 21 15:20:16 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



Mime
View raw message