field.cc, sql_mode.result, sql_mode.test:

  "SHOW CREATE TABLE" mysql-4.0 and mysql-3.23 
  compatibiliry mode change:
  Check that a binary collation adds 'binary'
  suffix into a char() column definition in
  mysql40 and mysql2323 modes. This allows
  not to lose the column's case sensitivity
  when loading the dump in pre-4.1 servers.

3 files changed, 65 insertions, 0 deletions

diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result
index e54dd217f8c..c18be2df403 100644
--- a/mysql-test/r/sql_mode.result
+++ b/mysql-test/r/sql_mode.result
@@ -85,6 +85,36 @@ t1	CREATE TABLE "t1" (
   UNIQUE KEY "email" ("email")
 )
 drop table t1;
+CREATE TABLE t1 (
+a char(10),
+b char(10) collate latin1_bin,
+c binary(10)
+) character set latin1;
+set @@sql_mode="";
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` char(10) default NULL,
+  `b` char(10) character set latin1 collate latin1_bin default NULL,
+  `c` binary(10) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+set @@sql_mode="mysql323";
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` char(10) default NULL,
+  `b` char(10) binary default NULL,
+  `c` binary(10) default NULL
+) TYPE=MyISAM
+set @@sql_mode="mysql40";
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` char(10) default NULL,
+  `b` char(10) binary default NULL,
+  `c` binary(10) default NULL
+) TYPE=MyISAM
+drop table t1;
 set session sql_mode = '';
 create table t1 ( min_num   dec(6,6)     default .000001);
 show create table t1;
diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test
index 63a5d6d3671..f841d36e837 100644
--- a/mysql-test/t/sql_mode.test
+++ b/mysql-test/t/sql_mode.test
@@ -30,6 +30,37 @@ show create table t1;
 drop table t1;
 
 #
+# Check that a binary collation adds 'binary'
+# suffix into a char() column definition in
+# mysql40 and mysql2323 modes. This allows
+# not to lose the column's case sensitivity
+# when loading the dump in pre-4.1 servers. 
+#
+# Thus, in 4.0 and 3.23 modes we dump:
+#
+#   'char(10) collate xxx_bin' as 'char(10) binary' 
+#   'binary(10)' as 'binary(10)'
+#
+# In mysql-4.1 these types are different, and they will
+# be recreated differently.
+#
+# In mysqld-4.0 the the above two types were the same,
+# so it will create a 'char(10) binary' column for both definitions.
+# 
+CREATE TABLE t1 (
+ a char(10),
+ b char(10) collate latin1_bin,
+ c binary(10)
+) character set latin1;
+set @@sql_mode="";
+show create table t1;
+set @@sql_mode="mysql323";
+show create table t1;
+set @@sql_mode="mysql40";
+show create table t1;
+drop table t1;
+
+#
 # BUG#5318 - failure: 'IGNORE_SPACE' affects numeric values after DEFAULT
 #
 # Force the usage of the default
diff --git a/sql/field.cc b/sql/field.cc
index 4b833874221..24bd0c48c92 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -4417,6 +4417,7 @@ void Field_string::sort_string(char *to,uint length)
 
 void Field_string::sql_type(String &res) const
 {
+  THD *thd= table->in_use;
   CHARSET_INFO *cs=res.charset();
   ulong length= cs->cset->snprintf(cs,(char*) res.ptr(),
 			    res.alloced_length(), "%s(%d)",
@@ -4427,6 +4428,9 @@ void Field_string::sql_type(String &res) const
 			      (has_charset() ? "char" : "binary")),
 			    (int) field_length / charset()->mbmaxlen);
   res.length(length);
+  if ((thd->variables.sql_mode & (MODE_MYSQL323 | MODE_MYSQL40)) &&
+      has_charset() && (charset()->state & MY_CS_BINSORT))
+    res.append(" binary");
 }
 
 char *Field_string::pack(char *to, const char *from, uint max_length)