Rendered at 16:52:00 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
jelder 2 days ago [-]
DuckDB had the right idea: just allow some flexibility in the relative order of the `select` and `from` clauses, and make a few other concessions for ergonomics. This then becomes valid:
from events -- table is first, which enables autocomplete
select
count(), -- * is implied, easier to type
customer_id, -- trailing commas allowed everywhere
group by all -- automatically groups by all non-aggregate columns
order by all -- orders rows by all columns in selected order
I get the ease of use - and sometimes use them myself- but implied (or relative) shortcuts are IMO a bad habit that can lead to serious issues that don't manifest as errors.
I do like the from clause first which better matches the underlying relationship algebra!
thesz 2 days ago [-]
SQL is not a pipeline, it is a graph.
Imagine three joins of three queries A,B and C, where first join J1 joins A and B, second join J2 joins A and C and third join J3 joins J1 and J2. Note that I said "queries," not "tables" - these A, B and C can be complex things one would not want or be able to compute more than once. Forget about compute, A, B and C can be quite complex to even write down and the user may really do not want to repeat itself. Look at TPC-DS, there are subqueries in the "with" sections that are quite complex.
This is why pipeline replacements for SQL are more or less futile efforts. They simplify simple part and avoid touching complex one.
I think that something like Verse [1] is more or less way to go. Not the Verse itself, but functional logic programming as an idea, where you can have first class data producers and effect system to specify transactions.
TIL about Verse looks cool I'll have to check it out.
> SQL is not a pipeline, it is a graph.
Maybe it's both? and maybe there will always be hard-to-express queries in SQL, and that's ok?
the RDBMS's relational model is certainly a graph and joins accordingly introduce complexity.
For me, just as creators of the internet regret that subdomains come before domains, I really we could go back in time and have `FROM` be the first predicate and not `SELECT`. This is much more intuitive and lends itself to the idea of a pipeline: a table scan (FROM) that is piped to a projection (SELECT).
thesz 2 days ago [-]
Pipeline is a specific kind of a graph.
Yes, there will always be hard-to-express queries, the question is how far can we go?
snthpy 23 hours ago [-]
Thanks, I'll check out Verse.
I haven't seen anyone make the point about graphs before. FWIW PRQL allows defining named subqueries that can be reused, like J1 and J2 in your example.
jnpnj 2 days ago [-]
Crazy to think that Fortnite might unleash a new population of people who toyed with functional-logic as their first paradigm.
lloydatkinson 2 days ago [-]
Does it really help to call SQL a graph?
data_ders 2 days ago [-]
right? like it's a graph and a relational model query and a pipeline and a language and an abstract syntax tree and declarative logical plan
thesz 2 days ago [-]
It does. Just like any other programming language.
lloydatkinson 2 days ago [-]
May as well call everything a graph at that point; meaningless.
thesz 2 days ago [-]
> meaningless.
No.
You present "programs are graphs" as trivial truth. True trivial truths are, as you pointed out, meaningless. But you leave out degree of applicability - information in the dependence graph differs between programming languages.
Dependencies form a graph, and analyses needed to optimize execution of the program graph differ wildly between languages. Look at С++ aliasing rules and C's "restrict" keyword.
One can't escape the dependence graph. But one can execute dependence graph better or worse, depending (pun intended) on the programming language.
dewey 2 days ago [-]
Every time I see these layers on top of SQL I think: Just use regular, boring SQL
It will be around for a long time, there's an infinite number of resources and examples for it and if you ever have to onboard someone into your code they don't need to learn something new. You can get pretty far by just using CTEs to "pipeline".
data_ders 2 days ago [-]
I'm as big a SQL stan as the next person and I'm also very skeptical anytime anyone says that SQL needs to be replaced.
At the same time, it's challenging that SQL cannot be iteratively improved and experimented upon.
IMHO, PRQL is a reasonable approach to extending SQL without replacing SQL.
But what I'd love to see is projects like Google's zeta-sql [1] and Substrait [2] get more traction. It would provide a more stable, standardized foundation upon which SQL could be improved, which would make the case for "SQL forever" even more strong.
Does anybody just use "regular, boring SQL" in practice though? All queries I have seen are loaded with regex and other non-standard extensions.
Is there even a db vendor that offers full ANSII SQL support? Last I'd checked the answer was no.
dewey 2 days ago [-]
In my case I consider Postgres / MariaDB "regular, boring SQL".
itishappy 2 days ago [-]
The problem persists, as Postgres and MariaDB use incompatible SQL dialects, right down to (imo) core concepts such as how to specify an automatically generated primary key.
dewey 1 days ago [-]
I'm aware of that, but what I meant is that they both extended the SQL standard in a similar way and they will have equivalent higher level features like "regex and other non-standard extensions" even if they are not 100% drop-in replacements.
itishappy 1 days ago [-]
Understood. My point is that the underlying concepts are consistent, but the syntax differs between vendors. This largely applies to PRQL as well. Admittedly slightly moreso, but if I already have to learn a new SQL dialect to use postgres, I might not mind learning a nicer one.
kubb 2 days ago [-]
Complex queries in SQL can quickly get out of control.
The fact that you need to replicate the same complex expressions in multiple values that you select or multiple parts of a where clause is bad enough.
That there’s no way to pipe the result of a query into another query is just adding insult to injury. (Just create a custom view bro).
But if technology competed in quality and not in vendor lock in, we wouldn’t have to deal with C++ or JavaScript.
tomjakubowski 2 days ago [-]
DuckDB's "friendly SQL" variant fixes some of these little problems with SQL, including giving the ability to use a column alias in WHERE clauses.
It's not as elegant as PRQL, because of course it's bolted onto the existing SQL syntax, rather than a redesign from scratch. But it has a big name behind it, and it's actually running in prod in Google Cloud... so it might have more momentum.
There’s probably a good reason why not, but I’d love a query language with sum types. They just feel like a natural way to model a lot of data
Taikonerd 2 days ago [-]
Is this project stalling out? The last post on the "posts" page is from March 2023. But the last commit to the git repo was last week...
maximilianroos 2 days ago [-]
Maintainer here!
Indeed we're doing fewer new features (and haven't posted to the posts page in a long time, as you noticed).
But it's still maintained, folks are still using it, if anyone finds bugs in simple-to-moderate queries then we'll fix them.
LLMs probably took a bit of the wind out of our sails for making this "the new standard". But I still think it's a really nice language and interface; if the world changed again such that it became more widely useful, I'd jump to spending lots of time on it again.
Taikonerd 2 days ago [-]
Hi Maximilian -- nice to hear from you!
I'm sad to hear that about LLMs. I sometimes wonder if the software world is going to be "locked into" our existing languages, because it's what the LLMs can work with.
FWIW, I think the PRQL syntax is beautiful.
maximilianroos 2 days ago [-]
Thanks!
I think at the moment that's indeed the case...
But also maybe that will change — LLMs can learn new languages faster than people, and can _write_ new languages much faster than people. So wide confidence bounds for the future!
jeltz 2 days ago [-]
Matching SQL in features is very hard, especially if you also want to make it more sane and more powerful at the same time while also wanting to be able the generate valid SQL from your syntax. So I am not surprised that it stalled out.
Taikonerd 2 days ago [-]
Sure, but they never intended to support everything you can do in SQL. For example, they say on the Roadmap page that they're only going to support SELECTs -- there won't be a PRQL way to do an INSERT, UPDATE, etc.
jeltz 2 days ago [-]
I was only thinking about SELECT queries when I wrote ny comment because those are the hard things to implement.
andrew_lettuce 2 days ago [-]
When it comes to SQL it's the select that's by far the majority of the work though, the hard work with mutating operations is on the database implementation, not really the syntax or query plan
The title of the submission is literally the first line on the website.
I always find that funny. If you have to provide a pronunciation guide for your product, perhaps consider a different name. I guarantee you’ll still have people pronouncing each individual letter, either because they don’t know or because it’ll be less ambiguous.
chuckadams 2 days ago [-]
For the first half of the 90's I pronounced Linux as "LINE-nucks". Then while he still had a thick accent, Linus told us all how he pronounced it "LEE-nooks".
rgovostes 2 days ago [-]
> I wrote SQLite, and I think it should be pronounced "S-Q-L-ite". Like a mineral. But I'm cool with y'all pronouncing it any way you want. :-)
— D. Richard Hipp
dmit 4 days ago [-]
I mean, as someone who grew up pronouncing it "Ess-Cue-Ell", I wish I learned earlier on that "Sequel" was the intended pronunciation. :)
yhavr 2 days ago [-]
Yes, in Ukrainian/Russian PRQL can be easily read as "prikol" (joke/gag/quirk).
But I guess the best name would be "perkele" (emotional, like "damn") in Finnish.
tremon 2 days ago [-]
I always used ess-cue-ell to refer to the language, and sequel to refer to the Microsoft product. It would never occur to me to pronounce the Open Source alternative as postgressequel either, that's also invariably called post-gress-cue-ell here.
ajuc 2 days ago [-]
Nobody calls it sequel in my country.
Even people who know because then they have to explain it which wastes time for no benefit.
latexr 4 days ago [-]
Which is my point. A better name wouldn’t have had that problem. How could you ever know how it’s pronounced if you bump into it on a blog or social media post instead of the official website? We don’t write “SQL (pronounced “sequel”)” every time, we just write “SQL”.
But even then, it makes sense to choose to pronounce it “the wrong way”. I say “sequelite” because that’s fairly clear in context, but “sequel” might not be so I pronounce each letter in that case.
Did know PNG is supposed to be pronounced “ping”? I don’t know anyone who chooses to do that, even if they know.
randallsquared 2 days ago [-]
I pronounce PNG "ping". Also JPEG as "jay peg" but, counter to the creator's intention, GIF with a hard "g".
WorldMaker 2 days ago [-]
My favorite answer to the GIF pronunciation debate is to prefer the Old English pronunciation from back before the noun "gift" picked up that extra consonant at the end, which Old English pronounced it rather like "hyeef". (Similar to what a "g" will do in some latinx dialects.) That pronunciation splits the difference and makes some of both hard and soft "g" proponents angry, which is to say that pronunciation is also a gift from the past.
WorldMaker 2 days ago [-]
SQL's terrible name is IBM's fault for multiple reasons. They were going to spell it Sequel but IBM's lawyers found another company had a trademark on Sequel and forced them to rename it to avoid lawsuits. Rather than pick a new, more original name they instead shrunk the acronym and then IBM's legal also made them tell people to spell it out instead of call it "sequel" to continue to avoid the other company's trademark. So IBM made sure the name and its pronunciation was always a mixed message from very early in the language's history. Thanks, IBM
(I've always pronounced PNG "ping". It's interesting that there is a split there, I'm not sure I would have expected a large one.)
Avshalom 2 days ago [-]
I continue to pronounce it S-Q-L... and G-U-I; generally I pronounce most things as initialism and I'm right to do so.
2 days ago [-]
infogulch 2 days ago [-]
This looks pretty nice to use!
How does it work if you want to join multiple complex subqueries?
How far can a new query language like this go? Could this be added as a native query language in e.g. postgresql?
bob1029 2 days ago [-]
"Pipelined" SQL already exists in the form of common table expressions. I don't know of any providers where this is not available. SQLite has had support since 2014.
data_ders 2 days ago [-]
I agree that CTEs help solve the problem of being able to read a SQL query from top to bottom, but I wouldn't say they're a panacea!
Personally, it's weird to me that `FROM` (scan) comes after `SELECT` (projection). IMHO the datasource should come first!
CTEs don't solve this problem they just let you chain multiple SELECTs together.
A real use case is that it would allow intellisense to kick in a lot earlier!
Instead you have to write `SELECT * FROM my_table` and only after can you edit the `*` and get auto-complete suggestions of the columns from `my_table`
bob1029 2 days ago [-]
> CTEs don't solve this problem
They kind of do in my head. "WITH" reads to me exactly like the datasource you are looking for.
jdkfjdo 2 days ago [-]
Sometimes I wonder if the only thing needed in SQL is to switch the order of FROM and SELECT. I think that would satisfy many people who are bothered by the syntax.
andrew_lettuce 2 days ago [-]
You might already know this but in relational algebra the select is SQL's from and the projection is SQL's select, which makes more sense. I always preferred the linq syntax with the from first too
cyanydeez 2 days ago [-]
Its not the only thing needed. To do anything, first you need major databases to implement.
2 days ago [-]
hbarka 2 days ago [-]
Procedural language fanatics have been trying for years to overturn the best declarative language for relational data.
ux266478 2 days ago [-]
That would be the domain of logic programming languages like Prolog. SQL and its dialects are more for very specific and restricted applications of relational calculus, not general languages for expression of relations, conditions and categories.
esafak 2 days ago [-]
PRQL is declarative. They are just heeding the maxim "If it's broke, fix it".
otabdeveloper4 2 days ago [-]
Typing fields before table name is like the least bad thing about SQL and doesn't need fixing.
data_ders 2 days ago [-]
what do you think is the "most bad" thing about SQL?
otabdeveloper4 2 days ago [-]
Lack of abstractions like (block-scoped) variables and lambda functions.
SQL is declarative and purely functional anyways, so implementing these is a no-brainer.
Imagine three joins of three queries A,B and C, where first join J1 joins A and B, second join J2 joins A and C and third join J3 joins J1 and J2. Note that I said "queries," not "tables" - these A, B and C can be complex things one would not want or be able to compute more than once. Forget about compute, A, B and C can be quite complex to even write down and the user may really do not want to repeat itself. Look at TPC-DS, there are subqueries in the "with" sections that are quite complex.
This is why pipeline replacements for SQL are more or less futile efforts. They simplify simple part and avoid touching complex one.
I think that something like Verse [1] is more or less way to go. Not the Verse itself, but functional logic programming as an idea, where you can have first class data producers and effect system to specify transactions.
[1] https://en.wikipedia.org/wiki/Unreal_Engine#Verse
> SQL is not a pipeline, it is a graph.
Maybe it's both? and maybe there will always be hard-to-express queries in SQL, and that's ok?
the RDBMS's relational model is certainly a graph and joins accordingly introduce complexity.
For me, just as creators of the internet regret that subdomains come before domains, I really we could go back in time and have `FROM` be the first predicate and not `SELECT`. This is much more intuitive and lends itself to the idea of a pipeline: a table scan (FROM) that is piped to a projection (SELECT).
Yes, there will always be hard-to-express queries, the question is how far can we go?
I haven't seen anyone make the point about graphs before. FWIW PRQL allows defining named subqueries that can be reused, like J1 and J2 in your example.
You present "programs are graphs" as trivial truth. True trivial truths are, as you pointed out, meaningless. But you leave out degree of applicability - information in the dependence graph differs between programming languages.
Dependencies form a graph, and analyses needed to optimize execution of the program graph differ wildly between languages. Look at С++ aliasing rules and C's "restrict" keyword.
One can't escape the dependence graph. But one can execute dependence graph better or worse, depending (pun intended) on the programming language.
It will be around for a long time, there's an infinite number of resources and examples for it and if you ever have to onboard someone into your code they don't need to learn something new. You can get pretty far by just using CTEs to "pipeline".
At the same time, it's challenging that SQL cannot be iteratively improved and experimented upon.
IMHO, PRQL is a reasonable approach to extending SQL without replacing SQL.
But what I'd love to see is projects like Google's zeta-sql [1] and Substrait [2] get more traction. It would provide a more stable, standardized foundation upon which SQL could be improved, which would make the case for "SQL forever" even more strong.
I've blogged about this before [3].
[1]: https://github.com/google/googlesql [2]: https://substrait.io/ [3]: https://roundup.getdbt.com/p/problem-exists-between-database...
Is there even a db vendor that offers full ANSII SQL support? Last I'd checked the answer was no.
The fact that you need to replicate the same complex expressions in multiple values that you select or multiple parts of a where clause is bad enough.
That there’s no way to pipe the result of a query into another query is just adding insult to injury. (Just create a custom view bro).
But if technology competed in quality and not in vendor lock in, we wouldn’t have to deal with C++ or JavaScript.
https://duckdb.org/docs/stable/sql/dialect/friendly_sql
It's not as elegant as PRQL, because of course it's bolted onto the existing SQL syntax, rather than a redesign from scratch. But it has a big name behind it, and it's actually running in prod in Google Cloud... so it might have more momentum.
[0]: https://cloud.google.com/blog/products/data-analytics/simpli...
Indeed we're doing fewer new features (and haven't posted to the posts page in a long time, as you noticed).
But it's still maintained, folks are still using it, if anyone finds bugs in simple-to-moderate queries then we'll fix them.
LLMs probably took a bit of the wind out of our sails for making this "the new standard". But I still think it's a really nice language and interface; if the world changed again such that it became more widely useful, I'd jump to spending lots of time on it again.
I'm sad to hear that about LLMs. I sometimes wonder if the software world is going to be "locked into" our existing languages, because it's what the LLMs can work with.
FWIW, I think the PRQL syntax is beautiful.
I think at the moment that's indeed the case...
But also maybe that will change — LLMs can learn new languages faster than people, and can _write_ new languages much faster than people. So wide confidence bounds for the future!
I always find that funny. If you have to provide a pronunciation guide for your product, perhaps consider a different name. I guarantee you’ll still have people pronouncing each individual letter, either because they don’t know or because it’ll be less ambiguous.
— D. Richard Hipp
Even people who know because then they have to explain it which wastes time for no benefit.
But even then, it makes sense to choose to pronounce it “the wrong way”. I say “sequelite” because that’s fairly clear in context, but “sequel” might not be so I pronounce each letter in that case.
Did know PNG is supposed to be pronounced “ping”? I don’t know anyone who chooses to do that, even if they know.
(I've always pronounced PNG "ping". It's interesting that there is a split there, I'm not sure I would have expected a large one.)
How does it work if you want to join multiple complex subqueries?
How far can a new query language like this go? Could this be added as a native query language in e.g. postgresql?
Personally, it's weird to me that `FROM` (scan) comes after `SELECT` (projection). IMHO the datasource should come first!
CTEs don't solve this problem they just let you chain multiple SELECTs together.
A real use case is that it would allow intellisense to kick in a lot earlier!
Instead you have to write `SELECT * FROM my_table` and only after can you edit the `*` and get auto-complete suggestions of the columns from `my_table`
They kind of do in my head. "WITH" reads to me exactly like the datasource you are looking for.
SQL is declarative and purely functional anyways, so implementing these is a no-brainer.