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

251–260 of 302 posts

Re: PRQL – A proposal for a better SQL

#251

Post by Maxion »

gunshai wrote: post #201
ako wrote: post #86

Earlier quoted context omitted.

With a CTE it would read a bit more like prql: with usa_employees as ( SELECT title, country, salary, (salary + payroll_tax) AS gross_salary, (salary + payroll_tax + healthcare_cost) AS gross_cost FROM employees WHERE country = 'USA' AND (salary + payroll_tax + healthcare_cost) > 0 ) select title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(gross_salary) AS average_gross_salary, SUM(gross_s…

My brain just thinks in CTEs over sub queries. I really dislike that my co-workers use these ridiculously nested sub sub sub queries. I just look at something like this and I immediately know what's going on. If it's nested sub queries it always takes me much longer.

To me those nested sub sub sub SQL queries come from a similar place as beginner coders who tend to make nested IF statements - a lack of experience with the language.

For very complicated stuff, SQL does become very hard to read compared to e.g. tidyverse + targets in R.

Re: PRQL – A proposal for a better SQL

#252

Post by dagss »

magicalhippo wrote: post #62

Now this is actually nice, unlike the other suggestion posted today[1]. Maybe I'm just too used to non-standard extensions of our database but the SQL example could, at least for our db, be rewritten as SELECT TOP 20 title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(gross_salary) AS average_gross_salary, SUM(gross_salary) AS sum_gross_salary, AVG(gross_cost) AS average_gross_cost, SUM(gros…

In Microsoft SQL cross apply can be used for this in even more situations and with less repetition:

    select top(20)
        title,
        country,
        ...
        avg(gross_salary) as average_gross_salary,
        ...
    from employees
    cross apply ( select
        gross_salary = employees.salary + employees.payroll_tax, -- or "as .."
        gross_cost = ...
    ) v -- some name required but don't need to use it if column names are unique
    where ...

Re: PRQL – A proposal for a better SQL

#253

Post by rkrzr »

maximilianroos wrote: post #131
ianbicking wrote: post #78

I like it, it's readable, unlike some SQL alternatives I've seen it doesn't make me feel like I'm dumb and don't understand what a query even is. I can't decide if it would be better or worse if it stuck more closely to SQL keywords. You use "from" and "select", but not "where", "order by", "group by". There's some danger of it being in an uncanny valley of SQLish, but I'm pretty sure I'd prefer just using those term…

Thanks! I just fleshed out composing CTEs, which is a small step towards the broader goal of making composition easier: https://github.com/max-sixty/prql/commit/dc68fcaaceef26cc078... Let me know if you have a good case of the sort of composition you find difficult in SQL (either here or in an issue). Thank you!

I think supporting variables and functions already solves most of my composability gripes with SQL.

Another problem that I have with composing SQL is that large queries quickly become unreadable, and error messages are also often not terribly helpful. I think having a more expressive type system would help with the error messages. Do you have any plans on adding a type system to PRQL?

Re: PRQL – A proposal for a better SQL

#254

Post by olau »

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 mainstream Java/Python/C#/Javascript etc. then functions need parentheses, "take 20" may be worse than slice, etc.

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.

Also I think you should try writing one or more native wrappers in your intended target languages to make sure it's easy to interface between the two, even if it means you'd have to use dots in that language.

I could imagine an end game where the ergonomics were so good that a database like Postgres ends up with a native PRQL frontend. Not sure you're there yet, though. IMHO SQL as a query language suffers from a) sometimes really bad ergonomics, b) it's hard to wrap in another programming language (also ergonomics), c) it has far too many concepts - it's not orthogonal.

Re: PRQL – A proposal for a better SQL

#255

Post by zigzag312 »

galkk wrote: post #88

I'm quite opposed to the idea "from should be first". I want to understand what exactly the query returns, not the implementation detail of the source of this data (that can later be changed). Literally first example from page - I have no idea what is being returned: from employees filter country = "USA" # Each line transforms the previous result. let gross_salary = salary + payroll_tax # This _adds_ a column / varia…

_from_ is kind of one of the most important context about the data being returned. It provides the type information. Columns you select are just properties of that type.

In SQL, where _from_ is placed at the end, we are essentially writing equivalent of 'property.object'; eg.: name.person, age.person

Re: PRQL – A proposal for a better SQL

#256

Post by silvestrov »

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…

"LIMIT 10" from PostgreSQL sounds nicer than "take 10" because "take" for me sounds like a random selection is made (take 10 from the bag).

Also want "OFFSET 20" from PG.

Re: PRQL – A proposal for a better SQL

#258

Post by jseban »

cribwi wrote: post #248
ako wrote: post #86

Earlier quoted context omitted.

With a CTE it would read a bit more like prql: with usa_employees as ( SELECT title, country, salary, (salary + payroll_tax) AS gross_salary, (salary + payroll_tax + healthcare_cost) AS gross_cost FROM employees WHERE country = 'USA' AND (salary + payroll_tax + healthcare_cost) > 0 ) select title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(gross_salary) AS average_gross_salary, SUM(gross_s…

In some cases for removing repeating (intermediate) calculations, I generally find it easier to use a lateral join (in postgres), like select title, country, avg(salary) as average_salary, sum(salary) as sum_salary, avg(gross_salary) as average_gross_salary, sum(gross_salary) as sum_gross_salary, avg(gross_cost) as average_gross_cost, sum(gross_cost) as sum_gross_cost, count(*) as emp_count from employees, lateral (…

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 people always argue for immutability and eliminating local state, when using procedural languages, but as soon as they switch to SQL, that actually works like this, they immediately want to introduce mutability and local state.

Re: PRQL – A proposal for a better SQL

#259

Post by dotancohen »

brigandish wrote: post #212
makeitdouble wrote: post #193

Earlier quoted context omitted.

On ORMs, the best use I see of them is for “transparent” queries that you don’t define. Like fetching a record by id, or a single record and all of its related properties. Or a list of all the record in a table matching a simple filter. That’s 98% of what we do against the DB, and I’m all for having it basically invisible. Then let’s just bypass the ORM altogether the minute we think about joining or grouping things…

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?

  > Isn’t it more important that the query you write with the ORM is readable than the underlying SQL it spits out?
I would look at the issue from a slightly different angle. Performance issues aside, I personally prefer either the ORM or the SQL depending on which is easier for the guy maintaining it to understand. Getting a row from the database and transforming it into an object? ORM. Generating a report on historical data across half a dozen tables? SQL.

Re: PRQL – A proposal for a better SQL

#260

Post by dotancohen »

cogman10 wrote: post #92
ianbicking wrote: post #78

I like it, it's readable, unlike some SQL alternatives I've seen it doesn't make me feel like I'm dumb and don't understand what a query even is. I can't decide if it would be better or worse if it stuck more closely to SQL keywords. You use "from" and "select", but not "where", "order by", "group by". There's some danger of it being in an uncanny valley of SQLish, but I'm pretty sure I'd prefer just using those term…

Yup, I like a lot of things about the way this looks. In particular, I like how friendly this looks to be for things like auto complete (pretty annoying to need to practically type the entire sql query only to go back and fix up the columns in order to get autocomplete to work). Specific things I'd like to see. How do you handle column ambiguity. In the examples, they show a join of positions to employee on employee_…

  > SQL null handling rules are terrible. I understand them, I work with them, but at
  > the same time, they are so different from other languages concept of "null" that
  > they are easy to trip over.
Could you elaborate? I'm really only versed in the MySQL accent, but I don't find anything unusual or unexpected about NULLS in MySQL. If there are any pitfalls that I should be aware of, I'd love to know about it here before my users start complaining about bugs.

Thanks.

Post reply on HN