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] Updated: (DERBY-3288) wrong query result in presence of a unique index
Date Wed, 02 Apr 2008 03:35:24 GMT

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

A B updated DERBY-3288:
-----------------------

    Attachment: releaseNote.html

I don't know if fixes for wrong results *regressions* really need a release note or not, but
I'm attaching one here anyway.  I am *not* checking the "Existing Application Impact" box
as I don't know what the rules are for regressions.  If it is agreed that release notes are
good for regression fixes, as well, then someone can check the appropriate box to have the
release note picked up by the generator tool.  Note: the html file will have to be "scrubbed"
in order to play nicely with the release note tool.

> 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, releaseNote.html
>
>
> 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