13 ms·
SQL:2023 is finished: Here is what's new
- mistersys 4y agotitle is confusing. SQL announces: 2023 is finished, on April 13th, 2023.
- mananaysiempre 4y agoIt’s the space after the colon in the submission title (was that the infamous HN title normalizer?). The original has SQL:2023, which makes sense if you know that the ISO naming scheme introduces the year with a colon (e.g. “C99” is officially ISO 9899:1999, “C95”—that is ANSI C plus wchar.h and wctype.h—is ISO 9899:1990/Amd 1:1995).
- osigurdson 4y agoIt made perfect sense to me at first but now that you mention it, the title is quite funny. I guess if "SQL" says 2023 is over, who are we to argue - happy new year everyone!
- rjbwork 4y agoCool. Let's see if Postgres will decide to implement this one. We're 12 years on from 2011 with no implementation.
- yobert 4y agoSome of this has already implemented in PostgreSQL 9.6! lpad / ltrim, for example.
- paulddraper 4y agoIt's practically ripped straight from PostresSQL documentation. PostgreSQL already supports: * ORDER BY in grouped table (F868) * GREATEST and LEAST (T054) * String padding functions (T055) * Multi-character TRIM functions (T056) * Optional string types maximum length (T081) * Enhanced cycle mark values (T133) * JSON data type (T801) * Enhanced JSON data type (T802) * String-based JSON (T803) * SQL/JSON simplified accessor (T860–T864) The upcoming release of PostgreSQL 16 will have: * Non-decimal integer literals (T661) * Underscores in numeric literals (T662) PostgreSQL currently lacks: * UNIQUE null treatment (F292) * ANY_VALUE (T626) * Hex integer literals in SQL/JSON path language (T840) * SQL/JSON item methods (T865–T878) * JSON comparison (T879–T882) - though the nonstandard JSONB has this * Property Graph Queries --- As with many standards, the SQL standard likes to include things that DBMSs already have in the wild and have found practically useful.
- demurgos 4y agoPostgres has support for specifying NULL behavior in UNIQUE constraints since version 15.0 (released in 2022), with the same syntax as described in the article. See the end of the documentation on unique constraints: https://www.postgresql.org/docs/15/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS https://www.postgresql.org/docs/15/ddl-constraints.html#DDL-...
- paulddraper 4y agoOoh, fancy I'm out of date :)
- deleted 4y ago[deleted]
- masklinn 4y ago> SQL/JSON simplified accessor (T860–T864) Where is the doc for this one? I can find Postgres’ own JSON(B) operators, as well as jsonpath (sql/json path language) support, but not SQL-level json paths, specifically the dot syntax.
- e12e 3y agoYeah, postgres uses proprietary syntax for this AFAIK. Would be nice to see t860-t864 supported. Then there's the whole graph data stuff that seems to come straight from Oracle? https://pgql-lang.org/ https://pgql-lang.org/
- Tommstein 3y agoMan, there's few pieces of software that I love quite like Postgres.
- starik36 4y agoCan someone explain why the standard endorses this ambiguity: CREATE TABLE t1 (a VARCHAR, b VARCHAR) The length of `a` and `b` is left up to the implementers: "an implementation-defined default limit will be applied." Why even allow ambiguity in the first place? This is just asking for problems.
- treeman79 4y agoPeople don’t interchange databases that often.
- osigurdson 4y agoSome people (unfortunately) have to support multiple database brands.
- masklinn 4y agoIn which case this‘ll hurt them no more than the current wide-spread incompatibilities between basic data types e.g. oracle not making a difference between NULL and an empty string, some databases defining storage limits in bytes where others use “characters” (whatever they mean by that), databases ignoring length limit versus truncating versus erroring (possibly the same database engine based on settings), …, and on top of that the database-specific limitations on indexing.
- paulryanrogers 4y agoLikely as a way to ask for arbitrary text up to something smaller than TEXT. The VARCHAR(000) form is already limited in its max by implementations.
- gaganyaan 4y agoI don't have any special knowledge, but I would speculate that it's for approximately the same reasons C allows you to define a byte as 7 bits: Backwards compatibility that let everyone agree on a standard
- mastax 4y agoLots of nice stuff in here. SELECT t.j.foo.bar[2], ... FROM tbl t ... I'm going to miss having this for years, probably.
- paulddraper 4y agoOr.....you could just use PostgreSQL 14+ and have it today :)
- deleted 4y ago[deleted]
- masklinn 4y agoI don’t think postgres has that tho? It has support for SQL2016’s jsonpath, but those are string queries, not sql-level traversal.
- k_bx 4y agoNot sure if Postgres has it, but last time I was using TimescaleDB I've written my own functions in Rust and it wasn't hard at all. I've heard it became even easier recently with PL/Rust https://news.ycombinator.com/item?id=35501065 https://news.ycombinator.com/item?id=35501065
- oblio 3y ago"Not sure if Postures has it, but for a totally unrelated database I wrote my own implementation in a famously finicky, but powerful language."
- masklinn 3y agoMade worse by “it” specifically not being a function, that’s the entire point. It’s already available behind a function and a stringly-typed path.
- pdw 4y ago
- jbverschoor 4y agoWhy not directly allow each column to be specified as “NULLS DISTINCT”? unique (a null distinct, b) > ORDER BY in grouped table So that means you’re allowed to use values you haven’t grouped by and haven’t aggregated? Sounds like MySQL to me
- amluto 4y agoAgreed. I read: > it turned out that this was technically not allowed. And thought “no, it turned out this query made no sense and had ill-defined results”. But the rest of the description is odd: > In particular, it was not allowed to have a grouped table ordered by a column that is not exposed by the SELECT list of the grouped table. And I’m wondering if the example query was wrong and they meant: SELECT product.product_id, sum(product_part.num) FROM product JOIN product_part ON product.product_id = product_part.product_id GROUP BY product.product_id, product.product_code ORDER BY product.product_code; In which case the change makes sense and is inoffensive.
- Dylan16807 4y agoIt's already grouping by the primary key of `product`. Why would you need to add another column in `product` to the group by?
- jbverschoor 4y agoIf that’s the very specific case, then they should be clear about it. But my interpretation was that our order by ungrouped column If it’s about preferring by non selected columns, that’d be fine. Something is wrong somewhere imo
- amluto 3y agoI assume that query optimizers are able to detect a GROUP BY of a primary key and other columns in the same table and optimize accordingly. But using this trick to change the set of valid queries seems odd. I think I'd rather see special syntax for it: GROUP BY table.*
- osigurdson 4y agoI wonder what the revenue per developer is for the SQL server team. It must be enormous at this point. Obviously declining as Postgres is so good but lots of companies / DBAs still demand SQL server (and it is not cheap).
- selcuka 4y agoThe article is about SQL, the language, not Microsoft SQL Server.
- osigurdson 4y agoSorry, I'm dumb.
- fdr 4y agoFunny story. at Microsoft it is common to call anything having to do with "SQL Server" simply "SQL". It was even on internal tchotchkes, like a "Linux <3 SQL" fridge magnet, presumably alluding to Linux support for Microsoft SQL Server. Since I came to them by the way of Citus acquisition and the Postgres world generally, this was first confusing (where SQL, to me, always meant the family of implementations, and the standard) and then amusing...but not quite endearing, in its imperiousness. On the other hand, they could have done worse than to name their database product the both bland and imperious "SQL Server:" they could have repeated their performance in naming "WinCE" as is the customary abbreviation of "Windows," plus "Compact Edition" which, if you simply type it out, spells "wince."
- beer2beerPrtcl 4y agoHow many devs to needed to keep SQL server running? Dozens? Hundreds? (1000?). Revenue has to be in the billions right?
- osigurdson 3y agoI'm thinking less than 100 at this point, with revenue in billions.
- ellisv 4y agoI can’t wait for Property Graph Queries
- mikece 4y agoIs there a competing query strategy, technology, or approach to compete with SQL? Yes, it gets the job done but it's always felt kludgy.
- nhumrich 4y agoYou mean, the ORM? Edgedb also is nice if you want a more object like model for your db.
- mind-blight 4y agoORMs generally solve a different problem than the query language. In SQL, everything is a table (that's not 100% true, but it's close enough). So, joins still result in a flat list of records. ORMs solve the problem of translating a flat list of records into objects that have arrays of other objects. Some people also use them to avoid having to write SQL, but that's honestly a secondary feature. You can have a more modern query language that still results in tables
- giovannibonetti 4y agoThere are some ideas based on Datalog, like this one: https://opensource.googleblog.com/2021/04/logica-organizing-your-data-queries.html https://opensource.googleblog.com/2021/04/logica-organizing-...
- resonantjacket5 4y agoI've read about edgedb in the past https://www.edgedb.com/blog/we-can-do-better-than-sql https://www.edgedb.com/blog/we-can-do-better-than-sql where they mainly changed the query language, underlying it still uses postgres. Though not sure how successful they've been.
- fbdab103 4y agoI really like what PRQL[0] is doing. Fixes a lot of the unfortunate SQL syntax. My impossible dream is that after years of language stability, Postgres could make it a first party query language. [0] https://prql-lang.org/ https://prql-lang.org/
- hbarka 4y agoWill this be part of the ANSI SQL standard?
- nerdponx 4y agoThis is the latest SQL Standard.
- fulafel 4y agoThe ISO standard is all there is. From https://blog.ansi.org/2018/10/sql-standard-iso-iec-9075-2016-ansi-x3-135/ https://blog.ansi.org/2018/10/sql-standard-iso-iec-9075-2016... ''' The “ANSI Standard” for SQL There is no such thing as “ANSI Standards,” as ANSI does not develop standards. Instead, there’s American National Standards and other documents written by ANSI-approved committees of standards developing organizations. Nonetheless, we get a lot of inquiries for the “ANSI standard” for SQL. It’s worth noting that, while this phrase is misleading and inaccurate for numerous reasons, it is referring to existing standard documents. SQL, just like many great things that outlasted the 70s, has a powerful history auspiciously intertwined with standards. At the conception of SQL’s specifications was ANSI (just another great feat throughout our 100-year history). The Current Edition of ISO/IEC 9075 for SQL If you don’t want to read all the history on SQL that we’ve detailed below, to make things short, SQL was standardized in ANSI X3.135 in 1986, and, within a few months, it was adopted by ISO as ISO 9075-1987. While most vendors modify SQL to meet their needs, they generally base their programs off the current version of this standard. The international standard (now ISO/IEC 9075) has been revised periodically ever since, most recently in 2016. It exists in 9 parts: [...] '''
- paulddraper 4y ago> There is no such thing as American National Standards Institute standards. :/
- hbarka 4y agoThank you for enlightening me with that detail. I’ve taken for granted that there is a generally standard version of SQL and the token term for it is ANSI SQL. I was in T-SQL (Microsoft), PL-SQL (Oracle), and DB2 SQL (IBM) dialects and now I feel at home with PostgreSql (ANSI SQL) which generally works across other DB platforms.
- ksec 4y agoI wonder if MySQL will have a new version for these features or if they will continue to milk version 8.
- Nonhocapito 4y ago"has finished" ffs
- de6u99er 4y agoHTTPS -> 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...
- filoeleven 4y agoSQL only hangs around because it was the best thing available at the time when DBs first exploded into common use. It’s pretty declarative, which is a point in its favor, but it’s not very amenable to query construction since it has AppleScript levels of “being like English.” Have fun writing something that can generate it while handling highly-adaptive and/or customizable requirements. I’m not going to call Datalog the definitive answer, but it’s a step in the right direction.
- hsbauauvhabzb 4y agoI’ve long felt that SQL was somehow backwards, but I don’t know what would be better. ‘Go and get two eggs’ Is better than ‘go to the fridge and get eggs, only two’.
- masklinn 4y ago> but I don’t know what would be better. Strictly following the semantics order of the querying, whether forward or reverse. The ordering issue of SQL is that it uses a mixed ordering, the physical layout of the query in text form doesn’t follow the logical execution of the query, which makes it very confusing. Comparing to short cutesy sentences is not helpful as a non-trivial query has dozens or hundreds of steps, it’s closer to a build book or checklist. Furthermore english is far from a paragon of clarity or regularity, and languages which try to follow it (SQL, AppleScript) tend to scale a lot worse with instruction sizes and complexity.
- afiori 4y agoAfter using moonshine MongoDB pipelines I would say that it is a fantastic model. You can sort of already do this in SQL with CTE* but a nice pipeline syntax would be welcome. * Actually I believe that you can do more with CTE as you can mix the various steps in arbitrary ways
- Deukhoofd 4y agoWouldn't a better analogy for SQL be 'Get the top two eggs from the fridge'? It'd translate to select top 2 eggs from fridge;
- slim 4y agoORDER BY in grouped table (F868) Finally! This will let one have control over selected columns whose value would otherwise be randomly selected from the group
- burakemir 4y agoIn the end, SQL is what people are familiar with (for better or worse) and what is entrenched in some form in most database, and new versions of the standard are unlikely to change anything fundamental. I don't believe that adding property graph support to SQL makes much of a difference for SQL and relational databases... However, the PG query additions (I believe they are close to the Cypher language neo4j) being part of the SQL standard will give a boost to marketing and adoption of PG databases. Vendors can now say "our language follows the SQL standard" instead of referencing cypher and indirectly neo4j. Even if neo4j may have the best intentions, marketing is very important in databases and it is unlikely that vendors could have supported Cypher and thus admit that they are following neo4j rather than leading. Standards can thus be valuable regardless of whether they are adopted or not and a lot of discussion and collaboration was likely necessary to get to this point re: the PG additions. In this context, it is also worth mentioning the GQL standard here, which neo4j folks are part of https://en.m.wikipedia.org/wiki/Graph_Query_Language https://en.m.wikipedia.org/wiki/Graph_Query_Language
- noobcoder 3y agoCypher has built-in support for traversing relationships between nodes in a graph, which can be more intuitive and efficient than using SQL to join tables.
- timenova 3y agoIn relational databases, the property graph queries can be really effective to write authorization queries. Whether user 'x' has access to a resource 'z' which is related via 'y', becomes a much simpler looking query.
- hobofan 3y agoHuh, how views can differ... I think SQL/PGQ have the potential to outright kill the whole field of PG databases (if/once existing databases actually start implementing it). The big marketing claim of Neo4J & friends has always been that they make graph queries possible, with a big portion of that claim being that those queries would be intractable for RDBMs performance-wise. With SQL/PGQ it becomes quite apparent that there is next to no magic sauce in PG databases and that it's all just syntactic sugar on top of node/vertex tables. All the rest of their query plans looks 1:1 what you'll find in a RDBMs. Now that they are on a level playing field query syntax-wise, PG databases will have to compete against RDBMs with a lot more advanced query optimizers, better administrative tooling etc..
- go_prodev 4y agoAny_Value is a strange addition. I'm struggling to think of a scenario where I need a value from a group of records, but don't care which one. Why not just use min, max or even row_number to specify an order? If the value isn't important, do you really need to present it?
- tomtom1337 4y agoWe use it to select any value from an aggregated list of values which we know are identical. It requires fewer operations than e.g. max.
- OscarCunningham 4y agoWhat I'd really want in that example is a function that errored if they weren't all the same.
- dspillett 4y agoAny circumstance where it would be needed should have given a syntax error irrespective of the data present (all matching or with differences) – everything in the projection list needing to be in the grouping clause or an aggregate. For a very simple but slightly odd (why would you ask exactly that?) example: > SELECT FirstName, WorkEmail FROM Person GROUP BY WorkEmail should give an error in current SQL implementations. Despite the fact that in the data in question there is no ambiguity as the email addresses are never the same between two people, the database has no way of knowing that. The error message in SQL Server in this case is “Column 'Person.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”. In this case you could declare WorkEmail to be unique, but that should still error as the standard doesn't take this into account. The new ANY_VALUE us counted as an aggregate so “fixes” the syntax error like so: > SELECT ANY_VALUE(FirstName), WorkEmail FROM Person GROUP BY WorkEmail Basically it is a way of telling the query planner that you know your data better than it does, and it shouldn't worry its little head and take the first thing it sees to project for that column. mySQL actually does this by default, or at least it used to, using the first thing it sees for columns that are neither aggregates nor part of the grouping clause, and you'll find any number of people asking why this doesn't work in other databases (SQL Server, postgres, …) when they try porting things over. -- ANY_VALUE() feels icky to me, similar to how the related mysql behaviour always did. It is explicitly giving an opening for the output to change arbitrarily for a given query and that makes me think it is going to be the source of difficult to find bugs, for instance when some code relies on a particular output but changes in data balance hit a tipping point and make the query planner take a different route meaning ANY_VALUE() returns something other than it has in that place for months/years. I can think of examples where it could be useful or at least makes things no worse (you need to get a little more complex than my single table example above), i.e. where trying to disambiguate with another aggregate like MAX still gives effectively arbitrary results, trying with ROW_NUMBER() tricks adds complexity (both for the human reading the code and the query planner), and adding the column to the GROUP BY would cause duplicate rows otherwise, but I'd rather not have it because of the issues that it could easily create in the wrong hands.
- RyanHamilton 4y agoIt saddens me to see how misguided some groups attempt at language creation are. Contrast these 2 quotes: (greatest/least) "To be clear, in most programming languages, these functions would merely be called max and min. But in SQL, those names are already used for aggregate functions. Since there is no syntactic difference between normal functions and aggregate functions, you have to pick two different names." "After 2-3 years the language had grown by accretion.... Eventually I found it was shrinking, once you look at enough different applications you come to general notions, these generalizations allowed me to take out whole chunks of special things and furthermore the general ideas are usually much simpler to understand than the special cases." The easiest path for someone creating a language or API is to add a new layer/function for everything. It solves the immediate problem, it allows you to create something new, it can't break existing code. As a java programmer I often find myself creating an abstraction layer without thinking. What's made this much worse actually is google and stackoverflow, people can't google general solutions but they can google for "convert php string to camel case". However when I look at beautiful implementations of languages, they are beautiful in their exposed simplicity.
- gigatexal 3y agothis whole nulls distinct v not distinct is a side-effect of the standard not being more explicit in what nulls really mean -- so to me adding this distinction in the unique constraint looks like a code smell if one could apply that term to standards.
- hzia 3y agoI think we need a transpiler that allows writing SQL 2023, while having specific backends for PostGres and MySQL. What babel / TS did for JS This will make the standard process a lot easier, as community can try out new features first before actually building a standard. It really feels like they are shooting in the dark, with major focus on features and light on the actual DX of devs using SQL day to day.
- wutania 3y agoMy fear with yet another transpiler is that we start working on layers of tech. I like Postgres for its directness w/ the DB. You query, you get it.
- jakewhelan 3y agoDefinitely agree! I've never taken a liking to SQL but using transpiler libraries has really improved the DX, and made me not dislike it so much.
- gigatexal 3y agowhoa Property Graph Queries being added to the standard is huge imo
- roenxi 3y agoThe SQL Standard appears to me to be some sort of political tool, likely developed and used by Oracle. It is the most significant standard I know of that isn't publicly available. Seriously, if you want a legal copy that costs something like 200 Swiss francs. This isn't how serious comp-sci standards work (the TCP people just throw out RFCs). Especially since the SQL standards committee probably don't know how to design a language given what most SQL implementations look like. I can't be sure though, I'll probably never be in a position to read the official standard. Reflect on the madness. Postgres? Free. SQL Standard? 200 francs. Postgres makes a better standard than the SQL standard. The ISO standard thing isn't here to support people using or building SQL databases.
- smokel 3y agoI sometimes fail to understand the disconnect in some of us here: you want six figure salaries, AND have standardization committees work for free (and all software and AI models should be provided for free as well). This would all make sense if you were in favor of fat governments, but that does not seem to be the case either. The entitlement baffles me. Or am I accidentally combining opinions of different people and making a misjudgment?
- roenxi 3y agoI'm not asking for anything; the postgres documentation is more important to me than the standard and already free. I'm pointing out that the standard isn't intended for implementers or users. By ruling out options, my guess is it is intended for governments to select Oracle DBs rather than alternate (OSS?) DBs - or just to put petty impediments in the way of new DBs trying to get started. Otherwise why is the standard the only component of the entire chain that is not free on the internet? It would be bizarre if ONLY the standards committee needs $$$ to operate. They have the easiest job and there are natural incentives for the different SQL implemeters to cooperate. Why else would the core postgres maintainers be expected to pay 200 francs for the privilege of getting a copy of a standard that they are implementing for free? That way lies madness. And we know the standards committee would be willing to work for free or people are available who would do the work (indeed, we can probably recover the standard from the postgres docs since they mention the things that aren't standards compliant - https://www.postgresql.org/docs/current/features.html https://www.postgresql.org/docs/current/features.html). The postgres maintainers do notably more work than the standards committee for free. How hard is it for them and Dr Hipp to throw out a document saying where the common ground is? It is far less work than they've already put in to the free docs. The useful parts SQL standard is an appendix compared to https://www.sqlite.org/docs.html https://www.sqlite.org/docs.html At least, I assume. As previously mentioned, I've never read the standard and as far as I can tell nobody is allowed to link it for me. EDIT I feel like I should link some standard here, so in lieu of the SQL standard which I can't link, here is the TCP standard: https://www.ietf.org/rfc/rfc9293.html https://www.ietf.org/rfc/rfc9293.html
- orthoxerox 3y agoI would love the next revision of the standard to include QoL stuff like: - SELECT column aliases being visible from WHERE and GROUP BY clauses, or even from the following expressions in the same SELECT clause - JOIN other_table ON FK - GROUP BY ALL - SELECT * EXCEPT column
- tofflos 3y agoWould love to see a more composable UNION in the future. Instead of: select * from a UNION select * from b UNION select * from c I'd like to do: select * from UNION (a, b, c)
- orthoxerox 3y agoIf SQL ever standardizes that table_name alone is equivalent to SELECT * FROM table_name, you will be able to write: a UNION b UNION c
- wizofaus 3y agoStill no explicit support for enums... best thing about Postgres in the brief period I used it was that. Looking at tables full of integer values and trying to remember what each mean does everyone's head in IME.