Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.0, 13.1
-
None
-
Can result in unexpected behaviour
Description
SELECT f_zf() on a function declared RETURNS INT(5) ZEROFILL returns 7, while CONCAT(f_zf()), CAST(f_zf() AS CHAR) and a view over the same call all return 00007
CREATE TABLE t_zf (c INT(5) ZEROFILL); |
INSERT INTO t_zf VALUES (7); |
SELECT c, CONCAT(c), CAST(c AS CHAR) FROM t_zf; |
CREATE FUNCTION f_zf() RETURNS INT(5) ZEROFILL DETERMINISTIC RETURN 7; |
SELECT f_zf(), CONCAT(f_zf()), CAST(f_zf() AS CHAR); |
CREATE VIEW v_zf AS SELECT f_zf() AS c; |
SELECT c FROM v_zf; |
CREATE TABLE t_zf3 AS SELECT f_zf() AS c; |
SHOW CREATE TABLE t_zf3; |
CLI output
10.11.19-opt>CREATE TABLE t_zf (c INT(5) ZEROFILL); |
Query OK, 0 rows affected (0.010 sec) |
|
|
10.11.19-opt>INSERT INTO t_zf VALUES (7); |
Query OK, 1 row affected (0.001 sec)
|
|
|
10.11.19-opt>SELECT c, CONCAT(c), CAST(c AS CHAR) FROM t_zf; |
+-------+-----------+-----------------+ |
| c | CONCAT(c) | CAST(c AS CHAR) | |
+-------+-----------+-----------------+ |
| 00007 | 00007 | 00007 |
|
+-------+-----------+-----------------+ |
1 row in set (0.000 sec) |
|
|
10.11.19-opt>CREATE FUNCTION f_zf() RETURNS INT(5) ZEROFILL DETERMINISTIC RETURN 7; |
Query OK, 0 rows affected (0.002 sec) |
|
|
10.11.19-opt>SELECT f_zf(), CONCAT(f_zf()), CAST(f_zf() AS CHAR); |
+--------+----------------+----------------------+ |
| f_zf() | CONCAT(f_zf()) | CAST(f_zf() AS CHAR) | |
+--------+----------------+----------------------+ |
| 7 | 00007 | 00007 |
|
+--------+----------------+----------------------+ |
1 row in set (0.000 sec) |
|
|
10.11.19-opt>CREATE VIEW v_zf AS SELECT f_zf() AS c; |
Query OK, 0 rows affected (0.003 sec) |
|
|
10.11.19-opt>SELECT c FROM v_zf; |
+-------+ |
| c |
|
+-------+ |
| 00007 |
|
+-------+ |
1 row in set (0.000 sec) |
|
|
10.11.19-opt>CREATE TABLE t_zf3 AS SELECT f_zf() AS c; |
Query OK, 1 row affected (0.005 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
|
10.11.19-opt>SHOW CREATE TABLE t_zf3; |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+ |
| Table | Create Table | |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+ |
| t_zf3 | CREATE TABLE `t_zf3` ( |
`c` int(5) unsigned zerofill DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci | |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+ |
1 row in set (0.000 sec) |
|
|
10.11.19-opt>
|