Ensure that fields declared with NOT NULL doesn't have DEFAULT values if not specified and if not timestamp or auto_increment

In original code, sometimes one got an automatic DEFAULT value in some cases, in other cases not.

For example:
create table t1 (a int primary key)      - No default
create table t2 (a int, primary key(a))  - DEFAULT 0
create table t1 SELECT ....              - Default for all fields, even if they where defined as NOT NULL
ALTER TABLE ... MODIFY could sometimes add an unexpected DEFAULT value.

The patch is quite big because we had some many test cases that used
CREATE ... SELECT or CREATE ... (...PRIMARY KEY(xxx)) which doesn't have an automatic DEFAULT anymore.

Other things:
- Removed warnings from InnoDB when waiting from semaphore (got this when testing things with --big)

5 files changed, 9 insertions, 9 deletions

diff --git a/mysql-test/suite/innodb/r/innodb-16k.result b/mysql-test/suite/innodb/r/innodb-16k.result
index ed1a827e4ec..9e6e5145fa0 100644
--- a/mysql-test/suite/innodb/r/innodb-16k.result
+++ b/mysql-test/suite/innodb/r/innodb-16k.result
@@ -299,7 +299,7 @@ a	LENGTH(b)	b=LEFT(REPEAT(d,100*a), 65535)	LENGTH(c)	c=REPEAT(d,20*a)	d
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` int(11) NOT NULL DEFAULT '0',
+  `a` int(11) NOT NULL,
   `b` blob NOT NULL,
   `c` text NOT NULL,
   `d` text NOT NULL,
diff --git a/mysql-test/suite/innodb/r/innodb-autoinc.result b/mysql-test/suite/innodb/r/innodb-autoinc.result
index 6ecb6055f26..6ac8bb53c66 100644
--- a/mysql-test/suite/innodb/r/innodb-autoinc.result
+++ b/mysql-test/suite/innodb/r/innodb-autoinc.result
@@ -1286,7 +1286,7 @@ t1	CREATE TABLE `t1` (
 SELECT * FROM t1;
 c1	c2
 2147483648	a
-ALTER TABLE t1 CHANGE c1 c1 INT;
+ALTER TABLE t1 CHANGE c1 c1 INT default 0;
 Warnings:
 Warning	1264	Out of range value for column 'c1' at row 1
 SHOW CREATE TABLE t1;
diff --git a/mysql-test/suite/innodb/r/innodb-index.result b/mysql-test/suite/innodb/r/innodb-index.result
index 996bc45f7e2..b151e20d321 100644
--- a/mysql-test/suite/innodb/r/innodb-index.result
+++ b/mysql-test/suite/innodb/r/innodb-index.result
@@ -528,7 +528,7 @@ info: Records: 0  Duplicates: 0  Warnings: 0
 show create table t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `alpha` int(11) NOT NULL DEFAULT '0',
+  `alpha` int(11) NOT NULL,
   `beta` int(11) DEFAULT NULL,
   `c` int(11) NOT NULL,
   `d` int(11) DEFAULT NULL,
@@ -1166,7 +1166,7 @@ t2	CREATE TABLE `t2` (
 show create table t2i;
 Table	Create Table
 t2i	CREATE TABLE `t2i` (
-  `a` int(11) NOT NULL DEFAULT '0',
+  `a` int(11) NOT NULL,
   `b` char(1) NOT NULL,
   PRIMARY KEY (`a`,`b`),
   KEY `t2a` (`a`)
@@ -1174,7 +1174,7 @@ t2i	CREATE TABLE `t2i` (
 show create table t2c;
 Table	Create Table
 t2c	CREATE TABLE `t2c` (
-  `a` int(11) NOT NULL DEFAULT '0',
+  `a` int(11) NOT NULL,
   `b` char(1) NOT NULL,
   PRIMARY KEY (`a`,`b`),
   KEY `t2a` (`a`)
diff --git a/mysql-test/suite/innodb/r/innodb_bug21704.result b/mysql-test/suite/innodb/r/innodb_bug21704.result
index 239aeaa354d..c0bc3af2f20 100644
--- a/mysql-test/suite/innodb/r/innodb_bug21704.result
+++ b/mysql-test/suite/innodb/r/innodb_bug21704.result
@@ -54,14 +54,14 @@ info: Records: 0  Duplicates: 0  Warnings: 0
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `e` int(11) NOT NULL DEFAULT '0',
+  `e` int(11) NOT NULL,
   `c` int(11) DEFAULT NULL,
   PRIMARY KEY (`e`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `z` int(11) NOT NULL DEFAULT '0',
+  `z` int(11) NOT NULL,
   `c` int(11) DEFAULT NULL,
   PRIMARY KEY (`z`),
   CONSTRAINT `fk1` FOREIGN KEY (`z`) REFERENCES `t1` (`e`)
@@ -69,7 +69,7 @@ t2	CREATE TABLE `t2` (
 SHOW CREATE TABLE t3;
 Table	Create Table
 t3	CREATE TABLE `t3` (
-  `f` int(11) NOT NULL DEFAULT '0',
+  `f` int(11) NOT NULL,
   `g` int(11) DEFAULT NULL,
   `d` int(11) DEFAULT NULL,
   PRIMARY KEY (`f`),
diff --git a/mysql-test/suite/innodb/t/innodb-autoinc.test b/mysql-test/suite/innodb/t/innodb-autoinc.test
index ffe4ac9c3ae..ab708e8762c 100644
--- a/mysql-test/suite/innodb/t/innodb-autoinc.test
+++ b/mysql-test/suite/innodb/t/innodb-autoinc.test
@@ -659,7 +659,7 @@ CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENG
 INSERT INTO t1 VALUES (2147483648, 'a');
 SHOW CREATE TABLE t1;
 SELECT * FROM t1;
-ALTER TABLE t1 CHANGE c1 c1 INT;
+ALTER TABLE t1 CHANGE c1 c1 INT default 0;
 SHOW CREATE TABLE t1;
 INSERT INTO t1(c2) VALUES('b');
 SELECT * FROM t1;