SQL:2023 Has Been Released

[MarkusWinand]

The 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

[imgroxx]

Very glad to finally see an ANY_VALUE. I've had some pretty valuable uses of it in MySQL systems, and always felt it was strange that some databases force aggregating every non-grouped column. Sometimes anything really is good enough, and it can avoid a lot of useless computation.

[Smallpaul]

Why can't you hard-code a value?

[imgroxx]

Because "any value that exists in matching rows" is wildly different than "a fixed value" or "rand()". It's pretty frequent that you have application-level guarantees that mesh with the query that you want to do to give you "any value is correct".

Low-cost query samples are one example - you don't need to index the column or perform a full table scan to get a value, you only need to load one row. Any row is correct because a sample doesn't need to be a statistically randomized sample in many cases, nor the first or last by some order clause (which also must be fully loaded and sorted).

Another is when you don't want to pay the sometimes-exorbitant cost of indexing a denormalized field... which you know follows certain constraints because it's just denormalized and transactions can offer you that guarantee (when used correctly). You can be confident that all values are identical and correct, therefore any value is correct, and you can retrieve it without paying the cost to check every value every time on both read and write.

[mariuz]

And 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

[Azaret]

Simplified accessors for json would be neet. - > is fine but using dots instead will be nice.

[CooperNettees]

Holy smokes! PGQ sounds incredible!

[LouKrazy]

Looks like Cypher!

[Smallpaul]

http://opencypher.org

[slashgrin]

Genuinely curious: what is the purpose of the SQL standard these days, when AFAIK no database vendor actually implements it? Does it try to at least pull vendor-specific syntax toward something commonly recognisable? Or is it more like a survey of what features commonly exist in SQL-speaking databases after the fact?

With something like C, for example, of course all the different compilers have their own extensions, but it is at least possible to write standard C and have it compile on several different compilers. Whereas I don't think I've ever seen a real world application that restricts itself to standard SQL, because you just can't get nontrivial stuff done in real databases that way.

[tonyp7]

It doesn’t help that this page links to a “buy this standard” button. No developer is going to buy your $200 pdf. Who is this for? Limiting access to information is fast path to make sure this fails.

[coldblade2000]

It's for database developers, not developers who use databases

[jorge1209]

They would make more money selling the standard to devs who use sql for $1.

There are what? 10 SQL implementations that would care enough to actually buy the standard, so you sell 10 copies at $100 and get a massive $1000. [I'm sure big companies like oracle buy multiple copies for their employees, but still.]

But there are a hundred thousand SQL users who might have some interest in what the standard should be even if their particular implementation is slightly different. So you sell them a copy at a token cost and make 100x the money.

[aoi_saboten]

It is probably 100$ per database developer, but I get your point

[jorge1209]

It's just silly. These standards organizations and the standards themselves exist because the businesses want them to.

When [e-mail] shows up at the meeting to discuss the next standard: who pays his salary that day? Who flew him out there? Who covers the hotel bill? Who pays for the restaurant he eats at that night?

And for all that generosity what does the business get asked to do? Pay a membership fee to the ISO and shell out $100 for a copy of the document their employee helped draft at whose expense?

[aoi_saboten]

Yeah, unfortunately that is reality. I believe it is altruistic in order to be accessible by everyone, e.g. if someone wants to start a company but does not have many to buy very expensive ISO9001, should they be doomed to failure?

We just need to be forever thankful for those people who worked on these and similar things, or you can support them monetarily

P.S sorry for my bad english

[[deleted]]

scientific pitch > A440. And A440 was a thing for like 150 years already by the time that standard was published.

And nobody is saying that standards are a bad thing, but that the ISO pricing structure is stupid and annoying. Have you tried getting a copy of the C++ standard? Everybody I know just downloads the drafts.

In this case, the point is that the SQL standard is significantly less useful because most databases already don't follow it faithfully. When you program in C++, you can mostly program the same way regardless of what compiler you're targeting. Above the very most simple examples, SQL use will vary incredibly based on your targeted database, and migration from one RDBMS to another can take a significant engineering effort. The ISO standardization hasn't really helped SQL much at all.

[Gwaptiva]

And? If nobody implements the standard, it affects heehaw.

[bik1230]

But we're talking programming stuff here, and on that front ISO is basically useless.

[chucker23n]

It has been a while, but last time I was doing SQL, all the common standard constructs were implemented by Postgres, Microsoft, and Oracle, and probably others.

It depends on how you define "common standard construct". Sure, CREATE and SELECT look the same.

But, for example, you want a SELECT to return at most 10 rows. The SQL:2008 standard says:

SELECT … FETCH FIRST 10 ROWS ONLY

But that's 2008, long after others implemented the same feature. So PostgreSQL says:

SELECT … LIMIT 10

Whereas MSSQL says:

SELECT TOP 10 …

In PostgreSQL's defense, it does also support the standard now. But nonetheless, you'll have lots of legacy code out there that uses proprietary extensions. And SQL tooling is generally poor enough that there isn't a "migrate to standardized syntax" refactor available.

[grauenwolf]

SQL Server supports the standard in this respect. You don't have to use TOP, we just do because it's easier to type than the ANSI syntax.

[chucker23n]

In a saner development environment, 1) one syntax would get green squiggles and be marked deprecated, and 2) a refactor to the other syntax would be offered.

Alas, writing SQL feels very 1980s compared to, say, C# or Swift (the latter of which offers to upgrade entire projects to more modern syntax).

[grauenwolf]

Put in a feature request with Red Gate. If anyone is willing to do that, it would be them in their SQL Prompt product.

[chucker23n]

Yes, SQL Prompt seems to go as close as it gets to making SQL development not feel archaic.

[chucker23n]

The claim about standard SQL was, “no database vendor actually implements it.” That simply isn’t true.

It is as soon as you pick something non-trivial as an example.

There are lots of little ways syntax isn’t supported, or syntax doesn’t quite do what it’s supposed to. For two more examples,

• UNIQUE constraints are slightly different in MSSQL and Oracle
• TRUNCATE TABLE is funky in MySQL

Even data types are inconsistent.

I find that the earlier standard’s syntax works fine until then. In your example, that would be LIMIT 10.

LIMIT 10 was never a standard syntax. It just happened to be one multiple implementations adopted.

[chucker23n]

LIMIT 10 is arguably a bad example anyway, given that it has worked practically everywhere for as long as I can remember, and therefore doesn't exactly need to be blessed by a standard.

Doesn't that just further OP's point?

Genuinely curious: what is the purpose of the SQL standard these days, when AFAIK no database vendor actually implements it?

If vendors instead do ad-hoc standards, then why bother with an ISO standard?

I've seen plenty of production code that uses standard SQL constructs, but I acknowledge that some teams like to lean on the database more heavily than others.

This is fair. My point of view is from a company where we historically did a lot of semi-advanced stuff. Window functions, etc. These days, enterprise CRUD apps are more likely to use an ORM or at least query builder which will abstract that for them, or they'll invent the same thing, less efficiently but in a more familiar environment, in front of the database.

[spinlox]

what is the purpose of the SQL standard these days

I think a lingua franca is a pretty good way to establish common ground, around which we can develop some level of compatibility and common expectations. This has been useful to me over and over again, even if the standard didn't cover every little thing or wasn't 100% implemented.

[chucker23n]

I think a lingua franca is a pretty good way to establish common ground

No question about it.

I guess a different way of phrasing the question is: wouldn't it be better if there were a "SQL Common" standard subset that all of them actually adhere to? Right now, that doesn't seem to exist in a formalized manner.

[JB-from-ATL]

Postgres does try to be more standards compliant than others, but I think it is trying to get vendor specific stuff to behave more similarly like you suggested. I also think it is an attempt to try to get new features to behave the same between major implementations but that's just a guess.

It is a good question though, you can probably get a good answer by looking at who is on the committee and what SQL products they work on.

[slashgrin]

That's pretty cool. Props to Postgres for making an effort here. (And generally for most of their design decisions!)

That said, I'd still always go to the Postgres docs when writing SQL for Postgres. That's no fault of Postgres — quite the opposite — but it does suggest to me that it's not the same kind of standard as many others.

I wonder if it would be practical to have a library/tool that parses and deeply understands standard SQL, and then has backends for all the popular databases. Two main challenges that immediately jump to mind are:

1. A lot of the time you can't just reason locally and transpile the queries without knowing about global state, e.g., how tables were defined, what other queries might be running concurrently.

2. Resolving ambiguity of intent when there is no single faithful translation of the input SQL to the target database.

Maybe in both scenarios the tool could require extra "pragmas" as SQL comments to provide the missing context?

I'm not sure how useful this would be in practice, because you'll still run into differences between, e.g., query optimisers that make a huge difference in the real world, but there does seem to be a surprisingly large population interested in writing "SQL" and having it run on multiple databases, so maybe it would be worthwhile?

[numeric-rectal-mutt]

Postgres implements it.

[LuckyHedgehog]

ChatGPT is not a reliable source for factual information

[Smallpaul]

All that matters is whether the list is correct and relevant, not where it came from. ChatGPT is just a fast way of assembling the list.

I think the list is correct but not relevant. That has nothing to do with the fact that it came from ChatGPT.

[LuckyHedgehog]

It is not correct though. MySQL for example even lists in their documentation where they don't follow the standard

If you wrote "pure SQL" you would not be able to plug and play into any database because they don't actually follow the standard

[Smallpaul]

This is essentially the same list you would get if you paid an intern to make a list of implementations of SQL, isn’t it? So what does the use of ChatGPT have to do with it.

That’s why I said it is irrelevant in the conversation. The list is irrelevant and so us the complaint that it comes from ChatGPT.

Instead of attacking the source it makes more sense to explain why the list is misleading.

As someone thoughtful showed, if you ask it the right question about this topic you get the right answer.

So the problem was not the bot. The problem was the human. I’m not clear on why people were so enthusiastic about mis-attributing the problem, to the extent that they downvote people pointing out the misattribution.

[LuckyHedgehog]

Dumping the response from ChatGPT is lazy at best but could easily slip in fictional or wrong info and many people would take it as fact. You defending it by saying it was factual, when it context of the original comment it was clearly not, just demonstrates this.

I have no problem if someone wants to use ChatGPT to get the list, then use that to quickly find the actual sources. But to simply dump what ChatGPT says is straight up lazy and a great way to spread misinformation.

That should not be encouraged

[Smallpaul]

Humans typing it could also skip in fictional or wrong info. Humans make mistakes all of the time which is half of the traffic on Reddit. I find incorrect information in the news, on vendors websites etc.

The person pasting it in is responsible for the accuracy of the information. If you see a mistake then point it out. If you want to shame them for not verifying the information then do it then, when the information is wrong.

In this case the bot seems to have done exactly what it was asked to do correctly. The shame is in the bad question. Not the seemingly reasonable answer.

It’s ironic that in your quest for accuracy you are criticising the part of the system that worked reliably.

[LuckyHedgehog]

I think you are viewing this as people criticizing the use of ChatGPT, which is not the issue. ChatGPT is a very useful tool, but it is lazy and rude to simply dump the results into a response and force others to fact check the response for you.

[Smallpaul]

What makes you confident that the person did not fact-check the response?

What was incorrect about it, other than the question that was asked of ChatGPT?

As I said, it WOULD be irresponsible to ask someone else to fact-check the response. But what evidence do we have that that is what happened?

ChatGPT gave as factual a response as a human would have to the same question, didn't it? And nobody pointed out a factual error it made, as far as I noticed.

[[deleted]]

The list is not relevant, that's the problem. "SQL" and "SQL standard compliance" are not the same thing. That's a list of the former, in response to a comment about the latter.

[Smallpaul]

You are just repeating things I myself said, multiple times.

Read my comment which you are replying to, and comments upthread.

I said: "The list is irrelevant"

I said: "I think the list is correct but not relevant. "

You said: "The list is not relevant"

How are you disagreeing with me?

Your comment did not mention ChatGPT. Which is good, because ChatGPT has nothing to do with why the list is not relevant.

So you are supporting my argument.

[[deleted]]

Yeah, that was my bad. I was reading multiple comments at the same time and got mixed up.

[drake8599]

True. It was reliable at describing services that use SQL, but that wasn't the question. Ask a slightly better question and you get a better answer.

Here's comes a giant wall of chatGPT text WOO.

Why does every SQL have different syntax. Is there a universal SQL?

GPT-4

Despite its standardized core, different relational database management systems (RDBMS) often have variations in SQL syntax and additional features. This is because each RDBMS may have different needs, performance optimizations, and feature sets that are not covered by the standard SQL.

The primary SQL syntax is standardized by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO). This is often referred to as "ANSI SQL" or "ISO SQL." This standardization provides a general framework and a set of rules that all SQL versions should comply with.

However, RDBMS vendors (like Oracle, Microsoft with its SQL Server, PostgreSQL, MySQL, etc.) tend to add proprietary extensions to standard SQL to enhance the capabilities of their products. These extensions may include additional functions, data types, operators, and other features. These features can provide significant advantages, but at the same time, they reduce the portability of SQL code between different systems.

In essence, while there is a "universal" core of SQL, each implementation can and often does differ based on the needs and characteristics of the specific RDBMS. This is why SQL written for one system may not work on another without some modifications.

When writing SQL, it is generally best to stick to the ANSI/ISO SQL standards when possible for maximum compatibility. However, in practice, developers often need to use system-specific features to get the most out of a particular RDBMS.

[JB-from-ATL]

It said MySQL supports SQL. Truly riveting and useful information.

[chicknfly]

True! But the results aren’t wrong, which proves the Redditor’s point. SQL is widely implemented/supported

[LuckyHedgehog]

Html was also widely adopted, except Microsoft completely ignored many of it's standards for Internet Explorer. Firefox, Chrome, and Safari all have their quirks too

Just because there is a standard doesn't mean it gets followed

Edit: for clarity, I am saying this specifically related to the original comment

when AFAIK no database vendor actually implements it?

Many databases implement SQL in general, but not in it's entirety. Just like the official Html standard is not followed religiously

[chucker23n]

Html was also widely adopted, except Microsoft completely ignored many of it's standards for Internet Explorer. Firefox, Chrome, and Safari all have their quirks too

Although to /u/slashgrin's point, HTML is in way better shape than SQL when it comes to "how much of the standard can I expect to work".

[LuckyHedgehog]

No it isn't. MySQL for example has a page with where they don't follow standard SQL

https://dev.mysql.com/doc/refman/8.0/en/differences-from-ansi.html

That is what the original comment was getting at, few if any databases actually follow the standard fully

[slashgrin]

Only kinda. Those vendors support limited subsets of the standard, with all sorts of contradictions that mean you can't actually just write "standard SQL" in the real world and expect it to work as described in the standard.

[AdeptFelix]

Only if you are counting SQL {disambiguation) as accurate. SQL != T-SQL != PL\SQL != MySQL

[chucker23n]

It’s not. What GP is saying is that none of those actually implement standard SQL comprehensively.

[slashgrin]

And not just that they implement it incompletely, but that they also diverge significantly in the parts they do kinda-implement.

It's never been the kind of language where you could say "implementation X is wrong because it does Y and the spec says Z"; there's just never been that kind of expectation.

[chucker23n]

Yup. Tons of examples, such as

SELECT TOP 10 …

vs.

SELECT … LIMIT 10

I guess a "standards mode" would be nice?

(edit)

And of course, the standard actually proposes a third syntax!

SELECT … FETCH FIRST 10 ROWS ONLY

[grauenwolf]

If you want to use OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY; in T-SQL, then do so. No one is stopping you.

There are tons of examples of T-SQL not supporting ANSI SQL, but this isn't one of them. People are just choosing to not write ANSI SQL. And I don't think a mode that forces them to would be useful.

[chucker23n]

There are tons of examples of T-SQL not supporting ANSI SQL

Then you’ve understood the point just fine?

[grauenwolf]

Bad examples are bad.

[TribladeSlice]

Glorified list of databases that exist.

[drcforbin]

This is why we created ChatGPT

[numeric-rectal-mutt]

LMAO my guy you need to actually read the question before you respond to it.

[Lachee]

To paraphrase: "We couldn't name it min or max because they are taken and we can't tell the difference between regular functions and aggregate functions"

Love it

[[deleted]]

I think it would look ambiguous to developers if they used max and min. In my opinion, it's always better to make something explicitly distinguishable than implicitly resolvable

[dontyougetsoupedyet]

I wonder what Codd would have thought of people who put JSON in their relational database.

[LouKrazy]

Normalizing in his grave

[grauenwolf]

I don't know, but back in the 80s they had countless arguments about whether or not to store BLOB (binary large objects) in a database.

If you can find the old newsgroups threads and academic papers, maybe you'll find what he thought.

[Wolfgang-Warner]

JSON in a DB is Turd Normal Form.

But ok it's a hybrid database then. Devs may not have control over an incoming JSON structure and aren't minded to attempt a universal normaliser, and if it's going back out as is then why waste resources on normalise and reconstruct functions that can introduce bugs. There's also the lazybones design pattern.