From derby-dev-return-72949-apmail-db-derby-dev-archive=db.apache.org@db.apache.org Thu Sep 17 14:32:20 2009 Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 77445 invoked from network); 17 Sep 2009 14:32:20 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 17 Sep 2009 14:32:20 -0000 Received: (qmail 19613 invoked by uid 500); 17 Sep 2009 14:32:19 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 19538 invoked by uid 500); 17 Sep 2009 14:32:19 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 19530 invoked by uid 99); 17 Sep 2009 14:32:19 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 17 Sep 2009 14:32:19 +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.140] (HELO brutus.apache.org) (140.211.11.140) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 17 Sep 2009 14:32:17 +0000 Received: from brutus (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id B16B6234C1E9 for ; Thu, 17 Sep 2009 07:31:57 -0700 (PDT) Message-ID: <205267614.1253197917725.JavaMail.jira@brutus> Date: Thu, 17 Sep 2009 07:31:57 -0700 (PDT) From: "Bryan Pendleton (JIRA)" To: derby-dev@db.apache.org Subject: [jira] Commented: (DERBY-4372) Wrong result for simple join when index is created In-Reply-To: <244476502.1252408977463.JavaMail.jira@brutus> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/DERBY-4372?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12756561#action_12756561 ] Bryan Pendleton commented on DERBY-4372: ---------------------------------------- Are BaseExpressionActivation's minValue and maxValue methods used widely? I'm irrationally nervous about changing the handling of NULL because the complex semantics of NULL have been implemented with exquisite care over the years. I suppose an alternative would be to make a change to InListOperatorNode.generateStartStopKey() so that it uses some *new* set of functions, rather than BaseExpressionActivation.minValue()/maxValue() to find min/max. We could call the new methods "minNonNullValue()" and "maxNonNullValue()", or something like that, to help clarify that they had different semantics. > Wrong result for simple join when index is created > -------------------------------------------------- > > Key: DERBY-4372 > URL: https://issues.apache.org/jira/browse/DERBY-4372 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.5.1.1 > Reporter: Bernt M. Johnsen > Assignee: Knut Anders Hatlen > Priority: Critical > Attachments: derby-4372-1a.diff, derby-4372-1a.stat > > > In the example below, the first SELECT has correct result. After the index is created, the second SELECT gives wrong result. > ij> CREATE TABLE t1 (i1 INT, j1 INT); > 0 rows inserted/updated/deleted > ij> CREATE TABLE t2 (i2 INT, j2 INT); > 0 rows inserted/updated/deleted > ij> INSERT INTO t1 VALUES (8, 8),(NULL, 8); > 2 rows inserted/updated/deleted > ij> INSERT INTO t2 VALUES (8, 8); > 1 row inserted/updated/deleted > ij> SELECT * FROM t1 INNER JOIN t2 ON (t2.j2 = t1.i1) OR (t2.j2 = t1.j1); > I1 |J1 |I2 |J2 > ----------------------------------------------- > 8 |8 |8 |8 > NULL |8 |8 |8 > 2 rows selected > ij> CREATE INDEX ix2 ON t2(j2); > 0 rows inserted/updated/deleted > ij> SELECT * FROM t1 INNER JOIN t2 ON (t2.j2 = t1.i1) OR (t2.j2 = t1.j1); > I1 |J1 |I2 |J2 > ----------------------------------------------- > 8 |8 |8 |8 > 1 row selected -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.