I seem to be having a sybase-specific problem with criteria.addOrder(Order.asc("someproperty"));, when "someproperty" is a formula instead of a column.

I'm using SybaseDialect, and it seems that when I try to order by a formula, I get an exception from my driver stating the following:
"Subqueries are not allowed in an ORDER BY clause."

of course, this is somewhat expected because you can't explicitly order by a subquery in sybase. however, what you can do is alias the subquery and then order by its alias. the generated sql shows that hibernate is aliasing my subquery in the from clause, but in the order by it refers to the subquery directly instead of by its alias. I'm sure there is a good reason for this, and am wondering why the query is generated in this manner?

also wondering if anyone has run into this problem, and if anyone came up with a good workaround for it. I'm thinking I could map the object separately with a discriminator to acheive the same end result. details below, thanks for your help!


Hibernate version:3.2

Mapping documents:
<class name="SomeBean" table="SomeTable" schema="dbo">
<id name="id" type="int">
<column name="work_item_id" />
<generator class="assigned" />
</id>
<property name="toDt" type="timestamp">
<column name="to_dt" length="23" />
</property>

<property name="sticky" type="boolean"
formula="(select count(SA.string_value) from SomeAttribute SA where SA.work_item_id = work_item_id and SA.work_item_attr_cd = 'SOMECODE')"/>
</class>

Code between sessionFactory.openSession() and session.close():
Criteria criteria = getSession().createCriteria(SomeBean.class);
criteria.addOrder(Order.asc("sticky"));
return crit.list();

Full stack trace of any exception that occurs:

2008-09-10 10:01:15,709 ERROR [main] util.JDBCExceptionReporter (JDBCExceptionReporter.java:78) - Subqueries are not allowed in an ORDER BY clause.

org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at com.gs.res.distribution.dashboard.dao.impl.HibernateDashboardPostDAO.getPosts(HibernateDashboardPostDAO.java:55)
at com.gs.res.distribution.dashboard.dao.DashboardPostDAOTest.testGetPostsByCount(DashboardPostDAOTest.java:99)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)
at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:71)
at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:45)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: com.sybase.jdbc2.jdbc.SybSQLException: Subqueries are not allowed in an ORDER BY clause.

at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2846)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:2168)
at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:220)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:203)
at com.sybase.jdbc2.jdbc.SybStatement.queryLoop(SybStatement.java:1596)
at com.sybase.jdbc2.jdbc.SybStatement.executeQuery(SybStatement.java:1581)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:85)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)

Name and version of the database you are using:
Sybase 12.5.4

The generated SQL (show_sql=true):
select
this_.work_item_id as work1_23_0_,
this_.to_dt as to5_23_0_,

(select
count(SA.string_value)
from
SomeAttribute SA
where
SA.work_item_id = this_.work_item_id
and SA.work_item_attr_cd = 'SOMECODE') as formula4_0_,

from
SomeTable this_
where
this_.some_id=?
order by
(select
count(SA.string_value)
from
SomeAttribute SA
where
SA.work_item_id = this_.work_item_id
and SA.work_item_attr_cd = 'SOMECODE') asc

I have the same problem. No one has a solution?