Fix for bug#17626 CREATE TABLE ... SELECT failure with TRADITIONAL SQL mode

transfer NO_DEFAULT_VALUE_FLAG flag to new field

4 files changed, 23 insertions, 2 deletions

diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result
index 2b1a47ed337..271cd7bf486 100644
--- a/mysql-test/r/strict.result
+++ b/mysql-test/r/strict.result
@@ -1288,3 +1288,13 @@ ERROR 22001: Data too long for column 'a' at row 1
 select * from t1;
 a
 drop table t1;
+set sql_mode='traditional';
+create table t1 (date date not null);
+create table t2 select date from t1;
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `date` date NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t2,t1;
+set @@sql_mode= @org_mode;
diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
index bcdba3dca76..bd89c09e94d 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -241,7 +241,7 @@ Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 auto	int(5) unsigned	NULL	NO		0		#	
 string	char(10)	latin1_swedish_ci	YES		newdefault		#	
 tiny	tinyint(4)	NULL	NO		0		#	
-short	smallint(6)	NULL	NO		0		#	
+short	smallint(6)	NULL	NO				#	
 medium	mediumint(8)	NULL	NO		0		#	
 long_int	int(11)	NULL	NO		0		#	
 longlong	bigint(13)	NULL	NO		0		#	
@@ -259,7 +259,7 @@ date_time	datetime	NULL	YES		NULL		#
 new_blob_col	varchar(20)	latin1_swedish_ci	YES		NULL		#	
 tinyblob_col	tinyblob	NULL	YES		NULL		#	
 mediumblob_col	mediumblob	NULL	NO				#	
-options	enum('one','two','tree')	latin1_swedish_ci	NO		one		#	
+options	enum('one','two','tree')	latin1_swedish_ci	NO				#	
 flags	set('one','two','tree')	latin1_swedish_ci	NO				#	
 new_field	char(10)	latin1_swedish_ci	NO		new		#	
 select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.time_field<>t2.time_field and (t1.time_field is not null or t2.time_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.new_blob_col<>t2.new_blob_col and (t1.new_blob_col is not null or t2.new_blob_col is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)) or (t1.new_field<>t2.new_field and (t1.new_field is not null or t2.new_field is not null)));
diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test
index d3b36cbc2a8..5044a20ae9f 100644
--- a/mysql-test/t/strict.test
+++ b/mysql-test/t/strict.test
@@ -1145,3 +1145,13 @@ create table t1(a bit(2));
 insert into t1 values(b'101');
 select * from t1;
 drop table t1;
+
+#
+# Bug#17626 CREATE TABLE ... SELECT failure with TRADITIONAL SQL mode
+#
+set sql_mode='traditional';
+create table t1 (date date not null);
+create table t2 select date from t1;
+show create table t2;
+drop table t2,t1;
+set @@sql_mode= @org_mode;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 1fe1747c44f..d6e1650ba83 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7984,6 +7984,7 @@ Field* create_tmp_field_from_field(THD *thd, Field* org_field,
       item->result_field= new_field;
     else
       new_field->field_name= name;
+    new_field->flags|= (org_field->flags & NO_DEFAULT_VALUE_FLAG);
     if (org_field->maybe_null() || (item && item->maybe_null))
       new_field->flags&= ~NOT_NULL_FLAG;	// Because of outer join
     if (org_field->type() == MYSQL_TYPE_VAR_STRING ||