db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lars Gråmark (JIRA) <j...@apache.org>
Subject [jira] Updated: (DERBY-4063) Constraint causes wrong query result when using exists
Date Thu, 19 Feb 2009 16:32:02 GMT

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

Lars Gråmark updated DERBY-4063:
--------------------------------

    Attachment: withconst_nocond.txt

Constraint enabled. No prj.other condition.

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


Mime
View raw message