maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #08738
Re: Please review: MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
Hi Sergei,
On 05/29/2015 08:05 PM, Sergei Golubchik wrote:
Hi, Alexander!
On May 19, Alexander Barkov wrote:
I'm not sure, though, how to do that with a minimal overhead. A couple
of bool's in TABLE, like
bool all_default_are_checked;
bool write_set_defaults_are_checked;
I rewrote the patch slightly, so now we don't need to remember
all_default_are_checked or write_set_defaults_are_checked.
The default values are now checked before the query execution
and before the first restore_record() call. They are now
checked directly in table->s->default_values.
I hope it's now faster and clearer comparing to the original patch
version from MySQL-5.6.
Thanks.
that are set to false at the beginning on INSERT,INSERT...SELECT,LOAD.
And used like that
...
Sorry, I did't understand your idea about having two separate
flags for "all" and "write_set".
Is it for the cases when one mixes empty and non-empty parenthesized
value lists, like these:
insert into (a,b) t1 values (),(10,20);
insert into (a,b) t1 values (10,20),();
???
Yes, exactly. I thought it's the only case when the set of columns might
change within a single query execution.
Note, this currently does not work and return this error:
ERROR 1136 (21S01): Column count doesn't match value count at row 2
Looks like a bug. I guess this could work.
Okay, then I was wrong :)
Does that mean a set of inserted columns can *never* change within a
single query? Good for us.
If so, would you like me to report and fix it before mdev-7824?
No, certainly not before mdev-7824. And may be not at all. I don't care
much. It's apparently not a bug. It could be a new minor feature. But as
nobody ever wanted it, I wouldn't bother.
Regards,
Sergei
diff --git a/mysql-test/include/type_temporal_zero_default.inc b/mysql-test/include/type_temporal_zero_default.inc
new file mode 100644
index 0000000..8503d8d
--- /dev/null
+++ b/mysql-test/include/type_temporal_zero_default.inc
@@ -0,0 +1,59 @@
+--echo #
+--echo # MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
+--echo #
+
+# Testing direct INSERT
+
+SET sql_mode=DEFAULT;
+eval CREATE TABLE t1 (a $type DEFAULT $defval);
+SET sql_mode=TRADITIONAL;
+--error ER_TRUNCATED_WRONG_VALUE
+eval INSERT INTO t1 VALUES ($defval);
+--error ER_INVALID_DEFAULT_VALUE_FOR_FIELD
+INSERT INTO t1 VALUES ();
+--error ER_INVALID_DEFAULT_VALUE_FOR_FIELD
+INSERT INTO t1 VALUES (DEFAULT);
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+
+
+# Testing INSERT .. SELECT
+
+eval CREATE TABLE t1 (a $type NOT NULL DEFAULT $defval, b $type NOT NULL DEFAULT $defval);
+eval CREATE TABLE t2 (a $type NOT NULL DEFAULT $defval);
+eval INSERT INTO t2 VALUES ($defval);
+SET sql_mode=TRADITIONAL;
+--error ER_INVALID_DEFAULT_VALUE_FOR_FIELD
+INSERT INTO t1 (a) SELECT a FROM t2;
+DROP TABLE t1, t2;
+SET sql_mode=DEFAULT;
+
+
+# Testing LOAD
+
+--eval CREATE TABLE t1 (a $type DEFAULT $defval, b $type DEFAULT $defval)
+--eval INSERT INTO t1 VALUES (DEFAULT,DEFAULT);
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--eval SELECT a INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/mdev-7824.txt' FROM t1
+DELETE FROM t1;
+SET sql_mode=TRADITIONAL;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--error ER_INVALID_DEFAULT_VALUE_FOR_FIELD
+--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/mdev-7824.txt' INTO TABLE t1 (a)
+--remove_file $MYSQLTEST_VARDIR/tmp/mdev-7824.txt
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+
+# Testing ALTER when an old field default becomes invalid
+# Return an error, even if there is no STRICT_XXX_TABLES set
+--eval CREATE TABLE t1 (a $type DEFAULT $defval);
+SET sql_mode='NO_ZERO_DATE';
+--error ER_INVALID_DEFAULT
+ALTER TABLE t1 ADD b INT NOT NULL;
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+
+
+--echo #
+--echo # End of MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
+--echo #
diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index 398f7b8..3aaa2fb 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -438,3 +438,49 @@ id select_type table type possible_keys key key_len ref rows Extra
select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
1
drop table t1;
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
+#
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a DATE DEFAULT '0000-00-00');
+SET sql_mode=TRADITIONAL;
+INSERT INTO t1 VALUES ('0000-00-00');
+ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1
+INSERT INTO t1 VALUES ();
+ERROR 22007: Incorrect default value '0000-00-00' for column 'a'
+INSERT INTO t1 VALUES (DEFAULT);
+ERROR 22007: Incorrect default value '0000-00-00' for column 'a'
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a DATE NOT NULL DEFAULT '0000-00-00', b DATE NOT NULL DEFAULT '0000-00-00');
+CREATE TABLE t2 (a DATE NOT NULL DEFAULT '0000-00-00');
+INSERT INTO t2 VALUES ('0000-00-00');
+SET sql_mode=TRADITIONAL;
+INSERT INTO t1 (a) SELECT a FROM t2;
+ERROR 22007: Incorrect default value '0000-00-00' for column 'b'
+DROP TABLE t1, t2;
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a DATE DEFAULT '0000-00-00', b DATE DEFAULT '0000-00-00');
+INSERT INTO t1 VALUES (DEFAULT,DEFAULT);;
+SELECT a INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/mdev-7824.txt' FROM t1;
+DELETE FROM t1;
+SET sql_mode=TRADITIONAL;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/mdev-7824.txt' INTO TABLE t1 (a);
+ERROR 22007: Incorrect default value '0000-00-00' for column 'b'
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a DATE DEFAULT '0000-00-00');;
+SET sql_mode='NO_ZERO_DATE';
+ALTER TABLE t1 ADD b INT NOT NULL;
+ERROR 42000: Invalid default value for 'a'
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+#
+# End of MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
+#
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index fc1946b..48610f8 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -836,3 +836,49 @@ DROP TABLE t1,t2;
#
# End of 5.5 tests
#
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
+#
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a DATETIME DEFAULT '0000-00-00 00:00:00');
+SET sql_mode=TRADITIONAL;
+INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
+ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at row 1
+INSERT INTO t1 VALUES ();
+ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'a'
+INSERT INTO t1 VALUES (DEFAULT);
+ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'a'
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', b DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00');
+CREATE TABLE t2 (a DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00');
+INSERT INTO t2 VALUES ('0000-00-00 00:00:00');
+SET sql_mode=TRADITIONAL;
+INSERT INTO t1 (a) SELECT a FROM t2;
+ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'b'
+DROP TABLE t1, t2;
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a DATETIME DEFAULT '0000-00-00 00:00:00', b DATETIME DEFAULT '0000-00-00 00:00:00');
+INSERT INTO t1 VALUES (DEFAULT,DEFAULT);;
+SELECT a INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/mdev-7824.txt' FROM t1;
+DELETE FROM t1;
+SET sql_mode=TRADITIONAL;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/mdev-7824.txt' INTO TABLE t1 (a);
+ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'b'
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a DATETIME DEFAULT '0000-00-00 00:00:00');;
+SET sql_mode='NO_ZERO_DATE';
+ALTER TABLE t1 ADD b INT NOT NULL;
+ERROR 42000: Invalid default value for 'a'
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+#
+# End of MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
+#
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result
index 786bce4..6387075 100644
--- a/mysql-test/r/type_timestamp.result
+++ b/mysql-test/r/type_timestamp.result
@@ -747,5 +747,45 @@ Warnings:
Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00'
DROP TABLE t1;
#
+# MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
+#
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00');
+SET sql_mode=TRADITIONAL;
+INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
+ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at row 1
+INSERT INTO t1 VALUES ();
+ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'a'
+INSERT INTO t1 VALUES (DEFAULT);
+ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'a'
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', b TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00');
+CREATE TABLE t2 (a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00');
+INSERT INTO t2 VALUES ('0000-00-00 00:00:00');
+SET sql_mode=TRADITIONAL;
+INSERT INTO t1 (a) SELECT a FROM t2;
+ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'b'
+DROP TABLE t1, t2;
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00', b TIMESTAMP DEFAULT '0000-00-00 00:00:00');
+INSERT INTO t1 VALUES (DEFAULT,DEFAULT);;
+SELECT a INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/mdev-7824.txt' FROM t1;
+DELETE FROM t1;
+SET sql_mode=TRADITIONAL;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/mdev-7824.txt' INTO TABLE t1 (a);
+ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'b'
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00');;
+SET sql_mode='NO_ZERO_DATE';
+ALTER TABLE t1 ADD b INT NOT NULL;
+ERROR 42000: Invalid default value for 'a'
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+#
+# End of MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
+#
+#
# End of 10.1 tests
#
diff --git a/mysql-test/suite/funcs_1/r/innodb_func_view.result b/mysql-test/suite/funcs_1/r/innodb_func_view.result
index b91f1a0..38ad472 100644
--- a/mysql-test/suite/funcs_1/r/innodb_func_view.result
+++ b/mysql-test/suite/funcs_1/r/innodb_func_view.result
@@ -14,7 +14,7 @@ ALTER TABLE t1_values ADD my_varbinary_1000 VARBINARY(1000);
ALTER TABLE t1_values ADD my_datetime DATETIME;
ALTER TABLE t1_values ADD my_date DATE;
ALTER TABLE t1_values ADD ts_dummy TIMESTAMP;
-ALTER TABLE t1_values ADD my_timestamp TIMESTAMP;
+ALTER TABLE t1_values ADD my_timestamp TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30';
ALTER TABLE t1_values ADD my_time TIME;
ALTER TABLE t1_values ADD my_year YEAR;
ALTER TABLE t1_values ADD my_bigint BIGINT;
@@ -1276,7 +1276,7 @@ SELECT IFNULL(my_timestamp,'IS_NULL'),
my_timestamp, id FROM t1_values
WHERE select_id = 136 OR select_id IS NULL order by id;
IFNULL(my_timestamp,'IS_NULL') my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -1288,7 +1288,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 136 OR select_id IS NULL) order by id;
IFNULL(my_timestamp,'IS_NULL') my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -1595,7 +1595,7 @@ SELECT IF(my_timestamp IS NULL, 'IS NULL',
WHERE select_id = 124 OR select_id IS NULL order by id;
IF(my_timestamp IS NULL, 'IS NULL',
'IS NOT NULL') my_timestamp id
-IS NOT NULL 0000-00-00 00:00:00 1
+IS NOT NULL 2001-01-01 10:20:30 1
IS NOT NULL 1970-01-01 14:00:01 2
IS NOT NULL 2038-01-01 02:59:59 3
IS NOT NULL 2004-02-29 23:59:59 4
@@ -1609,7 +1609,7 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 124 OR select_id IS NULL) order by id;
IF(my_timestamp IS NULL, 'IS NULL',
'IS NOT NULL') my_timestamp id
-IS NOT NULL 0000-00-00 00:00:00 1
+IS NOT NULL 2001-01-01 10:20:30 1
IS NOT NULL 1970-01-01 14:00:01 2
IS NOT NULL 2038-01-01 02:59:59 3
IS NOT NULL 2004-02-29 23:59:59 4
@@ -1936,7 +1936,7 @@ SELECT IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE'),
my_timestamp, id FROM t1_values
WHERE select_id = 112 OR select_id IS NULL order by id;
IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE') my_timestamp id
-IS NOT TRUE 0000-00-00 00:00:00 1
+IS TRUE 2001-01-01 10:20:30 1
IS TRUE 1970-01-01 14:00:01 2
IS TRUE 2038-01-01 02:59:59 3
IS TRUE 2004-02-29 23:59:59 4
@@ -1948,7 +1948,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 112 OR select_id IS NULL) order by id;
IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE') my_timestamp id
-IS NOT TRUE 0000-00-00 00:00:00 1
+IS TRUE 2001-01-01 10:20:30 1
IS TRUE 1970-01-01 14:00:01 2
IS TRUE 2038-01-01 02:59:59 3
IS TRUE 2004-02-29 23:59:59 4
@@ -2496,7 +2496,7 @@ SELECT CAST(my_timestamp AS UNSIGNED INTEGER),
my_timestamp, id FROM t1_values
WHERE select_id = 92 OR select_id IS NULL order by id;
CAST(my_timestamp AS UNSIGNED INTEGER) my_timestamp id
-0 0000-00-00 00:00:00 1
+20010101102030 2001-01-01 10:20:30 1
19700101140001 1970-01-01 14:00:01 2
20380101025959 2038-01-01 02:59:59 3
20040229235959 2004-02-29 23:59:59 4
@@ -2508,7 +2508,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 92 OR select_id IS NULL) order by id;
CAST(my_timestamp AS UNSIGNED INTEGER) my_timestamp id
-0 0000-00-00 00:00:00 1
+20010101102030 2001-01-01 10:20:30 1
19700101140001 1970-01-01 14:00:01 2
20380101025959 2038-01-01 02:59:59 3
20040229235959 2004-02-29 23:59:59 4
@@ -2874,7 +2874,7 @@ SELECT CAST(my_timestamp AS SIGNED INTEGER),
my_timestamp, id FROM t1_values
WHERE select_id = 80 OR select_id IS NULL order by id;
CAST(my_timestamp AS SIGNED INTEGER) my_timestamp id
-0 0000-00-00 00:00:00 1
+20010101102030 2001-01-01 10:20:30 1
19700101140001 1970-01-01 14:00:01 2
20380101025959 2038-01-01 02:59:59 3
20040229235959 2004-02-29 23:59:59 4
@@ -2886,7 +2886,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 80 OR select_id IS NULL) order by id;
CAST(my_timestamp AS SIGNED INTEGER) my_timestamp id
-0 0000-00-00 00:00:00 1
+20010101102030 2001-01-01 10:20:30 1
19700101140001 1970-01-01 14:00:01 2
20380101025959 2038-01-01 02:59:59 3
20040229235959 2004-02-29 23:59:59 4
@@ -3232,7 +3232,7 @@ SELECT CAST(my_timestamp AS DECIMAL(37,2)),
my_timestamp, id FROM t1_values
WHERE select_id = 68 OR select_id IS NULL order by id;
CAST(my_timestamp AS DECIMAL(37,2)) my_timestamp id
-0.00 0000-00-00 00:00:00 1
+20010101102030.00 2001-01-01 10:20:30 1
19700101140001.00 1970-01-01 14:00:01 2
20380101025959.00 2038-01-01 02:59:59 3
20040229235959.00 2004-02-29 23:59:59 4
@@ -3244,7 +3244,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 68 OR select_id IS NULL) order by id;
CAST(my_timestamp AS DECIMAL(37,2)) my_timestamp id
-0.00 0000-00-00 00:00:00 1
+20010101102030.00 2001-01-01 10:20:30 1
19700101140001.00 1970-01-01 14:00:01 2
20380101025959.00 2038-01-01 02:59:59 3
20040229235959.00 2004-02-29 23:59:59 4
@@ -3624,7 +3624,7 @@ SELECT CAST(my_timestamp AS TIME),
my_timestamp, id FROM t1_values
WHERE select_id = 56 OR select_id IS NULL order by id;
CAST(my_timestamp AS TIME) my_timestamp id
-00:00:00 0000-00-00 00:00:00 1
+10:20:30 2001-01-01 10:20:30 1
14:00:01 1970-01-01 14:00:01 2
02:59:59 2038-01-01 02:59:59 3
23:59:59 2004-02-29 23:59:59 4
@@ -3636,7 +3636,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 56 OR select_id IS NULL) order by id;
CAST(my_timestamp AS TIME) my_timestamp id
-00:00:00 0000-00-00 00:00:00 1
+10:20:30 2001-01-01 10:20:30 1
14:00:01 1970-01-01 14:00:01 2
02:59:59 2038-01-01 02:59:59 3
23:59:59 2004-02-29 23:59:59 4
@@ -3970,7 +3970,7 @@ SELECT CAST(my_timestamp AS DATETIME),
my_timestamp, id FROM t1_values
WHERE select_id = 45 OR select_id IS NULL order by id;
CAST(my_timestamp AS DATETIME) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -3982,7 +3982,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 45 OR select_id IS NULL) order by id;
CAST(my_timestamp AS DATETIME) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -4332,7 +4332,7 @@ SELECT CAST(my_timestamp AS DATE),
my_timestamp, id FROM t1_values
WHERE select_id = 34 OR select_id IS NULL order by id;
CAST(my_timestamp AS DATE) my_timestamp id
-0000-00-00 0000-00-00 00:00:00 1
+2001-01-01 2001-01-01 10:20:30 1
1970-01-01 1970-01-01 14:00:01 2
2038-01-01 2038-01-01 02:59:59 3
2004-02-29 2004-02-29 23:59:59 4
@@ -4344,7 +4344,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 34 OR select_id IS NULL) order by id;
CAST(my_timestamp AS DATE) my_timestamp id
-0000-00-00 0000-00-00 00:00:00 1
+2001-01-01 2001-01-01 10:20:30 1
1970-01-01 1970-01-01 14:00:01 2
2038-01-01 2038-01-01 02:59:59 3
2004-02-29 2004-02-29 23:59:59 4
@@ -4690,7 +4690,7 @@ SELECT CAST(my_timestamp AS CHAR),
my_timestamp, id FROM t1_values
WHERE select_id = 23 OR select_id IS NULL order by id;
CAST(my_timestamp AS CHAR) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -4702,7 +4702,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 23 OR select_id IS NULL) order by id;
CAST(my_timestamp AS CHAR) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -5002,7 +5002,7 @@ SELECT CAST(my_timestamp AS BINARY),
my_timestamp, id FROM t1_values
WHERE select_id = 11 OR select_id IS NULL order by id;
CAST(my_timestamp AS BINARY) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -5014,7 +5014,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 11 OR select_id IS NULL) order by id;
CAST(my_timestamp AS BINARY) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
diff --git a/mysql-test/suite/funcs_1/r/memory_func_view.result b/mysql-test/suite/funcs_1/r/memory_func_view.result
index 250ef44..f3eb50c 100644
--- a/mysql-test/suite/funcs_1/r/memory_func_view.result
+++ b/mysql-test/suite/funcs_1/r/memory_func_view.result
@@ -15,7 +15,7 @@ ALTER TABLE t1_values ADD my_varbinary_1000 VARBINARY(1000);
ALTER TABLE t1_values ADD my_datetime DATETIME;
ALTER TABLE t1_values ADD my_date DATE;
ALTER TABLE t1_values ADD ts_dummy TIMESTAMP;
-ALTER TABLE t1_values ADD my_timestamp TIMESTAMP;
+ALTER TABLE t1_values ADD my_timestamp TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30';
ALTER TABLE t1_values ADD my_time TIME;
ALTER TABLE t1_values ADD my_year YEAR;
ALTER TABLE t1_values ADD my_bigint BIGINT;
@@ -1277,7 +1277,7 @@ SELECT IFNULL(my_timestamp,'IS_NULL'),
my_timestamp, id FROM t1_values
WHERE select_id = 136 OR select_id IS NULL order by id;
IFNULL(my_timestamp,'IS_NULL') my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -1289,7 +1289,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 136 OR select_id IS NULL) order by id;
IFNULL(my_timestamp,'IS_NULL') my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -1596,7 +1596,7 @@ SELECT IF(my_timestamp IS NULL, 'IS NULL',
WHERE select_id = 124 OR select_id IS NULL order by id;
IF(my_timestamp IS NULL, 'IS NULL',
'IS NOT NULL') my_timestamp id
-IS NOT NULL 0000-00-00 00:00:00 1
+IS NOT NULL 2001-01-01 10:20:30 1
IS NOT NULL 1970-01-01 14:00:01 2
IS NOT NULL 2038-01-01 02:59:59 3
IS NOT NULL 2004-02-29 23:59:59 4
@@ -1610,7 +1610,7 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 124 OR select_id IS NULL) order by id;
IF(my_timestamp IS NULL, 'IS NULL',
'IS NOT NULL') my_timestamp id
-IS NOT NULL 0000-00-00 00:00:00 1
+IS NOT NULL 2001-01-01 10:20:30 1
IS NOT NULL 1970-01-01 14:00:01 2
IS NOT NULL 2038-01-01 02:59:59 3
IS NOT NULL 2004-02-29 23:59:59 4
@@ -1937,7 +1937,7 @@ SELECT IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE'),
my_timestamp, id FROM t1_values
WHERE select_id = 112 OR select_id IS NULL order by id;
IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE') my_timestamp id
-IS NOT TRUE 0000-00-00 00:00:00 1
+IS TRUE 2001-01-01 10:20:30 1
IS TRUE 1970-01-01 14:00:01 2
IS TRUE 2038-01-01 02:59:59 3
IS TRUE 2004-02-29 23:59:59 4
@@ -1949,7 +1949,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 112 OR select_id IS NULL) order by id;
IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE') my_timestamp id
-IS NOT TRUE 0000-00-00 00:00:00 1
+IS TRUE 2001-01-01 10:20:30 1
IS TRUE 1970-01-01 14:00:01 2
IS TRUE 2038-01-01 02:59:59 3
IS TRUE 2004-02-29 23:59:59 4
@@ -2497,7 +2497,7 @@ SELECT CAST(my_timestamp AS UNSIGNED INTEGER),
my_timestamp, id FROM t1_values
WHERE select_id = 92 OR select_id IS NULL order by id;
CAST(my_timestamp AS UNSIGNED INTEGER) my_timestamp id
-0 0000-00-00 00:00:00 1
+20010101102030 2001-01-01 10:20:30 1
19700101140001 1970-01-01 14:00:01 2
20380101025959 2038-01-01 02:59:59 3
20040229235959 2004-02-29 23:59:59 4
@@ -2509,7 +2509,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 92 OR select_id IS NULL) order by id;
CAST(my_timestamp AS UNSIGNED INTEGER) my_timestamp id
-0 0000-00-00 00:00:00 1
+20010101102030 2001-01-01 10:20:30 1
19700101140001 1970-01-01 14:00:01 2
20380101025959 2038-01-01 02:59:59 3
20040229235959 2004-02-29 23:59:59 4
@@ -2875,7 +2875,7 @@ SELECT CAST(my_timestamp AS SIGNED INTEGER),
my_timestamp, id FROM t1_values
WHERE select_id = 80 OR select_id IS NULL order by id;
CAST(my_timestamp AS SIGNED INTEGER) my_timestamp id
-0 0000-00-00 00:00:00 1
+20010101102030 2001-01-01 10:20:30 1
19700101140001 1970-01-01 14:00:01 2
20380101025959 2038-01-01 02:59:59 3
20040229235959 2004-02-29 23:59:59 4
@@ -2887,7 +2887,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 80 OR select_id IS NULL) order by id;
CAST(my_timestamp AS SIGNED INTEGER) my_timestamp id
-0 0000-00-00 00:00:00 1
+20010101102030 2001-01-01 10:20:30 1
19700101140001 1970-01-01 14:00:01 2
20380101025959 2038-01-01 02:59:59 3
20040229235959 2004-02-29 23:59:59 4
@@ -3233,7 +3233,7 @@ SELECT CAST(my_timestamp AS DECIMAL(37,2)),
my_timestamp, id FROM t1_values
WHERE select_id = 68 OR select_id IS NULL order by id;
CAST(my_timestamp AS DECIMAL(37,2)) my_timestamp id
-0.00 0000-00-00 00:00:00 1
+20010101102030.00 2001-01-01 10:20:30 1
19700101140001.00 1970-01-01 14:00:01 2
20380101025959.00 2038-01-01 02:59:59 3
20040229235959.00 2004-02-29 23:59:59 4
@@ -3245,7 +3245,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 68 OR select_id IS NULL) order by id;
CAST(my_timestamp AS DECIMAL(37,2)) my_timestamp id
-0.00 0000-00-00 00:00:00 1
+20010101102030.00 2001-01-01 10:20:30 1
19700101140001.00 1970-01-01 14:00:01 2
20380101025959.00 2038-01-01 02:59:59 3
20040229235959.00 2004-02-29 23:59:59 4
@@ -3625,7 +3625,7 @@ SELECT CAST(my_timestamp AS TIME),
my_timestamp, id FROM t1_values
WHERE select_id = 56 OR select_id IS NULL order by id;
CAST(my_timestamp AS TIME) my_timestamp id
-00:00:00 0000-00-00 00:00:00 1
+10:20:30 2001-01-01 10:20:30 1
14:00:01 1970-01-01 14:00:01 2
02:59:59 2038-01-01 02:59:59 3
23:59:59 2004-02-29 23:59:59 4
@@ -3637,7 +3637,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 56 OR select_id IS NULL) order by id;
CAST(my_timestamp AS TIME) my_timestamp id
-00:00:00 0000-00-00 00:00:00 1
+10:20:30 2001-01-01 10:20:30 1
14:00:01 1970-01-01 14:00:01 2
02:59:59 2038-01-01 02:59:59 3
23:59:59 2004-02-29 23:59:59 4
@@ -3971,7 +3971,7 @@ SELECT CAST(my_timestamp AS DATETIME),
my_timestamp, id FROM t1_values
WHERE select_id = 45 OR select_id IS NULL order by id;
CAST(my_timestamp AS DATETIME) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -3983,7 +3983,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 45 OR select_id IS NULL) order by id;
CAST(my_timestamp AS DATETIME) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -4333,7 +4333,7 @@ SELECT CAST(my_timestamp AS DATE),
my_timestamp, id FROM t1_values
WHERE select_id = 34 OR select_id IS NULL order by id;
CAST(my_timestamp AS DATE) my_timestamp id
-0000-00-00 0000-00-00 00:00:00 1
+2001-01-01 2001-01-01 10:20:30 1
1970-01-01 1970-01-01 14:00:01 2
2038-01-01 2038-01-01 02:59:59 3
2004-02-29 2004-02-29 23:59:59 4
@@ -4345,7 +4345,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 34 OR select_id IS NULL) order by id;
CAST(my_timestamp AS DATE) my_timestamp id
-0000-00-00 0000-00-00 00:00:00 1
+2001-01-01 2001-01-01 10:20:30 1
1970-01-01 1970-01-01 14:00:01 2
2038-01-01 2038-01-01 02:59:59 3
2004-02-29 2004-02-29 23:59:59 4
@@ -4691,7 +4691,7 @@ SELECT CAST(my_timestamp AS CHAR),
my_timestamp, id FROM t1_values
WHERE select_id = 23 OR select_id IS NULL order by id;
CAST(my_timestamp AS CHAR) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -4703,7 +4703,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 23 OR select_id IS NULL) order by id;
CAST(my_timestamp AS CHAR) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -5003,7 +5003,7 @@ SELECT CAST(my_timestamp AS BINARY),
my_timestamp, id FROM t1_values
WHERE select_id = 11 OR select_id IS NULL order by id;
CAST(my_timestamp AS BINARY) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -5015,7 +5015,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 11 OR select_id IS NULL) order by id;
CAST(my_timestamp AS BINARY) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
diff --git a/mysql-test/suite/funcs_1/r/myisam_func_view.result b/mysql-test/suite/funcs_1/r/myisam_func_view.result
index 250ef44..f3eb50c 100644
--- a/mysql-test/suite/funcs_1/r/myisam_func_view.result
+++ b/mysql-test/suite/funcs_1/r/myisam_func_view.result
@@ -15,7 +15,7 @@ ALTER TABLE t1_values ADD my_varbinary_1000 VARBINARY(1000);
ALTER TABLE t1_values ADD my_datetime DATETIME;
ALTER TABLE t1_values ADD my_date DATE;
ALTER TABLE t1_values ADD ts_dummy TIMESTAMP;
-ALTER TABLE t1_values ADD my_timestamp TIMESTAMP;
+ALTER TABLE t1_values ADD my_timestamp TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30';
ALTER TABLE t1_values ADD my_time TIME;
ALTER TABLE t1_values ADD my_year YEAR;
ALTER TABLE t1_values ADD my_bigint BIGINT;
@@ -1277,7 +1277,7 @@ SELECT IFNULL(my_timestamp,'IS_NULL'),
my_timestamp, id FROM t1_values
WHERE select_id = 136 OR select_id IS NULL order by id;
IFNULL(my_timestamp,'IS_NULL') my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -1289,7 +1289,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 136 OR select_id IS NULL) order by id;
IFNULL(my_timestamp,'IS_NULL') my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -1596,7 +1596,7 @@ SELECT IF(my_timestamp IS NULL, 'IS NULL',
WHERE select_id = 124 OR select_id IS NULL order by id;
IF(my_timestamp IS NULL, 'IS NULL',
'IS NOT NULL') my_timestamp id
-IS NOT NULL 0000-00-00 00:00:00 1
+IS NOT NULL 2001-01-01 10:20:30 1
IS NOT NULL 1970-01-01 14:00:01 2
IS NOT NULL 2038-01-01 02:59:59 3
IS NOT NULL 2004-02-29 23:59:59 4
@@ -1610,7 +1610,7 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 124 OR select_id IS NULL) order by id;
IF(my_timestamp IS NULL, 'IS NULL',
'IS NOT NULL') my_timestamp id
-IS NOT NULL 0000-00-00 00:00:00 1
+IS NOT NULL 2001-01-01 10:20:30 1
IS NOT NULL 1970-01-01 14:00:01 2
IS NOT NULL 2038-01-01 02:59:59 3
IS NOT NULL 2004-02-29 23:59:59 4
@@ -1937,7 +1937,7 @@ SELECT IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE'),
my_timestamp, id FROM t1_values
WHERE select_id = 112 OR select_id IS NULL order by id;
IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE') my_timestamp id
-IS NOT TRUE 0000-00-00 00:00:00 1
+IS TRUE 2001-01-01 10:20:30 1
IS TRUE 1970-01-01 14:00:01 2
IS TRUE 2038-01-01 02:59:59 3
IS TRUE 2004-02-29 23:59:59 4
@@ -1949,7 +1949,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 112 OR select_id IS NULL) order by id;
IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE') my_timestamp id
-IS NOT TRUE 0000-00-00 00:00:00 1
+IS TRUE 2001-01-01 10:20:30 1
IS TRUE 1970-01-01 14:00:01 2
IS TRUE 2038-01-01 02:59:59 3
IS TRUE 2004-02-29 23:59:59 4
@@ -2497,7 +2497,7 @@ SELECT CAST(my_timestamp AS UNSIGNED INTEGER),
my_timestamp, id FROM t1_values
WHERE select_id = 92 OR select_id IS NULL order by id;
CAST(my_timestamp AS UNSIGNED INTEGER) my_timestamp id
-0 0000-00-00 00:00:00 1
+20010101102030 2001-01-01 10:20:30 1
19700101140001 1970-01-01 14:00:01 2
20380101025959 2038-01-01 02:59:59 3
20040229235959 2004-02-29 23:59:59 4
@@ -2509,7 +2509,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 92 OR select_id IS NULL) order by id;
CAST(my_timestamp AS UNSIGNED INTEGER) my_timestamp id
-0 0000-00-00 00:00:00 1
+20010101102030 2001-01-01 10:20:30 1
19700101140001 1970-01-01 14:00:01 2
20380101025959 2038-01-01 02:59:59 3
20040229235959 2004-02-29 23:59:59 4
@@ -2875,7 +2875,7 @@ SELECT CAST(my_timestamp AS SIGNED INTEGER),
my_timestamp, id FROM t1_values
WHERE select_id = 80 OR select_id IS NULL order by id;
CAST(my_timestamp AS SIGNED INTEGER) my_timestamp id
-0 0000-00-00 00:00:00 1
+20010101102030 2001-01-01 10:20:30 1
19700101140001 1970-01-01 14:00:01 2
20380101025959 2038-01-01 02:59:59 3
20040229235959 2004-02-29 23:59:59 4
@@ -2887,7 +2887,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 80 OR select_id IS NULL) order by id;
CAST(my_timestamp AS SIGNED INTEGER) my_timestamp id
-0 0000-00-00 00:00:00 1
+20010101102030 2001-01-01 10:20:30 1
19700101140001 1970-01-01 14:00:01 2
20380101025959 2038-01-01 02:59:59 3
20040229235959 2004-02-29 23:59:59 4
@@ -3233,7 +3233,7 @@ SELECT CAST(my_timestamp AS DECIMAL(37,2)),
my_timestamp, id FROM t1_values
WHERE select_id = 68 OR select_id IS NULL order by id;
CAST(my_timestamp AS DECIMAL(37,2)) my_timestamp id
-0.00 0000-00-00 00:00:00 1
+20010101102030.00 2001-01-01 10:20:30 1
19700101140001.00 1970-01-01 14:00:01 2
20380101025959.00 2038-01-01 02:59:59 3
20040229235959.00 2004-02-29 23:59:59 4
@@ -3245,7 +3245,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 68 OR select_id IS NULL) order by id;
CAST(my_timestamp AS DECIMAL(37,2)) my_timestamp id
-0.00 0000-00-00 00:00:00 1
+20010101102030.00 2001-01-01 10:20:30 1
19700101140001.00 1970-01-01 14:00:01 2
20380101025959.00 2038-01-01 02:59:59 3
20040229235959.00 2004-02-29 23:59:59 4
@@ -3625,7 +3625,7 @@ SELECT CAST(my_timestamp AS TIME),
my_timestamp, id FROM t1_values
WHERE select_id = 56 OR select_id IS NULL order by id;
CAST(my_timestamp AS TIME) my_timestamp id
-00:00:00 0000-00-00 00:00:00 1
+10:20:30 2001-01-01 10:20:30 1
14:00:01 1970-01-01 14:00:01 2
02:59:59 2038-01-01 02:59:59 3
23:59:59 2004-02-29 23:59:59 4
@@ -3637,7 +3637,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 56 OR select_id IS NULL) order by id;
CAST(my_timestamp AS TIME) my_timestamp id
-00:00:00 0000-00-00 00:00:00 1
+10:20:30 2001-01-01 10:20:30 1
14:00:01 1970-01-01 14:00:01 2
02:59:59 2038-01-01 02:59:59 3
23:59:59 2004-02-29 23:59:59 4
@@ -3971,7 +3971,7 @@ SELECT CAST(my_timestamp AS DATETIME),
my_timestamp, id FROM t1_values
WHERE select_id = 45 OR select_id IS NULL order by id;
CAST(my_timestamp AS DATETIME) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -3983,7 +3983,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 45 OR select_id IS NULL) order by id;
CAST(my_timestamp AS DATETIME) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -4333,7 +4333,7 @@ SELECT CAST(my_timestamp AS DATE),
my_timestamp, id FROM t1_values
WHERE select_id = 34 OR select_id IS NULL order by id;
CAST(my_timestamp AS DATE) my_timestamp id
-0000-00-00 0000-00-00 00:00:00 1
+2001-01-01 2001-01-01 10:20:30 1
1970-01-01 1970-01-01 14:00:01 2
2038-01-01 2038-01-01 02:59:59 3
2004-02-29 2004-02-29 23:59:59 4
@@ -4345,7 +4345,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 34 OR select_id IS NULL) order by id;
CAST(my_timestamp AS DATE) my_timestamp id
-0000-00-00 0000-00-00 00:00:00 1
+2001-01-01 2001-01-01 10:20:30 1
1970-01-01 1970-01-01 14:00:01 2
2038-01-01 2038-01-01 02:59:59 3
2004-02-29 2004-02-29 23:59:59 4
@@ -4691,7 +4691,7 @@ SELECT CAST(my_timestamp AS CHAR),
my_timestamp, id FROM t1_values
WHERE select_id = 23 OR select_id IS NULL order by id;
CAST(my_timestamp AS CHAR) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -4703,7 +4703,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 23 OR select_id IS NULL) order by id;
CAST(my_timestamp AS CHAR) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -5003,7 +5003,7 @@ SELECT CAST(my_timestamp AS BINARY),
my_timestamp, id FROM t1_values
WHERE select_id = 11 OR select_id IS NULL order by id;
CAST(my_timestamp AS BINARY) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
@@ -5015,7 +5015,7 @@ SELECT v1.* FROM v1
WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 11 OR select_id IS NULL) order by id;
CAST(my_timestamp AS BINARY) my_timestamp id
-0000-00-00 00:00:00 0000-00-00 00:00:00 1
+2001-01-01 10:20:30 2001-01-01 10:20:30 1
1970-01-01 14:00:01 1970-01-01 14:00:01 2
2038-01-01 02:59:59 2038-01-01 02:59:59 3
2004-02-29 23:59:59 2004-02-29 23:59:59 4
diff --git a/mysql-test/suite/funcs_1/views/func_view.inc b/mysql-test/suite/funcs_1/views/func_view.inc
index 1f0b0ac..5bd3047 100644
--- a/mysql-test/suite/funcs_1/views/func_view.inc
+++ b/mysql-test/suite/funcs_1/views/func_view.inc
@@ -210,7 +210,7 @@ ALTER TABLE t1_values ADD my_varbinary_1000 VARBINARY(1000);
ALTER TABLE t1_values ADD my_datetime DATETIME;
ALTER TABLE t1_values ADD my_date DATE;
ALTER TABLE t1_values ADD ts_dummy TIMESTAMP;
-ALTER TABLE t1_values ADD my_timestamp TIMESTAMP;
+ALTER TABLE t1_values ADD my_timestamp TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30';
ALTER TABLE t1_values ADD my_time TIME;
ALTER TABLE t1_values ADD my_year YEAR;
ALTER TABLE t1_values ADD my_bigint BIGINT;
diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test
index 6b8ed12..53627c6 100644
--- a/mysql-test/t/type_date.test
+++ b/mysql-test/t/type_date.test
@@ -385,3 +385,14 @@ select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
drop table t1;
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+let type=DATE;
+let defval='0000-00-00';
+--source include/type_temporal_zero_default.inc
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index e44b190..f3e603e 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -613,3 +613,15 @@ DROP TABLE t1,t2;
--echo #
--echo # End of 5.5 tests
--echo #
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+let type=DATETIME;
+let defval='0000-00-00 00:00:00';
+--source include/type_temporal_zero_default.inc
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test
index a12b221..53c0fd7 100644
--- a/mysql-test/t/type_timestamp.test
+++ b/mysql-test/t/type_timestamp.test
@@ -534,6 +534,10 @@ SELECT DATE_ADD(a, INTERVAL 10 SECOND) FROM t1;
DROP TABLE t1;
+let type=TIMESTAMP;
+let defval='0000-00-00 00:00:00';
+--source include/type_temporal_zero_default.inc
+
--echo #
--echo # End of 10.1 tests
--echo #
diff --git a/sql/field.cc b/sql/field.cc
index ba6d4ff..4a854a3 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -4616,11 +4616,12 @@ Field_timestamp::Field_timestamp(uchar *ptr_arg, uint32 len_arg,
}
-my_time_t Field_timestamp::get_timestamp(ulong *sec_part) const
+my_time_t Field_timestamp::get_timestamp(const uchar *pos,
+ ulong *sec_part) const
{
ASSERT_COLUMN_MARKED_FOR_READ;
*sec_part= 0;
- return sint4korr(ptr);
+ return sint4korr(pos);
}
@@ -4832,6 +4833,16 @@ String *Field_timestamp::val_str(String *val_buffer, String *val_ptr)
}
+bool
+Field_timestamp::validate_value_in_record(THD *thd, const uchar *record) const
+{
+ DBUG_ASSERT(!is_null_in_record(record));
+ ulong sec_part;
+ return !get_timestamp(ptr_in_record(record), &sec_part) && !sec_part &&
+ (sql_mode_for_dates(thd) & TIME_NO_ZERO_DATE) != 0;
+}
+
+
bool Field_timestamp::get_date(MYSQL_TIME *ltime, ulonglong fuzzydate)
{
THD *thd= get_thd();
@@ -5014,11 +5025,12 @@ void Field_timestamp_hires::store_TIME(my_time_t timestamp, ulong sec_part)
store_bigendian(sec_part_shift(sec_part, dec), ptr+4, sec_part_bytes[dec]);
}
-my_time_t Field_timestamp_hires::get_timestamp(ulong *sec_part) const
+my_time_t Field_timestamp_hires::get_timestamp(const uchar *pos,
+ ulong *sec_part) const
{
ASSERT_COLUMN_MARKED_FOR_READ;
- *sec_part= (long)sec_part_unshift(read_bigendian(ptr+4, sec_part_bytes[dec]), dec);
- return mi_uint4korr(ptr);
+ *sec_part= (long)sec_part_unshift(read_bigendian(pos+4, sec_part_bytes[dec]), dec);
+ return mi_uint4korr(pos);
}
double Field_timestamp_with_dec::val_real(void)
@@ -5118,10 +5130,11 @@ void Field_timestampf::store_TIME(my_time_t timestamp, ulong sec_part)
}
-my_time_t Field_timestampf::get_timestamp(ulong *sec_part) const
+my_time_t Field_timestampf::get_timestamp(const uchar *pos,
+ ulong *sec_part) const
{
struct timeval tm;
- my_timestamp_from_binary(&tm, ptr, dec);
+ my_timestamp_from_binary(&tm, pos, dec);
*sec_part= tm.tv_usec;
return tm.tv_sec;
}
@@ -5980,12 +5993,22 @@ String *Field_newdate::val_str(String *val_buffer,
bool Field_newdate::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate)
{
uint32 tmp=(uint32) uint3korr(ptr);
- ltime->day= tmp & 31;
- ltime->month= (tmp >> 5) & 15;
+ ltime->day= num_to_day(tmp);
+ ltime->month= num_to_month(tmp);
ltime->year= (tmp >> 9);
ltime->time_type= MYSQL_TIMESTAMP_DATE;
ltime->hour= ltime->minute= ltime->second= ltime->second_part= ltime->neg= 0;
- return validate_for_get_date(tmp, ltime, fuzzydate);
+ return validate_MMDD(tmp, ltime->month, ltime->day, fuzzydate);
+}
+
+
+bool
+Field_newdate::validate_value_in_record(THD *thd, const uchar *record) const
+{
+ DBUG_ASSERT(!is_null_in_record(record));
+ uint32 num= (uint32) uint3korr(ptr_in_record(record));
+ return validate_MMDD(num, num_to_month(num), num_to_day(num),
+ sql_mode_for_dates(thd));
}
@@ -6109,7 +6132,17 @@ bool Field_datetime::get_date(MYSQL_TIME *ltime, ulonglong fuzzydate)
ltime->day= (int) (part1%100);
ltime->month= (int) (part1/100%100);
ltime->year= (int) (part1/10000);
- return validate_for_get_date(tmp, ltime, fuzzydate);
+ return validate_MMDD(tmp, ltime->month, ltime->day, fuzzydate);
+}
+
+bool
+Field_datetime::validate_value_in_record(THD *thd, const uchar *record) const
+{
+ DBUG_ASSERT(!is_null_in_record(record));
+ longlong tmp= sint8korr(ptr_in_record(record));
+ uint32 YYMMDD= (uint32) (tmp / 1000000LL);
+ return validate_MMDD(tmp, YYMMDD /100 % 100, YYMMDD % 100,
+ sql_mode_for_dates(thd));
}
int Field_datetime::cmp(const uchar *a_ptr, const uchar *b_ptr)
@@ -6166,6 +6199,15 @@ void Field_datetime_hires::store_TIME(MYSQL_TIME *ltime)
store_bigendian(packed, ptr, Field_datetime_hires::pack_length());
}
+ulonglong
+Field_datetime_hires::get_TIME(MYSQL_TIME *ltime, const uchar *pos) const
+{
+ ulonglong packed= read_bigendian(pos, Field_datetime_hires::pack_length());
+ unpack_time(sec_part_unshift(packed, dec), ltime);
+ return packed;
+}
+
+
int Field_temporal_with_date::store_decimal(const my_decimal *d)
{
ulonglong nr;
@@ -6225,11 +6267,22 @@ String *Field_datetime_with_dec::val_str(String *str,
bool Field_datetime_hires::get_date(MYSQL_TIME *ltime, ulonglong fuzzydate)
{
- ulonglong packed= read_bigendian(ptr, Field_datetime_hires::pack_length());
- unpack_time(sec_part_unshift(packed, dec), ltime);
- return validate_for_get_date(packed, ltime, fuzzydate);
+ ulonglong packed= get_TIME(ltime, ptr);
+ return validate_MMDD(packed, ltime->month, ltime->day, fuzzydate);
}
+
+bool
+Field_datetime_hires::validate_value_in_record(THD *thd, const uchar *record)
+ const
+{
+ DBUG_ASSERT(!is_null_in_record(record));
+ MYSQL_TIME ltime;
+ ulonglong packed= get_TIME(<ime, ptr_in_record(record));
+ return validate_MMDD(packed, ltime.month, ltime.day, sql_mode_for_dates(thd));
+}
+
+
uint32 Field_datetime_hires::pack_length() const
{
return datetime_hires_bytes[dec];
@@ -6270,7 +6323,18 @@ bool Field_datetimef::get_date(MYSQL_TIME *ltime, ulonglong fuzzydate)
{
longlong tmp= my_datetime_packed_from_binary(ptr, dec);
TIME_from_longlong_datetime_packed(ltime, tmp);
- return validate_for_get_date(tmp, ltime, fuzzydate);
+ return validate_MMDD(tmp, ltime->month, ltime->day, fuzzydate);
+}
+
+
+bool
+Field_datetimef::validate_value_in_record(THD *thd, const uchar *record) const
+{
+ DBUG_ASSERT(!is_null_in_record(record));
+ longlong tmp= my_datetime_packed_from_binary(ptr_in_record(record), dec);
+ MYSQL_TIME ltime;
+ TIME_from_longlong_datetime_packed(<ime, tmp);
+ return validate_MMDD(tmp, ltime.month, ltime.day, sql_mode_for_dates(thd));
}
@@ -10268,3 +10332,29 @@ void Field::set_explicit_default(Item *value)
return;
set_has_explicit_value();
}
+
+
+bool Field::validate_value_in_record_with_warn(THD *thd, const uchar *record)
+{
+ if (!validate_value_in_record(thd, record))
+ return false;
+ /*
+ Only write_set[field_index] is set for "this",
+ while read_set[field_index] is not guaranteed to be set.
+ Set table->read_set to NULL,
+ to make ASSERT_COLUMN_MARKED_FOR_READ in val_str() happy.
+ */
+ MY_BITMAP *read_set_backup= table->read_set;
+ table->read_set= 0;
+ // Get val_str() for the DEFAULT value
+ StringBuffer<MAX_FIELD_WIDTH> tmp;
+ val_str(&tmp, ptr_in_record(record));
+ // Convert val_str() result to a printable error parameter
+ ErrConvString err(&tmp);
+ push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+ ER_INVALID_DEFAULT_VALUE_FOR_FIELD,
+ ER(ER_INVALID_DEFAULT_VALUE_FOR_FIELD),
+ err.ptr(), field_name);
+ table->read_set= read_set_backup;
+ return true;
+}
diff --git a/sql/field.h b/sql/field.h
index 4ca493e..2b430cb 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -518,6 +518,11 @@ class Field
virtual int reset(void) { bzero(ptr,pack_length()); return 0; }
virtual void reset_fields() {}
+ const uchar *ptr_in_record(const uchar *record) const
+ {
+ my_ptrdiff_t l_offset= (my_ptrdiff_t) (record - table->record[0]);
+ return ptr + l_offset;
+ }
virtual void set_default()
{
my_ptrdiff_t l_offset= (my_ptrdiff_t) (table->s->default_values -
@@ -964,6 +969,15 @@ class Field
flags |= (column_format_arg << FIELD_FLAGS_COLUMN_FORMAT);
}
+ /*
+ Validate a non-null field value stored in the given record
+ according to the current thread settings, e.g. sql_mode.
+ @param thd - the thread
+ @param record - the record to check in
+ */
+ virtual bool validate_value_in_record(THD *thd, const uchar *record) const
+ { return false; }
+ bool validate_value_in_record_with_warn(THD *thd, const uchar *record);
key_map get_possible_keys();
/* Hash value */
@@ -1681,12 +1695,12 @@ class Field_temporal_with_date: public Field_temporal {
int store_TIME_with_warning(MYSQL_TIME *ltime, const ErrConv *str,
int was_cut, int have_smth_to_conv);
virtual void store_TIME(MYSQL_TIME *ltime) = 0;
- bool validate_for_get_date(bool not_zero_date, const MYSQL_TIME *ltime,
- ulonglong fuzzydate) const
+ bool validate_MMDD(bool not_zero_date, uint month, uint day,
+ ulonglong fuzzydate) const
{
if (!not_zero_date)
return fuzzydate & TIME_NO_ZERO_DATE;
- if (!ltime->month || !ltime->day)
+ if (!month || !day)
return fuzzydate & TIME_NO_ZERO_IN_DATE;
return false;
}
@@ -1755,7 +1769,11 @@ class Field_timestamp :public Field_temporal {
return res;
}
/* Get TIMESTAMP field value as seconds since begging of Unix Epoch */
- virtual my_time_t get_timestamp(ulong *sec_part) const;
+ virtual my_time_t get_timestamp(const uchar *pos, ulong *sec_part) const;
+ my_time_t get_timestamp(ulong *sec_part) const
+ {
+ return get_timestamp(ptr, sec_part);
+ }
virtual void store_TIME(my_time_t timestamp, ulong sec_part)
{
int4store(ptr,timestamp);
@@ -1771,6 +1789,7 @@ class Field_timestamp :public Field_temporal {
{
return unpack_int32(to, from, from_end);
}
+ bool validate_value_in_record(THD *thd, const uchar *record) const;
uint size_of() const { return sizeof(*this); }
};
@@ -1828,7 +1847,7 @@ class Field_timestamp_hires :public Field_timestamp_with_dec {
{
DBUG_ASSERT(dec);
}
- my_time_t get_timestamp(ulong *sec_part) const;
+ my_time_t get_timestamp(const uchar *pos, ulong *sec_part) const;
void store_TIME(my_time_t timestamp, ulong sec_part);
int cmp(const uchar *,const uchar *);
uint32 pack_length() const;
@@ -1872,7 +1891,7 @@ class Field_timestampf :public Field_timestamp_with_dec {
return memcmp(a_ptr, b_ptr, pack_length());
}
void store_TIME(my_time_t timestamp, ulong sec_part);
- my_time_t get_timestamp(ulong *sec_part) const;
+ my_time_t get_timestamp(const uchar *pos, ulong *sec_part) const;
uint size_of() const { return sizeof(*this); }
};
@@ -1934,6 +1953,9 @@ class Field_date :public Field_temporal_with_date {
class Field_newdate :public Field_temporal_with_date {
void store_TIME(MYSQL_TIME *ltime);
+ // Routines to convert a packed numeric representation into some date parts
+ uint num_to_day(uint32 tmp) const { return tmp & 31; }
+ uint num_to_month(uint32 tmp) const { return (tmp >> 5) & 15; }
public:
Field_newdate(uchar *ptr_arg, uchar *null_ptr_arg, uchar null_bit_arg,
enum utype unireg_check_arg, const char *field_name_arg)
@@ -1953,6 +1975,7 @@ class Field_newdate :public Field_temporal_with_date {
uint32 pack_length() const { return 3; }
void sql_type(String &str) const;
bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate);
+ bool validate_value_in_record(THD *thd, const uchar *record) const;
uint size_of() const { return sizeof(*this); }
};
@@ -2153,6 +2176,7 @@ class Field_datetime :public Field_temporal_with_date {
{
return unpack_int64(to, from, from_end);
}
+ bool validate_value_in_record(THD *thd, const uchar *record) const;
uint size_of() const { return sizeof(*this); }
};
@@ -2200,6 +2224,7 @@ class Field_datetime_with_dec :public Field_datetime {
*/
class Field_datetime_hires :public Field_datetime_with_dec {
void store_TIME(MYSQL_TIME *ltime);
+ ulonglong get_TIME(MYSQL_TIME *ltime, const uchar *pos) const;
public:
Field_datetime_hires(uchar *ptr_arg, uchar *null_ptr_arg,
uchar null_bit_arg, enum utype unireg_check_arg,
@@ -2212,6 +2237,7 @@ class Field_datetime_hires :public Field_datetime_with_dec {
int cmp(const uchar *,const uchar *);
uint32 pack_length() const;
bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate);
+ bool validate_value_in_record(THD *thd, const uchar *record) const;
uint size_of() const { return sizeof(*this); }
};
@@ -2252,6 +2278,7 @@ class Field_datetimef :public Field_datetime_with_dec {
}
int reset();
bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate);
+ bool validate_value_in_record(THD *thd, const uchar *record) const;
uint size_of() const { return sizeof(*this); }
};
diff --git a/sql/field_conv.cc b/sql/field_conv.cc
index e31f7c5..14f2947 100644
--- a/sql/field_conv.cc
+++ b/sql/field_conv.cc
@@ -859,7 +859,8 @@ bool memcpy_field_possible(Field *to,Field *from)
from->charset() == to->charset() &&
(!sql_mode_for_dates(to->table->in_use) ||
(from->type()!= MYSQL_TYPE_DATE &&
- from->type()!= MYSQL_TYPE_DATETIME)) &&
+ from->type()!= MYSQL_TYPE_DATETIME &&
+ from->type()!= MYSQL_TYPE_TIMESTAMP)) &&
(from_real_type != MYSQL_TYPE_VARCHAR ||
((Field_varstring*)from)->length_bytes ==
((Field_varstring*)to)->length_bytes));
diff --git a/sql/item.cc b/sql/item.cc
index f685242..0f2813a 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -8184,7 +8184,12 @@ int Item_default_value::save_in_field(Field *field_arg, bool no_conversions)
return 1;
}
field_arg->set_default();
- return 0;
+ THD *thd= current_thd;
+ return
+ !field_arg->is_null_in_record(field_arg->table->s->default_values) &&
+ field_arg->validate_value_in_record_with_warn(thd,
+ field_arg->table->s->default_values) &&
+ thd->is_error() ? -1 : 0;
}
return Item_field::save_in_field(field_arg, no_conversions);
}
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 0846740..d79fbcc 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7127,3 +7127,5 @@ ER_ROLE_DROP_EXISTS
eng "Can't drop role '%-.64s'; it doesn't exist"
ER_CANNOT_CONVERT_CHARACTER
eng "Cannot convert '%s' character 0x%-.64s to '%s'"
+ER_INVALID_DEFAULT_VALUE_FOR_FIELD 22007
+ eng "Incorrect default value '%-.128s' for column '%.192s'"
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index aaaf7b9..1a02b1d 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -872,10 +872,29 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
table->reset_default_fields();
+ if (fields.elements || !value_count)
+ {
+ /*
+ There are possibly some default values:
+ INSERT INTO t1 (fields) VALUES ...
+ INSERT INTO t1 VALUES ()
+ */
+ if (table->validate_default_values_of_unset_fields(thd))
+ {
+ error= 1;
+ goto values_loop_end;
+ }
+ }
+
while ((values= its++))
{
if (fields.elements || !value_count)
{
+ /*
+ There are possibly some default values:
+ INSERT INTO t1 (fields) VALUES ...
+ INSERT INTO t1 VALUES ()
+ */
restore_record(table,s->default_values); // Get empty record
if (fill_record_n_invoke_before_triggers(thd, table, fields, *values, 0,
TRG_EVENT_INSERT))
@@ -896,6 +915,10 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
}
else
{
+ /*
+ No field list, all fields are set explicitly:
+ INSERT INTO t1 VALUES (values)
+ */
if (thd->lex->used_tables) // Column used in values()
restore_record(table,s->default_values); // Get empty record
else
@@ -967,6 +990,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
thd->get_stmt_da()->inc_current_row_for_warning();
}
+values_loop_end:
free_underlaid_joins(thd, &thd->lex->select_lex);
joins_freed= TRUE;
@@ -3555,6 +3579,8 @@ int select_insert::prepare2(void)
thd->locked_tables_mode <= LTM_LOCK_TABLES &&
!thd->lex->describe)
table->file->ha_start_bulk_insert((ha_rows) 0);
+ if (table->validate_default_values_of_unset_fields(thd))
+ DBUG_RETURN(1);
DBUG_RETURN(0);
}
diff --git a/sql/sql_load.cc b/sql/sql_load.cc
index 142036b..e0e0d66 100644
--- a/sql/sql_load.cc
+++ b/sql/sql_load.cc
@@ -477,7 +477,12 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
thd->abort_on_warning= !ignore && thd->is_strict_mode();
thd_progress_init(thd, 2);
- if (ex->filetype == FILETYPE_XML) /* load xml */
+ if (table_list->table->validate_default_values_of_unset_fields(thd))
+ {
+ read_info.error= true;
+ error= 1;
+ }
+ else if (ex->filetype == FILETYPE_XML) /* load xml */
error= read_xml_field(thd, info, table_list, fields_vars,
set_fields, set_values, read_info,
*(ex->line_term), skip_lines, ignore);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 0c6ea2f..120031f 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2437,7 +2437,8 @@ void JOIN::exec_inner()
}
columns_list= &procedure_fields_list;
}
- (void) result->prepare2(); // Currently, this cannot fail.
+ if (result->prepare2())
+ DBUG_VOID_RETURN;
if (!tables_list && (table_count || !select_lex->with_sum_func))
{ // Only test of functions
diff --git a/sql/table.cc b/sql/table.cc
index 5d2c188..a027b39 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -6896,6 +6896,40 @@ bool TABLE::prepare_triggers_for_update_stmt_or_event()
return FALSE;
}
+
+/**
+ Validates default value of fields which are not specified in
+ the column list of INSERT/LOAD statement.
+
+ @Note s->default_values should be properly populated
+ before calling this function.
+
+ @param thd thread context
+ @param record the record to check values in
+
+ @return
+ @retval false Success.
+ @retval true Failure.
+*/
+
+bool TABLE::validate_default_values_of_unset_fields(THD *thd) const
+{
+ DBUG_ENTER("TABLE::validate_default_values_of_unset_fields");
+ for (Field **fld= field; *fld; fld++)
+ {
+ if (!bitmap_is_set(write_set, (*fld)->field_index) &&
+ !((*fld)->flags & NO_DEFAULT_VALUE_FLAG))
+ {
+ if (!(*fld)->is_null_in_record(s->default_values) &&
+ (*fld)->validate_value_in_record_with_warn(thd, s->default_values) &&
+ thd->is_error())
+ DBUG_RETURN(true); // strict mode converted WARN to ERROR
+ }
+ }
+ DBUG_RETURN(false);
+}
+
+
/*
@brief Reset const_table flag
diff --git a/sql/table.h b/sql/table.h
index 3c9938d..622a3b2 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1378,6 +1378,8 @@ struct TABLE
void prepare_triggers_for_insert_stmt_or_event();
bool prepare_triggers_for_delete_stmt_or_event();
bool prepare_triggers_for_update_stmt_or_event();
+
+ bool validate_default_values_of_unset_fields(THD *thd) const;
};
Follow ups
References
