5 ms·

Would love to learn more about this single billion dollar mistake

• 00098345 10y ago
  SQL summation without coalescing null addenda to zero. The sum result was a null. This had been going on for years. Oops! The lesson/mitigation was to add a NOT NULL attribute on top of a DEFAULT 0.
  • brobinson 10y ago
    Sounds like this could have also been mitigated by either the SQL server warning about SUM() operating over a nullable column or a "where foo is not null" clause. Your solution is best, though.
  • int_19h 10y ago
    Hoare was talking about null references specifically. Or, to be even more precise, making references nullable by default, and allowing all operations on them in the type system, with U.B. or runtime errors if they actually happen. NULL in SQL is a very different and largely unrelated concept. Though probably a mistake just as bad - not the concept itself even, but the name. Why did they ever think that "NULL" was a smart name to give to an unknown value? For developers, by the time SQL was a thing, "null" was already a fairly established way to describe null pointers/references. For non-developers, whom SQL presumably targeted, "null" just means zero, which is emphatically not the same thing as "unknown". They really should have called it "UNKNOWN" - then its semantics would make sense, and people wouldn't use it to denote a value that is missing (but known).