Add support for two-value (i.e. null-respecting) equality operator in DBs that support it

[asazernik]

Many SQL implementations have an operator that is like =, but treats null as a normal value, i.e. equal to itself and not equal to other values. There is a SQL standard syntax for this (a IS DISTINCT FROM b) that AFAICT is only supported by postgres, but there are non-standard operators with the same semantics in MySQL/MariaDB (<=>) and SQLite (IS). It's also possible to emulate this logic in standard SQL, using CASE WHEN to collapse three-value logic into two-value, though I imagine indexes have trouble with that.

My preferred route to support this would be to add a default implementation using the CASE WHEN implementation, and allow individual drivers to overwrite it with native implementations. This is similar to how upsert support was added.

What I can contribute, and what I'd like help with:

• Implementing this feature in drivers with native support seems fairly straightforward to me - all the variants are binary operators, and the standard slick.ast.Library.SqlOperator will do the job for me
• I'd appreciate someone pointing me in the right direction for implementing the more syntactically complex CASE WHEN emulation
• Naming help - is there some symbolic operator that's idiomatic in the Scala ecosystem that would be intuitive for this? Should I just go with def distinctFrom and def notDistinctFrom?

[hvesalai]

Hi @asazernik, a PR would be very welcome.

• long names like distinctFrom sounds good
• can you give a full examples of the CASE WHEN?

[asazernik]

Re: CASE WHEN, notDistinctFrom would look something like this:

CASE foo = bar
WHEN true THEN true
ELSE false
END

Using notDistinctFrom because it makes the mechanism more clear - using the binary nature of WHEN and ELSE logic flow to convert ternary to binary logic. The generated code is ugly, and I doubt any query planner will know how to usefully optimize this when used as a filtering or join condition, but as a fallback shim it kind of works.

I have no idea how to craft this kind of relatively complex syntax tree around values passed to a Slick method, would appreciate any pointers to similar existing code or good places to hook in.