I am in the process of migrating from Hibernate 2.0 to Hibernate 3.0.
I am attempting to execute a Hibernate 3.0 query that was generated from an HQL string. The following is an example of the HQL I am using:

SELECT p.id
FROM Person p LEFT JOIN p.names name
LEFT JOIN name.type type
WHERE name.myName = :myName

The following is an example of the generated SQL string:

select * from (
select person0_.ID as col_0_0_
from PERSON person0_
left outer join NAMES na1_ on person0_.ID=na1_.ID,
left outer join TYPES types2_ on na1_.TYPE_ID=types2_.TYPE_ID
where na1_.MY_NAME)=upper('test') )
where rownum <= 50

Notice that it is adding a comma (,) character between the two left outer joins. This is incorrect since the comma shouldn't be there and as a result, I am getting a SQL Exception.

Note that the nested select is because I am using setFirstResult and setMaxResults on my Query object.

When I used Hibernate 2, the same SQL was generated, but without the incorrect comma. It appears that the SQL generation in Hibernate 3.0 has been completely re-written using ANTLR instead of pure Java code so it is hard to debug into the source to see what is happening.

Has anybody had a similar problem and/or can anybody provide any suggestions on how to get past this problem?

Thanks in advance.

- Andrew Pach

(I have slightly modified the above examples to make them a little easier to read on this post - but the point should still be clear)

3.0.what?

3.0.5

Enter in JIRA.

I get what sounds like the exact same issue of the extra comma appearing before a left outer join, resulting in an SQLGrammarException. The SQL generated by Hibernate 2 did not have the comma.

I'm using MySQL 4.1 and originally tried hibernate 3.0.5 and then after reading this thread I tried the latest source from CVS. Unfortunately I still get the same issue.

The bug is marked as fixed in JIRA (http://opensource.atlassian.com/projects/hibernate/browse/HHH-554).

Here is the generated SQL

select spc.category, count( spc.category ) from SearchableProductCategory as spc
left join spc.searchableProduct sp inner join spc.category c inner join c.categoryHierarchiesByCategoryId ch where ch.co
mp_id.categoryTypeId = 'PRODUCT_CATEGORY' and sp.id in ( select sp.id from SearchableProduct sp left join sp.products p
left join sp.searchableProductCategories spc1 left join spc1.category cat1 left join cat1.categoryHierarchiesByCategoryI
d hi1 inner join p.channelPricings cp inner join cp.channel c where ((hi1.categoryType.id = 'PRODUCT_CATEGORY') and ((ca
t1.categoryId = 'CAT_WASHINGMACHINES'))) and (sp.isVisible = 'T') and (c.name = 'PWS') and (cp.isVisible = 'T') ) and no
t spc.comp_id.category in ('CAT_WASHINGMACHINES') group by spc.category order by count( spc.category ) desc
16:16:48,162 INFO [STDOUT] Hibernate: select searchable0_.category as col_0_0_, count(searchable0_.category) as col_1_0
_, category2_.category_id as category1_, category2_.name as name194_, category2_.short_description as short3_194_, categ
ory2_.long_description as long4_194_, category2_.small_image as small5_194_, category2_.large_image as large6_194_, cate
gory2_.opt_img1 as opt7_194_, category2_.opt_img2 as opt8_194_, category2_.opt_img3 as opt9_194_, category2_.version as
version194_, category2_.created_dts as created11_194_, category2_.created_by as created12_194_, category2_.last_updated_
dts as last13_194_, category2_.last_updated_by as last14_194_ from searchable_product_category searchable0_ left outer j
oin searchable_product searchable1_ on searchable0_.id=searchable1_.id, category category2_ inner join category_hierarch
y categoryhi3_ on category2_.category_id=categoryhi3_.category_id where searchable0_.category=category2_.category_id and
categoryhi3_.category_type_id='PRODUCT_CATEGORY' and (searchable1_.id in (select searchable5_.id from searchable_produc
t searchable5_, product products6_, channel_pricing channelpri10_, channel channel11_, searchable_product_category searc
hable7_, left outer join category category8_ on searchable7_.category=category8_.category_id, category_hierarchy categor
yhi9_ where channelpri10_.id=channel11_.id and products6_.sku=channelpri10_.sku and categoryhi9_.category_type_id='PRODU
CT_CATEGORY' and category8_.category_id='CAT_WASHINGMACHINES' and searchable5_.is_visible='T' and channel11_.name='PWS'
and channelpri10_.is_visible='T')) and not (searchable0_.category in ('CAT_WASHINGMACHINES')) group by searchable0_.cat
egory order by count(searchable0_.category) desc limit ?