Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A Critique of SQL, 40 Years Later

carlineng.com

Post reply on HN
Print view View original

221–230 of 260 posts

Re: A Critique of SQL, 40 Years Later

#221

Post by [deleted] »

tremon wrote: post #133
emaginniss wrote: post #35

Earlier quoted context omitted.

Everyone else is mentioning it from an IDE perspective, but let's also think about logically from a language perspective. When you start a FROM clause and add some JOINs, a few WHERE conditions and maybe GROUP BY, you are building a virtual view of a series of tables, columns, and aggregations. You could even define this data set as an ephemeral table. What you do with that data set afterwards might vary depending on…

You could even define this data set as an ephemeral table Exactly! This is where SQL hurts me the most: not being able to store (partial) query expressions in variables for later reuse. The only way to do this is by creating explicit views (requires DDL permissions) or executing the partial query into a temporary table (which is woefully inefficient for obvious reasons).

[deleted]

Re: A Critique of SQL, 40 Years Later

#222

Post by [deleted] »

chrsig wrote: post #25
taeric wrote: post #18

Earlier quoted context omitted.

You could improve type ahead by just pouring effort into the IDE. Especially with the amount of resources we have nowadays. Easy enough to have basic typeahead on basically all possible columns when writing the select, and then you could use the columns as a filter on the tables during auto complete. In general, this isn't done. But I don't see any technical reason it can't be done.

This strikes me as solving the wrong problem. It's not as useful to be able to derive a table from selected columns -- the desire is to complete columns from a table. It's both more intuitive and helps the user more than once.

[deleted]

Re: A Critique of SQL, 40 Years Later

#223

Post by rawgabbit »

alphanumeric0 wrote: post #105
vendiddy wrote: post #5

Earlier quoted context omitted.

I think these are great suggestions. It seems like you're suggesting that someone could design a functional-style programming language that compiles to SQL. 2 & 3 are my biggest pain points. I can't just extract functions like I can with a regular programming language. Instead, SQL queries get increasingly complex with no great tools to manage that. For 3, products like https://materialize.com/ look interesting for b…

I'm not familiar with Ecto, dplyr, or DBT, but I would love an ML-like language to replace SQL. I'm imagining being able to pass a table (or any table-oriented data, like a sub-query) to functions that would type-check columns and would return table-oriented or scalar data. I'm not sure if this is actually possible in practice, but one can dream. For instance, a "top 10" function that could be re-used on any table (a…

I don't quite follow what you are saying. In some SQL engines you can use row_number() function and derived tables (or CTE) to get top 10.

  SELECT Id,col1,col2
  FROM
  (
    SELECT 
    Id
    ,col1
    ,col2 
    ,row_number () over (partition by columnkey1, columnkey2 order by anycolumnwilldohere desc) as _row
    FROM _table
  ) as anytablealiaswilldohere
  WHERE _row

Re: A Critique of SQL, 40 Years Later

#224

Post by rawgabbit »

crazygringo wrote: post #37

I've written a bajillion queries and have tons of nitpicks, but it's the twin meanings of NULL that really kills me. NULL can be the value of a field in a record, but it is also used to indicate the lack of a record in a JOIN. If I run: SELECT x.a, y.b FROM x LEFT JOIN y on x.a = y.a and I get back [5, NULL] I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table…

As another commenter said, if your table have required ID columns, you should specify it in your SELECT.

     SELECT x.Id,x.a,y.ID,y.b 
     FROM x 
     LEFT JOIN y 
       on x.a = y.a
The output [Id12345,5,Id6789,NULL] means the column is null. The output [Id12345,5,NULL,NULL] means the row is empty.

On a side not, I use the SQL below all the time to identify missing rows on the "comparetable".

  SELECT #basetable.columnname
  FROM #basetable
  LEFT OUTER JOIN #comparetable
  ON #basetable.columnname1 = #comparetable.columnname1
    and #basetable.columnname2 = #comparetable.columnname2
    and #basetable.columnname3 = #comparetable.columnname3
  WHERE #comparetable.columnname1 is null

Re: A Critique of SQL, 40 Years Later

#225

Post by v0idzer0 »

exabrial wrote: post #10

The only thing that affects my daily life with SQL is that FROM should be before the SELECT keyword. This would _greatly_ improve type-ahead support in SQL IDEs. Nothing is perfect, but that is really the main beef. Another commentor already nailed having a LIMIT WITH ERROR clause to be specified on UPDATE,DELETE statements and explicitly throw an error otherwise. SQL is on of my favorite tools to use and I don't see…

Just a few years ago Javascript was pretty awful, so people started writing transpilers (most famously Babel) to add features like arrow functions, async/await, decorators, constants, etc. Typescript is another transpiler that greatly improves the language but outputs regular old, awful javascript. Many of these features even ended up being built into the language.

I wonder if it’s time for a transpiler renaissance in SQL? It would be trivial to convert “select id from users” into “from users select id”. I’m sure theres a lot of other cool feautures/sugar you could add while still outputting valid SQL.

Re: A Critique of SQL, 40 Years Later

#226

Post by cmrdporcupine »

zasdffaa wrote: post #190
cmrdporcupine wrote: post #143

Earlier quoted context omitted.

Which right away rules out a whole bunch of more sophisticated and elegant behaviours, honestly. The other alternative might be to implement one's new thing as a patch to alter the frontend of Postgres. I looked at this many years ago and the engineering effort was immense. But it might be easier now.

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

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 , so implementing e.g. Date's concept of "relation valued attributes" 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 and b) the presence of the possibility of dupes actually makes some query planner optimizations impossible to perform.

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.

Re: A Critique of SQL, 40 Years Later

#227

Post by colejohnson66 »

The_Colonel wrote: post #216
colejohnson66 wrote: post #171

Earlier quoted context omitted.

SQLite has an 608 times more lines of code for tests than the original code![0] I’d wager it’s the highest test/production ratio there is. [0]: https://www.sqlite.org/testing.html

Most of those tests are generated. It's wrong to focus on this metric IMO.

Auto generated or not, the point being made, in regards to @bot41's comment, was that SQLite is thoroughly tested.

Re: A Critique of SQL, 40 Years Later

#228

Post by zasdffaa »

cmrdporcupine wrote: post #226
zasdffaa wrote: post #190

Earlier quoted context omitted.

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

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 , so implementing e.g. Date's concept of "relation valued attributes" for example would be impractical 2) SQL's storage model doesn't map 1:1 with e.g. a relational model because S…

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.... 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.

Re: A Critique of SQL, 40 Years Later

#229

Post by andy81 »

merb wrote: post #157
bearjaws wrote: post #8

The only thing I would really blame solely on SQL is that UPDATE and DELETE statements don't require you to specify a limit. I have seen many times in my career where a rogue delete just truncates a table, a simple statement of intent (e.g. LIMIT 1) would tell the query planner that if it is about update/delete more than 1 row, it should error. In fact MySQL actually returns a warning if you do this. TRUNCATE clearly…

> TRUNCATE clearly states your intention to delete everything, but DELETE by default also deletes everything. btw. they are completly different. TRUNCATE basically ignores all transaction semantices for the sake of performance (which can be really really bad) in mysql it can't be rolled back, in postgres it's not mvcc safe. TRUNCATE most often uses either an exclusive lock or some other mechanism. if you know what yo…

The other important factor is permissions.

Truncate in MSSQL requires alter permissions, so a user that can wipe a table can also change its definition. Not true with delete.

Re: A Critique of SQL, 40 Years Later

#230

Post by naranha »

acdha wrote: post #219
naranha wrote: post #217

Earlier quoted context omitted.

But even SUM in AWS RDS Aurora is not optimal in terms of performance. You could do much better by having partial SUMs in the index btree nodes, that way you could get the sum of billions of records without processing any row individually. CouchDB supports this (even though it's by far not the most efficient database). I wish Postgres and others provided access to that sort of low level map-reduce index functionality…

> You could do much better by having partial SUMs in the index btree nodes, that way you could get the sum of billions of records without processing any row individually. It sounds like you don’t want a general purpose database. Something like what you describe might be more efficient on certain queries but only because it’s giving up a lot of flexibility - consider what happens if you add any filtering or grouping c…

> Something like what you describe might be more efficient on certain queries but only because it’s giving up a lot of flexibility - consider what happens if you add any filtering or grouping changes and those partial sums can no longer be used.

If you're doing a query that is not backed by an index, it's not scalable, so you could also do it in Excel.

> This preference for general systems rather than optimizing for a single query shows through in the tools used for this kind of task: materialized views and indexes using expressions.

Materialized views totally suck (at least in postgresql), because you cannot update them incrementally (yet). A better option most of the time are triggers and aggregate columns, but it requires a lot of micromanagement.

> It sounds like you don’t want a general purpose database

I don't think I want a special feature, map-reduce indexes seem pretty general "mathematically" sound thing.

> Another way of looking at this is to ask why a field with billions in R&D hasn’t already done it, and especially why various big data tools have been significantly less impactful than promised.

Well that's what's exiting about research, that some things haven't been done yet. Even though they may seem fairly obvious.

Post reply on HN