Sharing custom function implementations with community

[3bst0r]

Hi,

Many databases support more functions/operators than the common denominator that hibernate, HQL, etc. support.
E.g., Postgis has '<->' to support KNN queries.

As I understand the recommended way to make such functions/operators usable in HQL is to implement a custom SQLFunction and register it with a custom dialect, which I've tried and it works fine.

How would we share such extensions with the community? I am thinking about contributing a custom dialect and the custom functions for postgis, but I wanted to hear your opinions on how this could be meaningful for more people to use and less people writing the same "boilerplate" function implementations.

[sebersole]

Support for custom operators in HQL is not something I am going to do. As it seems you tried already, you'd define a custom function which renders itself using the operator syntax and use that in your query.

We could implement support for such a thing through Criteria via custom nodes, although for consistency it is honestly probably best to simply do the same in Criteria as well. Implementing a custom Criteria Expression node will require a bit of internals knowledge (e.g. you'd need it to extend SelfRenderingExpression).

As far as "sharing".. I assume you mean contributing? I don't think these make sense in hibernate-core proper. Maybe in hibernate-spatial, but I'll defer to @maesenka for that. In terms of it not being accepted upstream, you could also host a project which adds such capabilities.

I am also making the assumption that you are referring to 6.0, since these Discussions are limited to 6.0. In which case, all of these things are much more pluggable. For functions, it is as simple as leveraging Java's ServiceLoader mechanism to provide a FunctionContributor.

[3bst0r]

Hi @sebersole and thanks for the reply.

I agree that custom functions don't have a place in hibernate-core or even hibernate-spatial, even though I am curious about @maesenka thoughts. So I propose another project, that I'd be willing to initiate, that acts as a "plugin" to hibernate for various database-specific function extensions.

The implementation side of adding operators or functions would be rather trivial I assume (whether it's a 6.0 FunctionContributor or a 5.x SQLFunction), it's the publishing and compatibility that I currently don't have a grip on. How would this project ensure and communicate compatibility between Hibernate versions? Are there any other examples for such "plugins" to Hibernate that I could take inspiration from?

As a side question, is your comment about 6.0 only referring to HQL, and not to Criteria? Or is Criteria also more pluggable in 6.0?

[beikov]

Maybe it's a stupid question, but isn't the PostGIS operator approach based on a sphere which other database might also support? For example MariaDB: https://mariadb.com/kb/en/st_distance_sphere/

So let's maybe name the functions accordingly and try to use the "standard naming" for these operators.

• st_distance_sphere renders to <->
• st_distance_cpa renders to |=|
• etc.

If we can maybe emulate these functions somehow through the standard set of functions, that would make this even desirable, but I don't know enough about the function to understand if that is possible.

[3bst0r]

Not a stupid question. It boils down to the question if <-> can be substituted for https://postgis.net/docs/ST_DistanceSphere.html in any case. I assume not, because that would mean <-> is merely syntactic sugar, and the docs don't say that. It would also mean that postgis could have implemented KNN search using ST_DistanceSphere in the ORDER BY, but it doesn't. I will try to find out more about the exact differences when I have time.

[beikov]

AFAIK PostgreSQL has a problem with indexes and functions which is why the operator is needed, but I'm pretty sure that all operators have a corresponding function name.

[maesenka]

@3bst0r I've created issue HHH-15160 for this.

I don't mind to expand the set of standard functions beyond what we currently have, and st_distancesphere is indeed a good candidate.

However, I've been looking at the documentation and code of the '<->' operator. The operator is syntactic sugar, but the function invoked is different when used on Geometries, resp. Geographies. On Geometries the function geometry_distance_centroid is invoked, but on Geographies it's geography_distance_knn.

Now we don't (yet) have a distinction between Geometry/Geography in the Java geometry libraries, but Hibernate Spatial (mostly) works with both. So in order not to confuse users I think on balance it is justified to have special functions for these specific postgis operators.

[beikov]

Now that we have HHH-15160 for tracking this, I'll close this discussion.