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 da...@apache.org on 2012/09/26 00:56:01 UTC
svn commit: r1390205 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/ColumnReference.java
testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
Author: dag
Date: Tue Sep 25 22:56:00 2012
New Revision: 1390205
URL: http://svn.apache.org/viewvc?rev=1390205&view=rev
Log:
DERBY-5933 SQL sorting error
Patch "d5933-remap+test", which fixes a problem in column reference
remapping in connection with flattening of a left outer join with a
base table. See the Javadoc for the new test case
JoinTest#testDerby_5933 for details.
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/tests/lang/JoinTest.java
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=1390205&r1=1390204&r2=1390205&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 Tue Sep 25 22:56:00 2012
@@ -718,7 +718,10 @@ public class ColumnReference extends Val
/* Find the matching ResultColumn */
source = getSourceResultColumn();
columnName = source.getName();
- columnNumber = source.getColumnPosition();
+ // Use the virtual column id if the ResultColumn's expression
+ // is a virtual column (DERBY-5933).
+ columnNumber = source.getExpression() instanceof VirtualColumnNode ?
+ source.getVirtualColumnId() : source.getColumnPosition();
if (source.getExpression() instanceof ColumnReference)
{
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java?rev=1390205&r1=1390204&r2=1390205&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java Tue Sep 25 22:56:00 2012
@@ -2055,4 +2055,50 @@ public class JoinTest extends BaseJDBCTe
rollback();
}
+
+ /**
+ * DERBY-5933. Error in column reference remapping in connection
+ * with flattening of a left outer join with a base table "d", cf
+ * test case below. In the example, the predicate b1=1 is pushed
+ * into the LOJ node, and in that connection the column reference
+ * to "b1" is remapped. The generated table number of the LOJ node
+ * is 4, and the correct column number of "b1" should be 5, i.e.
+ * [a1: 1, a2: 2, a3: 3, a4: 4, b1:5, c1:6]. However, the
+ * remapping logic erroneously picked b1's column number from its
+ * base table, 1, which really is the position of a1. Now, since
+ * b1 is constant, the column reference b1 "alias" a1 gets marked
+ * as such. Since we are ordering on a1, the sort avoidance logic
+ * is led to believe a1 is constant, and hence sorting is skipped,
+ * hence the wrong result. For related issues, see DERBY-4679,
+ * DERBY-4695, DERBY-3023, DERBY-2526.
+ */
+
+ public void testDerby_5933() throws SQLException {
+ setAutoCommit(false);
+
+ Statement s = createStatement();
+
+ s.executeUpdate("create table a (a1 int, a2 int, a3 int, a4 int)");
+ s.executeUpdate("create table b (b1 int)");
+ s.executeUpdate("create table c (c1 int)");
+ s.executeUpdate("create table d (d1 int)");
+ s.executeUpdate("insert into a values (1,2,1,2), (2,3,1,3), (1,4,1,4)");
+ s.executeUpdate("insert into b values 1");
+ s.executeUpdate("insert into d values 2,3,4");
+
+ ResultSet rs = s.executeQuery("select a1 from " +
+ "a inner join b on a3 = b1 " +
+ " left outer join c on a4 = c1 " +
+ " inner join d on a2 = d1 " +
+ "where b1 = 1 " +
+ "order by a1");
+
+ JDBC.assertFullResultSet(rs, new String[][] {
+ {"1"},
+ {"1"},
+ {"2"}});
+
+ rollback();
+ }
+
}