JPQL EXTRACT not working on PostgreSQL and Oracle using EclipseLink #2242
Comments
|
I think the root of this issue has to do with how EclipseLink saves date/time data to PostgreSQL. The instant So I can see two solutions:
NOTE: in this case ZonedDateTime stores zone offset and zone id data, PostgreSQL can store one or the other but not both.
Dates and Times
NOTE: In this case ZonedDateTime is stored as |
|
@lukasj I would have expected EclipseLink to support basic JPQL with just the core library, and for the PostgreSQL extension to support data types specific to PostgreSQL. Similar to how the Oracle Extension supports things like Instant, LocalDate, LocalTime, OffsetTime, and OffsetData should be supported data types for time without the need for the PostgreSQL extension, IMHO. |
|
@KyleAure Have I said anything about extract being or not being supported without the PostgreSQL extension?
Have you tried the same use-case on DB different from PostgreSQL?
Can all these be represented by the same datatypes in all supported DBs or are there differences? Anyway, I do see two possible things to check here:
...both items are DB specific, so the DB specific support class seems like the best starting point for digging in. WDYT? |
No sorry, what I meant by that statement was that it was not supported without an extension, but the exception thrown by EclipseLink does not make that clear.
Yeah, this works on Derby, DB2, and MS SQLServer without any additional extensions. I ran the same test on MS SQLServer and found that the table is created with The SQL Query that is run is:
No, but now that I am learning more about EclipseLink I do not think they should when not using the PostgreSQL extension.
Yes, because the database needs to cast the VARCHAR into a DATE/TIME object for time based functions to work. Therefore, the EXTRACT function needs to be customized for PostgreSQL (similar to what SQLServer does above). But I would argue that this functionality should not be sequestered to the extension but rather be included in the base org.eclipse.persistence.platform.database.PostgreSQLPlatform class. Thanks for sticking with me while I learn more about EclipseLink and JPA :D |
ajaypaul-ibm
changed the title
|
This issue is seen in Oracle DB , hence updating the title. |
|
|
@rfelcman [eclipselink.query] Execute query DataModifyQuery(sql="CREATE TABLE DEMOGRAPHICINFO (ID NUMBER(38) NOT NULL, COLLECTEDON VARCHAR2(255) NULL, INTRAGOVERNMENTALDEBT NUMBER(38) NULL, NUMFULLTIMEWORKERS NUMBER(38) NULL, PUBLICDEBT NUMBER(38) NULL, PRIMARY KEY (ID))")Then when attempting to extract the year from the date column providing the following JPQL query: SELECT this.publicDebt / this.numFullTimeWorkers FROM DemographicInfo WHERE EXTRACT (YEAR FROM this.collectedOn) = ?1Eclipselink generates an incorrect SQL query: SELECT (PUBLICDEBT / NUMFULLTIMEWORKERS) FROM DEMOGRAPHICINFO WHERE (EXTRACT(YEAR FROM COLLECTEDON) = ?)Resulting in the following exception: Exception [EclipseLink-4002] (Eclipse Persistence Services - 5.0.0-B03.v202409121024-4a7149f0cd04d7466837d70f68abb743c88acb83): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-30076: invalid extract field for extract source
https://docs.oracle.com/error-help/db/ora-30076/
Error Code: 30076
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) = ?)")
[10/2/24, 8:42:05:508 CDT] 00000050 id=00000000 eclipselink 3 throwable
Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 5.0.0-B03.v202409121024-4a7149f0cd04d7466837d70f68abb743c88acb83): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-30076: invalid extract field for extract source
https://docs.oracle.com/error-help/db/ora-30076/
Error Code: 30076
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.basicExecuteCall(DatabaseAccessor.java:702)
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)The correct query should have been: SELECT (PUBLICDEBT / NUMFULLTIMEWORKERS)
FROM DEMOGRAPHICINFO
WHERE (EXTRACT(YEAR FROM(TO_DATE( COLLECTEDON , 'yyyy-mm-dd"T"hh24:mi:ss"Z"' ))) = ?) |
|
I re-tested DDL generation against Oracle 23C and PostgreSQL with following entity: and for the Oracle 23C DDL script is for the PostgreSQL DDL script is so I don't see any issue there as expected |
|
@rfelcman
Where you using the Oracle (org.eclipse.persistence.oracle) and PostgreSQL (org.eclipse.persistence.pgsql) extensions?
|
|
It's about persistence unit in persistence.xml property or wasn't needed as auto-detection works |
|
@rfelcman <property name="jakarta.persistence.schema-generation.scripts.create-target" value="target/create_ddlentity.sql"/>
<property name="jakarta.persistence.schema-generation.scripts.drop-target" value="target/drop_ddlentity.sql"/>You are not replicating this issue. Here is the persistence unit I am trying to use: <persistence ...>
<persistence-unit name="RecreatePersistenceUnit">
...
<properties>
<!-- EclipseLink should create the database schema automatically -->
<property name="jakarta.persistence.schema-generation.database.action" value="drop-and-create" />
<property name="eclipselink.logging.parameters" value="true"/>
</properties>
</persistence-unit>
</persistence>Eclipse link can correctly identify the target database for both Oracle and PostgreSQL. But what EclipseLink fails to do is to generate a valid SQL query given the type of column it is using to store timestamp data. |
|
Sorry I don't understand Your comment about: These properties are for the script output and generated by EclipseLink. Not as the input to generate DB schema. says drop/generate DB schema and additionally scripts specified above. https://jakarta.ee/specifications/persistence/3.2/jakarta-persistence-spec-3.2#a12384 |
|
Thanks for the information. I was just providing information on how you might replicate this issue. I am not sure why the tables being created using DDL vs the table that is created in my environment are different. |
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:
The text was updated successfully, but these errors were encountered: