Bug #31394 Using tinyint with zerofill in DECLARE changes -1 to 000.

• Submitted: 4 Oct 2007 11:59
• Modified: 4 Oct 2007 15:24
• Reporter: Horst Hunger
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 5.1.23
• OS: Linux (Suse 10.2)
• CPU Architecture: Any

[4 Oct 2007 11:59] Horst Hunger

Description:
This is the output if I use zerofill. 
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( )
BEGIN
declare x, y, z tinyint zerofill default -1;
SELECT x, y, z;
END//
CALL sp1();
x       y       z
000     000     000
Warnings:
Warning 1264    Out of range value for column 'x' at row 1
Warning 1264    Out of range value for column 'y' at row 1
Warning 1264    Out of range value for column 'z' at row 1

This seems also the same for other negative values:
~
~DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( )
BEGIN
declare x, y, z tinyint zerofill default -126;
SELECT x, y, z;
END//
CALL sp1();
x       y       z
000     000     000
Warnings:
Warning 1264    Out of range value for column 'x' at row 1
Warning 1264    Out of range value for column 'y' at row 1
Warning 1264    Out of range value for column 'z' at row 1
~
Without zerofill:

DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( )
BEGIN
declare x, y, z tinyint default -1;
SELECT x, y, z;
END//
CALL sp1();
x       y       z
-1      -1      -1

~
~

How to repeat:
Put the attached test program into mysql-test/t
and execute ./mysql-test-run.pl tinyint-bug

[4 Oct 2007 15:24] Horst Hunger

Just read the sentence in the ref man:
If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.
So, I change the state to 'no bug'.