Bug #79366 | ORDER BY does not work correctly with grouped AVG() values extracted from JSON | ||
---|---|---|---|
Submitted: | 20 Nov 2015 19:04 | Modified: | 3 Oct 2016 18:34 |
Reporter: | Philip Antoniades | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.7.9 | OS: | Ubuntu (5.7.9 MySQL Community Server (GPL)) |
Assigned to: | CPU Architecture: | Any | |
Tags: | avg, json |
[20 Nov 2015 19:04]
Philip Antoniades
[26 Nov 2015 11:37]
Knut Anders Hatlen
Thanks for the bug report. It seems like this is a problem that affects all BLOB-based types, not only the JSON type. For example: mysql> CREATE TABLE t(txt TEXT, i INT); mysql> INSERT INTO t VALUES ('a', 2), ('b', 8), ('c', 2); mysql> SELECT txt, AVG(i) a FROM t GROUP BY txt ORDER BY a; +------+--------+ | txt | a | +------+--------+ | a | 2.0000 | | b | 8.0000 | | c | 2.0000 | +------+--------+
[13 Aug 2016 7:42]
MySQL Verification Team
See also: http://bugs.mysql.com/bug.php?id=82572
[15 Aug 2016 5:51]
Gillian Gunson
Duplicate bug found here: http://bugs.mysql.com/bug.php?id=82334
[27 Sep 2016 11:16]
Knut Anders Hatlen
This bug was fixed in 8.0.0 as part of WL#8699.
[3 Oct 2016 18:34]
Paul DuBois
Posted by developer: Noted in 5.7.17 changelog. Queries that were grouped on a column of a BLOB-based type, and that were ordered on the result of the AVG(), VAR_POP(), or STDDEV_POP() aggregate function, returned results in the wrong order if InnoDB temporary tables were used.
[12 Jul 2017 21:51]
Rick James
https://stackoverflow.com/questions/44865156/mysql-order-by-avg-desc-not-working-when-cert... suggests that LONGTEXT may also be at fault.