Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <722650224.1235060882127.JavaMail.jira@brutus>
Date: Thu, 19 Feb 2009 08:28:02 -0800 (PST)
From: =?utf-8?Q?Lars_Gr=C3=A5mark_=28JIRA=29?= <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-4063) Constraint causes wrong query result
 when using exists
In-Reply-To: <1833019354.1234960024469.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/DERBY-4063?page=3Dcom.atlassian=
.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1267=
5027#action_12675027 ]=20

Lars Gr=C3=A5mark 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). Que=
ry result is empty and incorrect.
3. Query with constraint and enabled condition (withconst_withcond.txt). Qu=
ery 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 co=
nfident 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 e=
asily 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=C3=A5mark
>
> 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 primar=
y 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 criteri=
a "AND prj.other =3D 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=20
> FROM child c0
> WHERE EXISTS (
>    SELECT MAX(c1.id)=20
>    FROM child c1=20
>    JOIN parent p ON p.id =3D c1.parent_id
>    JOIN project prj ON prj.id =3D p.project_id=20
>    --AND prj.other =3D 100
>    GROUP BY c1.parent_id=20
>    HAVING MAX(c1.id) =3D c0.id
> );

--=20
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.