| Bug #106154 | Got different query result between 5.7.33 and 8.0.25 | ||
|---|---|---|---|
| Submitted: | 13 Jan 2022 3:34 | Modified: | 13 Jan 2022 7:40 |
| Reporter: | Lin TSE YU | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.25 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Jan 2022 3:34]
Lin TSE YU
[13 Jan 2022 7:40]
MySQL Verification Team
Hello Lin TSE YU,
Thank you for the report and feedback.
I confirmed that 8.0.25 is affected but issue is no longer seen in latest GA version 8.0.27(same as in 5.7.36), I suggest you to upgrade to latest MySQL GA version. Please note that we don't fix bugs in old versions, don't back-port bug fixes, so need you to check with latest version anyway. So, please, upgrade and inform us if problem still exists.
- 8.0.27
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test
Database changed
mysql>
mysql> CREATE TABLE j (
-> uid int unsigned NOT NULL DEFAULT '0',
-> a1 decimal(40,4) DEFAULT NULL,
-> a2 decimal(40,4) DEFAULT NULL,
-> a3 decimal(40,4) DEFAULT NULL,
-> a4 decimal(40,4) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into j(uid ,a1 ,a2 ,a3 ,a4)
-> values(1 ,10.0000 ,0.0000 ,0.0000 ,0.0000)
-> ,(1 ,0.0000 ,50.0000 ,0.0000 ,0.0000)
-> ,(2 ,200.0000 ,0.0000 ,0.0000 ,0.0000)
-> ,(2 ,0.0000 ,500 ,0.0000 ,0.0000)
-> ,(3 ,1000.0000 ,0.0000 ,0.0000 ,0.0000)
-> ,(3 ,0.0000 ,3100.0000 ,0.0000 ,0.0000)
-> ,(4 ,2000.0000 ,0.0000 ,0.0000 ,0.0000)
-> ,(4 ,0.0000 ,20000.0000 ,0.0000 ,0.0000)
-> ,(5 ,2000.0000 ,0.0000 ,0.0000 ,0.0000)
-> ,(5 ,0.0000 ,20000.0000 ,0.0000 ,0.0000);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select *
-> from (
-> select uid ,s2+s3+s4-s1 as r1
-> from (
-> select uid ,sum(a1) as s1 ,sum(a2) as s2 ,sum(a3) as s3 ,sum(a4) as s4
-> from j
-> group by uid
-> ) x
-> ) y
-> where r1 > 1 ;
+-----+------------+
| uid | r1 |
+-----+------------+
| 1 | 40.0000 |
| 2 | 300.0000 |
| 3 | 2100.0000 |
| 4 | 18000.0000 |
| 5 | 18000.0000 |
+-----+------------+
5 rows in set (0.00 sec)
- 5.7.36
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> CREATE TABLE j (
-> uid int unsigned NOT NULL DEFAULT '0',
-> a1 decimal(40,4) DEFAULT NULL,
-> a2 decimal(40,4) DEFAULT NULL,
-> a3 decimal(40,4) DEFAULT NULL,
-> a4 decimal(40,4) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into j(uid ,a1 ,a2 ,a3 ,a4)
-> values(1 ,10.0000 ,0.0000 ,0.0000 ,0.0000)
-> ,(1 ,0.0000 ,50.0000 ,0.0000 ,0.0000)
-> ,(2 ,200.0000 ,0.0000 ,0.0000 ,0.0000)
-> ,(2 ,0.0000 ,500 ,0.0000 ,0.0000)
-> ,(3 ,1000.0000 ,0.0000 ,0.0000 ,0.0000)
-> ,(3 ,0.0000 ,3100.0000 ,0.0000 ,0.0000)
-> ,(4 ,2000.0000 ,0.0000 ,0.0000 ,0.0000)
-> ,(4 ,0.0000 ,20000.0000 ,0.0000 ,0.0000)
-> ,(5 ,2000.0000 ,0.0000 ,0.0000 ,0.0000)
-> ,(5 ,0.0000 ,20000.0000 ,0.0000 ,0.0000);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select *
-> from (
-> select uid ,s2+s3+s4-s1 as r1
-> from (
-> select uid ,sum(a1) as s1 ,sum(a2) as s2 ,sum(a3) as s3 ,sum(a4) as s4
-> from j
-> group by uid
-> ) x
-> ) y
-> where r1 > 1 ;
+-----+------------+
| uid | r1 |
+-----+------------+
| 1 | 40.0000 |
| 2 | 300.0000 |
| 3 | 2100.0000 |
| 4 | 18000.0000 |
| 5 | 18000.0000 |
+-----+------------+
5 rows in set (0.00 sec)
regards,
Umes
