PostgresPlusDialect SQL functions in version 5.6 vs version 6.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

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 hibernate-orm/PostgreSQLDialect.java at 6.1 · hibernate/hibernate-orm · GitHub 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()

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,

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.