Expressions Involving NULL

VSCode

Malloy and SQL use a "NULL tainting" approoach to null values in computation. Many computation which include a null produce a null result.

Malloy and NULL

For most operations, missing or null-valued data can still be used in a computation and the "NULL tainting", along with treating null as a "false-ish" value, mostly works. For example x = y results in null if either value is null, and the "false-ish" treatment of NULL means that comparison is meaningful, even if some of the values are null.

SQL = with NULLs
`x`	`y`	SQL `x = y`	Acts Like
`1`	`1`	true	true
`1`	`2`	false	false
`1`	null	null	false
null	null	null	false

However, the boolean output of a negated boolean operation, when null tainted, is not useful, and SQL programmers have to protect any use of NOT or != in order for the result to be correct.

SQL NOT with NULLs
`x`	SQL `NOT x`	Acts Like
`true`	false	false
`false`	true	true
null	null	false

Malloy avoids this by coalescing the output of NOT to produce the much more useful truth table ...

Malloy NOT with NULLs
`x`	Malloy `NOT x`
`true`	false
`false`	true
null	true

Inequality

Similarly != equality produces a nonsensical result when some of the values are null.

SQL != with NULLs
`x`	`y`	SQL `x != y`	Acts Like
`1`	`1`	false	false
`1`	`2`	true	true
`1`	null	null	false
null	null	null	false

Malloy also protects the inequality comparison with a coalesce, to give the much more useful truth table ...

Malloy != with NULLs
`x`	`y`	Malloy `x != y`
`1`	`1`	false
`1`	`2`	true
`1`	null	true
null	null	true