* 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. + *
* + *+ * If all the input values are SQL NULL, return SQL NULL. Otherwise, return + * the minimum value of the non-NULL inputs. + *
+ * * @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; } /** + ** 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. + *
* + *+ * If all the input values are SQL NULL, return SQL NULL. Otherwise, return + * the maximum value of the non-NULL inputs. + *
+ * * @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"); + } }