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

Google's new pipe syntax in SQL

simonwillison.net

Post reply on HN
Print view View original

141–150 of 192 posts

Re: Google's new pipe syntax in SQL

#141

Post by meindnoch »

aragonite wrote: post #47

> This remains a long-standing pet peeve of mine. PDFs like this are horrible to read on mobile phones, hard to copy-and-paste from ... I've never understood why copying text from digitally native PDFs (created directly from digital source files, rather than by OCR-ing scanned images) is so often such a poor experience. Even PDFs produced from LaTex often contain undesirable ligatures in the copied text like fi and fl.…

If a PDF doesn't support text extraction, it's the fault of the software that created it. Most likely the software didn't include the glyph → Unicode character mapping in the PDF.

Re: Google's new pipe syntax in SQL

#142

Post by WesolyKubeczek »

BeefWellington wrote: post #69

Every time this FROM-first syntax style crops up it's always the most basic simple query (one table, no projections / subselects / consideration to SP/Views). Just for once I want to see complete examples of the syntax on an actual advanced query of any kind right away. Sure, toss out one simple case, but then show me how it looks when I have to join 4-5 reference tables to a fact table and then filter based on those…

> Once you do that, it becomes clear why SELECT first won out originally: legibility and troubleshooting.

Also, tools can trivially tell DQL from DML by the first word they encounter, barring data-modifying functions (o great heavens, no!).

Re: Google's new pipe syntax in SQL

#143

Post by bvrmn »

Sankozi wrote: post #88
garrettgarcia wrote: post #75

Earlier quoted context omitted.

> Sounds a bit like "new thing scary" unless you show why having select in front actually avoids problems This isn't really fair. BeefWellington gave a reason why SQL is how it is (and how it has been for ~50 years). It's reasonable to ask for a compelling reason to change the clause order. Simon's post says it "has always been confusing", but doesn't really explain why except by linking to a blog post that says that…

But it has been explained many times from many angles. * SELECT first makes autocomplete hard * SELECT first is the only out of order clause in the SQL statement when you look at it from execution perspective * you cannot use aliases defined in SELECT in following clauses * in some places SELECT is pointless but it is still required (to keep things consistent?) Probably many more.

> you cannot use aliases defined in SELECT in following clauses

Some DBs allow it or allow it partially. It's a major constant friction factor for me to do a guess work across different database systems.

Re: Google's new pipe syntax in SQL

#144

Post by bvrmn »

BeefWellington wrote: post #69

Every time this FROM-first syntax style crops up it's always the most basic simple query (one table, no projections / subselects / consideration to SP/Views). Just for once I want to see complete examples of the syntax on an actual advanced query of any kind right away. Sure, toss out one simple case, but then show me how it looks when I have to join 4-5 reference tables to a fact table and then filter based on those…

> SELECT first won out originally: legibility and troubleshooting.

It quite interesting to dive into history of SQL alternatives in 70x/80x.

Re: Google's new pipe syntax in SQL

#145

Post by meepmorp »

mixedCase wrote: post #76
BeefWellington wrote: post #69

Every time this FROM-first syntax style crops up it's always the most basic simple query (one table, no projections / subselects / consideration to SP/Views). Just for once I want to see complete examples of the syntax on an actual advanced query of any kind right away. Sure, toss out one simple case, but then show me how it looks when I have to join 4-5 reference tables to a fact table and then filter based on those…

https://prql-lang.org/ has a bunch of good examples on its home page. If you engage the syntax with your System 2 thinking (prefrontal cortex, slow, the part of thinking we're naturally lazy to engage) rather than System 1 (automated, instinctual, optimized brain path to things we're used to) you'll most likely find that it is simpler, makes more logical sense so that you're filtering down things naturally like a sie…

> If you engage the syntax with your System 2 thinking (prefrontal cortex, slow, the part of thinking we're naturally lazy to engage) rather than System 1 (automated, instinctual, optimized brain path to things we're used to)

You might not have intended it this way, but your choice of phrasing is very condescending.

Re: Google's new pipe syntax in SQL

#146

Post by [deleted] »

maxbond wrote: post #140
foldr wrote: post #139

Earlier quoted context omitted.

Yes, it's an important point that SQLite is not a project with an open contribution model. However, they do presumably accept external contributions in the form of bug reports, suggested patches, etc. etc. You didn't have to dig through the repository to find the CoC. It was right there on the website at /codeofconduct.html: https://web.archive.org/web/20180315125217/https://www.sqlit...

Another cool project from Dr. Hipp is the fossil SCM, which SQLite is developed in, and one of it's features is that it ships with a web view similar to GitHub. The website is actually the web view of the repo. (Apologies for expressing that in a confusing way, I knew it was on the website, I was referring to the website as the repository.)

[deleted]

Re: Google's new pipe syntax in SQL

#147

Post by anonzzzies »

tehlike wrote: post #15

LINQ, PRQL, Kusto has all preceeded this. While LINQ is mostly restricted to .NET, PRQL is not. https://prql-lang.org/ It's a welcome change in the industry. I made this prediction a couple years back: https://x.com/tehlike/status/1517533067497201666

Not having LINQ is a terrible inconvenience everywhere. Most languages have libs that try to hack something similar, but it usually simply isn't.

Re: Google's new pipe syntax in SQL

#148

Post by Sesse__ »

willvarfar wrote: post #103
aidos wrote: post #93

Earlier quoted context omitted.

What’s group-by-all? Sounds like distinct?

Normally the SELECT has a bunch of columns to group by and a bunch of columns that are aggregates. Then, in the GROUP BY clause, you have to list all the columns to group by. The query compiler knows which they are, and polices you, making sure you got it right. All the GROUP BY ALL does is say 'the compiler knows, there's no need to list them all'. Very convenient. BigQuery supports GROUP BY ALL and it really cleans…

MySQL doesn't do this anymore; the ONLY_FULL_GROUP_BY mode became default in 5.7 (I think). You can still turn it off and get the old behavior, though.

Re: Google's new pipe syntax in SQL

#149

Post by mrits »

anonzzzies wrote: post #147
tehlike wrote: post #15

LINQ, PRQL, Kusto has all preceeded this. While LINQ is mostly restricted to .NET, PRQL is not. https://prql-lang.org/ It's a welcome change in the industry. I made this prediction a couple years back: https://x.com/tehlike/status/1517533067497201666

Not having LINQ is a terrible inconvenience everywhere. Most languages have libs that try to hack something similar, but it usually simply isn't.

It's a lot easier to design a good DSL when it doesn't have to be compatible with anything

Re: Google's new pipe syntax in SQL

#150

Post by croes »

willvarfar wrote: post #84
Blackthorn wrote: post #11

Earlier quoted context omitted.

FROM first would be nothing short of incredible. I can only hope that Postgres and others can find it within themselves to get together and standardize on such an extension!

Yeap I didn't know DuckDB supported it already! Being able to do SELECT FROM WHERE in any order and allowing multiple WHEREs and AGGREGATE etc, combined with supporting trailing commas, makes copy pasting templating and reusing and code-generating SQL so much easier. FROM table ...

A special keyword like HAVING prevents erros by typing in the wrong line.

How is OR done with this WHERES?

Post reply on HN