openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Joseph Weinstein <...@bea.com>
Subject A consult request for a SQLServer SQL problem from JPA
Date Tue, 11 Mar 2008 18:49:06 GMT
<html>
<body>
Hi all.<br><br>
A JPA bug I am working on finds a weakness in MS's SQLServer,<br>
from a query generated by JPA.<br>
&nbsp; I have the problem isolated, and have a possible solution
path,<br>
but not yet enough insight into the SQL generation of OPENJPA to<br>
quickly know the right approach to fixing it. Below is a JDBC<br>
query line where I duplicate the problem query by hand.<br><br>
&nbsp; The simple issue is that the select asks for one of it's<br>
columns returned as a subselect, and then asks that the<br>
results be ordered by that subselect. The DBMS is throwing<br>
a spurious error message, saying that in order to do a<br>
SELECT DISTINCT/ORDER BY, the select list has to contain the<br>
column to be ordered by. It's spurious because the query<br>
clearly does list the identical subselect in the select list<br>
and the order-by, but the DBMS is apparently not smart enough<br>
to equate those.<br><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; s.executeQuery(&quot;SELECT DISTINCT
&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp; t0.id, &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp; <b>(SELECT PMH_testPCKeyStringValue.value</b>
&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; <b>FROM PMH_testPCKeyStringValue</b>
&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; <b>WHERE
PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id</b> &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; <b>AND
PMH_testPCKeyStringValue.testPCKeyStringValue = 1)</b> &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;FROM PMH t0 &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;INNER JOIN PMH_testPCKeyStringValue t1 ON t0.id =
t1.PERSISTENTMAPHOLDER_ID &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;WHERE (&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp; (SELECT PMH_testPCKeyStringValue.value
&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; FROM PMH_testPCKeyStringValue
&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; WHERE
PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; AND
PMH_testPCKeyStringValue.testPCKeyStringValue = 1) &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; IS NOT NULL) &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;ORDER BY &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp; <b>(SELECT PMH_testPCKeyStringValue.value</b>
&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; <b>FROM PMH_testPCKeyStringValue</b>
&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; <b>WHERE
PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id </b>&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; <b>AND
PMH_testPCKeyStringValue.testPCKeyStringValue = 1)</b> &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;DESC&quot;);<br><br>
The actual SQL generated has parameter markers for the
<b>testPCKeyStringValue<br>
</b>value, and is executed with a prepared statement.<br><br>
I am asking my SQLServer contacts to comment on whether this is a<br>
DBMS bug, or a limitation (bug they won't fix) etc, but regardless,<br>
we can't require OpenJPA customers to upgrade to some future
SQLServer<br>
version. I ran this against Sybase (a close cousin of SQLServer),
and<br>
got a clearer exception, saying that a subselect can't be part of 
an<br>
order-by.<br>
&nbsp; I have a modified query that works, which initially simply
enough,<br>
involves declaring a column name for the subselect, and then using<br>
that column name in the order-by:<br><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; s.executeQuery(&quot;SELECT DISTINCT
&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp; t0.id, &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp; <b>(SELECT PMH_testPCKeyStringValue.value</b>
&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; <b>FROM PMH_testPCKeyStringValue</b>
&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; <b>WHERE
PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id</b> &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; <b>AND
PMH_testPCKeyStringValue.testPCKeyStringValue = 1)<font color="#FF0000">
AS VALUE</b> </font>&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;FROM PMH t0 &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;INNER JOIN PMH_testPCKeyStringValue t1 ON t0.id =
t1.PERSISTENTMAPHOLDER_ID &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;WHERE (&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp; (SELECT PMH_testPCKeyStringValue.value
&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; FROM PMH_testPCKeyStringValue
&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; WHERE
PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; AND
PMH_testPCKeyStringValue.testPCKeyStringValue = 1) &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;&nbsp;&nbsp;&nbsp;&nbsp; IS NOT NULL) &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;ORDER BY <font color="#FF0000"><b>VALUE</b></font> &quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+ &quot;DESC&quot;);<br><br>
I am looking for a consult with an expert around the jdbc\sql
package<br>
code to discuss how this should be best implemented.<br>
thanks<br>
Joe Weinstein at BEA Systems</body>
</html>

<br>
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.

Mime
View raw message