diff options
Diffstat (limited to 'mysql-test/r/type_timestamp.result')
| -rw-r--r-- | mysql-test/r/type_timestamp.result | 193 |
1 files changed, 193 insertions, 0 deletions
diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 786bce471ce..95e89e22f2b 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -747,5 +747,198 @@ 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 +# +# +# MDEV-8373 Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field +# +SET sql_mode=DEFAULT; +CREATE TABLE t1 (a TIMESTAMP);; +INSERT INTO t1 VALUES (0); +SET sql_mode='TRADITIONAL'; +CREATE TABLE t2 AS SELECT * FROM t1; +ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at row 1 +DROP TABLE t1; +# +# End of MDEV-8373 Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field +# +# +# MDEV-8699 Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x' +# +CREATE TABLE t1 (a TIMESTAMP);; +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); +SELECT * FROM t1 WHERE a='2001-01-01 00:00:00x'; +a +2001-01-01 00:00:00 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' +SELECT * FROM t1 WHERE LENGTH(a) != 20; +a +2001-01-01 00:00:00 +2001-01-01 00:00:01 +SELECT * FROM t1 WHERE LENGTH(a) != 20 AND a='2001-01-01 00:00:00x'; +a +2001-01-01 00:00:00 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' +Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a) != 20 AND a='2001-01-01 00:00:00x'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = '2001-01-01 00:00:00x') +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)!=30+RAND() AND a='2001-01-01 00:00:00x'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = '2001-01-01 00:00:00x') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) <> (30 + rand()))) +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP);; +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); +SELECT * FROM t1 WHERE LENGTH(a)=19; +a +2001-01-01 00:00:00 +2001-01-01 00:00:01 +SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=' 2001-01-01 00:00:00'; +a +2001-01-01 00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=' 2001-01-01 00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = ' 2001-01-01 00:00:00') +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=19+RAND() AND a=' 2001-01-01 00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = ' 2001-01-01 00:00:00') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = (19 + rand()))) +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=' garbage '; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Warning 1292 Incorrect datetime value: ' garbage ' +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = ' garbage ') and (length(`test`.`t1`.`a`) = (30 + rand()))) +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP);; +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); +SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 00:00:00.000000'; +a +2001-01-01 00:00:00 +SELECT * FROM t1 WHERE LENGTH(a)=19; +a +2001-01-01 00:00:00 +2001-01-01 00:00:01 +SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIMESTAMP'2001-01-01 00:00:00.000000'; +a +2001-01-01 00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIMESTAMP'2001-01-01 00:00:00.000000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000') +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 00:00:00.000000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = (30 + rand()))) +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP(6));; +INSERT INTO t1 VALUES ('2001-01-01 00:00:00.000000'),('2001-01-01 00:00:01.000000'); +SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 00:00:00.000000'; +a +2001-01-01 00:00:00.000000 +SELECT * FROM t1 WHERE LENGTH(a)=26; +a +2001-01-01 00:00:00.000000 +2001-01-01 00:00:01.000000 +SELECT * FROM t1 WHERE LENGTH(a)=26 AND a=TIMESTAMP'2001-01-01 00:00:00.000000'; +a +2001-01-01 00:00:00.000000 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=26 AND a=TIMESTAMP'2001-01-01 00:00:00.000000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000') +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=40+RAND() AND a=TIMESTAMP'2001-01-01 00:00:00.000000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00.000000')) = (40 + rand()))) +DROP TABLE t1; +SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); +CREATE TABLE t1 (a TIMESTAMP);; +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); +SELECT * FROM t1 WHERE a=TIME'00:00:00'; +a +2001-01-01 00:00:00 +SELECT * FROM t1 WHERE LENGTH(a)=19; +a +2001-01-01 00:00:00 +2001-01-01 00:00:01 +SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIME'00:00:00'; +a +2001-01-01 00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIME'00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = TIME'00:00:00') +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=40+RAND() AND a=TIME'00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = TIME'00:00:00') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = (40 + rand()))) +DROP TABLE t1; +# # End of 10.1 tests # |