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

231–240 of 260 posts

Re: A Critique of SQL, 40 Years Later

#231

Post by TristanBall »

tremon wrote: post #212
briHass wrote: post #191

Earlier quoted context omitted.

Isn't that what a common table expression is? Basically a pseudo temp-table to break down queries. Of course, they also allow recursion, which you can't do with a temp table.

Yes, but a common table expression is still bound to only one query. You can use it multiple times within the same query, but you still can't save a common table expression in a variable and re-use it in multiple queries. This is what I'd what to do if common table expression really were common: SELECT c1, c2 FROM DifficultJoinStructure AS myCte; WITH myCte SELECT c1, c2 WHERE SomeCondition(c1); WITH myCte SELECT c1,…

So.. a view then?

Granted most environments effectively treat views as DBA/Sysadmin owned objects, especially where end users/apps are effectively sharing one, or a small number, of user accounts.

But given user=schema aspect several of the traditional databases, I get the impression the original intent might have been a little more laissez fair?

Of course the same can be said for tables, and that was perhaps a little idealistic!

Views aren't always quite as composable as you'd like either, or maybe I'm just scarred by the particular DB engines I use most.

So I actually agree with you, but unfortunately SQL requires that the "WELL AKSHWELLY" be followed by one or more "BUT" clauses.

Re: A Critique of SQL, 40 Years Later

#232

Post by warrenm »

johannes1234321 wrote: post #220
warrenm wrote: post #218

Earlier quoted context omitted.

For every bit of JSON that comes in? No And then having to figure out what all the fields are after the fact? No That's why noSQL exists

What is the thin noSQL brings to the table which a relational system doesn't have? (Except that writing a depth search in a JSON document in SQL is a bit more cumbersome, but if that is a concern that can be wrapped in a data access library to generate the SQL)

I think I've explained that above - it's not having to be rigidly tied to a schema :)

Re: A Critique of SQL, 40 Years Later

#233

Post by thomas536 »

colinmcd wrote: post #164
thomas536 wrote: post #13

I once read an article about SQL and how reordering the sections of a query would make it more ergonomic for users (iirc things like specifying what you want first and then how to present it last, more like a pipeline). I searched many times over the years but have not been able to find it again.

Perhaps this one? https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with...

Perhaps? I remember the article arguing more comprehensively why a different order would be beneficial, but maybe I am just misremembering

Re: A Critique of SQL, 40 Years Later

#234

Post by jodrellblank »

tadfisher wrote: post #177
mamcx wrote: post #136

Earlier quoted context omitted.

> I'm struggling to come up with a case where removing SELECT results in parsing ambiguity. Oh! Is super-ambiguous! Make the parser and enjoy it! Lets make this more concrete: city SELECT id city ORDER id city id You could then "favor" projection as the most important than the others. Ok, so: city city city Which is the table, or the field?

I didn't suggest removing ORDER or FROM. This still makes sense: id FROM city ORDER BY id A plus is that sub-selects have more natural, expression-like syntax: id FROM (city WHERE elevation > 1000)

How do you know if you are SELECTing or DELETEing?

Re: A Critique of SQL, 40 Years Later

#235

Post by emptyparadise »

matheusmoreira wrote: post #195
noirscape wrote: post #53

Earlier quoted context omitted.

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

> if you're using SQLite outside of toy environments or hobby projects, you're doing it wrong https://www.sqlite.org/mostdeployed.html > Every Android device > Every iPhone and iOS device > Every Mac > Every Windows 10 machine > Every Firefox, Chrome, and Safari web browser > Every instance of Skype > Every instance of iTunes > Every Dropbox client > Every TurboTax and QuickBooks > PHP and Python > Most television se…

everyone is doing it wrong :(

Re: A Critique of SQL, 40 Years Later

#236

Post by boaarmpit »

dominotw wrote: post #3

SQL is having somewhat of a moment in the bigdata world, thanks in part to 'modern datastack' and new age datawarehouses like snowflake,bigquery. However there are a lot of pushback from 'traditional' dataengineers who were trained on spark/scala. Its bit of hardsell to go from a highly typed language to a free for all text based logic. I think the following is needed for sql to be finally accepted as 'serious' conte…

Ibis might be an option. It has syntax similar to pandas and can compile to a number of types of sql, pyspark, or dask.

https://github.com/ibis-project/ibis

Re: A Critique of SQL, 40 Years Later

#237

Post by alphanumeric0 »

rawgabbit wrote: post #223
alphanumeric0 wrote: post #105

Earlier quoted context omitted.

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

My idea is you wouldn't have to cut and paste implementations like this. You would be able to just call the function with a column name and table name.

Re: A Critique of SQL, 40 Years Later

#238

Post by The_Colonel »

colejohnson66 wrote: post #227
The_Colonel wrote: post #216

Earlier quoted context omitted.

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.

It's well tested, but that's still not a guarantee that there are no security issues. Latest acknowledged CVE bug is a month old: https://www.sqlite.org/cves.html

Re: A Critique of SQL, 40 Years Later

#239

Post by johannes1234321 »

warrenm wrote: post #232
johannes1234321 wrote: post #220

Earlier quoted context omitted.

What is the thin noSQL brings to the table which a relational system doesn't have? (Except that writing a depth search in a JSON document in SQL is a bit more cumbersome, but if that is a concern that can be wrapped in a data access library to generate the SQL)

I think I've explained that above - it's not having to be rigidly tied to a schema :)

Not that my "schema" is really rigid ... just a little more convoluted syntax than "createCollection" but underneath quite similar.

Re: A Critique of SQL, 40 Years Later

#240

Post by xpe »

gfody wrote: post #206
xpe wrote: post #203

Earlier quoted context omitted.

Sort of. "Does not specify" does not mean there is no connection between logical and physical layers. The relational model has strong implications for what a storage engine should do well.

> The relational model has strong implications for what a storage engine should do well. agreed, the implication is that the engine should do everything well. rdbms is a product of being pulled in every direction, there's generally an index strategy for everything including fully connected hyper graphs, and that's how they ought to be because we can write wildly complex recursive sql queries and the goal is an effici…

> agreed, the implication is that the engine should do everything well.

I don't think you mean "everything, equally well".

But if you mean that database vendors feel pressure to have their "RDBMS" be able to "be competitive" across a wide variety of use cases and metrics, I tend to agree.

Post reply on HN