From derby-commits-return-12098-apmail-db-derby-commits-archive=db.apache.org@db.apache.org Fri Sep 25 17:50:57 2009 Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 69268 invoked from network); 25 Sep 2009 17:50:56 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 25 Sep 2009 17:50:56 -0000 Received: (qmail 31686 invoked by uid 500); 25 Sep 2009 17:50:56 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 31657 invoked by uid 500); 25 Sep 2009 17:50:56 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 31648 invoked by uid 99); 25 Sep 2009 17:50:56 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 25 Sep 2009 17:50:56 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 25 Sep 2009 17:50:53 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 582B523888CC; Fri, 25 Sep 2009 17:50:32 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit 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 -0000 To: derby-commits@db.apache.org From: kahatlen@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20090925175032.582B523888CC@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org 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 @@ /** + *

* 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"); + } }