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, 10 insertions, 10 deletions

diff --git a/mysql-test/suite/funcs_1/r/innodb_views.result b/mysql-test/suite/funcs_1/r/innodb_views.result
index e7523922154..59eec5b0220 100644
--- a/mysql-test/suite/funcs_1/r/innodb_views.result
+++ b/mysql-test/suite/funcs_1/r/innodb_views.result
@@ -22032,7 +22032,7 @@ Testcase 3.3.2.1 - 3.3.2.6 alternative implementation
 --------------------------------------------------------------------------------
 DROP TABLE IF EXISTS t1;
 DROP VIEW  IF EXISTS v1;
-CREATE TABLE t1 ( f1 BIGINT, f2 CHAR(20), f3 NUMERIC(7,4),
+CREATE TABLE t1 ( f1 BIGINT DEFAULT 0, f2 CHAR(20), f3 NUMERIC(7,4),
 f4 CHAR, PRIMARY KEY(f1));
 CREATE VIEW v1 AS SELECT f1, f2, f3 FROM t1;
 INSERT INTO v1 SET f1 = 1;
diff --git a/mysql-test/suite/funcs_1/r/is_columns.result b/mysql-test/suite/funcs_1/r/is_columns.result
index e3d66a1ed6c..ada8110d229 100644
--- a/mysql-test/suite/funcs_1/r/is_columns.result
+++ b/mysql-test/suite/funcs_1/r/is_columns.result
@@ -125,10 +125,10 @@ def	db_datadict	t1	f1	1	NULL	YES	char	10	10	NULL	NULL	NULL	latin1	latin1_swedish
 def	db_datadict	t1	f2	2	NULL	YES	text	65535	65535	NULL	NULL	NULL	latin1	latin1_swedish_ci	text			select,insert,update,references	
 def	db_datadict	t1	f3	3	NULL	YES	date	NULL	NULL	NULL	NULL	NULL	NULL	NULL	date			select,insert,update,references	
 def	db_datadict	t1	f4	4	NULL	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)	PRI	auto_increment	select,insert,update,references	
-def	db_datadict	t2	f1	1		NO	char	10	10	NULL	NULL	NULL	latin1	latin1_swedish_ci	char(10)	PRI		select,insert,update,references	
+def	db_datadict	t2	f1	1	NULL	NO	char	10	10	NULL	NULL	NULL	latin1	latin1_swedish_ci	char(10)	PRI		select,insert,update,references	
 def	db_datadict	t2	f2	2	NULL	YES	text	65535	65535	NULL	NULL	NULL	latin1	latin1_swedish_ci	text			select,insert,update,references	
 def	db_datadict	t2	f3	3	NULL	YES	date	NULL	NULL	NULL	NULL	NULL	NULL	NULL	date			select,insert,update,references	
-def	db_datadict	t2	f4	4	0	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)	PRI		select,insert,update,references	
+def	db_datadict	t2	f4	4	NULL	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)	PRI		select,insert,update,references	
 def	db_datadict	v1	f1	1	0	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int(1)			select,insert,update,references	
 def	db_datadict	v1	f2	2	0	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int(1)			select,insert,update,references	
 SHOW COLUMNS FROM db_datadict.t1;
@@ -139,10 +139,10 @@ f3	date	YES		NULL
 f4	int(11)	NO	PRI	NULL	auto_increment
 SHOW COLUMNS FROM db_datadict.t2;
 Field	Type	Null	Key	Default	Extra
-f1	char(10)	NO	PRI		
+f1	char(10)	NO	PRI	NULL	
 f2	text	YES		NULL	
 f3	date	YES		NULL	
-f4	int(11)	NO	PRI	0	
+f4	int(11)	NO	PRI	NULL	
 SHOW COLUMNS FROM db_datadict.v1;
 Field	Type	Null	Key	Default	Extra
 f1	int(1)	NO		0	
@@ -169,13 +169,13 @@ SELECT * FROM information_schema.columns
 WHERE table_schema = 'db_datadict'
 ORDER BY table_schema, table_name, ordinal_position;
 TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT
-def	db_datadict	t2	f1	1		NO	char	10	10	NULL	NULL	NULL	latin1	latin1_swedish_ci	char(10)	PRI		insert	
+def	db_datadict	t2	f1	1	NULL	NO	char	10	10	NULL	NULL	NULL	latin1	latin1_swedish_ci	char(10)	PRI		insert	
 def	db_datadict	t2	f2	2	NULL	YES	text	65535	65535	NULL	NULL	NULL	latin1	latin1_swedish_ci	text			insert	
 SHOW COLUMNS FROM db_datadict.t1;
 ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 't1'
 SHOW COLUMNS FROM db_datadict.t2;
 Field	Type	Null	Key	Default	Extra
-f1	char(10)	NO	PRI		
+f1	char(10)	NO	PRI	NULL	
 f2	text	YES		NULL	
 SHOW COLUMNS FROM db_datadict.v1;
 ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 'v1'
diff --git a/mysql-test/suite/funcs_1/r/memory_views.result b/mysql-test/suite/funcs_1/r/memory_views.result
index 9f8ec11a7d8..995787aba84 100644
--- a/mysql-test/suite/funcs_1/r/memory_views.result
+++ b/mysql-test/suite/funcs_1/r/memory_views.result
@@ -22034,7 +22034,7 @@ Testcase 3.3.2.1 - 3.3.2.6 alternative implementation
 --------------------------------------------------------------------------------
 DROP TABLE IF EXISTS t1;
 DROP VIEW  IF EXISTS v1;
-CREATE TABLE t1 ( f1 BIGINT, f2 CHAR(20), f3 NUMERIC(7,4),
+CREATE TABLE t1 ( f1 BIGINT DEFAULT 0, f2 CHAR(20), f3 NUMERIC(7,4),
 f4 CHAR, PRIMARY KEY(f1));
 CREATE VIEW v1 AS SELECT f1, f2, f3 FROM t1;
 INSERT INTO v1 SET f1 = 1;
diff --git a/mysql-test/suite/funcs_1/r/myisam_views-big.result b/mysql-test/suite/funcs_1/r/myisam_views-big.result
index 9f5adc15b7f..0e0bede7257 100644
--- a/mysql-test/suite/funcs_1/r/myisam_views-big.result
+++ b/mysql-test/suite/funcs_1/r/myisam_views-big.result
@@ -23736,7 +23736,7 @@ Testcase 3.3.2.1 - 3.3.2.6 alternative implementation
 --------------------------------------------------------------------------------
 DROP TABLE IF EXISTS t1;
 DROP VIEW  IF EXISTS v1;
-CREATE TABLE t1 ( f1 BIGINT, f2 CHAR(20), f3 NUMERIC(7,4),
+CREATE TABLE t1 ( f1 BIGINT DEFAULT 0, f2 CHAR(20), f3 NUMERIC(7,4),
 f4 CHAR, PRIMARY KEY(f1));
 CREATE VIEW v1 AS SELECT f1, f2, f3 FROM t1;
 INSERT INTO v1 SET f1 = 1;
diff --git a/mysql-test/suite/funcs_1/views/views_master.inc b/mysql-test/suite/funcs_1/views/views_master.inc
index c34773b6c43..bb9bbdb06d0 100644
--- a/mysql-test/suite/funcs_1/views/views_master.inc
+++ b/mysql-test/suite/funcs_1/views/views_master.inc
@@ -3315,7 +3315,7 @@ DROP TABLE IF EXISTS t1;
 DROP VIEW  IF EXISTS v1;
 --enable_warnings
 
-CREATE TABLE t1 ( f1 BIGINT, f2 CHAR(20), f3 NUMERIC(7,4),
+CREATE TABLE t1 ( f1 BIGINT DEFAULT 0, f2 CHAR(20), f3 NUMERIC(7,4),
                   f4 CHAR, PRIMARY KEY(f1));
 
 # VIEW including the base table PRIMARY KEY, but not all base table columns (f4)