4 ms·

> First, while SQL allows user-defined types, it doesn't have any concept of a union type. Isn't a union type essentially a de-normalized field? This seems li

> First, while SQL allows user-defined types, it doesn't have any concept of a union type. Isn't a union type essentially a de-normalized field? This seems like attacking arithmetic operators for their lousy character string support. Weren't XML databases (briefly) a (marketing) thing some decades back? One idea might be to have everyone integrate jq[1] into their database engines. My understanding is that one can make the JSON do back flips with jq. Then we can move to complaining about queries that appear to have been written in Klingon instead of boring ol' SQueaL. [1] https://stedolan.github.io/jq/manual/ https://stedolan.github.io/jq/manual/

deleted 5y ago
[deleted]
masklinn 5y ago
> Isn't a union type essentially a de-normalized field? No? You have to denormalize to emulate unions when they're missing. Sum types are a fundamental category of types, that SQL only supports product types is a problem you have to work around.
- smitty1e 5y ago
  See mannykannot's reply. The argument for union types seems to get weak when one asks: how do we index their components? Because there seems little middle ground between needing discrete fields and safely just parking the data as a memo field and deferring the management to the application. Unix win by letting the system utilities specialize. SQL need not be "one language to rule them all".
  - masklinn 5y ago
    > See mannykannot's reply. Their answer clearly, explicitly, assumes a C-style `union`. Despite the essay literally using a proper sum type as example. > The argument for union types seems to get weak when one asks: how do we index their components? With the system creating partial indices under the cover? I fail to see what's complicated about it. > SQL need not be "one language to rule them all". SQL has "won" the relational battle and is literally the only way to query relational databases. Any time SQL is unable to do the job and you have to move that job to application code, you're making the schema less reliable and less of a source of truth, because parts of the schema's information have to be embedded in each application instead. That doesn't seem desirable to me, unless you assert SQL should just be a trivial data storage and retrieval interface, which it has not been… possibly ever, but at the very least since the introduction of window functions.
    - smitty1e 5y ago
      I would contend that there is a minimum set of tools across which an system will span. However, attempts to crunch the system into One True Tool will hit the diminishing returns curve early and hard. Quite to the contrary, many users tend toward Object-Relational Mappers, just to ease the transition between data and logic portions of the application.
mannykannot 5y ago
It is more fundamental than that: a union violates the fundamental tenets of the relational model. In that model, each element of a relation must be a simple value of some type. If you are working with unions and, for whatever reason, want to put unions into a data store, this may seem to be a capricious limitation, but this rule, together with the other principles Codd stipulated, are the basis of the semantic transparency of the relational model, in which each relation expresses an atomic fact about the world of discourse. This, in turn, is the basis of its desirable features, such as its openness to ad-hoc querying, and the applicability of referential integrity constraints. To look at it in more concrete terms, suppose you had an attribute with a union type: the meaning of any particular bit-pattern would be ambiguous - it might depend on the value of a different datum, or, worse, be context-dependent in a more complex way. This is going to make querying more complicated, whether you are using SQL or some replacement for it, and while one or two cases may seem expedient and harmless, these are the sort of accommodations that, as they accumulate, lead to programs becoming hard to understand and brittle. At this point, I am unsure whether it would be acceptable, within the relational model, to have types that are, structurally, a union together with a flag disambiguating it. On the one hand, this would avoid the problem of disambiguation I mentioned above, but if it were implemented in such a way that the flag value is independently queryable and/or settable, that would seem to open a back door to let in all the seems-expedient-but-ends-badly design choices that raw union types would facilitate.

deleted 5y ago

[deleted]

masklinn 5y ago

> Isn't a union type essentially a de-normalized field? No? You have to denormalize to emulate unions when they're missing. Sum types are a fundamental category of types, that SQL only supports product types is a problem you have to work around.

smitty1e 5y ago
See mannykannot's reply. The argument for union types seems to get weak when one asks: how do we index their components? Because there seems little middle ground between needing discrete fields and safely just parking the data as a memo field and deferring the management to the application. Unix win by letting the system utilities specialize. SQL need not be "one language to rule them all".
- masklinn 5y ago
  > See mannykannot's reply. Their answer clearly, explicitly, assumes a C-style `union`. Despite the essay literally using a proper sum type as example. > The argument for union types seems to get weak when one asks: how do we index their components? With the system creating partial indices under the cover? I fail to see what's complicated about it. > SQL need not be "one language to rule them all". SQL has "won" the relational battle and is literally the only way to query relational databases. Any time SQL is unable to do the job and you have to move that job to application code, you're making the schema less reliable and less of a source of truth, because parts of the schema's information have to be embedded in each application instead. That doesn't seem desirable to me, unless you assert SQL should just be a trivial data storage and retrieval interface, which it has not been… possibly ever, but at the very least since the introduction of window functions.
  - smitty1e 5y ago
    I would contend that there is a minimum set of tools across which an system will span. However, attempts to crunch the system into One True Tool will hit the diminishing returns curve early and hard. Quite to the contrary, many users tend toward Object-Relational Mappers, just to ease the transition between data and logic portions of the application.

mannykannot 5y ago

It is more fundamental than that: a union violates the fundamental tenets of the relational model. In that model, each element of a relation must be a simple value of some type. If you are working with unions and, for whatever reason, want to put unions into a data store, this may seem to be a capricious limitation, but this rule, together with the other principles Codd stipulated, are the basis of the semantic transparency of the relational model, in which each relation expresses an atomic fact about the world of discourse. This, in turn, is the basis of its desirable features, such as its openness to ad-hoc querying, and the applicability of referential integrity constraints. To look at it in more concrete terms, suppose you had an attribute with a union type: the meaning of any particular bit-pattern would be ambiguous - it might depend on the value of a different datum, or, worse, be context-dependent in a more complex way. This is going to make querying more complicated, whether you are using SQL or some replacement for it, and while one or two cases may seem expedient and harmless, these are the sort of accommodations that, as they accumulate, lead to programs becoming hard to understand and brittle. At this point, I am unsure whether it would be acceptable, within the relational model, to have types that are, structurally, a union together with a flag disambiguating it. On the one hand, this would avoid the problem of disambiguation I mentioned above, but if it were implemented in such a way that the flag value is independently queryable and/or settable, that would seem to open a back door to let in all the seems-expedient-but-ends-badly design choices that raw union types would facilitate.