Bug #65068 mysql will return error result while using "case...when"
Submitted: 23 Apr 2012 3:31 Modified: 24 Apr 2012 8:50
Reporter: zhai weixiang (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1,5.5 OS:Any
Assigned to: Norvald Ryeng CPU Architecture:Any

[23 Apr 2012 3:31] zhai weixiang
Description:
as described below ("how to repeat") 

How to repeat:
it's very easy to repeat:

master [localhost] {root} (test) > create table t1 (a int ,b varchar(10));
Query OK, 0 rows affected (0.01 sec)

master [localhost] {root} (test) > insert into t1 values (1,0),(2,0),(3,1),(4,1);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

master [localhost] {root} (test) > select b , case b when b = '1' then '=1' else '=0' end as xx from t1;
+------+------+
| b | xx |
+------+------+
| 0 | =1 |
| 0 | =1 |
| 1 | =1 |
| 1 | =1 |
+------+------+

master [localhost] {root} (test) > select @@version;
+------------+
| @@version |
+------------+
| 5.5.18-log |
+------------+
1 row in set (0.00 sec)

i guess "0" was treated specially´╝î because when i update b from '0' to '2' , it works fine......

master [localhost] {root} (test) > update t1 set b = '2' where a < 3;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

master [localhost] {root} (test) > select * from t1;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
+------+------+
4 rows in set (0.00 sec)

master [localhost] {root} (test) > select b , case b when b = '1' then '=1' else '=0' end as xx from t1;
+------+------+
| b | xx |
+------+------+
| 2 | =0 |
| 2 | =0 |
| 1 | =1 |
| 1 | =1 |
+------+------+
4 rows in set (0.00 sec)
[23 Apr 2012 3:48] Valeriy Kravchuk
Thank you for the bug report. Verified just as described:

macbook-pro:mysql-5.5.21-osx10.5-x86_64 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> create table t1 (a int ,b varchar(10));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t1 values (1,0),(2,0),(3,1),(4,1);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>  select b , case b when b = '1' then '=1' else '=0' end
    -> as xx from t1;
+------+------+
| b    | xx   |
+------+------+
| 0    | =1   |
| 0    | =1   |
| 1    | =1   |
| 1    | =1   |
+------+------+
4 rows in set (0.04 sec)

mysql> update t1 set b = '2' where a < 3;
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    1 | 2    |
|    2 | 2    |
|    3 | 1    |
|    4 | 1    |
+------+------+
4 rows in set (0.00 sec)

mysql>  select b , case b when b = '1' then '=1' else '=0' end
    -> as xx from t1;
+------+------+
| b    | xx   |
+------+------+
| 2    | =0   |
| 2    | =0   |
| 1    | =1   |
| 1    | =1   |
+------+------+
4 rows in set (0.00 sec)
[24 Apr 2012 7:16] Norvald Ryeng
This is not a bug, just a misunderstanding about the CASE syntax.

The query you use is

    SELECT b, CASE b WHEN b='1' THEN '=1' ELSE '=0' END AS xx FROM t1;

but the query that gives output you expect is

    SELECT b, CASE b WHEN '1' THEN '=1' ELSE '=0' END AS xx FROM t1;

The mistake is to use a comparison in the WHEN expression. This
comparison is evaluated first, and then the result of the comparison
is compared with the case expression. The CASE syntax is explained
here:
http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html#operator_case

To further shed light on what happens, let's look at this query:

SELECT a, b, b='1', b=(b='1') FROM t1;

a       b       b='1'   b=(b='1')
1       0       0       1
2       0       0       1
3       1       1       1
4       1       1       1

The third column is the WHEN expression. The fourth column is the
expression of the CASE statement: case_expression=when_expression. As
you can see, it first evaluates (b='1'), which is 0 (false) when b='0'
and 1 (true) when b='1'. Then it compares b with (b='1'), and the
result is true for all rows, hence the result '=1' from the CASE
statement.

Let's look at the same query after the UPDATE statement:

SELECT a, b, b='1', b=(b='1') FROM t1;

a       b       b='1'   b=(b='1')
1       2       0       0
2       2       0       0
3       1       1       1
4       1       1       1

Here, we see the same evaluation of (b='1'), but since we now have
b='2', the comparison in the case statement for the first two rows is
'2'=0, which is clearly not true.

I hope this explains the behavior you're seeing. I'm closing this
report as not a bug.
[24 Apr 2012 8:50] zhai weixiang
Thanks very much for your explanation.
this is really my misunderstand of "case...when"
[24 Apr 2012 8:50] zhai weixiang
Thanks very much for your explanation.
this is really my misunderstand of "case...when"