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)

1 files changed, 52 insertions, 0 deletions

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;