Bug #107363 | Unexpected results if the query with subquery, FLOOR and GROUP BY. | ||
---|---|---|---|
Submitted: | 22 May 14:01 | Modified: | 23 May 7:18 |
Reporter: | Zhang JiYang | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 8.0.25 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[22 May 14:01]
Zhang JiYang
[23 May 7:15]
MySQL Verification Team
Hello Zhang JiYang, Thank you for the report and feedback. Confirmed that 8.0.25 build is affected but issue is not seen since 8.0.26 build. 8.0.26+ -- mysql> SELECT -> a.* -> FROM -> ( -> SELECT -> a_c1, -> floor(a_c1 / 1) AS c1_1 -> FROM -> ( -> SELECT -> t.c1 as a_c1 -> FROM -> t2 t -> GROUP BY -> t.c1 -> ) b -> ) a -> where c1_1 > 0; +------+------+ | a_c1 | c1_1 | +------+------+ | 5 | 5 | | 6 | 6 | | 8 | 8 | +------+------+ 3 rows in set (0.01 sec) mysql> SELECT -> a.* -> FROM -> ( -> SELECT -> a_c1, -> floor(a_c1 / 1) AS c1_1 -> FROM -> ( -> SELECT -> t.c1 as a_c1 -> FROM -> t2 t -> GROUP BY -> t.c1 -> ) b -> ) a; +------+------+ | a_c1 | c1_1 | +------+------+ | 5 | 5 | | 6 | 6 | | 8 | 8 | +------+------+ 3 rows in set (0.00 sec) - 8.0.25 - affected mysql> SELECT -> a.* -> FROM -> ( -> SELECT -> a_c1, -> floor(a_c1 / 1) AS c1_1 -> FROM -> ( -> SELECT -> t.c1 as a_c1 -> FROM -> t2 t -> GROUP BY -> t.c1 -> ) b -> ) a -> where c1_1 > 0; +------+------+ | a_c1 | c1_1 | +------+------+ | 5 | 8 | | 6 | 8 | | 8 | 8 | +------+------+ 3 rows in set (0.00 sec) mysql> SELECT -> a.* -> FROM -> ( -> SELECT -> a_c1, -> floor(a_c1 / 1) AS c1_1 -> FROM -> ( -> SELECT -> t.c1 as a_c1 -> FROM -> t2 t -> GROUP BY -> t.c1 -> ) b -> ) a; +------+------+ | a_c1 | c1_1 | +------+------+ | 5 | 5 | | 6 | 6 | | 8 | 8 | +------+------+ 3 rows in set (0.00 sec) Please note that we don't fix bugs in old versions, don't back-port bug fixes, so you need to check with latest MySQL Server version. So, please, upgrade MySQL Server to 8.0.29 and inform us if problem still exists. Thank you! regards, Umesh
[23 May 7:18]
Zhang JiYang
Thank you very much!