Support SQL IS binary operator

[FPtje]

I found myself manually creating the following definition:

is :: Esq.SqlExpr (Esq.Value a) -> Esq.SqlExpr (Esq.Value a) -> Esq.SqlExpr (Esq.Value Bool)
is = EsqInternal.unsafeSqlBinOp "IS"

Equivalently, one can imagine the following operator:

is_not :: Esq.SqlExpr (Esq.Value a) -> Esq.SqlExpr (Esq.Value a) -> Esq.SqlExpr (Esq.Value Bool)
is_not = EsqInternal.unsafeSqlBinOp "IS NOT"

(although this one can be done with the not_ (is ...) function)

The IS binary operator is most commonly seen in IS NULL and IS NOT NULL. esqueleto indeed has this in the isNothing function, but they can also be used for other values, e.g. foo IS true. The reason to use IS over = here is because they have different behaviors regarding NULL.

Here are the truth tables for x = y and x IS y for booleans:

x = y	true	false	NULL
true	true	false	NULL
false	false	true	NULL
NULL	NULL	NULL	NULL

x IS y	true	false	NULL
true	true	false	false
false	false	true	false
NULL	false	false	true

I didn't make a pull request because this function might be up to debate. Arguably, one might say that with a correct Persistent table definition, you statically know whether a column is NULL or not, so the IS operator may not technically be needed. On the other hand, there might be other expressions where NULL sneaks in to give funny behavior. What do you think?

---

Also, apologies if this issue is duplicate. It's kind of hard to search for a two-letter operator 😅

[belevy]

Is IS supported like this in all databases? If so, is there ever a reason to use =? Does IS perform differently? Honestly it feels like .== should just use IS if this is actually supported in all 3 of our supported DBs

[belevy]

looking this up, is can only be used for trivalued booleans (true, false, null)

[parsonsmatt]

So the proper type here is more like SqlExpr (Value (Maybe Bool)) -> SqlExpr (Value (Maybe Bool)) -> SqlExpr (Value (Maybe Bool))?

[Vlix]

Should be is :: SqlExpr (Value (Maybe Bool)) -> SqlExpr (Value (Maybe Bool)) -> SqlExpr (Value Bool), no?

[parsonsmatt]

Yes, good catch- thanks!

[FPtje]

I hadn't realized that the use is limited to true/false/null. The signature looks good to me 👍

[Vlix]

Yeah, it's limited to Bool and Maybe a, so I'd personally probably want the API to be more like:

-- Boolean operator
is :: SqlExpr (Value (Maybe Bool)) -> SqlExpr (Value (Maybe Bool)) -> SqlExpr (Value Bool)
-- Synonym for `isNothing`
isNull :: SqlExpr (Value (Maybe a)) -> SqlExpr (Value Bool)
-- Synonym for `not_ . isNothing`
isNotNull :: SqlExpr (Value (Maybe a)) -> SqlExpr (Value Bool)