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