Invalid ORDER BY clause in SQL (DB2 OS390)

tnleeuw · **Posted:** Fri Oct 22, 2004 6:15 am

Hi,

I have to redeploy an existing application onto DB2 for OS390. The current version is running on DB2 for AIX, where it works.

I changed the dialect to:

Code:

net.sf.hibernate.dialect.DB2390Dialect

To get the right SQL generator.

I get an error from DB2 on an ORDER BY clause. I can reproduce the error on the command-line.

The Hibernate query is:

Code:

select distinct b.policyRef 
from Billing b
where b.booked = no
and b.contractEngineRef = :contractEngineRef
and b.companyRef = :companyRef
and b.policyRef  > :polRefFrom
and b.billingDueDate < :dueDate
order by b.policyRef

The generated SQL query is:

Quote:

select distinct billing0_.POLREF as x0_0_ from BILLING billing0_ where (billing0_.IS_BKD='N' )and(billing0_.CTRENGREF=? )and(billing0_.CMPREF=? )and(billing0_.POLREF>? )and(billing0_.BLGDUEDTE<? ) order by billing0_.POLREF fetch first 1400 rows only

If I fill in some values for the parameters and use the command line processor I get the same error. When I change the ORDER BY to this:

Quote:

order by x0_0_

Then the query works on the command line.

So now my problem is, how to make Hibernate output the query in this way?

I guess that I'll have to change some code in Hibernate, but I rather not put this into production with an unofficially patched Hibernate version (begging for trouble with future releases and updates to Hibernate).

Still, for the immediate term, I'm looking for the best way to patch this in Hibernate sources.

I don't know if changing the SQL generator in Hibernate to output the AS alias for a column instead of the atual column name will cause troubles for other databases, so any such change might have to become dialect-specific... That makes a fix even bigger, and I'll need even more guidance in how to go about it.

Any help is appreciated; this is quite an urgent issue for me and as I said, I have no objections to coding changes myself but I'd like to get some guidance about the most appropriate way, and some feedback if changing the column-reference in ORDER BY for all SQL will cause other databases to throw up.

With regards,

--Tim van der Leeuw

Below the mandatory information, for so far as I considered it relevant:

Hibernate version: 2.1.6

Mapping documents:

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

Code:

    public SortedSet getPolicyRefsFrom(
        String contractEngineRef,
        String companyRef,
        String beginRef,
        Date premiumDueDate,
        int maxRows)
        throws PDGException
    {
        Date queryEndDate = getQueryEndTime(premiumDueDate);
        Query query;
        SortedSet retval;

        Session s = null;
        try
        {
            s = getSession();
            query =
                s.createQuery(
                    "select distinct b.policyRef "
                        + "from Billing b "
                        + "where b.booked = no "
                        + "and b.contractEngineRef = :contractEngineRef "
                        + "and b.companyRef = :companyRef "
                        + "and b.policyRef  > :polRefFrom "
                        + "and b.billingDueDate < :dueDate "
                        + "order by b.policyRef");

            query.setString("companyRef", companyRef);
            query.setString("contractEngineRef", contractEngineRef);
            query.setString("polRefFrom", beginRef);
            query.setDate("dueDate", queryEndDate);
            query.setMaxResults(maxRows * 14);
            query.setFetchSize(maxRows);
            
//            retval = query.list();
//            int queryResultSize = retval.size();
//            retval = new ArrayList(retval);
            
            // Workaround for the lack of 'distinct'-ion; the non-unique
            // polrefs. Now I'm forcing uniqueness and the right size of
            // the batch.
            retval = new TreeSet();
            ScrollableResults results = query.scroll();

^^^^ This is the line on which the exception occurs

Name and version of the database you are using: DB2 OS390 v7

The generated SQL (show_sql=true):

Code:

select distinct billing0_.POLREF as x0_0_ from BILLING billing0_ where (billing0_.IS_BKD='N' )and(billing0_.CTRENGREF=? )and(billing0_.CMPREF=? )and(billing0_.POLREF>? )and(billing0_.BLGDUEDTE<? ) order by  billing0_.POLREF fetch first 1400 rows only

Debug level Hibernate log excerpt:

Full stack trace of any exception that occurs:

(Sorry for the \par things in front of each line, comes from terminal emulator)

Code:

\par 
\par [21/10/04 16:13:22:060 CEST]  f93e91c JDBCException E net.sf.hibernate.util.JDBCExceptionReporter  [IBM][CLI Driver][DB2] SQL0214N  An expression in the ORDER BY clause in the following position, or starting with "POLREF" in the "ORDER BY" clause is not valid.  Reason code = "2".  SQLSTATE=42822
\par 
\par 
\par [21/10/04 16:13:22:064 CEST]  f93e91c JDBCException W net.sf.hibernate.util.JDBCExceptionReporter  SQL Error: -214, SQLState: 42822
\par 
\par [21/10/04 16:13:22:068 CEST]  f93e91c JDBCException E net.sf.hibernate.util.JDBCExceptionReporter  [IBM][CLI Driver][DB2] SQL0214N  An expression in the ORDER BY clause in the following position, or starting with "POLREF" in the "ORDER BY" clause is not valid.  Reason code = "2".  SQLSTATE=42822
\par 
\par 
\par [21/10/04 16:13:22:072 CEST]  f93e91c JDBCException E net.sf.hibernate.util.JDBCExceptionReporter  Could not execute query
\par 
\par [21/10/04 16:13:22:077 CEST]  f93e91c JDBCException E net.sf.hibernate.util.JDBCExceptionReporter  TRAS0014I: The following exception was logged COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0214N  An expression in the ORDER BY clause in the following position, or starting with "POLREF" in the "ORDER BY" clause is not valid.  Reason code = "2".  SQLSTATE=42822
\par 
\par         at COM.ibm.db2.jdbc.DB2Exception.<init>(DB2Exception.java:72)
\par         at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:281)
\par         at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:218)
\par         at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java:448)
\par         at COM.ibm.db2.jdbc.app.DB2PreparedStatement.<init>(DB2PreparedStatement.java:660)
\par         at COM.ibm.db2.jdbc.app.DB2Connection.prepareStatement(DB2Connection.java:2031)
\par         at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:1404)
\par         at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:251)
\par         at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:232)
\par         at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:65)
\par         at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:779)
\par         at net.sf.hibernate.hql.QueryTranslator.scroll(QueryTranslator.java:880)
\par         at net.sf.hibernate.impl.SessionImpl.scroll(SessionImpl.java:1643)
\par         at net.sf.hibernate.impl.QueryImpl.scroll(QueryImpl.java:33)
\par         at com.unisys.pdg.gi.dao.BillingDAO.getPolicyRefsFrom(Unknown Source)
\par         at com.unisys.pdg.gi.batch.GiBatchTaskBean.selectMaxPoliciesFrom(Unknown Source)
\par         at com.unisys.pdg.common.batch.AbstractBatchTaskBean.registerTask(Unknown Source)
\par         at com.unisys.pdg.common.batch.AbstractBatchTaskBean.doReceiveMessage(Unknown Source)
\par         at com.unisys.pdg.gi.batch.EJSLocalStatelessGiBatchTask_4106ec18.doReceiveMessage(Unknown Source)
\par         at com.unisys.pdg.common.batch.AbstractReceiveTaskMessageBean$1.perform(Unknown Source)
\par         at com.unisys.pdg.common.batch.AbstractBatchMessageBean.performWithRetries(Unknown Source)
\par         at com.unisys.pdg.common.batch.AbstractReceiveTaskMessageBean.handleMessage(Unknown Source)
\par         at com.unisys.pdg.common.jms.ReceiveMessage.onMessage(Unknown Source)
\par         at com.ibm.ejs.jms.listener.MDBWrapper$PriviledgedOnMessage.run(MDBWrapper.java:205)
\par         at java.security.AccessController.doPrivileged(Native Method)
\par         at com.ibm.ejs.jms.listener.MDBWrapper.callOnMessage(MDBWrapper.java:194)
\par         at com.ibm.ejs.jms.listener.MDBWrapper.onMessage(MDBWrapper.java:172)
\par         at com.ibm.mq.jms.MQSession.run(MQSession.java:1043)
\par         at com.ibm.ejs.jms.JMSSessionHandle.run(JMSSessionHandle.java:922)
\par         at com.ibm.ejs.jms.listener.ServerSession.connectionConsumerOnMessage(ServerSession.java:697)
\par         at com.ibm.ejs.jms.listener.ServerSession.onMessage(ServerSession.java:482)
\par         at com.ibm.ejs.jms.listener.ServerSession.dispatch(ServerSession.java:449)
\par         at java.lang.reflect.Method.invoke(Native Method)
\par         at com.ibm.ejs.jms.listener.ServerSessionDispatcher.dispatch(ServerSessionDispatcher.java:37)
\par         at com.ibm.ejs.container.MDBWrapper.onMessage(MDBWrapper.java:91)
\par         at com.ibm.ejs.container.MDBWrapper.onMessage(MDBWrapper.java:127)
\par         at com.ibm.ejs.jms.listener.ServerSession.run(ServerSession.java:372)
\par         at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)
\par .
\par                                  COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0214N  An expression in the ORDER BY clause in the following position, or starting with "POLREF" in the "ORDER BY" clause is not valid.  Reason code = "2".  SQLSTATE=42822
\par 
\par         at COM.ibm.db2.jdbc.DB2Exception.<init>(DB2Exception.java:72)
\par         at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:281)
\par         at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:218)
\par         at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java:448)
\par         at COM.ibm.db2.jdbc.app.DB2PreparedStatement.<init>(DB2PreparedStatement.java:660)
\par         at COM.ibm.db2.jdbc.app.DB2Connection.prepareStatement(DB2Connection.java:2031)
\par         at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:1404)
\par         at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:251)
\par         at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:232)
\par         at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:65)
\par         at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:779)
\par         at net.sf.hibernate.hql.QueryTranslator.scroll(QueryTranslator.java:880)
\par         at net.sf.hibernate.impl.SessionImpl.scroll(SessionImpl.java:1643)
\par         at net.sf.hibernate.impl.QueryImpl.scroll(QueryImpl.java:33)
\par         at com.unisys.pdg.gi.dao.BillingDAO.getPolicyRefsFrom(Unknown Source)
\par         at com.unisys.pdg.gi.batch.GiBatchTaskBean.selectMaxPoliciesFrom(Unknown Source)
\par         at com.unisys.pdg.common.batch.AbstractBatchTaskBean.registerTask(Unknown Source)
\par         at com.unisys.pdg.common.batch.AbstractBatchTaskBean.doReceiveMessage(Unknown Source)
\par         at com.unisys.pdg.gi.batch.EJSLocalStatelessGiBatchTask_4106ec18.doReceiveMessage(Unknown Source)
\par         at com.unisys.pdg.common.batch.AbstractReceiveTaskMessageBean$1.perform(Unknown Source)
\par         at com.unisys.pdg.common.batch.AbstractBatchMessageBean.performWithRetries(Unknown Source)
\par         at com.unisys.pdg.common.batch.AbstractReceiveTaskMessageBean.handleMessage(Unknown Source)
\par         at com.unisys.pdg.common.jms.ReceiveMessage.onMessage(Unknown Source)
\par         at com.ibm.ejs.jms.listener.MDBWrapper$PriviledgedOnMessage.run(MDBWrapper.java:205)
\par         at java.security.AccessController.doPrivileged(Native Method)
\par         at com.ibm.ejs.jms.listener.MDBWrapper.callOnMessage(MDBWrapper.java:194)
\par         at com.ibm.ejs.jms.listener.MDBWrapper.onMessage(MDBWrapper.java:172)
\par         at com.ibm.mq.jms.MQSession.run(MQSession.java:1043)
\par         at com.ibm.ejs.jms.JMSSessionHandle.run(JMSSessionHandle.java:922)
\par         at com.ibm.ejs.jms.listener.ServerSession.connectionConsumerOnMessage(ServerSession.java:697)
\par         at com.ibm.ejs.jms.listener.ServerSession.onMessage(ServerSession.java:482)
\par         at com.ibm.ejs.jms.listener.ServerSession.dispatch(ServerSession.java:449)
\par         at java.lang.reflect.Method.invoke(Native Method)
\par         at com.ibm.ejs.jms.listener.ServerSessionDispatcher.dispatch(ServerSessionDispatcher.java:37)
\par         at com.ibm.ejs.container.MDBWrapper.onMessage(MDBWrapper.java:91)
\par         at com.ibm.ejs.container.MDBWrapper.onMessage(MDBWrapper.java:127)
\par         at com.ibm.ejs.jms.listener.ServerSession.run(ServerSession.java:372)
\par         at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)

tnleeuw · **Posted:** Sat Oct 23, 2004 3:32 am

No takers so far? Should I post this as a bug in Jira?

--Tim

gavin · **Posted:** Sat Oct 23, 2004 10:23 am

No, you should report this bug in DB2 to IBM.

Kango · **Joined:** Sun Jun 13, 2004 9:49 pm **Posts:** 38

I believe this may not be a bug with DB2 on OS/390.

If you notice in the query that hibernate generates, it renames billing0_.POLREF to x0_0_.

DB2 for OS/390 is complaining that x0_0_ is not in the select list, which it isn't.

I'm not sure if this is hibernates fault or not. Could this be a dialect issue?

Incedentaly, DB2/400 V5R1 does this too. This is not very helpful as i cannot perform sorts now.

I've just checked the DB2/400 SQL Reference and this is correct behaviour not a bug.

tnleeuw · **Posted:** Wed Oct 27, 2004 12:47 pm

Kango wrote:

I believe this may not be a bug with DB2 on OS/390.

If you notice in the query that hibernate generates, it renames billing0_.POLREF to x0_0_.

DB2 for OS/390 is complaining that x0_0_ is not in the select list, which it isn't.

I'm not sure if this is hibernates fault or not. Could this be a dialect issue?

Incedentaly, DB2/400 V5R1 does this too. This is not very helpful as i cannot perform sorts now.

I've just checked the DB2/400 SQL Reference and this is correct behaviour not a bug.

Hi,

I think it's a dialect-issue yes, but the current interfaces / classes for Dialects don't seem to offer a way to handle this difference. I don't know how many more databases show this problem we have with DB2.

So to solve it there'd need to be more methods on the Dialect class and I don't know what kind of binary compatibility issues that might give.

I also didn't see an easy way yet for me to add this to the Hibernate source, so I just changed the application instead to use a direct JDBC SQL query instead... (which was ok for me here since I was only interested in a list of strings anyways)

regards,

--Tim

robwunderlich · **Joined:** Wed Apr 13, 2005 11:11 pm **Posts:** 1

tnleeuw wrote:

Kango wrote:

I also didn't see an easy way yet for me to add this to the Hibernate source, so I just changed the application instead to use a direct JDBC SQL query instead... (which was ok for me here since I was only interested in a list of strings anyways)
--Tim

A resonable workaround for this problem is to use "ORDER BY 1".

stigj · **Joined:** Tue Jun 28, 2005 9:21 am **Posts:** 2

You could also leave the distinct out of the HQL query and make the result distinct in Java code by putting the result into a HashSet. Just make sure your equals/hashCode implementations will make the same result as the distinct in the database.

Regards,
Stig Kleppe-Jorgensen

eagle79 · **Posted:** Tue Jun 28, 2005 11:21 am

Just a thought, but is the execution order of a SQL statement defined by the SQL92 standard? If so, I believe the ORDER BY clause is usually executed last (or at least before the SELECT clause is executed. Therefore, would the hibernate team consider the feasibility of changing how the ORDER BY clause is translated so that it would reference the alias instead of the actual column?

I say feasibility because, of course, it should be verified that making such a change doesn't break other databases.