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/25 19:50:32 UTC
svn commit: r818931 - in /db/derby/code/branches/10.5: ./
java/engine/org/apache/derby/impl/sql/execute/BaseExpressionActivation.java
java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
Author: kahatlen
Date: Fri Sep 25 17:50:31 2009
New Revision: 818931
URL: http://svn.apache.org/viewvc?rev=818931&view=rev
Log:
DERBY-4372: Wrong result for simple join when index is created
Merged fix from trunk (revision 816531).
Modified:
db/derby/code/branches/10.5/ (props changed)
db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/execute/BaseExpressionActivation.java
db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
Propchange: db/derby/code/branches/10.5/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Fri Sep 25 17:50:31 2009
@@ -1 +1 @@
-/db/derby/code/trunk:769596,769602,769606,769962,772090,772337,772449,772534,774281,777105,779681,782991,785131,785139,785163,785570,785662,788369,788670,788674,788968,789264,790218,792434,793089,793588,794106,794303,794955,795166,796020,796027,796316,796372,797147,798347,798742,800523,803548,803948,805696,808494,808850,809643,810860,812669,816536
+/db/derby/code/trunk:769596,769602,769606,769962,772090,772337,772449,772534,774281,777105,779681,782991,785131,785139,785163,785570,785662,788369,788670,788674,788968,789264,790218,792434,793089,793588,794106,794303,794955,795166,796020,796027,796316,796372,797147,798347,798742,800523,803548,803948,805696,808494,808850,809643,810860,812669,816531,816536
Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/execute/BaseExpressionActivation.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/execute/BaseExpressionActivation.java?rev=818931&r1=818930&r2=818931&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/execute/BaseExpressionActivation.java (original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/execute/BaseExpressionActivation.java Fri Sep 25 17:50:31 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/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java?rev=818931&r1=818930&r2=818931&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java (original)
+++ db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java Fri Sep 25 17:50:31 2009
@@ -21,6 +21,7 @@
package org.apache.derbyTesting.functionTests.tests.lang;
+import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
@@ -121,4 +122,41 @@
JDBC.assertNullability(rs, new boolean[]{true, false, true, true});
JDBC.assertEmpty(rs);
}
+
+ /**
+ * 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");
+ }
}