bitemyapp / bitemyapp/esqueleto

Support SQL `IS` binary operator

Open
#358 7 comments 1 reaction 0 assignees View on GitHub
Null Handling
Dominant language
Haskell
Stars
399
Forks
107
Avg merge
1d 17h
Merged PRs (30d)
1

Description

I found myself manually creating the following definition:

```haskell
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:

```haskell
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 :sweat_smile:

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.