4 ms·

Why does it rule out 'more sophisticated and elegant behaviours'?

cmrdporcupine 4y ago
If your language has to interoperate with data in the SQL database, it right away has to deal with SQL's limitations and oddities: 1) SQL's type model has no concept of nested relations or rich datatypes; it's limited to basic scalar types <tho there are vendor additions to this>, so implementing e.g. Date's concept of "relation valued attributes" <essentially tables-in-tables> for example would be impractical 2) SQL's storage model doesn't map 1:1 with e.g. a relational model because SQL tables are bags instead of sets (duplicate rows are possible). This has a couple downsides. a) whatever more expressive and relational algebraic language you built would have to inherit that model and and have some way of dealing with bags instead of proper relational ranges <which are sets> and b) the presence of the possibility of dupes actually makes some query planner optimizations impossible to perform. <CJ Date goes into more depth explaining this, I don't have room for it here> 3) Likewise similar problems with SQL NULLs. NULLs are not in the "pure" relational model, so your language can't be pure. NULLs mess with the type system, and you end up inheriting a bunch of the weird casting/identity/comparison issues that come with NULLS. And NULLs, again mess with the query planner. Finally, the execution model of SQL is always against a DBMS table or view. So in-memory relations, etc. look difficult to implement.
- zasdffaa 4y ago
  1) yeah, probably. 2) Supose you're writing sqlng, compiling it to sql. sqlng simply mandates a primary key on everything. Not a problem. 3) nulls can be a horror but the alternatives aren't pretty. Removing nulls means tables have to be broken up, see https://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without.... I believe there are better ways to handle this issue. (final line) Makes no sense, a tab;e doesn't have to be disk-resident. Once read, with sufficient memory, the file sits in ram cache.
- cryptonector 4y ago
  Thanks for listing these. It's very useful. (1) is a function of what SQL you might transpile to. Any SQL w/ rich types (like PG) or w/ JSON support (most others) will be able to meet (1) with varying levels of hackiness. (2) even if some SQL RDBMS allows you to have tables w/o primary keys, the thing transpiling to SQL wouldn't be obligated to have tables w/o primary keys. Some SQL RDBMSes do not allow tables to lack PKs (e.g., SQLite3 doesn't), but even this wouldn't be a problem -if one wanted tables-with-dups- because one could use hidden row IDs to make up for that. (3) just as with (2), the new thing transpiling to SQL wouldn't have to allow NULLs, and could generate SQL that uses `IS [NOT] NULL` / `IS [NOT] DISTINCT FROM NULL` to obtain the desired semantics if the new thing kept any notion of NULL. (4) is not a semantics issue, and anyways, some SQL RDBMSes let you implement tables yourself, and for the others there's CTEs and TEMP tables. Of these only (1) is a problem if you want to target pure standard SQL, though it depends on which SQL standard you target. We're definitely not talking about transpiling to 1980s SQL! So, while there might some semantic impedance mismatches for a transpiling approach, and the choice of RDBMS to transpile to matters, on the whole I'm unconvinced that there are such problems that are very serious. My own imagination is failing me in my attempts to come with such semantic impedance mismatches.
  - zasdffaa 4y ago
    Interesting. Ok > (3) just as with (2), the new thing transpiling to SQL wouldn't have to allow NULLs, and could generate SQL that uses `IS [NOT] NULL` / `IS [NOT] DISTINCT FROM NULL` to obtain the desired semantics if the new thing kept any notion of NULL. You may disallow nulls stored, but anything like a non-inner join will produce nulls and then you're almost back to where you started. > (4) is not a semantics issue, and anyways, [where 4 is "Finally, the execution model of SQL is always against a DBMS table or view. So in-memory relations, etc. look difficult to implement"]. Spot on and great catch. I missed that. > I'm unconvinced that there are such problems that are very serious Let me help you - simple sqlNG commands could transpile into more much complex normal sql target statements, breaking the optimiser. That's a very real risk. Good post though
    - cryptonector 4y ago
- ErwinSmout 4y ago
  SQL (the language that is, as defined by ISO) has COLLECT and UNNEST operators which come reasonably close (not 100% of course) to "Date's concept of relation-valued attributes". Moreover, I'm told those two operators are supported by at least one of the three big dogs.

If your language has to interoperate with data in the SQL database, it right away has to deal with SQL's limitations and oddities: 1) SQL's type model has no concept of nested relations or rich datatypes; it's limited to basic scalar types <tho there are vendor additions to this>, so implementing e.g. Date's concept of "relation valued attributes" <essentially tables-in-tables> for example would be impractical 2) SQL's storage model doesn't map 1:1 with e.g. a relational model because SQL tables are bags instead of sets (duplicate rows are possible). This has a couple downsides. a) whatever more expressive and relational algebraic language you built would have to inherit that model and and have some way of dealing with bags instead of proper relational ranges <which are sets> and b) the presence of the possibility of dupes actually makes some query planner optimizations impossible to perform. <CJ Date goes into more depth explaining this, I don't have room for it here> 3) Likewise similar problems with SQL NULLs. NULLs are not in the "pure" relational model, so your language can't be pure. NULLs mess with the type system, and you end up inheriting a bunch of the weird casting/identity/comparison issues that come with NULLS. And NULLs, again mess with the query planner. Finally, the execution model of SQL is always against a DBMS table or view. So in-memory relations, etc. look difficult to implement.

zasdffaa 4y ago
1) yeah, probably. 2) Supose you're writing sqlng, compiling it to sql. sqlng simply mandates a primary key on everything. Not a problem. 3) nulls can be a horror but the alternatives aren't pretty. Removing nulls means tables have to be broken up, see https://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without.... I believe there are better ways to handle this issue. (final line) Makes no sense, a tab;e doesn't have to be disk-resident. Once read, with sufficient memory, the file sits in ram cache.
cryptonector 4y ago
Thanks for listing these. It's very useful. (1) is a function of what SQL you might transpile to. Any SQL w/ rich types (like PG) or w/ JSON support (most others) will be able to meet (1) with varying levels of hackiness. (2) even if some SQL RDBMS allows you to have tables w/o primary keys, the thing transpiling to SQL wouldn't be obligated to have tables w/o primary keys. Some SQL RDBMSes do not allow tables to lack PKs (e.g., SQLite3 doesn't), but even this wouldn't be a problem -if one wanted tables-with-dups- because one could use hidden row IDs to make up for that. (3) just as with (2), the new thing transpiling to SQL wouldn't have to allow NULLs, and could generate SQL that uses `IS [NOT] NULL` / `IS [NOT] DISTINCT FROM NULL` to obtain the desired semantics if the new thing kept any notion of NULL. (4) is not a semantics issue, and anyways, some SQL RDBMSes let you implement tables yourself, and for the others there's CTEs and TEMP tables. Of these only (1) is a problem if you want to target pure standard SQL, though it depends on which SQL standard you target. We're definitely not talking about transpiling to 1980s SQL! So, while there might some semantic impedance mismatches for a transpiling approach, and the choice of RDBMS to transpile to matters, on the whole I'm unconvinced that there are such problems that are very serious. My own imagination is failing me in my attempts to come with such semantic impedance mismatches.
- zasdffaa 4y ago
  Interesting. Ok > (3) just as with (2), the new thing transpiling to SQL wouldn't have to allow NULLs, and could generate SQL that uses `IS [NOT] NULL` / `IS [NOT] DISTINCT FROM NULL` to obtain the desired semantics if the new thing kept any notion of NULL. You may disallow nulls stored, but anything like a non-inner join will produce nulls and then you're almost back to where you started. > (4) is not a semantics issue, and anyways, [where 4 is "Finally, the execution model of SQL is always against a DBMS table or view. So in-memory relations, etc. look difficult to implement"]. Spot on and great catch. I missed that. > I'm unconvinced that there are such problems that are very serious Let me help you - simple sqlNG commands could transpile into more much complex normal sql target statements, breaking the optimiser. That's a very real risk. Good post though
  - cryptonector 4y ago
ErwinSmout 4y ago
SQL (the language that is, as defined by ISO) has COLLECT and UNNEST operators which come reasonably close (not 100% of course) to "Date's concept of relation-valued attributes". Moreover, I'm told those two operators are supported by at least one of the three big dogs.