db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4372) Wrong result for simple join when index is created
Date Thu, 17 Sep 2009 12:48:57 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4372?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Knut Anders Hatlen updated DERBY-4372:
--------------------------------------

    Attachment: derby-4372-1a.stat
                derby-4372-1a.diff

The attached patch adds a fix for the bug and a test case that exposes it.

The javadoc for minValue() and maxValue() in BaseExpressionActivation is clarified with respect
to how NULLs are handled. Also, the methods are changed so that they handle NULLs the same
way regardless of their position in the IN list. Now, SQL NULL is only returned if and only
if all the inputs are SQL NULL. Previously, SQL NULL was returned if and only if the first
input was SQL NULL. This change made the start and stop keys come out right if the first input
was SQL NULL and there was other non-NULL inputs.

I have started the full regression test suite.

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


Mime
View raw message