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