[
https://issues.apache.org/jira/browse/DERBY-4372?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12762250#action_12762250
]
Mamta A. Satoor commented on DERBY-4372:
----------------------------------------
I have backported DERBY-4376 into 10.4 codeline and when I run the test script
for this jira entry on that 10.4 codeline, I see that it returns the correct
results even after the index is created. the script I tried is as follows
java -Dij.exceptionTrace=true org.apache.derby.tools.ij
connect 'jdbc:derby:c:/dellater/db;create=true';
create table t(x int);
insert into t values (8);
prepare ps as 'select * from t where x = ? or x = ?';
execute ps using 'values (cast(null as int), 8)';
create index idx on t(x);
execute ps using 'values (cast(null as int), 8)';
I didn't not running the above script before backporting DERBY-4376.
I will try the script on 10.3 and earlier codelines and backport the fix as
needed.
> 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
> Fix For: 10.5.3.1, 10.6.0.0
>
> 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.
