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.
