Bug #114186 Make ANY_VALUE standard compliant
Submitted: 1 Mar 15:32 Modified: 1 Mar 15:56
Reporter: Markus Winand Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[1 Mar 15:32] Markus Winand 
Description:
ANY_VALUE, with 5.7 in 2015, is not an aggregate function.

In the meanwhile, the latest SQL Standard (from 2023) also defines an ANY_VALUE function, which IS an aggregate function.

It would be great if MySQL's ANY_VALUE would align with the latest standard (as good as possible).

The main differences I see are:

- SQL:2023 ANY_VALUE doesn't return NULL if there is a non-null value
- SQL:2023 ANY_VALUE can be used with OVER(..)
- SQL:2023 ANY_VALUE implies GROUP BY () if there is no GROUP BY or OVER clause
- [Not applicable: SQL:2023 accepts the FILTER clause]
- [Pointless: SQL:2023 ANY_VALUE accepts DISTINCT/ALL]

In particular the first two could be fixed without breaking existing queries.

How to repeat:
Not a bug.
[1 Mar 15:55] MySQL Verification Team 
Hello Markus Winand,

Thank you for the feature request!

regards,
Umesh