db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Resolved: (DERBY-3288) wrong query result in presence of a unique index
Date Fri, 08 Feb 2008 16:39:08 GMT

     [ https://issues.apache.org/jira/browse/DERBY-3288?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

A B resolved DERBY-3288.
------------------------

       Resolution: Fixed
    Fix Version/s: 10.3.2.2

Committed d3288_10_3_merge.patch to 10.3 with svn # 619932:

  URL: http://svn.apache.org/viewvc?rev=619932&view=rev

Gerald, if you have time to try out the fix, can you do so and report back?

> wrong query result in presence of a unique index
> ------------------------------------------------
>
>                 Key: DERBY-3288
>                 URL: https://issues.apache.org/jira/browse/DERBY-3288
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.4.0.0
>            Reporter: Gerald Khin
>            Assignee: A B
>             Fix For: 10.3.2.2, 10.4.0.0
>
>         Attachments: d3288_10_3_merge.patch, d3288_incomplete_v1.patch, d3288_v2.patch,
DERBY-3288.htm
>
>
> The DDL to reproduce the bug is:
> CREATE TABLE tab_a (PId BIGINT NOT NULL);
> CREATE TABLE tab_c (Id BIGINT NOT NULL PRIMARY KEY, PAId BIGINT NOT NULL, PBId BIGINT
NOT NULL);
> INSERT INTO tab_c VALUES (91, 81, 82);
> INSERT INTO tab_c VALUES (92, 81, 84);
> INSERT INTO tab_c VALUES (93, 81, 88);
> INSERT INTO tab_c VALUES (96, 81, 83);
> CREATE TABLE tab_v (OId BIGINT NOT NULL , UGId BIGINT NOT NULL, val CHAR(1) NOT NULL);
> CREATE UNIQUE INDEX tab_v_i1 ON tab_v (OId, UGId, val);
> CREATE INDEX tab_v_i2 ON tab_v (UGId, val, OId);
> INSERT INTO tab_v VALUES (81, 31, 'A'); 
> INSERT INTO tab_v VALUES (82, 31, 'A'); 
> INSERT INTO tab_v VALUES (83, 31, 'A'); 
> INSERT INTO tab_v VALUES (84, 31, 'A'); 
> INSERT INTO tab_v VALUES (85, 31, 'A'); 
> INSERT INTO tab_v VALUES (86, 31, 'A'); 
> INSERT INTO tab_v VALUES (87, 31, 'A'); 
> INSERT INTO tab_v VALUES (81, 32, 'A'); 
> INSERT INTO tab_v VALUES (82, 32, 'A'); 
> INSERT INTO tab_v VALUES (83, 32, 'A'); 
> INSERT INTO tab_v VALUES (84, 32, 'A'); 
> INSERT INTO tab_v VALUES (85, 32, 'A'); 
> INSERT INTO tab_v VALUES (86, 32, 'A'); 
> INSERT INTO tab_v VALUES (87, 32, 'A');
> CREATE TABLE tab_b (Id BIGINT NOT NULL PRIMARY KEY, OId BIGINT NOT NULL);
> INSERT INTO tab_b VALUES (141, 81);
> INSERT INTO tab_b VALUES (142, 82);
> INSERT INTO tab_b VALUES (143, 84);
> INSERT INTO tab_b VALUES (144, 88);
> INSERT INTO tab_b VALUES (151, 81);
> INSERT INTO tab_b VALUES (152, 83);
> CREATE TABLE tab_d (Id BIGINT NOT NULL PRIMARY KEY, PAId BIGINT NOT NULL, PBId BIGINT
NOT NULL);
> INSERT INTO tab_d VALUES (181, 141, 142);
> INSERT INTO tab_d VALUES (182, 141, 143);
> INSERT INTO tab_d VALUES (186, 151, 152);
> The query returning the wrong result is:
> SELECT tab_b.Id
> FROM tab_b JOIN tab_c ON (tab_b.OId = tab_c.PAId OR tab_b.OId = tab_c.PBId) 
> LEFT OUTER JOIN tab_a ON tab_b.OId = PId 
> WHERE EXISTS (SELECT 'X' FROM tab_d WHERE (PAId = 141 AND PBId = tab_b.Id) OR (PBId =
141 AND PAId = tab_b.Id)) 
>   AND EXISTS (SELECT 'X' FROM tab_v WHERE OId = tab_b.OId AND UGId = 31 AND val = 'A')
> The result should consist of two rows (142),(143), but it returns only one row (142).
> The correct result would be returned if the index tab_v_i1 had been created as non-unique.
> The correct result would also be returned if the condition ...AND val='A' had been replaced
by ...AND val='A'  || ''.

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