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 ka...@apache.org on 2009/09/18 10:30:25 UTC
svn commit: r816531 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/execute/BaseExpressionActivation.java
testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
Author: kahatlen
Date: Fri Sep 18 08:30:24 2009
New Revision: 816531
URL: http://svn.apache.org/viewvc?rev=816531&view=rev
Log:
DERBY-4372: Wrong result for simple join when index is created
If the inner table in the join is restricted by an explicit or
implicit IN list on an indexed column, an index range scan is
performed between the smallest value and the greatest value in the IN
list.
If the first element in the IN list was NULL, the methods that
calculated the minimum and maximum values always returned NULL, which
resulted in a range scan between NULL and NULL, and no rows were
found. If some other element in the IN list was NULL, it was simply
ignored.
This patch makes the methods always return the min or max non-NULL
value, if such a value exists. Since the index scan is not supposed to
return the NULL values anyway, it is safe to ignore them. The edge
case where all the values are NULL still returns NULL for both min and
max. This makes the index scan return no rows, which is correct
because X IN (NULL, NULL) is never true, regardless of the actual
value of X.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/BaseExpressionActivation.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/execute/BaseExpressionActivation.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/BaseExpressionActivation.java?rev=816531&r1=816530&r2=816531&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/BaseExpressionActivation.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/BaseExpressionActivation.java Fri Sep 18 08:30:24 2009
@@ -46,12 +46,20 @@
/**
+ * <p>
* Get the minimum value of 4 input values. If less than 4 values, input
- * NULL. If more than 4 input values, call this multiple times to
+ * {@code null} for the unused parameters and place them at the end.
+ * If more than 4 input values, call this multiple times to
* accumulate results. Also have judge's type as parameter to have a base
* upon which the comparison is based. An example use is for code
* generation in bug 3858.
+ * </p>
*
+ * <p>
+ * If all the input values are SQL NULL, return SQL NULL. Otherwise, return
+ * the minimum value of the non-NULL inputs.
+ * </p>
+ *
* @param v1 1st value
* @param v2 2nd value
* @param v3 3rd value
@@ -77,23 +85,34 @@
judge = (DataValueDescriptor) new TypeId(judgeTypeFormatId, new UserDefinedTypeIdImpl()).getNull();
DataValueDescriptor minVal = v1;
- if (v2 != null && judge.lessThan(v2, minVal).equals(true))
+ if (v2 != null &&
+ (minVal.isNull() || judge.lessThan(v2, minVal).equals(true)))
minVal = v2;
- if (v3 != null && judge.lessThan(v3, minVal).equals(true))
+ if (v3 != null &&
+ (minVal.isNull() || judge.lessThan(v3, minVal).equals(true)))
minVal = v3;
- if (v4 != null && judge.lessThan(v4, minVal).equals(true))
+ if (v4 != null &&
+ (minVal.isNull() || judge.lessThan(v4, minVal).equals(true)))
minVal = v4;
return minVal;
}
/**
+ * <p>
* Get the maximum value of 4 input values. If less than 4 values, input
- * NULL. If more than 4 input values, call this multiple times to
+ * {@code null} for the unused parameters and place them at the end.
+ * If more than 4 input values, call this multiple times to
* accumulate results. Also have judge's type as parameter to have a base
* upon which the comparison is based. An example use is for code
* generation in bug 3858.
+ * </p>
*
+ * <p>
+ * If all the input values are SQL NULL, return SQL NULL. Otherwise, return
+ * the maximum value of the non-NULL inputs.
+ * </p>
+ *
* @param v1 1st value
* @param v2 2nd value
* @param v3 3rd value
@@ -119,11 +138,14 @@
judge = new TypeId(judgeTypeFormatId, new UserDefinedTypeIdImpl()).getNull();
DataValueDescriptor maxVal = v1;
- if (v2 != null && judge.greaterThan(v2, maxVal).equals(true))
+ if (v2 != null &&
+ (maxVal.isNull() || judge.greaterThan(v2, maxVal).equals(true)))
maxVal = v2;
- if (v3 != null && judge.greaterThan(v3, maxVal).equals(true))
+ if (v3 != null &&
+ (maxVal.isNull() || judge.greaterThan(v3, maxVal).equals(true)))
maxVal = v3;
- if (v4 != null && judge.greaterThan(v4, maxVal).equals(true))
+ if (v4 != null &&
+ (maxVal.isNull() || judge.greaterThan(v4, maxVal).equals(true)))
maxVal = v4;
return maxVal;
}
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=816531&r1=816530&r2=816531&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 Fri Sep 18 08:30:24 2009
@@ -127,6 +127,43 @@
}
/**
+ * DERBY-4372: Some joins used to miss some rows after an index was
+ * created, because the start and stop keys passed to the index scan were
+ * wrong if the IN list in the JOIN condition contained a NULL.
+ */
+ public void testDerby4372() throws SQLException {
+ Statement s = createStatement();
+ s.execute("create table d4372_1 (a int, b int)");
+ s.execute("create table d4372_2 (c int)");
+ s.execute("insert into d4372_1 values (1,1),(null,1),(1,null)," +
+ "(2,2),(2,null),(null,2),(3,3),(null,3),(3,null),(null,null)");
+ s.execute("insert into d4372_2 values (1), (3)");
+
+ String[][] expectedJoinResult = {
+ {"1", "1", "1"},
+ {null, "1", "1"},
+ {"1", null, "1"},
+ {"3", "3", "3"},
+ {null, "3", "3"},
+ {"3", null, "3"}
+ };
+
+ // Try a problematic join, but without an index.
+ PreparedStatement ps = prepareStatement(
+ "select * from d4372_1 join d4372_2 on c in (a, b)");
+
+ JDBC.assertUnorderedResultSet(ps.executeQuery(), expectedJoinResult);
+
+ // Now create an index on C and retry the join. Should still return the
+ // same rows, but didn't before DERBY-4372 was fixed.
+ s.execute("create index d4372_idx on d4372_2(c)");
+ JDBC.assertUnorderedResultSet(ps.executeQuery(), expectedJoinResult);
+
+ s.execute("drop table d4372_1");
+ s.execute("drop table d4372_2");
+ }
+
+ /**
* Test the CROSS JOIN syntax that was added in DERBY-4355.
*/
public void testCrossJoins() throws SQLException {