[hibernate-issues] [Hibernate-JIRA] Closed: (HHH-940) Generated SQL missing table join in from clause

[Steve Ebersole (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-940?page=all ]
     
Steve Ebersole closed HHH-940:
------------------------------

    Resolution: Fixed

closing this in favor of HHH-1631 since it appears to be the same cause and it has a much more easily reproducible test case.

> Generated SQL missing table join in from clause
> -----------------------------------------------
>
>          Key: HHH-940
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-940
>      Project: Hibernate3
>         Type: Bug

>   Components: query-hql
>     Versions: 3.0.5, 3.1 beta 2
>  Environment: Hibernate 3.0.5 & 3.1 beta 2, MySQL 4.1
>     Reporter: Mike
>     Priority: Minor
>  Attachments: hbms.zip
>
>
> Since moving from hibernate 2 to hibernate 3 a previously working HQL statement now results in the throwing of an SQLGrammarException. 
> The HQL is:
> 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.comp_id.categoryTypeId = 'PRODUCT_CATEGORY' 
>  and sp.id in ( 
> select sp.id 
>  from SearchableProduct sp 
>  left join sp.products p left 
>  join sp.searchableProductCategories spc 
> left join spc.category cat 
>  left join cat.categoryHierarchiesByCategoryId hi
> inner join p.channelPricings cp 
>  inner join cp.channel c 
> where 
> (hi.categoryType.id = 'PRODUCT_BRAND') 
> and 
> ((lower(cat.name) like '%washing%') 
> or (lower(p.sku) like '%washing%') or (lower(p.manufacturer) like '%washing%') 
> or (lower(sp.keywords) like '%washing%') or (lower(sp.title) like '%washing%') 
>  or (lower(sp.longDescription) like '%washing%') or (lower(sp.shortDescription) like '%washing%')) 
> and (sp.isVisible = 'T') and (c.name = 'PWS') and (cp.isVisible = 'T') ) 
> group by spc.category order by count( spc.category ) desc
> The exception is: 
> 11:43:02,714 WARN  [JDBCExceptionReporter] SQL Error: 1054, SQLState: 42S22
> 11:43:02,714 ERROR [JDBCExceptionReporter] null,  message from server: "Unknown column 'categoryhi9_.category_type_id' in 'where clause'"
>  
> Generated SQL is:
> select 
> searchable0_.category as col_0_0_, count(searchable0_.category) as col_1_0_, category2_.category_id as category1_, category2_.name as name4_, category2_.short_description as short3_4_, category2_.long_description as long4_4_, category2_.small_image as small5_4_, category2_.large_image as large6_4_, category2_.opt_img1 as opt7_4_, category2_.opt_img2 as opt8_4_, category2_.opt_img3 as opt9_4_, category2_.version as version4_, category2_.created_dts as created11_4_, category2_.created_by as created12_4_, category2_.last_updated_dts as last13_4_, category2_.last_updated_by as last14_4_ 
> from 
> searchable_product_category searchable0_ left outer join searchable_product searchable1_ on searchable0_.id=searchable1_.id, category category2_ inner join category_hierarchy 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_product searchable5_, product products6_, channel_pricing channelpri10_, channel channel11_, searchable_product_category searchable7_  
> where 
> channelpri10_.id=channel11_.id and products6_.sku=channelpri10_.sku and categoryhi9_.category_type_id='PRODUCT_BRAND' and (lower(category2_.name)like '%washing%' or lower(products6_.sku)like '%washing%' or lower(products6_.manufacturer)like '%washing%' or lower(searchable5_.keywords)like '%washing%' or lower(searchable5_.title)like '%washing%' or lower(searchable5_.long_description)like '%washing%' or lower(searchable5_.short_description)like '%washing%') and searchable5_.is_visible='T' and channel11_.name='PWS' and channelpri10_.is_visible='T')) group by searchable0_.category order by count(searchable0_.category) desc
>  
> The SQL refers to categoryhi9 in the where clause but the table alias is not declared in the from clause despite being in the original HQL. 
> Thanks.

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