You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2009/09/09 09:49:57 UTC
[jira] Commented: (DERBY-4372) Wrong result for simple join when
index is created
[ https://issues.apache.org/jira/browse/DERBY-4372?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12752968#action_12752968 ]
Knut Anders Hatlen commented on DERBY-4372:
-------------------------------------------
Other variants of the query also show this problem:
ij> select * from t1,t2 where t2.j2 in (t1.i1, t1.j1);
I1 |J1 |I2 |J2
-----------------------------------------------
8 |8 |8 |8
1 row selected
ij> drop index ix2;
0 rows inserted/updated/deleted
ij> select * from t1,t2 where t2.j2 in (t1.i1, t1.j1);
I1 |J1 |I2 |J2
-----------------------------------------------
8 |8 |8 |8
NULL |8 |8 |8
2 rows selected
Oddly enough, if the last condition (t2.j2=t1.j1) is changed into the equivalent form (t1.j1=t2.j2) the query returns correct results regardless of the index (the query plan says that the index is not used in this case).
> 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
> 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.