20 ms·

SQL is good, but it shows its age. Today somebody should come up with something statically typed and more functional (meaning using lambda calculus as a startin

by js8 10y ago

SQL is good, but it shows its age. Today somebody should come up with something statically typed and more functional (meaning using lambda calculus as a starting point). The biggest pain points of SQL (IMHO) are: - lack of statically typing guarantees (for example, no guarantee that a certain table has certain column) - bad capability to abstract over parts of the data model (for example, queries have to specify the table that they query) Both of these can be resolved with use of good enough functional language. There are projects like that in the FP/Haskell community (e.g. Ermine), but it's fragmented.

elmigranto 10y ago
> statically typing guarantees int will be an int. You can't store string there. What else do you need? Something along the lines of SQL's `check` or Postgres's domains? > queries have to specify the table that they query How do you imagine not doing that, something like "pull some things and do stuff with it"? I think we are quite far from that kind of reality. > certain table has certain column I don't get it. It either has, or it doesn't. In one case you get a value, in other one — SQL error. If that's so vital, pull up some schema information and check before running an app.
- deathanatos 10y ago
  > What else do you need? Me personally: sum types. (Some languages call these tagged unions. Rust calls it an enum, but note that enum here does not mean "an integer under the hood") We have the case where we have an entity who, as part of its primary key, has a value that is either a valid integer or a sort of "Empty" value. It's part of the key, so I can't use a nullable integer to describe this column, as doing so would prevent me guaranteeing uniqueness (the "empty" value is only valid once, unlike a NULL in a unique constraint). I can use (bool, int) column pair and some check constraints, but it leaves the integer exposed to poorly formed queries, such as SUM(the_integer_part). If I can dedicate a "special value" in the integer, I can use just the integer; that's similarly brittle. (SUM — and most other arithmetic — is valid, but ONLY if the column isn't the "Empty" value.) It'd be nice to be able to model the table as something like, CREATE TABLE ( ... count_or_empty union { int | empty } NOT NULL, ... PRIMARY KEY (..., count_or_empty, ...) ); If the DB supported sum types, the type of count_or_empty here could deliberately be a union, which would not be compatible with SUM. You'd need to "unwrap" the union prior to doing such operations on it (check if it's Empty or not) and then do the appropriate thing: trying to blindly SUM on it would be a static error.
  - elmigranto 10y ago
    Interesting. Well, you could write a custom type for Postgres as a C extension with all those collations, indexing and other requirements, I know that's not quite SQL or out-of-the-box kind of solution, but still, there are options if you really need it.
  - infogulch 10y ago
    EDIT: SEE REPLY How about (SQL Server): CREATE TABLE #P ( name VARCHAR(100), count_empty BIT NOT NULL, count INT NULL, count_key AS ISNULL(count,0), CHECK ((count_empty = 0 AND count IS NOT NULL) OR (count_empty = 1 AND count IS NULL)), PRIMARY KEY (name, count_empty, count_key) ); I think this should give the semantics you're looking for (if not the ergonomics). COUNT,SUM,AVG etc should work correctly on the "count" column (btw, I would consider using a different name ;P), and the check+primary key should enforce the constraints. You might add a before update trigger to set count to NULL when count_empty = 1, and hiding the actual table behind a view to hide the internals of the count_key column.
    - infogulch 10y ago
      Whoa I was way overcomplicating this. This is all you need, semantics AND ergonomics included: CREATE TABLE P ( name VARCHAR(100), count INT NULL, count_empty AS CASE WHEN count IS NULL THEN 1 ELSE 0 END, count_key AS ISNULL(count,0), PRIMARY KEY (name, count_empty, count_key) ); No trigger or updating two fields needed at all. Just update "count". If you insert NULL twice it will yell at you.
techno_modus 10y ago
> SQL is good, but it shows its age "The relational model is dead, SQL is dead, and I don’t feel so good myself". [PDF] - https://sigmodrecord.org/publications/sigmodRecord/1306/pdfs/11.reports.atzeni.pdf https://sigmodrecord.org/publications/sigmodRecord/1306/pdfs... > Both of these can be resolved with used of good enough functional language Yes, functional aspects is exactly what is missing in RM in general and SQL in particular. Yet, I do not think this can be easily added - the whole paradigm has to be changed.
laumars 10y ago
You might need to explain yourself a little more because I'm confused by your examples: > "- lack of statically typing guarantees (for example, no guarantee that a certain table has certain column)" I would have described SQL as statically typed because columns are given a type when created. The issue you described seems more akin to you wanting auto-complete features for SQL (which do exist in some DB management tools) because while you can specify in code a column that doesn't exist, it will fail to compile on the RDBMS in much the same way that you can still write code that fails to compile in imperative and functional languages. > "- bad capability to abstract over parts of the data model (for example, queries have to specify the table that they query)" You don't need to specify the table name for each column if you're only querying one table. The issue arises with multiple tables. I see this a bit like having to specify the namespace in Go packages or class name in Java where you don't want to import every property and method into the global namespace. I'm not sure what the functional solution you envisage would be but there are workarounds in imperative languages such as the `using namespace` declaration in C++ and the `with` block in VB (I can't believe I just referenced VB in a serious discussion!). Generally though I've found the pain of referencing table names in SQL to be somewhat mitigated through aliasing them via their acronyms. eg select * from people p, borrowed_books bb where p.name = "laumars" SQL definitely isn't a pretty language though so I am very interested in your thoughts for how a more intuitive, functional-inspired, query language might read.
- marcosdumay 10y ago
  > I would have described SQL as statically typed because columns are given a type when created. The biggest problem is that SQL has no well defined "compile time". For all practical purposes, it's always an interpreted language. I can't check my code against the production database without running it. > You don't need to specify the table name for each column if you're only querying one table. I do think the GP was about doing stuff like that: SELECT * FROM function_that_returns_table();
  - yawaramin 10y ago
    You can check your code without running it on databases which support it, e.g. Postgres https://www.postgresql.org/docs/current/static/sql-prepare.html https://www.postgresql.org/docs/current/static/sql-prepare.h... db=# prepare t as select 1 from a; ERROR: relation "a" does not exist at character 28 STATEMENT: prepare t as select 1 from a; ERROR: relation "a" does not exist LINE 1: prepare t as select 1 from a; ^ Time: 0.684 ms
marcosdumay 10y ago
SQL is a declarative reactive language. There's something to gain by adding functions as first-class objects, but I don't think rethinking it from functional foundations would help. Lambda calculus is focused on data processing, and a query language must be focused on data searching, those are completely different applications. But I do completely agree with the pain points you pointed there. I'd go further on the first point, client software should have an easy way to check its guarantees at connection time too.
- js8 10y ago
  > There's something to gain by adding functions as first-class objects, but I don't think rethinking it from functional foundations would help. So what other approach to designing a declarative language do you want to take? I propose total functional language because it's extremely simple and systematic approach. You add your primitive types, primitive functions, primitive type operators (like algebraic types) and voila - you are pretty much set. That way, you get lot of reusability which would be hard to do in an adhoc designed declarative language. And a very uniform syntax as a nice bonus. My question is not rhetorical. Building declarative languages based on typed lambda calculus is very well understood.
metaphorm 10y ago
can you elaborate? you haven't really explained these ideas in a way that helps me understand them but it sounds interesting. what do you mean by static typing guarantees? tables have a schema already (static type declarations). table schemas are essentially all objects of the same type though (a collection of column types, and the possible values of those types are enumerated). are you referring to a meta-data level of type checking? like "schema of type Foo always contains a column named FOO with type varchar" or something like that? what do you mean by "queries have to specify the table that they query"? do you mean that there ought to be templated or generic queries? I can't see a way out of specifying which data sets you want to query on though. I'm hoping you can use more detailed language to clarify your intent here.

by js8 10y ago

elmigranto 10y ago

> statically typing guarantees int will be an int. You can't store string there. What else do you need? Something along the lines of SQL's `check` or Postgres's domains? > queries have to specify the table that they query How do you imagine not doing that, something like "pull some things and do stuff with it"? I think we are quite far from that kind of reality. > certain table has certain column I don't get it. It either has, or it doesn't. In one case you get a value, in other one — SQL error. If that's so vital, pull up some schema information and check before running an app.

deathanatos 10y ago
> What else do you need? Me personally: sum types. (Some languages call these tagged unions. Rust calls it an enum, but note that enum here does not mean "an integer under the hood") We have the case where we have an entity who, as part of its primary key, has a value that is either a valid integer or a sort of "Empty" value. It's part of the key, so I can't use a nullable integer to describe this column, as doing so would prevent me guaranteeing uniqueness (the "empty" value is only valid once, unlike a NULL in a unique constraint). I can use (bool, int) column pair and some check constraints, but it leaves the integer exposed to poorly formed queries, such as SUM(the_integer_part). If I can dedicate a "special value" in the integer, I can use just the integer; that's similarly brittle. (SUM — and most other arithmetic — is valid, but ONLY if the column isn't the "Empty" value.) It'd be nice to be able to model the table as something like, CREATE TABLE ( ... count_or_empty union { int | empty } NOT NULL, ... PRIMARY KEY (..., count_or_empty, ...) ); If the DB supported sum types, the type of count_or_empty here could deliberately be a union, which would not be compatible with SUM. You'd need to "unwrap" the union prior to doing such operations on it (check if it's Empty or not) and then do the appropriate thing: trying to blindly SUM on it would be a static error.
- elmigranto 10y ago
  Interesting. Well, you could write a custom type for Postgres as a C extension with all those collations, indexing and other requirements, I know that's not quite SQL or out-of-the-box kind of solution, but still, there are options if you really need it.
- infogulch 10y ago
  EDIT: SEE REPLY How about (SQL Server): CREATE TABLE #P ( name VARCHAR(100), count_empty BIT NOT NULL, count INT NULL, count_key AS ISNULL(count,0), CHECK ((count_empty = 0 AND count IS NOT NULL) OR (count_empty = 1 AND count IS NULL)), PRIMARY KEY (name, count_empty, count_key) ); I think this should give the semantics you're looking for (if not the ergonomics). COUNT,SUM,AVG etc should work correctly on the "count" column (btw, I would consider using a different name ;P), and the check+primary key should enforce the constraints. You might add a before update trigger to set count to NULL when count_empty = 1, and hiding the actual table behind a view to hide the internals of the count_key column.
  - infogulch 10y ago
    Whoa I was way overcomplicating this. This is all you need, semantics AND ergonomics included: CREATE TABLE P ( name VARCHAR(100), count INT NULL, count_empty AS CASE WHEN count IS NULL THEN 1 ELSE 0 END, count_key AS ISNULL(count,0), PRIMARY KEY (name, count_empty, count_key) ); No trigger or updating two fields needed at all. Just update "count". If you insert NULL twice it will yell at you.

techno_modus 10y ago

> SQL is good, but it shows its age "The relational model is dead, SQL is dead, and I don’t feel so good myself". [PDF] - https://sigmodrecord.org/publications/sigmodRecord/1306/pdfs/11.reports.atzeni.pdf https://sigmodrecord.org/publications/sigmodRecord/1306/pdfs... > Both of these can be resolved with used of good enough functional language Yes, functional aspects is exactly what is missing in RM in general and SQL in particular. Yet, I do not think this can be easily added - the whole paradigm has to be changed.

laumars 10y ago

You might need to explain yourself a little more because I'm confused by your examples: > "- lack of statically typing guarantees (for example, no guarantee that a certain table has certain column)" I would have described SQL as statically typed because columns are given a type when created. The issue you described seems more akin to you wanting auto-complete features for SQL (which do exist in some DB management tools) because while you can specify in code a column that doesn't exist, it will fail to compile on the RDBMS in much the same way that you can still write code that fails to compile in imperative and functional languages. > "- bad capability to abstract over parts of the data model (for example, queries have to specify the table that they query)" You don't need to specify the table name for each column if you're only querying one table. The issue arises with multiple tables. I see this a bit like having to specify the namespace in Go packages or class name in Java where you don't want to import every property and method into the global namespace. I'm not sure what the functional solution you envisage would be but there are workarounds in imperative languages such as the `using namespace` declaration in C++ and the `with` block in VB (I can't believe I just referenced VB in a serious discussion!). Generally though I've found the pain of referencing table names in SQL to be somewhat mitigated through aliasing them via their acronyms. eg select * from people p, borrowed_books bb where p.name = "laumars" SQL definitely isn't a pretty language though so I am very interested in your thoughts for how a more intuitive, functional-inspired, query language might read.

marcosdumay 10y ago
> I would have described SQL as statically typed because columns are given a type when created. The biggest problem is that SQL has no well defined "compile time". For all practical purposes, it's always an interpreted language. I can't check my code against the production database without running it. > You don't need to specify the table name for each column if you're only querying one table. I do think the GP was about doing stuff like that: SELECT * FROM function_that_returns_table();
- yawaramin 10y ago
  You can check your code without running it on databases which support it, e.g. Postgres https://www.postgresql.org/docs/current/static/sql-prepare.html https://www.postgresql.org/docs/current/static/sql-prepare.h... db=# prepare t as select 1 from a; ERROR: relation "a" does not exist at character 28 STATEMENT: prepare t as select 1 from a; ERROR: relation "a" does not exist LINE 1: prepare t as select 1 from a; ^ Time: 0.684 ms

marcosdumay 10y ago

SQL is a declarative reactive language. There's something to gain by adding functions as first-class objects, but I don't think rethinking it from functional foundations would help. Lambda calculus is focused on data processing, and a query language must be focused on data searching, those are completely different applications. But I do completely agree with the pain points you pointed there. I'd go further on the first point, client software should have an easy way to check its guarantees at connection time too.

js8 10y ago
> There's something to gain by adding functions as first-class objects, but I don't think rethinking it from functional foundations would help. So what other approach to designing a declarative language do you want to take? I propose total functional language because it's extremely simple and systematic approach. You add your primitive types, primitive functions, primitive type operators (like algebraic types) and voila - you are pretty much set. That way, you get lot of reusability which would be hard to do in an adhoc designed declarative language. And a very uniform syntax as a nice bonus. My question is not rhetorical. Building declarative languages based on typed lambda calculus is very well understood.

metaphorm 10y ago

can you elaborate? you haven't really explained these ideas in a way that helps me understand them but it sounds interesting. what do you mean by static typing guarantees? tables have a schema already (static type declarations). table schemas are essentially all objects of the same type though (a collection of column types, and the possible values of those types are enumerated). are you referring to a meta-data level of type checking? like "schema of type Foo always contains a column named FOO with type varchar" or something like that? what do you mean by "queries have to specify the table that they query"? do you mean that there ought to be templated or generic queries? I can't see a way out of specifying which data sets you want to query on though. I'm hoping you can use more detailed language to clarify your intent here.