Bug #107608 Results not satisfy expectations with zerofill fields
Submitted: 20 Jun 2022 10:54 Modified: 20 Jun 2022 11:50
Reporter: yi qian Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S1 (Critical)
Version:8.0.25, 8.0.29 OS:Any
Assigned to: CPU Architecture:Any

[20 Jun 2022 10:54] yi qian
Description:
This bug is reported by GoldenDB Team.

Problem description:
When the fields in the table have the zerofill attribute, in some cases, the query results are inconsistent with expectations.

How to repeat:
1、prepare data:
mysql> create table t1(a BIGINT(89) ZEROFILL);
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                   |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column. |
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release.                                                                                              |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> insert into t1 values(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(0);
Query OK, 1 row affected (0.01 sec)

2、Normal condition:
mysql> select (ifnull(t1.a,1) || t1.a) from t1;
+--------------------------+
| (ifnull(t1.a,1) || t1.a) |
+--------------------------+
|                        1 |
|                        1 |
|                        0 |
+--------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead |
+---------+------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ALL t1.a AS ref0 FROM t1 WHERE (ifnull(t1.a,1) || t1.a);
+------+
| ref0 |
+------+
| NULL |
| NULL |
+------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead |
+---------+------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3、Abnormal condition:
mysql> select not (ifnull(t1.a,1) || t1.a) from t1;
+------------------------------+
| not (ifnull(t1.a,1) || t1.a) |
+------------------------------+
|                            0 |
|                            0 |
|                            1 |
+------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead |
+---------+------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ALL t1.a AS ref0 FROM t1 WHERE not (ifnull(t1.a,1) || t1.a);
Empty set, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                        |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead                          |
| Warning | 1292 | Truncated incorrect DECIMAL value: '0'                                                                                         |
| Warning | 1292 | Truncated incorrect DECIMAL value: '00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000' |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

Notes:
Similar to normal situation, expected to return one row of results
[20 Jun 2022 11:50] MySQL Verification Team
Hello yi qian,

Thank you for the report and feedback.

regards,
Umesh