Native query projection fails with PostgreSQL UUID [DATAJPA-1480]

[spring-projects-issues]

Mickaël Tricot opened DATAJPA-1480 and commented

When a JPA entity contains a UUID field and is stored in PostgreSQL, native queries failed to project that field:

org.springframework.orm.jpa.JpaSystemException: No Dialect mapping for JDBC type: 1111; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

I have created a reproduction project with tests that:

• succeeds without projection
• succeeds with the projection of a string field
• fails with projection of a UUID field

 

---

Affects: 2.1.3 (Lovelace SR3)

Reference URL: https://github.com/mickaeltr/spring-data-jpa-bug-with-native-query-uuid-projection

[spring-projects-issues]

Jens Schauder commented

Thanks for the reproducing repository.

This seems to be a Hibernate limitation (bug?).

If you add this to your test:

	@Autowired
	private EntityManager entityManager;

	@Test
	void selectJpaNoTargetClass() {

		Person p = new Person();
		p.setName("Daisy");
		UUID id = personRepository.save(p).getId();

		Object reloadedId = entityManager //
				.createQuery("select id from Person") //
				.getSingleResult();

		Assertions.assertEquals(id, reloadedId);
	}

	@Test
	void selectNativeJpaNoTargetClass() {

		Person p = new Person();
		p.setName("Daisy");
		UUID id = personRepository.save(p).getId();

		Object reloadedId = entityManager //
				.createNativeQuery("select id from Person") //
				.getSingleResult();

		Assertions.assertEquals(id, reloadedId);
	}

The native variant fails with the exception you got as well.
See https://github.com/schauder/DATAJPA-1480-bug-with-native-query-uuid-projection

I'll try to get some input from a Hibernate person

[spring-projects-issues]

Mickaël Tricot commented

Thanks Jens Schauder for the heads up!

[spring-projects-issues]

Jens Schauder commented

This is actually a known issue for Hibernate, but they decided not to fix it: https://hibernate.atlassian.net/browse/HHH-12926

Vlad Mihalcea suggests using addScalar but that is not JPA but Hibernate specific, so I don't want to do that in Spring Data JPA.

But I found a workaround.
If you change your select to the following it works:

SELECT cast(p.id as varchar) as id FROM person p WHERE p.id = :id

Of course, that causes a double conversion but that should be irrelevant in 98% of all use cases.

I pushed an update to my fork of your example to demonstrate that workaround

Please confirm if this works for you

[spring-projects-issues]

Mickaël Tricot commented

Thanks Jens Schauder for taking care of that so quickly, I confirm that it works with interface-based projections.

By the way, I didn't manage to use class-based projections with native queries. If you have any hints on that, I would be glad to read them

[spring-projects-issues]

Jens Schauder commented

We won't fix this since the fix would be Hibernate specific and also there is a workaround

[spring-projects-issues]

Jens Schauder commented

Regarding class-based projections: Nothing on top of my head.
But if you create a new issue I can take a closer look

[spring-projects-issues]

Jens Schauder commented

Batch closing resolved issue without a fix version and a resolution indicating that there is nothing to release (Won't fix, Invalid ...)