A consult request for a SQLServer SQL problem from JPA

[Joseph Weinstein]

Hi all.

A JPA bug I am working on finds a weakness in MS's SQLServer,
from a query generated by JPA.
  I have the problem isolated, and have a possible solution path,
but not yet enough insight into the SQL generation of OPENJPA to
quickly know the right approach to fixing it. Below is a JDBC
query line where I duplicate the problem query by hand.

  The simple issue is that the select asks for one of it's
columns returned as a subselect, and then asks that the
results be ordered by that subselect. The DBMS is throwing
a spurious error message, saying that in order to do a
SELECT DISTINCT/ORDER BY, the select list has to contain the
column to be ordered by. It's spurious because the query
clearly does list the identical subselect in the select list
and the order-by, but the DBMS is apparently not smart enough
to equate those.

      s.executeQuery("SELECT DISTINCT "
              + "    t0.id, "
              + "    (SELECT PMH_testPCKeyStringValue.value "
              + "     FROM PMH_testPCKeyStringValue "
              + "     WHERE PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id "
              + "     AND PMH_testPCKeyStringValue.testPCKeyStringValue = 1) "
              + "FROM PMH t0 "
              + "INNER JOIN PMH_testPCKeyStringValue t1 ON t0.id = t1.PERSISTENTMAPHOLDER_ID "
              + "WHERE ("
              + "    (SELECT PMH_testPCKeyStringValue.value "
              + "     FROM PMH_testPCKeyStringValue "
              + "     WHERE PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id "
              + "     AND PMH_testPCKeyStringValue.testPCKeyStringValue = 1) "
              + "     IS NOT NULL) "
              + "ORDER BY "
              + "    (SELECT PMH_testPCKeyStringValue.value "
              + "     FROM PMH_testPCKeyStringValue "
              + "     WHERE PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id "
              + "     AND PMH_testPCKeyStringValue.testPCKeyStringValue = 1) "
              + "DESC");

The actual SQL generated has parameter markers for the testPCKeyStringValue
value, and is executed with a prepared statement.

I am asking my SQLServer contacts to comment on whether this is a
DBMS bug, or a limitation (bug they won't fix) etc, but regardless,
we can't require OpenJPA customers to upgrade to some future SQLServer
version. I ran this against Sybase (a close cousin of SQLServer), and
got a clearer exception, saying that a subselect can't be part of an
order-by.
  I have a modified query that works, which initially simply enough,
involves declaring a column name for the subselect, and then using
that column name in the order-by:

      s.executeQuery("SELECT DISTINCT "
              + "    t0.id, "
              + "    (SELECT PMH_testPCKeyStringValue.value "
              + "     FROM PMH_testPCKeyStringValue "
              + "     WHERE PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id "
              + "     AND PMH_testPCKeyStringValue.testPCKeyStringValue = 1) AS VALUE "
              + "FROM PMH t0 "
              + "INNER JOIN PMH_testPCKeyStringValue t1 ON t0.id = t1.PERSISTENTMAPHOLDER_ID "
              + "WHERE ("
              + "    (SELECT PMH_testPCKeyStringValue.value "
              + "     FROM PMH_testPCKeyStringValue "
              + "     WHERE PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id "
              + "     AND PMH_testPCKeyStringValue.testPCKeyStringValue = 1) "
              + "     IS NOT NULL) "
              + "ORDER BY VALUE "
              + "DESC");

I am looking for a consult with an expert around the jdbc\sql package
code to discuss how this should be best implemented.
thanks
Joe Weinstein at BEA Systems
Notice: This email message, together with any attachments, may contain information of BEA Systems, Inc., its subsidiaries and affiliated entities, that may be confidential, proprietary, copyrighted and/or legally privileged, and is intended solely for the use of the individual or entity named in this message. If you are not the intended recipient, and have received this message in error, please immediately return this by email and then delete it.