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:
None 
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
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.
[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;