[
https://issues.apache.org/jira/browse/DERBY-3288?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Bryan Pendleton reassigned DERBY-3288:
--------------------------------------
Assignee: A B
Hi Army, thanks for looking at this issue (I marked it as assigned to you).
Your assessment makes complete sense to me; thanks for writing
the issues up so clearly and completely!
Two questions occurred to me as I read your writeup:
1) Why is it that TAB_D is dependent on HOJ but TAB_V is not?
Both of the exists subqueries look very similar to me; they each
seem to be correlated to TAB_B in the HOJ, and isn't that where
the dependency arises? It isn't really directly relevant to the bug,
I was just hoping you could educate me on why the one subquery
was dependent on the B-C-A join but the other wasn't.
2) The way I read your patch, you basically caused steps (3) and (4)
to be done as part of step (1b), which makes complete sense to
me. But it surprised me a bit that you didn't seem to *move* that
code; rather you inserted *new* code into step (1b). That is, it now
seems like there are multiple places in OptimizerImpl.getNextPermutation()
which all seem to be doing the same thing:
- your new code to be inserted at line 589 or so
- The hunk of code at line 809 or so, starting with the comment
"We are going to try an optimizable at the current join order position"
- The hunk of code at line 1093 or so, starting with the comment
"Clear the assigned table map"
I'm afraid I don't really have a very clear comment to make here; I'm
just observing that getNextPermutation() appears to compute the
"pullMe" optimizable twice, and in one case it clears the assignedTableMap
bits but in the other it doesn't, and your patch adds a 3rd place where
we compute the pullMe optimizable (and clear the assignedTableMap bits).
Perhaps this question puts my finger most closely on my confusion:
Why don't we need to clear the assigned table map bits at line 809?
> 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
> Attachments: d3288_incomplete_v1.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.
