Exception UNION could not convert type for inheritance hierarchies with JsonStringType

[jwgmeligmeyling]

Given an class hierarchy with entities B and C that both extend base class A, when C has a property mapped with JsonStringType, and one queries all A using SELECT a FROM A a, the query fails with the following exception:

org.hibernate.exception.SQLGrammarException: could not extract ResultSet (3a7690e6-4840-4004-89d2-479af6a72963): javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.postgresql.util.PSQLException: ERROR: UNION could not convert type json to uuid
  Position: 579

The error is caused by the fact that the produced SQL query has the following format: (I slightly simplified it to only include the relevant part)

SELECT b.*, null::uuid as properties FROM B b UNION ( select c.*, c.properties from C c)

The ::uuid cast originates from PostgreSQL81Dialect#getSelectClauseNullString(sqlType), which returns uuid for input 1111 (which is Types.OTHER). So it seems somehow, OTHER is already bound to uuid, and the work around performed by Hibernate does not work anymore (a better workaround would probably be to consider the columnDefinition of the column as well).

I'm uncertain whether this workaround is still required, but all new dialects (I am using the PostgreSQL95Dialect) still extend from the PostgreSQL81Dialect without overriding getSelectClauseNullString method. Furthermore, I am aware that this issue should probably be fixed in Hibernate rather than here. This method will in fact break for all queries using a null value of a type for which its JDBC type is mapped to multiple types.

Nevertheless, I've had success with mapping my JSON properties to Types.JAVA_OBJECT rather than Types.OTHER. This type is not subject to the bug for me. Types.JAVA_OBJECT is also the JDBC type of choice as of the PostgreSQL92Dialect.

Is there any particular reason to not switch the JDBC type of JsonStringType to Types.JAVA_OBJECT?

[vladmihalcea]

Please provide a replicating test case for this issue. Just fork the project and use only of the PostgreSQL unit tests as an example to create a new use case that demonstrates this issue.

Thanks.

[vladmihalcea]

Closing because no test case was provided to prove the issue.

[jwgmeligmeyling]

Finally got the time to create a reproducer. The issue is easily worked around, but I think a proper fix requires a change in Hibernate which is why I wanted you to have another look at the issue. See #109.