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

ManyToMany with Filter generates incorrect load SQL

    Details

    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Last commented by a user?:
      true

      Description

      A filter condition for a many-to-many association is added to the where clause instead of to the left outer join on clause when doing a get by ID with eager join fetching. This means that the select returns no records, when there are associated entities, but they are all filtered out.

      The example below generates this SQL:

          select
              myuserdbo0_.id as id1_8_0_,
              myuserdbo0_.active as active2_8_0_,
              myuserdbo0_.name as name3_8_0_,
              roles1_.MYUSER_id as MYUSER_i1_8_1_,
              myroledbo2_.id as roles_id2_9_1_,
              myroledbo2_.id as id1_7_2_,
              myroledbo2_.active as active2_7_2_,
              myroledbo2_.name as name3_7_2_ 
          from
              MYUSER myuserdbo0_ 
          left outer join
              MYUSER_MYROLE roles1_ 
                  on myuserdbo0_.id=roles1_.MYUSER_id 
          left outer join
              MYROLE myroledbo2_ 
                  on roles1_.roles_id=myroledbo2_.id 
          where
              myroledbo2_.active = 1 
              and myuserdbo0_.id=?
      

      instead of

          select
              myuserdbo0_.id as id1_8_0_,
              myuserdbo0_.active as active2_8_0_,
              myuserdbo0_.name as name3_8_0_,
              roles1_.MYUSER_id as MYUSER_i1_8_1_,
              myroledbo2_.id as roles_id2_9_1_,
              myroledbo2_.id as id1_7_2_,
              myroledbo2_.active as active2_7_2_,
              myroledbo2_.name as name3_7_2_ 
          from
              MYUSER myuserdbo0_ 
          left outer join
              MYUSER_MYROLE roles1_ 
                  on myuserdbo0_.id=roles1_.MYUSER_id 
          left outer join
              MYROLE myroledbo2_ 
                  on roles1_.roles_id=myroledbo2_.id and  myroledbo2_.active = 1
          where 
              and myuserdbo0_.id=?
      

      BTW: createCriteria with ManyToMany association and Filter creates the correct SQL.

      Example DBOs:

      @MappedSuperclass
      public abstract class AbstractMyDBO implements Serializable {
      
         @Id
         @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = SequenceName.GENERATOR)
         @SequenceGenerator(name = SequenceName.GENERATOR, sequenceName = SequenceName.SEQUENCE)
         private Integer id;
         ...
      }
      
      @Entity
      @Table(name = "MYUSER")
      @FilterDef(name = "activeUserFilter", defaultCondition = "active = 1")
      @Filter(name = "activeUserFilter")
      public class MyUserDBO extends AbstractMyDBO {
      
         private String name;
      
         private int active;
      
         @ManyToMany()
         @Fetch(FetchMode.JOIN)
         @Filter(name = "activeRoleFilter")
         private Set<MyRoleDBO> roles;
         ...
      }
      
      @Entity
      @Table(name = "MYROLE")
      @FilterDef(name = "activeRoleFilter", defaultCondition = "active = 1")
      @Filter(name = "activeRoleFilter")
      public class MyRoleDBO extends AbstractMyDBO {
      
         private String name;
      
         private int active;
         ...
      }
      

      Example Test using above DBOs: testManyToManyFilterGet fails, while testManyToManyFilterGetAll succeeds.

      public class HibernateFilterTest extends ... {
      
         @Autowired
         private TransactionTemplate transactionTemplate;
      
         @Autowired
         private SessionFactory sessionFactory;
      
         @Test
         public void testManyToManyFilterGet() {
            final List<MyUserDBO> users = new ArrayList<>();
            final List<MyRoleDBO> roles = new ArrayList<>();
            this.doInTransaction(() -> {
               roles.add(this.save(this.role("R1", false)));
               roles.add(this.save(this.role("R2", false)));
               users.add(this.save(this.user("A", true, roles.get(0), roles.get(1))));
            });
            this.doInTransaction(() -> {
               final MyUserDBO dbUser = this.get(MyUserDBO.class, users.get(0).getId());
               Assert.assertNotNull(dbUser);
               Assert.assertThat(dbUser.getRoles(), Matchers.empty());
            });
         }
      
         @Test
         public void testManyToManyFilterGetAll() {
            final List<MyUserDBO> users = new ArrayList<>();
            final List<MyRoleDBO> roles = new ArrayList<>();
            this.doInTransaction(() -> {
               roles.add(this.save(this.role("R1", false)));
               roles.add(this.save(this.role("R2", false)));
               users.add(this.save(this.user("A", true, roles.get(0), roles.get(1))));
            });
            this.doInTransaction(() -> {
               final List<MyUserDBO> dbUsers = this.getAll(MyUserDBO.class);
               Assert.assertNotNull(dbUsers);
               Assert.assertThat(dbUsers.size(), Matchers.is(1));
               Assert.assertThat(dbUsers.get(0).getRoles(), Matchers.empty());
            });
         }
      
         private void doInTransaction(final Runnable r) {
            this.transactionTemplate.execute(new TransactionCallback<Object>() {
               @Override
               public Object doInTransaction(final TransactionStatus status) {
                  r.run();
                  return null;
               }
            });
         }
      
         private Session getSession() {
            final Session session = this.sessionFactory.getCurrentSession();
            session.enableFilter("activeUserFilter");
            session.enableFilter("activeRoleFilter");
            return session;
         }
      
         private <T extends AbstractMyDBO> T save(final T dbo) {
            this.getSession().save(dbo);
            return dbo;
         }
      
         @SuppressWarnings("unchecked")
         private <T> T get(final Class<T> clazz, final Serializable id) {
            return (T) this.getSession().get(clazz, id);
         }
      
         @SuppressWarnings("unchecked")
         private <T> List<T> getAll(final Class<T> clazz) {
            return this.getSession().createCriteria(clazz).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();
         }
      
         private MyUserDBO user(final String name, final boolean active, final MyRoleDBO... roles) {
            final MyUserDBO user = new MyUserDBO();
            user.setName(name);
            user.setActive(active ? 1 : 0);
            user.setRoles(new HashSet<>(Arrays.asList(roles)));
            return user;
         }
      
         private MyRoleDBO role(final String name, final boolean active) {
            final MyRoleDBO role = new MyRoleDBO();
            role.setName(name);
            role.setActive(active ? 1 : 0);
            return role;
         }
      }
      

        Attachments

          Issue links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: