Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » [not] exists
[not] exists [message #382789] Mon, 27 October 2008 10:30 Go to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 806
Registered: July 2009
Senior Member
In an effort to create a certain JPQL I referred to the original Toplink JPA manuals. In the "JPQL Language Reference" it says:

----

10.2.5.13. JPQL Exists Expressions
An EXISTS expression is a predicate that is true only if the result of the subquery consists of one or more values and that is false otherwise. The syntax of an exists expression is

exists_expression ::= [NOT] EXISTS (subquery)

The use of the reserved word OF is optional in this expression.

Example:

SELECT DISTINCT auth FROM Author auth
WHERE EXISTS
(SELECT spouseAuthor FROM Author spouseAuthor WHERE spouseAuthor = auth.spouse)
The result of this query consists of all authors whose spouse is also an author.

----


So I took the example JPQL and replace the tables resulting in:

SELECT DISTINCT auth FROM Buyorder auth WHERE EXISTS (SELECT spouseAuthor FROM Delivery2Buyorder spouseAuthor WHERE spouseAuthor.iBuyorder = auth)

However, EclipseLink 1.0.1M2 makes this SQL out of it:

Call: SELECT DISTINCT t0.buyordernr, t0.order_addressnr, t0.delivery_addressnr, t0.pickup_addressnr, t0.contactnr, t0.currencynr, t0.enteredby_empnr, t0.relationnr, t0.delivery_relationnr, t0.stage, t0.buydate, t0.reason, t0.mededeling, t0.delivery_date, t0.weight, t0.lazylock, t0.financialcomplete, t0.freightbill, t0.bill, t0.freightletter, t0.freightmoney, t0.dwhmodified, t0.dwhby, t0.transport_relationnr, t0.packaging, t0.inserted FROM buyorder t0 WHERE EXISTS (SELECT ? FROM buyorder t2, delivery2buyorder t1 WHERE ((t1.buyordernr = t2.buyordernr) AND (t0.buyordernr = t2.buyordernr)))
bind => [1]

Note the bind and the question mark in the SQL. Naturally the database stumbled over this:

Internal Exception: java.sql.SQLException: A syntax error has occurred.
Error Code: -201

Not good. What is wrong?

Tom
Re: [not] exists [message #382791 is a reply to message #382789] Mon, 27 October 2008 10:53 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 806
Registered: July 2009
Senior Member
I was able to create a workaround using

(select count(xx) ...) > 0

and

(select count(xx) ...) = 0
Re: [not] exists [message #382792 is a reply to message #382789] Tue, 28 October 2008 09:14 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

That is odd. What database are you using? Please log a bug for this and
include the full exception stack trace in the bug.

----
James
http://www.nabble.com/EclipseLink---Users-f26658.html


James : Wiki : Book : Blog : Twitter
Re: [not] exists [message #382794 is a reply to message #382792] Tue, 28 October 2008 13:33 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 806
Registered: July 2009
Senior Member
> That is odd. What database are you using? Please log a bug for this
> and include the full exception stack trace in the bug.

Informix 10. Stacktrace is a bit of work ATM, will add that later.
Re: [not] exists [message #382795 is a reply to message #382794] Tue, 28 October 2008 15:08 Go to previous message
Tom Eugelink is currently offline Tom Eugelink
Messages: 806
Registered: July 2009
Senior Member
> Stacktrace is a bit of work ATM, will add that later.

I was thinking, the exception won't help much, since it's the JDBC driver that complains of a syntax error. Eclipselink thinks the query is peachy.
Previous Topic:abort query
Next Topic:Wrong bundle-version for EclipseLink 1.0.2 OSGi bundles
Goto Forum:
  


Current Time: Fri Aug 01 00:03:28 EDT 2014

Powered by FUDForum. Page generated in 0.14503 seconds