Bug #18180 zerofill with "select into"

• Submitted: 13 Mar 2006 9:03
• Modified: 4 Oct 2008 19:18
• Reporter: Anca Dogaru
• Status: Won't fix
• Category: MySQL Server: General
• Severity: S4 (Feature request)
• Version: 5.0.18
• OS: Linux (fedora core 2)
• CPU Architecture: Any

[13 Mar 2006 9:03] Anca Dogaru

Description:
i have the following table

-- Table structure for table `mytable`
-- 

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL default '0',
  `number` mediumint(8) unsigned zerofill NOT NULL default '00000000',
  KEY `id` (`id`)
) TYPE=InnoDB;

-- 
-- Dumping data for table `mytable`
-- 

INSERT INTO `mytable` VALUES (1, 00000001);
INSERT INTO `mytable` VALUES (2, 00000044);

if i try (inside a stored procedure)

select number into varname from mytable where id=1;

the varname will have the value without the padded zeros. even if the varname is declared as varchar, or int.

select number from mytable; works just fine showing padded zeros

How to repeat:
create the table and do a select

select number into @varname from mytable where id=1;
select @varname;

Suggested fix:
none

[13 Mar 2006 13:29] Valeriy Kravchuk

Thank you for a problem report. I can confirm your findigs even on the latest version of MySQL:

mysql> CREATE TABLE `mytable` (
    ->   `id` int(11) NOT NULL default '0',
    ->   `number` mediumint(8) unsigned zerofill NOT NULL default '00000000',
    ->   KEY `id` (`id`)
    -> ) TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> INSERT INTO `mytable` VALUES (1, 00000001);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO `mytable` VALUES (2, 00000044);
Query OK, 1 row affected (0.03 sec)

mysql> select number into @varname from mytable where id=1;
Query OK, 1 row affected (0.03 sec)

mysql> select @varname;
+----------+
| @varname |
+----------+
| 1        |
+----------+
1 row in set (0.00 sec)

mysql> select number from mytable;
+----------+
| number   |
+----------+
| 00000001 |
| 00000044 |
+----------+
2 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19-nt |
+-----------+
1 row in set (0.00 sec)

But I am not sure it is a bug. So, I'll mark it as a verified feature request.

[13 Mar 2006 13:56] Anca Dogaru

I still think is a bug as long as the manual sais that:

"When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. "

the retrieved value should not depend on the method uset to get data...

[13 Mar 2006 14:09] Hartmut Holzgraefe

verified, not sure whether is intended or not though ...

[4 Oct 2008 19:18] Konstantin Osipov

User variables are weakly typed and can have only one of four basic types (64 bit int, string, double, decimal).

You should use stored procedure variables for this:
mysql> create procedure p1() begin
    ->   declare varname mediumint(8) unsigned zerofill;
    ->   CREATE TABLE mytable (id int(11) NOT NULL default '0',
    ->       number mediumint(8) unsigned zerofill NOT NULL default '00000000',
    ->       KEY id (id)
    ->       ) engine=InnoDB;
    ->   INSERT INTO mytable VALUES (1, 00000001);
    ->   INSERT INTO mytable VALUES (2, 00000044);
    ->   select number into varname from mytable where id=1;
    ->   select varname;
    -> end|
Query OK, 0 rows affected (0.00 sec)

mysql> call p1()|
+----------+
| varname  |
+----------+
| 00000001 | 
+----------+
1 row in set (0.07 sec)

Query OK, 0 rows affected (0.07 sec)

Based on acceptable workaround and the fact that user variables work as intended setting to "Won't fix".