8 ms·
SQL:2023 has been released
- gatvol 3y agoNot a standard if access requires payment.
- blacklion 3y agoWhat do you think about all MPEG series and WiFi? You need to pay to read, you need to pay to implement, even if it is clean-room implementation. Ridiculous.
- MarkusWinand 3y agoThe major news are: - SQL/PGQ - A Graph Query Language - JSON improvements (a JSON type, simplified notations) Peter Eisentraut gives a nice overview here: https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-finished-here-is-whats-new https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-fin...
- wslh 3y agoBasic question: is it correct to assume that having PGQ involves a big change in the database engine?
- zozbot234 3y agoIt's just a different language and a simple "property" layer over the existing data. No changes to the internals are necessary.
- WorldMaker 3y agoNo changes are required by PGQ but some engines may wind up using PGQ as a signal to bring in new types of indexes or disk storage formats to back it. It may be interesting to see if real world usage of PGQ pushes the database engines to do interesting things.
- pphysch 3y agoAFAICT the idea is that you are not directly querying the tables as a graph, but you construct a graph "view" from the tables, and then query that graph using PCQ.
- zubiaur 3y agoCorrect. The one RD I know that has implemented this "graph view" concept is Oracle's. They did it first with PGQL extensions on top of say 19c. Interesting, but the execution seemed a bit unpolished. Now with 23c, they are adopting PGQ and one should be able to interface through the regular JDBC connectors. Will see how it shapes up.
- justinclift 3y agoAs a thought, it might be better to use the https:// https:// link to Peter's overview. :)
- MarkusWinand 3y agoFixed. I wonder why Google sent me to http...
- bokchoi 3y agoThanks! Lots of little neat improvements in there like accessing JSON values using dots and array syntax: SELECT t.j.foo.bar[2], ... FROM tbl t ...
- Zpalmtree 3y agoI like the DISTINCT / NOT DISTINCT unique NULL option, I was wanting this feature just a few weeks ago
- MarkusWinand 3y agoThat particular one is already available in PostgreSQL 15. https://modern-sql.com/caniuse/unique-nulls-not-distinct https://modern-sql.com/caniuse/unique-nulls-not-distinct
- sverhagen 3y agoI'm a pretty average SQL user, but I've heard expert consultants say before that they could do many more things with SQL databases that developers like me would have maybe grabbed a different tool for, like a graph database. So this then makes me wonder, once there's even broader adoption through PGQ, is that going to be a killer for niche databases like Neo4j, in favor of, say, Postgres?
- pphysch 3y ago"Kill" is a strong word, as Postgres's solid JSON support technically obsoleted MongoDB for most use cases, but Mongo is still around for various reasons. I suspect if Postgres had a solid implementation of SQL/PCQ it would be a similar story for Neo4j.
- derefr 3y agoGraph databases are about as different from RDBMSes storage-wise, as column-stores are from row-stores. It comes down to how you plan to shard data and distribute queries when data doesn't fit on a single node. Using a graph DB with many underlying KV-store nodes, you can have a single graph spread over many machines representing e.g. Facebook's social graph, and run a query which "chases around" edges between vertices that live on different nodes, to solve that query, while ensuring that as little of that has to happen as possible — both by rebalancing vertices so that data is sharded at low-connection-degree points in the graph; and by consolidating the steps of queries that occur on the same node into single batch queries, such that the whole thing becomes (close to) a single map/reduce step. There's nothing in Postgres that knows how to do that; if you had e.g. a graph stored in a Citus hypertable, and did a recursive CTE over it to do graph search, then you'd get pretty dang bad perf.
- MarkusWinand 3y ago> It comes down to how you plan to shard data and distribute queries when data doesn't fit on a single node. A problem everbody would love to have but pretty much nobody actually has.
- 3y ago
- pphysch 3y agoSee also [1] for how this (might) relate to PostgreSQL In particular it is nice to see that a core dev views JSON dot accessing and PCQ as "sensible" future additions to Postgres. [1] - https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-sql-2023 https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-...
- thanatos519 3y agoPGQ looks neat - create a "property graph" from a relational model, then query it via Cypher-like expressions. The best or the worst of both worlds, depending on implementation quality.
- ksec 3y agoI wonder when or even if MySQL will adopt any of these.
- dang 3y agoDiscussed here: SQL:2023 is finished: Here is what's new - https://news.ycombinator.com/item?id=35562430 https://news.ycombinator.com/item?id=35562430 - April 2023 (153 comments)
- bionhoward 3y agoWhich SQL DBs support these features now? Who is almost there? I’m definitely excited to try it!
- mariuz 3y agoAnd here is the article on the status of SQL:2023 support in PostgreSQL https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-sql-2023 https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-...
- justinclift 3y agoUgh, it's CHF 208.00 (about US$230.00). --- As @rgbgraph points out below, the price is actually several times that. There are several parts to the standard, and that US$230 is per part.
- MarkusWinand 3y agoLuckily pretty much nobody needs the standard documents. It's actually my aim at https://modern-sql.com/ https://modern-sql.com/ to make the relevant information more accessible — in particular including support-matrices ("Can I Use").
- sosodev 3y agoI've certainly needed them before :(
- chrismorgan 3y agoI’ve never had access to the SQL standard, but in things like HTML and CSS I know I reach for the specs extremely regularly, and that when working with SQL (mostly PostgreSQL or SQLite, including sometimes having to support both) I get frustrated at the general poor state of SQL documentation (and certainly a lot of this is because of engine diversity and), and have often wished I had access to the SQL Standard (even though I know engine diversity significantly lowers its value for users of particular databases—my ideal would probably be a version of the spec augmented with engine support and links to each engine’s documentation of the matter). Certainly the table of contents for this spec sounds delightful. Not all specs are particularly accessible (e.g. ECMAScript is often hard to follow if you haven’t spent a fair bit of time around it—it’s mostly natural-language code that works with a ECMAScript virtual machine), but most of the time, I would much rather have the actual spec over someone’s digested summary that covers what they thought was important, but regularly lacks details important for my situation. Some specs are absolutely magnificent. The HTML Standard is my very favourite as both a piece of spec work and as reference material. Seriously, specs are really good stuff and it makes me sad how people often ignore them because they assume they’ll be inscrutable. (Similar remarks apply to legislative texts. They’re normally pretty easy to understand, and you find all kinds of sometimes fun and sometimes useful gems that potted summaries exclude.)
- la_fayette 3y agoPGQs match syntax seems interesting and reminds me to writing sparql. I wonder if any RDBMS will support this?
- sdflhasjd 3y agoAh, a sequel for SQL
- Alifatisk 3y agoGood one
- xucheng 3y agoA related question: what is the state in term of supporting the SQL standard among the popular RDBMS? It seems that almost all database engines use their own custom syntax.
- MarkusWinand 3y agoThis is one of the questions I try to answer at https://modern-sql.com/ https://modern-sql.com/
- bafe 3y agoYour website is great and I regularly check it to see what's new in various implementations. Unfortunately it seems that many databases don't support many modern SQL features yet. Any ideas as to why?
- mdaniel 3y ago> Unfortunately it seems that many databases don't support many modern SQL features yet. Any ideas as to why? I'd guess the incentive structure is the opposite of what you're implying; the same reason why every cordless drill manufacturer has their own battery connector: vendor lock in fuels private planes and shareholder reports, versus being compatible means no one is forced to buy your batteries and thus profits are `$total - $forced_purchases` This situation gets even worse in the case of a standard without any objective way of knowing one is in compliance. Having a badge on the mysql.com website saying "now featuring SQL:2023 compliance!11" sells how many more support contracts exactly?
- bafe 3y agoThat's a good point. Additionally, it seems the standard isn't freely available and I doubt most of the developers of existing SQL DBs partecipate in drafting new standards. It seems it is doomed to diverge even further, which begs the question whether is it relevant anymore to have the SQL standard at all
- 3y ago
- tofflos 3y agoSeems you can play with SQL/PGQ at https://blogs.oracle.com/database/post/get-started-with-property-graphs-in-oracle-database-23c-free-developer-release https://blogs.oracle.com/database/post/get-started-with-prop....
- nologic01 3y agoIs Oracle's PGQL (e.g. 2.0) more or less the same as SQL/PGQ? It might be interesting to have a comparison of where major databases stand (or plan to be) with respect to SQL/PGQ
- awestroke 3y agoIf only they could start allowing queries to begin with "FROM tbl". It would allow for for much more helpful autocomplete. Also, DELETE or SELECT should really be on the very last line of the query. Seems like these changes could be done without losing backwards compat
- calvinmorrison 3y agoAs in. If only sql was actually writable or intuitive you'd want to use it more and instead I just reach for wrappers 99% of the time where I can chain all the operations I want together and let Eloquent figure it out
- bafe 3y agoAll great features, but unfortunately most SQL DBs still miss the implementation of features from SQL:2016 like MATCH_RECOGNIZE. I wonder what's the purpose of an ever growing standard when most implementations only support a small subset of it, and often with nonstandard syntax and semantics
- hashhar 3y agohttps://trino.io/docs/current/sql/match-recognize.html https://trino.io/docs/current/sql/match-recognize.html
- bafe 3y agoGreat thanks, if I understand it right, instead of having a new database engine, Trino compiles the statement into the query languages of the different backends and runs these queries in a distributed way?
- hashhar 3y agoExactly. It provides an API using which you can build connectors to whatever systems you want. e.g. Here's a connector for GitHub API https://github.com/nineinchnick/trino-rest/tree/master/trino-rest-github https://github.com/nineinchnick/trino-rest/tree/master/trino.... It doesn't have to be SQL based systems on the other end - the most used connector with Trino is to query files on object storage (S3/GCS/Azure Blob). Disclaimer: I'm one of the maintainers of the project.
- jchw 3y agoOne thing that has always agitated me about SQL is that although it's standardized, and the standard seems to encompass a shit-ton, in practice a lot of SQL engines don't really seem to have any meaningful interoperability for practical uses among the world's most popular database engines. For example, OK, I realize auto-incrementing IDs are not the most important thing in the world, and arguably not even a good approach in many cases. But sometimes you want them, and helpfully almost every database engine I know of has some kind of support for this, even if the semantics may differ. It's a super basic thing to want a unique ID that roughly counts upward on a table. You might have specific needs about re-using numbers and whatnot, but the general idea is very simple. However: in practice, there is not an excellent way to do it that I can see. The closest thing I could find is `GENERATED BY DEFAULT AS IDENTITY` which, well, works. However, none of SQLite3, MSSQL, nor MariaDB support this to my knowledge. This is relentlessly annoying. Is it the standards fault, or the implementations? I honestly can't say. However, I definitely find this annoying, since I was really hoping that by this time, we'd at least have a nice clean subset of standard SQL you could count on anywhere, for popular database engines. Unfortunately, it's not quite there yet, necessitating ugly hacks to this day. I assume this new standard doesn't really change anything on this regard, since it's a desync with implementations that is a problem, and it does not seem the standards committee really cares too much about this kind of thing. (I could be wrong, though, as I am saying this based on feel and not evidence.)
- dmux 3y agoSQLite does support auto-incrementing: https://www.sqlite.org/autoinc.html https://www.sqlite.org/autoinc.html. Is your gripe with the lack of "GENERATED BY DEFAULT AS IDENTITY" syntax specifically?
- richardwhiuk 3y agoThe gripe is that there's not a standard say of defining this in SQL. SQLite SQL != MySQL SQL != ISO SQL
- jchw 3y agoYes, sorry. My gripe is that we don't have a single general way to say "I don't really care about the specifics, I just want a number that goes roughly upward" for all database engines. SQLite is interesting in that it supports at least two distinct sets of semantics for auto-incrementing rows, but neither of them are the standard syntax...
- gigatexal 3y agoIt really is utter bullshit that we have to buy these standards. What are the business models of these standards bodies anyway?
- jacobsenscott 3y agoSelling the standards documents.
- skissane 3y agoOther (computing industry) standards bodies make their standards freely available–e.g. ECMA, W3C, OMG, The Open Group, IETF, etc. Given they can't make money from selling the standards documents, they need to find other funding sources. Many of them rely on corporate patrons who fund the standards process, and in exchange often get greater input into moulding the standard to meet their interests. The Open Group makes some of its money by selling trademark licenses and proprietary test suites. ISO defends its own model by arguing that it produces greater independence from vendors, being less reliant on them for funding and thus making it easier for them to say "no" to them. One big difference is ISO is not an industry-specific standards body, it has standards for all kinds of things that have nothing to do with computers – screw threads, metallurgy, analytical chemistry, oil and gas pipelines, you name it. Possibly their defence makes more sense for some of those other industries than it does for ours. If the major SQL players got together (major proprietary vendors and leading open source projects), they could create their own SQL standards process to supersede the ISO one, and release the standard freely. The ISO standard could still exist, but it could turn into one of these ISO standards where ISO just adopts the text of an existing standards organisation - e.g. the ECMAScript standard is developed by ECMA. Initially ISO republished ECMA's standards under their own number (ISO/IEC 16262), now instead they publish a 3 page standard which just incorporates ECMA's by reference (ISO/IEC 22275). They offer it for free download [0], but are also willing to sell you a copy if you are desperate to give them money. ISO's SQL standard could turn into the same thing – but, that would require the SQL community to decide to push for that, and I'm not sure any of the players feel sufficiently motivated to do it. [0] https://standards.iso.org/ittf/PubliclyAvailableStandards/index.html https://standards.iso.org/ittf/PubliclyAvailableStandards/in...
- cpdean 3y agoI like SQL and all but I really don't care to follow ISO releases. They're hundreds of dollars and nobody actually implements the whole thing. I get way more excited about database releases. Does anyone else find value in what's in an ISO standard?
- lolinder 3y ago> They're hundreds of dollars This isn't SQL-specific, but this is 100% the problem for me. There's such a big culture gap between the way that we do things in most of the tech world and ISO, and one of the biggest clashes is this weird $180 PDF thing. If I want to implement a new standards-compliant HTML parser, I can hop right onto whatwg.org and view the complete standard instantly [0]. It's massive and complicated, but it's freely accessible to anyone interested. In contrast, if I want to implement an ISO 8601-compliant date parser, ISO wants me to buy their PDF for CHF166 (~$180 USD). This spec is for a standard that is orders of magnitude less complex, and they're charging through the nose for it. I'm unclear what makes the difference between a standard that can be maintained by a community for the benefit of everyone and a standard that needs to be locked behind a paywall. [0] https://html.spec.whatwg.org/ https://html.spec.whatwg.org/
- rizky05 3y ago[dead]
- chillfox 3y agoA pay-walled standard is not available, and an unavailable standard is not a standard at all. The only real way of fixing it is for enough people to ignore ISO so they become irrelevant. If you are building a new DB engine (toy or not), don't use SQL. Either design a new spec or use something that's more openly specified (maybe GraphQL or EdgeQL).
- aerzen 3y agoWhere would one find a pirated mirror of this standard? Or the 2019 one? Asking for a friend, of course.
- jeppebemad 3y agoOr said in a more 2023-chatgpt-jailbreaky kind of way: what urls to avoid in order to not find pirated mirrors?
- minroot 3y agoWhat's the point of a standard if it takes money to read?
- blacklion 3y agoWhat do you think about all MPEG series and WiFi? You need to pay to read, you need to pay to implement, even if it is clean-room implementation. Ridiculous.
- galaxyLogic 3y agoThe irony of for-profit (= for pay) standard is this: If someone provides a product for a price, then others should be able to produce a similar but better product for similar or better price. In the case of SQL that would mean there should be alternative standards provided by different vendors. But then if there are multiple standards, it can hardly be called a "standard". I think tax-payer money should pay for standards, because they benefit us all. It is like the highway system, or clean air, and water.
- qalmakka 3y agoAs much as I try understanding it, I don't see the point of this standard honestly. It sounds like some weird fanfiction made by delusional people who think SQL is actually a single language and not a hodgepodge of incompatible dialects. It makes no sense to have a standard SQL when nonsensical implementations like MSSQL or MySQL exist.