Invalid DEFAULT values for CREATE TABLE now generates errors. (Bug #5902)

CAST() now produces warnings when casting a wrong INTEGER or CHAR values. This also applies to implicite string to number casts. (Bug #5912)
ALTER TABLE now fails in STRICT mode if it generates warnings.
Inserting a zero date in a DATE, DATETIME or TIMESTAMP column during TRADITIONAL mode now produces an error. (Bug #5933)


mysql-test/r/bigint.result:
  New warning added
mysql-test/r/cast.result:
  Added testing of wrong CAST's of strings to numbers and numbers to strings
mysql-test/r/create.result:
  Added test for wrong default values (#5902)
mysql-test/r/func_if.result:
  Changed tests to produce less warnings
mysql-test/r/func_misc.result:
  New warning
mysql-test/r/func_str.result:
  Added missing drop table
  Changed test to produce less warnings
  New warnings
mysql-test/r/ndb_index_unique.result:
  Removed wrong default usage
mysql-test/r/ps_1general.result:
  Changed tests to produce less warnings
mysql-test/r/row.result:
  New warnings
mysql-test/r/rpl_session_var.result:
  Changed tests to produce less warnings
mysql-test/r/strict.result:
  New tests for CAST() and zero date handling
mysql-test/r/subselect.result:
  Changed tests to produce less warnings
mysql-test/r/type_ranges.result:
  Changed tests to produce less warnings
mysql-test/t/cast.test:
  Added testing of wrong CAST's of strings to numbers and numbers to strings
mysql-test/t/create.test:
  Added test for wrong default values (#5902)
mysql-test/t/func_if.test:
  Changed tests to produce less warnings
mysql-test/t/func_str.test:
  Added missing drop table
  Changed test to produce less warnings
  New warnings
mysql-test/t/ndb_index_unique.test:
  Removed wrong default usage
mysql-test/t/ps_1general.test:
  Changed tests to produce less warnings
mysql-test/t/rpl_session_var.test:
  Changed tests to produce less warnings
mysql-test/t/strict.test:
  New tests for CAST() and zero date handling
mysql-test/t/subselect.test:
  Changed tests to produce less warnings
mysql-test/t/type_ranges.test:
  Changed tests to produce less warnings
sql/Makefile.am:
  Added new include file
sql/field.cc:
  Added warnings for zero dates for DATE, DATETIME and TIMESTAMP
  Moved Field_blob::max_length() to a more appropriate position
  Changed type for 'level' in set_warning() to avoid casts
sql/field.h:
  Changed type for 'level' in set_warning() to avoid casts
sql/field_conv.cc:
  Copy date and datetime fields through string in 'traditional' mode to detect zero dates
sql/item.cc:
  Removed compiler warnings
  Give warnings for wrong CAST of strings -> number
sql/item.h:
  Moved Item_string::val_real() and ::val_int() to item.cc
sql/item_row.cc:
  Better detection of null values (which doesn't produce warnings)
sql/item_sum.cc:
  Better detection of null values (which doesn't produce warnings)
sql/item_timefunc.cc:
  Give warnings for wrong CAST of number -> string
sql/my_decimal.cc:
  Fixed typo in comment
sql/mysql_priv.h:
  Removed prototype for static function
  Moved defines for error handling to sql_error.h (to be able to use these in field.h)
sql/mysqld.cc:
  Simplify code
sql/sql_class.h:
  Moved to sql_error.h
sql/sql_load.cc:
  Removed wrong cast
sql/sql_parse.cc:
  Fixed wrong printf()
sql/sql_table.cc:
  Made mysql_prepare_table() static
  Changed references to pointers to make code more readable
  ALTER TABLE now aborts if one gets warnings in STRICT mode
sql/time.cc:
  Fixed possible wrong call
sql/unireg.cc:
  Removed one call to current_thd
  Give errors if one uses a wrong DEFAULT value

23 files changed, 293 insertions, 55 deletions

diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result
index 50e99e3ecd7..e372c307ba7 100644
--- a/mysql-test/r/bigint.result
+++ b/mysql-test/r/bigint.result
@@ -87,6 +87,8 @@ drop table t1;
 SELECT '0x8000000000000001'+0;
 '0x8000000000000001'+0
 0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '0x8000000000000001'
 create table t1 (
 value64  bigint unsigned  not null,
 value32  integer          not null,
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index b015648534b..79d903737bf 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -7,6 +7,9 @@ CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER)
 select CONVERT('-1',UNSIGNED);
 CONVERT('-1',UNSIGNED)
 18446744073709551615
+select CAST('10 ' as unsigned integer);
+CAST('10 ' as unsigned integer)
+10
 select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1;
 cast(-5 as unsigned) | 1	cast(-5 as unsigned) & -1
 18446744073709551611	18446744073709551611
@@ -42,6 +45,15 @@ cast("1:2:3" as TIME)
 select CONVERT("2004-01-22 21:45:33",DATE);
 CONVERT("2004-01-22 21:45:33",DATE)
 2004-01-22
+select 10+'10';
+10+'10'
+20
+select 10.0+'10';
+10.0+'10'
+20
+select 10E+0+'10';
+10E+0+'10'
+20
 select CONVERT(DATE "2004-01-22 21:45:33" USING latin1);
 CONVERT(DATE "2004-01-22 21:45:33" USING latin1)
 2004-01-22 21:45:33
@@ -51,12 +63,43 @@ CONVERT(DATE "2004-01-22 21:45:33",CHAR)
 select CONVERT(DATE "2004-01-22 21:45:33",CHAR(4));
 CONVERT(DATE "2004-01-22 21:45:33",CHAR(4))
 2004
+Warnings:
+Warning	1292	Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33'
 select CONVERT(DATE "2004-01-22 21:45:33",BINARY(4));
 CONVERT(DATE "2004-01-22 21:45:33",BINARY(4))
 2004
+Warnings:
+Warning	1292	Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33'
 select CAST(DATE "2004-01-22 21:45:33" AS BINARY(4));
 CAST(DATE "2004-01-22 21:45:33" AS BINARY(4))
 2004
+Warnings:
+Warning	1292	Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33'
+select cast('-10a' as signed integer);
+cast('-10a' as signed integer)
+-10
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: '-10a'
+select cast('a10' as unsigned integer);
+cast('a10' as unsigned integer)
+0
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'a10'
+select 10+'a';
+10+'a'
+10
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+select 10.0+cast('a' as decimal);
+10.0+cast('a' as decimal)
+10.00
+Warnings:
+Error	1366	Incorrect decimal value: '' for column '' at row -1
+select 10E+0+'a';
+10E+0+'a'
+10
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 set names binary;
 select cast(_latin1'test' as char character set latin2);
 cast(_latin1'test' as char character set latin2)
@@ -79,12 +122,23 @@ cast(_latin1'a  ' AS char(2)) as c4,
 cast(_latin1'a'   AS char(2)) as c5;
 c1	c2	c3	c4	c5
 ab	a 	ab	a 	a
+Warnings:
+Warning	1292	Truncated incorrect CHAR(2) value: 'abc'
+Warning	1292	Truncated incorrect CHAR(2) value: 'a  '
+select cast(1000 as CHAR(3));
+cast(1000 as CHAR(3))
+100
+Warnings:
+Warning	1292	Truncated incorrect CHAR(3) value: '1000'
 create table t1 select
 cast(_latin1'ab'  AS char)    as c1,
 cast(_latin1'a '  AS char)    as c2,
 cast(_latin1'abc' AS char(2)) as c3,
 cast(_latin1'a  ' AS char(2)) as c4,
 cast(_latin1'a'   AS char(2)) as c5;
+Warnings:
+Warning	1292	Truncated incorrect CHAR(2) value: 'abc'
+Warning	1292	Truncated incorrect CHAR(2) value: 'a  '
 select * from t1;
 c1	c2	c3	c4	c5
 ab	a 	ab	a 	a
@@ -106,12 +160,18 @@ cast(_koi8r'Æ  ' AS nchar(2)) as c4,
 cast(_koi8r'Æ'   AS nchar(2)) as c5;
 c1	c2	c3	c4	c5
 фг	ф 	фг	ф 	ф
+Warnings:
+Warning	1292	Truncated incorrect CHAR(4) value: 'фгх'
+Warning	1292	Truncated incorrect CHAR(3) value: 'Ñ„  '
 create table t1 select
 cast(_koi8r'ÆÇ'  AS nchar)    as c1,
 cast(_koi8r'Æ '  AS nchar)    as c2,
 cast(_koi8r'ÆÇÈ' AS nchar(2)) as c3,
 cast(_koi8r'Æ  ' AS nchar(2)) as c4,
 cast(_koi8r'Æ'   AS nchar(2)) as c5;
+Warnings:
+Warning	1292	Truncated incorrect CHAR(4) value: 'фгх'
+Warning	1292	Truncated incorrect CHAR(3) value: 'Ñ„  '
 select * from t1;
 c1	c2	c3	c4	c5
 фг	ф 	фг	ф 	ф
@@ -167,6 +227,10 @@ a	CAST(a AS CHAR(3))
 aac	aac
 aab	aab
 aaa	aaa
+Warnings:
+Warning	1292	Truncated incorrect CHAR(2) value: 'aaa'
+Warning	1292	Truncated incorrect CHAR(2) value: 'aab'
+Warning	1292	Truncated incorrect CHAR(2) value: 'aac'
 SELECT a, CAST(a AS UNSIGNED) FROM t1 ORDER BY CAST(a AS CHAR) ;
 a	CAST(a AS UNSIGNED)
 aaa	3
@@ -177,6 +241,10 @@ a	CAST(a AS CHAR(2))
 aaa	aa
 aab	aa
 aac	aa
+Warnings:
+Warning	1292	Truncated incorrect CHAR(2) value: 'aaa'
+Warning	1292	Truncated incorrect CHAR(2) value: 'aab'
+Warning	1292	Truncated incorrect CHAR(2) value: 'aac'
 DROP TABLE t1;
 select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour);
 date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour)
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index 1c378f88e5c..a323669ac97 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -44,12 +44,24 @@ create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 ERROR 42000: Incorrect table name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
 create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int);
 ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long
-create table test (a datetime default now());
+create table t1 (a datetime default now());
 ERROR 42000: Invalid default value for 'a'
-create table test (a datetime on update now());
+create table t1 (a datetime on update now());
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-create table test (a int default 100 auto_increment);
+create table t1 (a int default 100 auto_increment);
 ERROR 42000: Invalid default value for 'a'
+create table t1 (a tinyint default 1000);
+ERROR 42000: Invalid default value for 'a'
+create table t1 (a varchar(5) default 'abcdef');
+ERROR 42000: Invalid default value for 'a'
+create table t1 (a varchar(5) default 'abcde');
+insert into t1 values();
+select * from t1;
+a
+abcde
+alter table t1 alter column a set default 'abcdef';
+ERROR 42000: Invalid default value for 'a'
+drop table t1;
 create table 1ea10 (1a20 int,1e int);
 insert into 1ea10 values(1,1);
 select 1ea10.1a20,1e+ 1e+10 from 1ea10;
diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result
index 0ee7412cbe9..4db31121756 100644
--- a/mysql-test/r/func_if.result
+++ b/mysql-test/r/func_if.result
@@ -44,21 +44,26 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where; Using filesort
 Warnings:
 Note	1003	select if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) AS `s` from `test`.`t1` where (`test`.`t1`.`st` like _latin1'%a%') order by if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary))
-select nullif(u=0, 'test') from t1;
-nullif(u=0, 'test')
+select nullif(u, 1) from t1;
+nullif(u, 1)
 NULL
 NULL
 NULL
 NULL
 NULL
-1
-1
-explain extended select nullif(u=0, 'test') from t1;
+0
+0
+explain extended select nullif(u, 1) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	
 Warnings:
-Note	1003	select nullif((`test`.`t1`.`u` = 0),_latin1'test') AS `nullif(u=0, 'test')` from `test`.`t1`
+Note	1003	select nullif(`test`.`t1`.`u`,1) AS `nullif(u, 1)` from `test`.`t1`
 drop table t1;
+select nullif(1,'test');
+nullif(1,'test')
+1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'test'
 select NULLIF(NULL,NULL), NULLIF(NULL,1), NULLIF(NULL,1.0), NULLIF(NULL,"test");
 NULLIF(NULL,NULL)	NULLIF(NULL,1)	NULLIF(NULL,1.0)	NULLIF(NULL,"test")
 NULL	NULL	NULL	NULL
diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
index 2d464c891bf..1d9b813e68a 100644
--- a/mysql-test/r/func_misc.result
+++ b/mysql-test/r/func_misc.result
@@ -25,6 +25,8 @@ length(uuid())	charset(uuid())	length(unhex(replace(uuid(),_utf8'-',_utf8'')))
 select length(format('nan', 2)) > 0;
 length(format('nan', 2)) > 0
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'nan'
 select concat("$",format(2500,2));
 concat("$",format(2500,2))
 $2,500.00
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 1c2cfeca01e..bbb5a94606e 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -1,4 +1,4 @@
-drop table if exists t1;
+drop table if exists t1,t2;
 set names latin1;
 select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo';
 hello	'hello'	""hello""	'h'e'l'l'o'	hel"lo	hel'lo
@@ -236,6 +236,8 @@ Warning	1301	Result of repeat() was larger than max_allowed_packet (1048576) - t
 select position("0" in "baaa" in (1)),position("0" in "1" in (1,2,3)),position("sql" in ("mysql"));
 position("0" in "baaa" in (1))	position("0" in "1" in (1,2,3))	position("sql" in ("mysql"))
 1	0	3
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'baaa'
 select position(("1" in (1,2,3)) in "01");
 position(("1" in (1,2,3)) in "01")
 2
@@ -393,9 +395,9 @@ select FIELD(_latin2'b','A','B');
 ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'
 select FIELD('b',_latin2'A','B');
 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'
-select FIELD('b',_latin2'A','B',1);
-FIELD('b',_latin2'A','B',1)
-1
+select FIELD('1',_latin2'3','2',1);
+FIELD('1',_latin2'3','2',1)
+3
 select POSITION(_latin1'B' IN _latin1'abcd');
 POSITION(_latin1'B' IN _latin1'abcd')
 2
diff --git a/mysql-test/r/ndb_index_unique.result b/mysql-test/r/ndb_index_unique.result
index 517d09ddf29..1401ae26ddb 100644
--- a/mysql-test/r/ndb_index_unique.result
+++ b/mysql-test/r/ndb_index_unique.result
@@ -248,8 +248,8 @@ INSERT INTO t3 VALUES (1,'V1',NULL);
 CREATE TABLE t4 (
 uid bigint(20) unsigned NOT NULL default '0',
 gid bigint(20) unsigned NOT NULL,
-rid bigint(20) unsigned NOT NULL default '-1',
-cid bigint(20) unsigned NOT NULL default '-1',
+rid bigint(20) unsigned NOT NULL,
+cid bigint(20) unsigned NOT NULL,
 UNIQUE KEY m (uid,gid,rid,cid)
 ) engine=ndbcluster;
 INSERT INTO t4 VALUES (1,1,2,4);
@@ -275,8 +275,8 @@ CREATE TABLE t7 (
 mid bigint(20) unsigned NOT NULL PRIMARY KEY,
 uid bigint(20) unsigned NOT NULL default '0',
 gid bigint(20) unsigned NOT NULL,
-rid bigint(20) unsigned NOT NULL default '-1',
-cid bigint(20) unsigned NOT NULL default '-1',
+rid bigint(20) unsigned NOT NULL,
+cid bigint(20) unsigned NOT NULL,
 UNIQUE KEY m (uid,gid,rid,cid)
 ) engine=ndbcluster;
 INSERT INTO t7 VALUES(1, 1, 1, 1, 1);
diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result
index 06acb72f49b..34f693ea33a 100644
--- a/mysql-test/r/ps_1general.result
+++ b/mysql-test/r/ps_1general.result
@@ -464,17 +464,17 @@ select 'a' || 'b' ;
 ab
 prepare stmt4 from ' SET sql_mode="" ';
 execute stmt4;
-select 'a' || 'b' ;
-'a' || 'b'
-0
-prepare stmt5 from ' select ''a'' || ''b'' ' ;
+select '2' || '3' ;
+'2' || '3'
+1
+prepare stmt5 from ' select ''2'' || ''3'' ' ;
 execute stmt5;
-'a' || 'b'
-0
+'2' || '3'
+1
 SET sql_mode=ansi;
 execute stmt5;
-'a' || 'b'
-0
+'2' || '3'
+1
 SET sql_mode="";
 prepare stmt1 from ' flush local privileges ' ;
 ERROR HY000: This command is not supported in the prepared statement protocol yet
diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result
index 40a31563604..691687762cb 100644
--- a/mysql-test/r/row.result
+++ b/mysql-test/r/row.result
@@ -16,12 +16,17 @@ row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a'))
 1
 Warnings:
 Error	1366	Incorrect decimal value: '' for column '' at row -1
+Warning	1292	Truncated incorrect INTEGER value: 'a'
 select row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3));
 row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3))
 1
 select row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3));
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'a'
 row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3))
 1
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'a'
 select row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3));
 row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3))
 1
diff --git a/mysql-test/r/rpl_session_var.result b/mysql-test/r/rpl_session_var.result
index f1f79ffa597..b5b4b815ade 100644
--- a/mysql-test/r/rpl_session_var.result
+++ b/mysql-test/r/rpl_session_var.result
@@ -11,14 +11,14 @@ create table t1(a varchar(100),b int);
 set @@session.sql_mode=pipes_as_concat;
 insert into t1 values('My'||'SQL', 1);
 set @@session.sql_mode=default;
-insert into t1 values('My'||'SQL', 2);
+insert into t1 values('1'||'2', 2);
 select * from t1 where b<3 order by a;
 a	b
-0	2
+1	2
 MySQL	1
 select * from t1 where b<3 order by a;
 a	b
-0	2
+1	2
 MySQL	1
 set @@session.sql_mode=ignore_space;
 insert into t1 values(password          ('MySQL'), 3);
diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result
index 47dbb87b990..1f8ada55061 100644
--- a/mysql-test/r/strict.result
+++ b/mysql-test/r/strict.result
@@ -1089,4 +1089,68 @@ Error	1411	Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_t
 Error	1411	Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_time
 Error	1411	Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_time
 drop table t1;
+create table t1 (col1 char(3), col2 integer);
+insert into t1 (col1) values (cast(1000 as char(3)));
+ERROR 22007: Truncated incorrect CHAR(3) value: '1000'
+insert into t1 (col1) values (cast(1000E+0 as char(3)));
+ERROR 22007: Truncated incorrect CHAR(3) value: '1000'
+insert into t1 (col1) values (cast(1000.0 as char(3)));
+ERROR 22007: Truncated incorrect CHAR(3) value: '1000.0'
+insert into t1 (col2) values (cast('abc' as signed integer));
+ERROR 22007: Truncated incorrect INTEGER value: 'abc'
+insert into t1 (col2) values (10E+0 + 'a');
+ERROR 22007: Truncated incorrect DOUBLE value: 'a'
+insert into t1 (col2) values (cast('10a' as unsigned integer));
+ERROR 22007: Truncated incorrect INTEGER value: '10a'
+insert into t1 (col2) values (cast('10' as unsigned integer));
+insert into t1 (col2) values (cast('10' as signed integer));
+insert into t1 (col2) values (10E+0 + '0 ');
+select * from t1;
+col1	col2
+NULL	10
+NULL	10
+NULL	10
+drop table t1;
+create table t1 (col1 date, col2 datetime, col3 timestamp);
+insert into t1 values (0,0,0);
+ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1
+insert into t1 values (0.0,0.0,0.0);
+ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1
+set sql_mode='no_zero_date';
+insert into t1 values (0,0,0);
+Warnings:
+Warning	1264	Out of range value adjusted for column 'col1' at row 1
+Warning	1264	Out of range value adjusted for column 'col2' at row 1
+Warning	1265	Data truncated for column 'col3' at row 1
+insert into t1 values (0.0,0.0,0.0);
+Warnings:
+Warning	1264	Out of range value adjusted for column 'col1' at row 1
+Warning	1264	Out of range value adjusted for column 'col2' at row 1
+Warning	1265	Data truncated for column 'col3' at row 1
+drop table t1;
+set sql_mode='traditional';
+create table t1 (col1 date);
+insert ignore into t1 values ('0000-00-00');
+Warnings:
+Warning	1265	Data truncated for column 'col1' at row 1
+insert into t1 select * from t1;
+ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
+insert ignore into t1 values ('0000-00-00');
+Warnings:
+Warning	1265	Data truncated for column 'col1' at row 1
+insert into t1 select * from t1;
+ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
+alter table t1 modify col1 datetime;
+ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col1' at row 1
+alter ignore table t1 modify col1 datetime;
+Warnings:
+Warning	1264	Out of range value adjusted for column 'col1' at row 1
+Warning	1264	Out of range value adjusted for column 'col1' at row 2
+insert into t1 select * from t1;
+ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1
+select * from t1;
+col1
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+drop table t1;
 set sql_mode=@org_mode;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 738c011012d..24d54c3ca33 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -117,15 +117,17 @@ SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
 SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
 (SELECT 1.5,2,'a') = ROW(1.5,2,'b')
 0
-SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b');
-(SELECT 1.5,2,'a') = ROW('b',2,'b')
+SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b');
+(SELECT 1.5,2,'a') = ROW('1.5b',2,'b')
 0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '1.5b'
 SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
 (SELECT 'b',2,'a') = ROW(1.5,2,'a')
 0
-SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a');
-(SELECT 1.5,2,'a') = ROW(1.5,'c','a')
-0
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
+(SELECT 1.5,2,'a') = ROW(1.5,'2','a')
+1
 SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
 (SELECT 1.5,'c','a') = ROW(1.5,2,'a')
 0
diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
index e553d9d86f2..12efb38179f 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -133,7 +133,7 @@ ALTER TABLE t1
 add new_field char(10) default "new" not null,
 change blob_col new_blob_col varchar(20),
 change date_field date_field char(10),
-alter column string set default "new default",
+alter column string set default "newdefault",
 alter short drop default,
 DROP INDEX utiny,
 DROP INDEX ushort,
@@ -211,7 +211,7 @@ update t2 set string="changed" where auto=16;
 show full columns from t1;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 auto	int(5) unsigned	NULL	NO	MUL	NULL	auto_increment	select,insert,update,references	
-string	char(10)	latin1_swedish_ci	YES		new defaul		select,insert,update,references	
+string	char(10)	latin1_swedish_ci	YES		newdefault		select,insert,update,references	
 tiny	tinyint(4)	NULL	NO	MUL	0		select,insert,update,references	
 short	smallint(6)	NULL	NO	MUL	0		select,insert,update,references	
 medium	mediumint(8)	NULL	NO	MUL	0		select,insert,update,references	
@@ -237,7 +237,7 @@ new_field	char(10)	latin1_swedish_ci	NO		new		select,insert,update,references
 show full columns from t2;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 auto	int(5) unsigned	NULL	NO		0		select,insert,update,references	
-string	char(10)	latin1_swedish_ci	YES		new defaul		select,insert,update,references	
+string	char(10)	latin1_swedish_ci	YES		newdefault		select,insert,update,references	
 tiny	tinyint(4)	NULL	NO		0		select,insert,update,references	
 short	smallint(6)	NULL	NO		0		select,insert,update,references	
 medium	mediumint(8)	NULL	NO		0		select,insert,update,references	
diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index 94d9590dd5f..e7dd49394ee 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -5,6 +5,7 @@
 select CAST(1-2 AS UNSIGNED);
 select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER);
 select CONVERT('-1',UNSIGNED);
+select CAST('10 ' as unsigned integer);
 select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1;
 select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1;
 select ~5, cast(~5 as signed);
@@ -16,11 +17,22 @@ select cast("A" as binary) = "a", cast(BINARY "a" as CHAR) = "A";
 select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME);
 select cast("1:2:3" as TIME);
 select CONVERT("2004-01-22 21:45:33",DATE);
+select 10+'10';
+select 10.0+'10';
+select 10E+0+'10';
+
+# The following cast creates warnings
+
 select CONVERT(DATE "2004-01-22 21:45:33" USING latin1);
 select CONVERT(DATE "2004-01-22 21:45:33",CHAR);
 select CONVERT(DATE "2004-01-22 21:45:33",CHAR(4));
 select CONVERT(DATE "2004-01-22 21:45:33",BINARY(4));
 select CAST(DATE "2004-01-22 21:45:33" AS BINARY(4));
+select cast('-10a' as signed integer);
+select cast('a10' as unsigned integer);
+select 10+'a';
+select 10.0+cast('a' as decimal);
+select 10E+0+'a';
 
 #
 # Character set convertion
@@ -41,6 +53,7 @@ select
   cast(_latin1'abc' AS char(2)) as c3,
   cast(_latin1'a  ' AS char(2)) as c4,
   cast(_latin1'a'   AS char(2)) as c5;
+select cast(1000 as CHAR(3));
 
 create table t1 select
   cast(_latin1'ab'  AS char)    as c1,
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 26b467a398d..1e2cf391221 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -49,14 +49,25 @@ create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int);
 
 #
-# Some wrong defaults, so these creates should fail too
+# Some wrong defaults, so these creates should fail too (Bug #5902)
 #
 --error 1067
-create table test (a datetime default now());
+create table t1 (a datetime default now());
 --error 1294
-create table test (a datetime on update now());
+create table t1 (a datetime on update now());
 --error 1067
-create table test (a int default 100 auto_increment);
+create table t1 (a int default 100 auto_increment);
+--error 1067
+create table t1 (a tinyint default 1000);
+--error 1067
+create table t1 (a varchar(5) default 'abcdef');
+
+create table t1 (a varchar(5) default 'abcde');
+insert into t1 values();
+select * from t1;
+--error 1067
+alter table t1 alter column a set default 'abcdef';
+drop table t1;
 
 #
 # test of dummy table names
diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test
index 693773b18c8..a2ea26390ca 100644
--- a/mysql-test/t/func_if.test
+++ b/mysql-test/t/func_if.test
@@ -25,9 +25,10 @@ explain extended select if(u=1,st,binary st) s from t1 where st like "%a%" order
 #
 # NULLIF test
 #
-select nullif(u=0, 'test') from t1;
-explain extended select nullif(u=0, 'test') from t1;
+select nullif(u, 1) from t1;
+explain extended select nullif(u, 1) from t1;
 drop table t1;
+select nullif(1,'test');
 
 #
 # Bug 2629
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index 8bf36431587..b3c875f7bf5 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -3,7 +3,7 @@
 # Testing string functions
 
 --disable_warnings
-drop table if exists t1;
+drop table if exists t1,t2;
 --enable_warnings
 
 set names latin1;
@@ -244,7 +244,7 @@ select FIELD('b','A' COLLATE latin1_bin,'B');
 select FIELD(_latin2'b','A','B');
 --error 1270
 select FIELD('b',_latin2'A','B');
-select FIELD('b',_latin2'A','B',1);
+select FIELD('1',_latin2'3','2',1);
 
 select POSITION(_latin1'B' IN _latin1'abcd');
 select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin);
@@ -523,4 +523,3 @@ SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id
  ORDER BY t1.id;
 
 DROP TABLE t1, t2;
-
diff --git a/mysql-test/t/ndb_index_unique.test b/mysql-test/t/ndb_index_unique.test
index 2fc7c496fea..6714f0233cf 100644
--- a/mysql-test/t/ndb_index_unique.test
+++ b/mysql-test/t/ndb_index_unique.test
@@ -182,8 +182,8 @@ INSERT INTO t3 VALUES (1,'V1',NULL);
 CREATE TABLE t4 (
   uid bigint(20) unsigned NOT NULL default '0',
   gid bigint(20) unsigned NOT NULL,
-  rid bigint(20) unsigned NOT NULL default '-1',
-  cid bigint(20) unsigned NOT NULL default '-1',
+  rid bigint(20) unsigned NOT NULL,
+  cid bigint(20) unsigned NOT NULL,
   UNIQUE KEY m (uid,gid,rid,cid)
 ) engine=ndbcluster;
 INSERT INTO t4 VALUES (1,1,2,4);
@@ -209,8 +209,8 @@ CREATE TABLE t7 (
   mid bigint(20) unsigned NOT NULL PRIMARY KEY,
   uid bigint(20) unsigned NOT NULL default '0',
   gid bigint(20) unsigned NOT NULL,
-  rid bigint(20) unsigned NOT NULL default '-1',
-  cid bigint(20) unsigned NOT NULL default '-1',
+  rid bigint(20) unsigned NOT NULL,
+  cid bigint(20) unsigned NOT NULL,
   UNIQUE KEY m (uid,gid,rid,cid)
 ) engine=ndbcluster;
 INSERT INTO t7 VALUES(1, 1, 1, 1, 1);
diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test
index 22a4a8bf2c9..4800cb831f7 100644
--- a/mysql-test/t/ps_1general.test
+++ b/mysql-test/t/ps_1general.test
@@ -500,10 +500,10 @@ select 'a' || 'b' ;
 prepare stmt4 from ' SET sql_mode="" ';
 execute stmt4;
 # check if the sql_mode is not ansi
-select 'a' || 'b' ;
+select '2' || '3' ;
 # Will a switch of the sqlmode affect the execution of already prepared 
 # statements ?
-prepare stmt5 from ' select ''a'' || ''b'' ' ;
+prepare stmt5 from ' select ''2'' || ''3'' ' ;
 execute stmt5;
 SET sql_mode=ansi;
 execute stmt5;
diff --git a/mysql-test/t/rpl_session_var.test b/mysql-test/t/rpl_session_var.test
index 2379df721b7..a6f4b496a23 100644
--- a/mysql-test/t/rpl_session_var.test
+++ b/mysql-test/t/rpl_session_var.test
@@ -7,7 +7,7 @@ create table t1(a varchar(100),b int);
 set @@session.sql_mode=pipes_as_concat;
 insert into t1 values('My'||'SQL', 1);
 set @@session.sql_mode=default;
-insert into t1 values('My'||'SQL', 2);
+insert into t1 values('1'||'2', 2);
 select * from t1 where b<3 order by a;
 save_master_pos;
 connection slave;
diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test
index 96ba9993c49..f98fc30ff48 100644
--- a/mysql-test/t/strict.test
+++ b/mysql-test/t/strict.test
@@ -978,6 +978,58 @@ select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') I
 drop table t1;
 
 #
+# Check insert with wrong CAST() (Bug #5912)
+#
+
+create table t1 (col1 char(3), col2 integer);
+--error 1292
+insert into t1 (col1) values (cast(1000 as char(3)));
+--error 1292
+insert into t1 (col1) values (cast(1000E+0 as char(3)));
+--error 1292
+insert into t1 (col1) values (cast(1000.0 as char(3)));
+--error 1292
+insert into t1 (col2) values (cast('abc' as signed integer));
+--error 1292
+insert into t1 (col2) values (10E+0 + 'a');
+--error 1292
+insert into t1 (col2) values (cast('10a' as unsigned integer));
+insert into t1 (col2) values (cast('10' as unsigned integer));
+insert into t1 (col2) values (cast('10' as signed integer));
+insert into t1 (col2) values (10E+0 + '0 ');
+select * from t1;
+drop table t1;
+
+#
+# Zero dates using numbers was not checked properly (Bug #5933)
+#
+
+create table t1 (col1 date, col2 datetime, col3 timestamp);
+--error 1292
+insert into t1 values (0,0,0);
+--error 1292
+insert into t1 values (0.0,0.0,0.0);
+set sql_mode='no_zero_date';
+insert into t1 values (0,0,0);
+insert into t1 values (0.0,0.0,0.0);
+drop table t1;
+set sql_mode='traditional';
+create table t1 (col1 date);
+insert ignore into t1 values ('0000-00-00');
+--error 1292
+insert into t1 select * from t1;
+insert ignore into t1 values ('0000-00-00');
+--error 1292
+insert into t1 select * from t1;
+--error 1292
+alter table t1 modify col1 datetime;
+alter ignore table t1 modify col1 datetime;
+--error 1292
+insert into t1 select * from t1;
+select * from t1;
+drop table t1;
+
+#
 # Restore mode
 #
 set sql_mode=@org_mode;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index cfcb32f37c8..69b30d2235d 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -46,9 +46,9 @@ SELECT ROW(1,2,3) > (SELECT 1,2,1);
 SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
 SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
 SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
-SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b');
+SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b');
 SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
-SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a');
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
 SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
 
 -- error 1241
diff --git a/mysql-test/t/type_ranges.test b/mysql-test/t/type_ranges.test
index 5f035921064..3bd3502766b 100644
--- a/mysql-test/t/type_ranges.test
+++ b/mysql-test/t/type_ranges.test
@@ -69,7 +69,7 @@ ALTER TABLE t1
 add new_field char(10) default "new" not null,
 change blob_col new_blob_col varchar(20),
 change date_field date_field char(10),
-alter column string set default "new default",
+alter column string set default "newdefault",
 alter short drop default,
 DROP INDEX utiny,
 DROP INDEX ushort,