0 Replies Latest reply on Nov 25, 2002 1:51 PM by Partha Dutta

    Bug in JBossQL w/ ORDER BY??

    Partha Dutta Newbie

      I am running JBoss 3.0.3. I have the following finder query in the jbosscmp-jdbc.xml file for a bean named Groups:

      <jboss-ql>SELECT OBJECT(g) FROM Groups g ORDER BY g.groupName</jboss-ql>

      I am using Informix as my database server. This is a snippet of the server.log file:
      2002-11-25 12:40:41,756 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCJBossQLQuery.GroupsEJB.findAllOrdered] Executing SQL: SELECT t0_g.group_number FROM GROUPS t0_g ORDER BY t0_g.name ASC
      2002-11-25 12:40:41,763 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.GroupsEJB] Executing SQL: SELECT group_number,name, description FROM GROUPS WHERE (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?)

      The first SQL statement DOES NOT work in Informix!! It will produce an error (SQL error code -309). Anything in the order by clause must be specified in the select list.

      The question I have is why is the container even trying to perform an ORDER BY while fetching the primary keys? I would have though that the ORDER BY would be performed on the subsequent query.

      I would appreciate it if anyone could het back to me or has come across this before!

      Partha Dutta
      Bloomberg L.P