Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

UUIDs fail with EclipseLink and Postgres #2190

Open
anija-anil opened this issue Jul 2, 2024 · 5 comments · May be fixed by #2284
Open

UUIDs fail with EclipseLink and Postgres #2190

anija-anil opened this issue Jul 2, 2024 · 5 comments · May be fixed by #2284

Comments

@anija-anil
Copy link
Contributor

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();
@rfelcman
Copy link
Contributor

rfelcman commented Oct 2, 2024

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
Copy link
Member

mswatosh commented Oct 2, 2024

@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
Copy link
Contributor

rfelcman commented Oct 4, 2024

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
Copy link
Member

mswatosh commented Oct 8, 2024

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants