db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject 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
Date Fri, 25 Sep 2009 17:50:32 GMT
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");
+    }
 }



Mime
View raw message