Bug #103192 Wrong result of execute prepare on 'smallint'
Submitted: 2 Apr 7:24 Modified: 12 Apr 22:27
Reporter: Guo Wenxuan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[2 Apr 7:24] Guo Wenxuan
Description:
correct:
mysql> select sum(col1) from t1 where col1 > 32767;
+-----------+
| sum(col1) |
+-----------+
| NULL      |
+-----------+
1 row in set (0.04 sec)

incorrect:
mysql>  prepare stmt from 'select sum(col1) from t1 where col1 > ?;';

Query OK, 0 rows affected (0.06 sec)
Statement prepared
 
mysql> set @a=14742;

Query OK, 0 rows affected (0.04 sec)
 
mysql> execute stmt using @a;
+-----------+
| sum(col1) |
+-----------+
| 47510     |
+-----------+
1 row in set (0.04 sec)
 
mysql> set @a=32767;
Query OK, 0 rows affected (0.04 sec)
 
mysql> execute stmt using @a;
+-----------+
| sum(col1) |
+-----------+
| 47510     |
+-----------+
1 row in set (0.04 sec)
 

How to repeat:
mysql> CREATE TABLE t1(
  col1 smallint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values(32767);
Query OK, 1 row affected (0.05 sec)
 
mysql> insert into t1 values(14742);
Query OK, 1 row affected (0.07 sec)
 
mysql> insert into t1 values(14743);
Query OK, 1 row affected (0.05 sec)
[2 Apr 9:31] MySQL Verification Team
Thank you for the bug report.
[12 Apr 22:27] Jon Stephens
Documented fix as follows in the MySQL 8.0.25 changelog:

    For a prepared, implicitly grouped SELECT statement in which the
    WHERE clause was determined to be always false, the result of
    some aggregate functions could sometimes be picked up from the
    previous execution of the statement.

Closed.