from Hacker News

Google's new pipe syntax in SQL

by heydenberk on 8/25/24, 1:33 PM with 182 comments

by samwillis on 8/28/24, 10:13 PM
Richard Hipp, creator of SQLite, has implemented this in an experimental branch: https://sqlite.org/forum/forumpost/5f218012b6e1a9db
Worth reading the thread, there are some good insights. It looks like he will be waiting on Postgres to take the initiative on implementing this before it makes it into a release.
by tehlike on 8/28/24, 10:50 PM
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
by aragonite on 8/29/24, 1:57 AM
> 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 ﬁ and ﬂ. Text copied from some Springer journals sometimes lacks space between words or introduces unwanted space between letters in a word ... Is it due to something inherent in PDF technology?
by summerlight on 8/28/24, 10:00 PM
Previous submissions on the paper itself:
https://news.ycombinator.com/item?id=41321876 (first) https://news.ycombinator.com/item?id=41338877 (plenty of discussions)
I tried this new syntax and this seems a reasonable proposal for complex analytical queries. This new syntax probably does not change most simple transactional queries though. The syntax matches the execution semantic more closely, which means you less likely need to formulate query in a weird form to make query planner work as expected; usually users only need to move some pipe operators to more appropriate places.
by BeefWellington on 8/29/24, 4:17 AM
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 things.
Once you do that, it becomes clear why SELECT first won out originally: legibility and troubleshooting.
As long as DBs continue to support standard SQL they can add whatever additional syntax support they want but based on history this'll wind up being a whole new generation of emacs vs vi style holy war.
by urbandw311er on 8/28/24, 9:44 PM
Title should probably be changed, since the article is about using AI to convert a PDF to semantic HTML.
by yarg on 8/28/24, 10:40 PM
This reminds me .NET's short lived Linq to SQL;
There was a talk at the time, but I can't find the video: http://jaoo.dk/aarhus2007/presentation/Using+LINQ+to+SQL+to+....
Basically, it was a way to cleanly plug SQL queries into C# code.
It used this sort of ordering (where the constraints come after the thing being constrained); it needed to do so for IntelliSense to work.
by mav3ri3k on 8/29/24, 6:13 AM
The first piped query language I used was Nushell's implementation of wide-column tables. PRQL offers almost similar approach which I have loved dearly. It also maps to different SQL dialects. There is also proposal to work on type system: https://github.com/PRQL/prql/issues/381.
Google has now proposed a syntax inspired by these approaches. However, I am afraid how well it would be adopted. As someone new to SQL, nearly every DB seem to provide its own SQL dialect which becomes cumbersome very quickly.
Whereas PRQL feels something like Apache Arrow which can map to other dialects.
by 0xbadcafebee on 8/29/24, 3:01 AM
As to the writer's problem with PDFs on the web: they aren't for reactive web app viewing on mobile phones. Not everything has to be. If you reeeeeeeally need to read that research paper, find a screen that's bigger than 3" wide.
by slaymaker1907 on 8/28/24, 10:30 PM
I actually work on SQL Server, but I also write a lot of KQL queries which also work this way and I totally agree that the sequential pipe stuff is easier to write. I haven't read through the whole paper, but one aspect that I really like is that I think it's easier to guide the query optimization in this sequential style.
by donatj on 8/29/24, 4:50 AM
I've been writing SQL for something like 25 years and always thought the columns being SELECTed should have come last, not first. Naming your sources before what you're trying to get from them to me at least makes much more logical sense. Calling aliased table names before I have done the aliasing is weird.
Also it would make autocomplete in intelligent IDEs much more helpful when typing a query out from nothing.
by victorbjorklund on 8/29/24, 6:51 AM
Looks just like writing sql using Ecto in Elixir:
"users" |> where([u], u.age > 18) |> select([u], u.name)
https://hexdocs.pm/ecto/Ecto.Query.html
by chubot on 8/29/24, 12:09 AM
The next thing I would like is to define a function / macro that has a bunch of |> terms.
I pointed out that you can do this with shell:
Pipelines Support Vectorized, Point-Free, and Imperative Style https://www.oilshell.org/blog/2017/01/15.html
e.g.
```
    hist() {
      sort | uniq -c | sort -n -r
    }

    $ { echo a; echo bb; echo a; } | hist
      1 bb
      2 a

    $ foo | hist
    ...
   
```
Something like that should be possible in SQL!
by wvenable on 8/29/24, 5:32 AM
I didn't see this the first time:
```
    GROUP AND ORDER BY component_id DESC;
```
Is this kind of syntax combining grouping and ordering really necessary in addition the pipe operator? My advice would be to add the pipe operator and not get fancy adding other syntax to SQL as well.

by minkles on 8/29/24, 7:30 AM

That is basically R with tidyverse.

  flights |>
    filter(
      carrier == "UA",
      dest %in% c("IAH", "HOU"),
      sched_dep_time > 0900,
      sched_arr_time < 2000
      ) |>
    group_by(flight) |>
    summarize(
      delay = mean(arr_delay, na.rm = TRUE),
      cancelled = sum(is.na(arr_delay)),
      n = n()
      ) |>
    filter(n > 10)

If you haven't used R, it has some serious data manipulation legs built into it.

by AdieuToLogic on 8/28/24, 11:39 PM
If anyone is interested in the theoretical background to the thrush combinator, a.k.a. "|>", here is one using Ruby as the implementation language:
https://leanpub.com/combinators/read#leanpub-auto-the-thrush
Being a concept which transcends programming languages, a search for "thrush combinator" will yield examples in several languages.
by Ericson2314 on 8/29/24, 2:42 AM
We should really standardize a core language for SQL. Rust has MIR, Clang is making a CIR for C/C++. Once we have that, we'll be able to to communicate much better.
Right now, it's everyone faffing around with different mental models and ugly single pass compilers (my understanding is that parsing-->query planning is not nearly as well-separated in most DBs as parsing-->optomize-->codegen in most compilers).
by verdverm on 8/28/24, 9:58 PM
The research paper: https://storage.googleapis.com/gweb-research2023-media/pubto...
by Zopieux on 8/29/24, 9:17 PM
I just want trailing commas allowed everywhere. I can't believe this 2024 and we still have to deal with this crap. Humanity deserves better.
Syntax/DSL designers: if your language uses a separator for anything, please kindly allow trailing versions of that separator anywhere possible.
by themerone on 8/29/24, 12:42 AM
My big wish for SQL is for single row inserts to have a {key: value} syntax.
by gopiandcode on 8/29/24, 7:09 AM
I find this particular choice of syntax somewhat amusing because the pipe notation based query construction was something I ended up using a year ago when making an SQL library in OCaml:
https://github.com/kiranandcode/petrol
An example query being:
```
let insert_person ~name:n ~age:a db = Query.insert ~table:example_table ~values:Expr.[ name := s n; age := i a ] |> Request.make_zero |> Petrol.exec db
```
by KronisLV on 8/29/24, 6:38 AM
This feels like this should be in the official SQL standard and supported across a bunch of RDBMSes and understood by IDEs, libraries and frameworks.

by middayc on 8/29/24, 8:40 AM

Looking at the first example from PDF:

    FROM customer
    |> LEFT OUTER JOIN orders ON c_custkey = o_custkey
    AND o_comment NOT LIKE '%unusual%packages%'
    |> AGGREGATE COUNT(o_orderkey) c_count
    GROUP BY c_custkey
    |> AGGREGATE COUNT(*) AS custdist
    GROUP BY c_count
    |> ORDER BY custdist DESC, c_count DESC;

You could do something similar with Ryelang's spreadsheet datatype:

    customers: load\csv %customers.csv
    orders: load\csv %orders.csv

    orders .where-not-contains 'o_comment "unusual packages" 
    |left-join customers 'o_custkey 'c_custkey
    |group-by 'c_custkey { 'c_custkey count }
    |group-by 'c_custkey_count { 'c_custkey_count count }
    |order-by 'c_custkey_count_count 'descending

Looking at this, maybe we should add an option to name the new aggregate column (now they get named automatically) in group-by function because c_custkey_count_count is not that elegant for example.

by rileymat2 on 8/28/24, 10:57 PM
Is there research on what is easier to read when you are sifting through many queries?
I like the syntax for reading what the statement expects to output first, even though I agree that I don’t write them select first. I feel like this might be optimizing the wrong thing.
Although the example is nice, it does not show 20 tables joined first, which will really muddle it.
by delegate on 8/29/24, 8:57 AM
There's honeysql library in Clojure, where you define queries as maps, which are then rendered to SQL strings:
```
    {:select [:name :age]
     :from {:people :p}
     :where [:> :age 10]}
```
Since maps are unordered, this is equivalent to
```
    {:from {:people :p}
     :select [:name :age]
     :where [:> :age 10]}
```
and also
```
    {:where [:> :age 10]
     :select [:name :age]
     :from {:people :p}}
```
These can all be rendered to 'SELECT... FROM' or 'FROM .. SELECT'.
Queries as data structures are very versatile, since you can use the language constructs to compose them.
Queries as strings (FROM-first or not) are still strings which are hard to compose without breaking the syntax.
by OptionOfT on 8/29/24, 8:18 PM
> GROUP AND ORDER BY component_id DESC;
This feels like too much. GROUP BY and ORDER BY are separate clauses, and creating a way to group (heh) them in one clause complicates cognitive load, especially when there is an effort to reduce the overall effort to parse the query in your mind (and to provide a way for an intellisense-like system a way to make better suggestions).
```
    GROUP AND ORDER BY x DESC;
```
vs
```
    GROUP BY x;
    ORDER BY x DESC;
```
This long form is 1 word longer, but, it easier to parse in your mind, and doesn't introduce unneeded diffs when changing either the GROUP or the ORDER BY column reference.
by isoprophlex on 8/29/24, 6:35 AM
I love the idea but something in my brain starts to itch when I see that pipe operator
```
     |>
```
What IS that thing? A unix pipe that got confused with a redirect? A weird smiley of a bird wearing sunglasses?
It'll take some getting used to, for me...
by OscarCunningham on 8/29/24, 8:02 AM
> Rationale: We used the same operator name for full-table and grouped aggregation to minimize edit distance between these operations. Unfortunately, this puts the grouping and aggregate columns in different orders in the syntax and output. Putting GROUP BY first would require adding a required keyword before the AGGREGATE list.
I think this is bad rationale. Having the columns in order is much more important than having neat syntax for full-table aggregation.
by philippta on 8/29/24, 6:43 AM
Why even add the pipe operator?
If the DB engine is executing the statement out of order, why not allow the statement to be written in any order and let itself figure it out?
by julien040 on 8/29/24, 7:15 AM
I haven't seen it mentioned yet, but it reminds me of PQL (not PRQL): https://pql.dev
It's inspired by Kusto and available as an open-source CLI. I've made it compatible with SQLite in one of my tools, and it's refreshing to use.
An example:
```
  StormEvents
  | where State startswith "W"
  | summarize Count=count() by State
```
by eezing on 8/29/24, 7:03 AM
For autocomplete, FROM first makes a lot of sense. For readability, SELECT first makes more sense because the output is always at the top.
by nagisa on 8/29/24, 8:09 AM
People here are describing many projects that already have something resembling this syntax and concept, so I'll add another query language to the pile too: Influx's now-mostly-abandoned Flux. Uses the same |> token and structures the query descriptions starting with an equivalent of "FROM".
by ahmed_ds on 8/29/24, 9:13 AM
This is why I like tools like datastation and hex.tech. You write the initial query using SQL than process the results as a dataframe using Python/pandas. Surely, mixing Pandas and SQL like that is not good for data pipelines but for exploration and analytics, I have found this approach to be enjoyable.
by aloukissas on 8/29/24, 6:44 AM
This like Elixir's pipe operator [1]! I use it on the daily (with Ecto) and it's epic!
[1] https://elixirschool.com/en/lessons/basics/pipe_operator
by stevefan1999 on 8/29/24, 6:16 AM
That's just Linq from C# except Google want to make it a SQL standard...
by dang on 8/29/24, 5:14 AM
Recent and related:
Pipe Syntax in SQL - https://news.ycombinator.com/item?id=41338877 - Aug 2024 (219 comments)
by datadeft on 8/29/24, 7:26 AM
> It's been 50 years. It's time to clean up SQL. This
Is it though?
Are we trying to solve the human SQL parser and generator problem or there is some underlying implementation detail that benefits from pipes?
by eternauta3k on 8/29/24, 5:10 AM
Do manually-generated SQL strings have a place outside of interactive use? I use them in my small projects but I wonder if a query builder isn't better for larger systems.
by oznog on 8/29/24, 10:46 PM
SQL replacements is like not understanding the magnitude of the success of something so old.
SQL is fine.
SQL has been the state of the art for db queries for 40 years.
And it will continue to be when we all retire.
by jiggawatts on 8/29/24, 6:49 AM
They’re a bit late to the game, there’s are least a dozen such popular query languages. LINQ and KQL come to mind, but there are many others…
by metadat on 8/29/24, 12:31 AM
Simon: Please keep pushing, and mute nothing.
by carabiner on 8/29/24, 1:32 AM
I like this. Reminds me of pandas.
by fridental on 8/29/24, 8:29 AM
For the sake of God, please fucking stop inventing new pipe languages.
LINQ: exists
Splunk query language: exists
KQL: exists
MongoDB query language: exists
PRQL: exists
by 1024core on 8/29/24, 2:40 AM
Isn't this the same syntax (or very similar to) Apache Beam?
by notfed on 8/29/24, 1:12 AM
Is it just me, or does this seem anachronistic? Like, this is a conversation I expected to blow up 20 years ago. Better late than never.
by make3 on 8/29/24, 4:03 AM
this reads like an article written by someone with adhd who started writing about a scientific paper but got distracted by some random thing instead of reading it
by rosencrantz on 8/30/24, 8:15 PM
int *ptr;
// but let's change it to *int ptr;
// because the pointer symbol is more logical to write first
Please can we solve a real problem instead?
by jappgar on 8/29/24, 12:06 PM
Wait, is this post about SQL or PDF...
by thenegation on 8/29/24, 1:08 AM
Now wondering if there is any relation to "Structural versus Pipeline Composition of Higher-Order Functions (Experience Report)":
https://cs.brown.edu/~sk/Publications/Papers/Published/rk-st...
by sharpshadow on 8/29/24, 1:42 PM
I have to honestly say that I like PDFs they always work and don’t fail without JS.