MySQL Bugs: #107106: Using prepare statement, sum() results in incorrect results.

Bug #107106	Using prepare statement, sum() results in incorrect results.
Submitted:	23 Apr 2022 3:09	Modified:	23 Apr 2022 10:16
Reporter:	bao yang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	mysql community server 8.0.22	OS:	CentOS
Assigned to:		CPU Architecture:	Any
Tags:	prepare statement, sum

Description:
Using prepare statement, sum() results in incorrect results.
server version: mysql community server 8.0.22 (Linux -Generic (glibc 2.12)(x86,64-bit))
OS platform: Centos7

How to repeat:
Run below sqls on 8.0.22; See outputs.

1. Create database, table and insert data.

SQL> CREATE DATABASE `lucy_rebate` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SQL> USE `lucy_rebate`;

SQL> CREATE TABLE `t_account_charges` (
  `account_id` varchar(12) NOT NULL,
  `date` char(8) NOT NULL,
  `charges` decimal(8,2) NOT NULL,
  `useful_num` decimal(8,2) NOT NULL,
  PRIMARY KEY (`account_id`,`date`));

SQL> INSERT INTO `t_account_charges` VALUES ('00005838','20220414',0.00,4.00),('20220414','20220414',0.00,83.00),('35030011','20220414',100.00,100.00),('80008138','20220413',0.00,0.00),('80008138','20220414',0.00,0.00),('85130009','20220413',0.00,0.00),('85130009','20220414',14.00,95.00),('85130009','20220420',7.00,7.00),('85130011','20220407',20.00,20.00),('85130011','20220408',20.00,66.00),('85130011','20220413',0.00,87.00),('85130011','20220414',0.00,79.00);

SQL> SELECT * FROM t_account_charges;
+------------+----------+---------+------------+
| account_id | date     | charges | useful_num |
+------------+----------+---------+------------+
| 00005838   | 20220414 |    0.00 |       4.00 |
| 20220414   | 20220414 |    0.00 |      83.00 |
| 35030011   | 20220414 |  100.00 |     100.00 |
| 80008138   | 20220413 |    0.00 |       0.00 |
| 80008138   | 20220414 |    0.00 |       0.00 |
| 85130009   | 20220413 |    0.00 |       0.00 |
| 85130009   | 20220414 |   14.00 |      95.00 |
| 85130009   | 20220420 |    7.00 |       7.00 |
| 85130011   | 20220407 |   20.00 |      20.00 |
| 85130011   | 20220408 |   20.00 |      66.00 |
| 85130011   | 20220413 |    0.00 |      87.00 |
| 85130011   | 20220414 |    0.00 |      79.00 |
+------------+----------+---------+------------+

2. Execute prepared statements.

SQL> PREPARE stmt1 FROM 'SELECT sum(useful_num) as total_num FROM t_account_charges WHERE account_id=? AND date>=? AND date<=? AND date<=?';

SQL> SET @a = '35030011';
SQL> SET @b = '20220414';
SQL> SET @c = '20220513';
SQL> SET @d = '20220414';
SQL> EXECUTE stmt1 USING @a, @b, @c, @d;
+-----------+
| total_num |
+-----------+
|    100.00 |    --this result is correct.
+-----------+

SQL> SET @a = '20220414';
SQL> SET @b = '20220414';
SQL> SET @c = '20220422';
SQL> SET @d = '20220414';
SQL> EXECUTE stmt1 USING @a, @b, @c, @d;
+-----------+
| total_num |
+-----------+
|     83.00 |    --this result is also correct.
+-----------+

SQL> SET @a = '85130011';
SQL> SET @b = '20220416';
SQL> SET @c = '20220422';
SQL> SET @d = '20220414';
SQL> EXECUTE stmt1 USING @a, @b, @c, @d;
+-----------+
| total_num |
+-----------+
|     83.00 |    --this result is incorrect!
+-----------+

However, the last result of total_num 83.00 is incorrect. The actual result should by NULL. Let's replace the parameter with actual values, query again.

SQL> SELECT sum(useful_num) as total_num FROM t_account_charges WHERE account_id='85130011' AND date>='20220416' AND date<='20220422' AND date<='20220414';
+-----------+
| total_num |
+-----------+
|      NULL |
+-----------+

Suggested fix:
Choose one of below action:
1. choose the latest mysql version.
2. do not use prepare statement.

Hello bao yang,

Thank you for the report and test case.
Quickly tried on latest GA and observed that issue is no longer seen.

-
bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.28-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2022, 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> USE `lucy_rebate`;
Database changed
mysql> CREATE TABLE `t_account_charges` (
    ->   `account_id` varchar(12) NOT NULL,
    ->   `date` char(8) NOT NULL,
    ->   `charges` decimal(8,2) NOT NULL,
    ->   `useful_num` decimal(8,2) NOT NULL,
    ->   PRIMARY KEY (`account_id`,`date`));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `t_account_charges` VALUES ('00005838','20220414',0.00,4.00),('20220414','20220414',0.00,83.00),('35030011','20220414',100.00,100.00),('80008138','20220413',0.00,0.00),('80008138','20220414',0.00,0.00),('85130009','20220413',0.00,0.00),('85130009','20220414',14.00,95.00),('85130009','20220420',7.00,7.00),('85130011','20220407',20.00,20.00),('85130011','20220408',20.00,66.00),('85130011','20220413',0.00,87.00),('85130011','20220414',0.00,79.00);
Query OK, 12 rows affected (0.01 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t_account_charges;
+------------+----------+---------+------------+
| account_id | date     | charges | useful_num |
+------------+----------+---------+------------+
| 00005838   | 20220414 |    0.00 |       4.00 |
| 20220414   | 20220414 |    0.00 |      83.00 |
| 35030011   | 20220414 |  100.00 |     100.00 |
| 80008138   | 20220413 |    0.00 |       0.00 |
| 80008138   | 20220414 |    0.00 |       0.00 |
| 85130009   | 20220413 |    0.00 |       0.00 |
| 85130009   | 20220414 |   14.00 |      95.00 |
| 85130009   | 20220420 |    7.00 |       7.00 |
| 85130011   | 20220407 |   20.00 |      20.00 |
| 85130011   | 20220408 |   20.00 |      66.00 |
| 85130011   | 20220413 |    0.00 |      87.00 |
| 85130011   | 20220414 |    0.00 |      79.00 |
+------------+----------+---------+------------+
12 rows in set (0.00 sec)

mysql> PREPARE stmt1 FROM 'SELECT sum(useful_num) as total_num FROM t_account_charges WHERE account_id=? AND date>=? AND date<=? AND date<=?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @a = '35030011';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = '20220513';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @d = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @a, @b, @c, @d;
+-----------+
| total_num |
+-----------+
|    100.00 |
+-----------+
1 row in set (0.00 sec)

mysql> SET @a = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = '20220422';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @d = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @a, @b, @c, @d;
+-----------+
| total_num |
+-----------+
|     83.00 |
+-----------+
1 row in set (0.00 sec)

mysql> SET @a = '85130011';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = '20220416';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = '20220422';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @d = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @a, @b, @c, @d;
+-----------+
| total_num |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT sum(useful_num) as total_num FROM t_account_charges WHERE account_id='85130011' AND date>='20220416' AND date<='20220422' AND date<='20220414';
+-----------+
| total_num |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

Please note that we don't fix bugs in old versions, don't back port bug fixes. 
I suggest you to upgrade to latest available MySQL Server. Thank you!

regards,
Umesh

Thanks for you reply. I know this bug can't repeat on the 8.0.28. But it doesn't mean this bug had been fixed. I just hope you to test this bug on 8.0.22 and tell us this is a real bug on 8.0.22. It's terrible that sum() can results to wrong result. We should do something to warn people to update mysql version or don't use prepare statement if they are using 8.0.22 in their production as soon as possible.

As I mentioned in mt previous note - Please note that we don't fix bugs in old versions, don't back port bug fixes. I suggest you to upgrade to latest available MySQL Server. Thank you!

I confirm that issue exists in 8.0.22/8.0.23/8.0.24 but fixed in 8.0.25. Looking at the change log it is fixed after Bug #103192 - https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-25.html

- 8.0.22-24

mysql> PREPARE stmt1 FROM 'SELECT sum(useful_num) as total_num FROM t_account_charges WHERE account_id=? AND date>=? AND date<=? AND date<=?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @a = '35030011';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = '20220513';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @d = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @a, @b, @c, @d;
+-----------+
| total_num |
+-----------+
|    100.00 |
+-----------+
1 row in set (0.00 sec)

mysql> SET @a = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = '20220422';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @d = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @a, @b, @c, @d;
+-----------+
| total_num |
+-----------+
|     83.00 |
+-----------+
1 row in set (0.00 sec)

mysql> SET @a = '85130011';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = '20220416';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = '20220422';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @d = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @a, @b, @c, @d;
+-----------+
| total_num |
+-----------+
|     83.00 |
+-----------+
1 row in set (0.00 sec)

- 8.0.25

mysql> PREPARE stmt1 FROM 'SELECT sum(useful_num) as total_num FROM t_account_charges WHERE account_id=? AND date>=? AND date<=? AND date<=?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @a = '35030011';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = '20220513';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @d = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @a, @b, @c, @d;
+-----------+
| total_num |
+-----------+
|    100.00 |
+-----------+
1 row in set (0.00 sec)

mysql> SET @a = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = '20220422';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @d = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @a, @b, @c, @d;
+-----------+
| total_num |
+-----------+
|     83.00 |
+-----------+
1 row in set (0.00 sec)

mysql> SET @a = '85130011';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = '20220416';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = '20220422';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @d = '20220414';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @a, @b, @c, @d;
+-----------+
| total_num |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)