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] Commented: (DERBY-4063) Constraint causes wrong query result when using exists
Date Thu, 19 Feb 2009 16:28:02 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12675027#action_12675027
] 

Lars Gråmark commented on DERBY-4063:
-------------------------------------

I've compared with 10.2.2.0 and the problem does NOT appear in that version. Cant really say
when the problem started to appear.

I attached three files to this issue that shows the query plan for:
1. Query with no constraint and with disabled condition (noconst_nocond.txt). Query result
is correct.
2. Query with constraint and disabled condition (withconst_nocond.txt). Query result is empty
and incorrect.
3. Query with constraint and enabled condition (withconst_withcond.txt). Query result is correct.

The query plans from (1) and (3) are very similar except from the operation being considered
in (3).
The query plans differ quite much between (1) and (2) but I'm not really confident in the
query plan format so I'll leave the analysis to you. What I can see is that optimizer estimation
is a bit off the track in most places and that an index scan on PROJECT_PK results in zero
rows in (2). You can easily compare the files using meld or some other text-diff tool.
Hope this helps.

> 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