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

171–180 of 302 posts

Re: PRQL – A proposal for a better SQL

#171

Post by sanderjd »

taeric wrote: post #124
phailhaus wrote: post #116

Earlier quoted context omitted.

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.

This is... An odd choice. I'd assume I'm not without context looking at a query to know why I would want those columns. And the auto complete story is backwards. Often I know what columns I want, but I'm not clear what table I need to get them from. Such that, if you make a smarter suggest in the from to only include tables that have the columns, I'd be much happier.

Just throwing in another point of anecdata onto this pile: "Often I know what columns I want, but I'm not clear what table I need to get them from" does not make sense to me. I don't relate at all to their being a global namespace of columns, rather than a namespace of tables, each with its own columns specific to its context.

Re: PRQL – A proposal for a better SQL

#172

Post by tristanz »

hyperpallium2 wrote: post #169

\tangent What are today's data transformation needs (differ from Codd's inspiration?), how does relational algebra serve them, and design an "SQL" around that. There's got to be a 10x leapfrog in benefit for some niche in there, and that's the gateway to adoption.

To get 10x I think you need to wed it to solving broader workflow challenges, like dbt does today.

Re: PRQL – A proposal for a better SQL

#173

Post by KerryJones »

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…

This feels like a English-language thing. In english we tend to put our adjectives first, it feels natural, "Where is my red, round ball?", rather than some other languages (like German) where you put the subject first. Equivalent of "Where is my ball, red & round?"

While it inherently feels unnatural I do agree with the others here that the context is actually easier to understand once over the initial uncomfort.

Re: PRQL – A proposal for a better SQL

#174

Post by louthy »

tester756 wrote: post #143
spullara wrote: post #135

SQL could get a lot better by just adopting the ordering of operations like they did with LINQ: https://docs.microsoft.com/en-us/dotnet/csharp/programming-g...

Funnily enough LINQ Query syntax is really uncommon and everybody uses method syntax var list = new List {1,2,3} var extracted = list ....................Where(x => x > 1) ....................Select(x => $"my number: {x}) ....................ToList();

> everybody

Not everybody

Re: PRQL – A proposal for a better SQL

#175

Post by goodlinks »

maybe i am jaded (too many hours reading and writing sql as it is) but the structure of SQL is very closely linked to how i write a query:

what data do i want to see: most important

where doest is come from: next most important thing

What filters and restrictions am i going to put on it: least important

Re: PRQL – A proposal for a better SQL

#176

Post by taeric »

sanderjd wrote: post #171
taeric wrote: post #124

Earlier quoted context omitted.

This is... An odd choice. I'd assume I'm not without context looking at a query to know why I would want those columns. And the auto complete story is backwards. Often I know what columns I want, but I'm not clear what table I need to get them from. Such that, if you make a smarter suggest in the from to only include tables that have the columns, I'd be much happier.

Just throwing in another point of anecdata onto this pile: "Often I know what columns I want, but I'm not clear what table I need to get them from" does not make sense to me. I don't relate at all to their being a global namespace of columns, rather than a namespace of tables, each with its own columns specific to its context.

I challenge this. I accept that there are ambiguities, but I assert that you can go really fast by just telling someone to fetch a few columns by name.

I further assert that if your database is filled with "Id" and "name" columns, instead of "department_name" and similar, you are probably as likely to mess up a join as any benefit you get from the name being short. (And really, what advantage is there in short names nowadays?)

That all said. I worded my take too strongly. My point should have been that auto suggest should not be confined in either direction.

Re: PRQL – A proposal for a better SQL

#177

Post by drittich »

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…

I don't think it's falling apart at all. Personally, I would require that what columns get returned be explicit (optionally with a * type syntax that you have to enable - the defaults should be safe and * has its risks). For one thing, you don't necessarily want to return all the columns you have aggregated. E.g., you may be running the equivalent of a HAVING clause on an aggregate column, so don't need the value returned.

"Each line transforms the previous result." - I assume this is referring to the order that transpilation happens, so you can read it top to bottom and understand the flow easily.

One thing I would like to see is how a recursive CTE might look.

Re: PRQL – A proposal for a better SQL

#178

Post by oblio »

jsyolo wrote: post #114
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…

what expressions are being repeated here?

> (salary + payroll_tax) AS gross_salary,

> (salary + payroll_tax + healthcare_cost) AS gross_cost

> AND (salary + payroll_tax + healthcare_cost) > 0

And his is a simple example.

Re: PRQL – A proposal for a better SQL

#179

Post by m1sta_ »

jnsie wrote: post #133
munk-a wrote: post #48

I like the flow direction compared to standard SQL. SQL is supposed to read like a sentence I suppose but I have many times looked at it and really wanted things to be in a more logical order. My main suggestion would be to be a bit less terse and introduce a bit more firm formatting. I'm not a huge fan of the term "split" and feel like jazzing that up to "split over" or even just reviving "group by" would improve re…

I like the flow direction specifically for intellisense/autocomplete. I'm sure it would be easier to provide hints when the table name is known immediately.

I'd love for the next release of SQL to have optional alternative ordering of clauses

Re: PRQL – A proposal for a better SQL

#180

Post by hn_throwaway_99 »

munk-a wrote: post #48

I like the flow direction compared to standard SQL. SQL is supposed to read like a sentence I suppose but I have many times looked at it and really wanted things to be in a more logical order. My main suggestion would be to be a bit less terse and introduce a bit more firm formatting. I'm not a huge fan of the term "split" and feel like jazzing that up to "split over" or even just reviving "group by" would improve re…

> On the brief topic of form vs. flexibility. SQL is a thing that, when complex, is written by many people over the course of its lifetime - removing the ability to make bad decisions is better than enabling the ability to write simple things even simpler

Hallelujah! But, to your footnote, this is a major reason why I despise ORMs. In my mind they make writing simple code slightly easier, but they make complicated SQL statements, especially when you get some weird issue under load and you're trying to debug why your DB is falling over, a ton more difficult and you spend so much time just battling your ORM.

Post reply on HN