| 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: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;
Description: IF command is not taking into account the names setting. This happens when I use the mysql console client and using the PHP library. How to repeat: CREATE TABLE x ( id int NOT NULL AUTO_INCREMENT, name varchar(255), ); SET names utf8; INSERT x(name) VALUES('Grüß'); SELECT name FROM x; -- this will return the the string correctly -- but the following one SELECT IF(name='',concat('-',.id,'-'),name) as name FROM x; -- will return a sting that doesn;t take into account the charset settings. Suggested fix: Make/Fix IF command 'names' setting aware.