SQL:2023 has been released (opens in new tab)

Discussed here:

SQL:2023 is finished: Here is what's new - https://news.ycombinator.com/item?id=35562430 - April 2023 (153 comments)

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

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

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

Basic question: is it correct to assume that having PGQ involves a big change in the database engine?

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

As a thought, it might be better to use the https:// link to Peter's overview. :)

And here is the article on the status of SQL:2023 support in PostgreSQL https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-...

I like the DISTINCT / NOT DISTINCT unique NULL option, I was wanting this feature just a few weeks ago

Which SQL DBs support these features now? Who is almost there? I’m definitely excited to try it!

I wonder when or even if MySQL will adopt any of these.

> Is it the standards fault, or the implementations?

My mental model is that it's a mixture, but my life experience has been that a "standard" without a test harness or (at bare minimum) a reference implementation is just a bunch of navel gazing. For SQL specifically, that problem is even worse given the number of existing engines that move faster than the specification, so in the absence of leadership they just make stuff up

Natural language is also a catastrophically horrible specification mechanism, since your black/blue is my white/gold

The way I see it is the fundamental problem is that SQL is not a proper composable language, and the standard never defines such a thing. So every feature added to an RDBMS is done so as an extension to the language, rather than an update to a standard library like any sane modern programming language. SQL as a language still operates with the mentality of COBOL -- if anything reusable is going to be provided, it will be provided by the RDBMS manufacturer. The user is only expected to produce highly business-specific logic for their own needs. (RDBMS's do often offer standard PL language support, but these typically hook into database internals, are intended for highly specific scenarios, cannot be transferred to any other RDBMS, and are generally meant for writing stored procs -- non-reusable functions)

As a result, for databases to compete on features, they must arbitrarily extend the SQL language standard; these modifications to the language then get backfilled into the standard, and runs headfirst into backwards compatibility, and suddenly no one agrees on really what the feature should precisely be so they it becomes an optional part of the standard, which really just means that it isn't standardized.

In any sane language, you wouldn't need different databases to add specific support for GENERATED BY DEFAULT AS IDENTITY, and especially not for 15 different syntax's used in 15 different databases to specify kinda sorta not really the same thing -- it would simply be a function, one you could write yourself, or provided by the standard library. It wouldn't be up to the RDBMS to offer support beyond actual language features -- it'd just be up to you to update your libraries.

SQLite does support auto-incrementing: https://www.sqlite.org/autoinc.html. Is your gripe with the lack of "GENERATED BY DEFAULT AS IDENTITY" syntax specifically?

Luckily pretty much nobody needs the standard documents. It's actually my aim at https://modern-sql.com/ to make the relevant information more accessible — in particular including support-matrices ("Can I Use").

For one part. IIRC, there's 11 "new" parts this year.

Which is even more hilarious, considering the ISO is already being funded by the tax dollars of member countries.

A bit like having to pay a journal to get access to research papers: ridiculous.

they say the Standard contributes to SDG 9...

"Inclusive and sustainable industrialization, together with innovation and infrastructure, can unleash dynamic and competitive economic forces that generate employment and income"

Not clear how an arbitrary levy as this can contribute to an SDG goal. It is the definition of exclusion. A bright new thing can use the $230 to get several good database books.

Surely ISO has enough corporate stakeholders that can defray the cost of running the standardization process.

I have always despised them for putting a paywall in front of the SQL standard - it's been there since at least 2010 and probably quite a bit earlier. The good news is that nobody actually needs to read it since you need DB specific information anyways... but it'd be nice to have access to it (especially when I was working on RDBMS neutral dialect to support multiple backends). Still, if you ever have a question about documentation postgres[1] does an absolutely amazing job detailing everything in its dialect and their dialect tends to be the most "neutral" imo.

1. https://www.postgresql.org/docs/current/index.html

As 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

Or said in a more 2023-chatgpt-jailbreaky kind of way: what urls to avoid in order to not find pirated mirrors?

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

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

Other (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/in...

I’m astounded at the outrage at having to buy them. Producing standards takes time and labor. Editing them takes time and labor. So does posting them and maintaining them. So it’s no surprise that one way to pay for that time and labor is to charge money. I doubt anyone here complaining about buying standards works for free. That other standards-producers post their standards for free does not mean that charging for them is automatically an outrageous business model.

Finally, for the folks who need professional access to this document, such as RDBMS implementors or professional developers using an RDBMS, the price is a pittance. To anyone for whom the price is a problem - perhaps someone writing a free software application - the lack of access to the standard isn’t a problem, because what they really need is documentation on how their implementations work, and two superb implementations - PostgreSQL and SQLite - have voluminous documentation and they’re 100% free of charge.

There is a serious cottage industry around standards (not just ISO). Selling the standards themselves is part of an overall architecture for selling "certification" of compliance with the standards and ancillary consulting and training services and, in some instances, selling standard-compliant solutions.

While obviously standards don't have virgin birth (some costs are involved) it does smack as rent extraction: By definition there is only one standard so you can't ask for competitive pressures to lower the price.

Selling the standards documents.

Is 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

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

I can say that none of Oracle, Sybase, or Microsoft Sql Server really aim at conforming to the standard. While they will often try to use standard syntax for new features if such syntax exists, there is tons of old non-conforming syntax that there seems to be no real effort in addressing, even by adding new options, etc. Some of these mean really common features deviate significantly from what the standard requires.

PostgreSQL does mostly aim at conforming to the standard. They will invent new syntax when needed, But compared to the those previously mentioned, Postgres seems to prefer to stick closer to the standard whenever possible, including adding standard syntax for existing features whenever possible.

PostgreSQL does have some places where there is deliberate non-conformance (beyond just incompletely implemented features). They document many deliberate deviations (other than unimplemented or partially implemented features) and if they think they can be fixed in the future or not: https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard . Looking at the list I'd say only one especially likely to bite a developer is the default escape character for LIKE clauses, or the non-standard trailing space behavior for character(n) datatypes (but who used fixed length character datatypes instead of varchar or text?). And obviously not yet implemented features could bite people, but many such features are optional to implement anyway, so...

I cannot speak about MySQL or MariaDB, due to insufficient familiarity.

This is one of the questions I try to answer at https://modern-sql.com/

Good one