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