I am using Hibernate 3.5.1(Oracle10gDialect) and query with createAlias & Restrictions function as below HQL, it would generate wrong SQL. In the SQL, you can see ut2_.ID that is wrong SQL alias "ut2" and also add a colon before 'from' text "UPDATE12_22_0_, from"

Is my HQL wrong or Hibernate bug?

HQL
=============================================
session = getSessionFactory().openSession();
coll = (Collection) session.createCriteria(Function.class).
createAlias("application", "app").
add( Restrictions.eq("app.appId", appId) ).
createAlias("userTypes", "ut").
add( Restrictions.eq("ut.id", userTypeId) ).list();

Generated SQL
=============
select this_.ID as ID17_1_, this_.VERSION_NO as VERSION2_17_1_, this_.FUNC_DESC as FUNC3_17_1_,
this_.APP_ID as APP4_17_1_, this_.CREATE_DATE as CREATE5_17_1_, this_.CREATE_USER as CREATE6_17_1_,
this_.UPDATE_DATE as UPDATE7_17_1_, this_.UPDATE_USER as UPDATE8_17_1_, app1_.APP_ID as APP1_22_0_,
app1_.VERSION_NO as VERSION2_22_0_, app1_.APP_DESC as APP3_22_0_, app1_.APP_NAME as APP4_22_0_,
app1_.EXEC_FILENAME as EXEC5_22_0_, app1_.EXEC_PATH as EXEC6_22_0_, app1_.IMAGE_FILENAME as IMAGE7_22_0_,
app1_.SYSTEM_VER as SYSTEM8_22_0_, app1_.CREATE_DATE as CREATE9_22_0_, app1_.CREATE_USER as CREATE10_22_0_,
app1_.UPDATE_DATE as UPDATE11_22_0_, app1_.UPDATE_USER as UPDATE12_22_0_,
from SCS_FUNCTION this_ inner join SCS_APPLICATION app1_ on this_.APP_ID=app1_.APP_ID
inner join SCS_FUNCTION_USER_TYPE usertypes5_ on this_.ID=usertypes5_.FUNC_ID
where app1_.APP_ID=? and ut2_.ID=?

More information:

Hibernate Configuration
=================
<property name="dialect">org.hibernate.dialect.Oracle10gDialect</property>
<property name="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
<property name="hibernate.cache.region.factory_class">net.sf.ehcache.hibernate.EhCacheRegionFactory</property>


JDBC Driver: Oracle 11.2.0.1

Please help

HBML Configuration - Function
===============
<hibernate-mapping
>
<class
name="com.shkco.adsr.security.domain.Function"
table="SCS_FUNCTION"
dynamic-update="true"
dynamic-insert="true"
>

<id
name="id"
column="ID"
type="java.lang.Long"
>
<generator class="sequence">
<param name="sequence">SEQ_SCS_FUNCTION</param>
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-Function.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>
:
:
:
<map
name="userTypes"
table="SCS_FUNCTION_USER_TYPE"
lazy="true"
sort="unsorted"
cascade="none"
>

<key
column="FUNC_ID"
>
</key>

<index
column="USER_TYPE_KEY"
type="long"
/>

<many-to-many
class="com.shkco.adsr.security.domain.UserType"
column="USER_TYPE_ID"
outer-join="auto"
/>

</map>


HBML Configuration - User Types
===============

<hibernate-mapping>
<class
name="com.shkco.adsr.security.domain.UserType"
table="SCS_USER_TYPE"
dynamic-update="true"
dynamic-insert="true"
>

<id
name="id"
column="ID"
type="java.lang.Long"
>
<generator class="sequence">
<param name="sequence">SEQ_SCS_USER_TYPE</param>
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-UserType.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>

<version
name="versionNo"
column="VERSION_NO"
type="int"
/>

<property
name="name"
type="java.lang.String"
update="true"
insert="true"
column="NAME"
/>

<property
name="userTypeId"
type="java.lang.String"
update="true"
insert="true"
column="USER_TYPE_ID"
length="10"
/>

<property
name="globalAdmin"
type="java.lang.Long"
update="true"
insert="true"
column="GLOBAL_ADMIN"
length="1"
/>

<set
name="userControlTypes"
table="SCS_USER_TYPE_LIST"
lazy="true"
cascade="none"
sort="unsorted"
>

<key
column="USER_TYPE_ID"
>
</key>

<many-to-many
class="com.shkco.adsr.security.domain.UserType"
column="USER_CONTROL_TYPE_ID"
outer-join="auto"
/>

</set>

<property
name="createDate"
type="timestamp"
update="true"
insert="true"
column="CREATE_DATE"
not-null="true"
/>

<property
name="createUser"
type="java.lang.Long"
update="true"
insert="true"
column="CREATE_USER"
not-null="true"
/>

<property
name="updateDate"
type="timestamp"
update="true"
insert="true"
column="UPDATE_DATE"
not-null="true"
/>

<property
name="updateUser"
type="java.lang.Long"
update="true"
insert="true"
column="UPDATE_USER"
not-null="true"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-UserType.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>

You are using Criteria not HQL.

The second wrong SQL that you mentioned is just how Hibernate renames the alias internally. For the first one; what is wrong with it? Did you mean comma instead of column before the "from"?

Most importantly what's the error message? I.e. what error does the database give, why the SQL is invalid?

You are correct, it is not HQL as I am using createCriteria function to query data.

I knew Hibernate would rename the alias to own alias. However, you can see the generate SQL "ut2_.ID=?" , the "ut2_" alias should be defined in table e.g (<table name> ut2_) but I cannot find in SQL.

In addition, let see "UPDATE12_22_0_, from SCS_FUNCTION" SQL, a comma followed "from" it is wrong SQL.

The error message is
[java] Caused by: java.sql.SQLException: ORA-00936: missing expression
[java]
[java] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)

[java] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)


More your information,I change to HQL as below, it works fine.

coll = (Collection) session.createQuery(
"from Function as func join func.userTypes as types " +
"where types.id = :pUserTypeId " +
" and func.application.appId = :pAppId")
.setParameter("pUserTypeId", userTypeId)
.setParameter("pAppId", appId)
.list();

I think it should be bug, Hibernate developer/support team please help to solve.