Fix for bug #7977 in sql_mode=ANSI, show create table ignores auto_increment

  "CHARACTER SET", "COLLATE", and "DEFAULT" are always
  printed(excepting MODE_MYSQL323 and MODE_MYSQL40)
  "AUTO_INCREMENT", "ON UPDATE CURRENT_TIMESTAMP" are printed only 
  if NO_FIELD_OPTIONS is not set.


mysql-test/r/sql_mode.result:
  Fix for bug #7977 in sql_mode=ANSI, show create table ignores auto_increment
    test case
mysql-test/t/sql_mode.test:
  Fix for bug #7977 in sql_mode=ANSI, show create table ignores auto_increment
    test case

3 files changed, 41 insertions, 7 deletions

diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result
index 4e1693e15ec..d459b6acf6d 100644
--- a/mysql-test/r/sql_mode.result
+++ b/mysql-test/r/sql_mode.result
@@ -65,7 +65,7 @@ sql_mode	NO_FIELD_OPTIONS,MYSQL323,MYSQL40
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` int(11) NOT NULL auto_increment,
+  `a` int(11) NOT NULL,
   `pseudo` varchar(35) NOT NULL default '',
   `email` varchar(60) NOT NULL default '',
   PRIMARY KEY  (`a`),
@@ -79,8 +79,8 @@ show create table t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
   "a" int(11) NOT NULL,
-  "pseudo" varchar(35) NOT NULL default '',
-  "email" varchar(60) NOT NULL default '',
+  "pseudo" varchar(35) character set latin2 NOT NULL default '',
+  "email" varchar(60) character set latin2 NOT NULL default '',
   PRIMARY KEY  ("a"),
   UNIQUE KEY "email" ("email")
 )
@@ -140,3 +140,23 @@ t1	CREATE TABLE `t1` (
 drop table t1 ;
 set @@SQL_MODE=NULL;
 ERROR 42000: Variable 'sql_mode' can't be set to the value of 'NULL'
+set session sql_mode=ansi;
+create table t1
+(f1 integer auto_increment primary key,
+f2 timestamp default current_timestamp on update current_timestamp);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "f1" int(11) NOT NULL auto_increment,
+  "f2" timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+  PRIMARY KEY  ("f1")
+)
+set session sql_mode=no_field_options;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` timestamp NOT NULL default CURRENT_TIMESTAMP,
+  PRIMARY KEY  (`f1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test
index 4a5a136f54a..7877f9acc40 100644
--- a/mysql-test/t/sql_mode.test
+++ b/mysql-test/t/sql_mode.test
@@ -86,4 +86,16 @@ drop table t1 ;
 --error 1231
 set @@SQL_MODE=NULL;
 
+#
+# Bug #797: in sql_mode=ANSI, show create table ignores auto_increment
+#
+set session sql_mode=ansi;
+create table t1
+(f1 integer auto_increment primary key,
+ f2 timestamp default current_timestamp on update current_timestamp);
+show create table t1;
+set session sql_mode=no_field_options;
+show create table t1;
+drop table t1;
+
 # End of 4.1 tests
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index c3c28535954..e619b148f3a 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -1299,7 +1299,8 @@ store_create_info(THD *thd, TABLE *table, String *packet)
     field->sql_type(type);
     packet->append(type.ptr(), type.length(), system_charset_info);
 
-    if (field->has_charset() && !limited_mysql_mode && !foreign_db_mode)
+    if (field->has_charset() && 
+        !(thd->variables.sql_mode & (MODE_MYSQL323 | MODE_MYSQL40)))
     {
       if (field->charset() != table->table_charset)
       {
@@ -1337,7 +1338,7 @@ store_create_info(THD *thd, TABLE *table, String *packet)
     
     has_default= (field->type() != FIELD_TYPE_BLOB &&
 		  field->unireg_check != Field::NEXT_NUMBER &&
-                  !((foreign_db_mode || limited_mysql_mode) &&
+                  !((thd->variables.sql_mode & (MODE_MYSQL323 | MODE_MYSQL40)) &&
                     has_now_default));
 
     if (has_default)
@@ -1367,12 +1368,13 @@ store_create_info(THD *thd, TABLE *table, String *packet)
         packet->append(tmp);
     }
 
-    if (!foreign_db_mode && !limited_mysql_mode &&
+    if (!(thd->variables.sql_mode & MODE_NO_FIELD_OPTIONS) &&
         table->timestamp_field == field && 
         field->unireg_check != Field::TIMESTAMP_DN_FIELD)
       packet->append(" on update CURRENT_TIMESTAMP",28);
 
-    if (field->unireg_check == Field::NEXT_NUMBER && !foreign_db_mode)
+    if (field->unireg_check == Field::NEXT_NUMBER && 
+        !(thd->variables.sql_mode & MODE_NO_FIELD_OPTIONS))
       packet->append(" auto_increment", 15 );
 
     if (field->comment.length)