[jira] Resolved: (DERBY-4063) Constraint causes wrong query result when using exists

["Dag H. Wanvik (JIRA)" <ji...@apache.org>]

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

Dag H. Wanvik resolved DERBY-4063.
----------------------------------

       Resolution: Fixed
    Fix Version/s: 10.5.0.0
                   10.4.3.0
                   10.4.2.1

It appears that this bug is fixed by 711615 (DERBY-3880 fix), at least I
can not reproduce it after that change, so resolving. That is not unreasonable,
since that bug also fixes a regression from DERBY-681 and concerns a JOIN issue.

Lars, if you are able to build Derby from the trunk of the 10.4 branch to verify that
the problem is gone, that would be great!



> Constraint causes wrong query result when using exists
> ------------------------------------------------------
>
>                 Key: DERBY-4063
>                 URL: https://issues.apache.org/jira/browse/DERBY-4063
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.2.0
>         Environment: Running Sun JVM 1.6.0_10
>            Reporter: Lars Gråmark
>             Fix For: 10.4.2.1, 10.4.3.0, 10.5.0.0
>
>         Attachments: noconst_nocond.txt, withconst_nocond.txt, withconst_withcond.txt
>
>
> Enabling the primary key constraint yields different results in an exists sub-select.
> The select statement below will return the values 1 and 3 when the primary key constraint is disabled in the project table (project_pk).
> When the constraint is enabled, the same query returns nothing.
> Another interesting effect on the result can be observed when the criteria "AND prj.other = 100" is enabled
> in the join clause and when the constraint is enabled.
> drop table child;
> drop table parent;
> drop table project;
> CREATE TABLE project (id INT NOT NULL, other INT NOT NULL
> --,CONSTRAINT project_pk PRIMARY KEY (id)
> );
> CREATE TABLE parent (id INT NOT NULL, project_id INT NOT NULL);
> CREATE TABLE child  (id INT NOT NULL, parent_id INT NOT NULL);
> insert into project (id, other) values(50,100);
> insert into parent(id, project_id) values (10,50);
> insert into parent(id, project_id) values (20,50);
> insert into child(id, parent_id) values(1,10);
> insert into child(id, parent_id) values(2,20);
> insert into child(id, parent_id) values(3,20);
> SELECT c0.id 
> FROM child c0
> WHERE EXISTS (
>    SELECT MAX(c1.id) 
>    FROM child c1 
>    JOIN parent p ON p.id = c1.parent_id
>    JOIN project prj ON prj.id = p.project_id 
>    --AND prj.other = 100
>    GROUP BY c1.parent_id 
>    HAVING MAX(c1.id) = c0.id
> );

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.