Bug #32538 View definition picks up character set, but not collation
Submitted: 20 Nov 2007 18:14 Modified: 15 Mar 2008 9:10
Reporter: Scott Noyes Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Any
Assigned to: Alexander Nozdrin CPU Architecture:Any
Tags: bfsm_2007_12_06, regression

[20 Nov 2007 18:14] Scott Noyes
Description:
A view created with a string literal for one of the columns picks up the connection character set, but not the collation. Comparison to that field therefore uses the default collation for that character set, which causes an error if the connection_collation is not compatible with that default collation.

Appears related to http://bugs.mysql.com/bug.php?id=21505

How to repeat:
SET collation_connection = 'latin1_general_cs';
CREATE VIEW myView AS SELECT 'test' AS test;
SHOW CREATE VIEW myView; -- note the _latin1'test' in the output
SELECT * FROM myView WHERE test = 'test';

Result:

ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin1_general_cs,COERCIBLE) for operation '='

Suggested fix:
For 5.0, add a COLLATE clause, using connection_collation's value, to any strings which have the explicit character set added by the code. For the above case, the string should become _latin1'test' COLLATE latin1_general_cs

For 5.1, which stores a character set and collation with the view definition, do not add a character set to strings that do not already have one.

Or, leave the character set and collation off the strings altogether, let them use the current value of whatever system variable is appropriate, and document accordingly.
[20 Nov 2007 18:16] Kolbe Kegel
mysql 5.0.50-enterprise-gpl (root) [test]> select collation('test');
+-------------------+
| collation('test') |
+-------------------+
| latin1_general_cs | 
+-------------------+
1 row in set (0.00 sec)

mysql 5.0.50-enterprise-gpl (root) [test]> create view v1 as select 'test' as col1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.0.50-enterprise-gpl (root) [test]> select collation(col1) from v1;
+-------------------+
| collation(col1)   |
+-------------------+
| latin1_swedish_ci | 
+-------------------+
1 row in set (0.00 sec)

mysql 5.0.50-enterprise-gpl (root) [test]> show create view v1\G
*************************** 1. row ***************************
       View: v1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'test' AS `col1`
1 row in set (0.00 sec)
[12 Feb 2008 19:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42132

ChangeSet@1.2548, 2008-02-12 22:09:16+03:00, anozdrin@quad. +37 -0
  Fix for Bug#32538: View definition picks up character set,
  but not collation.
  
  The problem here was that text literals in a view were always
  dumped with character set introducer. That lead to loosing
  collation information.
  
  The fix is to dump character set introducer only if it was
  in the original query. That is now possible because there 
  is no problem any more of loss of character set of string
  literals in views -- after WL#4052 the view is dumped 
  in the original character set.
[12 Feb 2008 19:24] Alexander Nozdrin
Pushed into 5.1-runtime.
[13 Feb 2008 8:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42197

ChangeSet@1.2549, 2008-02-13 12:02:16+03:00, anozdrin@quad. +2 -0
  Additional patch for Bug#32538. Fix result files of windows test cases.
[3 Mar 2008 18:19] Bugs System
Pushed into 5.1.24-rc
[3 Mar 2008 18:19] Bugs System
Pushed into 6.0.5-alpha
[15 Mar 2008 9:10] Jon Stephens
Documented bugfix in the 5.1.24 and 6.0.5 changelogs as follows:

        A view created with a string literal for one of the columns picked up
        the connection character set, but not the collation. Comparison to that
        field therefore used the default collation for that character set,
        causing an error if the connection collation was not compatible with the
        default collation. The problem was caused by text literals in a view
        being dumped with a character set introducer even when this was not
        necessary, sometimes leading to a loss of collation information. Now the
        character set introducer is dumped only if it was included in the 
        original query.
[31 Mar 2008 19:01] Jon Stephens
Also documented in the 5.1.23-ndb-6.3.11 changelog.