[DERBY-4063] Constraint causes wrong query result when using exists

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.4.2.0
• Fix Version/s: 10.3.3.1, 10.4.2.1, 10.5.1.1
• Component/s: SQL
• Environment: Running Sun JVM 1.6.0_10
• Issue & fix info: High Value Fix
• Bug behavior facts: Regression, Wrong query result
• Assignee: Unassigned
• Reporter: Lars Gråmark
• Created: 18/Feb/09 12:25
• Updated: 21/Jan/11 17:52
• Resolved: 19/Mar/09 17:15

Description

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
);

Issue Links

• is related to: DERBY-3880 NPE on a query with having clause involving a join
• relates to: DERBY-681 Eliminate the parser's rewriting of the abstract syntax tree for queries with GROUP BY and/or HAVING clauses