MySQL Bugs: #9547: CASE statement gives incorrect result

Bug #9547	CASE statement gives incorrect result
Submitted:	1 Apr 2005 0:33	Modified:	14 Jun 2005 2:33
Reporter:	Clinton A	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.22	OS:	FreeBSD (FreeBSD 5.1, Linux 2.6.11)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
Ratingstring should be 'A', not '0' for following query - 

mysql> SELECT currentoffice AS office_name, lastname AS candidate_lastname, firstname AS candidate_firstname, id AS candidate_id, b.state AS office_state, CASE WHEN party LIKE '%democrat%' THEN 'Democrat' ELSE SUBSTRING(party,1,30) END AS party_name, currentdistrict AS district_name, usevalue, CASE WHEN ratingstring = ""  THEN 0 ELSE ratingstring END AS ratingstring, ratingvalue FROM tblBiograph b, tblRatings r, tblRatingKeys rk WHERE r.ratingkeyid = '001571B' AND r.ratingkeyid = rk.ratingid AND bioid = b.id AND b.Active = 1 AND currentoffice != '' AND (rk.state = '' OR (rk.state = b.state)) ORDER BY office_state, CASE WHEN currentdistrict='Sr' THEN '1' WHEN currentdistrict='Jr' THEN '2' ELSE '3' END, currentdistrictsort LIMIT 1;                                                                                     

+--------------+--------------------+---------------------+--------------+--------------+------------+---------------+----------+--------------+-------------+
| office_name  | candidate_lastname | candidate_firstname | candidate_id | office_state | party_name | district_name | usevalue | ratingstring | ratingvalue |
+--------------+--------------------+---------------------+--------------+--------------+------------+---------------+----------+--------------+-------------+
| State-Senate | Mrvan              | Frank               | CIN43231     | IN           | Democrat   | 1             |        0 |            0 |           0 |
+--------------+--------------------+---------------------+--------------+--------------+------------+---------------+----------+--------------+-------------+
1 row in set (0.01 sec)

Without the limit, all returned rows have a '0' in ratingstring.

Replacing query above with "0" instead of 0 returns correct ratingstring, as does removing the ORDER BY or some of the other columns from the SELECT.

mysql> SELECT  currentoffice AS office_name, lastname AS candidate_lastname, firstname AS candidate_firstname, id AS candidate_id, b.state AS office_state, CASE WHEN party LIKE '%democrat%' THEN 'Democrat' ELSE SUBSTRING(party,1,30) END AS party_name, currentdistrict AS district_name, usevalue, CASE WHEN ratingstring = ""  THEN "0" ELSE ratingstring END AS ratingstring, ratingvalue FROM tblBiograph b, tblRatings r, tblRatingKeys rk WHERE r.ratingkeyid = '001571B' AND r.ratingkeyid = rk.ratingid AND bioid = b.id AND b.Active = 1 AND currentoffice != '' AND (rk.state = '' OR (rk.state = b.state)) ORDER BY office_state, CASE WHEN currentdistrict='Sr' THEN '1' WHEN currentdistrict='Jr' THEN '2' ELSE '3' END, currentdistrictsort LIMIT 1;
+--------------+--------------------+---------------------+--------------+--------------+------------+---------------+----------+--------------+-------------+
| office_name  | candidate_lastname | candidate_firstname | candidate_id | office_state | party_name | district_name | usevalue | ratingstring | ratingvalue |
+--------------+--------------------+---------------------+--------------+--------------+------------+---------------+----------+--------------+-------------+
| State-Senate | Mrvan              | Frank               | CIN43231     | IN           | Democrat   | 1             |        0 | D            |           0 |
+--------------+--------------------+---------------------+--------------+--------------+------------+---------------+----------+--------------+-------------+

How to repeat:
100% reproducible with the above queries.

Hi

Can you please provide us with the create statement of your table 'tblBiograph' as well as some of the records so that we can reproduce this ?

THanks

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

See http://dev.mysql.com/doc/mysql/en/control-flow-functions.html
Note that "The type of the return value (INTEGER, DOUBLE, or STRING) is the same as the type of the first returned value (the expression after the first THEN). "

The man page explains the results above that I mistook for a bug but does not account for the evident inconsistency in the results, considering that I'm able to use the first query (where type to be returned is integer) and get the correct result for ratingstring, 'D', if I remove the ORDER BY, or make other changes to the query outside of the CASE in question.  I've attached a file for tables creation and sample data.  If I remove all but one row in tblbiograph, the correct string result (but apparently incorrect because it is not an integer) is returned.

The type of the return value is not always that of the first value to be returned, evidently.

You're right. Return type for the CASE should not depend on ORDER BY or number of rows in the table.

Reopened.

THanks for your bug report.  Tested w/4.0.24 from bk

The only reason why you are not getting 0 as a result when you're not using ORDER BY is because MySQL isn't internally creating a temporary table to buffer the data for sorting. When it does this, it respects the result type and so the temporary buffer only stores an integer.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24218

This is not a bug but a consequence of an MySQL extension.

CASE had  in 4.0  default return type depending of the first argument to the function.
In future versions, the default return type is the compatable aggregated type of all used types.

Note that how the CASE is evaluted depend also of the context it's used. If it's used in string context, the argument will be returned as strings, if it's used in numeric context it will be returned as a decimal/real or integer.

What we will do:
- Document the exact behaviour of CASE
- Add a warning in the case where result are of different types (in 5.0)

I added Monty's information to the description
of the CASE expression.