Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1148548946.1254431363503.JavaMail.jira@brutus>
Date: Thu, 1 Oct 2009 14:09:23 -0700 (PDT)
From: "Mamta A. Satoor (JIRA)" <jira@apache.org>
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


    [ https://issues.apache.org/jira/browse/DERBY-4372?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12761342#action_12761342 ] 

Mamta A. Satoor commented on DERBY-4372:
----------------------------------------

Knut, does the fix need to be backported to earlier revisions too? Copying one of your earlier comment for reference. thanks
*************
It seems like the problem can be reproduced even without the join. However, because of DERBY-4376, it is only reproducible this way on 10.2.2.0 and earlier. On 10.3.1.4 and later, this will go into an infinite loop. 
*************

> 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
>             Fix For: 10.5.3.1, 10.6.0.0
>
>         Attachments: derby-4372-1a.diff, derby-4372-1a.stat, experiment.diff
>
>
> 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.