Merge

mysql-test/r/create.result:
  Auto merged
mysql-test/r/ctype_latin1_de.result:
  Auto merged
mysql-test/r/grant.result:
  Auto merged
mysql-test/r/grant2.result:
  Auto merged
mysql-test/r/limit.result:
  Auto merged
mysql-test/r/order_by.result:
  Auto merged
mysql-test/r/rpl000001.result:
  Auto merged
mysql-test/r/sp.result:
  Auto merged
mysql-test/r/strict.result:
  Auto merged
mysql-test/r/type_blob.result:
  Auto merged
mysql-test/r/type_ranges.result:
  Auto merged
mysql-test/r/warnings.result:
  Auto merged
mysql-test/t/ctype_latin1_de.test:
  Auto merged
mysql-test/t/limit.test:
  Auto merged
mysql-test/t/order_by.test:
  Auto merged
mysql-test/t/show_check.test:
  Auto merged
mysql-test/t/sp.test:
  Auto merged
mysql-test/t/type_blob.test:
  Auto merged
mysql-test/t/type_ranges.test:
  Auto merged
sql/item.cc:
  Auto merged
sql/item.h:
  Auto merged
sql/sql_insert.cc:
  Auto merged
mysql-test/t/strict.test:
  SCCS merged

12 files changed, 57 insertions, 24 deletions

diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test
index 512ae88a445..e17806a54aa 100644
--- a/mysql-test/t/ctype_latin1_de.test
+++ b/mysql-test/t/ctype_latin1_de.test
@@ -66,7 +66,7 @@ drop table t1;
 #
 # The below checks both binary and character comparisons.
 #
-create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word));
+create table t1 (word varchar(255) not null, word2 varchar(255) not null default '', index(word));
 show create table t1;
 insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae');
 update t1 set word2=word;
diff --git a/mysql-test/t/ctype_many.test b/mysql-test/t/ctype_many.test
index 26057e7c997..454e2c63cbb 100644
--- a/mysql-test/t/ctype_many.test
+++ b/mysql-test/t/ctype_many.test
@@ -8,7 +8,7 @@ SET CHARACTER SET latin1;
 
 CREATE TABLE t1 (
   comment CHAR(32) ASCII NOT NULL,
-  koi8_ru_f CHAR(32) CHARACTER SET koi8r NOT NULL
+  koi8_ru_f CHAR(32) CHARACTER SET koi8r NOT NULL default ''
 ) CHARSET=latin5;
 
 SHOW CREATE TABLE t1;
@@ -142,13 +142,13 @@ INSERT INTO t1 (koi8_ru_f,comment) VALUES (_koi8r'ñ','CYR CAPIT YA');
 SET CHARACTER SET utf8;
 SELECT koi8_ru_f,MIN(comment),COUNT(*) FROM t1 GROUP BY 1;
 
-ALTER TABLE t1 ADD utf8_f CHAR(32) CHARACTER SET utf8 NOT NULL;
+ALTER TABLE t1 ADD utf8_f CHAR(32) CHARACTER SET utf8 NOT NULL default '';
 UPDATE t1 SET utf8_f=CONVERT(koi8_ru_f USING utf8);
 SET CHARACTER SET koi8r;
 SELECT * FROM t1;
 
 
-ALTER TABLE t1 ADD bin_f CHAR(32) BYTE NOT NULL;
+ALTER TABLE t1 ADD bin_f CHAR(32) BYTE NOT NULL default '';
 UPDATE t1 SET bin_f=koi8_ru_f;
 SELECT COUNT(DISTINCT bin_f),COUNT(DISTINCT koi8_ru_f),COUNT(DISTINCT utf8_f) FROM t1;
 
diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test
index 716dd38a119..da59c6ae5e4 100644
--- a/mysql-test/t/gis-rtree.test
+++ b/mysql-test/t/gis-rtree.test
@@ -120,7 +120,6 @@ INSERT INTO t1 (g) VALUES (GeomFromText('LineString(1 2, 2 3)')),(GeomFromText('
 drop table t1;
 
 CREATE TABLE t1 (
-  geoobjid INT NOT NULL,
   line LINESTRING NOT NULL,
   kind ENUM('po', 'pp', 'rr', 'dr', 'rd', 'ts', 'cl') NOT NULL DEFAULT 'po',
   name VARCHAR(32),
diff --git a/mysql-test/t/index_merge_ror.test b/mysql-test/t/index_merge_ror.test
index 223bd241d96..5375f9d1a31 100644
--- a/mysql-test/t/index_merge_ror.test
+++ b/mysql-test/t/index_merge_ror.test
@@ -8,13 +8,13 @@ drop table if exists  t0,t1,t2;
 create table t1
 (
   /* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
-  st_a int not null,
-  swt1a int not null,
-  swt2a int not null,
+  st_a int not null default 0,
+  swt1a int not null default 0,
+  swt2a int not null default 0,
 
-  st_b int not null,
-  swt1b int not null,
-  swt2b int not null,
+  st_b int not null default 0,
+  swt1b int not null default 0,
+  swt2b int not null default 0,
 
   /* fields/keys for row retrieval tests */
   key1 int,
diff --git a/mysql-test/t/limit.test b/mysql-test/t/limit.test
index 28b287a5d4a..3dc56295375 100644
--- a/mysql-test/t/limit.test
+++ b/mysql-test/t/limit.test
@@ -6,7 +6,7 @@
 drop table if exists t1;
 --enable_warnings
 
-create table t1 (a int primary key, b int not null);
+create table t1 (a int not null default 0 primary key, b int not null default 0);
 insert into t1 () values ();		-- Testing default values
 insert into t1 values (1,1),(2,1),(3,1);
 update t1 set a=4 where b=1 limit 1;
diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test
index 4bc6a4f051a..934b165f604 100644
--- a/mysql-test/t/null.test
+++ b/mysql-test/t/null.test
@@ -59,7 +59,7 @@ drop table t1;
 #
 # Test inserting and updating with NULL
 #
-CREATE TABLE t1 (a varchar(16) NOT NULL, b smallint(6) NOT NULL, c datetime NOT NULL, d smallint(6) NOT NULL);
+CREATE TABLE t1 (a varchar(16) NOT NULL default '', b smallint(6) NOT NULL default '', c datetime NOT NULL default '0000-00-00 00:00:00', d smallint(6) NOT NULL default 0);
 INSERT INTO t1 SET a = "", d= "2003-01-14 03:54:55";
 UPDATE t1 SET d=1/NULL;
 UPDATE t1 SET d=NULL;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 9608b7900ee..36eee6e43b7 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -206,7 +206,7 @@ CREATE TABLE t1 (
   favo_tv varchar(50) NOT NULL default '',
   favo_eten varchar(50) NOT NULL default '',
   favo_muziek varchar(30) NOT NULL default '',
-  info text NOT NULL,
+  info text NOT NULL default '',
   ipnr varchar(30) NOT NULL default '',
   PRIMARY KEY  (member_id)
 ) ENGINE=MyISAM PACK_KEYS=1;
diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test
index 23aec58e419..0004591288f 100644
--- a/mysql-test/t/show_check.test
+++ b/mysql-test/t/show_check.test
@@ -106,7 +106,7 @@ drop table t1;
 # Do a create table that tries to cover all types and options
 #
 create table t1 (
-type_bool bool not null,
+type_bool bool not null default false,
 type_tiny tinyint not null auto_increment primary key,
 type_short smallint(3),
 type_mediumint mediumint,
@@ -117,9 +117,9 @@ empty_char char(0),
 type_char char(2),
 type_varchar varchar(10),
 type_timestamp timestamp not null,
-type_date date not null,
-type_time time not null,
-type_datetime datetime not null,
+type_date date not null default '0000-00-00',
+type_time time not null default '00:00:00',
+type_datetime datetime not null default '0000-00-00 00:00:00',
 type_year year,
 type_enum enum ('red', 'green', 'blue'),
 type_set enum ('red', 'green', 'blue'),
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index b8eb371931f..a741a4af7d8 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -24,7 +24,7 @@ use test;
 drop table if exists t1;
 --enable_warnings
 create table t1 (
-	id   char(16) not null,
+	id   char(16) not null default '',
         data int not null
 );
 --disable_warnings
diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test
index 6b7ce1a8f9e..333ec40b1b9 100644
--- a/mysql-test/t/strict.test
+++ b/mysql-test/t/strict.test
@@ -1049,3 +1049,37 @@ drop table t1;
 # Restore mode
 #
 set sql_mode=@org_mode;
+
+# Test fields with no default value that are NOT NULL (Bug #5986)
+SET @@sql_mode = 'traditional';
+CREATE TABLE t1 (i int not null);
+--error 1364
+INSERT INTO t1 VALUES ();
+--error 1364
+INSERT INTO t1 VALUES (DEFAULT);
+--error 1364
+INSERT INTO t1 VALUES (DEFAULT(i));
+ALTER TABLE t1 ADD j int;
+--error 1364
+INSERT INTO t1 SET j = 1;
+--error 1364
+INSERT INTO t1 SET j = 1, i = DEFAULT;
+--error 1364
+INSERT INTO t1 SET j = 1, i = DEFAULT(i);
+--error 1364
+INSERT INTO t1 VALUES (DEFAULT,1);
+DROP TABLE t1;
+SET @@sql_mode = '';
+CREATE TABLE t1 (i int not null);
+INSERT INTO t1 VALUES ();
+INSERT INTO t1 VALUES (DEFAULT);
+# DEFAULT(i) is an error even with the default sql_mode
+--error 1364
+INSERT INTO t1 VALUES (DEFAULT(i));
+ALTER TABLE t1 ADD j int;
+INSERT INTO t1 SET j = 1;
+INSERT INTO t1 SET j = 1, i = DEFAULT;
+--error 1364
+INSERT INTO t1 SET j = 1, i = DEFAULT(i);
+INSERT INTO t1 VALUES (DEFAULT,1);
+DROP TABLE t1;
diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test
index 456c65e57d9..c33ea3f435d 100644
--- a/mysql-test/t/type_blob.test
+++ b/mysql-test/t/type_blob.test
@@ -304,7 +304,7 @@ drop table t1;
 # Bug when blob is updated
 #
 
-create table t1 (id integer auto_increment unique,imagem LONGBLOB not null);
+create table t1 (id integer auto_increment unique,imagem LONGBLOB not null default '');
 insert into t1 (id) values (1);
 # We have to clean up the path in the results for safe comparison
 --replace_result $MYSQL_TEST_DIR ../..
diff --git a/mysql-test/t/type_ranges.test b/mysql-test/t/type_ranges.test
index cada0c371f0..a2deb367e14 100644
--- a/mysql-test/t/type_ranges.test
+++ b/mysql-test/t/type_ranges.test
@@ -28,10 +28,10 @@ CREATE TABLE t1 (
   date_time datetime,
   blob_col blob,
   tinyblob_col tinyblob,
-  mediumblob_col mediumblob  not null,
-  longblob_col longblob  not null,
-  options enum('one','two','tree') not null,
-  flags set('one','two','tree') not null,
+  mediumblob_col mediumblob  not null default '',
+  longblob_col longblob  not null default '',
+  options enum('one','two','tree') not null ,
+  flags set('one','two','tree') not null default '',
   PRIMARY KEY (auto),
   KEY (utiny),
   KEY (tiny),