Richard Hipp, creator of SQLite, has implemented this in an experimental branch: <a href="https://sqlite.org/forum/forumpost/5f218012b6e1a9db" rel="nofollow">https://sqlite.org/forum/forumpost/5f218012b6e1a9db</a><p>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.
LINQ, PRQL, Kusto has all preceeded this.<p>While LINQ is mostly restricted to .NET, PRQL is not.
<a href="https://prql-lang.org/" rel="nofollow">https://prql-lang.org/</a><p>It's a welcome change in the industry.<p>I made this prediction a couple years back:
<a href="https://x.com/tehlike/status/1517533067497201666" rel="nofollow">https://x.com/tehlike/status/1517533067497201666</a>
> 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 ...<p>I've never understood why copying text from <i>digitally native</i> 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. 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?
Previous submissions on the paper itself:<p><a href="https://news.ycombinator.com/item?id=41321876">https://news.ycombinator.com/item?id=41321876</a> (first)
<a href="https://news.ycombinator.com/item?id=41338877">https://news.ycombinator.com/item?id=41338877</a> (plenty of discussions)<p>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.
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).<p>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.<p>Once you do that, it becomes clear why SELECT first won out originally: legibility and troubleshooting.<p>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.
This reminds me .NET's short lived Linq to SQL;<p>There was a talk at the time, but I can't find the video: <a href="http://jaoo.dk/aarhus2007/presentation/Using+LINQ+to+SQL+to+Access+Relational+Data" rel="nofollow">http://jaoo.dk/aarhus2007/presentation/Using+LINQ+to+SQL+to+...</a>.<p>Basically, it was a way to cleanly plug SQL queries into C# code.<p>It used this sort of ordering (where the constraints come after the thing being constrained); it needed to do so for IntelliSense to work.
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: <a href="https://github.com/PRQL/prql/issues/381">https://github.com/PRQL/prql/issues/381</a>.<p>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.<p>Whereas PRQL feels something like Apache Arrow which can map to other dialects.
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.
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.
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.<p>Also it would make autocomplete in intelligent IDEs much more helpful when typing a query out from nothing.
Looks just like writing sql using Ecto in Elixir:<p>"users"
|> where([u], u.age > 18)
|> select([u], u.name)<p><a href="https://hexdocs.pm/ecto/Ecto.Query.html" rel="nofollow">https://hexdocs.pm/ecto/Ecto.Query.html</a>
The next thing I would like is to define a function / macro that has a bunch of |> terms.<p>I pointed out that you can do this with shell:<p><i>Pipelines Support Vectorized, Point-Free, and Imperative Style</i> <a href="https://www.oilshell.org/blog/2017/01/15.html" rel="nofollow">https://www.oilshell.org/blog/2017/01/15.html</a><p>e.g.<p><pre><code> hist() {
sort | uniq -c | sort -n -r
}
$ { echo a; echo bb; echo a; } | hist
1 bb
2 a
$ foo | hist
...
</code></pre>
Something like that should be possible in SQL!
I didn't see this the first time:<p><pre><code> GROUP AND ORDER BY component_id DESC;
</code></pre>
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.
That is basically R with tidyverse.<p><pre><code> 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)
</code></pre>
If you haven't used R, it has some serious data manipulation legs built into it.
If anyone is interested in the theoretical background to the thrush combinator, a.k.a. "|>", here is one using Ruby as the implementation language:<p><a href="https://leanpub.com/combinators/read#leanpub-auto-the-thrush" rel="nofollow">https://leanpub.com/combinators/read#leanpub-auto-the-thrush</a><p>Being a concept which transcends programming languages, a search for "thrush combinator" will yield examples in several languages.
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.<p>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).
The research paper: <a href="https://storage.googleapis.com/gweb-research2023-media/pubtools/1004848.pdf" rel="nofollow">https://storage.googleapis.com/gweb-research2023-media/pubto...</a>
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.<p>Syntax/DSL designers: if your language uses a separator for anything, please kindly allow trailing versions of that separator anywhere possible.
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:<p><a href="https://github.com/kiranandcode/petrol">https://github.com/kiranandcode/petrol</a><p>An example query being:<p>```<p>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<p>```
Looking at the first example from PDF:<p><pre><code> 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;
</code></pre>
You could do something similar with Ryelang's spreadsheet datatype:<p><pre><code> 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
</code></pre>
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.
Is there research on what is easier to read when you are sifting through many queries?<p>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.<p>Although the example is nice, it does not show 20 tables joined first, which will really muddle it.
There's honeysql library in Clojure, where you define queries as maps, which are then rendered to SQL strings:<p><pre><code> {:select [:name :age]
:from {:people :p}
:where [:> :age 10]}
</code></pre>
Since maps are unordered, this is equivalent to<p><pre><code> {:from {:people :p}
:select [:name :age]
:where [:> :age 10]}
</code></pre>
and also<p><pre><code> {:where [:> :age 10]
:select [:name :age]
:from {:people :p}}
</code></pre>
These can all be rendered to 'SELECT... FROM' or 'FROM .. SELECT'.<p>Queries as data structures are very versatile, since you can use the language constructs to compose them.<p>Queries as strings (FROM-first or not) are still strings which are hard to compose without breaking the syntax.
> GROUP AND ORDER BY component_id DESC;<p>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).<p><pre><code> GROUP AND ORDER BY x DESC;
</code></pre>
vs<p><pre><code> GROUP BY x;
ORDER BY x DESC;
</code></pre>
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.
I love the idea but something in my brain starts to itch when I see that pipe operator<p><pre><code> |>
</code></pre>
What IS that thing? A unix pipe that got confused with a redirect? A weird smiley of a bird wearing sunglasses?<p>It'll take some getting used to, for me...
> 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.<p>I think this is bad rationale. Having the columns in order is much more important than having neat syntax for full-table aggregation.
Why even add the pipe operator?<p>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?
I haven't seen it mentioned yet, but it reminds me of PQL (not PRQL): <a href="https://pql.dev" rel="nofollow">https://pql.dev</a><p>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.<p>An example:<p><pre><code> StormEvents
| where State startswith "W"
| summarize Count=count() by State</code></pre>
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".
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.
This like Elixir's pipe operator [1]! I use it on the daily (with Ecto) and it's epic!<p>[1] <a href="https://elixirschool.com/en/lessons/basics/pipe_operator" rel="nofollow">https://elixirschool.com/en/lessons/basics/pipe_operator</a>
Recent and related:<p><i>Pipe Syntax in SQL</i> - <a href="https://news.ycombinator.com/item?id=41338877">https://news.ycombinator.com/item?id=41338877</a> - Aug 2024 (219 comments)
> It's been 50 years. It's time to clean up SQL. This<p>Is it though?<p>Are we trying to solve the human SQL parser and generator problem or there is some underlying implementation detail that benefits from pipes?
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.
SQL replacements is like not understanding the magnitude of the success of something so old.<p>SQL is fine.<p>SQL has been the state of the art for db queries for 40 years.<p>And it will continue to be when we all retire.
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
int *ptr;<p>// but let's change it to *int ptr;<p>// because the pointer symbol is more logical to write first<p>Please can we solve a real problem instead?
Now wondering if there is any relation to "Structural versus Pipeline Composition of Higher-Order Functions (Experience Report)":<p><a href="https://cs.brown.edu/~sk/Publications/Papers/Published/rk-struct-pipe-comp-hof/" rel="nofollow">https://cs.brown.edu/~sk/Publications/Papers/Published/rk-st...</a>