# Pattern 04 — NULL equality for shared resources

## The failure

AI generates a policy to allow access to shared (unowned) rows:

```sql
CREATE POLICY "select_own_or_shared" ON documents
  FOR SELECT USING (
    owner_id = auth.uid()
    OR owner_id = NULL          -- This is the bug
  );
```

In SQL, `NULL = NULL` evaluates to `NULL` — not `true`. This is three-valued logic: `true`, `false`, or `null`. In a RLS USING clause, `null` is treated as `false` (deny). So `owner_id = NULL` is always denied, regardless of what `owner_id` contains.

This means shared documents (rows where `owner_id IS NULL`) are permanently invisible. No user can ever read them. The bug is completely silent: no error, no warning, just missing data.

## Why AI gets this wrong

This is also incorrect general SQL, not just an RLS issue. But it's especially common in AI-generated RLS because:

1. In many programming languages, `x == null` is how you check for null — AI maps this to SQL incorrectly
2. The error produces no SQL syntax error, so it passes without warnings
3. The resulting query is logically valid; it just never matches shared rows

## The fix

Use `IS NULL` for null checks in SQL:

```sql
CREATE POLICY "select_own_or_shared" ON documents
  FOR SELECT
  TO authenticated
  USING (
    owner_id = auth.uid()
    OR owner_id IS NULL           -- IS NULL, not = NULL
  );
```

## Variant: IS NOT DISTINCT FROM

When you want equality that treats NULLs as equal (useful for comparing two nullable columns):

```sql
-- True if both are NULL, or both have the same non-null value
owner_id IS NOT DISTINCT FROM auth.uid()
-- Equivalent to: owner_id = auth.uid() OR (owner_id IS NULL AND auth.uid() IS NULL)
```

This is rarely what you want in RLS (it would allow unauthenticated users to see unowned rows if `auth.uid()` also returns NULL), but it's the correct operator when the semantic IS "treat null as equal."

## Testing it

Insert a shared document (owner_id = NULL) and verify you can read it as an authenticated user:

```sql
INSERT INTO documents (title, owner_id) VALUES ('Shared doc', NULL);

-- Test as authenticated user:
SELECT * FROM documents; -- Should include the shared doc
```

If the shared doc is missing from the results, you have the `= NULL` bug.