Bug #103170 Executing prepare returns wrong result

• Submitted: 1 Apr 2021 9:27
• Modified: 3 May 2021 18:14
• Reporter: Guo Wenxuan
• Status: Closed
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.23
• OS: Any
• CPU Architecture: Any

[1 Apr 2021 9:27] Guo Wenxuan

Description:
correct:
mysql> select/*+ hash_agg() */ max(col1) from t1 where col1 > 796639081.6419018240;

+-----------+
| max(col1) |
+-----------+
| NULL      |
+-----------+
1 row in set (0.04 sec)
 
incorrect:
mysql> prepare stmt from 'select max(col1) from t1 where col1 > ?;';
Query OK, 0 rows affected (0.04 sec)
Statement prepared
 
mysql> set @a=796639081.6419018240;
Query OK, 0 rows affected (0.04 sec)
 
mysql> execute stmt using @a;
+----------------------+
| max(col1)            |
+----------------------+
| 796639081.6419018240 |
+----------------------+
1 row in set (0.04 sec)
 

How to repeat:
drop table t1;

CREATE TABLE `t1` (
  `COL1` decimal(54,10),
	UNIQUE KEY `UK_COL1` (`COL1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into t1  values(796639081.6419018240);

[1 Apr 2021 11:17] MySQL Verification Team

Thank you for the bug report.

[3 May 2021 18:14] Paul DuBois

Posted by developer:
 
Fixed in 8.0.26.

A prepared statement that used MIN() or MAX() could return an
incorrect result if it also included dynamic parameters.