Hey, thanks for bringing that up.

First, let's remember that CONTAINS is specific to SQL Server. Other RDBMS most likely expose FTS in different ways, which might not be easy to convert except for the simplest cases. That's fine though: a function that only works for SQL Server would already be useful to lots of people.

Determine if there is overlap with Hibernate Search here

To some extent, yes. Though Hibernate Search also provides a way to offload your FTS to another service, as well as various other features such as scoring or faceting. There are reasons for solutions like Elasticsearch/OpenSearch/Lucene to exist even though relational databases are better in non-FTS areas: they provide advanced features that relational databases don't.

if we can translate contains() on ElasticSearch/OpenSearch and Lucene.

I'm not a fan of the idea, mainly because CONTAINS is not standard SQL, so it feels a bit weird to tie ourselves to SQL-Server-specific syntax.

But even if we ignore that, CONTAINS is only part of the solution. Equally as important is the definition of the index: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-index-transact-sql?view=sql-server-ver15
Defining the index involves defining how text will be turned into tokens, which really is at the core of FTS, and customization here will dramatically change the result of a CONTAINS predicate.

Index definition involves things like tokenizing (wi-fi -> wi + fi or just wifi?) removing diacritics (résumé -> resume), stemming (running/runs -> run, bacteria/bacterium -> bacter), etc. Lots of which are use-case-dependent. And, I suspect, lots of which are only available on Elasticsearch/OpenSearch/Lucene, with no equivalent in SQL Server (or a fundamentally different equivalent, such as the synonym token filter which is apparently the FREETEXT predicate on SQL Server).

So, while we could probably translate CONTAINS predicates to Elasticsearch/OpenSearch/Lucene predicates to some extent, that wouldn't be so useful in itself. We would also have to, either:

• translate the index definition from SQL Server to Elasticsearch/OpenSearch/Lucene. I doubt that's possible in a reliable way, and it would be limiting compared to what Elasticsearch/OpenSearch/Lucene can do.
• OR let users define it directly in native Elasticsearch/OpenSearch/Lucene... leading to a bastard solution where you're using MS-SQL syntax when querying and Elasticsearch/OpenSearch/Lucene syntax when configuring, most likely leading to some inconsistencies.

Another thing to note is that Elasticsearch/OpenSearch/Lucene don't support cross-index joins. So if you were to translate SQL from to Elasticsearch/OpenSearch/Lucene, either you wouldn't support joins at all (limiting queries to a single table) or you would have to define joins at indexing time, like we do in Hibernate Search with @IndexedEmbedded.

And then you have concepts that only exist on Elasticsearch/OpenSearch/Lucene, which you would miss out on, because you're using Hibernate ORM and its SQL-specific APIs:

• scoring and everything that goes with it, like the bool predicate which will allows an OR where having more than one true operands does matter, because it affects the score.
• hit counts and faceting, where the same query execution will return not only tuples, but also extra data such as the total number of hits (beyond the limit you set for the number of returned tuples) and facets (a kind of aggregations, but not exactly in the same meaning as SQL aggregations).

To sum up:

• Supporting CONTAINS on MS-SQL is great.
• Trying to make it work on Elasticsearch/OpenSearch/Lucene would be possible, but so limited that I'm not sure that's worth the effort. Better stay on MS-SQL if you're going to use only CONTAINS.

contains_function: CONTAINS "(" (path ",")+ contains_query_string, analyzer_config=string_literal ")";

Do I understand correctly that you intend to accept multiple paths? Do all DB dialects support that? I'm not sure about postgres...
You probably know that already, but I'll say it just in case: CONTAINS(foo, bar, "word1 word2") might not be equivalent to CONTAINS(foo, "word1 word2") OR CONTAINS(bar, "word1 word2"), so support for targeting multiple paths at once is not exactly something we can emulate.

Also, I think ideally the analyzer config would be optional? I expect the string literal has a specific syntax (surrounded by quotes), so this might not lead to ambiguity?

simple_term: [^\s]+;

Just so you know, it's technically possible (at least with ES/OS/Lucene) to tokenize on something else than whitespace. While that's certainly an exotic use case, we can keep in mind that a way to escape spaces could be useful in a future version of the grammar. Probably not something for V1 though.

As far as I can see, the features exposed by this grammar all have a relatively direct equivalent in ES/OS/Lucene, so we should be fine if we want to implement it one day in Hibernate Search. I only have a doubt about the "NEAR" operator; ES/OS/Lucene have a phrase query with a "slop" option which seems equivalent if there are only two words in the phrase, but I'm not 100% sure. Tests would help for sure.

Adding some notes based on a discussions with @gavinking and @marko-bekhta about this:

• We're thinking that it might be nice to have a minilanguage constructor function e.g. fts(..) to use like attr contains fts('...') or contains(attr, fts(...)), which allows using contains(attr, 'abc')/attr contains 'abc' to mean locate(attr, 'abc') > 0 and open the door for other minilanguages e.g. regex
• The content inside fts could be parsed by the HQL parser already
• Semantics of the NEAR function (that e.g. SQL Server defines) match what slop means in Lucene etc., so ditch the operator syntax for the function syntax
• Terms and phrases are always analyzed i.e. queries for a term are not exact lookups e.g. the term Filming might match film, depending on the underlying DB analyzers.

A few open questions that came up:

• Is the minilanguage really sufficiently useful? Compare with Lucene query language

Is the minilanguage really sufficiently useful? Compare with Lucene query language

Note that even the Lucene query language exposes only a small part of what Lucene can do. Which is why Solr/Elasticsearch have their own language - using JSON for Elasticsearch, and I think XML for Solr.

It's really a whole world, so reducing it to a "standardized" predicate or query language is likely to only address the most common use cases. It may be enough, though.