MySQL Bugs: #23661: Decimal result type inferred incorrectly from case statement

Bug #23661	Decimal result type inferred incorrectly from case statement
Submitted:	26 Oct 2006 9:38	Modified:	15 Dec 2007 16:48
Reporter:	Martin Dirichs	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.26, 5.1 BK	OS:	Linux (Linux, WinXP SP2)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The case statement used in select queries returns a type which is the "compatible aggregated type of all return values" as stated in the MySQL reference documentation. This type inference may yield a wrong data type under certain conditions:
  - only decimal types are used
  - the select clause is a "select distinct"
  - number of decimal places varies for the values used in the case statement

How to repeat:
# The following steps yield a result set of (0.0),(9.9)
# instead of the expected (0.0),(20.0)

create table d (id int, name varchar(32));

insert into d values (1, "a"), (2, "b");

select distinct
case name when "a" then 0.0 when "b" then 20 else 0 end as result
from d;

Suggested fix:
Currently it is possible to work around this problem by, for example, specifying the same number of decimal places for all possible results of the case statement.

However, I regard this as a bug in the MySQL server which ought to be fixed there.

Thank you for the report.

Verified as described on Linux using last BK sources. Version 5.1 is affected too.