All,

We are using hibernate 3.0.5 and once a while I see following error getting generated due to incorrect SQL generated by hibernate:

Hibernate: select systembean0_.SYSTEMID as SYSTEMID12_0_, systembean0_.Version as Version12_0_, systembean0_.NAME as NAME12_0_, systembean0_.TYPE as TYPE12_0_, systembean0_.DESCRIPTION as DESCRIPT5_12_0_ from SDSYSTEM systembean0_ order by this_.NAME asc
- SQL Error: 904, SQLState: 42000
- ORA-00904: "THIS_"."NAME": invalid identifier

This normally works but occasionally we run into the issue mentioned above. I do not see alias "this_" in the sql query and I think that causes the error to happen. I believe the order by clause should use alias "systembean0_" and not "this_".

I am not sure what causes this problem. Has anyone else see similar problem earlier?

I appreciate any help with this. Below is other information:


Hibernate version: 3.0.5

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class
name="com.avaya.coreservice.admin.toolkit.common.system.SystemBean"
table="SDSYSTEM">

<id name="uniqueId" type="java.lang.String">
<column name="SYSTEMID" length="50" not-null="true"/>
<generator class="uuid"/>
</id>

<version name="version" column="Version" />

<property
name="name"
type="java.lang.String">
<column name="NAME" length="512" not-null="true"/>
</property>

<property
name="type"
type="java.lang.String">
<column name="TYPE" length="50" not-null="false"/>
</property>

<property
name="description"
type="java.lang.String">
<column name="DESCRIPTION" length="1024" not-null="false"/>
</property>

<set name="resourceBeanSet">
<key>
<column name="SYSTEMID" length="50" not-null="false"/>
</key>
<one-to-many class="com.avaya.coreservice.admin.toolkit.common.resource.ResourceBean"/>
</set>

<set name="siteBeanSet" order-by="name asc">
<key>
<column name="SYSTEMID" length="50" not-null="false"/>
</key>
<one-to-many class="com.avaya.coreservice.admin.toolkit.common.site.SiteBean"/>
</set>
</class>
</hibernate-mapping>



Code between sessionFactory.openSession() and session.close():
HibernateUtil.getSession().createCriteria(mCommonTypeBeanClass)
.addOrder(Order.asc("name")).list();


Full stack trace of any exception that occurs:
N/A


Name and version of the database you are using:
Oracle - 10.2.0


The generated SQL (show_sql=true):
select systembean0_.SYSTEMID as SYSTEMID12_0_, systembean0_.Version as Version12_0_, systembean0_.NAME as NAME12_0_, systembean0_.TYPE as TYPE12_0_, systembean0_.DESCRIPTION as DESCRIPT5_12_0_ from SDSYSTEM systembean0_ order by this_.NAME asc


I appreciate any help with this.
Thanks,
-Ashish

Try below code :


Code between sessionFactory.openSession() and session.close():

HibernateUtil.getSession().createCriteria(mCommonTypeBeanClass,"alias")
.addOrder(Order.asc("alias.name")).list();

Can you please explain what difference this change will bring? Please share if you have this information handy, else I will write sample program to see the difference. As I mentioned earlier, this problem doesn't happen always. We run automated build and test and once a while the test fails with similar error. The test doesn't always fails at the same line but the error turns out to be the same. Thanks for your help.
-Ashish

Hi ,
Functonaly there is no difference between your code and my code. but we can define our own alias in my code.

I ran one example for both of these codes and both are working similarly and not getting any error.

I am debugging thorugh the code and trying to find what would cause "this_." to be replaced with "systembean0_.". I am suspecting there is some bug that causes this to happen but do not have any further details yet. Thanks,

Any conclusion on this?

No new update. System test hasn't reported this error for quite some time now so this has become lower priority for us.

Well i am thinking the reason for that would be that there was a mismatch in the mapping file column name for this_ table ( whichever thing you're trying to get ) with the actual column on the table ....

I don't think that is the case because we do not always see this problem. We see this occasionally during our automated tests.

Hi,

i have the same problem. (basically works but once in a while i get this error)
Have you found the solution?

Thanks,

Audrey

Do you think writing the query in SQL would correct it?
There woulnd´t be hibernate intervention on the query so it might work better no?

Thanks,

Audrey