MySQL Bugs: #4959: COLLATE clause allowed without CHARACTER SET clause

Bug #4959	COLLATE clause allowed without CHARACTER SET clause
Submitted:	9 Aug 2004 15:50	Modified:	20 Sep 2006 14:58
Reporter:	Peter Gulutzan	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	4.1.4-beta-debug	OS:	Linux (SuSE 8.2)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
The MySQL Reference Manual describes the syntax thus: 
 
col_name {CHAR | VARCHAR | TEXT} (col_length) 
    [CHARACTER SET charset_name [COLLATE collation_name]] 
 
http://dev.mysql.com/doc/mysql/en/Charset-column.html 
 
But MySQL acts as if the syntax is described thus: 
 
col_name {CHAR | VARCHAR | TEXT} (col_length) 
    [CHARACTER SET charset_name ] [COLLATE collation_name] 
 
That is, it allows COLLATE without CHARACTER SET. 
 
 

How to repeat:
mysql> create table t103 (s1 char collate ucs2_czech_ci); 
Query OK, 0 rows affected (0.30 sec)

I can see three possible ways:

1. Disallow COLLATE clause without CHARACTER SET clause.
2. Allow COLLATE clause, and use the default character set.
3. Allow COLLATE clause, and take the character set from
   the given collation.

We had a conversation during Bordeaux conferece, and decided to
allow to use COLLATE clause without CHARACTER SET clause.
But I don't remember if we choosed the second or the third way.

Monty, as Peter insists on the first way, I have to ask you, if 
I should forget our Bordeaux agreement and go the first way,
or If I should choose betwen 2 and 3. Please suggest.

As this bug  is not on development stage, I'm reassigned it to Monty.

Sorry for the typo, please read: I'm reassigning it ...

Against latest BK source on Linux.

We can't remove in 4.1 or 5.0  that the user can only specify the collation as this would be a behaviour change (could cause old create table statements to fail).

So for these version, we should use the default character set for the table, database or
connection (as we are probably doing now)

We should of course give an error if the user has supplied a collation that doesn't match the given or implied character set in the same column specification. 

In 5.1 we will disallow the usage of COLLATION without CHARACTER set for CREATE TABLE to ensure that one doesn't use a CREATE statement in on version of MySQL that may fail in a newer version (becasue someone added another collation with the same name)