[
https://issues.apache.org/jira/browse/DERBY-7153?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Marco Belladelli updated DERBY-7153:
------------------------------------
Description:
We encountered what appears to be a problematic query that returns wrong data
for a column located in a left-joined subquery which has additional nested
left-joins.
I have attached a SQL script to the issue to create the simple schema needed to
reproduce this bug as well as the query itself.
The query is the following:
{code:sql}
select
r1_0.id,
r1_0.childId,
c1_1.id,
c1_0.disc_col
from
RootOne r1_0
left join
((select
t.id,
t.disc_col
from
BaseClass t
where
t.disc_col in ('child_a_2', 'child_a_1')) c1_0
join
child_entity c1_1
on c1_0.id=c1_1.id
left join
SubChildEntityA1 c1_2
on c1_0.id=c1_2.id)
on c1_1.id=r1_0.childId;{code}
Here, we get an incorrect result for {{{}c1_0.disc_col{}}}: we would expect a
varchar value ('child_a_1', contained in the BaseClass table), but we get '11'
(which incidently is the value of the {{id}} column).
The same exact query works as expected when either:
* not using {{left}} as the first join type (e.g. both {{inner}} and {{right}}
work)
* not left-joining the SubChildEntityA1 table (using {{inner}} join or
removing the join altogether)
As an added note, we tried inverting the column order in the BaseClass subquery
and we still got an incorrect result, but this time we got a '1' value as a
result.
All tests were run on Apache Derby embedded, both on version 10.15.2.0 and
10.16.1.1.
was:
We encountered what appears to be a problematic query that returns wrong data
for a column located in a left-joined subquery which has additional nested
left-joins.
I have attached a SQL script to the issue to create the simple schema needed to
reproduce this bug as well as the query itself.
The query is the following:
{code:sql}
select
r1_0.id,
r1_0.childId,
c1_1.id,
c1_0.disc_col
from
RootOne r1_0
left join
((select
t.id,
t.disc_col
from
BaseClass t
where
t.disc_col in ('child_a_2', 'child_a_1')) c1_0
join
child_entity c1_1
on c1_0.id=c1_1.id
left join
SubChildEntityA1 c1_2
on c1_0.id=c1_2.id)
on c1_1.id=r1_0.childId;{code}
Here, we get an incorrect result for `c1_0.disc_col`: we would expect a varchar
value (contained in the `BaseClass` table), but we get a `11` (which incidently
is the value of the `id` column).
The same exact query works as expected when either:
* not using `left` as the first join type (e.g. both `inner` and `right` work)
* not left-joining the `SubChildEntityA1` table (using `inner` join or
removing the join altogether)
As an added note, we tried inverting the column order in the `BaseClass`
subquery and we still got an incorrect result, but this time we got a `1`.
All tests were run on Apache Derby embedded, both on version 10.15.2.0 and
10.16.1.1.
> Wrong data returned from left join with subquery and nested left joins
> ----------------------------------------------------------------------
>
> Key: DERBY-7153
> URL: https://issues.apache.org/jira/browse/DERBY-7153
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.15.2.0, 10.16.1.1
> Reporter: Marco Belladelli
> Priority: Major
> Labels: join, subquery
> Attachments: problematic_query-1.sql, schema_creation-1.sql
>
>
> We encountered what appears to be a problematic query that returns wrong data
> for a column located in a left-joined subquery which has additional nested
> left-joins.
> I have attached a SQL script to the issue to create the simple schema needed
> to reproduce this bug as well as the query itself.
> The query is the following:
> {code:sql}
> select
> r1_0.id,
> r1_0.childId,
> c1_1.id,
> c1_0.disc_col
> from
> RootOne r1_0
> left join
> ((select
> t.id,
> t.disc_col
> from
> BaseClass t
> where
> t.disc_col in ('child_a_2', 'child_a_1')) c1_0
> join
> child_entity c1_1
> on c1_0.id=c1_1.id
> left join
> SubChildEntityA1 c1_2
> on c1_0.id=c1_2.id)
> on c1_1.id=r1_0.childId;{code}
> Here, we get an incorrect result for {{{}c1_0.disc_col{}}}: we would expect a
> varchar value ('child_a_1', contained in the BaseClass table), but we get
> '11' (which incidently is the value of the {{id}} column).
>
> The same exact query works as expected when either:
> * not using {{left}} as the first join type (e.g. both {{inner}} and
> {{right}} work)
> * not left-joining the SubChildEntityA1 table (using {{inner}} join or
> removing the join altogether)
> As an added note, we tried inverting the column order in the BaseClass
> subquery and we still got an incorrect result, but this time we got a '1'
> value as a result.
>
> All tests were run on Apache Derby embedded, both on version 10.15.2.0 and
> 10.16.1.1.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
