Bug #99343 Selecting with generated column gets the wrong result

• Submitted: 24 Apr 2020 6:03
• Modified: 24 Apr 2020 8:33
• Reporter: wj huang
• Status: Verified
• Category: MySQL Server: Parser
• Severity: S2 (Serious)
• Version: 8.0.19, 5.7.29, 8.0.25
• OS: Any
• CPU Architecture: Any

[24 Apr 2020 6:03] wj huang

Description:
Consider the following statements:

mysql> set sql_mode="";
Query OK, 0 rows affected (0.00 sec)
mysql> create table t(a int, b tinyint as (a+1), index(b));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values (1000, default);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t;
+------+------+
| a    | b    |
+------+------+
| 1000 |  127 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from t where a+1 > 200;
Empty set (0.01 sec)

The last query returns the wrong result. I think it's because MySQL substitutes `a+1` to `b`, and the value of `b` is `127`.

How to repeat:
set sql_mode="";
create table t(a int, b tinyint as (a+1), index(b));
insert into t values (1000, default);
select * from t;

Suggested fix:
When substitutes the expression to a generated column, check the type carefully. Though both types of expression and the generated column are integers, they are not equal exactly.

[24 Apr 2020 8:33] MySQL Verification Team

Hello wj huang,

Thank you for the report and test case.

regards,
Umesh