15 ms·

But the world just works like that. There are unions everywhere. No matter how it's implemented under the hood, it should really be a first class concept in any

But the world just works like that. There are unions everywhere. No matter how it's implemented under the hood, it should really be a first class concept in any language, including SQL.

thom 5y ago
In most languages where unions are a first class concept, are they not generally warned against?
- bccdee 5y ago
  Not at all. Untagged unions, sure, but no decent modern language has first-class support for untagged unions. First-class tagged unions are a real treat to use.
- OskarS 5y ago
  You’re probably thinking of C/C++ unions, and they do indeed have quite tricky semantics and are difficult to use correctly. What the article and the other commentators are talking about is more properly called a “sum type”, like Rust’s enums, for instance. Those are different things from C unions. In languages with first class support for sum types, they are used everywhere, it’s an incredibly useful concept.
  - thom 5y ago
    Gotcha, ta. That becomes a tricky problem when we're talking about something that's primarily a storage engine though, right?
    - valenterry 5y ago
      Not sure - because in the end, a programming language also has to store this concept in memory. I don't think there is a fundamental difference.
    - paavohtl 5y ago
      Well, sum types are not magic - they must be stored in the memory somehow. Rust's enums tend to use a simple approach: there's a large enough integer tag (usually an 8-bit integer), followed by enough space to store any of the variants. The same exact encoding could be used with a database storage engine too.
- ebingdom 5y ago
  The author uses the term "union", but unfortunately they picked the one wrong term against a long list of correct alternatives: sum type, tagged union, discriminated union, coproduct, disjoint union, variant, algebraic data type, ... "Union" does not imply disjointness (which is the source of many problems), whereas all those other terms do.
  - valenterry 5y ago
    Correct, I just continued to use the term of OP. Sum types r coproducts are what I mean.
    - ebingdom 5y ago
      By the "author", I meant the author of the article. I figured you were just being consistent with them.
NavinF 5y ago
I was talking specifically about the JSON example in the article. Needing to store objects in that manner is just a silly problem to have. Any solution will be slow or ugly or both. You're right, unions are everywhere. Right now a human has to think about each union and how to represent it in a database. It would be really cool if I could store capnp objects like the one below and still get optimal query performance and aesthetics without thinking about it: struct Shape { area @0 :Float64; union { circle :group { radius @1 :Float64; } rectangle :group { width @2 :Float64; height @3 :Float64; } } }
- goto11 5y ago
  You can already do that by having circle and rectangle relations. Union types dos not give you any additional power compared to relations. Your proposal might be more convenient though than creating multiple relations, so we should look into making it just as convenient to create the necessary relations to express this. So lets say your syntax proposal creates multiple relations under the hood - then I'm all aboard! The "everything is a relation" is a really powerful concept in the relational model. Adding composite column types like arrays, unions etc. are still less powerful than just using relations. But people like it because the syntax is more convenient to use.
  - NavinF 5y ago
    Agreed, that would solve my aesthetic concerns and kinda solve OP's XY problem with JSON. But the performance of auto-created tables for every union member would kinda suck if you never take advantage of the relation and still pay the cost. You'd also have to auto-create indexes so that queries that read full objects is as fast (IOPS) as storing BLOBs. I was imagining a future database that takes this a step further and lets you define named parameterized queries next to your tables and chooses the best implementation of unions based on your queries. That's what I meant by "without thinking about it". We already do this for algorithms (query optimizer), and I'd love to see it work with data structures
    - goto11 5y ago
goto11 5y ago
SQL already have first class support for union types in the form of relations. Adding union-type columns would actually be second class compare to this. You would have to add special-case operators and it wouldn't give you more power compared to just using relations. That said, I'm not averse to the idea if someone can provide a realistic use case. The JSON example in the article is misguided though - you should not save the structure of the syntax of a serialization format, you should save the data model which is represented by the syntax.
- wibblewobble123 5y ago
  Relations don’t provide sum types in a first-class way. If you want a list of employees each of which have different roles, and depending on the role you have different fields guaranteed to be available (not null), this is not possible to express directly in SQL. You can express this with sum types in ML, Haskell or Rust. In SQL you would have to split it out into separate relations, like: create table employee(id it) And then employee_boss, employee_dev, employee_sales which each have different sets of non-null fields. You can express a foreign key constraint that these tables must link to an employee, but not the other way around. You can’t guarantee in the limited type system of SQL that every employee row has a corresponding employee_X table, and exactly one, not more. This is trivial in languages with sum types but not in SQL. In this sense they are strictly more powerful. Various triggers can be added to try to enforce this constraint, but that’s a runtime check: better languages tell you when you type check the code.
  - IggleSniggle 5y ago
    Yes! This! Exactly! There’s so much that _can be knowable_ with SQL based on the constraints of the _query_ (not just the relational model) that it should be possible to know the characteristics of the data (and in some cases even the performance characteristics) just from type characteristics.
  - gfody 5y ago
    >You can express a foreign key constraint that these tables must link to an employee, but not the other way around. You can’t guarantee in the limited type system of SQL that every employee row has a corresponding employee_X table, and exactly one, not more. in this case instead of an employee table you would have an employee view that’s the union of the separate tables - engines vary dramatically in what sort of constraints they’ll enforce on the view now but the relational model is fine

But the world just works like that. There are unions everywhere. No matter how it's implemented under the hood, it should really be a first class concept in any language, including SQL.

thom 5y ago

In most languages where unions are a first class concept, are they not generally warned against?

bccdee 5y ago
Not at all. Untagged unions, sure, but no decent modern language has first-class support for untagged unions. First-class tagged unions are a real treat to use.
OskarS 5y ago
You’re probably thinking of C/C++ unions, and they do indeed have quite tricky semantics and are difficult to use correctly. What the article and the other commentators are talking about is more properly called a “sum type”, like Rust’s enums, for instance. Those are different things from C unions. In languages with first class support for sum types, they are used everywhere, it’s an incredibly useful concept.
- thom 5y ago
  Gotcha, ta. That becomes a tricky problem when we're talking about something that's primarily a storage engine though, right?
  - valenterry 5y ago
    Not sure - because in the end, a programming language also has to store this concept in memory. I don't think there is a fundamental difference.
  - paavohtl 5y ago
    Well, sum types are not magic - they must be stored in the memory somehow. Rust's enums tend to use a simple approach: there's a large enough integer tag (usually an 8-bit integer), followed by enough space to store any of the variants. The same exact encoding could be used with a database storage engine too.
ebingdom 5y ago
The author uses the term "union", but unfortunately they picked the one wrong term against a long list of correct alternatives: sum type, tagged union, discriminated union, coproduct, disjoint union, variant, algebraic data type, ... "Union" does not imply disjointness (which is the source of many problems), whereas all those other terms do.
- valenterry 5y ago
  Correct, I just continued to use the term of OP. Sum types r coproducts are what I mean.
  - ebingdom 5y ago
    By the "author", I meant the author of the article. I figured you were just being consistent with them.

NavinF 5y ago

I was talking specifically about the JSON example in the article. Needing to store objects in that manner is just a silly problem to have. Any solution will be slow or ugly or both. You're right, unions are everywhere. Right now a human has to think about each union and how to represent it in a database. It would be really cool if I could store capnp objects like the one below and still get optimal query performance and aesthetics without thinking about it: struct Shape { area @0 :Float64; union { circle :group { radius @1 :Float64; } rectangle :group { width @2 :Float64; height @3 :Float64; } } }

goto11 5y ago
You can already do that by having circle and rectangle relations. Union types dos not give you any additional power compared to relations. Your proposal might be more convenient though than creating multiple relations, so we should look into making it just as convenient to create the necessary relations to express this. So lets say your syntax proposal creates multiple relations under the hood - then I'm all aboard! The "everything is a relation" is a really powerful concept in the relational model. Adding composite column types like arrays, unions etc. are still less powerful than just using relations. But people like it because the syntax is more convenient to use.
- NavinF 5y ago
  Agreed, that would solve my aesthetic concerns and kinda solve OP's XY problem with JSON. But the performance of auto-created tables for every union member would kinda suck if you never take advantage of the relation and still pay the cost. You'd also have to auto-create indexes so that queries that read full objects is as fast (IOPS) as storing BLOBs. I was imagining a future database that takes this a step further and lets you define named parameterized queries next to your tables and chooses the best implementation of unions based on your queries. That's what I meant by "without thinking about it". We already do this for algorithms (query optimizer), and I'd love to see it work with data structures
  - goto11 5y ago

goto11 5y ago

SQL already have first class support for union types in the form of relations. Adding union-type columns would actually be second class compare to this. You would have to add special-case operators and it wouldn't give you more power compared to just using relations. That said, I'm not averse to the idea if someone can provide a realistic use case. The JSON example in the article is misguided though - you should not save the structure of the syntax of a serialization format, you should save the data model which is represented by the syntax.

wibblewobble123 5y ago
Relations don’t provide sum types in a first-class way. If you want a list of employees each of which have different roles, and depending on the role you have different fields guaranteed to be available (not null), this is not possible to express directly in SQL. You can express this with sum types in ML, Haskell or Rust. In SQL you would have to split it out into separate relations, like: create table employee(id it) And then employee_boss, employee_dev, employee_sales which each have different sets of non-null fields. You can express a foreign key constraint that these tables must link to an employee, but not the other way around. You can’t guarantee in the limited type system of SQL that every employee row has a corresponding employee_X table, and exactly one, not more. This is trivial in languages with sum types but not in SQL. In this sense they are strictly more powerful. Various triggers can be added to try to enforce this constraint, but that’s a runtime check: better languages tell you when you type check the code.
- IggleSniggle 5y ago
  Yes! This! Exactly! There’s so much that _can be knowable_ with SQL based on the constraints of the _query_ (not just the relational model) that it should be possible to know the characteristics of the data (and in some cases even the performance characteristics) just from type characteristics.
- gfody 5y ago
  >You can express a foreign key constraint that these tables must link to an employee, but not the other way around. You can’t guarantee in the limited type system of SQL that every employee row has a corresponding employee_X table, and exactly one, not more. in this case instead of an employee table you would have an employee view that’s the union of the separate tables - engines vary dramatically in what sort of constraints they’ll enforce on the view now but the relational model is fine