package model;

public class Foo {
   int id;
   private int val;
   private Bar bar;
   
   public int getId() {
      return id;
   }
   public void setId(int id) {
      this.id = id;
   }
   public Bar getBar() {
      return bar;
   }
   public void setBar(Bar bar) {
      this.bar = bar;
   }
   public int getVal() {
      return val;
   }
   public void setVal(int val) {
      this.val = val;
   }
}

package model;

import java.util.List;

public class Bar {
   private int id;
   private List foos;         // List of Foo, mapped with bag
   private int minimum;      // Minimum Foo.val in list, mapped with formula
   private int maximum;      // Maximum Foo.val in list, mapped with formula
   
   
   public int getId() {
      return id;
   }
   public void setId(int id) {
      this.id = id;
   }
   public List getFoos() {
      return foos;
   }
   public void setFoos(List foos) {
      this.foos = foos;
   }
   public int getMaximum() {
      return maximum;
   }
   public void setMaximum(int maximum) {
      this.maximum = maximum;
   }
   public int getMinimum() {
      return minimum;
   }
   public void setMinimum(int minimum) {
      this.minimum = minimum;
   }
}

<?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 package="model">
   <class name="Foo" table="foo_table">

       <id name="id" type="integer" unsaved-value="0" column="FOO_ID">
            <generator class="native"></generator>
       </id>

       <many-to-one name="bar" class="Bar" column="BAR_ID" index="IX_BAR_VAL"/>

       <property name="val" not-null="true">
          <column name="VAL" />
       </property>

   </class>
</hibernate-mapping>

<?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 package="model">
   <class name="Bar" table="bar_table">
      <id name="id" type="integer" unsaved-value="0" column="BAR_ID">
            <generator class="native"></generator>
       </id>   
       <property name="minimum">
          <formula>
             ( SELECT min( foo.val ) FROM foo_table foo WHERE foo.bar_id = bar_id )
          </formula>
       </property>       
       <property name="maximum">
          <formula>
             ( SELECT max( foo.val ) FROM foo_table foo WHERE foo.bar_id = bar_id )
          </formula>
       </property>
       <bag name="foos" inverse="true">
            <key column="BAR_ID" />
            <one-to-many class="Foo" />
       </bag>
   </class>
</hibernate-mapping>

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
 
     <!-- DB2, Database connection settings -->
    <property name="connection.driver_class">COM.ibm.db2.jdbc.app.DB2Driver</property>
    <property name="connection.url">jdbc:db2:BUG_TEST</property>
    <property name="dialect">org.hibernate.dialect.DB2Dialect</property>

      <property name="current_session_context_class">thread</property>
    <property name="connection.isolation">1</property>
    <property name="connection.pool_size">20</property>
   <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
    <property name="cache.use_second_level_cache">false</property>
    <property name="cache.use_query_cache">false</property>

   
    <!-- Echo all executed SQL to stdout -->
    <property name="show_sql">true</property>

   <mapping resource="model/Foo.hbm.xml"/>
   <mapping resource="model/Bar.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

package app;

import java.util.Iterator;
import java.util.List;

import model.Bar;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Order;

import util.HibernateUtil;

public class Test {
   
   public List getBars() {
      List result;   
      Session session = HibernateUtil.getSessionFactory().getCurrentSession();
      Transaction transaction = session.beginTransaction();
      Criteria criteria = session.createCriteria(Bar.class);
      criteria.addOrder( Order.asc( "maximum" ) );
      result = criteria.list();
      transaction.commit();
      return result;
   }

   
    public static void main(String[] args) {
       Test test = new Test();
       List bars = test.getBars();
       System.out.println("Result:");
       for (Iterator itr=bars.iterator(); itr.hasNext(); ) {
          Bar bar = (Bar)itr.next();
          System.out.println(   "bar_id="+bar.getId()+" "+
                          "min="+bar.getMinimum()+" "+
                          "max="+bar.getMaximum()
                          );
       }
    }   
}

Hibernate: select this_.BAR_ID as BAR1_1_0_,
             ( SELECT min( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id )
           as formula0_0_,
             ( SELECT max( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id )
           as formula1_0_ from bar_table this_ order by
             ( SELECT max( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id )
           asc
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:2148)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
   at org.hibernate.loader.Loader.list(Loader.java:2024)
   at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1533)
   at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
   at app.Test.getBars(Test.java:23)
   at app.Test.main(Test.java:31)
Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0206N  "THIS_.BAR_ID" is not valid in the context where it is used.  SQLSTATE=42703

   at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
   at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
   at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
   at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(Unknown Source)
   at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(Unknown Source)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
   at org.hibernate.loader.Loader.doQuery(Loader.java:662)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
   at org.hibernate.loader.Loader.doList(Loader.java:2145)
   ... 7 more

select this_.BAR_ID as BAR1_1_0_,
             ( SELECT min( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id )
           as formula0_0_,
             ( SELECT max( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id )
           as formula1_0_ from bar_table this_
order by
         ( SELECT max( foo.val ) FROM foo_table foo WHERE foo.bar_id = bar_id )
           asc

select this_.BAR_ID as BAR1_1_0_,
             ( SELECT min( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id )
           as formula0_0_,
             ( SELECT max( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id )
           as formula1_0_ from bar_table this_
order by formula1_0_ asc

package uglyhack;

import org.hibernate.dialect.DB2Dialect;

public class DB2DialectFix extends DB2Dialect {
   
   public String transformSelectString(String select) {
      select = super.transformSelectString(select);
      select = fixOrderBySelect(select);
      return select;
   }
   
   private static String ORDER_BY = "order by";
   private static String THIS_ = "this_.";
   
   private static String fixOrderBySelect(String select) {
      String result = select;
      
      // Does the select sting has an "order by"?
      int indexOfOrderBy = select.indexOf(ORDER_BY);
      if ( indexOfOrderBy!=-1 ) {
         // Get substring between "order by" and "asc"/"desc"
         String orderBy;
         int endIndexOfOrderBy = -1;
         indexOfOrderBy += ORDER_BY.length();
         endIndexOfOrderBy = select.indexOf("asc", indexOfOrderBy);
         if (endIndexOfOrderBy==-1)
            endIndexOfOrderBy = select.indexOf("desc", indexOfOrderBy);
         orderBy = select.substring(indexOfOrderBy, endIndexOfOrderBy);
         
         // Is the order by on a select
         int indexOfSelect = orderBy.indexOf("SELECT");
         int indexOf_this = -1;
         int endIndexOf_this = -1;
         if (indexOfSelect!=-1) {
            // Find the "this_." part
            indexOfSelect += indexOfOrderBy;    // index into original select string
            indexOf_this = orderBy.lastIndexOf(THIS_);
            if (indexOf_this!=-1) {
               // Remove "this_."
               indexOf_this += indexOfOrderBy; // index into original select string
               endIndexOf_this = indexOf_this + THIS_.length();
               
               // Create the new select string with "this_." removed
               result = select.substring(0, indexOf_this) +
                      select.substring(endIndexOf_this);
            }
         }
      }
   
      return result;
   }   
}

select this_.BAR_ID as BAR1_1_0_,
             ( SELECT min( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id )
           as formula0_0_,
             ( SELECT max( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id )
           as formula1_0_ from bar_table this_ order by
             formula1_0_
           desc

package uglyhack;

import org.hibernate.dialect.DB2Dialect;

public class DB2DialectFix extends DB2Dialect {
   
   public String transformSelectString(String select) {
      select = super.transformSelectString(select);
      select = fixOrderBySelect(select);
      return select;
   }
   
   private static String ORDER_BY_ = "order by ";   //do NOT remove the space
   private static String AS_ = "as ";            //do NOT remove the space

   private static String fixOrderBySelect(String select) {
      String result = select;
      
      // Does the select string has an "order by"?
      int indexOfOrderBy = select.indexOf(ORDER_BY_);
      if ( indexOfOrderBy!=-1 ) {
         // Get substring between "order by" and "asc"/"desc"
         String orderBy;
         int endIndexOfOrderBy = -1;
         indexOfOrderBy += ORDER_BY_.length();
         endIndexOfOrderBy = select.indexOf("asc", indexOfOrderBy);
         if (endIndexOfOrderBy==-1)
            endIndexOfOrderBy = select.indexOf("desc", indexOfOrderBy);
         orderBy = select.substring(indexOfOrderBy, endIndexOfOrderBy);
         
         // Is the order by on a select ?
         if (orderBy.indexOf("SELECT")!=-1) {
            // Find the same string before "order by"
            int fromIndex = select.indexOf( orderBy );
            if ( fromIndex!=-1 && fromIndex < indexOfOrderBy ) {
               // find the "as XXXX," part
               fromIndex += orderBy.length();
               int indexOfAs = select.indexOf(AS_, fromIndex);
               int indexOfComma = -1;
               int indexOfSpace = -1;
               int endIndexOfName = -1;
               fromIndex = indexOfAs + AS_.length();
               indexOfComma = select.indexOf(",", fromIndex);
               indexOfSpace = select.indexOf(" ", fromIndex);
               endIndexOfName = indexOfComma;
               if ( endIndexOfName==-1 || (indexOfSpace!=-1 && indexOfSpace < indexOfComma )) {
                  endIndexOfName = indexOfSpace;
               }
               // What was the column selected as (XXXX) ?
               String name = select.substring(fromIndex, endIndexOfName) + " ";
            
               // Transform the select to use the name instead of formula
               result = select.substring(0, indexOfOrderBy) +
                      name +
                      select.substring(endIndexOfOrderBy);
            }
         }
      }
   
      return result;
   }

}

Hibernate: select * from ( select rownumber() over(order by formula1_0_ desc) as rownumber_, this_.BAR_ID as BAR1_1_0_,
             ( SELECT min( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id )
           as formula0_0_,
             ( SELECT max( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id )
           as formula1_0_ from bar_table this_ order by formula1_0_ desc ) as temp_ where rownumber_ between ?+1 and ?
2006-10-12 17:37:15,258 WARN main org.hibernate.util.JDBCExceptionReporter - SQL Error: -206, SQLState: 42S22
2006-10-12 17:37:15,258 ERROR main org.hibernate.util.JDBCExceptionReporter - [IBM][CLI Driver][DB2/NT] SQL0206N  "FORMULA1_0_" is not valid in the context where it is used.  SQLSTATE=42703

select * from
( select
   rownumber() over(order by ( SELECT max( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id ) desc) as rownumber_,
   this_.BAR_ID as BAR1_1_0_,
    ( SELECT min( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id ) as formula0_0_,
    ( SELECT max( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id ) as formula1_0_
  from bar_table this_ order by formula1_0_ desc ) as temp_
where rownumber_ between 1 and 10

select * from
( select
   rownumber() over(order by ( SELECT min( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id ) asc) as rownumber_,
   this_.BAR_ID as BAR1_1_0_,
    ( SELECT min( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id ) as formula0_0_,
    ( SELECT max( foo.val ) FROM foo_table foo WHERE foo.bar_id = this_.bar_id ) as formula1_0_
  from bar_table this_) as temp_
where rownumber_ between 1 and 10

<hibernate-mapping>

    <class name="Foo" mutable="false">

        <subselect><![CDATA[SELECT ID, MAX( ID ) AS MAXID FROM FOO_TABLE GROUP BY ID]]></subselect>

        <synchronize table="FOO_TABLE"/>

        <id name="ID" type="long">
            <column name="id" not-null="true" />
            <generator class="assigned" />
        </id>

        <property name="maxID" type="long">
            <column name="MAXID" not-null="true" />
        </property>

    </class>

</hibernate-mapping>