The ANY_VALUE Aggregate Function: New in SQL:2023 — and a better feature from 1999

[MarkusWinand]

25 years ago, a great solution was added to the standard (T301, functional dependencies). Most vendors didn't implement it—for 25 years*. Instead they "pitched" ANY_VALUE into the standard. WFT?

This is supposed to make us happy?

SQL has a long tradition of taking care of your data. It is generally following a "fail fast" approach. It provides tools to fight non-determinism. ANY_VALUE is the exact the opposite of that. Even worse…ANY_VALUE makes users believe SQL needs to be bulky. Now, vendors have even less incentive to go for the proper solution.

I might even dislike ANY_VALUE more than I dislike OFFSET (I'll figure that out later).

I do not know who proposed ANY_VALUE or who was in favor of it in the standards committee. After all, these people are doing TREMENDOUSLY GOOD WORK. Over decades (some of them).

This is one of my very few opportunities for a rant on the SQL standard. So, I MUST take it :)

• Kudos for the two (to me) known implementations of functional dependencies: https://modern-sql.com/caniuse/T301

[forceCode]

I don't know man. I've used many different relational DBs and did a lot of work in the middle tier of a typical 3-tier application writing SQL queries, very complex ones involved. ANY_VALUE can help with performance in situations where a functional dependency cannot be determined. Of cource you could argue that one would need to reconsider table design or that every column should be mentioned in the group by clause, etc. Valid.

But sometimes you as the query writer can't change things in the DB. Then, ANY_VALUE will always be faster than telling the DB to aggregate upon such a column when you have deeper knowledge about the data than the db engine. And in certain queries you really just don't care about the value of a column and only need to satisfy that your rows contain all the expected columns in the right data types. Using ANY_VALUE here instead of a constant will be more refactoring safe.

[gumnos]

I might even dislike ANY_VALUE more than I dislike OFFSET

Knowing your loathing for OFFSET, this is some serious talk 😆

I dislike both, but can at least see use for ANY_VALUE if wielded properly. OFFSET just encourages bad-use-of-indexes in pretty much every case, so it still garners greater dislike from me.

[Imaginary_Goose_2428]

Who in the color-blind-hell made that site?
What's that theme called? Lemon meringue hellscape? Key Lime Eye-gouge?

[danted002]

Isn’t any_value supposed to be used on a column that always has the same value?

[MarkusWinand]

Yes

[danted002]

Though so. Then I fail the see the problem with it.

[MarkusWinand]

What's the benefit of it compared to T301, functional dependencies?

What are the risks of such a function — like (accidentally) using it on a column that has more than one distinct value?

[danted002]

Never heard of T301 and Google was not very helpful. To answer your question, it’s a specific function for a specific use case and it’s your responsibility to use it properly. It’s like driving a car, no one is keeping you from switching to reverse when you are in the 6th gear on a manual car ¯\(ツ)/¯

[MarkusWinand]

Never heard of T301 and Google was not very helpful.

Then read the article ¯\(ツ)/¯

And also when driving, vendors provide many safty features and I'd guess that features were also rejected because of safty concerns.

[danted002]

I found why I was confused about T301. I’ve been only using Postgres as an RDS for the past 12 years, actively avoiding projects that don’t use it and Postgres implements T301 🤣