ORA-00904 due to adding columns of type "*_ORDER"

Hibernate ORM
1

I am upgrading to 6.4 from 5.6 and started getting error ORA-00904 when trying to get a collection from an entity. The collection is declared as follows in the user entity:

	@OneToMany(mappedBy = "id.userId", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
	@OrderBy("id.userGroupId")
	@CollectionType(type = datatype.jpa.CustomPersistentListType.class)
	private List<UserGroupUser> groups = new ArrayList<>();

When trying to do user.getGroups() hibernate makes a request:

Hibernate: 
    select
        g1_0.USER_ID,
        g1_0.groups_ORDER,
        g1_0.USR_GRP_ID,
        g1_0.OWNER_FLG,
        g1_0.EXPIRATION_DT,
        g1_0.VERSION 
    from
        SC_USR_GRP_USR g1_0 
    where
        g1_0.USER_ID=? 
    order by
        g1_0.USR_GRP_ID

The column g1_0.groups_ORDER does not exist and I get ORA-00904. This happens with all collections. I logically suspect that the problem is in the sorting, but I couldn’t find similar errors to figure out what I’m doing wrong.

Could this be occurring because of my CustomPersistentListType? Where should I be paying attention?

2

Could you please share the code for CustomPersistentListType.class? Anyways, I’m guessing the “problem” is you custom type defines CollectionClassification.LIST as getClassification(), so Hibernate is adding the “_ORDER” column to preserve the order of elements in the association.

Note that by default java.util.List collections in Hibernate have CollectionClassification.BAG semantifcs (unordered), unless something different is explicitly specified by hibernate.mapping.default_list_semantics.

3

You were right, it was a bug of mine related to getClassification()