Marco Belladelli created DERBY-7153:
---------------------------------------
Summary: 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.16.1.1, 10.15.2.0
Reporter: Marco Belladelli
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 (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.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
