| 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: | |
| 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: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"
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)