[Pxtl]

> SQL NULLs aren't weird, they're just based off of Kleene's TRUE-FALSE-UNKNOWN logic!

Kleene's TRUE-FALSE-UNKNOWN logic is weird.

SQL nulls effectively violate the reflexive property of equality, because X=X does not result in a value of TRUE.

And in many contexts in SQL, NULL is treated as equivalent to false, such as within a WHERE clause.

So that means that X=X is effectively FALSE in SQL*.

That is a clown language.

*(yes, I have the same opinion about ANSI NaN, but that wouldn't come up so often if Javascript didn't love NaN for some stupid reason.)

[dragonwriter]

> And in many contexts in SQL, NULL is treated as equivalent to false, such as within a WHERE clause.

NULL is not equivalent to FALSE, it is neither FALSE nor TRUE. It has the same effect as FALSE as the final result of evaluating a WHERE clause condition only because WHERE clause conditions allow a row to be included only when they evaluate strictly to TRUE. But if NULL were equivalent to FALSE in a WHERE clause, than a WHERE clause condition which would evaluate to NULL that was instead negated would be equivalent to TRUE but instead it remains NULL which remains not TRUE.

[Pxtl]

No, because NOT NULL/UNKNOWN is still NULL/UNKNOWN.

I realized earlier I was using the term NULL, but going forwards let's use the ANSI SQL concept where the null state of a Boolean is called UNKNOWN. You'll have to forgive me for using the term NULL this far, but in my defense the concept of NULL column-values and UNKNOWN boolean expression results are pretty intertwingled.

    SELECT * FROM foo WHERE NOT (1 = NULL)

resolves to

    SELECT * FROM foo WHERE UNKNOWN

which is the same value that

    SELECT * FROM foo WHERE 1 = NULL

resolves to.

So the WHERE clause is treating UNKNOWN/NULL as equivalent to false. The rest of the Boolean algebra is not.

SQL likes to have it both ways. Sometimes UNKNOWN/NULL is equivalent to false sometimes it's not.

It does this because UNKNOWN/NULL Booleans are incredibly inconvenient and tedious and sometimes they'd rather not think about them.

I'd like to do that as well, but this hoary old language won't give me the same permission it gives itself.

[tpmoney]

> And in many contexts in SQL, NULL is treated as equivalent to false, such as within a WHERE clause.

I don't think any databases treat `NULL` as `FALSE` in the WHERE clause. `SELECT * FROM foo WHERE bar = NULL` doesn't return rows with a NULL in the bar column. `SELECT * FROM foo WHERE bar != NULL` doesn't return rows without NULL in the bar column. `SELECT * FROM foo WHERE (bar = 'a') = NULL;` doesn't return rows where bar is not equal to `a`[1]. As far as I know every DB treats NULL as what it is, an unknown value.

It also doesn't to my mind violate the reflexive property because NULL is not equal to anything. It is a marker for an unknown value, not a value in and of itself. If you have a database of every person in a room and what color shirt they're wearing, and in your database, Alice and Bob both have NULL in their "shirt_color" column, that does not mean that Alice and Bob have the same color shirt. Nor does it mean that they don't have the same color shirt. Nor does it mean that someone with a green colored shirt has the same color shirt as Bob or Alice. It doesn't mean they don't have a shirt either. It means you don't have a record of/don't know what color their shirts are. You can't violate the reflexive property because you can't say what color shirt they have. You're not doing `X = X -> false`, you're doing `X = UNKNOWN -> UNKNOWN`

[1]: https://www.db-fiddle.com/f/iVDDRJos1pUqxnuy1jTEEe/0

[Pxtl]

It treats the NULL/unknown value of the boolean as false

1 <> NULL => Boolean UNKNOWN,

so SELECT * FROM foo WHERE 1 <> NULL returns nothing.

1 = NULL => Boolean UNKNOWN,

so SELECT * FROM foo WHERE 1 = NULL returns nothing.

That's the thing that's being treated as FALSE. That UNKNOWN. Not the value of NULL itself.

> You're not doing `X = X -> false`, you're doing `X = UNKNOWN -> UNKNOWN`

That's not how "=" works. If you want a relationship for testing equality than handles unknown, don't call it equality.

Basic properties of equality, from Wikipedia

https://en.wikipedia.org/wiki/Equality_(mathematics)

- Reflexivity: for every a, one has a = a.

- Symmetry: for every a and b, if a = b, then b = a.

- Transitivity: for every a, b, and c, if a = b and b = c, then a = c.

edit:

We can also see the incoherence of this concept when we look at set theory.

Because UNKONWN booleans are neither true or false, if you use them in a WHERE clause you get the ugly result that the set of

    X ⋃ Xᶜ

is not everything.

[tpmoney]

> That's the thing that's being treated as FALSE. That UNKNOWN. Not the value of NULL itself.

I think the difference between how we're looking at this is for me there is no "value" of NULL. NULL has NO value which is why you can't do `1 = NULL` or `1 <> NULL` and have to specifically use `1 IS NOT NULL` or `1 IS DISTINCT FROM NULL`

>That's not how "=" works. If you want a relationship for testing equality >[that] handles unknown, don't call it equality. >Basic properties of equality, from Wikipedia >https://en.wikipedia.org/wiki/Equality_(mathematics) >- Reflexivity: for every a, one has a = a. >- Symmetry: for every a and b, if a = b, then b = a. >- Transitivity: for every a, b, and c, if a = b and b = c, then a = c.

Sure, that's all well and good, but equality only can work IF you know what the values are on either side. NULL means you don't know what a value is and therefore you can't answer whether or not a = NULL because NULL isn't known yet.

Or let me put it another way, is the launch price of the iPhone 17 != $799? The answer is neither true nor false, because the launch price of the iPhone 17 is unknown. We can make reasonable guesses, but if I give you a database with entries for iPhones from the first model and placeholder rows for the 17, 18, 19 and 20 models, none of them belong in the list when someone asks "which iPhones cost $799 at launch?" But equally true that none of them belong in the list when someone asks "which iPhones did not cost $799 at launch?"

[empthought]

> That's the thing that's being treated as FALSE. That UNKNOWN. Not the value of NULL itself.

No, it's being treated as UNKNOWN, and the semantics of SELECT...WHERE only returns rows where the value of the condition is TRUE.

I think you need to look into https://en.wikipedia.org/wiki/Negation_as_failure