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 Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.1.23 OS:Linux (Suse 10.2)
Assigned to: 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'.