4 ms·

"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 N

"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-with-nulls/ http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-wi...

zodiac 6y ago
Well, I think that's analogous to the max function having signature (say) Array<Int> -> Optional<Int>
- jeff-davis 6y ago
  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.
  - christophilus 6y ago
    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.
    - petters 6y ago
      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)
      - magicalhippo 6y ago
        And you can have that by using COALESCE(SUM(A), 0) for example.
    - heavenlyblue 6y ago
      But COUNT is just a SUM over ones for every row in the output
      - tsimionescu 6y ago
        By what definition? If anything, sums are defined as cardinalities of sets, rather than defining cardinalities in terms of sums.
        heavenlyblue 6y ago
        Then what is a NULL cardinality?
  - zodiac 6y ago
    > 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
    - cerved 6y ago
      imho, aggregation on []<x> should return option<x> unless aggregation on an empty list is exceptional in which case it may throw
  - tsimionescu 6y ago
    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?
    - anonymoushn 6y ago
      0 and 0 in Haskell
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 ago
    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.

zodiac 6y ago

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

jeff-davis 6y ago
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.
- christophilus 6y ago
  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.
  - petters 6y ago
    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)
    - magicalhippo 6y ago
      And you can have that by using COALESCE(SUM(A), 0) for example.
  - heavenlyblue 6y ago
    But COUNT is just a SUM over ones for every row in the output
    - tsimionescu 6y ago
      By what definition? If anything, sums are defined as cardinalities of sets, rather than defining cardinalities in terms of sums.
      - heavenlyblue 6y ago
        Then what is a NULL cardinality?
- zodiac 6y ago
  > 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
  - cerved 6y ago
    imho, aggregation on []<x> should return option<x> unless aggregation on an empty list is exceptional in which case it may throw
- tsimionescu 6y ago
  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?
  - anonymoushn 6y ago
    0 and 0 in Haskell

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