6 ms·

Especially since its literally untrue. SQL, with the addition of recursive queries (supported by all since the late 90s AND used in an example within this very

by NoToP 4y ago

Especially since its literally untrue. SQL, with the addition of recursive queries (supported by all since the late 90s AND used in an example within this very article), is Turing complete. There's literally no operation on data that can't in principle be expressed as an SQL query (albeit the construction might be painful and tedious).

egeozcan 4y ago
> There's literally no operation on data that can't in principle be expressed as an SQL query That may be technically true (or not, I don't know), but in many cases some complex data manipulation (especially when it's done in multiple passes) practically needs a lot of ram and a programming language to be more time efficient.
branko_d 4y ago
> There's literally no operation on data that can't in principle be expressed as an SQL query (albeit the construction might be painful and tedious). There are some problems though: - Single query can't write to multiple tables. - Single query can't return multiple resultsets. You can write a stored procedure, but this is no longer "an SQL query", strictly speaking. And once you start writing stored procedures, you are no longer using just SQL, but whatever Ada-inspired procedural extension to SQL was implemented by the database vendor. In other words, you are using "a traditional programming language to work with the data".
- feike 4y ago
  A single query can write to multiple tables, using CTE's in PostgreSQL for example. You could compose a SQL query that allows you to map multiple resultsets to 1 resultset, although that feels a bit awkward. WITH a AS ( insert into a (k, v) values ('a', 1.0) returning * ), b AS ( insert into b (k, v) values ('b', 2.0) returning * ) SELECT row_to_json(a) FROM a UNION ALL SELECT row_to_json(b) FROM b; Returns: row_to_json -------------------------- {"a_id":1,"k":"a","v":1} {"b_id":1,"k":"b","v":2} (2 rows)
  - branko_d 4y ago
    Good to know, I wasn't aware PostgreSQL supports this. I'm currently on SQL Server and it doesn't support INSERT as a CTE (and I think most DBMSes out there still don't). It would definitely make my life easier if it did...
    - chrisjc 4y ago
      It's not the only DB or way of doing it either. Snowflake supports multi-table inserts for example: https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table.html https://docs.snowflake.com/en/sql-reference/sql/insert-multi...
  - zasdffaa 4y ago
    That is eye-opening but - if it actually works and I find it hard to believe - is no way ... ah, the insert is a CTE because it produces a value ('returning' I guess). Hmm. This is very odd. Doesn't seem to work in mssql. Well thanks for the can of worms...
    - ltbarcly3 4y ago
      mssql CTE support is very very basic, to the point of being not very useful.
      - zasdffaa 4y ago
        What the hell are you talking about?
        ltbarcly3 4y ago
        You can only select, you can't nest cte, the query planner has no understanding of joins that cross cte boundaries so they are totally unoptimized, you can't use distinct or group by or etcetc. Really the CTE implementation in SqlServer is basically a parser level hack.
5e92cb50239222b 4y ago
For example, here's a raytracer in MySQL statements. https://news.ycombinator.com/item?id=21218144 https://news.ycombinator.com/item?id=21218144 https://www.pouet.net/prod.php?which=83222 https://www.pouet.net/prod.php?which=83222
kthejoker2 4y ago
Do a dynamic pivot (I'll wait.) But in all seriousness, DataFrame centric operations are a superset of SQL (you can always do a df.sql("...") if you want to ) and have a lot more efficient implementation of both OLTP/ORM requirements and OLAP/DS/BI requirements. They also encourage composability, modularization, reuse, unit and data testing ... So it's ironic I feel like SQL's replacement is another declarative language - its original inspiration - plain English. Just a natural language transpiler (like Palantir's Ontology plus Looker's Malloy (reverse disclaimer: I do not work for or enjoy either of these products but these underlying concepts are correct)) with some fancy domain heuristics and light AI (I suspect a Pareto like model that supports 80% of use cases only needs a semantic graph with a few thousand nodes and vertices)

by NoToP 4y ago

egeozcan 4y ago

> There's literally no operation on data that can't in principle be expressed as an SQL query That may be technically true (or not, I don't know), but in many cases some complex data manipulation (especially when it's done in multiple passes) practically needs a lot of ram and a programming language to be more time efficient.

branko_d 4y ago

> There's literally no operation on data that can't in principle be expressed as an SQL query (albeit the construction might be painful and tedious). There are some problems though: - Single query can't write to multiple tables. - Single query can't return multiple resultsets. You can write a stored procedure, but this is no longer "an SQL query", strictly speaking. And once you start writing stored procedures, you are no longer using just SQL, but whatever Ada-inspired procedural extension to SQL was implemented by the database vendor. In other words, you are using "a traditional programming language to work with the data".

feike 4y ago
A single query can write to multiple tables, using CTE's in PostgreSQL for example. You could compose a SQL query that allows you to map multiple resultsets to 1 resultset, although that feels a bit awkward. WITH a AS ( insert into a (k, v) values ('a', 1.0) returning * ), b AS ( insert into b (k, v) values ('b', 2.0) returning * ) SELECT row_to_json(a) FROM a UNION ALL SELECT row_to_json(b) FROM b; Returns: row_to_json -------------------------- {"a_id":1,"k":"a","v":1} {"b_id":1,"k":"b","v":2} (2 rows)
- branko_d 4y ago
  Good to know, I wasn't aware PostgreSQL supports this. I'm currently on SQL Server and it doesn't support INSERT as a CTE (and I think most DBMSes out there still don't). It would definitely make my life easier if it did...
  - chrisjc 4y ago
    It's not the only DB or way of doing it either. Snowflake supports multi-table inserts for example: https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table.html https://docs.snowflake.com/en/sql-reference/sql/insert-multi...
- zasdffaa 4y ago
  That is eye-opening but - if it actually works and I find it hard to believe - is no way ... ah, the insert is a CTE because it produces a value ('returning' I guess). Hmm. This is very odd. Doesn't seem to work in mssql. Well thanks for the can of worms...
  - ltbarcly3 4y ago
    mssql CTE support is very very basic, to the point of being not very useful.
    - zasdffaa 4y ago
      What the hell are you talking about?
      - ltbarcly3 4y ago
        You can only select, you can't nest cte, the query planner has no understanding of joins that cross cte boundaries so they are totally unoptimized, you can't use distinct or group by or etcetc. Really the CTE implementation in SqlServer is basically a parser level hack.

5e92cb50239222b 4y ago

For example, here's a raytracer in MySQL statements. https://news.ycombinator.com/item?id=21218144 https://news.ycombinator.com/item?id=21218144 https://www.pouet.net/prod.php?which=83222 https://www.pouet.net/prod.php?which=83222

kthejoker2 4y ago

Do a dynamic pivot (I'll wait.) But in all seriousness, DataFrame centric operations are a superset of SQL (you can always do a df.sql("...") if you want to ) and have a lot more efficient implementation of both OLTP/ORM requirements and OLAP/DS/BI requirements. They also encourage composability, modularization, reuse, unit and data testing ... So it's ironic I feel like SQL's replacement is another declarative language - its original inspiration - plain English. Just a natural language transpiler (like Palantir's Ontology plus Looker's Malloy (reverse disclaimer: I do not work for or enjoy either of these products but these underlying concepts are correct)) with some fancy domain heuristics and light AI (I suspect a Pareto like model that supports 80% of use cases only needs a semantic graph with a few thousand nodes and vertices)