[hibernate-issues] [Hibernate-JIRA] Created: (HHH-5270) Oracle Error ORA-01719 on @ManyToMany associations with @FilterJoinTable annotation

[Nico Mack (JIRA)]

Oracle Error ORA-01719 on @ManyToMany associations with @FilterJoinTable annotation
-----------------------------------------------------------------------------------

                 Key: HHH-5270
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5270
             Project: Hibernate Core
          Issue Type: Bug
          Components: query-hql
         Environment: Hibernate 3.4.0GA (shipped with JBoss 5.1GA) Oracle 11R2 Standard Edition
            Reporter: Nico Mack


I have an User EJB Bean who can be assigned via a relation table any number of roles. The following mapping in my User Bean

@ManyToMany(fetch = FetchType.LAZY)
@FilterJoinTable(name = "contextFilter", condition = "context_id = :contextId OR context_id IS NULL")
@JoinTable(name = "rel_user_role", schema = "usermanagement",
		   joinColumns 		    = {@JoinColumn(name = "user_id")},
		   inverseJoinColumns 	= {@JoinColumn(name = "role_id")})

public Set <Role> getRoles() 
	{
	return m_Roles;
	}

results in the following query being generated:

select roles0_.user_id as user4_1_, 
       roles0_.role_id as role3_1_, 
       role1_.id as id5_0_, 
       role1_.created as created5_0_, 
       role1_.creator as creator5_0_, 
       role1_.modified as modified5_0_, 
       role1_.modifier as modifier5_0_, 
       role1_.description as descript4_5_0_, 
       role1_.name as name5_0_ 
       
       from usermanagement.rel_user_role roles0_, 
            usermanagement.role role1_ 
            
where roles0_.role_id=role1_.id(+) and  roles0_.context_id = ? OR roles0_.context_id IS NULL and roles0_.user_id=?

which in turn causes the following oracle error:

java.sql.SQLException: ORA-01719: outer join operator (+) not allowed in operand of OR or IN

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira