Jooq: Add synthetic [NOT] LIKE ANY and [NOT] LIKE ALL operators

[lukaseder]

A lot of times, SQL users would like to be able to combine LIKE and IN predicates, as in:

SELECT *
FROM customer
WHERE last_name [ NOT ] LIKE ANY ('A%', 'E%') [ ESCAPE '!' ]

The workaround is to manually expand the predicate to the equivalent

SELECT *
FROM customer
WHERE last_name LIKE 'A%'
OR last_name LIKE 'E%'

jOOQ could support such a synthetic predicate out of the box.

---

See: https://stackoverflow.com/a/6074261/521799

[lukaseder]

Hah, I just needed this today! :-)

[knutwannheden]

Too bad I didn't already implement it!

[lukaseder]

I managed :-) But when you do, you could refactor the logic in SQLServerTableDefinition, such as:

        Field<UByte> columnPrecision =
            when(TYPES.NAME.like(inline("date%")).or(TYPES.NAME.like(inline("time%"))), inline(ubyte(0)))
            .else_(ALL_COLUMNS.PRECISION).as("c_precision");

To

        Field<UByte> columnPrecision =
            when(TYPES.NAME.likeAny(inline("date%"), inline("time%")), inline(ubyte(0)))
            .else_(ALL_COLUMNS.PRECISION).as("c_precision");

[knutwannheden]

Based on the description I assume it would be desirable if the parser would also understand the LIKE ANY and NOT LIKE ALL clauses. In Field we would offer corresponding likeAny() and notLikeAll() overloads. The concrete suggestion for overloads for likeAny() (same for notLikeAll()) would be:

• likeAny(String... values)
• likeAny(Collection<String> values)

Note that there are several orthogonal aspects here (negation through NOT, collection vs. varargs, values vs. Fields, escaping through ESCAPE, case sensitivity, and regexp matching), which would cause the number of new methods to explode, if we were to cater for all combinations. We can of course address some by introducing a new return type with corresponding member methods. While this would probably make sense for some aspects (e.g. escaping), it probably doesn't make sense for the other aspects (e.g. case sensitivity or regexp matching), as it would look a bit alien.

To address escaping the methods would simply return the existing LikeEscapeStep type.

The implementation would rely on a new implementation class rather than extending CompareCondition and thus also the Comparator enum).

If the user wants to do an ILIKE ANY he would have to do this manually for now. E.g.:

Optional<Condition> cond = List.of("foo%", "bar%", "baz%").stream()
    .map(p -> MY_FIELD.likeIgnoreCase(p, '!'))
    .reduce(Condition::or);

While this isn't as concise as it would be with a dedicated method:

Condition cond = MY_FIELD.likeAnyIgnoreCase("foo%", "bar%", "baz%").escape('!');

It is probably good enough for the more unusal cases.

[knutwannheden]

Should we also have overloads for Field as in:

• likeAny(Field<String>... fields)
• likeAny(Collection<? extends Field<String>> fields)

The problem is that the latter collides with likeAny(Collection<String>) due to type erasure, so we could only offer one of them (if we don't want to change the method name of one of them).

My proposal would be to keep only likeAny(Collection<? extends Field<String>>) so the list of overloads would look like this:

• likeAny(String... values)
• likeAny(Field<String>... fields)
• likeAny(Collection<? extends Field<String>> fields)

[lukaseder]

Based on the description I assume it would be desirable if the parser would also understand the LIKE ANY and NOT LIKE ALL clauses

Yes, we definitely want support for this in the parser

Note that there are several orthogonal aspects here (negation through NOT, collection vs. varargs, values vs. Fields, escaping through ESCAPE, case sensitivity, and regexp matching), which would cause the number of new methods to explode

We can think about this later. I think that some LIKE modifiers could definitely be added in the future, as separate methods / flags that can be passed to this API. But I wouldn't want to implement this right now. Let's wait for people to request it.

If you want, you can think about all the possible options and make some API suggestions in a new issue, that we schedule for 3.13.

To address escaping the methods would simply return the existing LikeEscapeStep type.

Yes, that's my expectation here.

My proposal would be to keep only likeAny(Collection>) so the list of overloads would look like this:

• likeAny(String... values)
• likeAny(Field<String>... fields)
• likeAny(Collection<? extends Field<String>> fields)

Unfortunately, we cannot have the third overload in this form. Given the constraints imposed by the Java language, the overloads should be, instead:

• likeAny(String... values)
• likeAny(Field<String>... fields)
• likeAny(Collection<?> fields)

A precedent is Field.in(Collection<?>), originating from jOOQ 3.4.0 https://github.com/jOOQ/jOOQ/issues/2973

It's OK to occasionally sacrifice type safety in exchange for convenience.

[knutwannheden]

Regarding likeAny(Collection<?> fields): There are currently quite a few API methods where the parameter has the type Collection<? extends Field<?>>. Would that work or do I have to go with Collection<?>, which would be very unfortunate, as the API wouldn't even make it clear whether this is a collection of Strings or Fields.

Further, also using Collection<? extends Field<?>> isn't very nice, as at some point in accept() the Java object will have to be cast to a String or will we have to use DSL#cast()?

I looked at issue #2973 but to me the use case which would require Collection<?> is not clear at this time. Can you point me to an example?

[lukaseder]

There are currently quite a few API methods where the parameter has the type Collection<? extends Field<?>>

Sure, that type isn't forbidden. But usually, in those cases, there are only two overloads:

• x(Field<T>...)
• x(Collection<? extends Field<T>>)

In this case, we have a third overload, which makes all the difference. The one that lists T... as a convenience for wrapping each value in DSL.val(T) to obtain Field<T>...

[lukaseder]

hich would be very unfortunate, as the API wouldn't even make it clear whether this is a collection of Strings or Fields.

We'll obviously accept both! Have a look at AbstractField.in(Collection<?>)

Further, also using Collection<? extends Field<?>> isn't very nice, as at some point in accept() the Java object will have to be cast to a String or will we have to use DSL#cast()?

Not sure if I understand this concern...?

[knutwannheden]

I still don't understand the problem with the three overloads as I proposed them, at least not from the Java language perspective. Of course one downside is that it would not allow for a fourth overload likeAny(Collection<String>) and this problem is then of course solved by having likeAny(Collection<?>) instead.

In the implementation I was planning on rendering the SQL using the jOOQ DSL; i.e. using Field#like(Field<String>) etc. This will then of course require a Java cast from Field<?> to Field<String>, but I guess that should be fine.

[lukaseder]

I still don't understand the problem with the three overloads as I proposed them, at least not from the Java language perspective. Of course one downside is that it would not allow for a fourth overload likeAny(Collection) and this problem is then of course solved by having likeAny(Collection) instead.

Your proposal is inconvenient for the user, that's all. And in this case, I would favour convenience over type safety. I'm already foreseeing users complaining about the missing overload, and I wouldn't want to spend any time explaining the limitations of the Java Language to them :-)

A better solution would be to use reified generics (we don't have those), or union types: likeAny(Collection<? extends String|Field<String>>) (we don't have those either). So, likeAny(Collection<?>) is a good compromise.

In the implementation I was planning on rendering the SQL using the jOOQ DSL; i.e. using Field#like(Field) etc. This will then of course require a Java cast from Field to Field, but I guess that should be fine.

Please have a look at the implementation of like(). I think this has already been solved.

[knutwannheden]

Your proposal is inconvenient for the user, that's all. And in this case, I would favour convenience over type safety. I'm already foreseeing users complaining about the missing overload, and I wouldn't want to spend any time explaining the limitations of the Java Language to them :-)

OK. Now I understand what you mean.

Please have a look at the implementation of like(). I think this has already been solved.

I doubt we have this case there, but I will have a look.

[lukaseder]

I doubt we have this case there, but I will have a look.

Then I probably don't understand the case you have in mind. I'll just review the change and tests, if that's OK with you?

[knutwannheden]

For both predicates we should either define what the result is when applied to the empty list or prohibit empty lists. The latter would be consistent with how the IN predicate works in many major SQL dialects (SQLite and H2 are the exceptions I am aware of). Thoughts?

[knutwannheden]

For now I decided to allow LIKE ANY () (will render like DSL#falseCondition()) and NOT LIKE ALL () (rendering like DSL#trueCondition()).

[lukaseder]

For both predicates we should either define what the result is when applied to the empty list or prohibit empty lists. The latter would be consistent with how the IN predicate works in many major SQL dialects (SQLite and H2 are the exceptions I am aware of). Thoughts?

I'd like the behaviour to be consistent with IN. While there are indeed dialects that implement empty in lists, I would expect those dialects to behave just like jOOQ. As a matter of fact, the do.

For now I decided to allow LIKE ANY () (will render like DSL#falseCondition()) and NOT LIKE ALL () (rendering like DSL#trueCondition()).

Yes, just like IN.

For the record, it's easy to reason about it in terms of a Stream reduction, and what should be the identity of such a reduction operation (my category theory fu is not sufficient to explain this more formally, but I'm sure it can be done):

System.out.println(Stream.<Boolean>of().reduce(true, Boolean::logicalAnd));
System.out.println(Stream.<Boolean>of(true).reduce(true, Boolean::logicalAnd));
System.out.println(Stream.<Boolean>of(true, false).reduce(true, Boolean::logicalAnd));

System.out.println(Stream.<Boolean>of().reduce(false, Boolean::logicalOr));
System.out.println(Stream.<Boolean>of(false).reduce(false, Boolean::logicalOr));
System.out.println(Stream.<Boolean>of(false, true).reduce(false, Boolean::logicalOr));

Result:

true
false

false
false
true

So, when we reduce an empty set of booleans with OR, we expect the result to be false, just as with

• a IN X, which is just syntax sugar for a = x1 OR a = x2 ... OR a = xn
• a LIKE ANY X which is just syntax sugar for a LIKE x1 OR a LIKE x2 ... OR a LIKE xn

When we reduce an empty set of booleans with AND, we expect the result to be true just as with

• a NOT IN X, which is just syntax sugar for a != x1 AND a != x2 ... AND a != xn
• a NOT LIKE ALL X, which is just syntax sugar for a NOT LIKE x1 AND a NOT LIKE x2 ... AND a NOT LIKE xn`

[lukaseder]

I'd like the behaviour to be consistent with IN. While there are indeed dialects that implement empty in lists, I would expect those dialects to behave just like jOOQ. As a matter of fact, the do.

Wer should be able to parse empty IN lists: https://github.com/jOOQ/jOOQ/issues/8852

[knutwannheden]

I will now also update the grammar and documentation.

[lukaseder]

@katzyn What do you think of this feature, as a natively supported feature in H2?

[katzyn]

It can be supported, but it looks like the trivial implementation will not be able to use the index.

[lukaseder]

Yes, that's a caveat, although it might not be the most selective predicate anyway, in a users' query. I can see how a user would expect an index to be used with LIKE ANY, but not with NOT LIKE ALL, which is still a useful exclusion filter...

[katzyn]

On the other hand, I'm not sure that such syntax is good enough.

In the SQL Standard ANY is used either as an aggregate function or in a quantifier comparison predicate. LIKE is also a predicate. But quantified comparison predicates have a subquery. In your syntax an expression list is used instead, so that syntax is not really compatible.

In PostgreSQL quantified comparison predicates can also be used with an array.

I don't like all these deviations. In H2 a query can be used almost everywhere just like a regular expression. For example, there is a conflict between IN subquery and IN(expression).

[lukaseder]

In the SQL Standard ANY is used either as an aggregate function or in a quantifier comparison predicate. LIKE is also a predicate. But quantified comparison predicates have a subquery. In your syntax an expression list is used instead, so that syntax is not really compatible.

We plan on supporting that syntax as well: https://github.com/jOOQ/jOOQ/issues/8853

In PostgreSQL quantified comparison predicates can also be used with an array.

Yes indeed. It's a pity it cannot be used with a list of values, too.

In H2 a query can be used almost everywhere just like a regular expression. For example, there is a conflict between IN subquery and IN(expression).

The first should not be possible, though, or does your subquery imply the (..)?

[katzyn]

or does your subquery imply the (..)?

Yes, I mean the <subquery> from the Standard with its parentheses. Of course, H2 resolves IN (query) as a predicate with a subquery.

It looks like H2 also accepts quantified comparison predicates with a single expression like 1 = ANY(1)
but that feature is not covered by tests and is not documented.

1 = ANY (1, 2) is not accepted.

[katzyn]

From my point of view all predicates with ANY|SOME|ALL should accept the same arguments, otherwise their behavior will be inconsistent with each other.

[lukaseder]

Sure, and I think that behaviour should include value lists. The ANY | SOME | ALL quantifiers are really modifiers of some operator. In the standard, this operator can be = | != | <> | .... In this suggestion, it can also be LIKE. It would probably make sense to further extend the set of operators that can accept quantifiers, e.g. IS [ NOT ] DISTINCT FROM.

Now, while a comma separated list of values is not supported for A = ANY B and A != ALL C, because these quantified comparison predicates have nice syntax sugars, namely A IN B and A NOT IN C, which can be used instead, doesn't mean that this omission is a reasonable one. It makes perfect sense to support comma separated lists of values with quantifiers. For example, the SQL standard doesn't support a reasonable way of writing A > ANY (x, y, z). We have to work around it by writing A > ANY (SELECT c FROM (VALUES (x), (y), (z)) AS t(c)). Which is a shame, no?

[lukaseder]

Given this very interesting discussion, I do wonder if we should really implement this feature using DSL.any() and DSL.all() instead, even if it's quite more tricky for jOOQ's internals. Otherwise, we'll have to eventually deprecate the current methods again...

[katzyn]

A > ANY (SELECT c FROM (VALUES (x), (y), (z)) AS t(c))

Why this query is so complicated?

Simple A > ANY(VALUES x, y, z) is enough (VALUES (x), (y), (z) is required by databases that don't support optional feature T051).

[lukaseder]

Good point

[knutwannheden]

As part of #8853 jOOQ now also supports LIKE quantified predicates in combination with subqueries. The emulation is done using an = quantified predicate:

a LIKE ANY (<select>):

TRUE = ANY (
  SELECT a LIKE pattern
  FROM (<select>) AS t(pattern)
)

a NOT LIKE ALL (<select>):

FALSE = ALL (
  SELECT a LIKE pattern
  FROM (<select>) AS t(pattern)
)