File: strict.result

package info (click to toggle)
mysql-8.0 8.0.43-3
links: PTS, VCS
area: main
in suites: sid
size: 1,273,924 kB
sloc: cpp: 4,684,605; ansic: 412,450; pascal: 108,398; java: 83,641; perl: 30,221; cs: 27,067; sql: 26,594; sh: 24,181; python: 21,816; yacc: 17,169; php: 11,522; xml: 7,388; javascript: 7,076; makefile: 2,194; lex: 1,075; awk: 670; asm: 520; objc: 183; ruby: 97; lisp: 86
file content (1967 lines) | stat: -rw-r--r-- 80,783 bytes
set @org_mode=@@sql_mode;
set @@sql_mode='ansi,traditional';
select @@sql_mode;
@@sql_mode
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (col1 date);
INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29');
INSERT INTO t1 VALUES('0000-10-31');
INSERT INTO t1 VALUES('2004-0-31');
ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31');
ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 2
INSERT INTO t1 VALUES('2004-10-0');
ERROR 22007: Incorrect date value: '2004-10-0' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-09-31');
ERROR 22007: Incorrect date value: '2004-09-31' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-10-32');
ERROR 22007: Incorrect date value: '2004-10-32' for column 'col1' at row 1
INSERT INTO t1 VALUES('2003-02-29');
ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-13-15');
ERROR 22007: Incorrect date value: '2004-13-15' for column 'col1' at row 1
INSERT INTO t1 VALUES('0000-00-00');
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
INSERT INTO t1 VALUES ('59');
ERROR 22007: Incorrect date value: '59' for column 'col1' at row 1
set @@sql_mode='STRICT_ALL_TABLES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
INSERT INTO t1 VALUES('2004-01-03'),('2004-0-31');
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
INSERT INTO t1 VALUES('2004-0-30');
ERROR 22007: Incorrect date value: '2004-0-30' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-01-04'),('2004-0-31'),('2004-01-05');
ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 2
INSERT INTO t1 VALUES('0000-00-00');
INSERT IGNORE INTO t1 VALUES('2004-0-29');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
INSERT INTO t1 VALUES('0000-00-00');
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
INSERT IGNORE INTO t1 VALUES('0000-00-00');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
INSERT INTO t1 VALUES ('2004-0-30');
INSERT INTO t1 VALUES ('2004-2-30');
ERROR 22007: Incorrect date value: '2004-2-30' for column 'col1' at row 1
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
INSERT INTO t1 VALUES ('2004-2-30');
set @@sql_mode='ansi,traditional';
INSERT IGNORE INTO t1 VALUES('2004-02-29'),('2004-13-15'),('0000-00-00');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 2
Warning	1264	Out of range value for column 'col1' at row 3
select * from t1;
col1
2004-01-01
2004-02-29
0000-10-31
2004-01-02
2004-01-03
2004-00-31
2004-01-04
0000-00-00
0000-00-00
0000-00-00
2004-00-30
2004-02-30
2004-02-29
0000-00-00
0000-00-00
drop table t1;
set @@sql_mode='strict_trans_tables';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
CREATE TABLE t1 (col1 date) engine=myisam;
INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');
ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 1
INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 2
INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT INTO t1 VALUES ('2003-02-29');
ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1
INSERT ignore INTO t1 VALUES('2003-02-30');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
INSERT ignore INTO t1 VALUES('2003-02-31');
select * from t1;
col1
2004-01-02
0000-00-00
2004-01-03
0000-00-00
2004-01-04
0000-00-00
2003-02-31
drop table t1;
set @@sql_mode='strict_trans_tables';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
CREATE TABLE t1 (col1 date) engine=innodb;
INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');
ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 1
INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');
ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 2
INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT INTO t1 VALUES ('2003-02-29');
ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1
INSERT ignore INTO t1 VALUES('2003-02-30');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
INSERT ignore INTO t1 VALUES('2003-02-31');
select * from t1;
col1
0000-00-00
2004-01-04
0000-00-00
2003-02-31
drop table t1;
set @@sql_mode='ansi,traditional';
CREATE TABLE t1 (col1 datetime);
INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');
INSERT INTO t1 VALUES('0000-10-31 15:30:00');
INSERT INTO t1 VALUES('2004-0-31 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-10-0 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-09-31 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-10-32 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2003-02-29 15:30:00');
ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-13-15 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('0000-00-00 15:30:00');
ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES ('59');
ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1
select * from t1;
col1
2004-10-31 15:30:00
2004-02-29 15:30:00
0000-10-31 15:30:00
drop table t1;
CREATE TABLE t1 (col1 timestamp);
INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');
INSERT INTO t1 VALUES('0000-10-31 15:30:00');
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-0-31 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-10-0 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-09-31 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-10-32 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2003-02-29 15:30:00');
ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-13-15 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-02-29 25:30:00');
ERROR 22007: Incorrect datetime value: '2004-02-29 25:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-02-29 15:65:00');
ERROR 22007: Incorrect datetime value: '2004-02-29 15:65:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-02-29 15:31:61');
ERROR 22007: Incorrect datetime value: '2004-02-29 15:31:61' for column 'col1' at row 1
INSERT INTO t1 VALUES('0000-00-00 15:30:00');
ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1
INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
INSERT INTO t1 VALUES ('59');
ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
INSERT INTO t1 VALUES('2004-0-31 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-10-0 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-10-32 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-02-30 15:30:04');
ERROR 22007: Incorrect datetime value: '2004-02-30 15:30:04' for column 'col1' at row 1
INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00');
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1
set @@sql_mode='ansi,traditional';
SELECT * FROM t1;
col1
2004-10-31 15:30:00
2004-02-29 15:30:00
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
DROP TABLE t1;
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y'));
INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '31.10.0000 15.30' for function str_to_date
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function str_to_date
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '31.9.2004 15.30' for function str_to_date
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '29.02.2003 15.30' for function str_to_date
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '31.10.0000 15.30' for function str_to_date
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function str_to_date
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '31.9.2004 15.30' for function str_to_date
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '29.02.2003 15.30' for function str_to_date
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '31.10.0000 15.30' for function str_to_date
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function str_to_date
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '31.9.2004 15.30' for function str_to_date
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '29.02.2003 15.30' for function str_to_date
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date
drop table t1;
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE));
INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME));
INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME));
INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE));
INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE));
ERROR 22007: Incorrect datetime value: '2004-10-0'
INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE));
ERROR 22007: Incorrect datetime value: '2004-0-10'
INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE));
ERROR 22007: Incorrect datetime value: '0000-00-00'
INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME));
INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME));
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30'
INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME));
ERROR 22007: Incorrect datetime value: '2004-0-10 15:30'
INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME));
ERROR 22007: Incorrect datetime value: '0000-00-00'
INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME));
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1
INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME));
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30'
INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME));
ERROR 22007: Incorrect datetime value: '2004-0-10 15:30'
INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME));
ERROR 22007: Incorrect datetime value: '0000-00-00'
drop table t1;
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE));
INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME));
INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME));
INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE));
INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE));
ERROR 22007: Incorrect datetime value: '2004-10-0'
INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE));
ERROR 22007: Incorrect datetime value: '2004-0-10'
INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE));
ERROR 22007: Incorrect datetime value: '0000-00-00'
INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME));
INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME));
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30'
INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME));
ERROR 22007: Incorrect datetime value: '2004-0-10 15:30'
INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME));
ERROR 22007: Incorrect datetime value: '0000-00-00'
INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME));
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1
INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME));
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30'
INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME));
ERROR 22007: Incorrect datetime value: '2004-0-10 15:30'
INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME));
ERROR 22007: Incorrect datetime value: '0000-00-00'
drop table t1;
CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED);
INSERT INTO t1 VALUES(-128,0),(0,0),(127,255),('-128','0'),('0','0'),('127','255'),(-128.0,0.0),(0.0,0.0),(127.0,255.0);
SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2;
MOD(col1,0)
NULL
NULL
Warnings:
Warning	1365	Division by 0
Warning	1365	Division by 0
INSERT INTO t1 (col1) VALUES(-129);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES(128);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES(-1);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES(256);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col1) VALUES('-129');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES('128');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES('-1');
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES('256');
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col1) VALUES(128.0);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES(-1.0);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES(256.0);
ERROR 22003: Out of range value for column 'col2' at row 1
SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 1;
MOD(col1,0)
NULL
Warnings:
Warning	1365	Division by 0
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
ERROR 22003: Out of range value for column 'col1' at row 1
UPDATE t1 SET col2=col2 + 50 WHERE col2 > 0;
ERROR 22003: Out of range value for column 'col2' at row 3
UPDATE t1 SET col1=col1 / 0 WHERE col1 > 0;
ERROR 22012: Division by 0
set @@sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
INSERT INTO t1 values (1/0,1/0);
Warnings:
Warning	1365	Division by 0
Warning	1365	Division by 0
set @@sql_mode='ansi,traditional';
SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2;
MOD(col1,0)
NULL
NULL
Warnings:
Warning	1365	Division by 0
Warning	1365	Division by 0
INSERT INTO t1 (col1) VALUES ('');
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('1a');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 values (1/0,1/0);
Warnings:
Warning	1365	Division by 0
Warning	1365	Division by 0
set @@sql_mode='ansi';
INSERT INTO t1 values (1/0,1/0);
set @@sql_mode='ansi,traditional';
INSERT IGNORE INTO t1 VALUES('-129','-1'),('128','256');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
INSERT IGNORE INTO t1 VALUES(-129.0,-1.0),(128.0,256.0);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
SELECT * FROM t1;
col1	col2
-128	0
0	NULL
127	255
-128	0
0	NULL
127	255
-128	0
0	NULL
127	255
NULL	NULL
2	NULL
NULL	NULL
NULL	NULL
-128	0
127	255
-128	0
127	255
DROP TABLE t1;
CREATE TABLE t1(col1 SMALLINT, col2 SMALLINT UNSIGNED);
INSERT INTO t1 VALUES(-32768,0),(0,0),(32767,65535),('-32768','0'),('32767','65535'),(-32768.0,0.0),(32767.0,65535.0);
INSERT INTO t1 (col1) VALUES(-32769);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES(32768);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES(-1);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES(65536);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col1) VALUES('-32769');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES('32768');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES('-1');
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES('65536');
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col1) VALUES(-32769.0);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES(32768.0);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES(-1.0);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES(65536.0);
ERROR 22003: Out of range value for column 'col2' at row 1
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
ERROR 22003: Out of range value for column 'col1' at row 1
UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0;
ERROR 22003: Out of range value for column 'col2' at row 3
UPDATE t1 SET col1 = col1 / 0 WHERE col1 > 0;
ERROR 22012: Division by 0
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
ERROR 22012: Division by 0
INSERT INTO t1 (col1) VALUES ('');
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('1a');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 values (1/0,1/0);
Warnings:
Warning	1365	Division by 0
Warning	1365	Division by 0
INSERT IGNORE INTO t1 VALUES(-32769,-1),(32768,65536);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
INSERT IGNORE INTO t1 VALUES('-32769','-1'),('32768','65536');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
INSERT IGNORE INTO t1 VALUES(-32769,-1.0),(32768.0,65536.0);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
SELECT * FROM t1;
col1	col2
-32768	0
0	NULL
32767	65535
-32768	0
32767	65535
-32768	0
32767	65535
2	NULL
NULL	NULL
-32768	0
32767	65535
-32768	0
32767	65535
-32768	0
32767	65535
DROP TABLE t1;
CREATE TABLE t1 (col1 MEDIUMINT, col2 MEDIUMINT UNSIGNED);
INSERT INTO t1 VALUES(-8388608,0),(0,0),(8388607,16777215),('-8388608','0'),('8388607','16777215'),(-8388608.0,0.0),(8388607.0,16777215.0);
INSERT INTO t1 (col1) VALUES(-8388609);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES(8388608);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES(-1);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES(16777216);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col1) VALUES('-8388609');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES('8388608');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES('-1');
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES('16777216');
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col1) VALUES(-8388609.0);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES(8388608.0);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES(-1.0);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES(16777216.0);
ERROR 22003: Out of range value for column 'col2' at row 1
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
ERROR 22003: Out of range value for column 'col1' at row 1
UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0;
ERROR 22003: Out of range value for column 'col2' at row 3
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
ERROR 22012: Division by 0
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
ERROR 22012: Division by 0
INSERT INTO t1 (col1) VALUES ('');
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('1a');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 values (1/0,1/0);
Warnings:
Warning	1365	Division by 0
Warning	1365	Division by 0
INSERT IGNORE INTO t1 VALUES(-8388609,-1),(8388608,16777216);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
INSERT IGNORE INTO t1 VALUES('-8388609','-1'),('8388608','16777216');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
INSERT IGNORE INTO t1 VALUES(-8388609.0,-1.0),(8388608.0,16777216.0);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
SELECT * FROM t1;
col1	col2
-8388608	0
0	NULL
8388607	16777215
-8388608	0
8388607	16777215
-8388608	0
8388607	16777215
2	NULL
NULL	NULL
-8388608	0
8388607	16777215
-8388608	0
8388607	16777215
-8388608	0
8388607	16777215
DROP TABLE t1;
CREATE TABLE t1 (col1 INT, col2 INT UNSIGNED);
INSERT INTO t1 VALUES(-2147483648,0),(0,0),(2147483647,4294967295),('-2147483648','0'),('2147483647','4294967295'),(-2147483648.0,0.0),(2147483647.0,4294967295.0);
INSERT INTO t1 (col1) VALUES(-2147483649);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES(2147643648);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES(-1);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES(4294967296);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col1) VALUES('-2147483649');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES('2147643648');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES('-1');
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES('4294967296');
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col1) VALUES(-2147483649.0);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES(2147643648.0);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES(-1.0);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES(4294967296.0);
ERROR 22003: Out of range value for column 'col2' at row 1
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
ERROR 22003: Out of range value for column 'col1' at row 1
UPDATE t1 SET col2 =col2 + 50 WHERE col2 > 0;
ERROR 22003: Out of range value for column 'col2' at row 3
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
ERROR 22012: Division by 0
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
ERROR 22012: Division by 0
INSERT INTO t1 (col1) VALUES ('');
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('1a');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 values (1/0,1/0);
Warnings:
Warning	1365	Division by 0
Warning	1365	Division by 0
INSERT IGNORE INTO t1 values (-2147483649, -1),(2147643648,4294967296);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
INSERT IGNORE INTO t1 values ('-2147483649', '-1'),('2147643648','4294967296');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
INSERT IGNORE INTO t1 values (-2147483649.0, -1.0),(2147643648.0,4294967296.0);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
SELECT * FROM t1;
col1	col2
-2147483648	0
0	NULL
2147483647	4294967295
-2147483648	0
2147483647	4294967295
-2147483648	0
2147483647	4294967295
2	NULL
NULL	NULL
-2147483648	0
2147483647	4294967295
-2147483648	0
2147483647	4294967295
-2147483648	0
2147483647	4294967295
DROP TABLE t1;
CREATE TABLE t1 (col1 BIGINT, col2 BIGINT UNSIGNED);
INSERT INTO t1 VALUES(-9223372036854775808,0),(0,0),(9223372036854775807,18446744073709551615);
INSERT INTO t1 VALUES('-9223372036854775808','0'),('9223372036854775807','18446744073709551615');
INSERT INTO t1 VALUES(-9223372036854774000.0,0.0),(9223372036854775700.0,1844674407370954000.0);
INSERT INTO t1 (col1) VALUES(-9223372036854775809);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES(9223372036854775808);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES(-1);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES(18446744073709551616);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col1) VALUES('-9223372036854775809');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES('9223372036854775808');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES('-1');
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES('18446744073709551616');
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col1) VALUES(-9223372036854785809.0);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES(9223372036854785808.0);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES(-1.0);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES(18446744073709551616.0);
ERROR 22003: Out of range value for column 'col2' at row 1
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
ERROR 22012: Division by 0
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
ERROR 22012: Division by 0
INSERT INTO t1 (col1) VALUES ('');
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('1a');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 values (1/0,1/0);
Warnings:
Warning	1365	Division by 0
Warning	1365	Division by 0
INSERT IGNORE INTO t1 VALUES(-9223372036854775809,-1),(9223372036854775808,18446744073709551616);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
INSERT IGNORE INTO t1 VALUES('-9223372036854775809','-1'),('9223372036854775808','18446744073709551616');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
INSERT IGNORE INTO t1 VALUES(-9223372036854785809.0,-1.0),(9223372036854785808.0,18446744073709551616.0);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
SELECT * FROM t1;
col1	col2
-9223372036854775808	0
0	NULL
9223372036854775807	18446744073709551615
-9223372036854775808	0
9223372036854775807	18446744073709551615
-9223372036854774000	0
9223372036854775700	1844674407370954000
2	NULL
NULL	NULL
-9223372036854775808	0
9223372036854775807	18446744073709551615
-9223372036854775808	0
9223372036854775807	18446744073709551615
-9223372036854775808	0
9223372036854775807	18446744073709551615
DROP TABLE t1;
CREATE TABLE t1 (col1 NUMERIC(4,2));
INSERT INTO t1 VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01);
Warnings:
Note	1265	Data truncated for column 'col1' at row 2
Note	1265	Data truncated for column 'col1' at row 5
INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01');
Warnings:
Note	1265	Data truncated for column 'col1' at row 2
Note	1265	Data truncated for column 'col1' at row 4
INSERT INTO t1 VALUES (101.55);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 VALUES (101);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 VALUES (-101.55);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 VALUES (1010.55);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 VALUES (1010);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 VALUES ('101.55');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 VALUES ('101');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 VALUES ('-101.55');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 VALUES ('-1010.55');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 VALUES ('-100E+1');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 VALUES ('-100E');
ERROR HY000: Incorrect decimal value: '-100E' for column 'col1' at row 1
UPDATE t1 SET col1 =col1 * 50000 WHERE col1 =11;
ERROR 22003: Out of range value for column 'col1' at row 6
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
ERROR 22012: Division by 0
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
ERROR 22012: Division by 0
INSERT INTO t1 (col1) VALUES ('');
ERROR HY000: Incorrect decimal value: '' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR HY000: Incorrect decimal value: 'a59b' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('1a');
ERROR HY000: Incorrect decimal value: '1a' for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Note	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 values (1/0);
Warnings:
Warning	1365	Division by 0
INSERT IGNORE INTO t1 VALUES(1000),(-1000);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
INSERT IGNORE INTO t1 VALUES('1000'),('-1000');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
INSERT IGNORE INTO t1 VALUES(1000.0),(-1000.0);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
UPDATE IGNORE t1 SET col1=1/NULL where col1=0;
SELECT * FROM t1;
col1
10.55
10.56
NULL
-10.55
-10.56
11.00
10.00
10.55
10.56
-10.55
-10.56
11.00
10.00
2.00
NULL
99.99
-99.99
99.99
-99.99
99.99
-99.99
DROP TABLE t1;
CREATE TABLE t1 (col1 FLOAT, col2 FLOAT UNSIGNED);
Warnings:
Warning	1681	UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
INSERT INTO t1 VALUES (-1.1E-37,0),(+3.4E+38,+3.4E+38);
INSERT INTO t1 VALUES ('-1.1E-37',0),('+3.4E+38','+3.4E+38');
INSERT INTO t1 (col1) VALUES (3E-46);
INSERT INTO t1 (col1) VALUES (+3.4E+39);
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES (-1.1E-3);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col1) VALUES ('+3.4E+39');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES ('-1.1E-3');
ERROR 22003: Out of range value for column 'col2' at row 1
UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0;
ERROR 22003: Out of range value for column 'col1' at row 2
UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0;
ERROR 22012: Division by 0
UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0;
ERROR 22012: Division by 0
INSERT INTO t1 (col1) VALUES ('');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('1a');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES (1/0);
Warnings:
Warning	1365	Division by 0
INSERT IGNORE INTO t1 VALUES (+3.4E+39,-3.4E+39);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
INSERT IGNORE INTO t1 VALUES ('+3.4E+39','-3.4E+39');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
SELECT * FROM t1;
col1	col2
-1.1e-37	0
3.4e38	3.4e38
-1.1e-37	0
3.4e38	3.4e38
0	NULL
2	NULL
NULL	NULL
3.40282e38	0
3.40282e38	0
DROP TABLE t1;
CREATE TABLE t1 (col1 DOUBLE PRECISION, col2 DOUBLE PRECISION UNSIGNED);
Warnings:
Warning	1681	UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
INSERT INTO t1 VALUES (-2.2E-307,0),(2E-307,0),(+1.7E+308,+1.7E+308);
INSERT INTO t1 VALUES ('-2.2E-307',0),('-2E-307',0),('+1.7E+308','+1.7E+308');
INSERT INTO t1 (col1) VALUES (-2.2E-330);
INSERT INTO t1 (col1) VALUES (+1.7E+309);
Got one of the listed errors
INSERT INTO t1 (col2) VALUES (-1.1E-3);
ERROR 22003: Out of range value for column 'col2' at row 1
INSERT INTO t1 (col1) VALUES ('+1.8E+309');
ERROR 22003: Out of range value for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES ('-1.2E-3');
ERROR 22003: Out of range value for column 'col2' at row 1
UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0;
ERROR 22003: DOUBLE value is out of range in '("test"."t1"."col1" * 5000)'
UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0;
ERROR 22012: Division by 0
UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0;
ERROR 22012: Division by 0
INSERT INTO t1 (col1) VALUES ('');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('1a');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) values (1/0);
Warnings:
Warning	1365	Division by 0
INSERT IGNORE INTO t1 VALUES (+1.9E+309,-1.9E+309);
ERROR 22007: Illegal double '1.9E+309' value found during parsing
INSERT IGNORE INTO t1 VALUES ('+2.0E+309','-2.0E+309');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
SELECT * FROM t1;
col1	col2
-2.2e-307	0
1e-303	0
1.7e308	1.7e308
-2.2e-307	0
-2e-307	0
1.7e308	1.7e308
0	NULL
2	NULL
NULL	NULL
1.7976931348623157e308	0
DROP TABLE t1;
CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6));
INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello   ', 'hello ');
INSERT INTO t1 (col1) VALUES ('hellobob');
ERROR 22001: Data too long for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES ('hellobob');
ERROR 22001: Data too long for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES ('hello  ');
Warnings:
Note	1265	Data truncated for column 'col2' at row 1
UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he';
ERROR 22001: Data too long for column 'col1' at row 2
UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he';
ERROR 22001: Data too long for column 'col2' at row 2
INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
Warning	1265	Data truncated for column 'col2' at row 1
UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he';
Warnings:
Warning	1265	Data truncated for column 'col2' at row 2
SELECT * FROM t1;
col1	col2
hello	hello
he	hellot
hello	hello 
NULL	hello 
hello	hellob
DROP TABLE t1;
CREATE TABLE t1 (col1 enum('red','blue','green'));
INSERT INTO t1 VALUES ('red'),('blue'),('green');
INSERT INTO t1 (col1) VALUES ('yellow');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('redd');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT INTO t1 VALUES ('');
ERROR 01000: Data truncated for column 'col1' at row 1
UPDATE t1 SET col1 ='yellow' WHERE col1 ='green';
ERROR 01000: Data truncated for column 'col1' at row 3
INSERT IGNORE INTO t1 VALUES ('yellow');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
UPDATE IGNORE t1 SET col1 ='yellow' WHERE col1 ='blue';
Warnings:
Warning	1265	Data truncated for column 'col1' at row 2
SELECT * FROM t1;
col1
red

green

DROP TABLE t1;
CREATE TABLE t1 (col1 INT NOT NULL, col2 CHAR(5) NOT NULL, col3 DATE NOT NULL);
INSERT INTO t1 VALUES (100, 'hello', '2004-08-20');
INSERT INTO t1 (col1,col2,col3) VALUES (101, 'hell2', '2004-08-21');
INSERT INTO t1 (col1,col2,col3) VALUES (NULL, '', '2004-01-01');
ERROR 23000: Column 'col1' cannot be null
INSERT INTO t1 (col1,col2,col3) VALUES (102, NULL, '2004-01-01');
ERROR 23000: Column 'col2' cannot be null
INSERT INTO t1 VALUES (103,'',NULL);
ERROR 23000: Column 'col3' cannot be null
UPDATE t1 SET col1=NULL WHERE col1 =100;
ERROR 23000: Column 'col1' cannot be null
UPDATE t1 SET col2 =NULL WHERE col2 ='hello';
ERROR 23000: Column 'col2' cannot be null
UPDATE t1 SET col2 =NULL where col3 IS NOT NULL;
ERROR 23000: Column 'col2' cannot be null
INSERT IGNORE INTO t1 values (NULL,NULL,NULL);
Warnings:
Warning	1048	Column 'col1' cannot be null
Warning	1048	Column 'col2' cannot be null
Warning	1048	Column 'col3' cannot be null
SELECT * FROM t1;
col1	col2	col3
100	hello	2004-08-20
101	hell2	2004-08-21
0		0000-00-00
DROP TABLE t1;
CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE "t1" (
  "col1" int NOT NULL DEFAULT '99',
  "col2" char(6) NOT NULL
)
INSERT INTO t1 VALUES (1, 'hello');
INSERT INTO t1 (col2) VALUES ('hello2');
INSERT INTO t1 (col2) VALUES (NULL);
ERROR 23000: Column 'col2' cannot be null
INSERT INTO t1 (col1) VALUES (2);
ERROR HY000: Field 'col2' doesn't have a default value
INSERT INTO t1 VALUES(default(col1),default(col2));
ERROR HY000: Field 'col2' doesn't have a default value
INSERT INTO t1 (col1) SELECT 1;
ERROR HY000: Field 'col2' doesn't have a default value
INSERT INTO t1 SELECT 1,NULL;
ERROR 23000: Column 'col2' cannot be null
INSERT IGNORE INTO t1 values (NULL,NULL);
Warnings:
Warning	1048	Column 'col1' cannot be null
Warning	1048	Column 'col2' cannot be null
INSERT IGNORE INTO t1 (col1) values (3);
Warnings:
Warning	1364	Field 'col2' doesn't have a default value
INSERT IGNORE INTO t1 () values ();
Warnings:
Warning	1364	Field 'col2' doesn't have a default value
SELECT * FROM t1;
col1	col2
1	hello
99	hello2
0	
3	
99	
DROP TABLE t1;
set sql_mode='traditional';
create table t1 (charcol char(255), varcharcol varchar(255),
binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext,
tinyblobcol tinyblob);
insert into t1 (charcol) values (repeat('x',256));
ERROR 22001: Data too long for column 'charcol' at row 1
insert into t1 (varcharcol) values (repeat('x',256));
ERROR 22001: Data too long for column 'varcharcol' at row 1
insert into t1 (binarycol) values (repeat('x',256));
ERROR 22001: Data too long for column 'binarycol' at row 1
insert into t1 (varbinarycol) values (repeat('x',256));
ERROR 22001: Data too long for column 'varbinarycol' at row 1
insert into t1 (tinytextcol) values (repeat('x',256));
ERROR 22001: Data too long for column 'tinytextcol' at row 1
insert into t1 (tinyblobcol) values (repeat('x',256));
ERROR 22001: Data too long for column 'tinyblobcol' at row 1
select * from t1;
charcol	varcharcol	binarycol	varbinarycol	tinytextcol	tinyblobcol
drop table t1;
set sql_mode='traditional';
create table t1 (col1 datetime);
insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
ERROR 22007: Truncated incorrect datetime value: '31.10.2004 15.30 abc'
insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date
insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));
ERROR HY000: Incorrect time value: '22:22:33 AM' for function str_to_date
insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));
ERROR HY000: Incorrect time value: 'abc' for function str_to_date
set sql_mode='';
insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
Warnings:
Warning	1292	Truncated incorrect datetime value: '31.10.2004 15.30 abc'
insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
Warnings:
Warning	1411	Incorrect datetime value: '32.10.2004 15.30' for function str_to_date
insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));
Warnings:
Warning	1411	Incorrect time value: '22:22:33 AM' for function str_to_date
insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));
Warnings:
Warning	1411	Incorrect time value: 'abc' for function str_to_date
insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i'));
insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r'));
insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T'));
select * from t1;
col1
2004-10-31 15:30:00
NULL
NULL
NULL
2004-10-31 15:30:00
2004-12-12 11:22:33
2004-12-12 10:22:59
set sql_mode='traditional';
select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL;
count(*)
7
Warnings:
Warning	1411	Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date
drop table t1;
create table t1 (col1 char(3), col2 integer);
insert into t1 (col1) values (cast(1000 as char(3)));
ERROR 22007: Truncated incorrect CHAR(3) value: '1000'
insert into t1 (col1) values (cast(1000E+0 as char(3)));
ERROR 22007: Truncated incorrect CHAR(3) value: '1000'
insert into t1 (col1) values (cast(1000.0 as char(3)));
ERROR 22007: Truncated incorrect CHAR(3) value: '1000.0'
insert into t1 (col2) values (cast('abc' as signed integer));
ERROR 22007: Truncated incorrect INTEGER value: 'abc'
insert into t1 (col2) values (10E+0 + 'a');
ERROR 22007: Truncated incorrect DOUBLE value: 'a'
insert into t1 (col2) values (cast('10a' as unsigned integer));
ERROR 22007: Truncated incorrect INTEGER value: '10a'
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;
col1	col2
NULL	10
NULL	10
NULL	10
drop table t1;
create table t1 (col1 date, col2 datetime, col3 timestamp);
insert into t1 values (0,0,0);
ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1
insert into t1 values (0.0,0.0,0.0);
ERROR 22007: Incorrect date value: '0.0' for column 'col1' at row 1
insert into t1 (col1) values (convert('0000-00-00',date));
ERROR 22007: Incorrect datetime value: '0000-00-00'
insert into t1 (col1) values (cast('0000-00-00' as date));
ERROR 22007: Incorrect datetime value: '0000-00-00'
set sql_mode='NO_ZERO_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
insert into t1 values (0,0,0);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col3' at row 1
insert into t1 values (0.0,0.0,0.0);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col3' at row 1
drop table t1;
set sql_mode='traditional';
create table t1 (col1 date);
insert ignore into t1 values ('0000-00-00');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
insert into t1 select * from t1;
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
insert ignore into t1 values ('0000-00-00');
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
insert ignore into t1 (col1) values (cast('0000-00-00' as date));
Warnings:
Warning	1292	Incorrect datetime value: '0000-00-00'
insert into t1 select * from t1;
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
alter table t1 modify col1 datetime;
ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col1' at row 1
select * from t1;
col1
0000-00-00
0000-00-00
NULL
drop table t1;
create table t1 (col1 datetime);
insert ignore into t1 values ('0000-00-00 00:00:00'),
('0000-00-00 00:00:00'),
(NULL);
Warnings:
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
insert into t1 select * from t1;
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1
select * from t1;
col1
0000-00-00 00:00:00
0000-00-00 00:00:00
NULL
drop table t1;
create table t1 (col1 tinyint);
drop procedure if exists t1;
Warnings:
Note	1305	PROCEDURE test.t1 does not exist
create procedure t1 () begin declare exit handler for sqlexception
select'a'; insert into t1 values (200); end;|
call t1();
a
a
select * from t1;
col1
drop procedure t1;
drop table t1;
set sql_mode=@org_mode;
SET @@sql_mode = 'traditional';
CREATE TABLE t1 (i int not null);
INSERT INTO t1 VALUES ();
ERROR HY000: Field 'i' doesn't have a default value
INSERT INTO t1 VALUES (DEFAULT);
ERROR HY000: Field 'i' doesn't have a default value
INSERT INTO t1 VALUES (DEFAULT(i));
ERROR HY000: Field 'i' doesn't have a default value
ALTER TABLE t1 ADD j int;
INSERT INTO t1 SET j = 1;
ERROR HY000: Field 'i' doesn't have a default value
INSERT INTO t1 SET j = 1, i = DEFAULT;
ERROR HY000: Field 'i' doesn't have a default value
INSERT INTO t1 SET j = 1, i = DEFAULT(i);
ERROR HY000: Field 'i' doesn't have a default value
INSERT INTO t1 VALUES (DEFAULT,1);
ERROR HY000: Field 'i' doesn't have a default value
DROP TABLE t1;
SET @@sql_mode = '';
CREATE TABLE t1 (i int not null);
INSERT INTO t1 VALUES ();
Warnings:
Warning	1364	Field 'i' doesn't have a default value
INSERT INTO t1 VALUES (DEFAULT);
Warnings:
Warning	1364	Field 'i' doesn't have a default value
INSERT INTO t1 VALUES (DEFAULT(i));
ERROR HY000: Field 'i' doesn't have a default value
ALTER TABLE t1 ADD j int;
INSERT INTO t1 SET j = 1;
Warnings:
Warning	1364	Field 'i' doesn't have a default value
INSERT INTO t1 SET j = 1, i = DEFAULT;
Warnings:
Warning	1364	Field 'i' doesn't have a default value
INSERT INTO t1 SET j = 1, i = DEFAULT(i);
ERROR HY000: Field 'i' doesn't have a default value
INSERT INTO t1 VALUES (DEFAULT,1);
Warnings:
Warning	1364	Field 'i' doesn't have a default value
DROP TABLE t1;
set @@sql_mode='traditional';
create table t1(a varchar(65537)) charset latin1;
ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
create table t1(a varbinary(65537));
ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
set @@sql_mode='traditional';
create table t1(a int, b date not null);
alter table t1 modify a bigint unsigned not null;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint unsigned NOT NULL,
  `b` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
drop table t1;
set @@sql_mode='traditional';
create table t1 (d date);
insert into t1 values ('2000-10-00');
ERROR 22007: Incorrect date value: '2000-10-00' for column 'd' at row 1
insert into t1 values (1000);
ERROR 22007: Incorrect date value: '1000' for column 'd' at row 1
insert into t1 values ('2000-10-01');
update t1 set d = 1100;
ERROR 22007: Incorrect date value: '1100' for column 'd' at row 1
select * from t1;
d
2000-10-01
drop table t1;
set @@sql_mode='traditional';
create table t1(a int, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
alter table t1 add primary key(a);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int NOT NULL,
  `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
drop table t1;
create table t1(a int, b timestamp not null default 20050102030405);
alter table t1 add primary key(a);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int NOT NULL,
  `b` timestamp NOT NULL DEFAULT '2005-01-02 03:04:05',
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
drop table t1;
set @@sql_mode='traditional';
create table t1(a bit(2));
insert into t1 values(b'101');
ERROR 22001: Data too long for column 'a' at row 1
select * from t1;
a
drop table t1;
set sql_mode='traditional';
create table t1 (date date not null);
create table t2 select date from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `date` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
drop table t2,t1;
set @@sql_mode= @org_mode;
set @@sql_mode='traditional';
create table t1 (i int)
comment '123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*';
ERROR HY000: Comment for table 't1' is too long (max = 2048)
create table t1 (
i int comment
'123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*');
ERROR HY000: Comment for field 'i' is too long (max = 1024)
set @@sql_mode= @org_mode;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
create table t1
(i int comment
'123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*');
Warnings:
Warning	1629	Comment for field 'i' is too long (max = 1024)
SET sql_mode = default;
select column_name, column_comment from information_schema.columns where
table_schema = 'test' and table_name = 't1';
COLUMN_NAME	COLUMN_COMMENT
i	123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*12345
drop table t1;
set names utf8;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
create table t1 (i int)
comment '123456789*123456789*123456789*123456789*123456789*123456789*';
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='123456789*123456789*123456789*123456789*123456789*123456789*'
drop table t1;
CREATE TABLE t3 (f1 INT) COMMENT 'כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן';
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `f1` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן'
DROP TABLE t3;
set sql_mode= 'traditional';
create table t1(col1 tinyint, col2 tinyint unsigned, 
col3 smallint, col4 smallint unsigned,
col5 mediumint, col6 mediumint unsigned,
col7 int, col8 int unsigned,
col9 bigint, col10 bigint unsigned);
insert into t1(col1) values('-');
ERROR HY000: Incorrect integer value: '-' for column 'col1' at row 1
insert into t1(col2) values('+');
ERROR HY000: Incorrect integer value: '+' for column 'col2' at row 1
insert into t1(col3) values('-');
ERROR HY000: Incorrect integer value: '-' for column 'col3' at row 1
insert into t1(col4) values('+');
ERROR HY000: Incorrect integer value: '+' for column 'col4' at row 1
insert into t1(col5) values('-');
ERROR HY000: Incorrect integer value: '-' for column 'col5' at row 1
insert into t1(col6) values('+');
ERROR HY000: Incorrect integer value: '+' for column 'col6' at row 1
insert into t1(col7) values('-');
ERROR HY000: Incorrect integer value: '-' for column 'col7' at row 1
insert into t1(col8) values('+');
ERROR HY000: Incorrect integer value: '+' for column 'col8' at row 1
insert into t1(col9) values('-');
ERROR HY000: Incorrect integer value: '-' for column 'col9' at row 1
insert into t1(col10) values('+');
ERROR HY000: Incorrect integer value: '+' for column 'col10' at row 1
drop table t1;
set sql_mode='traditional';
create table t1(a year);
insert into t1 values ('-');
ERROR HY000: Incorrect integer value: '-' for column 'a' at row 1
insert into t1 values ('+');
ERROR HY000: Incorrect integer value: '+' for column 'a' at row 1
insert into t1 values ('');
ERROR HY000: Incorrect integer value: '' for column 'a' at row 1
insert into t1 values ('2000a');
ERROR 01000: Data truncated for column 'a' at row 1
insert into t1 values ('2E3x');
ERROR 01000: Data truncated for column 'a' at row 1
drop table t1;
set sql_mode='traditional';
create table t1 (f1 set('a','a'));
ERROR HY000: Column 'f1' has duplicated value 'a' in SET
create table t1 (f1 enum('a','a'));
ERROR HY000: Column 'f1' has duplicated value 'a' in ENUM
set @@sql_mode='NO_ZERO_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
create table t1(a datetime not null);
select count(*) from t1 where a is null;
count(*)
0
drop table t1;
End of 5.0 tests
#
# Start of 5.6 tests
#
#
# WL#946 TIME/TIMESTAMP/DATETIME with fractional seconds: CAST to DATETIME
#
#
# STR_TO_DATE  with NO_ZERO_DATE did not return NULL (with warning)
# in get_date(). Only did in val_str() and val_int().
SET sql_mode='NO_ZERO_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
SELECT
STR_TO_DATE('2001','%Y'),
CONCAT(STR_TO_DATE('2001','%Y')),
STR_TO_DATE('2001','%Y')+1;
STR_TO_DATE('2001','%Y')	CONCAT(STR_TO_DATE('2001','%Y'))	STR_TO_DATE('2001','%Y')+1
NULL	NULL	NULL
Warnings:
Warning	1411	Incorrect datetime value: '2001' for function str_to_date
Warning	1411	Incorrect datetime value: '2001' for function str_to_date
Warning	1411	Incorrect datetime value: '2001' for function str_to_date
#
# End of 5.6 tests
#
#
# WL#7467 : Deprecate ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE,
#             NO_ZERO_IN_DATE SQL MODES and make their
#             functionality part of STRICT MODE
#
# Insertion of ZERO dates gives error if STRICT MODE
# is enabled with NO_ZERO_DATE and NO_ZERO_IN_DATE modes. Also Division
# by Zero would cause the statement to fail if STRICT MODE was enabled
# with ERROR_FOR_DIVISION_BY_ZERO mode.
#
SET @@sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES('0000-00-00');
ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1
INSERT IGNORE INTO t1 VALUES('0000-00-00');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
SELECT * FROM t1;
a
0000-00-00
INSERT INTO t1 VALUES('2004-0-30');
ERROR 22007: Incorrect date value: '2004-0-30' for column 'a' at row 1
INSERT IGNORE INTO t1 VALUES('2004-0-30');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
SELECT * FROM t1;
a
0000-00-00
0000-00-00
UPDATE t1 SET a = '0000-00-00';
ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1
UPDATE IGNORE t1 SET a = '0000-00-00';
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
Warning	1264	Out of range value for column 'a' at row 2
SELECT * FROM t1;
a
0000-00-00
0000-00-00
UPDATE t1 SET a = '2004-0-30';
ERROR 22007: Incorrect date value: '2004-0-30' for column 'a' at row 1
UPDATE IGNORE t1 SET a = '2004-0-30';
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
Warning	1264	Out of range value for column 'a' at row 2
SELECT * FROM t1;
a
0000-00-00
0000-00-00
INSERT INTO t1 SELECT * FROM t1;
ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1
INSERT IGNORE INTO t1 SELECT * FROM t1;
Warnings:
Warning	1292	Incorrect date value: '0000-00-00' for column 'a' at row 1
Warning	1292	Incorrect date value: '0000-00-00' for column 'a' at row 1
Warning	1264	Out of range value for column 'a' at row 1
Warning	1264	Out of range value for column 'a' at row 2
SELECT * FROM t1;
a
0000-00-00
0000-00-00
0000-00-00
0000-00-00
CREATE TABLE t2(b DATE) SELECT a FROM t1;
ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1
CREATE TABLE t2 (b DATE) IGNORE SELECT a FROM t1;
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
Warning	1264	Out of range value for column 'a' at row 2
Warning	1264	Out of range value for column 'a' at row 3
Warning	1264	Out of range value for column 'a' at row 4
SELECT * FROM t2;
b	a
NULL	0000-00-00
NULL	0000-00-00
NULL	0000-00-00
NULL	0000-00-00
DROP TABLE t2;
INSERT INTO t1 VALUES(1/0);
ERROR 22012: Division by 0
INSERT IGNORE INTO t1 VALUES(1/0);
Warnings:
Warning	1365	Division by 0
SELECT * FROM t1;
a
0000-00-00
0000-00-00
0000-00-00
0000-00-00
NULL
UPDATE t1 SET a = (1/0);
ERROR 22012: Division by 0
UPDATE IGNORE t1 SET a = (1/0);
Warnings:
Warning	1365	Division by 0
Warning	1365	Division by 0
Warning	1365	Division by 0
Warning	1365	Division by 0
Warning	1365	Division by 0
CREATE TABLE t2(b DATE) SELECT (1/0) FROM t1;
ERROR 22012: Division by 0
CREATE TABLE t2 (b DATE) IGNORE SELECT (1/0) FROM t1;
Warnings:
Warning	1365	Division by 0
Warning	1365	Division by 0
Warning	1365	Division by 0
Warning	1365	Division by 0
Warning	1365	Division by 0
SELECT * FROM t2;
b	(1/0)
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
# Clean-up
DROP TABLE t1,t2;
SET @@sql_mode = 'STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
CREATE TABLE t1 (a1 INT, a2 DATETIME);
UPDATE t1 SET a2={d '1789-07-14'} WHERE a1=0;
# Clean-up
DROP TABLE t1;
SET sql_mode=@org_mode;
#
# BUG 11756690 - ERROR 22012 DIVISION BY 0 HANDLED BY HANDLER FOR SQLSTATE 23000 (ER_DUP_ENTRY)
#
SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,STRICT_ALL_TABLES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
CREATE TABLE t1 (
Id INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY(Id),
c1 INTEGER NOT NULL,
c2 INTEGER NOT NULL
)|
CREATE PROCEDURE p1()
BEGIN
UPDATE t1 SET c1 = c1 / c2;
END|
CREATE PROCEDURE p2()
BEGIN
DECLARE EXIT HANDLER
FOR SQLSTATE '23000' # (ER_DUP_ENTRY)
BEGIN
SELECT 'Duplication handled!';
END;
CALL p1();
END|
INSERT INTO t1 (Id, c1, c2) VALUES (1, 1, 0);
CALL p1();
ERROR 22012: Division by 0
CALL p2();
ERROR 22012: Division by 0
# Clean-up
DROP TABLE t1;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
#
# Restore mode
#
SET sql_mode=@org_mode;
#
# BUG#11744940 - TRADITIONAL MODE: CAST ALLOWS INVALID VALUE
#
SET sql_mode='traditional';
CREATE TABLE t1 (col1 char(3));
INSERT INTO t1 VALUES(1000);
ERROR 22001: Data too long for column 'col1' at row 1
SELECT * FROM t1;
col1
INSERT INTO t1 VALUES(cast(1000 as char(4)));
ERROR 22001: Data too long for column 'col1' at row 1
SELECT * FROM t1;
col1
INSERT INTO t1 VALUES(cast(1000 as char(3)));
ERROR 22007: Truncated incorrect CHAR(3) value: '1000'
SELECT * FROM t1;
col1
# Clean-up
DROP TABLE t1;
#
# Restore mode
#
SET sql_mode=@org_mode;
#
# BUG#11744941 - TRADITIONAL MODE: BIGINT RANGE NOT CORRECTLY DELIMITED 
#
SET sql_mode='traditional';
CREATE TABLE t1 (col1 bigint) engine=innodb;
INSERT INTO t1 values(-9223372036854775808);
INSERT INTO t1 VALUES(-9223372036854775809);
ERROR 22003: Out of range value for column 'col1' at row 1
SELECT * FROM t1;
col1
-9223372036854775808
INSERT INTO t1 VALUES(9223372036854775807);
INSERT INTO t1 VALUES(9223372036854775808);
ERROR 22003: Out of range value for column 'col1' at row 1
SELECT * FROM t1;
col1
-9223372036854775808
9223372036854775807
CREATE TABLE t2 (col1 bigint unsigned);
INSERT INTO t1 VALUES(9223372036854775808);
ERROR 22003: Out of range value for column 'col1' at row 1
# Clean-up
DROP TABLE t1;
DROP TABLE t2;
# Restore mode
SET sql_mode=@org_mode;
#
# WL#6891- Test Coverage for ER_WARN_TOO_FEW_RECORDS in STRICT MODE
#
CREATE TABLE t1( a INT);
CREATE TABLE t2( a INT, b INT);
INSERT INTO t1 VALUES (1), (2), (3), (4);
SET sql_mode= STRICT_ALL_TABLES;
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/wl6614.txt' FROM t1;
SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/wl6614.txt');
LOAD_FILE('MYSQLTEST_VARDIR/tmp/wl6614.txt')
1
2
3
4

LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/wl6614.txt' INTO TABLE t2;
ERROR 01000: Row 1 doesn't contain data for all columns
SELECT * FROM t2;
a	b
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/wl6614.txt' IGNORE INTO TABLE t2;
Warnings:
Warning	1261	Row 1 doesn't contain data for all columns
Warning	1261	Row 2 doesn't contain data for all columns
Warning	1261	Row 3 doesn't contain data for all columns
Warning	1261	Row 4 doesn't contain data for all columns
SELECT * FROM t2;
a	b
1	NULL
2	NULL
3	NULL
4	NULL
# Clean-up
DROP TABLE t1;
DROP TABLE t2;
# Restore mode
SET sql_mode=@org_mode;
#
# BUG#11751889 - TRIGGERS OVERRIDE STRICT SQL_MODE 
# Fixed by WL#6891
#
SET sql_mode='STRICT_ALL_TABLES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
CREATE TABLE t1 (
id TINYINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=MyISAM;
CREATE TRIGGER t1_BI BEFORE INSERT ON t1
FOR EACH ROW
SET NEW.id := -1;
INSERT INTO t1 VALUES (18);
ERROR 22003: Out of range value for column 'id' at row 1
# Clean-up
DROP trigger t1_BI;
DROP TABLE t1;
# Restore mode
SET sql_mode=@org_mode;
#
# BUG#16976939 - FIX ERROR MESSAGE ON DUPLICATE INDEX CREATION AND STRICT MODE
# Fixed by WL#6891
#
SET sql_mode='STRICT_ALL_TABLES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
CREATE TABLE t1(c1 varchar(50)) engine=InnoDB;
ALTER TABLE t1 ADD KEY in_c1(c1);
# Previously this statement was giving error ER_DUP_INDEX in STRICT MODE
# In WL#6891, ER_DUP_INDEX is removed from being affected by STRICT MODE
# So the below statement will give warning only for ER_DUP_INDEX
ALTER TABLE t1 ADD KEY in_c2(c1);
Warnings:
Warning	1831	Duplicate index 'in_c2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
SHOW WARNINGS;
Level	Code	Message
Warning	1831	Duplicate index 'in_c2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
# Clean-up
DROP TABLE t1;
#
# Bug#18090591: STR_TO_DATE IN TRADITIONAL SQL_MODE FAILS TO PARSE
# TIME-ONLY FIELD
#
CREATE TABLE t1 ( a datetime(2) );
CREATE TABLE t2 ( a timestamp(2) );
SELECT str_to_date('09:22', '%H:%i');
str_to_date('09:22', '%H:%i')
09:22:00
SELECT str_to_date('09:22:23.33', '%H:%i:%s.%f');
str_to_date('09:22:23.33', '%H:%i:%s.%f')
09:22:23.330000
INSERT INTO t1 VALUES( str_to_date('09:22', '%H:%i') );
INSERT INTO t1 VALUES( str_to_date('09:22:23.33', '%H:%i:%s.%f') );
# We do this to get the test deterministic. The above INSERT's
# will insert the *current* date with the time added.
SELECT timediff( a, cast(CURRENT_DATE AS datetime) ) FROM t1;
timediff( a, cast(CURRENT_DATE AS datetime) )
09:22:00.00
09:22:23.33
DELETE FROM t1;
INSERT INTO t1 VALUES( str_to_date('2019-12-31', '%Y-%m-%d') );
SELECT * FROM t1;
a
2019-12-31 00:00:00.00
INSERT INTO t2 VALUES( str_to_date('09:22', '%H:%i') );
INSERT INTO t2 VALUES( str_to_date('09:22:23.33', '%H:%i:%s.%f') );
# We do this to get the test deterministic. The above INSERT's
# will insert the *current* date with the time added.
SELECT timediff( a, cast(CURRENT_DATE AS datetime) ) FROM t2;
timediff( a, cast(CURRENT_DATE AS datetime) )
09:22:00.00
09:22:23.33
DELETE FROM t2;
INSERT INTO t2 VALUES( str_to_date('2019-12-31', '%Y-%m-%d') );
CREATE TABLE t3 SELECT str_to_date('09:22:23.33', '%H:%i:%s.%f');
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `str_to_date('09:22:23.33', '%H:%i:%s.%f')` time(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t2;
a
2019-12-31 00:00:00.00
DROP TABLE t1, t2, t3;
#
# Bug#34704094: Mysql str_to_date can correct convert for '2022-11-30'
#
SET sql_mode='NO_ZERO_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
select str_to_date('0000-00-00', '%Y-%m-%d');
str_to_date('0000-00-00', '%Y-%m-%d')
NULL
Warnings:
Warning	1411	Incorrect datetime value: '0000-00-00' for function str_to_date
# Not compliant with how CAST works
select str_to_date('0000-01-00', '%Y-%m-%d');
str_to_date('0000-01-00', '%Y-%m-%d')
NULL
Warnings:
Warning	1411	Incorrect datetime value: '0000-01-00' for function str_to_date
# Not compliant with how CAST works
select str_to_date('0000-00-01', '%Y-%m-%d');
str_to_date('0000-00-01', '%Y-%m-%d')
NULL
Warnings:
Warning	1411	Incorrect datetime value: '0000-00-01' for function str_to_date
select str_to_date('2023-02-31', '%Y-%m-%d');
str_to_date('2023-02-31', '%Y-%m-%d')
NULL
Warnings:
Warning	1411	Incorrect datetime value: '2023-02-31' for function str_to_date
SET sql_mode='NO_ZERO_IN_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
select str_to_date('0000-00-00', '%Y-%m-%d');
str_to_date('0000-00-00', '%Y-%m-%d')
0000-00-00
select str_to_date('0000-01-00', '%Y-%m-%d');
str_to_date('0000-01-00', '%Y-%m-%d')
NULL
Warnings:
Warning	1411	Incorrect datetime value: '0000-01-00' for function str_to_date
select str_to_date('0000-00-01', '%Y-%m-%d');
str_to_date('0000-00-01', '%Y-%m-%d')
NULL
Warnings:
Warning	1411	Incorrect datetime value: '0000-00-01' for function str_to_date
select str_to_date('2023-02-31', '%Y-%m-%d');
str_to_date('2023-02-31', '%Y-%m-%d')
NULL
Warnings:
Warning	1411	Incorrect datetime value: '2023-02-31' for function str_to_date
SET sql_mode='ALLOW_INVALID_DATES';
select str_to_date('0000-00-00', '%Y-%m-%d');
str_to_date('0000-00-00', '%Y-%m-%d')
0000-00-00
select str_to_date('0000-01-00', '%Y-%m-%d');
str_to_date('0000-01-00', '%Y-%m-%d')
0000-01-00
select str_to_date('0000-00-01', '%Y-%m-%d');
str_to_date('0000-00-01', '%Y-%m-%d')
0000-00-01
select str_to_date('2023-02-31', '%Y-%m-%d');
str_to_date('2023-02-31', '%Y-%m-%d')
2023-02-31
SET sql_mode=@org_mode;