Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Issue with EclipseLink and union when using hsqldb(EclipseLink blowing up when using the union operator)
Issue with EclipseLink and union when using hsqldb [message #1696509] Tue, 26 May 2015 21:53
Silas Christiansen is currently offline Silas ChristiansenFriend
Messages: 3
Registered: July 2009
Junior Member
It looks like union is supported here

But when I try the following two things it errors out.

Query selectQuery = getEntityManager().createQuery("select c.id from Company c union select c.id from Company c");
selectQuery.setMaxResults(10);
selectQuery.setFirstResult(0);
List<Object[]> resultList = selectQuery.getResultList();


Gives me:
Internal Exception: java.sql.SQLSyntaxErrorException: unexpected token: AS
Error Code: -5581
Call: SELECT LIMIT ? ?T t0.ID AS a1 FROM COMPANY t0 UNION (SELECT t1.ID FROM COMPANY t1))
	bind => [0, 10]
Query: ReportQuery(referenceClass=Company sql="SELECT LIMIT ? ?T t0.ID AS a1 FROM COMPANY t0 UNION (SELECT t1.ID FROM COMPANY t1))")
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1991)
	at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
	at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:250)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2675)
	at org.eclipse.persistence.queries.ReportQuery.executeDatabaseQuery(ReportQuery.java:848)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127)
	at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1793)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1775)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1740)
	at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
	... 70 more
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: AS
	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
	at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1556)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1505)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:778)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:619)
	... 90 more
Caused by: org.hsqldb.HsqlException: unexpected token: AS
	at org.hsqldb.error.Error.parseError(Unknown Source)
	at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
	at org.hsqldb.ParserDQL.XreadSelect(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
	at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
	at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
	at org.hsqldb.ParserCommand.compilePart(Unknown Source)
	at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
	at org.hsqldb.Session.compileStatement(Unknown Source)
	at org.hsqldb.StatementManager.compile(Unknown Source)
	at org.hsqldb.Session.execute(Unknown Source)
	... 96 more


Query selectQuery = getEntityManager().createQuery("select c.id from Company c union select c.id from Company c ");
selectQuery.setMaxResults(10);
selectQuery.setFirstResult(0);
List<Object[]> resultList = selectQuery.getResultList();


Gives me:

Internal Exception: java.sql.SQLSyntaxErrorException: incompatible data type in operation: ; in LIMIT, OFFSET or FETCH
Error Code: -5563
Call: SELECT LIMIT 0 10T t0.ID AS a1, 'test1' FROM COMPANY t0 UNION (SELECT t1.ID, 'test2' FROM COMPANY t1))
Query: ReportQuery(referenceClass=Company sql="SELECT LIMIT ? ?T t0.ID AS a1, ? FROM COMPANY t0 UNION (SELECT t1.ID, ? FROM COMPANY t1))")
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1991)
	at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
	at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:250)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2675)
	at org.eclipse.persistence.queries.ReportQuery.executeDatabaseQuery(ReportQuery.java:848)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127)
	at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1793)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1775)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1740)
	at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
	... 70 more
Caused by: java.sql.SQLSyntaxErrorException: incompatible data type in operation: ; in LIMIT, OFFSET or FETCH
	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
	at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1005)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:642)
	... 90 more
Caused by: org.hsqldb.HsqlException: incompatible data type in operation: ; in LIMIT, OFFSET or FETCH
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.ParserDQL.XreadTopOrLimit(Unknown Source)
	at org.hsqldb.ParserDQL.XreadSelect(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
	at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
	at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
	at org.hsqldb.ParserCommand.compilePart(Unknown Source)
	at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
	at org.hsqldb.Session.executeDirectStatement(Unknown Source)
	at org.hsqldb.Session.execute(Unknown Source)


Am I doing something wrong or is there a bug with ExlipseLink?

Report message to a moderator

Previous Topic:Dilemma: Inheritance vs Cachin
Next Topic:moxy fails to render json, jaxb error thrown
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Dec 08 06:14:08 GMT 2024

Powered by FUDForum. Page generated in 0.03387 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly