[
https://issues.apache.org/jira/browse/DERBY-4063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12674882#action_12674882
]
Bryan Pendleton commented on DERBY-4063:
----------------------------------------
This seems like an interesting problem. It seems possible that it might
have to do with subquery flattening or group by re-writing. But that's just a
wild guess,
I haven't studied the problem in any depth.
Some questions:
- Have you tried this script with other versions of Derby to see if the
behavior varies?
- Can you collect the query plans for the various combinations
(constraint/no-constraint,
prj.other=100/no-prj.other=100), and perhaps do some initial comparisons to
see
how the query plans differ? To do this, you'll want to set
derby.language.logQueryPlan=true,
as described here:
http://db.apache.org/derby/docs/dev/tuning/ctunsetprop34818.html
> 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.
