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

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

    Details

    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Last commented by a user?:
      true
    • Sprint:

      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:

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

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

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

      and execute the following query:

      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:

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

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

      		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:

      		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.

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: