Dynamic filter parameter can be bound in wrong order when applied to OneToMany collections

Description

There is a problem with the parameter binding of dynamic filter parameters in case you use a dynamic filter definition on a OneToMany child collection. The order in which the parameters get bound does not necessarily match the order in which the query defined them.

I am quite new to this so I try to explain as best as I can.

I have a root entity with a filter definition for the deletion timestamp column, and I also apply this filter on its child entities for their deletion timestamp columns:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 @Entity @Table( name = "`ArticleRevision`" ) @FilterDefs( { @FilterDef( name = "aliveOnly", parameters = { @ParamDef( name = "aliveTimestamp", type = "timestamp" ) }, defaultCondition = "`DeletionTimestamp` = :aliveTimestamp" ) } ) @Filters( { @Filter( name = "aliveOnly", condition = "`DeletionTimestamp` = :aliveTimestamp" ) } ) public class ArticleRevision { @Column( name = "`ArticleID`" ) private Long articleId = null; @Column( name = "`DeletionTimestamp`" ) protected Timestamp deletionTimestamp; @OneToMany( mappedBy = "articleRevision", cascade = CascadeType.ALL, fetch = FetchType.LAZY ) @Filter( name = "aliveOnly" ) private Set< ArticleTrading > articleTradings = new HashSet< ArticleTrading >(); }

This is one of the child entites:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 @Entity @Table( name = "`ArticleTrading`" ) public class ArticleTrading { @ManyToOne( fetch = FetchType.LAZY ) @JoinColumn( name = "`ArticleRevisionID`", nullable = false ) private ArticleRevision articleRevision = null; @Column( name = "`PartyID`" ) private long partyId = 0; @Column( name = "`Classifier`" ) private String classifier = null; @Column( name = "`DeletionTimestamp`" ) protected Timestamp deletionTimestamp; }

Now if I enable the filter in the session:

1 2 3 Filter enableFilter = session.enableFilter( "aliveOnly" ); enableFilter.setParameter( "aliveTimestamp", Timestamp.valueOf( "9999-12-31 00:00:00" ) ); enableFilter.validate();

and execute the following query:

1 2 3 4 select a_root.articleId from ArticleRevision as a_root left join a_root.articleTradings as al_0 with ( (al_0.partyId = :p_0) and (al_0.classifier = :p_1) )

the query will result in the following SQL:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 [SQL] select articlerev0_."ArticleID" as col_0_0_ from "ArticleRevision" articlerev0_ left outer join "ArticleTrading" articletra1_ on articlerev0_."ID"=articletra1_."ArticleRevisionID" and articletra1_."DeletionTimestamp" = ? and ( articletra1_."PartyID"=? and articletra1_."Classifier"=? ) where articlerev0_."DeletionTimestamp" = ?

Now watch how the parameters get bound in the wrong order (the dynamic filter is parameter 3 although it should be 1):

1 2 3 4 [BasicBinder] binding parameter [1] as [BIGINT] - 1 [BasicBinder] binding parameter [2] as [VARCHAR] - Classifier [BasicBinder] binding parameter [3] as [TIMESTAMP] - 9999-12-31 00:00:00.0 [BasicBinder] binding parameter [4] as [TIMESTAMP] - 9999-12-31 00:00:00.0

I have debugged the code and I found out that although method JoinProcessor#processDynamicFilterParameters() adds the dynamic filter parameters last to the current JOIN, the SQL statement generation happens in the opposite order in method JoinSequence#toJoinFragment() and puts it first:

1 2 for ( Join join: joins ) { String on = join.getAssociationType().getOnCondition( join.getAlias(), factory, enabledFilters );

That is why I would suggest that the easiest bugfix for this is just putting the filter condition in the JoinSequence#toJoinFragment() method to the end as well:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 for ( Join join: joins ) { String condition = ""; if ( withClauseFragment != null && !isManyToManyRoot( join.joinable )) { condition += " and " + withClauseFragment; } String on = join.getAssociationType().getOnCondition( join.getAlias(), factory, enabledFilters ); if ( last != null && isManyToManyRoot( last ) && ( ( QueryableCollection ) last ).getElementType() == join.getAssociationType() ) { // the current join represents the join between a many-to-many association table // and its "target" table. Here we need to apply any additional filters // defined specifically on the many-to-many String manyToManyFilter = ( ( QueryableCollection ) last ) .getManyToManyFilterFragment( join.getAlias(), enabledFilters ); condition += "".equals( manyToManyFilter ) ? on : "".equals( on ) ? manyToManyFilter : on + " and " + manyToManyFilter; } else { condition += on; } joinFragment.addJoin( join.getJoinable().getTableName(), join.getAlias(), join.getLHSColumns(), JoinHelper.getRHSColumnNames( join.getAssociationType(), factory ), join.joinType, condition ); if (includeExtraJoins) { //TODO: not quite sure about the full implications of this! addExtraJoins( joinFragment, join.getAlias(), join.getJoinable(), join.joinType == JoinType.INNER_JOIN ); } last = join.getJoinable(); }

This is a blocker because there is no workaround for that case.

Environment

None

Status

Assignee

Andrea Boriero

Reporter

Markus

Fix versions

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

4.2.20
5.1.0
5.0.9
5.2.1

Priority

Blocker
Configure