6 ms·

Yup. To be blunt: SQL is a flawed old language that should've been replaced decades ago, but every time somebody tries to replace it they throw the baby out wi

• mrbungie 5y ago
  It may be flawed, it may be old, but it clearly continues to be sufficiently expressive and useful. Every time someone tries to replace it, it eventually comes back again. Just look at the recent NoSQL and Big Data craze, and then how we're today with a miriad of SQL engines.
  • qsort 5y ago
    I think GP is correct when he says "every time somebody tries to replace it they throw the baby out with the bathwater and throw out the relational theory too". However, I would add that SQL is a very strong local optimum: a "good" language that keeps the good parts would be insufficiently different from actual SQL to convince anybody to switch. Also people forget that querying is only half of the equation, there's DDL too.
    • setr 5y ago
      DDL is a bit curious, because it’s less a programming language and more of a set of configuration and operational commands. It just happens to look similar to DQL, but there’s nothing really pushing that beyond the fact that DDL can rely on DQL as a subexpression (eg create view as …). You could probably say the relationship between DQL and DDL is largely aesthetic The only reason I can think of where you often can’t strictly treat DQL and DDL as totally different languages is the just from the total lack of interest in consistency in SQL language design generally — eg update and delete steal parts of DML syntax haphazardly and differently; the syntax for update/delete data referring to another table UPDATE table WHERE … JOIN … vs DELETE FROM table USING … The lack of complexity in DDL generally (its very wide, but not very deep; there’s not much meaningful composition to be discussed) and its lack of a real relationship to DQL is probably why no one discusses it much
  • deleted 5y ago
    [deleted]
• civilized 5y ago
  > 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. Nah. One of the most common uses of null is to communicate missing values in the sense of "this entity has this attribute but we don't know what its value is". This is a fundamental feature needed in data management. So say you've got data on two balls A and B. You don't know what the color of either ball is. Is color(A) = color(B)? What's the best answer to this question? Obviously it's "we don't know", and the value we use to communicate "we don't know" is null. SQL nulls aren't broken. They're just a third option outside of True/False that needs its own handling, because reality is complicated. PS: Contrary to popular opinion in certain coding circles, floating point arithmetic isn't broken either. It's a beautiful, delicate tool that works the way it does because it must in order to support its use cases (e.g. numerical linear algebra and representing continuous physical quantities). If it doesn't work exactly the way you want, you may need a couple guardrails or a different system like fixed point arithmetic.
  • wyager 5y ago
    > One of the most common uses of null is to communicate missing values in the sense of "this entity has this attribute but we don't know what its value is" The correct way to represent this, which has been widely used for several decades now, is sum types. If your value might be unknown, you need to explicitly represent this in the type of your value (e.g. Optional<Foo>) instead of relying on a hack that allows subverting the type system.
    • civilized 5y ago
      I agree, and more sophisticated analytics data structures, like R's data frame, do have "typed nulls" (except in R they're called NAs). I don't know, and would love to know, why databases have been so slow to adopt this.
    • wongarsu 5y ago
      You could argue any SQL value not marked as NOT NULL is an Optional<Foo>, with NULL being the None value. It's not really subverting the type system, it's just an unconventional choice.
• qsort 5y ago
  > 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 like an attempt to throw the baby out. 3-valued logic anyone?
  • 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.
• barrkel 5y ago
  I'd hang on to NULLs, but I'd propagate nullability in the type system. That could mean disallowing anything but non-nullable boolean as filter predicates, forcing the use of COALESCE for clarity and explictness.
  • wyager 5y ago
    By far the best option would be to add support for sum types and pattern matching. Besides being tremendously useful in its own right, it would also eliminate the need for nulls.
    • barrkel 5y ago
      I don't think that's right; absent data is really common and a desirable feature, and ergonomically handling it warrants built-in support. Outer joins inherently require a concept of absence of data, so you'd need a built-in Optional type which would be nullability in all but name. And you'd want lifting of operators on the inner type to the optional type for ergonomic reasons; doing a pattern match for every selection of bar.* where bar is left-joined would be extremely painful. I think you'd end up where you started on the null front. Sum types generally, for columns etc., maybe (pun not intended), but I'm skeptical about complex types in a relational language - structure ought to be represented as relations where possible so that everything else composes.
      • wyager 5y ago
        It would be ergonomic - you'd just use a variant of the equals operator specialized to Option<A> that behaved in the way you wanted.
• gpderetta 5y ago
  > Any language where "x = x" commonly returns an object that is treated as falsey is broken. Err, that will include any language that implements IEEE floats.
  • Pxtl 5y ago
    "Commonly" is the operative word here. I'd say that null values are far more common than NaN.
• dragonwriter 5y ago
  > 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. So, any language using IEEE 754 floats is broken?