[DERBY-4372] Wrong result for simple join when index is created

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 10.1.3.3, 10.2.2.1, 10.5.1.1
• Fix Version/s: 10.5.3.1, 10.6.1.0
• Component/s: SQL
• Urgency: Urgent
• Issue & fix info: Repro attached
• Bug behavior facts: Wrong query result
• Assignee: Knut Anders Hatlen
• Reporter: Bernt Johnsen
• Created: 08/Sep/09 11:21
• Updated: 21/Jan/11 17:52
• Resolved: 25/Sep/09 17:51

Description

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