UUIDs fail with EclipseLink and Postgres

[anija-anil]

When creating a query with a UUID, the parameter is set as a VARCHAR instead of a UUID, and is rejected by Postgres.

Here is an example of the failing query:

UUID uuid = p1.id;
List<?> result = em.createQuery("SELECT p FROM PurchaseOrder p WHERE p.id=?1")
                   .setParameter(1, uuid).getResultList();

[mswatosh]

There is some discussion on how to convert to a UUID in postgres here:
https://stackoverflow.com/questions/12771737/conversion-string-to-uuid-in-postgres-and-java/34652560#34652560

There is a recreate here: https://github.com/mswatosh/persistence-recreates/blob/1a2658d4e90ce56807bf7ecbc65455d1b8600f17/src/main/java/com/example/application/PersistenceService.java#L119

[rfelcman]

Sorry, but I don see any bug there against PostgreSQL see attached testcase.
UUIDPostgreSQL.tar.gz
See and change pom.xml for EclipseLink and PostgreSQL JDBC driver version.
Required DB table must be created by init.sql .
Two tests are located in:

• com.oracle.jpa.bugtest.TestBug#persistUUIDUUIDTest ... insert data (one row)
• com.oracle.jpa.bugtest.TestBug#queryWithParameterUUIDUUIDEntityTest ... query/select data by JPQL with UUID parameter.

[mswatosh]

@rfelcman It looks like it only occurs if the id is Generated, in your test case you're specifying it. I modified your test case to generate the Id and it fails like in my recreate.

I added table creation, and changed the user/pass to what I use with the postgres docker container, so that will need to be updated in the persistence.xml and @BeforeAll method. I also switched to eclipseLink 4.0.4 since I don't have eclipselink locally.
UUIDGenerated.zip

[rfelcman]

I see some logical errors related with id field in the modified example.

• generated fields are available after commit

em.getTransaction().commit();
id = entity.getId();

• to pass id across tests methods static is needed

private static UUID id;

Without this id with null value is passed to query.setParameter(1, id);
If id is not null test passing against public 4.0.4 or 5.0.0-B03 and my local build 5.0.0-SNAPSHOT
See next version of TestBug.java .
TestBug.tar.gz

[mswatosh]

I think I've narrowed down the issue. When I use this in my persistence.xml:

            <property name="jakarta.persistence.schema-generation.database.action"
            value="drop-and-create"/>

It's creating the table as:

postgres=# \d TEST_TAB_UUID_UUID
                Table "public.test_tab_uuid_uuid"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 id     | character varying(255) |           | not null | 
 name   | character varying(255) |           |          | 

Where your working example was creating the table directly as this:

postgres=# \d TEST_TAB_UUID_UUID
                Table "public.test_tab_uuid_uuid"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 id     | uuid                   |           | not null | 
 name   | character varying(200) |           |          | 

So it seems like the schema generation is not creating the table correctly?