| Bug #102524 | Wrong result when order by a function expression. | ||
|---|---|---|---|
| Submitted: | 8 Feb 2021 9:07 | Modified: | 8 Feb 2021 14:07 |
| Reporter: | zetao wei | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 8.0.18 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[8 Feb 2021 14:07]
MySQL Verification Team
Hi Mr. wei, Thank you for your bug report. However, you are using a very old release of 8.0 version. I have ran your test case with 8.0.23 and I get a correct result: c2 cnt abcdef 2 abc 1 c2 cnt abcdef 2 abc 1 Not a bug. Try 8.0.23 yourself ......
Description: I found that order by on a function expression does not work when using temporary for grouping. mysql> SELECT c2, count(c3) as cnt FROM t1 GROUP BY c2 ORDER BY cnt; +--------+-----+ | c2 | cnt | +--------+-----+ | abc | 1 | | abcdef | 2 | +--------+-----+ mysql> SELECT c2, count(c3) as cnt FROM t1 GROUP BY c2 ORDER BY cnt/1; +--------+-----+ | c2 | cnt | +--------+-----+ | abcdef | 2 | | abc | 1 | +--------+-----+ 2 rows in set (0.00 sec) mysql> explain SELECT c2, count(c3) as cnt FROM t1 GROUP BY c2 ORDER BY cnt/1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 filtered: 100.00 Extra: Using temporary; Using filesort 1 row in set, 1 warning (0.01 sec) How to repeat: CREATE TABLE t1(c1 INT AUTO_INCREMENT PRIMARY KEY, c2 CHAR(20), c3 INT, c4 INT); INSERT INTO t1(c2, c3, c4) VALUES("abcdef", 103, 4); INSERT INTO t1(c2, c3, c4) VALUES("abcdef", 73, 3); INSERT INTO t1(c2, c3, c4) VALUES("abc", 47, 2); SELECT c2, count(c3) as cnt FROM t1 GROUP BY c2 ORDER BY cnt/1 desc;