Yet wrong join ordering? invalid SQL (is this a regression from HHH-14201)
Description
I am gettting a regression by updating Hibernate from 5.3.19 to 5.4.26 AND 5.4.27 (no changes in Code on our side).
Comparing the two SQL statements that were rendered:
Works:
select count(useringrou0_.userId) as col_0_0_ from AAA_UserInGroup useringrou0_ inner join AAA_Group group2_ on useringrou0_.group_groupId=group2_.groupId, AAA_GroupRoleResource grouproler1_ inner join AAA_Group group3_ on grouproler1_.group_groupId=group3_.groupId inner join AAA_Role role4_ on grouproler1_.role_roleId=role4_.roleId inner join AAA_Role_AAA_Right rights5_ on role4_.roleId=rights5_.AAA_Role_roleId inner join AAA_Right right6_ on rights5_.rights_rightId=right6_.rightId where useringrou0_.userId=8 and group2_.groupId=group3_.groupId and grouproler1_.resourceId=5 and (right6_.rightId in (1))
(tried 5.4.26 and 5.4.27) this does not work
select count(useringrou0_.userId) as col_0_0_ from AAA_UserInGroup useringrou0_, AAA_GroupRoleResource grouproler1_ inner join AAA_Group group2_ on useringrou0_.group_groupId=group2_.groupId inner join AAA_Group group3_ on grouproler1_.group_groupId=group3_.groupId inner join AAA_Role role4_ on grouproler1_.role_roleId=role4_.roleId inner join AAA_Role_AAA_Right rights5_ on role4_.roleId=rights5_.AAA_Role_roleId inner join AAA_Right right6_ on rights5_.rights_rightId=right6_.rightId where useringrou0_.userId=8 and group2_.groupId=group3_.groupId and grouproler1_.resourceId=5 and (right6_.rightId in (1))
Gives error
2021-01-15T11:39:31,093 WARN [main] spi.SqlExceptionHelper:137 SQL Error: 20000, SQLState: 42X04
2021-01-15T11:39:31,093 ERROR [main] spi.SqlExceptionHelper:142 Column 'USERINGROU0_.GROUP_GROUPID' 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 'USERINGROU0_.GROUP_GROUPID' is not a column in the target table.
At this point, we only fix critical (according to our own judgement) issues for 5.3 and 5.6. Other than that, we only support the latest minor of the latest major version, which is 6.2 for now, but soon will be 6.3. Also see https://github.com/hibernate/hibernate-orm/wiki/Huge-Project,-Small-Team
FlorianFAugust 2, 2023 at 3:44 PM
I guess we will drop Derby support now - not seeing that this regression will ever be fixed for the Derby Dialect. Last known version to work: 5.4.21.Final, broken since 5.4.22.Final
FlorianFFebruary 15, 2021 at 9:45 AM
<where is the markup editor in this JIRA?>
Should that be covered in a unit test? It’s an N:M relation between “User” and “Group” and Derby Dialect seems to have problems.
The “relation” object:
@Entity(name = "AAA_UserInGroup") @IdClass(UserInGroupPK.class) public class UserInGroup implements Serializable, IDataTransferObject {
…
@Id
private long userId;
@Id
private long groupId;
@ManyToOne
@PrimaryKeyJoinColumn(name = "groupId", referencedColumnName = "groupId")
private Group group;
@ManyToOne()
@PrimaryKeyJoinColumn(name = "userId", referencedColumnName = "userId")
private User user;
public class UserInGroupPK implements Serializable {
@Id private long userId; @Id private long groupId;
Christian BeikovJanuary 15, 2021 at 3:31 PM
Like I said, I need the model and the HQL query, otherwise it’s hard to reproduce.
I am gettting a regression by updating Hibernate from 5.3.19 to 5.4.26 AND 5.4.27 (no changes in Code on our side).
Comparing the two SQL statements that were rendered:
Works:
select count(useringrou0_.userId) as col_0_0_ from AAA_UserInGroup useringrou0_ inner join AAA_Group group2_ on useringrou0_.group_groupId=group2_.groupId, AAA_GroupRoleResource grouproler1_ inner join AAA_Group group3_ on grouproler1_.group_groupId=group3_.groupId inner join AAA_Role role4_ on grouproler1_.role_roleId=role4_.roleId inner join AAA_Role_AAA_Right rights5_ on role4_.roleId=rights5_.AAA_Role_roleId inner join AAA_Right right6_ on rights5_.rights_rightId=right6_.rightId where useringrou0_.userId=8 and group2_.groupId=group3_.groupId and grouproler1_.resourceId=5 and (right6_.rightId in (1))(tried 5.4.26 and 5.4.27) this does not work
select count(useringrou0_.userId) as col_0_0_ from AAA_UserInGroup useringrou0_, AAA_GroupRoleResource grouproler1_ inner join AAA_Group group2_ on useringrou0_.group_groupId=group2_.groupId inner join AAA_Group group3_ on grouproler1_.group_groupId=group3_.groupId inner join AAA_Role role4_ on grouproler1_.role_roleId=role4_.roleId inner join AAA_Role_AAA_Right rights5_ on role4_.roleId=rights5_.AAA_Role_roleId inner join AAA_Right right6_ on rights5_.rights_rightId=right6_.rightId where useringrou0_.userId=8 and group2_.groupId=group3_.groupId and grouproler1_.resourceId=5 and (right6_.rightId in (1))Gives error
2021-01-15T11:39:31,093 WARN [main] spi.SqlExceptionHelper:137 SQL Error: 20000, SQLState: 42X04 2021-01-15T11:39:31,093 ERROR [main] spi.SqlExceptionHelper:142 Column 'USERINGROU0_.GROUP_GROUPID' 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 'USERINGROU0_.GROUP_GROUPID' is not a column in the target table.