9 ms·

Google's new pipe syntax in SQL

328 points by heydenberk 2y ago

Liona234 2y ago
[dead]
eternauta3k 2y ago
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.
- otabdeveloper4 2y ago
  Query building for an analytics database is impossible. These queries are always hand-rolled because you pay the analysts to optimize them.
oznog 2y ago
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.
urbandw311er 2y ago
Title should probably be changed, since the article is about using AI to convert a PDF to semantic HTML.
- deleted 2y ago
  [deleted]
- simonw 2y ago
  A surprising problem I'm seeing with maintaining a link blog is that articles from it occasionally get submitted to Hacker News, where people inevitably call them out as not being as appropriate as the source they are linking to - which is fair enough! That's why I don't tend to submit them myself. This particular post quickly turned into a very thinly veiled excuse for me to complain about PDFs, then demonstrate a Gemini Pro trick. In this case I converted to HTML - I've since tried converting a paper to Markdown and sharing in a Gist, which I think worked even better: https://gist.github.com/simonw/46a33d66e069efe5c10b63625fdabb4e https://gist.github.com/simonw/46a33d66e069efe5c10b63625fdab... - notes here https://simonwillison.net/2024/Aug/27/distro/ https://simonwillison.net/2024/Aug/27/distro/
  - llimllib 2y ago
    Have you seen gist.io? If you replace `gist.github.com/<user>/<id>` -> `https://gist.io/@<user>/<id> https://gist.io/@<user>/<id>`, you get a gist with nice typography. https://gist.io/@simonw/46a33d66e069efe5c10b63625fdabb4e https://gist.io/@simonw/46a33d66e069efe5c10b63625fdabb4e is the same gist you linked, but nicer to read
    - simonw 2y ago
      That's pretty neat! I like that it's run by a GitHub employee too (presumably as a side-project, but still) - makes me less nervous about the domain name blinking out of existence one day.
      - deleted 2y ago
        [deleted]
verdverm 2y ago
The research paper: https://storage.googleapis.com/gweb-research2023-media/pubtools/1004848.pdf https://storage.googleapis.com/gweb-research2023-media/pubto...
summerlight 2y ago
Previous submissions on the paper itself: https://news.ycombinator.com/item?id=41321876 https://news.ycombinator.com/item?id=41321876 (first) https://news.ycombinator.com/item?id=41338877 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.
- scrlk 2y ago
  There was a second submission of the paper, which attracted more comments: https://news.ycombinator.com/item?id=41338877 https://news.ycombinator.com/item?id=41338877
  - summerlight 2y ago
    Thank you, added it to my comment. I missed all the discussions!
- FridgeSeal 2y ago
  Kinda looks like a half-assed version of what PRQL does. Like, if we’re going to have nonstandard sql, let’s just fix a whole bunch of things, not just one or two?
  - summerlight 2y ago
    > Like, if we’re going to have nonstandard sql, let’s just fix a whole bunch of things, not just one or two? I think they intentionally kept themselves away from massive redesign of the languages, which has a good chance of becoming multi decades of frustrating death march. I know a number of such cases from C++ standard proposals and probably the team wanted to avoid it.
  - chubot 2y ago
    This is addressed in the paper -- it's nice to have something deployable in existing SQL languages, and it also doesn't rule out using PRQL
samwillis 2y ago
Richard Hipp, creator of SQLite, has implemented this in an experimental branch: https://sqlite.org/forum/forumpost/5f218012b6e1a9db 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.
- Blackthorn 2y ago
  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!
  - willvarfar 2y ago
    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 <-- at this point there is an implicit SELECT * SELECT whatever WHERE some_filter WHERE another_filter <-- this is like AND AGGREGATE something WHERE a_filter_that_is_after_grouping <-- is like HAVING ORDER BY ALL <-- group-by-all is great in engines that support it; want it for ordering too ...
    - aidos 2y ago
      What’s group-by-all? Sounds like distinct?
      - willvarfar 2y ago
        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 up lots of queries. E.g. SELECT foo, bar, SUM(baz) FROM x GROUP BY ALL <-- equiv to GROUP BY foo, bar (eh, except MySQL; my memory of MySQL is it will silently do ANY_VALUE() on any columns that aren't an explicit aggregate function but are not grouped; argh it was a long time ago)
slaymaker1907 2y ago
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.
- beart 2y ago
  Is there any internal inertia for such changes to SQL server?
  - WorldMaker 2y ago
    Given how Entity Framework is quite ubiquitous as "the ORM of choice" for SQL Server and its usage of C# Linq, there's certainly external momentum, whether or not SQL Server devs themselves are paying attention to how the majority of their users are writing queries today.
yarg 2y ago
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+Access+Relational+Data 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.
- dragonwriter 2y ago
  > This reminds me .NET's short lived Linq to SQL; "Short lived"? Its still alive, AFAIK, and the more popular newer thing for the same use case, Linq to Enntities, has the same salient features but (because it is tied to Entity Framework and not SQL Server specific) is more broadly usable.
  - yarg 2y ago
    It was in 3.5 only. If they've replaced it with something else in the last decade and a half that does not mean that they didn't get rid of it, or that it wasn't short lived. https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/ https://learn.microsoft.com/en-us/dotnet/framework/data/adon...
    - plusplusungood 2y ago
      LINQ is not the same as LINQ-to-SQL. The former is a language feature, the latter a library (one of many) that uses that feature.
      - yarg 2y ago
        Did you reply to the wrong person? Because I'm not the guy that didn't know that.
    - LeonB 2y ago
      Yeh. Linq to sql was a much more lightweight extension than EF, and was killed due to internal warring at MS. Database people were investing a lot of time and energy on doing things “properly” with EF, and this scrappy little useful tool, linq to sql, was seen as a competitor.
tehlike 2y ago
LINQ, PRQL, Kusto has all preceeded this. While LINQ is mostly restricted to .NET, PRQL is not. https://prql-lang.org/ 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 https://x.com/tehlike/status/1517533067497201666
- andrewguy9 2y ago
  I’m a big kusto user, and it’s wonderful to have pipes in a query language. If you haven’t tried it, it’s great!
  - tehlike 2y ago
    I have not tried it, but I used to be a .net developer and worked a lot with LINQ (and contributed a bit to NHibernate and its Linq provider) and I am a big fan of the approach. Kusto does seem interesting too, and i think some of the stuff i want to build will find a use for it!
    - Salgat 2y ago
      LINQ is so incredibly intuitive. I wonder if this will make creating C# LINQ providers for databases that support this syntax easier.
  - kbouck 2y ago
    Indeed. Elastic has also recently released a piped query language called ES|QL. Feels similar to Kusto. I find piped queries both easier to write, and read.
- numbsafari 2y ago
  The paper directly references PRQL and Kusto. The main goal here is to take lessons learned from earlier efforts and try and find a syntax that works inside and alongside the existing SQL grammar, rather than as a wholly separate language.
  - 2y ago
rileymat2 2y ago
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.
- beart 2y ago
  The select list is meaningless without everything that follows. Knowing that a query selects "id, "date" tells you nothing without knowing the table, the search criteria, etc.
  - aragonite 2y ago
    I really wish SQL used "RETURN" instead of "SELECT" (like in XQuery): 1. Calling it "RETURN" makes the fact of its later order of execution (relative to FROM etc) less surprising. 2. "RETURN RAND()" just reads more naturally than "SELECT RAND()". After all, we're not really "selecting" anything here, are we? 3. Would also eliminate any confusion with the selection operation in relational algebra.
  - antonvs 2y ago
    That's one benefit of the SQL naming convention which would use names like e.g. customer_id, invoice_date, etc. Also, when joining tables (depending on the SQL dialect) that can allow a shortcut synax, JOIN ON field_name, if the field name in the two tables is the same.
  - rileymat2 2y ago
    If you name fields that way, but accountId, createDate may not be meaningless in the context you are looking at.
AdieuToLogic 2y ago
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 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.
- wslh 2y ago
  I find this [1] from this [2]. Seems like a good explanation. It doesn't exist on Wikipedia though. [1] https://github.com/raganwald-deprecated/homoiconic/blob/master/2008-10-30/thrush.markdown https://github.com/raganwald-deprecated/homoiconic/blob/mast... [2] https://stackoverflow.com/a/285973/88231 https://stackoverflow.com/a/285973/88231
  - AdieuToLogic 2y ago
    A key thing to keep in mind is that the thrush combinator is a fancy name for a simple construct. The semantics it provides is a declarative form of traditional function composition. For example, given the expression: f (g (h (x))) The same can be expressed in languages which support the "|>" infix operator as: h (x) |> g |> f There are other, equivalent, constructs such as the Cats Arrow[0] type class available in Scala, the same Arrow[1] concept available in Haskell, and the `andThen` method commonly available in many modern programming languages. 0 - https://typelevel.org/cats/typeclasses/arrow.html https://typelevel.org/cats/typeclasses/arrow.html 1 - https://wiki.haskell.org/Arrow_tutorial https://wiki.haskell.org/Arrow_tutorial
chubot 2y ago
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 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!
- jshute4444 2y ago
  It is, using table-valued functions (TVFs). There's an example at the bottom of this file: https://github.com/google/zetasql/blob/master/zetasql/examples/pipe_queries/walkthrough_7day.sql https://github.com/google/zetasql/blob/master/zetasql/exampl...
  - chubot 2y ago
    That's cool, thanks! What about scalar valued functions? :) So I can reuse an expression in a WHERE and so forth (and I appreciate that HAVING can be generalized/removed)
metadat 2y ago
Simon: Please keep pushing, and mute nothing.
themerone 2y ago
My big wish for SQL is for single row inserts to have a {key: value} syntax.
- nickpeterson 2y ago
  This would condense lines of code by a lot and prevent a lot of dumb bugs.
- zX41ZdbW 2y ago
  In ClickHouse you can do INSERT INTO table FORMAT JSONEachRow {"key": 123} It works with all other formats as well. Plus, it is designed in a way so you can make an INSERT query and stream the data, e.g.: clickhouse-client --query "INSERT INTO table FORMAT Protobuf" < data.protobuf curl 'https://example.com/?query=INSERT...' --data-binary @- < data.bson
- BostonFern 2y ago
  MySQL has it without the braces.
thenegation 2y ago
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-struct-pipe-comp-hof/ https://cs.brown.edu/~sk/Publications/Papers/Published/rk-st...
notfed 2y ago
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.
carabiner 2y ago
I like this. Reminds me of pandas.
aragonite 2y ago
> 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?
- mjevans 2y ago
  ligatures like fi fl ffi ffl etc are for changes in fonts specific to rendering correctly on a screen or printer. It's intended to be a _rendered_ format, rather than a parse-able format. Well formatted epub and HTML generally are usually intended to update to end user needs and better fit available layout space.
  - lupire 2y ago
    That's fine, but a good compiled format should also include a source map for accessibility.
  - WorldMaker 2y ago
    Though it's also a stuck legacy throwback. Modern advice would be to not send ligatures directly to the renderer and instead let the renderer poll OpenType features (and Unicode/ICU algorithms) to build them itself. PDF's baking of some ligatures in its files seems something of a backwards compatibility legacy mistake to still support ancient "dumb" PostScript fonts and pre-Unicode font encodings (or least pre-Unicode Normalization Forms). It's also a bit of the fact that PDF has always been confused about if it is the final renderer in a stack or not.
    - jahewson 2y ago
      That wouldn’t work for PDF’s use case of being an arbitrary paper-like format because the various Unicode and OpenType algorithms don’t provide sufficient functionality for rendering arbitrary text: there are no one-size-fits all rules! The standards are a set of generic “best effort” guidelines for lowest-common-denominator text layout that are constantly being extended. Even for English the exact tweaking of line breaking and hyphenation is a problem that requires manual intervention from time to time. In mathematics research papers it’s not uncommon to see symbols that haven’t yet made it into Unicode. Look at the state of text on the web and you’ll encounter all these problems; even Google Docs gave in and now renders to a canvas. PDF’s Unicode handling is indeed a big mess but it does have the ability to associate any glyph with an arbitrary Unicode string, for text extraction purposes, so there’s nothing to stop the program that generates the PDF from mapping the fi ligature glyph to the to-character string “fi”.
1024core 2y ago
Isn't this the same syntax (or very similar to) Apache Beam?
Ericson2314 2y ago
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).
- anothername12 2y ago
  > We should really standardize a core language for SQL Do you mean something other than ISO/IEC 9075:2023 (the 9th edition of the SQL standard)?
  - roenxi 2y ago
    It costs 194 CHF to read. There is room for improvement.
  - Ericson2314 2y ago
    A core language is a minimal AST without surface syntax (and thus no bikeshedding of that) that distills the surface language to its essence.
    - Ericson2314 2y ago
      SQL is basically the list monad, with various quotients / refinements: - Sometimes the order doesn't matter - Sometimes there are functional dependencies - Sometimes one knows the length of the list in question is 1 (foreign key constraints)
- rrrrrrrrrrrryan 2y ago
  ANSI SQL is very much a thing, and you should strive to keep your queries as close as possible to standard SQL as your database engine allows, if you want those queries to be portable to other database technology in the future.
- yencabulator 2y ago
0xbadcafebee 2y ago
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.
- simonw 2y ago
  Why shouldn’t I read research papers on my phone? That’s where I read almost everything else.
  - adrian_b 2y ago
    Even when reading on the phone, I do not understand the complaint against the two-column format. The one-column format is fine on a large monitor, but on a small phone I prefer narrower columns, because a wide column would either make the text too small or it would require horizontal panning while reading. So I consider the two-column format as better for phones, not worse.
    - 9dev 2y ago
      One of the most complex and battle-tested open source projects is essentially a rendering engine for semantic text that has supported reflowing text to fit the screen for decades. And now you’re seriously considering having to zoom in on a column, then scrolling all the way back up and right to the next column, then down to the footnotes at the bottom, then to a random figure, to be a solution?
      - adrian_b 2y ago
        Yes, I strongly prefer reading PDF documents with fixed layout instead of HTML or any other formats with reflowing text, including on small phone screens. I frequently read documents with many thousands of pages, which also contain many figures and tables. A variable layout, at least for me, makes the browsing and the search through such documents much more difficult. I have never ever seen any advantage in having the text reflow to match whatever window happens to be temporarily used to display the text, except for ephemeral messages that I will never read again. For anything that I will read multiple times, I want the text to retain the same layout, regardless of what device or window happens to display it. If necessary, I see no problem in adjusting the window to fit the text, instead of allowing changes in the text, which would interfere with my ability of remembering it from the previous readings. I really hate those who fail to provide their technical documentation as PDF documents, being content to just have some Web pages with it.
deleted 2y ago
[deleted]
make3 2y ago
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
- simonw 2y ago
  See my comment here: https://news.ycombinator.com/item?id=41385143 https://news.ycombinator.com/item?id=41385143
BeefWellington 2y ago
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.
- dietr1ch 2y ago
  Sounds a bit like "new thing scary" unless you show why having select in front actually avoids problems, and I don't think there's a clear problem they avoid, but it does make it really hard to autocomplete (can you even do it properly?) while something along the lines of just swap select for from is well defined.
  - garrettgarcia 2y ago
    > 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 the SQL engine (sort of but not really) executes the clauses in a different order. I think the onus of proof that SQL clauses are in the wrong order is on the people who claim they're in the wrong order.
    - Sankozi 2y ago
      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.
donatj 2y ago
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.
deleted 2y ago
[deleted]
dang 2y ago
Recent and related: Pipe Syntax in SQL - https://news.ycombinator.com/item?id=41338877 https://news.ycombinator.com/item?id=41338877 - Aug 2024 (219 comments)
wvenable 2y ago
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.
- bvrmn 2y ago
  It could be a custom zetasql extension leaked into the paper.
mav3ri3k 2y ago
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 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.
stevefan1999 2y ago
That's just Linq from C# except Google want to make it a SQL standard...
isoprophlex 2y ago
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...
- WorldMaker 2y ago
  It's like other "arrow" digraphs in common programming languages today, such as =>. You can picture it as a triangle pointing to the right. Many Programming Ligature fonts even often draw it that way. For instance it is shown under F# in the Fira Code README: https://github.com/tonsky/FiraCode https://github.com/tonsky/FiraCode
- summerlight 2y ago
  They considered ditching `|>` or using `|` but unfortunately there's a bunch of syntactic ambiguity.
KronisLV 2y ago
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.
- riku_iki 2y ago
  Yeah, and we will have two standards given popularity of existing syntax
philippta 2y ago
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?
- self 2y ago
  > Why even add the pipe operator? To make it easier for humans to read/write the queries.
- bvrmn 2y ago
  Aggregations could be non-commutative in general case and order is important. Filters before and after grouping are also tied to a particular place in the pipeline.
aloukissas 2y ago
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 https://elixirschool.com/en/lessons/basics/pipe_operator
jiggawatts 2y ago
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…
victorbjorklund 2y ago
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 https://hexdocs.pm/ecto/Ecto.Query.html
- h0l0cube 2y ago
  Thought this too. The example queries look very much like Ecto statements. I miss the ergonomics and flexibility of Ecto when I use database wrappers on other platforms.
eezing 2y ago
For autocomplete, FROM first makes a lot of sense. For readability, SELECT first makes more sense because the output is always at the top.
gopiandcode 2y ago
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 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 ```
julien040 2y ago
I haven't seen it mentioned yet, but it reminds me of PQL (not PRQL): https://pql.dev 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
datadeft 2y ago
> 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?
minkles 2y ago
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.
- dan-robertson 2y ago
  An interesting thing to me about all these dplyr-style syntaxes is that Wickham thinks the group_by operator was a design mistake. In modern dplyr you can often specify a .by on an operation instead. I found switching to this style a pretty easy adjustment, and I think it’s a bit better. Example: d |> filter(id==max(id),.by=orderId) I think PRQL were thinking a bit about ways to avoid a group_by operation and I think what they have is a kind of ‘scoped’ or ‘higher order’ group_by operation which takes your grouping keys and a pipeline and outputs a pipeline step that applies the inner pipeline to each group.
  - _Wintermute 2y ago
    Given 10 more years dplyr syntax might resemble data.table's
- countrymile 2y ago
  My thoughts exactly, it even uses the same pipe syntax, though I do prefer `%>%`. I've been avoiding SQL for a while now as it feels so clunky next to the tidyverse
OscarCunningham 2y ago
> 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.
nagisa 2y ago
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".
fridental 2y ago
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
- bvrmn 2y ago
  SQL parsers: exists. The paper clearly describes the goal: add a pipe syntax into existing systems with minor changes and be compatible with existing SQL queries. BTW: LINQ is an AST transformer not a language per se tied to a particular platform. None of existing DBs allows to use it directly.
- pxc 2y ago
  LINQ, Splunk, and KQL are all proprietary. For the purposes of setting new standards, they might as well not exist. PRQL is the only real entrant in your list when it comes to adding a pipelining syntax to a language for relational queries in a way that others can freely build on.
middayc 2y ago
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.
delegate 2y ago
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.
ahmed_ds 2y ago
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.
- theodpHN 2y ago
  Yes, it's very convenient to be able to use SQL with your massively parallel commercial database (Oracle, Snowflake, etc.) and then again with the results sets (Pandas, etc.). Interestingly, it's a concept that was implemented 35 years ago in SAS (link below) but is just now gaining traction in today's "modern" software (e.g., via DuckDB). USING THE NEW SQL PROCEDURE IN SAS PROGRAMS (1989) https://support.sas.com/resources/papers/proceedings-archive/SEUGI1989/Using%20The%20New%20SQL%20Procedure%20In%20SAS%20Programs.pdf https://support.sas.com/resources/papers/proceedings-archive... The Sql procedure uses SQL to create, modify, and retrieve data from SAS data sets and views derived from those data sets. You can also use the SOL procedure to join data sets and views with those from other database management systems through the SAS/ACCESS software interfaces.
  - ahmed_ds 2y ago
    Wow, that is really cool. One of my theses is that DuckDB will be bought by GCP (BigQuery), and polars will be bought by Databricks (or AWS). The thesis is based on the idea that Snowflake bought the Modin platform. The movement in DE seems to be towards data warehouse platforms streaming data (views/results) down to dataframe (Modin, Polars, DuckDB) platforms, which then stream down to their BI platforms. Because these database platforms are designed as OLAP platforms so, this approach makes sense.
deleted 2y ago
[deleted]
jappgar 2y ago
Wait, is this post about SQL or PDF...
sharpshadow 2y ago
I have to honestly say that I like PDFs they always work and don’t fail without JS.
OptionOfT 2y ago
> 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.
Zopieux 2y ago
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.
rosencrantz 2y ago
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?

328 points by heydenberk 2y ago

[dead]

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.

otabdeveloper4 2y ago
Query building for an analytics database is impossible. These queries are always hand-rolled because you pay the analysts to optimize them.

oznog 2y ago

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.

urbandw311er 2y ago

Title should probably be changed, since the article is about using AI to convert a PDF to semantic HTML.

deleted 2y ago
[deleted]
simonw 2y ago
A surprising problem I'm seeing with maintaining a link blog is that articles from it occasionally get submitted to Hacker News, where people inevitably call them out as not being as appropriate as the source they are linking to - which is fair enough! That's why I don't tend to submit them myself. This particular post quickly turned into a very thinly veiled excuse for me to complain about PDFs, then demonstrate a Gemini Pro trick. In this case I converted to HTML - I've since tried converting a paper to Markdown and sharing in a Gist, which I think worked even better: https://gist.github.com/simonw/46a33d66e069efe5c10b63625fdabb4e https://gist.github.com/simonw/46a33d66e069efe5c10b63625fdab... - notes here https://simonwillison.net/2024/Aug/27/distro/ https://simonwillison.net/2024/Aug/27/distro/
- llimllib 2y ago
  Have you seen gist.io? If you replace `gist.github.com/<user>/<id>` -> `https://gist.io/@<user>/<id> https://gist.io/@<user>/<id>`, you get a gist with nice typography. https://gist.io/@simonw/46a33d66e069efe5c10b63625fdabb4e https://gist.io/@simonw/46a33d66e069efe5c10b63625fdabb4e is the same gist you linked, but nicer to read
  - simonw 2y ago
    That's pretty neat! I like that it's run by a GitHub employee too (presumably as a side-project, but still) - makes me less nervous about the domain name blinking out of existence one day.
    - deleted 2y ago
      [deleted]

verdverm 2y ago

The research paper: https://storage.googleapis.com/gweb-research2023-media/pubtools/1004848.pdf https://storage.googleapis.com/gweb-research2023-media/pubto...

summerlight 2y ago

Previous submissions on the paper itself: https://news.ycombinator.com/item?id=41321876 https://news.ycombinator.com/item?id=41321876 (first) https://news.ycombinator.com/item?id=41338877 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.

scrlk 2y ago
There was a second submission of the paper, which attracted more comments: https://news.ycombinator.com/item?id=41338877 https://news.ycombinator.com/item?id=41338877
- summerlight 2y ago
  Thank you, added it to my comment. I missed all the discussions!
FridgeSeal 2y ago
Kinda looks like a half-assed version of what PRQL does. Like, if we’re going to have nonstandard sql, let’s just fix a whole bunch of things, not just one or two?
- summerlight 2y ago
  > Like, if we’re going to have nonstandard sql, let’s just fix a whole bunch of things, not just one or two? I think they intentionally kept themselves away from massive redesign of the languages, which has a good chance of becoming multi decades of frustrating death march. I know a number of such cases from C++ standard proposals and probably the team wanted to avoid it.
- chubot 2y ago
  This is addressed in the paper -- it's nice to have something deployable in existing SQL languages, and it also doesn't rule out using PRQL

samwillis 2y ago

Richard Hipp, creator of SQLite, has implemented this in an experimental branch: https://sqlite.org/forum/forumpost/5f218012b6e1a9db 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.

Blackthorn 2y ago
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!
- willvarfar 2y ago
  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 <-- at this point there is an implicit SELECT * SELECT whatever WHERE some_filter WHERE another_filter <-- this is like AND AGGREGATE something WHERE a_filter_that_is_after_grouping <-- is like HAVING ORDER BY ALL <-- group-by-all is great in engines that support it; want it for ordering too ...
  - aidos 2y ago
    What’s group-by-all? Sounds like distinct?
    - willvarfar 2y ago
      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 up lots of queries. E.g. SELECT foo, bar, SUM(baz) FROM x GROUP BY ALL <-- equiv to GROUP BY foo, bar (eh, except MySQL; my memory of MySQL is it will silently do ANY_VALUE() on any columns that aren't an explicit aggregate function but are not grouped; argh it was a long time ago)

slaymaker1907 2y ago

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.

beart 2y ago
Is there any internal inertia for such changes to SQL server?
- WorldMaker 2y ago
  Given how Entity Framework is quite ubiquitous as "the ORM of choice" for SQL Server and its usage of C# Linq, there's certainly external momentum, whether or not SQL Server devs themselves are paying attention to how the majority of their users are writing queries today.

yarg 2y ago

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+Access+Relational+Data 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.

dragonwriter 2y ago
> This reminds me .NET's short lived Linq to SQL; "Short lived"? Its still alive, AFAIK, and the more popular newer thing for the same use case, Linq to Enntities, has the same salient features but (because it is tied to Entity Framework and not SQL Server specific) is more broadly usable.
- yarg 2y ago
  It was in 3.5 only. If they've replaced it with something else in the last decade and a half that does not mean that they didn't get rid of it, or that it wasn't short lived. https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/ https://learn.microsoft.com/en-us/dotnet/framework/data/adon...
  - plusplusungood 2y ago
    LINQ is not the same as LINQ-to-SQL. The former is a language feature, the latter a library (one of many) that uses that feature.
    - yarg 2y ago
      Did you reply to the wrong person? Because I'm not the guy that didn't know that.
  - LeonB 2y ago
    Yeh. Linq to sql was a much more lightweight extension than EF, and was killed due to internal warring at MS. Database people were investing a lot of time and energy on doing things “properly” with EF, and this scrappy little useful tool, linq to sql, was seen as a competitor.

tehlike 2y ago

LINQ, PRQL, Kusto has all preceeded this. While LINQ is mostly restricted to .NET, PRQL is not. https://prql-lang.org/ 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 https://x.com/tehlike/status/1517533067497201666

andrewguy9 2y ago
I’m a big kusto user, and it’s wonderful to have pipes in a query language. If you haven’t tried it, it’s great!
- tehlike 2y ago
  I have not tried it, but I used to be a .net developer and worked a lot with LINQ (and contributed a bit to NHibernate and its Linq provider) and I am a big fan of the approach. Kusto does seem interesting too, and i think some of the stuff i want to build will find a use for it!
  - Salgat 2y ago
    LINQ is so incredibly intuitive. I wonder if this will make creating C# LINQ providers for databases that support this syntax easier.
- kbouck 2y ago
  Indeed. Elastic has also recently released a piped query language called ES|QL. Feels similar to Kusto. I find piped queries both easier to write, and read.
numbsafari 2y ago
The paper directly references PRQL and Kusto. The main goal here is to take lessons learned from earlier efforts and try and find a syntax that works inside and alongside the existing SQL grammar, rather than as a wholly separate language.
- 2y ago

rileymat2 2y ago

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.

beart 2y ago
The select list is meaningless without everything that follows. Knowing that a query selects "id, "date" tells you nothing without knowing the table, the search criteria, etc.
- aragonite 2y ago
  I really wish SQL used "RETURN" instead of "SELECT" (like in XQuery): 1. Calling it "RETURN" makes the fact of its later order of execution (relative to FROM etc) less surprising. 2. "RETURN RAND()" just reads more naturally than "SELECT RAND()". After all, we're not really "selecting" anything here, are we? 3. Would also eliminate any confusion with the selection operation in relational algebra.
- antonvs 2y ago
  That's one benefit of the SQL naming convention which would use names like e.g. customer_id, invoice_date, etc. Also, when joining tables (depending on the SQL dialect) that can allow a shortcut synax, JOIN ON field_name, if the field name in the two tables is the same.
- rileymat2 2y ago
  If you name fields that way, but accountId, createDate may not be meaningless in the context you are looking at.

AdieuToLogic 2y ago

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 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.

wslh 2y ago
I find this [1] from this [2]. Seems like a good explanation. It doesn't exist on Wikipedia though. [1] https://github.com/raganwald-deprecated/homoiconic/blob/master/2008-10-30/thrush.markdown https://github.com/raganwald-deprecated/homoiconic/blob/mast... [2] https://stackoverflow.com/a/285973/88231 https://stackoverflow.com/a/285973/88231
- AdieuToLogic 2y ago
  A key thing to keep in mind is that the thrush combinator is a fancy name for a simple construct. The semantics it provides is a declarative form of traditional function composition. For example, given the expression: f (g (h (x))) The same can be expressed in languages which support the "|>" infix operator as: h (x) |> g |> f There are other, equivalent, constructs such as the Cats Arrow[0] type class available in Scala, the same Arrow[1] concept available in Haskell, and the `andThen` method commonly available in many modern programming languages. 0 - https://typelevel.org/cats/typeclasses/arrow.html https://typelevel.org/cats/typeclasses/arrow.html 1 - https://wiki.haskell.org/Arrow_tutorial https://wiki.haskell.org/Arrow_tutorial

chubot 2y ago

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 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!

jshute4444 2y ago
It is, using table-valued functions (TVFs). There's an example at the bottom of this file: https://github.com/google/zetasql/blob/master/zetasql/examples/pipe_queries/walkthrough_7day.sql https://github.com/google/zetasql/blob/master/zetasql/exampl...
- chubot 2y ago
  That's cool, thanks! What about scalar valued functions? :) So I can reuse an expression in a WHERE and so forth (and I appreciate that HAVING can be generalized/removed)

metadat 2y ago

Simon: Please keep pushing, and mute nothing.

themerone 2y ago

My big wish for SQL is for single row inserts to have a {key: value} syntax.

nickpeterson 2y ago
This would condense lines of code by a lot and prevent a lot of dumb bugs.
zX41ZdbW 2y ago
In ClickHouse you can do INSERT INTO table FORMAT JSONEachRow {"key": 123} It works with all other formats as well. Plus, it is designed in a way so you can make an INSERT query and stream the data, e.g.: clickhouse-client --query "INSERT INTO table FORMAT Protobuf" < data.protobuf curl 'https://example.com/?query=INSERT...' --data-binary @- < data.bson
BostonFern 2y ago
MySQL has it without the braces.

thenegation 2y ago

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-struct-pipe-comp-hof/ https://cs.brown.edu/~sk/Publications/Papers/Published/rk-st...

notfed 2y ago

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.

carabiner 2y ago

I like this. Reminds me of pandas.

aragonite 2y ago

> 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?

mjevans 2y ago
ligatures like fi fl ffi ffl etc are for changes in fonts specific to rendering correctly on a screen or printer. It's intended to be a _rendered_ format, rather than a parse-able format. Well formatted epub and HTML generally are usually intended to update to end user needs and better fit available layout space.
- lupire 2y ago
  That's fine, but a good compiled format should also include a source map for accessibility.
- WorldMaker 2y ago
  Though it's also a stuck legacy throwback. Modern advice would be to not send ligatures directly to the renderer and instead let the renderer poll OpenType features (and Unicode/ICU algorithms) to build them itself. PDF's baking of some ligatures in its files seems something of a backwards compatibility legacy mistake to still support ancient "dumb" PostScript fonts and pre-Unicode font encodings (or least pre-Unicode Normalization Forms). It's also a bit of the fact that PDF has always been confused about if it is the final renderer in a stack or not.
  - jahewson 2y ago
    That wouldn’t work for PDF’s use case of being an arbitrary paper-like format because the various Unicode and OpenType algorithms don’t provide sufficient functionality for rendering arbitrary text: there are no one-size-fits all rules! The standards are a set of generic “best effort” guidelines for lowest-common-denominator text layout that are constantly being extended. Even for English the exact tweaking of line breaking and hyphenation is a problem that requires manual intervention from time to time. In mathematics research papers it’s not uncommon to see symbols that haven’t yet made it into Unicode. Look at the state of text on the web and you’ll encounter all these problems; even Google Docs gave in and now renders to a canvas. PDF’s Unicode handling is indeed a big mess but it does have the ability to associate any glyph with an arbitrary Unicode string, for text extraction purposes, so there’s nothing to stop the program that generates the PDF from mapping the fi ligature glyph to the to-character string “fi”.

1024core 2y ago

Isn't this the same syntax (or very similar to) Apache Beam?

Ericson2314 2y ago

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).

anothername12 2y ago
> We should really standardize a core language for SQL Do you mean something other than ISO/IEC 9075:2023 (the 9th edition of the SQL standard)?
- roenxi 2y ago
  It costs 194 CHF to read. There is room for improvement.
- Ericson2314 2y ago
  A core language is a minimal AST without surface syntax (and thus no bikeshedding of that) that distills the surface language to its essence.
  - Ericson2314 2y ago
    SQL is basically the list monad, with various quotients / refinements: - Sometimes the order doesn't matter - Sometimes there are functional dependencies - Sometimes one knows the length of the list in question is 1 (foreign key constraints)
rrrrrrrrrrrryan 2y ago
ANSI SQL is very much a thing, and you should strive to keep your queries as close as possible to standard SQL as your database engine allows, if you want those queries to be portable to other database technology in the future.
yencabulator 2y ago

0xbadcafebee 2y ago

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.

simonw 2y ago
Why shouldn’t I read research papers on my phone? That’s where I read almost everything else.
- adrian_b 2y ago
  Even when reading on the phone, I do not understand the complaint against the two-column format. The one-column format is fine on a large monitor, but on a small phone I prefer narrower columns, because a wide column would either make the text too small or it would require horizontal panning while reading. So I consider the two-column format as better for phones, not worse.
  - 9dev 2y ago
    One of the most complex and battle-tested open source projects is essentially a rendering engine for semantic text that has supported reflowing text to fit the screen for decades. And now you’re seriously considering having to zoom in on a column, then scrolling all the way back up and right to the next column, then down to the footnotes at the bottom, then to a random figure, to be a solution?
    - adrian_b 2y ago
      Yes, I strongly prefer reading PDF documents with fixed layout instead of HTML or any other formats with reflowing text, including on small phone screens. I frequently read documents with many thousands of pages, which also contain many figures and tables. A variable layout, at least for me, makes the browsing and the search through such documents much more difficult. I have never ever seen any advantage in having the text reflow to match whatever window happens to be temporarily used to display the text, except for ephemeral messages that I will never read again. For anything that I will read multiple times, I want the text to retain the same layout, regardless of what device or window happens to display it. If necessary, I see no problem in adjusting the window to fit the text, instead of allowing changes in the text, which would interfere with my ability of remembering it from the previous readings. I really hate those who fail to provide their technical documentation as PDF documents, being content to just have some Web pages with it.

deleted 2y ago

[deleted]

make3 2y ago

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

simonw 2y ago
See my comment here: https://news.ycombinator.com/item?id=41385143 https://news.ycombinator.com/item?id=41385143

BeefWellington 2y ago

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.

dietr1ch 2y ago
Sounds a bit like "new thing scary" unless you show why having select in front actually avoids problems, and I don't think there's a clear problem they avoid, but it does make it really hard to autocomplete (can you even do it properly?) while something along the lines of just swap select for from is well defined.
- garrettgarcia 2y ago
  > 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 the SQL engine (sort of but not really) executes the clauses in a different order. I think the onus of proof that SQL clauses are in the wrong order is on the people who claim they're in the wrong order.
  - Sankozi 2y ago
    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.

donatj 2y ago

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.

deleted 2y ago

[deleted]

dang 2y ago

Recent and related: Pipe Syntax in SQL - https://news.ycombinator.com/item?id=41338877 https://news.ycombinator.com/item?id=41338877 - Aug 2024 (219 comments)

wvenable 2y ago

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.

bvrmn 2y ago
It could be a custom zetasql extension leaked into the paper.

mav3ri3k 2y ago

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 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.

stevefan1999 2y ago

That's just Linq from C# except Google want to make it a SQL standard...

isoprophlex 2y ago

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...

WorldMaker 2y ago
It's like other "arrow" digraphs in common programming languages today, such as =>. You can picture it as a triangle pointing to the right. Many Programming Ligature fonts even often draw it that way. For instance it is shown under F# in the Fira Code README: https://github.com/tonsky/FiraCode https://github.com/tonsky/FiraCode
summerlight 2y ago
They considered ditching `|>` or using `|` but unfortunately there's a bunch of syntactic ambiguity.

KronisLV 2y ago

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.

riku_iki 2y ago
Yeah, and we will have two standards given popularity of existing syntax

philippta 2y ago

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?

self 2y ago
> Why even add the pipe operator? To make it easier for humans to read/write the queries.
bvrmn 2y ago
Aggregations could be non-commutative in general case and order is important. Filters before and after grouping are also tied to a particular place in the pipeline.

aloukissas 2y ago

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 https://elixirschool.com/en/lessons/basics/pipe_operator

jiggawatts 2y ago

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…

victorbjorklund 2y ago

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 https://hexdocs.pm/ecto/Ecto.Query.html

h0l0cube 2y ago
Thought this too. The example queries look very much like Ecto statements. I miss the ergonomics and flexibility of Ecto when I use database wrappers on other platforms.

eezing 2y ago

For autocomplete, FROM first makes a lot of sense. For readability, SELECT first makes more sense because the output is always at the top.

gopiandcode 2y ago

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 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 ```

julien040 2y ago

I haven't seen it mentioned yet, but it reminds me of PQL (not PRQL): https://pql.dev 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

datadeft 2y ago

> 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?

minkles 2y ago

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.

dan-robertson 2y ago
An interesting thing to me about all these dplyr-style syntaxes is that Wickham thinks the group_by operator was a design mistake. In modern dplyr you can often specify a .by on an operation instead. I found switching to this style a pretty easy adjustment, and I think it’s a bit better. Example: d |> filter(id==max(id),.by=orderId) I think PRQL were thinking a bit about ways to avoid a group_by operation and I think what they have is a kind of ‘scoped’ or ‘higher order’ group_by operation which takes your grouping keys and a pipeline and outputs a pipeline step that applies the inner pipeline to each group.
- _Wintermute 2y ago
  Given 10 more years dplyr syntax might resemble data.table's
countrymile 2y ago
My thoughts exactly, it even uses the same pipe syntax, though I do prefer `%>%`. I've been avoiding SQL for a while now as it feels so clunky next to the tidyverse

OscarCunningham 2y ago

> 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.

nagisa 2y ago

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".

fridental 2y ago

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

bvrmn 2y ago
SQL parsers: exists. The paper clearly describes the goal: add a pipe syntax into existing systems with minor changes and be compatible with existing SQL queries. BTW: LINQ is an AST transformer not a language per se tied to a particular platform. None of existing DBs allows to use it directly.
pxc 2y ago
LINQ, Splunk, and KQL are all proprietary. For the purposes of setting new standards, they might as well not exist. PRQL is the only real entrant in your list when it comes to adding a pipelining syntax to a language for relational queries in a way that others can freely build on.

middayc 2y ago

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.

delegate 2y ago

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.

ahmed_ds 2y ago

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.

theodpHN 2y ago
Yes, it's very convenient to be able to use SQL with your massively parallel commercial database (Oracle, Snowflake, etc.) and then again with the results sets (Pandas, etc.). Interestingly, it's a concept that was implemented 35 years ago in SAS (link below) but is just now gaining traction in today's "modern" software (e.g., via DuckDB). USING THE NEW SQL PROCEDURE IN SAS PROGRAMS (1989) https://support.sas.com/resources/papers/proceedings-archive/SEUGI1989/Using%20The%20New%20SQL%20Procedure%20In%20SAS%20Programs.pdf https://support.sas.com/resources/papers/proceedings-archive... The Sql procedure uses SQL to create, modify, and retrieve data from SAS data sets and views derived from those data sets. You can also use the SOL procedure to join data sets and views with those from other database management systems through the SAS/ACCESS software interfaces.
- ahmed_ds 2y ago
  Wow, that is really cool. One of my theses is that DuckDB will be bought by GCP (BigQuery), and polars will be bought by Databricks (or AWS). The thesis is based on the idea that Snowflake bought the Modin platform. The movement in DE seems to be towards data warehouse platforms streaming data (views/results) down to dataframe (Modin, Polars, DuckDB) platforms, which then stream down to their BI platforms. Because these database platforms are designed as OLAP platforms so, this approach makes sense.

deleted 2y ago

[deleted]

jappgar 2y ago

Wait, is this post about SQL or PDF...

sharpshadow 2y ago

I have to honestly say that I like PDFs they always work and don’t fail without JS.

OptionOfT 2y ago

> 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.

Zopieux 2y ago

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.

rosencrantz 2y ago

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?