Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <2075411.1199753974278.JavaMail.jira@brutus>
Date: Mon, 7 Jan 2008 16:59:34 -0800 (PST)
From: "A B (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-3288) wrong query result in presence of a
 unique index
In-Reply-To: <21465409.1197977323171.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-3288?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12556789#action_12556789 ] 

A B commented on DERBY-3288:
----------------------------

Thanks for the great questions, Bryan.

> 1) Why is it that TAB_D is dependent on HOJ but TAB_V is not? 

I admit I don't know.  The process of flattening EXISTS subqueries into specialized FromBaseTables that are called "existsBaseTables" is one I have not investigated.  I know it happens, and I know that an "exist base table" is one of the primary reasons that dependencies exist, at least according to the comment in FromBaseTable.legalJoinOrder():

    public boolean legalJoinOrder(JBitSet assignedTableMap)
    {
        // Only an issue for EXISTS FBTs
        if (existsBaseTable)
        {
            /* Have all of our dependencies been satisfied? */
            return assignedTableMap.contains(dependencyMap);
        }
        return true;
    }

As you say, it seems like the two EXISTS subqueries are pretty similar, but I do not know what is it that causes a dependency on TAB_D only.  I haven't spent time in that particular piece of the code...

> 2) [...] It surprised me a bit that you didn't seem to *move* that
> code; rather you inserted *new* code into step (1b). 

I agree with you on this.  I was originally thinking to reorg the code a bit, but then I just wanted to see if I was on track by making the "quick fix" and checking the results.  It solved the reported problem so I posted it; but yes, the patch is incomplete and a more formal reorganization of the relevant methods would be a good idea.  I don't know how much time I'll have to look at this over the next many days, so I just did a quick "drop" posting of what I found--which is one of the reasons I hadn't officially assigned myself to the issue :)  But you bring up a good point.

> Why don't we need to clear the assigned table map bits at line 809?

I don't think this is necessary because the other pullMe code (line 1093) will eventually (in a round-a-bout way) clean up after the missing "clear assigned table map" from 809--at least in most cases.  But adding the appropriate code to line 809 may fix this bug and render the other use unnecessary, as well.  If that's true then that would probably be the best final approach.

And of course, there's still the SpaceTable.sql issue to investigate...

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