Jooq: Querying with a UUID in WHERE clause has issue with postgresql

[kag0]

When creating a condition like so USERS.CUSTOMER_ID.equal(customerId) where customerId is a java UUID and USERS.CUSTOMER_ID is a postgres uuid the following error is encountered.

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = bytea
[error]   Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
[error]   Position: 783
[error]     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)
[error]     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)
[error]     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200)
[error]     at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
[error]     at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161)
[error]     at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
[error]     at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
[error]     at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
[error]     at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)

I'm using JPA from hibernate to execute a native query with code similar to this page from the documentation.

In JPA models UUID columns need to be annotated with @Type(type="org.hibernate.type.PostgresUUIDType"). What is the right way to handle this in JOOQ?

[lukaseder]

Thank you very much for reporting this. Indeed, I suspect you're using getBindValues() to return the types that you need to bind to the JDBC / JPA statement?

This is related to #4886. The current implementation of getBindValues() returns the values that are contained in the individual Param objects (e.g. your UUID), not the types that are bound to the JDBC statement. In the case of UUIDs, it's a bit more tricky, because the PostgreSQL JDBC driver natively supports java.util.UUID - there's no Converter involved here. I suspect you'll need to make JPA / Hibernate understand the type. I'm not sure if this can be done via Query.setParameter(), though. Perhaps, this is a good question for Stack Overflow?

[kag0]

Yes, I'm using getBindValues().

I've gotten this working in the interim as a hack USERS.CUSTOMER_ID.cast(String.class).equal(customerId.toString()).

Existing SO questions seem to indicate that there won't be any luck trying to use UUID with createNativeQuery and parameters. However using UUID in a JPQL query with createQuery seems to be ok. So this is a bit of a stupid ask, but would you make a JPQL dialect for JOOQ?

[dmitrygusev]

UUID works without any problems for us in jOOQ.

Not sure if this is somehow related, but the queries are executed on connection unwrapped from JPA/Hibernate, i.e.:

entityManager.unwrap(Session.class).doWork(connection -> {
    DSLContext create = DSL.using(connection);

    // ...

});

And there's a custom PostgreSQL dialect used in Hibernate configuration with custom type contribution:

public class CustomPostgreSQL9Dialect extends PostgreSQL9Dialect
{
    private final PostgresUUIDType postgresUUIDType = new PostgresUUIDType()
    {
        private static final long serialVersionUID = 1L;

        @Override
        public String getName()
        {
            return UUID.class.getName();
        }
    };

    @Override
    public void contributeTypes(TypeContributions typeContributions, ServiceRegistry serviceRegistry)
    {
        super.contributeTypes(typeContributions, serviceRegistry);

        typeContributions.contributeType(postgresUUIDType);
    }

[lukaseder]

[ @kag0 ] So this is a bit of a stupid ask, but would you make a JPQL dialect for JOOQ?

No :) That's far out of scope for jOOQ. JPQL is a very different language from SQL and it wouldn't be a good idea to support it in the same API.

[ @dmitrygusev ] UUID works without any problems for us in jOOQ.

Thank you very much for that hint! That sounds very useful, indeed. So does your user type also work with native Hibernate queries?

In fact, that's so useful as an approach, I think it should be published somewhere for other people to find it :) Do you blog? Or perhaps, would you be interested in posting a question and answer on Stack Overflow?

[dmitrygusev]

It seemed that the snippet I provided was not really relevant.

Now I won't remember why that custom UUID type was needed, but obviously not for this use case.

I've created a simple project to check this: https://github.com/dmitrygusev/postgresql-uuid-example

Custom dialect only needed for the JPA native query, the trick is to bind built-in "pg-uuid" type to SQL Types.OTHER:

import org.hibernate.dialect.PostgreSQL9Dialect;

import java.sql.Types;

public class CustomPostgreSQL9Dialect extends PostgreSQL9Dialect
{
    public CustomPostgreSQL9Dialect()
    {
        registerHibernateType(Types.OTHER, "pg-uuid");
    }
}

This might be not perfect, because you might want to bind more than one user type to OTHER, but I couldn't find any other way.

The jOOQ & JPA over QueryDSL queries worked just fine without custom dialect, few sample queries can be found here:
https://github.com/dmitrygusev/postgresql-uuid-example/blob/master/src/test/java/com/example/jooq/UuidTest.java

P.S.
I haven't blogged for a while, let's hope this thread could serve as a reference for others.

[dmitrygusev]

@kag0 for JPQL equivalent of jOOQ you can look at http://www.querydsl.com

For simple queries both libraries even have similar Java API, i.e.: selectFrom(...).where(...).fetchOne();

[mkurz]

We may want to use jOOQ later this year (with Postgres). We usually make use of UUID's a lot so hopefully this won't be a show stopper for us...

[lukaseder]

@mkurz. UUIDs work perfectly in jOOQ with PostgreSQL. This issue is quite specific to the way the original poster used jOOQ with JPA. There are workarounds for this particular problem, so I don't think this will be a show stopper for you - unless you have a concrete issue, perhaps?

[mkurz]

@lukaseder Perfect, thanks! No concrete issues, just scrolling through the issues here in the repo to figure out if there may be bugs we might be affected by or missing features we might need. Anyway, thank you.

[thyming]

For what it's worth, I've had trouble with this short of casting (as was suggested by @kag0) without any usage of JPA. The error is slightly different ERROR: operator does not exist: uuid = varchar but presents the same issue.

[lukaseder]

OK, thanks for the update, @thyming - that wasn't clear to me. I'll review this once more.

[thyming]

If it'd be helpful, I can make an example project that demonstrates the bug.

[kag0]

@mkurz fwiw @lukaseder is right, UUIDs work perfectly in pure JOOQ without JPA in my experience. We ditched JPA a while back and the world became a brighter place. The sun came out, birds started singing, UUIDs worked, and we didn't have to guess at what kind of bat-$h!t insane queries were being generated.

[mkurz]

@kag0 Thanks for the information :+1: :smile:

[thyming]

Must be something with how I've set it up, working on a repro based on what I've observed.

[thyming]

Okay, so I tracked down the issue. UUIDs in postgres will not work as expected when using Spring Boot with jOOQ unless you set the following property:

spring.jooq.sql-dialect=Postgres

Sorry for the noise on this thread.

[mkurz]

@thyming thanks for taking the time to investigate. Good to know there is no issue with jOOQ itself.

@lukaseder I think you can close this?

[lukaseder]

hmm, nope I still cannot reproduce this. Our integration tests include:

    @Test
    public void testPostgresUUIDinTables() throws Exception {
        clean(T_EXOTIC_TYPES);

        UUID uuid = UUID.randomUUID();

        assertEquals(1,
            create().insertInto(T_EXOTIC_TYPES)
                    .columns(T_EXOTIC_TYPES.ID, T_EXOTIC_TYPES.UU)
                    .values(1, null)
                    .execute()
        );

        assertEquals(1,
            create().insertInto(T_EXOTIC_TYPES)
                    .columns(T_EXOTIC_TYPES.ID, T_EXOTIC_TYPES.UU)
                    .values(2, uuid)
                    .execute()
        );

        Record2<Integer, UUID> r =
        create().select(T_EXOTIC_TYPES.ID, T_EXOTIC_TYPES.UU)
                .from(T_EXOTIC_TYPES)
                .where(T_EXOTIC_TYPES.UU.eq(uuid))
                .fetchOne();

        assertEquals(2, (int) r.value1());
        assertEquals(uuid, r.value2());
    }

Where...

CREATE TABLE t_exotic_types (
  ID                     INT  NOT NULL,
  UU                     UUID,
  ...
);

@thyming - how can this be reproduced?

[lukaseder]

Egh, my bad. My browser didn't update with your new comments. Thanks for your various confirmations!