Bug #29072 | IF command is not taking into account the names setting. | ||
---|---|---|---|
Submitted: | 13 Jun 2007 13:09 | Modified: | 19 Jun 2007 9:05 |
Reporter: | Slavey Karadzhov | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
Version: | 5.0.44-BK, 5.0.24a | OS: | Linux (Distribution: Debian 4) |
Assigned to: | Assigned Account | CPU Architecture: | Any |
Tags: | utf8 locale |
[13 Jun 2007 13:09]
Slavey Karadzhov
[13 Jun 2007 13:51]
Valeriy Kravchuk
Thank you for a bug report. Yes, looks like BINARY collation is used without really good reason. A bit different test case verified on latest 5.0.44-BK: openxs@suse:~/dbs/5.0> bin/mysql -uroot -T test Reading 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.0.44-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table x (name varchar(255)); Query OK, 0 rows affected (0.01 sec) mysql> set names utf8; Query OK, 0 rows affected (0.00 sec) mysql> insert into x values('a'); Query OK, 1 row affected (0.01 sec) mysql> select * from x; Field 1: `name` Catalog: `def` Database: `test` Table: `x` Org_table: `x` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 765 Max_length: 1 Decimals: 0 Flags: +------+ | name | +------+ | a | +------+ 1 row in set (0.01 sec) mysql> select if(name='', concat('-', 1, '-'), name) as name1 from x; Field 1: `name1` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 765 Max_length: 1 Decimals: 31 Flags: +-------+ | name1 | +-------+ | a | +-------+ 1 row in set (0.00 sec) So, as you can see above, even with integer literal in CONCAT we have utf8_general_ci. Now: mysql> alter table x add id int; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select if(name='', concat('-', id, '-'), name) as name1 from x; Field 1: `name1` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 255 Max_length: 1 Decimals: 31 Flags: BINARY +-------+ | name1 | +-------+ | a | +-------+ 1 row in set (0.00 sec) Why do we have BINARY collation in the results above? IF returns a binary string because CONCAT returns a binary string, it seems: mysql> select concat('-',1); Field 1: `concat('-',1)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 2 Max_length: 2 Decimals: 31 Flags: NOT_NULL BINARY +---------------+ | concat('-',1) | +---------------+ | -1 | +---------------+ 1 row in set (0.00 sec) mysql> select concat('-',1,'-'); Field 1: `concat('-',1,'-')` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 3 Max_length: 3 Decimals: 31 Flags: NOT_NULL BINARY +-------------------+ | concat('-',1,'-') | +-------------------+ | -1- | +-------------------+ 1 row in set (0.00 sec) But this is NOT explicitely described at http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html, anyway. And, finally, why select if(name='', concat('-', 1, '-'), name) as name1 from x; does NOT return BINARY flag and collation then?
[19 Jun 2007 9:05]
Alexander Barkov
The problem is that automatic number-to-string conversion returns binary string, while it should return a character string, with character set "character_set_connection" and collation "collation_connection". This problem was previously reported here: http://bugs.mysql.com/bug.php?id=8204 I'm closing this report as duplicate. As a workaround, you need to use: 1. String literal instead of numeric literal: SELECT concat('-','1'); instead of SELECT concat('-',1); 2. CAST for columns: SELECT concat('-', cast(id as integer)) FROM t1; instead if SELECT concat('-', id) FROM t1;