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

PRQL – A proposal for a better SQL

github.com

Post reply on HN
Print view View original

291–300 of 302 posts

Re: PRQL – A proposal for a better SQL

#291

Post by dotancohen »

yencabulator wrote: post #276
dotancohen wrote: post #271

Earlier quoted context omitted.

> The one thing that I have occasionally tripped over...this doesn't seem intuitive. This paragraph led me to discovering this oddity: > select 'true' from dual where 3 not in (1, 2, null) > Empty set (0.00 sec) That's unexpected, and definitely something that I should be aware of. Thank you.

SQL NULL is like NaN, it never equals anything. (And on top of that, the result of any comparison with NULL isn't a boolean, it's NULL.) Similarly (except the result is a boolean), $ deno eval --print 'NaN == NaN' false

Thanks.

Re: PRQL – A proposal for a better SQL

#292

Post by unfocussed_mike »

orthoxerox wrote: post #273

How does it handle subqueries? Especially correlated ones, where the subquery depends on the value provided by the outer query?

My SQL life is seemingly dominated by correlated subqueries and self-joins where the join checks another row does _not_ exist (the row R with the largest column X is the one where there is no row R1 with a larger X1).

I don't believe SQL queries like these can be improved by a metalanguage. They _can_ be meaningfully improved by indentation, whitespace, careful naming and detailed comments.

Re: PRQL – A proposal for a better SQL

#293

Post by munk-a »

brigandish wrote: post #287
makeitdouble wrote: post #221

Earlier quoted context omitted.

In my experience, a lot of very semantically reasonable and readable code end up with very penalizing SQL at the end, and it's a real challenge to then rewrite the whole into decent queries. There can be part of an app where a very bad query here and there is not important, but more often than not it creeps up in key parts of the user experience, and it becomes very hard to untangle when it becomes something importan…

My experience is that the ORMs I've used most (LINQ and Ruby's Sequel) can produce far more efficient SQL than a human can, and if not, you change the code, just as you would have to if you wrote a slow SQL query. Of course, I've not seen every query in existence so it's more than possible you've seen bad SQL from an ORM, but the untangling part would again fall to those skilled in the language of the ORM - unless th…

I have some serious doubts that an ORM can tune queries as well as a human due to the fact that the ORM lacks one key piece of information that both me (and the database planning a query) can leverage - table statistics. An ORM can produce a query that will behave well in the best general circumstances, but as soon as you get into topics like subquery performance fencing ORMs simply have no ability to compute optimality on the fly. One specific example I've seen is where multiple paths exist through the database to transit from one fact to another with one path being more strictly optimal and the other path having more associations that may be needed - this can effect the join strategies you want to use so if your ORM is anything more than "I'll essentially tell you the SQL but in a weird syntax" then there's a good chance it'll chose the wrong path.

Re: PRQL – A proposal for a better SQL

#294

Post by makeitdouble »

brigandish wrote: post #287
makeitdouble wrote: post #221

Earlier quoted context omitted.

In my experience, a lot of very semantically reasonable and readable code end up with very penalizing SQL at the end, and it's a real challenge to then rewrite the whole into decent queries. There can be part of an app where a very bad query here and there is not important, but more often than not it creeps up in key parts of the user experience, and it becomes very hard to untangle when it becomes something importan…

My experience is that the ORMs I've used most (LINQ and Ruby's Sequel) can produce far more efficient SQL than a human can, and if not, you change the code, just as you would have to if you wrote a slow SQL query. Of course, I've not seen every query in existence so it's more than possible you've seen bad SQL from an ORM, but the untangling part would again fall to those skilled in the language of the ORM - unless th…

I haven't got the chance yo try Sequel, on the ruby side I played more with plain ActiveRecord and querying layers like ransack (my predecessor on the job loved abstraction layers)

In general ORM queries become ugly at three to four levels of joins and/or excluding under non trivial conditions (e.g. finding users that have not participated to a specific set of events). They will spit out something that works, but will take a few orders of magnitude more than an optimized query.

As you say, there is the option to play jenga with the ORM code to hit the right combination that produces a better output. But that feels like teaching a toddler to solve a puzzle that you already solved and are keeping the cheat sheet in your pocket. I personally don't see the beauty of it and would prefer to directly use the right SQL and call it a day.

On people skilled in SQL, you should have a few onboard anyway if you're doing more than basic CRUD on the DB, and it's easier to find than ORM gurus IMHO.

Re: PRQL – A proposal for a better SQL

#295

Post by aksss »

Jeez I don’t know - SQL is the eternal language and people are always trying to replace it or come up with alternatives so they don’t have to learn it, but those efforts have always turned into their own obscure learning domains with the exact same problems people complained about in the first place (I have to learn this obscure syntax to get and shape data). Just learn SQL fundamentals and move forward.

Re: PRQL – A proposal for a better SQL

#297

Post by BeefWellington »

phailhaus wrote: post #274
BeefWellington wrote: post #243

Earlier quoted context omitted.

It's query asking for the id, name, and author fields. Very straightforward, I have no idea how this is confusing. > The table is the context: you can't change that without having to change everything else. Except even in the provided single-table example this isn't true - you're getting subselected/CTEd results. No functional joins are demonstrated unfortunately. For example: from employees left_join positions [id=e…

You restated the query; I was asking what it's about . Is it a query across publications? Or is it a query over news articles? That context changes everything: how the query is written, what it can be joined with, how it can be filtered, how it is used, etc. Putting the FROM clause first means that you immediately have context to understand the rest of the query.

We can both play this game though:

    FROM articles
What fields am I expecting in the resultset? Sure I have some context, I know it'll be about articles, but I have no idea what actual data I care about.

You're arguing that:

    FROM articles
    SELECT id, name, author
Is substantially superior to:

    SELECT id, name, author
    FROM articles
I don't see them as markedly different with such a small example. HOWEVER, where the difference comes in is the "at a glance what am I getting in my resultset" data that is much easier to see in the latter, second easiest in the former, and not at all present in the linked article.

I think what this boils down to is what you (any reader, not you specifically) individually expect to need knowledge of when you're writing SQL. In most cases when sitting down to write a brand new piece of code to pull some data from the database, putting the list of tables involved in the query first matters most to some, whereas putting the list of fields to expect in the resultset matters most to others (I put myself in this camp).

For what it's worth, as I've stated elsewhere while I don't prefer it and would find it annoying to debug personally, I do recognize that the idea of SQL that allows you to list FROM / JOIN / etc. first is very appealing to some. What I think is completely off is the near-obfuscation of the examples in the linked article.

Re: PRQL – A proposal for a better SQL

#298

Post by brigandish »

makeitdouble wrote: post #294
brigandish wrote: post #287

Earlier quoted context omitted.

My experience is that the ORMs I've used most (LINQ and Ruby's Sequel) can produce far more efficient SQL than a human can, and if not, you change the code, just as you would have to if you wrote a slow SQL query. Of course, I've not seen every query in existence so it's more than possible you've seen bad SQL from an ORM, but the untangling part would again fall to those skilled in the language of the ORM - unless th…

I haven't got the chance yo try Sequel, on the ruby side I played more with plain ActiveRecord and querying layers like ransack (my predecessor on the job loved abstraction layers) In general ORM queries become ugly at three to four levels of joins and/or excluding under non trivial conditions (e.g. finding users that have not participated to a specific set of events). They will spit out something that works, but wil…

> I personally don't see the beauty of it and would prefer to directly use the right SQL and call it a day.

I think that's fair enough, there are enough ways to do things now that it should be possible to accommodate both.

> On people skilled in SQL, you should have a few onboard anyway if you're doing more than basic CRUD on the DB, and it's easier to find than ORM gurus IMHO.

I agree but I'm not sure there are more SQL gurus than those used to ORMs nowadays. Lately I've favoured using SQL but even 15 years ago most devs I knew couldn't use it well, I can't see devs used to Rails et al having the chops for it, sadly. What was once convenient easily becomes one's master.

> I haven't got the chance yo try Sequel

If you get the chance, I think it's worth it. It's easy to drop into plain SQL without dumping the ORM, and I've never had a problem with the stuff it generates. It's a pity ActiveRecord gets all the love instead.

Re: PRQL – A proposal for a better SQL

#299

Post by phailhaus »

BeefWellington wrote: post #297
phailhaus wrote: post #274

Earlier quoted context omitted.

You restated the query; I was asking what it's about . Is it a query across publications? Or is it a query over news articles? That context changes everything: how the query is written, what it can be joined with, how it can be filtered, how it is used, etc. Putting the FROM clause first means that you immediately have context to understand the rest of the query.

We can both play this game though: FROM articles What fields am I expecting in the resultset? Sure I have some context, I know it'll be about articles, but I have no idea what actual data I care about. You're arguing that: FROM articles SELECT id, name, author Is substantially superior to: SELECT id, name, author FROM articles I don't see them as markedly different with such a small example. HOWEVER, where the differ…

> I don't see them as markedly different with such a small example.

Of course not, because we're talking about a fundamental change in syntax that affects more than just two-line queries. PRQL puts the SELECT clause practically at the end of the query, after every join, filter, and aggregate. If you only care about the output, that's cool too: just look at the end. But if I want to understand a query in SQL, I have to read the entire thing backwards, clause by clause! By contrast, if the SELECT is at the end, that's not much of a problem.

Now you'll come back and say "but it's the same if you care about context: just look at the end!" And that's where we differ. I care more about writing, debugging, and understanding queries, whereas you think it's more important that the column names are up front, even if it makes understanding a nontrivial query much, much harder.

Re: PRQL – A proposal for a better SQL

#300

Post by kthejoker2 »

I both love this (it's very well done!) and hate this, because I feel it misses the forest for the trees.

It's like that meme "Nobody says 'I want to be an Excel guru when I grow up.'"

SQL is just the means to an end - to alter or retrieve data in a system.

We shouldn't be coming up with nicer ways to handle autocompletion, or recursive semantics, or windowing functions.

SQL was created to be declarative and human-friendly, to be read aloud.

The next evolution of SQL should be natural language.

It should be bounded at the schema and enriched with as much context as it can, both about the domain of the data, the data itself, and its relationships within itself and to other schemas.

I just want to see all the people in my organization who haven't submitted their timesheet this week; all the orders in my ERP that are waiting for parts from a specific vendor; how much lift I'm getting from my targeted marketing campaign. I just want to delete any contact from CRM who hasn't responded to an email in the last 45 days!

I don't want SQL; I want something that translates natural language into a logical plan, and a physical plan; and a separate tool that allows me to express many, many natural language concepts for my bounded schema ... perhaps in a SQL-like way.

Like a metrics / definition store on steroids, or just a sea of rich aliases and computed columns and subqueries that can be composed without stressing over syntax or newlines or ordering or complex join conditions.

Post reply on HN