db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Resolved: (DERBY-4063) Constraint causes wrong query result when using exists
Date Thu, 19 Mar 2009 17:16:50 GMT

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

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:
>         Environment: Running Sun JVM 1.6.0_10
>            Reporter: Lars Gråmark
>             Fix For:,,
>         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;
> --,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
>    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.

View raw message