PostgresPlusDialect inconsistent behavior

Hibernate ORM
1

Hi,
I am getting below error for query:

SELECT TO_TIMESTAMP_TZ ('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM') from com.edb.test.hibernate.Employee

when using binary jar hibernate-core-5.2.11.Final.jar. This error is due to the fact that PostgresPlusDialect does not support the TO_TIMESTAMP_TZ function.

Problem occurred
java.lang.IllegalArgumentException: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'TO_TIMESTAMP_TZ' {originalText=TO_TIMESTAMP_TZ}
    \-[EXPR_LIST] SqlNode: 'exprList'
       +-[QUOTED_STRING] LiteralNode: ''2003/12/13 10:13:18 -8:00''
       \-[QUOTED_STRING] LiteralNode: ''YYYY/MM/DD HH:MI:SS TZH:TZM''
 [SELECT TO_TIMESTAMP_TZ ('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM') from com.edb.test.hibernate.Employee]
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:133)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:164)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:670)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:103)

However, when putting same function in test case of hibernate github code in “org.hibernate.orm.test.timestamp.JdbcTimeCustomTimeZoneTest” as

"select TO_TIMESTAMP_TZ('2003/12/13 10:13:18 -8:00','YYYY/MM/DD HH:MI:SS TZH:TZM') from Person"

It gets executed successfully. May I know why is this difference between binary jar and hibernate source code?

There would obviously be difference of code versions between jar and source code, but I was expecting same error in source code as well due to having same dialect class(PostgresPlusDialect).

Any hint or direction would be appreciated in this regard.

Thanks,
Affaan

2

By “hibernate source” I guess you mean you are using the code from the main branch? That branch is for version 6.0 and as you can imagine, a lot has changed since 5.2 which was first released around 2017.

The exception is that no return type can be determined because the function to_timestamp_tz is not known to Hibernate. You will have to register the function if you want to use that in the select clause with 5.2 like this in a custom Dialect subclass:

registerFunction( "to_timestamp_tz", new StandardSQLFunction("to_timestamp_tz", StandardBasicTypes.TIMESTAMP) );
3

Thanks beikov for the reply.

Yes I am using code from main branch. I am assigned a task of updating PostgresPlusDialect if some EnterpriseDB functions are not supported by this dialect.

I am aware of the fact that functions unknown to hibernate need to be registered but my target is to register such functions within PostgresPlusDialect instead of having custom Dialect subclass.
Before doing that I need to have test case with latest hibernate code to establish that certain functions that were added to EnterpriseDB after last update of PostgresPlusDialect are not supported.

As you mentioned that to_timestamp_tz is not known to hibernate in 5.2, is there any way other than dialect subclass or registering function inside Dialect class itself, that this function can be supported in hibernate 6?

Thanks,

4

In Hibernate 6 this is not a problem anymore as it asks the JDBC driver for the expected type. Either way, you can contribute functions through a MetadataContributor by adding them to Metadata#getSqlFunctionMap

5

Thanks beikov.

I will look into MetadataContributor. I am in the process of understanding hibernate code.

If Hibernate 6 can handle to_timestamp_tz function without requiring it to register in the dialect class, then is there any reason to contribute such functions through MetadataContributor?

If you could provide bit more insights in this regard, that would really be helpful for me to understand the concept. Any blog related with this would also be great.

Thanks,

6

If Hibernate 6 can handle to_timestamp_tz function without requiring it to register in the dialect class, then is there any reason to contribute such functions through MetadataContributor ?

Well, like I wrote, this is dependent on the JDBC driver support and the Java type mapping that Hibernate chooses for a SQL type which might not always be what you want. Apart from that, registering a function also allows to specify the required argument types in case you want some more early validation.

If you can update to Hibernate 6, that’s great, but if you can’t, you will simply have to create a custom MetadataContributor and register functions.