| 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:
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:
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:
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
|