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

121–130 of 260 posts

Re: A Critique of SQL, 40 Years Later

#121

Post by tremon »

noirscape wrote: post #53
spprashant wrote: post #27

Earlier quoted context omitted.

If you exploring a set of tables you have never touched before, it really neat if you could just type in: FROM tablename t SELECT t. and some form of autocomplete mechanism, either prefills all the column names from table "t" or suggests the list of columns and/or types associated with it. This is much better than having to: 1. Run a SELECT with LIMIT statement just to get an idea of the layout. 2. Point and click th…

DESCRIBE TABLE is a command that pretty much does exactly this (explain what a table contains) and it's a part of MySQL. If you use PostgreSQL then you can use \d instead. I'm sure the other RDBMSes have their own equivalent (except for maybe SQLite but if you're using SQLite outside of toy environments or hobby projects, you're doing it wrong).

You're missing the point: until you have typed the FROM TableName t in your IDE, SELECT t. can not do autocompletion. That has nothing to do with whether SQL supports querying metadata (the ANSI portable way would be through INFORMATION_SCHEMA btw, not DESCRIBE). It's a consequence of the brain not responding to think-ahead queries by the IDE.

Re: A Critique of SQL, 40 Years Later

#122

Post by rrrrrrrrrrrryan »

jokoon wrote: post #47

I wish there were SQL "primitives" functions instead of the SQL language. For example if I want to pick a single row by id, with SQL I must send a query string, which results in parsing, which means lower latency. If I want to randomly select 100k rows among a database of 1 million entries, I need to build 10k query strings (I think?), which won't be fast to parse. I don't think this happens when using C pointers in…

To get 100k random rows in T-SQL it's:

SELECT TOP (100000) * FROM tblNm ORDER BY newid()

Re: A Critique of SQL, 40 Years Later

#123

Post by erichocean »

All SQL database implement the actual Relational Calculus internally—it's required to implemented a SQL optimizer.

SQL is just a language for submitting Relational Calculus to the database (+ DDL statements).

If you wanted to, for instance, you could add a language alongside SQL in Postgres, submit the results to the internal RC optimizer, execute the optimized query, and get back the results.

In your new language, you can address all of the issues Carlin Eng/Chris Date identify in the article.

Re: A Critique of SQL, 40 Years Later

#124

Post by tremon »

toto444 wrote: post #104
tadfisher wrote: post #79

Earlier quoted context omitted.

SELECT itself should be optional. Languages with expressions are fairly intuitive, e.g. "int x = foo.bar;" where "foo.bar" is equivalent to the "SELECT bar FROM foo;" SQL statement. I don't breathe SQL every day, so I'm struggling to come up with a case where removing SELECT results in parsing ambiguity.

> I'm struggling to come up with a case where removing SELECT results in parsing ambiguity It's actually useful to the person reading the code. It clearly defines where a statement starts, what it does and makes reading a query close to reading English. Show a SELECT FROM WHERE query to someone who does not know SQL and the person will understand it. It might be a bit harder if you remove the SELECT.

It clearly defines where a statement starts

And this is another example of the ad-hoc problems of SQL: query terminators (;) are optional. If they weren't, there would be no abiguity where a statement would start: it's the first word after the previous terminator.

Re: A Critique of SQL, 40 Years Later

#125

Post by cryptonector »

RyanHamilton wrote: post #36

qSQL based on the concepts of ordered lists is more appropriate for many queries, examples available here: https://www.timestored.com/b/kdb-qsql-query-vs-sql/ Kdb the system that qSQL is ran within, allows full use of variables and all builtin functions with tables/functions/variable/columns. It really is a case of less is more. What this allows is functional form queries. Imagine being able to query: ?[tableName;les…

I really never want to assume order in tables. That's because there could be many orders of interest, and that's one reason to have multiple indexes. And also because for a table, the order of rows can be hard to guarantee. Obviously something like qSQL would try hard to guarantee table row order, and that's great in the cases where qSQL is useful, but more generally it's going to impose on how you work with the database.

On the other hand, SQL is set-based. But being set-based is weird in the world of computers because in memory everything is ordered. Sets are a fiction in programming, as they're always ordered in some way, and the set abstraction can only try to hide that order.

And that order can be very useful.

But there can be many orders that can be useful, but only one in which things are stored in memory -- the others can only be extra indexes.

So the general purpose thing (SQL) has to be set-based, offering explicit ordering, and taking advantage of actual order for optimization.

Re: A Critique of SQL, 40 Years Later

#126

Post by chrisjc »

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…

Not 100% sure about what you're suggesting, but wouldn't it be easier to pass your functions to your table/sub-query?

And that's exactly what you're able to do in most of the modern data warehouse services such as Snowflake. Inferences can be contained within internal/external user defined functions.

This is very reminiscent of made the big-data/map-reduce movement so notable, sending your query to the data instead of moving your data to the query. Sending your model to the data, instead of sending the data to the model.

Re: A Critique of SQL, 40 Years Later

#127

Post by cryptonector »

jakespoon wrote: post #113
adrian_b wrote: post #87

Earlier quoted context omitted.

The title did not refer to years passed since the introduction of SQL, but to years passed since the publication of the paper with the title "A Critique of the SQL Database Language", i.e. from 1984-11. So the title is correct.

Title may be correct. First sentence is totally wrong. Why would I read further?

It's off by two years. People often round 48 to 50. I don't mind TFA rounding 48 to 50; you seem to, so don't read it if you don't want to.

Re: A Critique of SQL, 40 Years Later

#128

Post by cryptonector »

warrenm wrote: post #26

the title doesn't match the first statement, which states a math unfact: >A Critique of SQL, 40 Years Later 08.11.2022 >The SQL language made its first appearance in 1974, as part of IBM’s System R database. It is now over 50 years later, and SQL is the de facto language for 1974 isn't 50 years ago yet :)

If it'd been 1971 and TFA still said "50 years later", would you have responded that "1971 is more than 50 years ago"?

Re: A Critique of SQL, 40 Years Later

#129

Post by mamcx »

jolmg wrote: post #75
mamcx wrote: post #61

Earlier quoted context omitted.

> I now understand that it is an IDE related thing not something fundamental to the language. No, is fundamental issue to the language! The relational model is clear. You START with a relation and then compose with relational operators that return relations. ie: rel | project Sql do it weird . Is like in OO, where instead of define a class THEN define the properties, you define the properties THEN define the class. A…

Having SELECT come first makes sense to me because it's the only part of the statement that's required. FROM and everything else is optional. Also when reading a statement, you're mostly interested in what the returned fields are rather details like where they came from or how they're ordered. It kind of makes sense to put it at the start. Maybe other syntax forms have their benefits, specially when writing, but I do…

> SELECT come first makes sense to me because it's the only part of the statement that's required.

Only *IN SQL*.

You don't need it on the relational model, heck, no even in any other paradigm:

    1
That is!. (aka: SELECT 1)

So this:

    SELECT * FROM foo
is because SQL is made weird. More correctly, this should be only:

    foo
Also, SELECT is not required all the time, you wanna do:

    foo WHERE .id = 1
    foo ORDER BY .id
    foo ORDER BY .id WHERE .id = 1 //Note this is not valid in SQL, but because SQL is wrong!
But you probably think this as weird, because SQL in his peculiar implementation, that is, ok for one-off, ad-hoc query, and in THAT case, having the list of fields first is not that bad.

But now, when you see it this way, you note how MUCH nicer and simpler it could have been, because then each "fragment" of a SQL query could become *composable*.

But not on SQL, where the only "composition" is string concatenation, that is bad as you get.

Re: A Critique of SQL, 40 Years Later

#130

Post by vbezhenar »

andy81 wrote: post #117
Koshkin wrote: post #71

Earlier quoted context omitted.

Don’t these exist for a reason?

Of course. You've got a list of countries and are pulling each country's national flower, national bird, largest port city etc. Without outer joins, Liechtenstein with no ports doesn't show in the list at all. Sad news for people who want to know all countries, or Liechtenstein's national bird (eagle).

You’re not obliged to pull everything in one request. You can issue several requests.
Post reply on HN