Bug #33628 numeric values returned as text

• Submitted: 2 Jan 2008 15:00
• Modified: 3 Jan 2008 7:17
• Reporter: Martins Brivnieks
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.45, 5.1
• OS: Windows (XP sp2)
• CPU Architecture: Any

[2 Jan 2008 15:00] Martins Brivnieks

Description:
Select 
if(Updated<SubDate(Now(),Interval 9 Second),@tmp:=0,@tmp:=UsageMS) as UsageMS,
if(@tmp=0,0,UsageLastSecMS) as UsageLastSecMS,
if(@tmp=0,0,OperationsCount) as OperationsCount,
if(@tmp=0,0,LastSecOperationsCount) as LastSecOperationsCount
From Usage_Monitor Where id1=1

+---------+----------------+-----------------+------------------------+
| UsageMS | UsageLastSecMS | OperationsCount | LastSecOperationsCount |
+---------+----------------+-----------------+------------------------+
|      82 | 16             | 34              | 3                      |
+---------+----------------+-----------------+------------------------+

same select on 4.1
+---------+----------------+-----------------+------------------------+
| UsageMS | UsageLastSecMS | OperationsCount | LastSecOperationsCount |
+---------+----------------+-----------------+------------------------+
|      82 |             16 |              34 |                      3 |
+---------+----------------+-----------------+------------------------+

How to repeat:
CREATE TABLE Usage_Monitor(
UsageMS MediumInt(5) Unsigned NOT NULL Default 0,
UsageLastSecMS MediumInt(4) Unsigned NOT NULL Default 0,
OperationsCount MediumInt(4) Unsigned NOT NULL Default 0,
LastSecOperationsCount MediumInt(4) Unsigned NOT NULL Default 0,
id1 TinyInt(1) Unsigned NOT NULL auto_increment,
Updated timestamp(14) not NULL,
PRIMARY Key (id1)
) ENGINE=MEMORY;

REPLACE InTo Usage_Monitor
(id1,UsageMS,UsageLastSecMS,OperationsCount,LastSecOperationsCount)
Values(1,82,16,34,3);

Select 
if(Updated<SubDate(Now(),Interval 9 Second),@tmp:=0,@tmp:=UsageMS) as UsageMS,
if(@tmp=0,0,UsageLastSecMS) as UsageLastSecMS,
if(@tmp=0,0,OperationsCount) as OperationsCount,
if(@tmp=0,0,LastSecOperationsCount) as LastSecOperationsCount
From Usage_Monitor Where id1=1

Suggested fix:
before write report i have tryed make example with only variables
when also value is returned as text
set @t111=55;
select @t111,@t111+0;
+--------+---------+
| @t111  | @t111+0 |
+--------+---------+
| 55     |      55 |
+--------+---------+
,but in that case "+0" is enough

have found only one painfull way "Cast( as Signed)":
Select 
Cast(if(Updated<SubDate(Now(),Interval 9 Second),@tmp:=0,@tmp:=UsageMS) as Signed) as UsageMS,
Cast(if(@tmp=0,0,UsageLastSecMS) as Signed) as UsageLastSecMS,
Cast(if(@tmp=0,0,OperationsCount) as Signed) as OperationsCount,
Cast(if(@tmp=0,0,LastSecOperationsCount) as Signed) as LastSecOperationsCount
From Usage_Monitor Where id1=1

[3 Jan 2008 4:47] Valeriy Kravchuk

Thank you for a problem report. I was not able to repeat the behaviour described with latest 5.0.54:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.54-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE Usage_Monitor(
    -> UsageMS MediumInt(5) Unsigned NOT NULL Default 0,
    -> UsageLastSecMS MediumInt(4) Unsigned NOT NULL Default 0,
    -> OperationsCount MediumInt(4) Unsigned NOT NULL Default 0,
    -> LastSecOperationsCount MediumInt(4) Unsigned NOT NULL Default 0,
    -> id1 TinyInt(1) Unsigned NOT NULL auto_increment,
    -> Updated timestamp(14) not NULL,
    -> PRIMARY Key (id1)
    -> ) ENGINE=MEMORY;
Query OK, 0 rows affected, 1 warning (0.16 sec)

mysql>
mysql> REPLACE InTo Usage_Monitor
    -> (id1,UsageMS,UsageLastSecMS,OperationsCount,LastSecOperationsCount)
    -> Values(1,82,16,34,3);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> Select
    -> if(Updated<SubDate(Now(),Interval 9 Second),@tmp:=0,@tmp:=UsageMS) as Usa
geMS,
    -> if(@tmp=0,0,UsageLastSecMS) as UsageLastSecMS,
    -> if(@tmp=0,0,OperationsCount) as OperationsCount,
    -> if(@tmp=0,0,LastSecOperationsCount) as LastSecOperationsCount
    -> From Usage_Monitor Where id1=1;
+---------+----------------+-----------------+------------------------+
| UsageMS | UsageLastSecMS | OperationsCount | LastSecOperationsCount |
+---------+----------------+-----------------+------------------------+
|      82 |             16 |              34 |                      3 |
+---------+----------------+-----------------+------------------------+
1 row in set (0.03 sec)

So, I think, bug is already fixed.

[3 Jan 2008 7:17] Martins Brivnieks

PASIBA,
sorry for spending your time - im just "Community Server" user
for which laitest version is 5.0.45 on download page