1--echo # 2--echo # MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value 3--echo # 4 5# Testing direct INSERT 6 7SET sql_mode=DEFAULT; 8eval CREATE TABLE t1 (a $type DEFAULT $defval); 9SET sql_mode=TRADITIONAL; 10--error ER_TRUNCATED_WRONG_VALUE 11eval INSERT INTO t1 VALUES ($defval); 12--error ER_INVALID_DEFAULT_VALUE_FOR_FIELD 13INSERT INTO t1 VALUES (); 14--error ER_INVALID_DEFAULT_VALUE_FOR_FIELD 15INSERT INTO t1 VALUES (DEFAULT); 16DROP TABLE t1; 17SET sql_mode=DEFAULT; 18 19 20# Testing INSERT .. SELECT 21 22eval CREATE TABLE t1 (a $type NOT NULL DEFAULT $defval, b $type NOT NULL DEFAULT $defval); 23eval CREATE TABLE t2 (a $type NOT NULL DEFAULT $defval); 24eval INSERT INTO t2 VALUES ($defval); 25SET sql_mode=TRADITIONAL; 26--error ER_INVALID_DEFAULT_VALUE_FOR_FIELD 27INSERT INTO t1 (a) SELECT a FROM t2; 28DROP TABLE t1, t2; 29SET sql_mode=DEFAULT; 30 31 32# Testing LOAD 33 34--eval CREATE TABLE t1 (a $type DEFAULT $defval, b $type DEFAULT $defval) 35--eval INSERT INTO t1 VALUES (DEFAULT,DEFAULT); 36--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 37--eval SELECT a INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/mdev-7824.txt' FROM t1 38DELETE FROM t1; 39SET sql_mode=TRADITIONAL; 40--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 41--error ER_INVALID_DEFAULT_VALUE_FOR_FIELD 42--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/mdev-7824.txt' INTO TABLE t1 (a) 43--remove_file $MYSQLTEST_VARDIR/tmp/mdev-7824.txt 44DROP TABLE t1; 45SET sql_mode=DEFAULT; 46 47# Testing ALTER when an old field default becomes invalid 48# Return an error, even if there is no STRICT_XXX_TABLES set 49--eval CREATE TABLE t1 (a $type DEFAULT $defval); 50SET sql_mode='NO_ZERO_DATE'; 51--error ER_INVALID_DEFAULT 52ALTER TABLE t1 ADD b INT NOT NULL; 53DROP TABLE t1; 54SET sql_mode=DEFAULT; 55 56 57--echo # 58--echo # End of MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value 59--echo # 60 61--echo # 62--echo # MDEV-8373 Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field 63--echo # 64 65SET sql_mode=DEFAULT; 66--eval CREATE TABLE t1 (a $type); 67INSERT INTO t1 VALUES (0); 68SET sql_mode='TRADITIONAL'; 69--error ER_TRUNCATED_WRONG_VALUE 70CREATE TABLE t2 AS SELECT * FROM t1; 71DROP TABLE t1; 72 73--echo # 74--echo # End of MDEV-8373 Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field 75--echo # 76