MySQL Bugs: #32538: View definition picks up character set, but not collation

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

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.

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)

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.

Pushed into 5.1-runtime.

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.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

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.

Also documented in the 5.1.23-ndb-6.3.11 changelog.