MySQL Bugs: #68358: Coolation gets reste to default

Bug #68358	Coolation gets reste to default
Submitted:	13 Feb 2013 9:59	Modified:	15 Feb 2013 18:22
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.5.30 - likely any 4.1+	OS:	Any ((I guess))
Assigned to:		CPU Architecture:	Any

Description:
Why reset collation to default on multiple SET NAMES statement like

SET NAMES 'utf8' COLLATE 'utf8_polish_ci';
SET NAMES 'utf8';

SHOW VARIABLES LIKE 'collation_connection'; -- returns 'utf8_general_ci'

How to repeat:
SET NAMES 'utf8' COLLATE 'utf8_polish_ci';
SET NAMES 'utf8';

SHOW VARIABLES LIKE 'collation_connection'; -- returns 'utf8_general_ci'

Suggested fix:
A SET NAMES statements with no COLLATE clause should not change 'collation_connection' it it already was a collation belonging to current charset, I think

I don't know (or even care) if current behavior is documented or not.  In either case it seems wrong to me in particular in cases where default collation for a server is a non-default collation for a given charset. I did not specify a collation change but collation changed anyway.

I'd actually say "expected behavior" as SET NAMES is supposed to set a sane state, and that would mean to me that after any SET NAMES charset and collation settings are always getting the same values no matter what the previous state was

So localized server settings are *insane*? :-)

Thank you for the report.

This behavior is consistent in all places where character sets and collations are used. For explanation see http://dev.mysql.com/doc/refman/5.5/en/charset-column.html:

 If both CHARACTER SET X and COLLATE Y are specified, character set X and collation Y are used.

CREATE TABLE t1
(
    col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci
) CHARACTER SET latin1 COLLATE latin1_bin;

The character set and collation are specified for the column, so they are used. The column has character set utf8 and collation utf8_unicode_ci.

If CHARACTER SET X is specified without COLLATE, character set X and its default collation are used.

CREATE TABLE t1
(
    col1 CHAR(10) CHARACTER SET utf8
) CHARACTER SET latin1 COLLATE latin1_bin;

The character set is specified for the column, but the collation is not. The column has character set utf8 and the default collation for utf8, which is utf8_general_ci. To see the default collation for each character set, use the SHOW COLLATION statement. 

This is not a bug.