Investigate alternative translations for (in)equality comparison

[ranma42]

The current translation of nullableA == B is nullableA = B AND nullableA IS NOT NULL.
Similarly, nullableA != B is translated to nullableA <> B OR nullableA IS NULL.

This causes the duplication of the nullableA expression.

Alternative translations that could avoid this issue are CASE WHEN nullableA == b THEN TRUE ELSE FALSE END / CASE WHEN nullableA == b THEN FALSE ELSE TRUE END.

[ranma42]

Note that this would not fix the issue of double evaluation on SqlServer unless #32519 is fixed.

EDIT: this was referring to the older alternative translation COALESCE(nullableA = B, FALSE) / COALESCE(nullableA <> B, TRUE).

[ranma42]

⚠️ while this alternative translation is interesting to avoid duplication of sub-expressions, it might cause worse plans to be generated, for example when nullableA is a simple column expression.

For example Sqlite could use indexes on a predicates like nullableA <> B OR nullableA IS NULL / nullableA = B AND nullableA IS NOT NULL.

[ranma42]

For example Sqlite could use indexes on a predicates like nullableA <> B OR nullableA IS NULL [...]

The index would not be useable for the nullableA <> B, so for the inequality it looks like a safe transformation.
For equality, more care might be needed. The same holds true for other comparisons; see 4db69e0 where the same CASE structure is used (and the same issues with indexes would be hit; on the bright side, that translation is only used when !optimize, aka outside of predicates).

[roji]

The current translation of nullableA == B is nullableA = B AND nullableA IS NOT NULL.

This only referes to "non-optimized" mode, right?

on the bright side, that translation is only used when !optimize, aka outside of predicates).

Note that !optimize does occur in predicate, e.g. within negation.

In any case, doesn't the switch to IS NOT DISTINCT FROM help here?

[ranma42]

The current translation of nullableA == B is nullableA = B AND nullableA IS NOT NULL.

This only referes to "non-optimized" mode, right?

Yes (for the !=/OR case, instead, we always emit the nullableA <> B OR nullableA IS NULL).

on the bright side, that translation is only used when !optimize, aka outside of predicates).

Note that !optimize does occur in predicate, e.g. within negation.

In any case, doesn't the switch to IS NOT DISTINCT FROM help here?

Yes, IS NOT DISTINCT FROM is a better solution, but requires explicit support from the DB provider.