JPQL EXTRACT not working on PostgreSQL and Oracle using EclipseLink

[KyleAure]

EclipseLink is generated a SQL query from JPQL to PostgreSQL that is rejected by the database.

For example, the JPQL SELECT this.publicDebt / this.numFullTimeWorkers FROM DemographicInfo WHERE EXTRACT (YEAR FROM this.collectedOn) = ?1

The PostgreSQL JDBC driver throws the exception:
org.postgresql.util.PSQLException: ERROR: function pg_catalog.extract(unknown, character varying) does not exist

Full exception stack:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 5.0.0.v202408071314-43356e84b79e71022b1656a5462b0a72d70787a4): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
Error Code: 0
Call: SELECT (PUBLICDEBT / NUMFULLTIMEWORKERS) FROM DEMOGRAPHICINFO WHERE (EXTRACT(YEAR FROM COLLECTEDON) = ?)
bind => [2024]
Query: ReportQuery(referenceClass=DemographicInfo sql="SELECT (PUBLICDEBT / NUMFULLTIMEWORKERS) FROM DEMOGRAPHICINFO WHERE (EXTRACT(YEAR FROM COLLECTEDON) = ?)")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:346)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1806)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:694)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:569)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2053)
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:611)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:282)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:268)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:354)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:794)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2836)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2772)
at org.eclipse.persistence.queries.ReportQuery.executeDatabaseQuery(ReportQuery.java:936)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:934)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1256)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:485)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1344)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3015)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1848)
at org.eclipse.persistence.internal.sessions.AbstractSession.retryQuery(AbstractSession.java:1917)
at org.eclipse.persistence.sessions.server.ClientSession.retryQuery(ClientSession.java:715)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.retryQuery(UnitOfWorkImpl.java:5820)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1883)
at org.eclipse.persistence.internal.sessions.AbstractSession.retryQuery(AbstractSession.java:1917)
at org.eclipse.persistence.sessions.server.ClientSession.retryQuery(ClientSession.java:715)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.retryQuery(UnitOfWorkImpl.java:5820)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1883)
at org.eclipse.persistence.internal.sessions.AbstractSession.retryQuery(AbstractSession.java:1917)
at org.eclipse.persistence.sessions.server.ClientSession.retryQuery(ClientSession.java:715)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.retryQuery(UnitOfWorkImpl.java:5820)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1883)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1830)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1795)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:263)
Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)
at jdk.internal.reflect.GeneratedMethodAccessor19.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at org.postgresql.ds.PGPooledConnection$StatementHandler.invoke(PGPooledConnection.java:447)
at jdk.proxy6/jdk.proxy6.$Proxy22.executeQuery(Unknown Source)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:477)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1026)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:662)
Caused by: org.postgresql.util.PSQLException: ERROR: function pg_catalog.extract(unknown, character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 70
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)
at jdk.internal.reflect.GeneratedMethodAccessor19.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at org.postgresql.ds.PGPooledConnection$StatementHandler.invoke(PGPooledConnection.java:447)
at jdk.proxy6/jdk.proxy6.$Proxy22.executeQuery(Unknown Source)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:477)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1026)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:662)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:569)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2053)
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:611)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:282)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:268)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:354)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:794)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2836)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2772)
at org.eclipse.persistence.queries.ReportQuery.executeDatabaseQuery(ReportQuery.java:936)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:934)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1256)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:485)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1344)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3015)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1848)

at componenttest.topology.utils.FATServletClient.assertTestResponse(FATServletClient.java:106)
at componenttest.topology.utils.FATServletClient.runTest(FATServletClient.java:91)
at componenttest.custom.junit.runner.SyntheticServletTest.invokeExplosively(SyntheticServletTest.java:49)
at componenttest.custom.junit.runner.FATRunner$1.evaluate(FATRunner.java:204)
at componenttest.custom.junit.runner.FATRunner$2.evaluate(FATRunner.java:365)
at componenttest.custom.junit.runner.FATRunner.run(FATRunner.java:178)
at org.testcontainers.containers.FailureDetectingExternalResource$1.evaluate(FailureDetectingExternalResource.java:29)
at componenttest.rules.repeater.RepeatTests$CompositeRepeatTestActionStatement.evaluate(RepeatTests.java:145) 

[ajaypaul-ibm]

Raised Eclipselink issue eclipse-ee4j/eclipselink#2242