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

[Nico Mack (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5270?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=37290#action_37290 ] 

Nico Mack commented on HHH-5270:
--------------------------------

putting the FilterJoinTable condition in parenthesis solved the issue. Perhaps the condition should be wrapped in parenthesis whenever an Operator is detected ?

> 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