Description:
When the fields in the table have the zerofill attribute, the union result will auto-truncate zerofill fields, which not what we expected.
How to repeat:
1、prepare data:
mysql> create table t1(a BIGINT(5) 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(0);
Query OK, 1 row affected (0.01 sec)
mysql> select a from t1 where ifnull(t1.a,1)=0 and t1.a=0;
+-------+
| a |
+-------+
| 00000 |
+-------+
1 row in set (0.01 sec)
2、Abnormal condition：
When we make the above result a union operation, the returned result will automatically truncate the zerofill field. As follows:
mysql> select a from t1 where ifnull(t1.a,1)=0 and t1.a=0 union select 1;
+------+
| a |
+------+
| 0 |
| 1 |
+------+
2 rows in set (0.00 sec)
