LocalDate conversion failure with DB2

[jgrassel]

Observed the following error while running my test bucket with DB2:

jakarta.persistence.PersistenceException: Exception [EclipseLink-3002] (Eclipse Persistence Services - 4.0.0.v202207051234): org.eclipse.persistence.exceptions.ConversionException Exception Description: The object [2022-06-07-12.00.00.000000000000], of class [class java.lang.String], from mapping [org.eclipse.persistence.mappings.DirectToFieldMapping[localDateTimeData-->QUERYDATETIMEENTITY.LOCALDATETIMEDATA]] with descriptor [RelationalDescriptor(io.openliberty.jpa.tests.jpa31.models.QueryDateTimeEntity --> [DatabaseTable(QUERYDATETIMEENTITY)])], could not be converted to [class java.time.LocalDateTime]. Internal Exception: java.time.format.DateTimeParseException: Text '2022-06-07-12.00.00.000000000000' could not be parsed, unparsed text found at index 13 at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:494) 
at io.openliberty.jpa.tests.jpa31.web.TestNewQueryTimeFunctionsServlet.testLocalDateFunction_Criteria(TestNewQueryTimeFunctionsServlet.java:343)

Involved Code:

            // SELECT qdte FROM QueryDateTimeEntity qdte WHERE qdte.localDateData < LOCAL DATE AND qdte.id <  100"
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery cq = cb.createQuery(QueryDateTimeEntity.class);
            Root<QueryDateTimeEntity> root = cq.from(QueryDateTimeEntity.class);
            cq.select(root);
            cq.where(cb.and(cb.lessThan(root.get("localDateData"), cb.localDate()), cb.lessThan(root.get("id"), 100)));
            TypedQuery<QueryDateTimeEntity> q = em.createQuery(cq);

            List<QueryDateTimeEntity> resList = q.getResultList();

With Entity:

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

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

[jgrassel]

Okay, so my test was using VARCHAR types instead of DATE/TIME/TIMESTAMP in the DDL defining the tables. Once I corrected the DDL, the function started working as expected.

That said, there does seem to be a bug with org.eclipse.persistence.internal.helper.ConversionManager.convertObjectToLocalDateTime(Object), the DataTimeFormatter it gets from org.eclipse.persistence.internal.helper.Helper.getDefaultDateTimeFormatter() includes DateTimeFormatter.ISO_LOCAL_TIME, which expects hours/minutes/seconds separated by : but as seen in the DB2Platform code, DB2 's native format 'YYYY-MM-DD-hh.mm.ss.SSSSSS' isn't compatible with that converter.

[jgrassel]

Is this an issue that should be addressed? Absolutely, though that is a general issue of persisting a java.time type with a VARCHAR column on DB2, and not something unique to the new function in JPA 3.1.

[lukasj]

The fix, if available/needed, can be included in 4.0.0-RC2 if submitted