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.
I've been watching the other thread - I'll join it now with some results from your suggestions.