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

111–120 of 302 posts

Re: PRQL – A proposal for a better SQL

#111

Post by maximilianroos »

akdor1154 wrote: post #97

Looks really nice, i've been scribbling away in a little notebook all the things i would do in "akdor's dream sql", and what you have here hits pretty much exactly. Wondering about generic use of `let` - you have let for col defns, but `func` for functions and a TODO for tables/CTEs - could/should `let` do the lot? (Like another commenter posted, this is how MS's M language, used in PowerQuery in PowerBI and Excel wo…

Thanks!

At the moment `let` is used to add a column as part of an existing pipeline. [1]

`func` is the start of new expressions / pipelines. And I just added a proposal for `table = `, which would be the same.

Does that make sense? Very open to more feedback...

[1] I just added `let` based on feedback here, it's better than it was, but not perfect, as it can be confused for a new pipeline given its use in other langs.

Re: PRQL – A proposal for a better SQL

#112

Post by phailhaus »

_jal wrote: post #102
inglor wrote: post #98

Earlier quoted context omitted.

The big advantage of "from first" like we have in Kusto KQL (a database we use at Microsoft) is that it provides much better autocomplete (if I write the `from` it can easily autocomplete the projection). If you want an interesting example of how a query language built for developer experience and autocompletions looks definitely check it out!.

Designing languages around autocomplete is like designing toilets for better toilet paper dispensers. The language should be right for human understanding, not automated mad-lib generation.

Building for autocomplete is building for human understanding. If it is impossible for a computer to determine the context of your query, why would a human do much better?

Re: PRQL – A proposal for a better SQL

#113

Post by inglor »

_jal wrote: post #102
inglor wrote: post #98

Earlier quoted context omitted.

The big advantage of "from first" like we have in Kusto KQL (a database we use at Microsoft) is that it provides much better autocomplete (if I write the `from` it can easily autocomplete the projection). If you want an interesting example of how a query language built for developer experience and autocompletions looks definitely check it out!.

Designing languages around autocomplete is like designing toilets for better toilet paper dispensers. The language should be right for human understanding, not automated mad-lib generation.

You would think that but having used both I find writing Kusto/KQL much smoother, neater and faster and if I have to choose between writing a query in either one I'd pick KQL.

I understand this is just an opinion but it's an opinion held by everyone in my org who writes both.

Theoretical correctness loses to pragmatism a lot and I'd read the KQL every day. Look at the examples at https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q... - look at the examples at https://docs.microsoft.com/en-us/azure/data-explorer/write-q... and tell me they're not more readable than comparable SQL?

(I can see the result type both by hovering on the query but also by just looking at the end of it - and in SQL most of the SELECTed items in complex queries are from subqueries anyway - at least in my use case)

Re: PRQL – A proposal for a better SQL

#114

Post by jsyolo »

ako wrote: post #86
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…

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…

what expressions are being repeated here?

Re: PRQL – A proposal for a better SQL

#115

Post by BeefWellington »

tomtheelder wrote: post #69
BeefWellington wrote: post #58

This is another in a series of these kinds of proposals that look excellent on first glance for perhaps the 75% case but start getting syntactically messy when I want to customize the resultset returned. On the surface, they're always neat but when you start to dig into how you'd implement something in an RDBMS, it begins to fall apart. Let's look at the example syntax: from employees filter country = "USA" # Each li…

> Where in here is it clearly stated which fields are returned? In the original SQL it's right up front but here it's buried into the "aggregate" function, and I'm not clear that this isn't an oversight. It's in the aggregate portion, like you said. Other example queries have a select portion. Why does it matter that it's not in the leading position like SQL? > Does this mean that the database must scan all records o…

> It's in the aggregate portion, like you said. Other example queries have a select portion. Why does it matter that it's not in the leading position like SQL?

I don't mind it not being in the leading position. The author provided a very simple query and in that case it's not immediately apparent what fields to expect the resultset to contain when returned to the consumer.

This is a troubleshooting issue more than anything else. IMO placing the "selected fields" into the very centre of the query is distracting and obfuscates what is happening.

> This is a proposal for a "transpiles to SQL" language. So long as that transpliation is predictable, you cannot run into the sort of issues you are describing.

I think a good test of whether any transpiled language works well is to look at whether it could work on its own as a language. See: Typescript.

Re: PRQL – A proposal for a better SQL

#116

Post by phailhaus »

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…

    SELECT id, name, author
Quick, what is this query about? What's ironic is that I think you have it backwards: the columns are the implementation detail, not the table. The table is the context: you can't change that without having to change everything else. But columns are the last step, the selection after the filters, joins, etc. They can be changed at any time without affecting the logic.

Re: PRQL – A proposal for a better SQL

#117

Post by samatman »

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…

I'm also completely unfamiliar with the PRQL syntax, outside of right now.

Reading the comment however, it would seem that `let` adds columns which are implicitly returned in the order they are defined.

I do see benefits in this, and can imagine pitfalls. Hard to judge without kicking the tires.

Update: It's quite possible we saw different syntax!

https://news.ycombinator.com/item?id=30063266

Without the `let` I would imagine having trouble reading it as well, I'm not sure if that would go away with familiarity but my instinct is that it's a useful addition.

Re: PRQL – A proposal for a better SQL

#118

Post by nextaccountic »

I also saw a proposal for a better syntax for SQL, called BQL, that was a strict superset but allowed for better modularity

http://intelligiblebabble.com/a-better-query-language-bql-la...

It had this github repo https://github.com/lelandrichardson/BQL but never went anywhere

I hope PRQL has a better fate! unfortunately, by deviating from SQL lexical conventions (using :, using [], etc) we lose the ability to copy-paste from sql code elswhere.

I want a better SQL, but I also want some compatibility. Like typescript is for javascript.

Re: PRQL – A proposal for a better SQL

#119

Post by jcdreads »

I like that everyone is trying to make something like SQL that reads more naturally to them. More alternatives is good! SQL is a widely accepted standard, and has strictly defined and super broadly accepted semantics.

As someone who has written quite a few half-baked-for-general-use but fit-for-purpose SQL generator utilities over the years, I'll suggest that if you intend for a novel syntax to be a general SQL replacement then being isomorphic to SQL would massively increase usefulness and uptake:

1. novel syntax to SQL; check! Now novel syntax works with all the databases!

2. any valid SQL to novel syntax; a bit harder, but I'd start by using a SQL parser like https://github.com/pganalyze/libpg_query and translating the resulting AST into the novel syntax.

3. novel syntax to SQL back to novel syntax is idempotent; a nice side effect is a validator/formatter for "novel syntax"

4. SQL to novel syntax back to SQL is idempotent; a nice side effect is a validator/formatter for SQL, which would be awesome. (See also https://go.dev/blog/gofmt, which is where I learned this "round trip as formatter" trick.)

I don't mean for this to sound negative, and I know that 2, 3, and 4 are kind of hard. Thank you for building prql!

Post reply on HN