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] Created: (DERBY-4063) Constraint causes wrong query result when using exists
Date Wed, 18 Feb 2009 12:27:04 GMT
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


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