LocalDate/LocalDateTime Conversion failure with Oracle

[jgrassel]

Encountered the following conversion failure while testing with Oracle DB (passed on Derby):

jakarta.persistence.PersistenceException: Exception [EclipseLink-3002] (Eclipse Persistence Services - 4.0.0.v202206101604): org.eclipse.persistence.exceptions.ConversionException Exception Description: The object [07-JUN-22], of class [class java.lang.String], from mapping [org.eclipse.persistence.mappings.DirectToFieldMapping[localDateData-->QUERYDATETIMEENTITY.LOCALDATEDATA]] with descriptor [RelationalDescriptor(io.openliberty.jpa.tests.jpa31.models.QueryDateTimeEntity --> [DatabaseTable(QUERYDATETIMEENTITY)])], could not be converted to [class java.time.LocalDate]. Internal Exception: java.time.format.DateTimeParseException: Text '07-JUN-22' could not be parsed, unparsed text found at index 0 at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:494) at io.openliberty.jpa.tests.jpa31.web.TestNewQueryTimeFunctionsServlet.testLocalDateFunction_JPQL(TestNewQueryTimeFunctionsServlet.java:107) at j

when executing the following code:

            TypedQuery<QueryDateTimeEntity> q = em.createQuery("SELECT qdte FROM QueryDateTimeEntity qdte WHERE qdte.localDateData < LOCAL DATE AND qdte.id <  100",
                                                               QueryDateTimeEntity.class);
            List<QueryDateTimeEntity> resList = q.getResultList();

Given entity:

@Entity
public class QueryDateTimeEntity {
    @Id
    private int id;

    private java.time.LocalDate localDateData;
    private java.time.LocalTime localTimeData;
    private java.time.LocalDateTime localDateTimeData;

A similar failure occurs while using Oracle with LocalDateTime:

jakarta.persistence.PersistenceException: Exception [EclipseLink-3002] (Eclipse Persistence Services - 4.0.0.v202206101604): org.eclipse.persistence.exceptions.ConversionException Exception Description: The object [07-JUN-22], of class [class java.lang.String], from mapping [org.eclipse.persistence.mappings.DirectToFieldMapping[localDateData-->QUERYDATETIMEENTITY.LOCALDATEDATA]] with descriptor [RelationalDescriptor(io.openliberty.jpa.tests.jpa31.models.QueryDateTimeEntity --> [DatabaseTable(QUERYDATETIMEENTITY)])], could not be converted to [class java.time.LocalDate]. Internal Exception: java.time.format.DateTimeParseException: Text '07-JUN-22' could not be parsed, unparsed text found at index 0 at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:494) at io.openliberty.jpa.tests.jpa31.web.TestNewQueryTimeFunctionsServlet.testLocalDateTimeFunction_JPQL(TestNewQueryTimeFunctionsServlet.java:270) 

Given code:

            Query q = em.createQuery("SELECT qdte FROM QueryDateTimeEntity qdte WHERE qdte.localDateTimeData < LOCAL DATETIME AND qdte.id <  100");
            List resList = q.getResultList();

[Tomas-Kraus]

I run JPA jse tests with Oracle 21c and some of the tests were failing. There are almost identical jUnits in org.eclipse.persistence.jpa.test.jpql.TestDateTimeFunctions.

@Entity
public class DateTimeEntity {
    @Id
    private Integer id;
    private LocalTime timeValue;
    private LocalDate dateValue;
    private LocalDateTime datetimeValue;
    ...

And JPQL doing the same:

    // Test JPQL with localTime in WHERE condition.
    @Test
    public void testJpqlQueryWhereLocalTime() {
        final EntityManager em = emf.createEntityManager();
        try {
            TypedQuery<Integer> query = em.createQuery(
                    "SELECT e.id FROM DateTimeEntity e WHERE e.timeValue < LOCAL TIME AND e.id = :id",
                    Integer.class);
            query.setParameter("id", 4);
            em.getTransaction().begin();
            query.getSingleResult();
            em.getTransaction().commit();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    // Test JPQL with localDate in WHERE condition.
    @Test
    public void testJpqlQueryWhereLocalDate() {
        final EntityManager em = emf.createEntityManager();
        try {
            TypedQuery<Integer> query = em.createQuery(
                    "SELECT e.id FROM DateTimeEntity e WHERE e.dateValue < LOCAL DATE AND e.id = :id",
                    Integer.class);
            query.setParameter("id", 4);
            em.getTransaction().begin();
            query.getSingleResult();
            em.getTransaction().commit();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    // Test JPQL with localDateTime in WHERE condition.
    @Test
    public void testJpqlQueryWhereLocalDateTime() {
        final EntityManager em = emf.createEntityManager();
        try {
            TypedQuery<Integer> query = em.createQuery(
                    "SELECT e.id FROM DateTimeEntity e WHERE e.datetimeValue < LOCAL DATETIME AND e.id = :id",
                    Integer.class);
            query.setParameter("id", 4);
            em.getTransaction().begin();
            query.getSingleResult();
            em.getTransaction().commit();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

[Tomas-Kraus]

But I'm getting slightly different exceptions:

[EL Warning]: 2022-06-16 12:30:22.142--UnitOfWork(1531782631)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.0.v202206161021): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00904: "CURRENT_TIME": invalid identifier

Error Code: 904
Call: SELECT CURRENT_TIME FROM DATETIMEENTITY WHERE (ID = ?)
	bind => [1 parameter bound]
Query: ReportQuery(referenceClass=DateTimeEntity sql="SELECT CURRENT_TIME FROM DATETIMEENTITY WHERE (ID = ?)")
[EL Warning]: 2022-06-16 12:30:22.143--Can't update DB offset: Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.0.v202206161021): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00904: "CURRENT_TIME": invalid identifier

[Tomas-Kraus]

Added exact copy of statements from this issue to the org.eclipse.persistence.jpa.test.jpql.TestDateTimeFunctions test.

[jgrassel]

Updated to today's master (ee55e109) and still seeing these failures on Oracle.

Using Oracle database: Version: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 Driver: Oracle JDBC driver Version: 21.1.0.0.0

[jgrassel]

any updates?

[lukasj]

Tomas will be back from vacation next week…

[Tomas-Kraus]

@jgrassel org.eclipse.persistence.jpa.test.jpql.TestDateTimeFunctions are passing with Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 in my environment. This test suite is checking the same scenario so there may be something wrong with your setup. Tested it with 18.4.0-xe too to make sure XE version works too.

[Tomas-Kraus]

Here is DB setup we are using for running the tests (JPA and AQ):

 CREATE USER scott IDENTIFIED BY tiger
     DEFAULT TABLESPACE users
     TEMPORARY TABLESPACE temp
     QUOTA UNLIMITED ON users;

     GRANT CONNECT , RESOURCE, QUERY REWRITE TO scott;
     GRANT CREATE VIEW, CREATE ANY CONTEXT TO scott;
     GRANT EXECUTE ON dbms_rls TO scott;
     GRANT aq_administrator_role TO scott;
     GRANT EXECUTE ON dbms_flashback TO scott;
     GRANT EXECUTE ON dbms_aq TO scott;
     GRANT EXECUTE ON dbms_aqadm TO scott;
     GRANT EXECUTE ON dbms_aqin TO scott;

This matches etc/el-test.oracle.properties with one exception - URL must be modified to match PDB, in my case: XE version:

log ...
ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE
Completed: ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE

db.url=jdbc:oracle:thin:@localhost:1521/XEPDB1

EE version: docker run --rm --name oracle -p 1521:1521 -p 5500:5500 -e ORACLE_SID=ORCL -e ORACLE_PDB=EL -e ORACLE_PWD=oracle11 oradb18c:18.3.0-ee db.url=jdbc:oracle:thin:@localhost:1521/EL

[jgrassel]

Yeah, like I commented on some of the other now-closed-as-of-today issues, the problem was because I was my DDL had mapped the java.time typed entity variables to VARCHAR2 columns in the Oracle Database. So naturally, there were problems. When I fixed the DDL to use the proper DATE/TIMESTAMP types, the problem went away. Closing as user error.