Wrong data returned from left join with subquery and nested left joins

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:

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;

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.