PostgresPlusDialect SQL functions in version 5.6 vs version 6.1

Hibernate ORM
1

Hi,
I am trying to understand the logic behind an observation:
In hibernate 5.6 functions are registered as:

public PostgresPlusDialect() {
		super();

		registerFunction( "ltrim", new StandardSQLFunction( "ltrim" ) );
		registerFunction( "rtrim", new StandardSQLFunction( "rtrim" ) );
		registerFunction( "soundex", new StandardSQLFunction( "soundex" ) );
		registerFunction( "sysdate", new NoArgSQLFunction( "sysdate", StandardBasicTypes.DATE, false ) );
		registerFunction( "rowid", new NoArgSQLFunction( "rowid", StandardBasicTypes.LONG, false ) );
		registerFunction( "rownum", new NoArgSQLFunction( "rownum", StandardBasicTypes.LONG, false ) );
		registerFunction( "instr", new StandardSQLFunction( "instr", StandardBasicTypes.INTEGER ) );
		registerFunction( "lpad", new StandardSQLFunction( "lpad", StandardBasicTypes.STRING ) );
		registerFunction( "replace", new StandardSQLFunction( "replace", StandardBasicTypes.STRING ) );
		registerFunction( "rpad", new StandardSQLFunction( "rpad", StandardBasicTypes.STRING ) );
		registerFunction( "translate", new StandardSQLFunction( "translate", StandardBasicTypes.STRING ) );
		registerFunction( "substring", new StandardSQLFunction( "substr", StandardBasicTypes.STRING ) );
		registerFunction( "coalesce", new NvlFunction() );
		registerFunction( "atan2", new StandardSQLFunction( "atan2", StandardBasicTypes.FLOAT ) );
		registerFunction( "mod", new StandardSQLFunction( "mod", StandardBasicTypes.INTEGER ) );
		registerFunction( "nvl", new StandardSQLFunction( "nvl" ) );
		registerFunction( "nvl2", new StandardSQLFunction( "nvl2" ) );
		registerFunction( "power", new StandardSQLFunction( "power", StandardBasicTypes.FLOAT ) );
		registerFunction( "add_months", new StandardSQLFunction( "add_months", StandardBasicTypes.DATE ) );
		registerFunction( "months_between", new StandardSQLFunction( "months_between", StandardBasicTypes.FLOAT ) );
		registerFunction( "next_day", new StandardSQLFunction( "next_day", StandardBasicTypes.DATE ) );
	}

But in hibernate 6.1, it seems these functions are moved to its parent class by providing

CommonFunctionFactory class and registered these functions as:

@Override
	public void initializeFunctionRegistry(FunctionContributions functionContributions) {
		super.initializeFunctionRegistry(functionContributions);

		CommonFunctionFactory functionFactory = new CommonFunctionFactory(functionContributions);
		functionFactory.soundex();
		functionFactory.rownumRowid();
		functionFactory.sysdate();
		functionFactory.systimestamp();

//		queryEngine.getSqmFunctionRegistry().register( "coalesce", new NvlCoalesceEmulation() );

	}

When I called for example MEDIAN function as

scope.inTransaction( session -> {
				sqlStatementInspector.clear();
				assertEquals(
						1.78d,
						session.createQuery(
								String.format( "select median(h.bigDecimalValue) from Human h", function ),
								BigDecimal.class
						).getSingleResult()
				);

Hibernate logs show it as mapped to percentile_cont(0.5) function:

15:15:24,279 DEBUG SQL:128 - 
    select
        percentile_cont(0.5) within 
    group (order by
        h1_0.bigDecimalValue) 
    from
        Human h1_0

But when I called INSTR function, it is shown as:

15:09:57,128 DEBUG SQL:128 - 
    select
        instr('Tech on the net','e')

I checked in PostgreSQLDialect which is direct parent class of PostgresPlusDialect and did not find in the initializeFunctionRegistry.
May I know how PostgresPlustDialect knows that instr function is added to function registry?
And how it knows that MEDIAN needs to map to percentile_cont(0.5) which actually an equivalent function in PostgreSQL database?

I am using Postgres Advanced server as backend database.

Thanks,
Affaan

2

I checked in PostgreSQLDialect which is direct parent class of PostgresPlusDialect and did not find in the initializeFunctionRegistry.

Then you didn’t look carefully enough. The method is there. See https://github.com/hibernate/hibernate-orm/blob/6.1/hibernate-core/src/main/java/org/hibernate/dialect/PostgreSQLDialect.java#L539 where it calls functionFactory.median_percentileCont( false ); which registers the median function.

May I know how PostgresPlustDialect knows that instr function is added to function registry?

That function is actually not registered and Hibernate falls back to treating this as a “native” function i.e. the function is just assumed to exist. Try it out, you can use any function name and Hibernate will just assume the DB supports it i.e. select abc()

3

I am aware of this function exists there. I meant that I could not find INSTR over there.

  • With respect to MEDIAN function, my question was how hibernate knows that it has to register MEDIAN by calling median_percentileCont from PostgreSQLDialect when MEDIAN is called from within user query?
  • May I know in which version of hibernate this was introduced?
  • If it assumes non-registered functions to exist then what is the significance of functions that are registered? We cannot make such assumptions for them as well?
  • Is this behaviour of assuming to exist also true in case of non-registered data types?

Thanks,

4

I don’t understand your questions, sorry. Hibernate offers the function median which has certain semantics. If a database dialect does not support that function natively, we strive to emulate the semantics if possible and it just so happens, that for PostgreSQL, calling percentile_cont(0.5) within group (order by abc) is the same as median(abc), so we implemented it this way.

May I know in which version of hibernate this was introduced?

This is the behavior of Hibernate since forever.

If it assumes non-registered functions to exist then what is the significance of functions that are registered? We cannot make such assumptions for them as well?

Type coercion, argument validation. If the dialect doesn’t support a function, then we obviously have to register an emulation.

Is this behaviour of assuming to exist also true in case of non-registered data types?

No idea what that means. What “data types” are you talking about? Hibernate has to read data somehow from JDBC and transform that to the Java types of your domain model, so it must know the JdbcType and JavaType to use, and these two types must know how to convert from one representation to another. This is the fundamental concept the type system in Hibernate is based on. If Hibernate doesn’t know the JdbcType or JavaType to use, it will fail booting, telling you exactly that.

5

Thanks. You understood my questions rightly.

I am actually trying to find out what is not supported by PostgresPlusDialect so that support for that can be contributed back to community from EDB.
But I can see that current design of hibernate is much improved and as you mention provides support for the things that even dialect does not have support for that.

I would like to have your expert opinion/recommendation for if there is something that PostgresPlustDialect does not support and I on behalf of EDB should contribute back?

Thanks,

6

Since we are testing the PostgresPlusDialect on every commit against a EDBv14, I don’t think that there is anything significant missing right now. There are some general PostgreSQL related things that we would love to get help for, but not sure if that is EDB related enough so that it would be fine for you to contribute that back.

Either way, I would like to invite you to our chat platform Zulip in case you want to discuss further collaboration.

7

Thanks for the invite. Can I get the details of PostgreSQL related things to see if that is EDB related?