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

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.