db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4063) Constraint causes wrong query result when using exists
Date Thu, 19 Feb 2009 04:03:02 GMT

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


Mime
View raw message