Community
Participate
Working Groups
CASE in Select and Where clause not supported on Derby version 10.5.3.0 This is one of the limitations of Derby. This is not a regression. all these tests are added for JPA 2.0 features. These JPA tests failed with Derby. org.eclipse.persistence.testing.tests.jpa.jpql.JUnitJPQLComplexTestSuite.complexSimpleCaseInSelectTest org.eclipse.persistence.testing.tests.jpa.jpql.JUnitJPQLComplexTestSuite.complexSimpleCaseInWhereTest org.eclipse.persistence.testing.tests.jpa.criteria.JUnitCriteriaSimpleTestSuite.simpleCaseInWhereTest org.eclipse.persistence.testing.tests.jpa.criteria.JUnitCriteriaSimpleTestSuite.simpleCaseInSelectTest Stack Trace: ============ Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "t0" at line 1, column 13. Error Code: 30000 Call: SELECT CASE t0.F_NAME WHEN 'Bob' THEN 'Robert' WHEN 'Jill' THEN 'Gillian' ELSE '' END FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (((t0.F_NAME = 'Bob') OR (t0.F_NAME = 'Jill')) AND (t1.EMP_ID = t0.EMP_ID)) Query: ReportQuery(referenceClass=Employee sql="SELECT CASE t0.F_NAME WHEN ? THEN ? WHEN ? THEN ? ELSE ? END FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (((t0.F_NAME = ?) OR (t0.F_NAME = ?)) AND (t1.EMP_ID = t0.EMP_ID))") Local Exception Stack: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.0.qualifier): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "t0" at line 1, column 13. Error Code: 30000 Call: SELECT CASE t0.F_NAME WHEN 'Bob' THEN 'Robert' WHEN 'Jill' THEN 'Gillian' ELSE '' END FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (((t0.F_NAME = 'Bob') OR (t0.F_NAME = 'Jill')) AND (t1.EMP_ID = t0.EMP_ID)) Query: ReportQuery(referenceClass=Employee sql="SELECT CASE t0.F_NAME WHEN ? THEN ? WHEN ? THEN ? ELSE ? END FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (((t0.F_NAME = ?) OR (t0.F_NAME = ?)) AND (t1.EMP_ID = t0.EMP_ID))") at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:686) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:529) at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:529) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:191) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:262) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:618) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2537) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2480) at org.eclipse.persistence.queries.ReportQuery.executeDatabaseQuery(ReportQuery.java:838) at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:675) at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:958) at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:432) at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1021) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2863) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1181) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:453) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getResultList(EJBQueryImpl.java:669) at org.eclipse.persistence.testing.tests.jpa.jpql.JUnitJPQLComplexTestSuite.complexSimpleCaseInSelectTest(JUnitJPQLComplexTestSuite.java:2367) at org.eclipse.persistence.testing.framework.junit.JUnitTestCase.runBare(JUnitTestCase.java:428) at org.eclipse.persistence.testing.framework.TestExecutor.execute(TestExecutor.java:248) at org.eclipse.persistence.testing.framework.TestModel.execute(TestModel.java:208) at org.eclipse.persistence.testing.framework.TestCollection.run(TestCollection.java:313) Caused by: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "t0" at line 1, column 13. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:889) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:601) Caused by: java.sql.SQLException: Syntax error: Encountered "t0" at line 1, column 13. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) Caused by: ERROR 42X01: Syntax error: Encountered "t0" at line 1, column 13. at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
EclipseLink does not support CASE syntax on Derby. It is not because Derby does not support CASE.
derby only supports certain types of CASE. (I believe complex-case is not supported) Also, I am not sure if case is supported in the select clause. Can you post the exception headers for each failing test?
org.eclipse.persistence.testing.tests.jpa.jpql.JUnitJPQLComplexTestSuite.complexSimpleCaseInWhereTest =================== Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "t1" at line 1, column 439. Error Code: 30000 Call: SELECT t1.EMP_ID, t2.EMP_ID, t1.PAY_SCALE, t1.L_NAME, t1.STATUS, t1.VERSION, t1.ROOM_NUM, t1.GENDER, t2.SALARY, t1.F_NAME, t1.START_OVERTIME, t1.END_OVERTIME, t1.START_TIME, t1.END_TIME, t1.ADDR_ID, t1.DEPT_ID, t1.MANAGER_EMP_ID, t1.FORMER_COMPANY, t1.FORMER_START_DATE, t1.FORMER_END_DATE, t1.START_DATE, t1.END_DATE, t0.ID, t0.NAME FROM CMP3_EMPLOYEE t1 LEFT OUTER JOIN CMP3_DEPT t0 ON (t0.ID = t1.DEPT_ID), CMP3_SALARY t2 WHERE ((CASE t1.F_NAME WHEN 'Bob' THEN 'Robert' WHEN 'Jill' THEN 'Gillian' ELSE '' END = 'Robert') AND (t2.EMP_ID = t1.EMP_ID)) Query: ReadAllQuery(referenceClass=Employee sql="SELECT t1.EMP_ID, t2.EMP_ID, t1.PAY_SCALE, t1.L_NAME, t1.STATUS, t1.VERSION, t1.ROOM_NUM, t1.GENDER, t2.SALARY, t1.F_NAME, t1.START_OVERTIME, t1.END_OVERTIME, t1.START_TIME, t1.END_TIME, t1.ADDR_ID, t1.DEPT_ID, t1.MANAGER_EMP_ID, t1.FORMER_COMPANY, t1.FORMER_START_DATE, t1.FORMER_END_DATE, t1.START_DATE, t1.END_DATE, t0.ID, t0.NAME FROM CMP3_EMPLOYEE t1 LEFT OUTER JOIN CMP3_DEPT t0 ON (t0.ID = t1.DEPT_ID), CMP3_SALARY t2 WHERE ((CASE t1.F_NAME WHEN ? THEN ? WHEN ? THEN ? ELSE ? END = ?) AND (t2.EMP_ID = t1.EMP_ID))") org.eclipse.persistence.testing.tests.jpa.criteria.JUnitCriteriaSimpleTestSuite.simpleCaseInWhereTest =================== Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "t1" at line 1, column 439. Error Code: 30000 Call: SELECT t1.EMP_ID, t2.EMP_ID, t1.PAY_SCALE, t1.L_NAME, t1.STATUS, t1.VERSION, t1.ROOM_NUM, t1.GENDER, t2.SALARY, t1.F_NAME, t1.START_OVERTIME, t1.END_OVERTIME, t1.START_TIME, t1.END_TIME, t1.ADDR_ID, t1.DEPT_ID, t1.MANAGER_EMP_ID, t1.FORMER_COMPANY, t1.FORMER_START_DATE, t1.FORMER_END_DATE, t1.START_DATE, t1.END_DATE, t0.ID, t0.NAME FROM CMP3_EMPLOYEE t1 LEFT OUTER JOIN CMP3_DEPT t0 ON (t0.ID = t1.DEPT_ID), CMP3_SALARY t2 WHERE ((CASE t1.F_NAME WHEN 'Bob' THEN 'Robert' WHEN 'Rob' THEN 'Robbie' ELSE 'Not Bob' END = 'Robert') AND (t2.EMP_ID = t1.EMP_ID)) Query: ReadAllQuery(referenceClass=Employee sql="SELECT t1.EMP_ID, t2.EMP_ID, t1.PAY_SCALE, t1.L_NAME, t1.STATUS, t1.VERSION, t1.ROOM_NUM, t1.GENDER, t2.SALARY, t1.F_NAME, t1.START_OVERTIME, t1.END_OVERTIME, t1.START_TIME, t1.END_TIME, t1.ADDR_ID, t1.DEPT_ID, t1.MANAGER_EMP_ID, t1.FORMER_COMPANY, t1.FORMER_START_DATE, t1.FORMER_END_DATE, t1.START_DATE, t1.END_DATE, t0.ID, t0.NAME FROM CMP3_EMPLOYEE t1 LEFT OUTER JOIN CMP3_DEPT t0 ON (t0.ID = t1.DEPT_ID), CMP3_SALARY t2 WHERE ((CASE t1.F_NAME WHEN ? THEN ? WHEN ? THEN ? ELSE ? END = ?) AND (t2.EMP_ID = t1.EMP_ID))") org.eclipse.persistence.testing.tests.jpa.criteria.JUnitCriteriaSimpleTestSuite.simpleCaseInSelectTest ====================== Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "t0" at line 1, column 13. Error Code: 30000 Call: SELECT CASE t0.F_NAME WHEN 'Bob' THEN 'Robert' WHEN 'Rob' THEN 'Robbie' ELSE 'Not Bob' END FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'Bob') AND (t1.EMP_ID = t0.EMP_ID)) Query: ReportQuery(referenceClass=Employee sql="SELECT CASE t0.F_NAME WHEN ? THEN ? WHEN ? THEN ? ELSE ? END FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = ?) AND (t1.EMP_ID = t0.EMP_ID))")
Simple case is not supported on Derby. These tests should not run on Derby. Setting to test bug.
Created attachment 155759 [details] patch file
Created attachment 155762 [details] patch file When these tests are run with Derby, "Warning: Derby does not support simple CASE" will be thrown
The patch is checked in both 2.1.0 and 2.0.1 Code reviewed by: Christopher Delahunt
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
