[Hibernate-issues] [Hibernate-JIRA] Created: (HB-378) error generate SQL from a HQL who use LEFT JOIN(on more levels)

[<leg...@at...>]

Message:

  A new issue has been created in JIRA.

---------------------------------------------------------------------
View the issue:

  http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-378


Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HB-378
    Summary: error generate SQL from a HQL who use LEFT JOIN(on more levels)
       Type: Bug

     Status: Unassigned
   Priority: Blocker

    Project: Hibernate2
 Components: 
             core
   Versions:
             2.0rc2
             2.1
             2.0 final
             2.0.1
             2.0.2
             2.0.3
             2.1 beta 1
             2.1 beta 2
             2.1 beta 3
             2.1 final
             2.1 beta 4

   Assignee: 
   Reporter: Adrian Minciuna

    Created: Fri, 3 Oct 2003 9:51 AM
    Updated: Fri, 3 Oct 2003 9:51 AM
Environment: Windows 2000 sp4, JDK 1.4.1_02

Description:
Bug:	i have 2 classes DACPerson and DACDepartment
DACPerson has an inner object manager with DACPerson , and an object department with type DACDepartment. Thus, has 2 relations <many-to-one>

I need an hql like this	
1) using left join
	select als.name, als0.name, als1.name, als2.name
	from DACPerson as als
	left join als.manager as als0
	left join als.manager.manager as als1
	left join als.manager.department as als2
	where  als.pkid=als.hcod

In this case hibernate generate a wrong SQL :
select 	tstdacpe0_.PKID as x0_0_,tstdacde3_.NAME as x1_0_, tstdacpe2_.NAME as x2_0_, tstdacpe0_.NAME as x3_0_,tstdacpe1_.NAME as x4_0_ from PERSON2 tstdacpe0_ left outer join PERSON2 tstdacpe1_ on tstdacpe0_.MGR_ID=tstdacpe1_.PKID left outer join PERSON2 tstdacpe2_ on tstdacpe1_.MGR_ID=tstdacpe2_.PKID left outer join DEPT tstdacde3_ on tstdacpe1_.Dept_id=tstdacde3_.PKID where and and ((tstdacpe0_.PKID=tstdacpe0_.HCOD ))

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Incorrect syntax near the keyword 'and'.
	at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
	at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source).....
-------------------------------------------
Obs. if i try a HQL,a little bit simple ex: 
select als.name, als0.name, als1.name
from DACPerson as als
left join als.manager as als0
left join als.manager.department as als1
where  als.pkid=als.hcod

i get following error

FINE: net.sf.hibernate.hql.QueryTranslator.compile() - unexpected query compilation problem
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
	at java.lang.String.substring(String.java:1480)
	at java.lang.String.substring(String.java:1447)
	at net.sf.hibernate.sql.QuerySelect.toQueryString(QuerySelect.java:109)
	at net.sf.hibernate.hql.QueryTranslator.renderSQL(QueryTranslator.java:515)
	at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:151)
---------------------------------------------------------
3) If i modify hql like this :
select als.name, als0.name, als1.name, als2.name
	from DACPerson as als
	left join als.manager as als0
	left join als0.manager as als1
	left join als0.department as als2
	where  als.pkid=als.hcod

and IT'S WORK fine
---------------------------------------------------
Obs: i tested also on Hibern8 IDE


---------------------------------------------------------------------
JIRA INFORMATION:
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

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira