Hacker News Reader

Comment by jeff-davis | Hacker News Reader

"First of all, in SQL NULL is entirely your choice - no column is forced to accept NULL values."

That's not true. Aggregate functions and outer joins produce NULLs even without NULLs in your data. This absolutely can cause weirdness.

"The only exception are aggregation functions..."

It's more weird and subtle than that. See:

http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-wi...

View on HN

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.

> 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.

I 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.

Well, I think that's analogous to the max function having signature (say) Array<Int> -> Optional<Int>

But why is the SUM of no elements NULL, but the COUNT-star of no elements zero?

Sure, for any given case you can come up with some explanation. But there is no general explanation that covers many cases. And that leads to weirdness and surprises and bugs.

Option types are way better and always do what you expect.

Because 0 is a valid sum for non empty sets, so it’s nice to be able to differentiate between: the items add up to 0 and: there are no items to sum.

It is useful to have the sum and product of empty lists be 0 and 1, respectively.

This means that sum(A ++ B) = sum(A) + sum(B). (++ is concatenation)

And you can have that by using COALESCE(SUM(A), 0) for example.

But COUNT is just a SUM over ones for every row in the output

By what definition? If anything, sums are defined as cardinalities of sets, rather than defining cardinalities in terms of sums.

Then what is a NULL cardinality?

> Option types are way better and always do what you expect.

No, I can implement the exact same weird functions with option types, with signatures

sum: Array<Int> -> Optional<Int>

count_elements: Array<Int> -> Int

FWIW, I personally prefer both functions to not return Optionals, but the point is that the mere presence of Option types in your type system (and the absence of null pointers) doesn't magically eliminate bad library design

imho, aggregation on []<x> should return option<x> unless aggregation on an empty list is exceptional in which case it may throw

What else would the sum/min/max/avg of an empty list be? What else would the count of an empty list be?

What is the sum of an empty list in Haskell? What is its length?

0 and 0 in Haskell

Reply on news.ycombinator.com