MySQL Bugs: #77561: A "CASE WHEN" expression with NULL and an unsigned type gives a signed result

Bug #77561	A "CASE WHEN" expression with NULL and an unsigned type gives a signed result
Submitted:	30 Jun 2015 9:28	Modified:	10 Jul 2015 10:34
Reporter:	Sylvain Décombe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.25	OS:	Any
Assigned to:	Tor Didriksen	CPU Architecture:	Any
Tags:	unsigned case when

Description:
When a query contains an expression with CASE WHEN … THEN NULL WHEN … THEN "unsigned column" …, the result type is signed. It should be unsigned according to the document chapter 12.4 Control flow functions ("The return type of a CASE expression is the compatible aggregated type of all return values").

It causes a serious annoyance when using MySQL connector .NET, because an exception is raised when a value greater than 127 is casted into a SByte type.

It may be the same issue than in bug n°49639 but I didn't check.

How to repeat:
create table MyTable (`n` tinyint unsigned not null);

insert into MyTable (n) values (180);

select n from MyTable;
=> result is type TinyInt with flag 0x1021 (not null, unsigned) and value = 180

select (case when 1 then n else null end) as value from MyTable;
=> result is type TinyInt with flag 0x080 (binary) and value = 180

Suggested fix:
The function Item_func_case::agg_num_lengths that determine the new "unsigned flag" sets the flag to 0 even if the type is Null.
To me, it should ignore Null args since NULL is compatible with both signed and unsigned fields.

Hello Sylvain Décombe,

Thank you for the bug report.
Verified this behavior on Visual Studio 2013 (C#.Net) with MySQL Connector/Net 6.9.6.

Thanks,
Chiranjeevi.

screenshot

Attachment: 77561.PNG (image/png, text), 101.68 KiB.

Bug is fixed in 5.7.4 by internal
bug#17388045 - FAILED: PRECISION || !SCALE
MY_DECIMAL_PRECISION_TO_LENGTH_NO_TRUNCATION()