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