Bug #103192 Wrong result of execute prepare on 'smallint'

• Submitted: 2 Apr 2021 7:24
• Modified: 12 Apr 2021 22:27
• Reporter: Guo Wenxuan
• Status: Closed
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.23
• OS: Any
• CPU Architecture: Any

[2 Apr 2021 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 2021 9:31] MySQL Verification Team

Thank you for the bug report.

[12 Apr 2021 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.