db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4372) Wrong result for simple join when index is created
Date Thu, 17 Sep 2009 15:40:58 GMT

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

Bryan Pendleton commented on DERBY-4372:
----------------------------------------

Well, your static analysis seems pretty persuasive to me.

And, like I said, it is an irrational fear.

But that code in inListOperatorNode.generateStartStopKey has been
present since since the original Derby contribution to Apache, if I'm
reading the svn history correctly
http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InListOperatorNode.java?revision=37083&view=markup
so that's very old code indeed.

Of course, if I'm understanding your earlier comments here and in DERBY-4376
correctly, this behavior has been around since the original Derby contribution to Apache,
too.

After re-reading your comments and reading the attached patch file, I can't see any reason
not to proceed with your change.

Thanks for letting me raise my concern, and thanks for taking the time to study this issue
in detail!


> 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, 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.


Mime
View raw message