3 ms·
From that list I think the only really surprising one is "3 NOT IT (1, 2, NULL)" returning NULL. The fact that ARRAY_AGG (C) and COUNT(C) treat NULL differently
by tsimionescu 6y ago
From that list I think the only really surprising one is "3 NOT IT (1, 2, NULL)" returning NULL. The fact that ARRAY_AGG (C) and COUNT(C) treat NULL differently is also a bit surprising, though in terms of usefulness I think the current behavior is not bad.
I also think the definitions is relatively clear: NULL means "unknown"/"missing data".
The MIN, MAX, and AVERAGE of an empty list is N/A. SUM could have been 0, but then SUM(X) / COUNT(X) would be different from AVERAGE(X).
The count of tuples (COUNT(*)) in an empty relation is 0.
The count of pieces of data on column X (COUNT(X)) of a table ((1), (NULL)) is 1.
If we have 2 tables A(ID, C1), VALUES ((1, 2)), B(ID, C2), VALUES ((2, 3)), and try to ask what value B.C2 has for A.ID=1, the answer can only be NULL.
IS NULL and IS DISTINCT FROM NULL are explicitly treating NULL differently, so it's not that surprising that they can be both true and both false.
Records and Strings are not the same KIND of types, so I don't see anything surprising about the fact that one can contain NULLs while the other can't. It's like complaining that a record in Haskell can have a Maybe field, while a String can't have a Maybe character.
- kd5bjo 6y ago> The MIN, MAX, and AVERAGE of an empty list is N/A. SUM could have been 0, but then SUM(X) / COUNT(X) would be different from AVERAGE(X). 0/0 is mathematically undefined— it is a reasonable calculation to produce NULL on its own, and then SUM could do the sensible thing of returning the additive identity.
- tsimionescu 6y agoI still find it much more useful in practice for SUM to return a NULL and not 0. Especially when plotting data over a period of time, having the chart go down to 0 and then go back up to some value is extremely annoying, and the query that would produce the current behavior of SUM would be much more annoying if it were implemented the other way around, while it is extremely easy to transform NULL to 0 if you want.