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

281–290 of 302 posts

Re: PRQL – A proposal for a better SQL

#281

Post by hadley »

Maxion wrote: post #250
sin7 wrote: post #227

Earlier quoted context omitted.

Is it too deeply entrenched to change? The number of times I have had a data.frame grouped when it wasn't supposed to be, I can count on my fingers. But the hours that I spent trying to figure it out must amount to a paycheck or two.

There's a lot of dplyr code out there, and a lot of people who know most every part of the tidyverse by heart, making breaking changes like this so far into a frameworks life would cause a lot of unnecessary work in re-coding old code as well as requring people to re-learn syntax. IMO for such a small adjustment the benefits don't outweight the costs.

Yeah, that's my current position. It's possible that we might be able to add it in optionally (by adding a new `.by` argument to summarise and friends), but just the analysis to determine how it would affect existing code is a lot of work.

Re: PRQL – A proposal for a better SQL

#282

Post by ako »

paulhodge wrote: post #277
jseban wrote: post #258

Earlier quoted context omitted.

So now we have easily come up with three different ways of rewriting the query to avoid that duplication (which obviously was not a problem at all to begin with): subquery, CTE and lateral join. And there are also several more well known ways (views, custom functions, computed columns etc) so the whole premise now for even inventing a "better" language than SQL is then false? Or what am I missing. It's also weird how…

If there's multiple ways to do the same thing that's usually a BAD thing in terms of language design. Especially if some approaches are just newbie traps that experts learn to avoid, or if deciding the best method is a really subtle context-dependent decision. The ideal design is that the language encourages the one obviously "good" way to do it.

I'm not aware of any general purpose programming language that doesn't have multiple ways to achieve a specific goal. Can you give an example of language with good design?

Re: PRQL – A proposal for a better SQL

#283

Post by darksaints »

This is awesome, I really like the ML syntactic approach. I also really like the composability. I might just have to sign myself up as a contributor, because this checks off a huge chunk of my list of things that is wrong with SQL, and I'd love to see this succeed.

I know this is meant to transpile to SQL and so maybe this language is the wrong place to do this, but my biggest pet peeve with SQL is the ternary logic introduced by SQL nullability. I'm begging and pleading for this wart to go away, and I would love to see some algebraic sum types (Optional/Maybe, etc.) used in their place.

Re: PRQL – A proposal for a better SQL

#284

Post by kbelder »

orthoxerox wrote: post #273

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

This is what I'm most interested in. This is where SQL becomes very awkward and repetitive.

  select
    customer,
    ( select sum(revenue) from orders where customer=accounts.customer) as revenue,
    ( select count(transactionid) from orders where customer=accounts.customer) as orders
  from
    accounts;
That's a very simple example, but it can get VERY wordy and complex, and in my dreams I'd be able to write something like:

  getrevenue(cust) is select sum(revenue) from orders where customer=cust
  getorders(cust) is select count(transactionid) from orders where customer=cust

  select
    customer,
    getrevenue(customer),
    getorders(customer)
  from
    accounts;
In a way, it's just dynamic sql without hacking strings together in unmaintainable ways.

Re: PRQL – A proposal for a better SQL

#285

Post by maximilianroos »

olau wrote: post #254
maximilianroos wrote: post #2

I wrote this over the holidays, because I find SQL wonderfully elegant in its function, but really frustrating in its form. Let me know any feedback — as you can see it's still at the proposal stage. If it gains some traction I'll write an implementation.

I like the explicit pipelining idea, seems much easier to reason about. Some comments: I found the "# `|` can be used rather than newlines." a bit odd. So when using let, you're only transforming one column? I think the example would look weird with returns instead of |. Depending on your intended target, it might help adoption if you stay closer to the naming conventions of that target. If you're targeting mainstrea…

> I think annotating microversions would get tiresome fast. I think the right way to think of this is that you put in a single version number like 1, and then only ever change that if you need to do backwards-compatible changes that cannot be handled by clever hacks in the runtime.

Thanks good idea, I just changed this to remove the microversions. If we use SemVer, then before `1`, we'd hold versions compatible to the 0.X, and then to the X.

Re: PRQL – A proposal for a better SQL

#287

Post by brigandish »

makeitdouble wrote: post #221
brigandish wrote: post #212

Earlier quoted context omitted.

Isn’t it more important that the query you write with the ORM is readable than the underlying SQL it spits out? Using an ORM I can get reusable parts of a query, while writing complex joins, I’m not sure why skipping that part is good?

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 the ORM can't produce efficient SQL in a particular case. And just as it would if the query was originally written in SQL, you'd need someone skilled in SQL to untangle that.

What would that case (where an ORM cannot produce efficient SQL) look like?

Re: PRQL – A proposal for a better SQL

#288

Post by tapas73 »

fragmede wrote: post #245
BeefWellington wrote: post #244

Earlier quoted context omitted.

You've never authored a SQL query that does things like check special functions that don't exist in a table? For example: select @@version

Seems easy enough to work around with a magic table name in this hypothetical future reworked dialect of SQL? from @@special select version

or

    from dual
    select @@version

Re: PRQL – A proposal for a better SQL

#289

Post by maximilianroos »

anentropic wrote: post #268
maximilianroos wrote: post #16

Earlier quoted context omitted.

Thanks! I agree that integrating with the DB would allow much more from a lang. But PRQL is a bet that languages which start there (e.g Kusto) get lost because it requires changing DB, which is really hard. I worry EdgeDB may hit this issue too (but I'm really hoping it works, and they have an excellent team). As I think you're suggesting — you could imagine a language starting out as a transpiler, and then over time…

FYI I think the phrase you're looking for is "impedance mismatch" (I noticed this on the github readme too)

Ah thank you!

Re: PRQL – A proposal for a better SQL

#290

Post by dotancohen »

yencabulator wrote: post #275
dotancohen wrote: post #270

Earlier quoted context omitted.

> Existence of such value introduces three value logic because expression "NULL = " is neither true nor false. Could you elaborate on that? I'm thought that in SQL `NULL=NULL` returns FALSE, much like the floats `NAN==NAN` returns false: > select if(null=null, "Yes", "No") > +----------------------------+ > | No | > +----------------------------+ What does it mean that this is neither TRUE or FALSE? I very much appre…

The value in your if is not false, it's NULL. NULL=NULL behaves exactly like NULL=42, the value is NULL. Which is what the parent was trying to explain. With Postgres: postgres=# \pset null postgres=# select null, null=null, null=42; ?column? | ?column? | ?column? ----------+----------+---------- | | (1 row)

  > mysql> select 42=42,42=314,42=null,null=null;
  > +-------+--------+---------+-----------+
  > | 42=42 | 42=314 | 42=null | null=null |
  > +-------+--------+---------+-----------+
  > |     1 |      0 |    NULL |      NULL |
  > +-------+--------+---------+-----------+
I see, thank you. This behaviour is news to me.
Post reply on HN