Avoid the trap of using where('x', '=',null) instead of where('x', 'is',null)

[SimonSimCity]

Today, I ran into a problem of a query not behaving as I expected. After researching a bit, I found a stackoverflow question, which pointed me to https://en.wikipedia.org/wiki/Null_%28SQL%29#Effect_of_Unknown_in_WHERE_clauses, which got summarized by someone on that question as:

= NULL is always unknown (this is piece of 3 state logic), but WHERE clause treats it as false and drops from the result set. So for NULL you should use IS NULL

There are multiple articles about it, and since this library is about types, I'd like it to help me avoiding this error by not allowing null when using = or != or any other operator which isn't about types.

My proposal would be to split up COMPARISON_OPERATORS in src/operation-node/operator-node.ts and make it a combination of COMPARISON_OPERATORS_NULL_SAFE and COMPARISON_OPERATORS_NOT_NULL_SAFE.

I know, that having WHERE foo = null is valid SQL, but it's a known source of error, because this will most likely not evaluate as you'd expect it.

If you see this as a valuable contribution, I can create a PR.

[koskimas]

We don't currently have operator specific behavior in filters by design. The issue is much slower type checking if not done and maintained religiously.

There are many cases where it'd be great tho. This issue, only arrays with in and not in etc.

Maybe we should reconsider this in the future.

[SimonSimCity]

To everyone who wants to have a quick glance at if he's using a null-value as third argument in a where() clause:

1. Open kysely/dist/esm/parser/binary-operation-parser.d.ts.
2. Replace the line
   export type OperandValueExpressionOrList<DB, TB extends keyof DB, RE> = ValueExpressionOrList<DB, TB, ExtractTypeFromReferenceExpression<DB, TB, RE> | null>;
   by the line
   export type OperandValueExpressionOrList<DB, TB extends keyof DB, RE> = ValueExpressionOrList<DB, TB, NonNullable<ExtractTypeFromReferenceExpression<DB, TB, RE> | null>>; (I just wrapped the type for the value in NonNullable<>)
3. Run npx tsc --noEmit and you'll get a list of all the places where you put null or an array possibly containing null as third argument of a where() clause.
4. Don't forget to revert the changes you did in kysely/dist/esm/parser/binary-operation-parser.d.ts 😅