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] Commented: (DERBY-4372) Wrong result for simple join when index is created
Date Thu, 17 Sep 2009 11:44:57 GMT

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

Knut Anders Hatlen commented on DERBY-4372:
-------------------------------------------

I think this is what's causing the bug:

- Internally, (t2.j2 = t1.i1) OR (t2.j2 = t1.j1) is rewritten to t2.j2 IN (t1.i1, t1.j1).

- With an index on J2, we'll choose an index scan of T2, with min(t1.i1, t1.j1) as start key
and max(t1.i1, t1.j1) as stop key.

- The code to create start/stop keys is generated in InListOperatorNode.generateStartStopKey(),
and it uses BaseExpressionActivation.minValue()/maxValue() to find min/max.

- The methods minValue() and maxValue() are not prepared for SQL NULL. If SQL NULL is passed
as the first argument, they will both return SQL NULL. If the first argument is not SQL NULL,
the min or max of the non-NULL values will be returned.

For the join that returns wrong results, the following happens:

When the second row in T1 is read, the join attempts to find a match in T2 where j2 IN (NULL,8).
It calls minValue() and maxValue() with NULL as first arg and 8 as second arg. Both of them
return NULL, so the index scan looks for all rows in T2 where j2 >= NULL and j2 <= NULL.
Comparison with NULL never returns TRUE, so no row is found.

Both minValue() and maxValue() should have returned 8, in which case the index scan would
have found a match.

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