Bug #9572 Stored procedures: variable type declarations ignored

• Submitted: 1 Apr 2005 20:36
• Modified: 7 Dec 2005 20:40
• Reporter: Peter Gulutzan
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0
• OS: Linux (SUSE 9.2)
• Assigned to: Alexander Nozdrin
• CPU Architecture: Any

[1 Apr 2005 20:36] Peter Gulutzan

Description:
If I say "declare v tinyint" in a routine, then the
data type of v should be tinyint. But if I say
"declare v tinyint default 1e200" and then select
v, I see that v has a bigint value (-9223372036854775808).
It's as if there's an automatic upgrade policy:
tinyint/smallint/int become bigint
decimal(5) becomes decimal(72)
char becomes varchar
and so on. So "traditional" checking won't work.

How to repeat:
mysql> delimiter //
mysql> set sql_mode='traditional'//
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure ptiny () begin declare v tinyint default 1e200; select v; end//
Query OK, 0 rows affected (0.02 sec)

mysql> call ptiny()//
+----------------------+
| v                    |
+----------------------+
| -9223372036854775808 |
+----------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> create procedure pdec () begin declare v decimal(5) default 1e200; select v; end//
Query OK, 0 rows affected (0.01 sec)

mysql> call pdec()//
+--------------------------------------------------------------------------+
| v                                                                        |
+--------------------------------------------------------------------------+
| 368278484639385041047239877871023591066789981811181813306167128854888448 |
+--------------------------------------------------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create procedure pchar () begin declare v char(5) default 'abc  '; select v like 'abc__'; end//
Query OK, 0 rows affected (0.01 sec)

mysql> call pchar()//
+----------------+
| v like 'abc__' |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

[14 Sep 2005 10:04] Valeriy Kravchuk

Verified on 5.0.13-BK also:

mysql> set sql_mode='traditional'//
Query OK, 0 rows affected (0,03 sec)

mysql> create procedure ptiny () begin declare v tinyint default 1e200; select
    -> vv; end//
Query OK, 0 rows affected (0,23 sec)

mysql> call ptiny()//
+----------------------+
| v                    |
+----------------------+
| -9223372036854775808 |
+----------------------+
1 row in set (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

mysql> create procedure pdec () begin declare v decimal(5) default 1e200; select
    -> v; end//
Query OK, 0 rows affected (0,01 sec)

mysql> call pdec()//
+-------------------------------------------------------------------------------
----+
| v
    |
+-------------------------------------------------------------------------------
----+
| 182135734368278484639385041047239877871023591066789981811181813306167128854888
448 |
+-------------------------------------------------------------------------------
----+
1 row in set (0,00 sec)

Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> create procedure pchar () begin declare v char(5) default 'abc  '; select
    -> v like 'abc__'; end//
Query OK, 0 rows affected (0,04 sec)

mysql> call pchar()//
+----------------+
| v like 'abc__' |
+----------------+
|              1 |
+----------------+
1 row in set (0,03 sec)

Query OK, 0 rows affected (0,03 sec)

mysql> select version()//
+-------------------+
| version()         |
+-------------------+
| 5.0.13-beta-debug |
+-------------------+
1 row in set (0,00 sec)

[21 Oct 2005 12:30] Roland Bouman

I'm having similar trouble in WinXP, and for parameters and variables alike:

create procedure p_varchar(in v varchar(2))
begin
    declare vv varchar(1);
    set vv := v;
    select v,vv;
end;
//

call p_varchar('aaa');
//
+-----+-----+
| v   | vv  |
+-----+-----+
| aaa | aaa |
+-----+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

create procedure p_char(in v char(2))
begin
    declare vv char(1);
    set vv := v;
    select v,vv;
end;
//

call p_char('aa');
//
+-----+-----+
| v   | vv  |
+-----+-----+
| aaa | aaa |
+-----+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

create procedure p_decimal(in v decimal(2))
begin
    declare vv decimal(1);
    set vv := v;
    select v,vv;
end;
//
call p_decimal(10);

+-----+-----+
| v   | vv  |
+-----+-----+
| 111 | 111 |
+-----+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

[7 Dec 2005 20:40] Paul DuBois

Noted in 5.0.18 changelog.