I use dynamic EJB-QL clauses in JBoss 4.0.3 and have folowing queries:
select distinct object(o) from orders as o, in(o.actions) as a order by o.state desc
select distinct object(o) from orders as o, in(o.actions) as a order by a.insertTime desc, o.state desc
In sectond query a get rowset that have duplicate results. When I see to logs:
2006-05-03 20:17:20,578 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.Order#ejbSelectFilteredOrders] Executing SQL:
SELECT DISTINCT t0_o.OrderId, t0_o.State FROM Orders t0_o, Actions t3_a ORDER BY t0_o.State DESC
...
2006-05-03 20:17:20,578 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.Order#ejbSelectFilteredOrders] Executing SQL:
SELECT DISTINCT t0_o.OrderId, t3_a.InsertTime, t0_o.State FROM Orders t0_o, Actions t3_a ORDER BY t3_a.InsertTime DESC, t0_o.State DESC
Field t3_a.InsertTime became in select clause and causes duplicate rows in result set!
What should I do to avoid its strange behaviour?
Thanks for any help.
So anybody knows any answer on my question?
Why fields added to ORDER BY clause appears in SELECT section and mar up result set?