[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-2189) Left Outer Join Conditions

[Yajun Shi (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2189?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Yajun Shi updated HHH-2189:
---------------------------

    Attachment: SUP-3701.zip

During my testing, there is no bug in Hibernate 3.1.3 and Hibernate 3.2.3 on this issue if the case is tested with Oracle 10g. My test case is attached here.

In brief, HQL:

from Company comp
left outer join comp.employees empl
with empl.birthday != :date

then the SQL produced:

select
         company0_.id as id0_0_,
         employees1_.id as id1_1_,
         company0_.companyName as companyN2_0_0_,
         employees1_.name as name1_1_,
         employees1_.birthday as birthday1_1_,
         employees1_.companyId as companyId1_1_
     from
         TB_COMPANY_TEMP company0_
     left outer join
         TB_EMPLOYEE_TEMP employees1_
             on company0_.id=employees1_.companyId
             and (
                 employees1_.birthday!=?
             )

and there is no exception thrown.

So you don't need the patch if Oracle 10g is used.

Regards,

Yajun Shi

SourceLabs - http://www.sourcelabs.com
Dependable Open Source Systems

> Left Outer Join Conditions
> --------------------------
>
>                 Key: HHH-2189
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2189
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: query-sql
>    Affects Versions: 3.1.3
>            Reporter: yann degat
>            Priority: Blocker
>         Attachments: OracleJoinFragment.java, OracleJoinFragment.java, SUP-3701.zip
>
>   Original Estimate: 10 minutes
>  Remaining Estimate: 10 minutes
>
> the "org.hibernate.sql.OracleJoinFragment.addLeftOuterJoinCondition"
> does not take the "!=" operator into account.
> it places the "(+)" like this "!(+)=" instead of " (+) !="
> (  != is a valid operator for Oracle )
> and it returns  (+)<(+)> for the '<>' operator
> here's a patch. (maybe not the best one )
> 	/**
> 	 * This method is a bit of a hack, and assumes
> 	 * that the column on the "right" side of the
> 	 * join appears on the "left" side of the
> 	 * operator, which is extremely wierd if this
> 	 * was a normal join condition, but is natural
> 	 * for a filter.
> 	 */
> 	private void addLeftOuterJoinCondition(String on) {
> 		StringBuffer buf = new StringBuffer( on );
> 		for ( int i = 0; i < buf.length(); i++ ) {
>             char character = buf.charAt( i );
>             boolean isInsertPoint = OPERATORS.contains( new Character( character ) ) ||
>            ( character == ' ' && buf.length() > i + 3 && "is ".equals( buf.substring( i + 1, i + 4 ) ) ) ;
>             if( character == '<' && buf.length() > i + 1 && ">".equals( buf.substring( i + 1, i + 2 ) ) ){
>                 isInsertPoint = false;
>                 buf.insert( i, "(+)" );
>                 i += 3 + 2;
>             }
>             
>             if ( isInsertPoint ) {
>                 buf.insert( i, "(+)" );
>                 i += 3;
>             }
>            if( character == '!' && buf.length() > i + 1 && "=".equals( buf.substring( i + 1, i + 2 ) ) ){
>                buf.insert( i, "(+)" );
>                i += 3 + 2;
>            }
> 		}
> 		addCondition( buf.toString() );
> 	}

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