JPA 3.1 CriteriaBuilder Query exception

[hantsy]

My sample project used the following software.

• Windows 10 64bit
• Java 17
• Glassfish 7.0M4 with built-in DB

The complete codes to reproduce the error: https://github.com/hantsy/jakartaee10-sandbox/blob/master/jpa/src/test/java/com/example/it/JPQLCriteriaBuilderTest.java#L155

@Entity
public class Person {
    @Id
    @Column(name = "id", nullable = false)
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;

Query:

var cb = em.getCriteriaBuilder();
var query = cb.createTupleQuery();
var root = query.from(Person.class);

query.multiselect(root.get("name"),
        cb.localTime(),
        cb.localDateTime(),
        cb.localDate()
);
query.where(cb.equal(root.get("id"), id));

var resultList = em.createQuery(query).getResultList();

The query execution caused the following exceptions.

Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.0-M3.v202203111216): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "cf3" at line 1, column 118.
Error Code: 20000
Call: SELECT NAME, CAST(CURRENT_TIME AS TIMESTAMP), CURRENT_TIMESTAMP, CURRENT_DATE FROM PERSON WHERE (id = d04a98a2-4408-4cf3-b232-5cfaab58afc9)
Query: TupleQuery(referenceClass=Person sql="SELECT NAME, CAST(CURRENT_TIME AS TIMESTAMP), CURRENT_TIMESTAMP, CURRENT_DATE FROM PERSON WHERE (id = d04a98a2-4408-4cf3-b232-5cfaab58afc9)")
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:343)
	

The complete server log file is here.
server.log

[lukasj]

provide full stacktrace, please

[hantsy]

@lukasj Attached the complete server.log at the end of the original post.

[Tomas-Kraus]

I added following test to org.eclipse.persistence.jpa.test.uuid.TestUUIDUUID class:

    @Test
    public void testIssue1554() {
        final String uuidName = "Issue testIssue1554 UUID";
        EntityManager em = uuidEmf.createEntityManager();
        try {
            em.getTransaction().begin();
            UUIDUUIDEntity entity = new UUIDUUIDEntity();
            entity.setName(uuidName);
            entity.setId(UUID.fromString("d04a98a2-4408-4cf3-b232-5cfaab58afc9"));
            em.persist(entity);
            em.flush();
            em.getTransaction().commit();
            UUID id = entity.getId();
            em.getTransaction().begin();
            var cb = em.getCriteriaBuilder();
            var query = cb.createTupleQuery();
            var root = query.from(UUIDUUIDEntity.class);
            query.multiselect(root.get("name"),
                    cb.localTime(),
                    cb.localDateTime(),
                    cb.localDate()
            );
            query.where(cb.equal(root.get("id"), id));
            List<?> result = em.createQuery(query).getResultList();
            em.getTransaction().commit();
            assertEquals(result.size(), 1);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
        finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

and it passed:

$ mvn verify -pl :org.eclipse.persistence.jpa.jse.test -Dit.test=TestUUIDUUID#testIssue1554
[INFO] Scanning for projects...
...
[EL Fine]: sql: 2022-06-28 12:29:27.388--ServerSession(1944201789)--Connection(746074699)--SELECT NAME, CAST(CURRENT_TIME AS TIMESTAMP), CURRENT_TIMESTAMP, CURRENT_DATE FROM UUID_UUID WHERE (ID = ?)
	bind => [1 parameter bound]
[INFO] Results:
[INFO] 
[INFO] Tests run: 1, Failures: 0, Errors: 0, Skipped: 0

But looks like PreparedStatement was not used in your case.

[Tomas-Kraus]

Looks like this was caused by 2 things:

1. PU property eclipselink.jdbc.bind-parameters set to false
2. Missing UUID value handling in DatabasePlatform#appendParameterInternal(Call,Writer,Object)

[dazey3]

@Tomas-Kraus

How about testing this?

    UUID id = entity.getId();
    var cb = em.getCriteriaBuilder();
...
    var param = cb.parameter(UUID.class);
    query.where(cb.equal(root.get("id"), param));
    List<?> result = em.createQuery(query).setParameter(param, id).getResultList();

Is it required that for UUID values, they MUST be bound as parameters and cannot be printed as literals in the query string? Is that platform specific? Is it not valid to print UUID values as string values (ie. WHERE (id = 'd04a98a2-4408-4cf3-b232-5cfaab58afc9')? Some databases convert String to UUID type. Perhaps other databases have special functions for handling UUID values? Perhaps we should remove all '-' char first?