I have an SQL query which includes a subquery in the FROM clause. The query works in POSTGRES SQL editor and returns one row with 3 columns as expected. The same query, in Hibernate context returns a row as well but I can not access the fields. (See my server log & exception) Would you give me a direction on how to capture the data in a row in this situation? Notice that I am using a join query; my first table is joining with the results of a sub query. It seems like it is not possible to capture columns into a Hibernate bean, nor parse them as an object array (Object[]) in this paticular situation. I am hoping I am wrong, please prove me wrong.

Hibernate version:3.0.5


private List getAlarmDataSample(Session hsession, CWirelessSensorModule wsm, CMeasurement m, short size) {
String sql = "select sd.value, sub_le.gw_timestamp, sd.id from " +
"(select gw_timestamp, id from \"SSI\".listener_event le where le.event_code = " + Integer.valueOf(IRequestCodes.UPDATE_SENSOR_DATA).toString() +
" and le.wsm_id = " + wsm.getId().toString() +
" order by le.gw_timestamp desc limit " + Short.valueOf(size).toString() +
") as sub_le, \"SSI\".sensor_data sd where sd.measurement_id = " + m.getId().toString() +
" and sd.listener_event_id = sub_le.id order by sub_le.gw_timestamp desc";

List results = (hsession.createSQLQuery(sql).list());
LOG.info("results size="+results.size());
List sdList = new Vector();
int i=0;
Iterator iter = results.iterator();
while (iter.hasNext())
{
Object[] row = (Object[]) iter.next();
LOG.info("row size="+row.length);
//exception array out of boundries happens below!!
Float val = (Float) row[0];
Timestamp ts = (Timestamp) row[1];
Long id = (Long) row[2];
LOG.info("val="+val+" ts="+ts+" id="+id);
sdList.set(i,new SensorData(val,ts,id)); i++;
}
return sdList;
}

10:14:15,484 INFO [STDOUT] Hibernate: select sd.value, sub_le.gw_timestamp, sd.id from (select gw_timestamp, id from "SSI".listener_event le where le.event_code = 10 and le.wsm_id = 2 order by le.gw_timestamp desc limit 1) as sub_le, "SSI".sensor_data sd where sd.measurement_id = 12 and sd.listener_event_id = sub_le.id order by sub_le.gw_timestamp desc
10:14:15,656 INFO [CListenerManagerBean] results size=1
10:14:15,656 INFO [CListenerManagerBean] row size=0
10:14:15,656 ERROR [CPersistenceUtil] Exception in ejb method LogSensorData
java.lang.ArrayIndexOutOfBoundsException: 0
at com.ssi.listener.ejb.CListenerManagerBean.getAlarmDataSample(CListenerManagerBean.java:617)
at com.ssi.listener.ejb.CListenerManagerBean.testAlarmCondition(CListenerManagerBean.java:530)
at com.ssi.listener.ejb.CListenerManagerBean.checkAlarm(CListenerManagerBean.java:485)
at com.ssi.listener.ejb.CListenerManagerBean.LogSensorData(CListenerManagerBean.java:469)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.invocation.Invocation.performCall(Invocation.java:345)
at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:214)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:185)
at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:130)
at org.jboss.webservice.server.ServiceEndpointInterceptor.invoke(ServiceEndpointInterceptor.java:51)
at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:48)
at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:105)
at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:335)
at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:166)
at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:139)
at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)
at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:624)
at org.jboss.ejb.Container.invoke(Container.java:873)
at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:413)
at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:88)
at $Proxy52.LogSensorData(Unknown Source)
at com.ssi.listener.handler.CLogSensorData.process2(CLogSensorData.java:55)
at com.ssi.listener.handler.AHandler.process(AHandler.java:37)
at com.ssi.listener.servlet.CGetRequestType.doGet(CGetRequestType.java:75)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:39)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:153)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:59)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
at java.lang.Thread.run(Thread.java:595)

POSTGRES8.1.1

select sd.value, sub_le.gw_timestamp, sd.id from (select gw_timestamp, id from "SSI".listener_event le where le.event_code = 10 and le.wsm_id = 2 order by le.gw_timestamp desc limit 1) as sub_le, "SSI".sensor_data sd where sd.measurement_id = 12 and sd.listener_event_id = sub_le.id order by sub_le.gw_timestamp desc