Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

EclipseLink query with UNION causes error

 
Andreas Roerig
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I've got a problem with EclipseLink (Version 2.5 and 2.6), MySQL and a query using UNION.
Here is what I try to do:



This produces the following error messages:

[EL Fine]: sql: 2015-05-06 16:25:33.876--ServerSession(977246141)--Thread(Thread[http-bio-8080-exec-6,5,main])--SELECT 1
[EL Warning]: 2015-05-06 16:25:33.877--UnitOfWork(233203564)--Thread(Thread[http-bio-8080-exec-6,5,main])--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.0.v20150309-bf26070): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION (SELECT t1.BXITEM2ID FROM CONTACTKK t1 WHERE ((t1.BXITEM1ID = 1) AND (t1.K' at line 1
Error Code: 1064
Call: (SELECT t0.BXITEM1ID FROM CONTACTKK t0 WHERE ((t0.BXITEM2ID = ?) AND (t0.KONTAKTDATUM IS NOT NULL)) UNION (SELECT t1.BXITEM2ID FROM CONTACTKK t1 WHERE ((t1.BXITEM1ID = ?) AND (t1.KONTAKTDATUM IS NOT NULL))))
bind => [1, 1]
Query: ReportQuery(referenceClass=ContactKK sql="(SELECT t0.BXITEM1ID FROM CONTACTKK t0 WHERE ((t0.BXITEM2ID = ?) AND (t0.KONTAKTDATUM IS NOT NULL)) UNION (SELECT t1.BXITEM2ID FROM CONTACTKK t1 WHERE ((t1.BXITEM1ID = ?) AND (t1.KONTAKTDATUM IS NOT NULL))))")
Mai 06, 2015 4:25:33 PM org.apache.catalina.core.StandardWrapperValve invoke
Schwerwiegend: Servlet.service() for servlet [Faces Servlet] in context with path [/bx-netzwerk] threw exception [Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.0.v20150309-bf26070): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION (SELECT t1.BXITEM2ID FROM CONTACTKK t1 WHERE ((t1.BXITEM1ID = 1) AND (t1.K' at line 1
Error Code: 1064
Call: (SELECT t0.BXITEM1ID FROM CONTACTKK t0 WHERE ((t0.BXITEM2ID = ?) AND (t0.KONTAKTDATUM IS NOT NULL)) UNION (SELECT t1.BXITEM2ID FROM CONTACTKK t1 WHERE ((t1.BXITEM1ID = ?) AND (t1.KONTAKTDATUM IS NOT NULL))))
bind => [1, 1]
Query: ReportQuery(referenceClass=ContactKK sql="(SELECT t0.BXITEM1ID FROM CONTACTKK t0 WHERE ((t0.BXITEM2ID = ?) AND (t0.KONTAKTDATUM IS NOT NULL)) UNION (SELECT t1.BXITEM2ID FROM CONTACTKK t1 WHERE ((t1.BXITEM1ID = ?) AND (t1.KONTAKTDATUM IS NOT NULL))))")] with root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION (SELECT t1.BXITEM2ID FROM CONTACTKK t1 WHERE ((t1.BXITEM1ID = 1) AND (t1.K' at line 1




It seems EclipseLink packs the whole SQL statement into parentheses and that causes the syntax error on the database side. If I copy the statement, remove the first and the last parentheses then the statement runs fine in SQL-Explorer.
My other queries in the application run fine, but this is the first time I used UNION.

Does anybody know how to solve this?

Best regards
Andreas
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Someone else seems to have a similar issue, unfortunately without a solution. Seems to be an ElipseLink bug.
 
Andreas Roerig
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:Someone else seems to have a similar issue, unfortunately without a solution. Seems to be an ElipseLink bug.


Thanks Roel,

well, then I have to find a way around this problem.

Best regards
Andreas
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Andreas Roerig wrote:well, then I have to find a way around this problem.

What about something like as a starting point:
Or maybe you can use a case expressions

Just some (crazy) thoughts
 
Andreas Roerig
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Roel,

thanks for your suggestions.
In the mean time I solved this the uggly way by splitting the query into two:



Not the nicest approach, but it works.

Best regards
Andreas
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Andreas Roerig wrote:Not the nicest approach, but it works.

True!

Although you could improve your code a little bit:
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic