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
