5 ms·

> SQL ANSI nulls alone are a good-enough reason to toss it. Any language where "x = x" commonly returns an object that is treated as falsey is broken. Seems li

• Pxtl 5y ago
  If 3-value logic is such a good idea, why doesn't any modern general-purpose language use it?
  • ttfkam 5y ago
    Optional<Boolean> As found in a few general purpose languages. Hell, JavaScript has true, false, null, and undefined. 4-value logic! The real world upon which we model our code has a "true", "false", and "can't tell yet; not enough info". As inconvenient as it feels psychologically, you can't deny the fundamental reality of the third, "can't tell yet", state.
    • Pxtl 5y ago
      Yes, those languages have nulls, but the nulls don't display the strange operator semantics of SQL ANSI nulls. In JavaScript, null == null yields true. "Test" == null yields false. In SQL, those expressions yield null. SQL is afaik unique in its belief that algebra involving nulls should yield nulls instead of boolean values. That's what's meant by "three value logic", and that's what I find problematic. It violates several mathematical rules, like the reflexive property of equality.
      • Taywee 5y ago
        SQL doesn't have this "belief". SQL has the behavior that is most useful for querying and joining when some columns have unset values, or foreign key relations that are unset. I understand your position, but your idealism would be strictly worse in real life. Can you imagine if an optional foreign key relation immediately made all joins fail because one was unset? Or if a join between two optional values made all NULL values join together? The SQL NULL behavior also allows UNIQUE nullable columns to have more than one instance that is NULL, so you can have uniqueness over a column that may also be unset. If you're a purist or an idealist, SQL NULL might be a mess, but pragmatically, it's the only behavior that is sane for SQL as-is.
      • dragonwriter 5y ago
        > SQL is afaik unique in its belief that algebra involving nulls should yield nulls instead of boolean values. Really? The basic operations of boolean algebra or AND, OR, and NOT. In JavaScript, what is: 1. null && true 2. false || null 3. !null Well, one of them produces a boolean result, but... Actually, JS has basically an infinite valued logic, because of its truthiness/falsiness dichotomy and the rules around how the “boolean” operators preserve truthy or falsy inputs; only ! of the core boolean operators always returns a boolean which is why !! is equivalent to Boolean().
        • Pxtl 5y ago
          Right, but js treats all values as either truthy or falsey. Only SQL does this weird 3rd-way approach where values can be neither true nor false (except in a WHERE or ON clause where NULL is treated as false). Like, this is a language where "X OR NOT X" is not true if X = null JS, for all its wonky truthiness rules, still satisfies the basic premise that "X || !X" yields something truthy.
          • dragonwriter 5y ago
            SQL NULL and IEEE 754 NaN are very similar in weirdness of how they propagate, for very similar reasons (they are both hacks for encoding “not a value in the domain” in a value otherwise restricted to the domain; they aren't actual values but information about the absence of an acceptable value.) Most other languages treat NULL as a distinct-but-normal value. IMO this isn't better or worse than SQL, or rather, the SQL approach is better for querying in SQL, whereas the approach common in general purpose programming languages is better more generally, but forces more special case handling.
    • dragonwriter 5y ago
      > Hell, JavaScript has true, false, null, and undefined. 4-value logic! You have vastly underestimated the number of values in JS’s logic.
  • Taywee 5y ago
    Most modern languages have option types.
  • dragonwriter 5y ago
    > If 3-value logic is such a good idea, why doesn't any modern general-purpose language use it? Most dynamically typed general-purpose languages use a large-number-of-values logic, they just use a different formulation than SQLs 3VL. Particularly, nulls tend to be equal to each other, and only propagate from basic boolean ops where false would, not whenever they are an input. (Though IEEE 754 NaNs, which most use and are another flavor of null-like value, are not equal to each other, and, in the case of quiet NaNs, propagate a lot like SQL nulls through arithmetic operations [though not consistently through other language features].)