Aggregate on (mappedBy) joined column produces invalid query

Description

We came across the following JQL query (which admittedly is not very useful as usedBy is actually mappedBy=uses - but I guess this can be reproduced with more useful ones) which starting with some changes in 5.2.3-5.2.4 (cannot really test 5.2.3 as this is broken for another reason) produces an query that derby considers invalid, although I'm not exactly sure why - Derby bug? The trigger seems to be that the inverse relation join now is replaced by a subquery.

JQL query:

1 2 3 4 5 6 SELECT usedBy.id, usedBy.name, COUNT(inverse.id) FROM AbstractConfigurationObject config INNER JOIN config.usedBy usedBy LEFT JOIN usedBy.uses inverse ON inverse.id = config.id WHERE config.id = :id GROUP BY usedBy.id, usedBy.name

Resulting query:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 select abstractco2_.ID as col_0_0_, abstractco2_.NAME as col_1_0_, count(abstractco4_.ID) as col_2_0_ from APP."config" abstractco0_ inner join APP."config_config" usedby1_ on abstractco0_.ID=usedby1_.USES_ID inner join APP."config" abstractco2_ on usedby1_.USEDBY_ID=abstractco2_.ID left outer join (select uses3_.* from APP."config_config" uses3_ left outer join APP."config" abstractco4_ on uses3_.USES_ID=abstractco4_.ID) uses3_ on abstractco2_.ID=uses3_.USEDBY_ID and (abstractco4_.ID=abstractco0_.ID) left outer join APP."config" abstractco4_ on uses3_.USES_ID=abstractco4_.ID where abstractco0_.ID=? group by abstractco2_.ID , abstractco2_.NAME

Results in SQLSytaxErrorException:
Column 'ABSTRACTCO4_.ID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'ABSTRACTCO4_.ID' is not a column in the target table.

Query in 5.2.2:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 select abstractco2_.ID as col_0_0_, abstractco2_.NAME as col_1_0_, count(abstractco4_.ID) as col_2_0_ from APP."config" abstractco0_ inner join APP."config_config" usedby1_ on abstractco0_.ID=usedby1_.USES_ID inner join APP."config" abstractco2_ on usedby1_.USEDBY_ID=abstractco2_.ID left outer join APP."config_config" uses3_ on abstractco2_.ID=uses3_.USEDBY_ID left outer join APP."config" abstractco4_ on uses3_.USES_ID=abstractco4_.ID and (abstractco4_.ID=abstractco0_.ID) where abstractco0_.ID=? group by abstractco2_.ID , abstractco2_.NAME

Environment

Apache Derby 10.13.1.1 (10.12.1.1 also)

Status

Assignee

Christian Beikov

Reporter

Moritz Bechler

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

backportDecision

None

Components

Affects versions

5.2.4

Priority

Major