# Bug 3028: Support is/is not null

- Status: closed
- Project: Zero
- Creator: @aboodman
- Assignee: @tantaman
- Labels: alpha-want, zero
- Created: 2024-10-10T00:34:06Z
- Modified: 2024-11-16T17:05:53Z
- URL: https://bugs.rocicorp.dev/p/zero/issue/3028

## Description

The sample app in the setup instructions wants to select messages that aren’t a reply. It would be nice to be able to say `z.query.message.where('replyToID', 'IS NOT NULL')`.

This will require changes to `ast.ts` , `builder.ts` , and `query.ts` 

Interesting thread in progress on right way to do this:

https://rocicorp.slack.com/archives/C013XFG80JC/p1728347213059369

## Comments (6)

### @tantaman — 2024-10-22T14:02:56Z

Some thoughts:

rather than `IS NOT NULL` we should have `IS` and `IS NOT`. They will behave exactly the same as `=` except they also compare `null` with JS semantics rather than SQL semantics.

SQLite also handles `IS` and `IS NOT` this way so it'll be easier to translate optional filters. I think Postgres does have the specific `IS NOT NULL` and doesn't let you do `IS NOT some_non_null_value`.

```
sqlite> create table foo (a);
sqlite> insert into foo values (null), (1), (2);
sqlite> SELECT * FROM foo WHERE a IS NULL;

sqlite> SELECT * FROM foo WHERE a IS NOT NULL;
1
2
sqlite> SELECT * FROM foo WHERE a IS NOT 1;
NULL
2
sqlite> SELECT * FROM foo WHERE a IS 1;
1
sqlite>
```

### @aboodman — 2024-10-23T01:56:38Z

I thought about this a bunch more and I still think we should treat `null` separately (the way SQL does and the way Matt originally advocated). I do not think we should allow `null` to be passed as an argument to `=` or `!=`.

Here is why. We know we need the SQL-style semantics for correlations. We do not know if we will ever have first-class subqueries, but if we do, then `where()` will have to behave differently in that case. I rather not have that inconsistency.

As for whether `IS NULL` / `IS NOT NULL` vs `IS` / `IS NOT`, I don't feel too strongly. On second look I was being overly clever with `IS NULL`.

Reactions: 👍️ ×1 (rajczi)

### @rajczi — 2024-11-06T16:13:04Z

My 2c, for consistency I would treat `IS` / `IS NOT` as operators (like `=` or `!=`) and then pass the `null` (if wanted) as the value.  That would keep the signature similar for `IS` and `=`.
I'm not sure that I would go to the effort of preventing a user from passing `null` to `=` / `!=` as that's perfectly valid SQL syntax that will be processed by any SQL server and return no records.  Not sure I would ever expect the zero client to "fix" the query syntax.

Reactions: 👍️ ×1 (tantaman)

### @tantaman — 2024-11-16T13:38:08Z

We ended up needing this for read auth.

The issue is that we have not implement uncorrelated subqueries yet so policies like "isLoggedIn" can't work.

Example --

```ts
const isLoggedIn = (authData) => query.user.where('id', authData.sub)
```

That is a fully uncorrelated subquery which we can not yet append to read queries.

Until we have uncorrelated subquery support, we'll model the above rule with literal comparisons like so:

```ts
const isLoggedIn = (authData, {cmp}) => cmp(authData.sub, 'IS NOT', null)
```

---

Note:
We don't actually have a case for `isLoggedIn` for read policies in `zbugs` but we do need that rule for write policies. You can only create a comment/issue if you're logged in. It is more consistent to implement `IS` and `IS NOT` rather than create a special case just for write auth rules that do uncorrelated queries.

### @tantaman — 2024-11-16T15:11:37Z

https://github.com/rocicorp/mono/pull/3025

### @aboodman — 2024-11-16T17:05:51Z

Nice.