Uploaded image for project: 'Hibernate ORM'
  1. Hibernate ORM
  2. HHH-11230

Aggregate on (mappedBy) joined column produces invalid query

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.2.4
    • Fix Version/s: 5.2.5, 5.1.8
    • Component/s: hibernate-core
    • Labels:
      None
    • Environment:
      Apache Derby 10.13.1.1 (10.12.1.1 also)

      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:

      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
      

        Attachments

          Issue links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: