DynamicQL ORDER BY does nothing

[kv_moj]

Hi,
Unfortunately I can't remember at which point this problem arose as I would probably have a better idea how to address the issue, but I recently discovered that the ORDER BY clause in my dql statements are no longer sorting the results. It could have been when I upgraded to version 3.0.4.
The application deploys with no errors and the query executes with no errors, but the results come back sorted by primary key not the column specified in the ORDER BY clause.
Here's a snippet of the log file:

Jboss version 3.0.4 / 3.0.6

2003-04-24 09:20:32,903 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.FWProductsEJB.ejbSelectDQL] DYNAMIC-QL: SELECT OBJECT(o) FROM products o WHERE ( o.price > ?1 ) ORDER BY o.name ASC
2003-04-24 09:20:32,903 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.FWProductsEJB.ejbSelectDQL] SQL: SELECT DISTINCT t0_o.id, t0_o.creationDate, t0_o.productId, t0_o.name, t0_o.description, t0_o.price, t0_o.unitCode, t0_o.packCode, t0_o.packSize, t0_o.packOnly, t0_o.weight, t0_o.tag, t0_o.mark, t0_o.tagDatestamp, t0_o.markDatestamp, t0_o.supplierId, t0_o.taxCatId, t0_o.formatId, t0_o.catalogId, t0_o.inventoryId FROM Products t0_o WHERE (t0_o.price > ?) ORDER BY t0_o.name ASC
2003-04-24 09:20:32,903 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.FWProductsEJB.ejbSelectDQL] Executing SQL: SELECT DISTINCT t0_o.id, t0_o.creationDate, t0_o.productId, t0_o.name, t0_o.description, t0_o.price, t0_o.unitCode, t0_o.packCode, t0_o.packSize, t0_o.packOnly, t0_o.weight, t0_o.tag, t0_o.mark, t0_o.tagDatestamp, t0_o.markDatestamp, t0_o.supplierId, t0_o.taxCatId, t0_o.formatId, t0_o.catalogId, t0_o.inventoryId FROM Products t0_o WHERE (t0_o.price > ?) ORDER BY t0_o.name ASC

If I run the SQL statement that jboss generates on mySQL command line, I get the results sorted as expected, which suggests this is not a restriction in the underlying database but something within the jboss layer.

Any ideas why this might be happening.

Many thanks,
kv.

[jonlee]

Should tie up with Kryptontri's thread http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t= Since you are both reporting a CMP problem with mySQL as your underlying DB, thought perhaps you could share your experiences. Since the SQL is being generated correctly by JBoss I can't imagine why the query would not get passed through directly to the underlying DB's pooled JDBC connection.

So I'm wondering if there is a problem with the JDBC driver? There are a couple of things you could try such as writing a Stateless session bean that connects to a mySQL JBoss pool and executes your query - and print the results. I would try this myself out of curiosity except I'm not a mySQL user - running 3 other DBs is enough of a headache for me. ;)

This test would show that things don't get mangled somewhere in JBoss as just about the same DB connection infrastructure is used in both cases. Also you should test a straight JDBC execution of your query in a normal Java client. Please see my comments in the other thread on some underlying DB problems I have seen with the distinct keyword - but I don't think this affects you.

Hope that gives you a place to start. Good luck.

[kv_moj]

Thanks jonlee
I've been watching the other thread - I'll join it now with some results from your suggestions.
kv.