MySQL Bugs: #18227: Problem with illegal mix of collations in VIEW (but not on TABLE)

Bug #18227	Problem with illegal mix of collations in VIEW (but not on TABLE)
Submitted:	14 Mar 2006 15:28	Modified:	15 Mar 2006 0:10
Reporter:	Mats-Ove Fant	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Linux (Fedora Core 4)
Assigned to:		CPU Architecture:	Any

Description:
When creating a view on a table where the SELECT statement includes a number of standard functions, usage of the view fails due to error message: "ERROR 1267 (HY000): Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='"

Executing the SELECT statement standalone doesn't produce the error. It seems like the collation used by the view itself differs from the one set on connection, database and server.

It must be noted that collation_connection is set in the session to be in line with database and server collation. One interpretation of the error message could be that the VIEW ignores the variable setting of the collation_connection (since latin1_swedish_ci is the session default).

How to repeat:
Perform the following steps:

mysql> SHOW VARIABLES LIKE '%version%';
+-------------------------+------------------------------------------+
| Variable_name           | Value                                    |
+-------------------------+------------------------------------------+
| protocol_version        | 10                                       |
| version                 | 5.0.18-standard-log                      |
| version_comment         | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686                                     |
| version_compile_os      | pc-linux-gnu                             |
+-------------------------+------------------------------------------+
5 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_general_ci |
| collation_server     | latin1_general_ci |
+----------------------+-------------------+
3 rows in set (0.01 sec)

mysql> SET collation_connection='latin1_general_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_general_ci |
| collation_database   | latin1_general_ci |
| collation_server     | latin1_general_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.00 sec)

mysql> USE test_db;
Database changed
mysql> CREATE TABLE test_table (sysid INT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(16) NOT NULL, PRIMARY KEY(sysid)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.36 sec)

mysql> INSERT INTO test_table SET name='testuser';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_table;
+-------+---------------+
| sysid | name          |
+-------+---------------+
|     1 | testuser |
+-------+---------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test_table WHERE name = substring_index(substring_index(CURRENT_USER(),'@',1),'.',-1);
Empty set (0.00 sec)

mysql> CREATE VIEW test_view AS SELECT sysid,name FROM test_table WHERE name = substring_index(substring_index(CURRENT_USER(),'@',1),'.',-1);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test_view;
ERROR 1267 (HY000): Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

Redefining the VIEW as this solves the problem:

CREATE VIEW test_view AS SELECT sysid,name FROM test_table WHERE name COLLATE latin1_swedish_ci =
substring_index(substring_index(CURRENT_USER(),'@',1),'.',-1);

The question is why I'm forced to set the collation of the column to latin1_swedish_ci in the VIEW definition, while it's not required in the standalone SELECT statement.

Thank you for the bug report. Testing with current source server I was
unable to repeat:

miguel@hegel:~/dbs/5.1> bin/mysql -uroot 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 1 to server version: 5.0.20-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW VARIABLES LIKE '%version%';
+-------------------------+----------------------------------------------------------+
| Variable_name           | Value                                                    |
+-------------------------+----------------------------------------------------------+
| protocol_version        | 10                                                       |
| version                 | 5.0.20-debug                                             |
| version_bdb             | Sleepycat Software: Berkeley DB 4.1.24: (April 30, 2003) |
| version_comment         | Source distribution                                      |
| version_compile_machine | i686                                                     |
| version_compile_os      | suse-linux                                               |
+-------------------------+----------------------------------------------------------+
6 rows in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+--------------------------------------------+
| Variable_name            | Value                                      |
+--------------------------+--------------------------------------------+
| character_set_client     | latin1                                     |
| character_set_connection | latin1                                     |
| character_set_database   | latin1                                     |
| character_set_filesystem | binary                                     |
| character_set_results    | latin1                                     |
| character_set_server     | latin1                                     |
| character_set_system     | utf8                                       |
| character_sets_dir       | /home/miguel/dbs/5.0/share/mysql/charsets/ |
+--------------------------+--------------------------------------------+
8 rows in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> SET collation_connection='latin1_general_ci';
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_general_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.01 sec)

mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.00 sec)

mysql> USE test_db;
Database changed
mysql> CREATE TABLE test_table (sysid INT UNSIGNED NOT NULL AUTO_INCREMENT, name
    -> CHAR(16) NOT NULL, PRIMARY KEY(sysid)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO test_table SET name='testuser';
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM test_table;
+-------+----------+
| sysid | name     |
+-------+----------+
| 1     | testuser |
+-------+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test_table WHERE name =
    -> substring_index(substring_index(CURRENT_USER(),'@',1),'.',-1);
Empty set (0.05 sec)

mysql> CREATE VIEW test_view AS SELECT sysid,name FROM test_table WHERE name =
    -> substring_index(substring_index(CURRENT_USER(),'@',1),'.',-1);
Query OK, 0 rows affected (0.04 sec)

mysql>  SELECT * FROM test_view;
Empty set (0.03 sec)

I had exact same issue, and resolved it with:

1. Setting default server collation to latin1_general_ci (using My SQL Administrator utility) and restarting the mysql service

2. Altering the table to set the default table collation to latin1_general_ci

After I restarted MySql browser utility, the stored proc stopped complaining about this issue, as all collations are set to the same value now.

collations:

'collation_connection', 'utf8_general_ci'
'collation_database', 'latin1_general_ci'
'collation_server', 'latin1_general_ci'
'protocol_version', '10'
'slave_compressed_protocol', 'OFF'

versions:

'character_set_server', 'latin1'
'collation_server', 'latin1_general_ci'
'innodb_force_recovery', '0'
'myisam_recover_options', 'OFF'
'protocol_version', '10'
'rpl_recovery_rank', '0'
'server_id', '0'
'version', '5.0.51a-community-nt'
'version_comment', 'MySQL Community Edition (GPL)'
'version_compile_machine', 'ia32'
'version_compile_os', 'Win32'

Cheers
jano