Bug #46912 Case when combined with hexadecimals

• Submitted: 25 Aug 2009 7:18
• Modified: 25 Aug 2009 10:07
• Reporter: Marc Nijland
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.31
• OS: Linux
• CPU Architecture: Any
• Tags: case hexadecimals

[25 Aug 2009 7:18] Marc Nijland

Description:
Hexadecimals are not working within a case when  statement.

How to repeat:
drop procedure if exists test;
delimiter //
create procedure test()
begin
#$i should return 256 like $j
#but it return 0

	declare $i int;
	declare $j int;
	declare $s varchar(3);

	set $s = 'aaa';
	set $i = case $s
			 when 'aaa' then 0x100 end;
	set $j = 0x100;

	select $i;
	select $j;

end //
delimiter ;
call test()

[25 Aug 2009 10:07] Sveta Smirnova

Thank you for the report.

Strictly say this is not a bug. Pleas see:

$mysql51 --column-type-info
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 383
Server version: 5.1.39-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select case 'aaa' when 'aaa' then 0x100 end;
Field   1:  `case 'aaa' when 'aaa' then 0x100 end`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     2
Max_length: 2
Decimals:   0
Flags:      UNSIGNED BINARY 

+--------------------------------------+
| case 'aaa' when 'aaa' then 0x100 end |
+--------------------------------------+
|                                     |
+--------------------------------------+
1 row in set (0.00 sec)

When you don't specify how to handle 0x100 it is treated as var_string.

Workaround: CAST(0x100 AS UNSIGNED).