1. New data types, from the user point of view:

   BINARY(N) and VARBIBARY(N)
2. More 4.0 compatibility and more BINARY keyword consistency:
2a. CREATE TABLE a (a CHAR(N) BINARY) 
    is now synonym for
    CREATE TABLE a (a CHAR(N) COLLATE xxxx_bin)
2b. SELECT BINARY x
    is still synonin for
    SELECT x COLLATE xxxxx_bin.

5 files changed, 33 insertions, 23 deletions

diff --git a/sql/field.cc b/sql/field.cc
index b7362d74c5f..474715f4e26 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -4224,7 +4224,8 @@ void Field_string::sql_type(String &res) const
 			    (field_length > 3 &&
 			     (table->db_options_in_use &
 			      HA_OPTION_PACK_RECORD) ?
-			     "varchar" : "char"),
+			      (has_charset() ? "varchar" : "varbinary") : 
+			      (has_charset() ? "char" : "binary")),
 			    (int) field_length / charset()->mbmaxlen);
   res.length(length);
 }
diff --git a/sql/field.h b/sql/field.h
index 22d406b339a..e6ed5c2dbb7 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -864,7 +864,8 @@ public:
   uint max_packed_col_length(uint max_length);
   uint size_of() const { return sizeof(*this); }
   enum_field_types real_type() const { return FIELD_TYPE_STRING; }
-  bool has_charset(void) const { return TRUE; }
+  bool has_charset(void) const
+  { return charset() == &my_charset_bin ? FALSE : TRUE; }
   field_cast_enum field_cast_type() { return FIELD_CAST_STRING; }
 };
 
@@ -911,7 +912,8 @@ public:
   uint max_packed_col_length(uint max_length);
   uint size_of() const { return sizeof(*this); }
   enum_field_types real_type() const { return FIELD_TYPE_VAR_STRING; }
-  bool has_charset(void) const { return TRUE; }
+  bool has_charset(void) const
+  { return charset() == &my_charset_bin ? FALSE : TRUE; }
   field_cast_enum field_cast_type() { return FIELD_CAST_VARSTRING; }
 };
 
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 221045c40ca..30adc06fbd0 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -1247,26 +1247,21 @@ store_create_info(THD *thd, TABLE *table, String *packet)
     field->sql_type(type);
     packet->append(type.ptr(),type.length());
 
-    if (field->has_charset())
+    if (field->has_charset() && !limited_mysql_mode && !foreign_db_mode)
     {
-      if (field->charset() == &my_charset_bin)
-        packet->append(" binary", 7);
-      else if (!limited_mysql_mode && !foreign_db_mode)
+      if (field->charset() != table->table_charset)
       {
-	if (field->charset() != table->table_charset)
-	{
-	  packet->append(" character set ", 15);
-	  packet->append(field->charset()->csname);
-	}
-	/* 
-	  For string types dump collation name only if 
-	  collation is not primary for the given charset
-	*/
-	if (!(field->charset()->state & MY_CS_PRIMARY))
-	{
-	  packet->append(" collate ", 9);
-	  packet->append(field->charset()->name);
-	}
+	packet->append(" character set ", 15);
+	packet->append(field->charset()->csname);
+      }
+      /* 
+	For string types dump collation name only if 
+	collation is not primary for the given charset
+      */
+      if (!(field->charset()->state & MY_CS_PRIMARY))
+      {
+	packet->append(" collate ", 9);
+	packet->append(field->charset()->name);
       }
     }
 
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 099dab8885b..be3615c71ce 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -476,6 +476,18 @@ int mysql_create_table(THD *thd,const char *db, const char *table_name,
     */
     if (create_info->table_charset && sql_field->charset != &my_charset_bin)
       sql_field->charset= create_info->table_charset;
+
+    CHARSET_INFO *savecs= sql_field->charset;
+    if ((sql_field->flags & BINCMP_FLAG) &&
+	!(sql_field->charset= get_charset_by_csname(sql_field->charset->csname,
+						    MY_CS_BINSORT,MYF(0))))
+    {
+      char tmp[64];
+      strmake(strmake(tmp, savecs->csname, sizeof(tmp)-4), "_bin", 4);
+      my_error(ER_UNKNOWN_COLLATION, MYF(0), tmp);
+      DBUG_RETURN(-1);
+    }
+    
     sql_field->create_length_to_internal_length();
 
     /* Don't pack keys in old tables if the user has requested this */
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 8184ea366d9..90dc209f0bc 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1450,6 +1450,7 @@ attribute:
 	| UNIQUE_SYM	  { Lex->type|= UNIQUE_FLAG; }
 	| UNIQUE_SYM KEY_SYM { Lex->type|= UNIQUE_KEY_FLAG; }
 	| COMMENT_SYM TEXT_STRING_sys { Lex->comment= &$2; }
+	| BINARY { Lex->type|= BINCMP_FLAG; }
 	| COLLATE_SYM collation_name
 	  {
 	    if (Lex->charset && !my_charset_same(Lex->charset,$2))
@@ -1531,7 +1532,6 @@ opt_binary:
 	/* empty */			{ Lex->charset=NULL; }
 	| ASCII_SYM			{ Lex->charset=&my_charset_latin1; }
 	| BYTE_SYM			{ Lex->charset=&my_charset_bin; }
-	| BINARY			{ Lex->charset=&my_charset_bin; }
 	| UNICODE_SYM
 	{
 	  if (!(Lex->charset=get_charset_by_csname("ucs2",MY_CS_PRIMARY,MYF(0))))
@@ -3089,7 +3089,7 @@ in_sum_expr:
 	};
 
 cast_type:
-	BINARY			{ $$=ITEM_CAST_BINARY; Lex->charset= NULL; Lex->length= (char*)0; }
+	BINARY opt_len		{ $$=ITEM_CAST_CHAR; Lex->charset= &my_charset_bin; }
 	| CHAR_SYM opt_len opt_binary	{ $$=ITEM_CAST_CHAR; }
 	| NCHAR_SYM opt_len	{ $$=ITEM_CAST_CHAR; Lex->charset= national_charset_info; }
 	| SIGNED_SYM		{ $$=ITEM_CAST_SIGNED_INT; Lex->charset= NULL; Lex->length= (char*)0; }