| 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 | ||
| Category: | Server | Severity: | S3 (Non-critical) |
| Version: | 4.0.22 | OS: | FreeBSD (FreeBSD 5.1, Linux 2.6.11) |
| Assigned to: | Antony Curtis | Target Version: | |
[1 Apr 2005 3:35]
Jorge del Conde
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
[1 Apr 2005 8:17]
Sergei Golubchik
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). "
[1 Apr 2005 17:12]
Clinton A
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.
[1 Apr 2005 18:13]
Sergei Golubchik
You're right. Return type for the CASE should not depend on ORDER BY or number of rows in the table. Reopened.
[6 Apr 2005 6:43]
Jorge del Conde
THanks for your bug report. Tested w/4.0.24 from bk
[21 Apr 2005 11:38]
Antony Curtis
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.
[22 Apr 2005 8:55]
Bugs System
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
[6 May 2005 9:44]
Michael Widenius
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)
[14 Jun 2005 2:33]
Paul DuBois
I added Monty's information to the description of the CASE expression.
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.