| Bug #99404 | AGG alias in ORDER BY works, but not if in an expression | ||
|---|---|---|---|
| Submitted: | 30 Apr 2020 7:11 | Modified: | 3 May 2020 10:19 |
| Reporter: | bai Kevin | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.17 and before | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[30 Apr 2020 12:33]
MySQL Verification Team
Hi Mr. Kevin, Thank you for your bug report. However, this is not a bug, since I am not able to repeat it with the current 8.0 patch-fix release, which is 8.0.20: c2 avg ab 30.0000 abc 38.0000 abcd 89.0000 abcdef 91.5000 c2 avg ab 30.0000 abc 38.0000 abcd 89.0000 abcdef 91.5000 Results are exactly as they should be. Hence, all you have to do is upgrade to the latest 8.0 release. Not a bug.
[3 May 2020 10:19]
bai Kevin
Yes. There is no this bug after 8.0.18 because of new AggregateIterator. But before 8.0.18, there is this bug in every version before.
[4 May 2020 12:19]
MySQL Verification Team
Hi, 8.0.18, 8.0.17 etc, are not versions. These are patch fix releases. Version is 8.0. Hence, we fix bugs in 8.0 in a new bug-fix release, not in the old ones.
Description: AGG alias in ORDER BY works, but not if in an expression. For example: SELECT c2, avg(c3) as avg FROM t1 GROUP BY c2 ORDER BY avg / 2; The result of sorting will get the wrong result. 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", 10, 1); +INSERT INTO t1(c2, c3, c4) VALUES("abc", 29, 1); +INSERT INTO t1(c2, c3, c4) VALUES("abcdef", 180, 3); +INSERT INTO t1(c2, c3, c4) VALUES("ab", 10, 2); +INSERT INTO t1(c2, c3, c4) VALUES("abcd", 89, 1); +INSERT INTO t1(c2, c3, c4) VALUES("ab", 50, 8); +INSERT INTO t1(c2, c3, c4) VALUES("abc", 47, 2); +INSERT INTO t1(c2, c3, c4) VALUES("abcdef", 103, 4); +INSERT INTO t1(c2, c3, c4) VALUES("abc", 38, 3); +INSERT INTO t1(c2, c3, c4) VALUES("abcdef", 73, 3); +ALTER TABLE t1 ADD INDEX i_c2(c2); +SELECT c2, avg(c3) as avg FROM t1 GROUP BY c2 ORDER BY avg; +c2 avg +ab 30.0000 +abc 38.0000 +abcd 89.0000 +abcdef 91.5000 +SELECT c2, avg(c3) as avg FROM t1 GROUP BY c2 ORDER BY avg / 2; +c2 avg +abcd 89.0000 +ab 30.0000 +abcdef 91.5000 +abc 38.0000 Suggested fix: @@ -5224,7 +5224,21 @@ bool Item_field::fix_fields(THD *thd, Item **reference) { item tree, then we use new Item_ref as an intermediate value to resolve referenced item only. In this case the new Item_ref item is unused. + + The new Item_ref should point to the Item_ref in the + base_ref_items but not the Item in the select list. Otherwise, + when the REF_SLICE_ACTIVE is switched to the other slice like + REF_SLICE_TMP1, the Item_ref can't be switched to the new one. + For example: + SELECT c2, avg(c3) as avg FROM t1 GROUP BY c2 ORDER BY avg/2; + When fixing the Item alias avg in the expr avg/2, if the new + Item_ref pointer to the select list, then it will still point + to the original Item in the select list after active slice is + switched to the REF_SLICE_TMP1. In reality, it should point to + the new field item of the temporary table. Later the sorting + with the wrong Item_ref will make wrong results. */ + res = &thd->lex->current_select()->base_ref_items[counter]; Item_ref *rf = new Item_ref(context, res, table_name, field_name, resolution == RESOLVED_AGAINST_ALIAS); if (!rf) return 1;