mysql5.5のおもしろ現象について

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.5.25a-log |
+-------------+
1 row in set (0.00 sec)

mysql> create database hoge;
Query OK, 1 row affected (0.01 sec)

mysql> use hoge;
Database changed

mysql> create table foo (d double, m mediumint);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into foo values (3.14, 6);
Query OK, 1 row affected (0.03 sec)

mysql> select case when 1 then d else m end from foo;
+-------------------------------+
| case when 1 then d else m end |
+-------------------------------+
|                             3 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> drop table foo;
Query OK, 0 rows affected (0.02 sec)

mysql> create table foo (f float, m mediumint);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into foo values (3.14, 6);
Query OK, 1 row affected (0.03 sec)

mysql> select case when 1 then f else m end from foo;
+-------------------------------+
| case when 1 then f else m end |
+-------------------------------+
|                             3 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select case when 1 then f else 1 end from foo;
+-------------------------------+
| case when 1 then f else 1 end |
+-------------------------------+
|             3.140000104904175 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> drop table foo;
Query OK, 0 rows affected (0.02 sec)

mysql> create table foo (f float, i int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into foo values (3.14, 6);
Query OK, 1 row affected (0.03 sec)

mysql> select case when 1 then f else i end from foo;
+-------------------------------+
| case when 1 then f else i end |
+-------------------------------+
|             3.140000104904175 |
+-------------------------------+
1 row in set (0.01 sec)

　caseのelseでつかうフィールドがmediumintのときだけ、評価結果の浮動小数点型が四捨五入したみたいになる。intだとふつうに出力されるので意味がわからない。

　とりあえず適当にキャストする必要があるっぽい。

mysql> select case when 1 then cast(f as binary) else m end from foo;
+-----------------------------------------------+
| case when 1 then cast(f as binary) else m end |
+-----------------------------------------------+
| 3.14                                          |
+-----------------------------------------------+
1 row in set (0.00 sec)

　手元に5.5.30があったので試してみたけど、直ってる感じだった。ソフトウェアは常にあたらしいのを使うのがいいと思う。