mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-10-31 02:46:29 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/strict.result
Geng Tian 173b16f3bc MDEV-11341 STR_TO_DATE does not return NULL for invalid dates 
STR_TO_DATE function in MariaDB does date to string conversion for invalid
dates strings, e.g.: 1949-02-30, and 1949-06-31, but null is return by
other invalid dates such as 1949-01-32 because the checking function
returns null for any date whose day is larger than 31, which is too naive
and needs to be changed.

This commit changed the rule of check_date in extract_date_time function,
which is executed after extracting date and time and check if the result
is valid. Now the conversion will consider the number of days in a month,
including skip years.

All new code of the whole pull request, including one or several files that
are either new files or modified ones, are contributed under the BSD-new
license. I am contributing on behalf of my employer Amazon Web Services, Inc.
2025-04-19 13:13:19 +04:00

1576 lines
67 KiB
Text
Raw Permalink Blame History

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,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,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 `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31');
ERROR 22007: Incorrect date value: '2004-0-31' for column `test`.`t1`.`col1` at row 2
INSERT INTO t1 VALUES('2004-10-0');
ERROR 22007: Incorrect date value: '2004-10-0' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES('2004-09-31');
ERROR 22007: Incorrect date value: '2004-09-31' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES('2004-10-32');
ERROR 22007: Incorrect date value: '2004-10-32' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES('2003-02-29');
ERROR 22007: Incorrect date value: '2003-02-29' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES('2004-13-15');
ERROR 22007: Incorrect date value: '2004-13-15' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES('0000-00-00');
ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES ('59');
ERROR 22007: Incorrect date value: '59' for column `test`.`t1`.`col1` at row 1
set @@sql_mode='STRICT_ALL_TABLES';
INSERT INTO t1 VALUES('2004-01-03'),('2004-0-31');
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
INSERT INTO t1 VALUES('2004-0-30');
ERROR 22007: Incorrect date value: '2004-0-30' for column `test`.`t1`.`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 `test`.`t1`.`col1` at row 2
INSERT INTO t1 VALUES('0000-00-00');
INSERT IGNORE INTO t1 VALUES('2004-0-29');
Warnings:
Warning 1265 Data truncated for column 'col1' at row 1
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
INSERT INTO t1 VALUES('0000-00-00');
ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`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 `test`.`t1`.`col1` at row 1
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
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';
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 `test`.`t1`.`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 `test`.`t1`.`col1` at row 1
INSERT ignore INTO t1 VALUES('2003-02-30');
Warnings:
Warning 1265 Data truncated for column 'col1' at row 1
INSERT INTO t1 VALUES('2001-01-01W');
ERROR 22007: Incorrect date value: '2001-01-01W' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES('2001-01-01T');
ERROR 22007: Incorrect date value: '2001-01-01T' for column `test`.`t1`.`col1` at row 1
set @@sql_mode='strict_all_tables';
INSERT INTO t1 VALUES('2001-01-01W');
ERROR 22007: Incorrect date value: '2001-01-01W' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES('2001-01-01T');
ERROR 22007: Incorrect date value: '2001-01-01T' for column `test`.`t1`.`col1` at row 1
set @@sql_mode='strict_all_tables,strict_trans_tables';
INSERT INTO t1 VALUES('2001-01-01W');
ERROR 22007: Incorrect date value: '2001-01-01W' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES('2001-01-01T');
ERROR 22007: Incorrect date value: '2001-01-01T' for column `test`.`t1`.`col1` at row 1
set @@sql_mode='';
INSERT INTO t1 VALUES('2001-01-01W');
Warnings:
Warning 1265 Data truncated for column 'col1' at row 1
INSERT INTO t1 VALUES('2001-01-01T');
Warnings:
Warning 1265 Data truncated for column 'col1' at row 1
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
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
2001-01-01
2001-01-01
2003-02-31
drop table t1;
set @@sql_mode='strict_trans_tables';
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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`col1` at row 1
INSERT ignore INTO t1 VALUES('2003-02-30');
Warnings:
Warning 1265 Data truncated for column 'col1' at row 1
INSERT INTO t1 VALUES('2001-01-01W');
ERROR 22007: Incorrect date value: '2001-01-01W' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES('2001-01-01T');
ERROR 22007: Incorrect date value: '2001-01-01T' for column `test`.`t1`.`col1` at row 1
set @@sql_mode='strict_all_tables';
INSERT INTO t1 VALUES('2001-01-01W');
ERROR 22007: Incorrect date value: '2001-01-01W' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES('2001-01-01T');
ERROR 22007: Incorrect date value: '2001-01-01T' for column `test`.`t1`.`col1` at row 1
set @@sql_mode='strict_all_tables,strict_trans_tables';
INSERT INTO t1 VALUES('2001-01-01W');
ERROR 22007: Incorrect date value: '2001-01-01W' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES('2001-01-01T');
ERROR 22007: Incorrect date value: '2001-01-01T' for column `test`.`t1`.`col1` at row 1
set @@sql_mode='';
INSERT INTO t1 VALUES('2001-01-01W');
Warnings:
Warning 1265 Data truncated for column 'col1' at row 1
INSERT INTO t1 VALUES('2001-01-01T');
Warnings:
Warning 1265 Data truncated for column 'col1' at row 1
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
INSERT ignore INTO t1 VALUES('2003-02-31');
select * from t1;
col1
0000-00-00
2004-01-04
0000-00-00
2001-01-01
2001-01-01
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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES ('59');
ERROR 22007: Incorrect datetime value: '59' for column `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`col1` at row 1
INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00');
Warnings:
Warning 1265 Data truncated for column 'col1' at row 1
INSERT INTO t1 VALUES ('59');
ERROR 22007: Incorrect datetime value: '59' for column `test`.`t1`.`col1` at row 1
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
INSERT INTO t1 VALUES('2004-0-31 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`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 `test`.`t1`.`col1` at row 1
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t1`.`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'));
Warnings:
Note 1265 Data truncated for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
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.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 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column `test`.`t1`.`col3` at row 1
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 `test`.`t1`.`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('2004-0-10');
ERROR 22007: Incorrect date value: '2004-0-10' for column `test`.`t1`.`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 (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 `test`.`t1`.`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';
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 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`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 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`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 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`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 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`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 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`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 01000: Data truncated 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 22007: Incorrect decimal value: '' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR 22007: Incorrect decimal value: 'a59b' for column `test`.`t1`.`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);
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);
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 22007: Incorrect double value: '' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR 22007: Incorrect double value: 'a59b' for column `test`.`t1`.`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);
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;
Got one of the listed errors
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 22007: Incorrect double value: '' for column `test`.`t1`.`col1` at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR 22007: Incorrect double value: 'a59b' for column `test`.`t1`.`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(11) 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 ');
Warnings:
Note 1292 Truncated incorrect DOUBLE value: '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 `test`.`t1`.`col1` at row 1
insert into t1 values (0.0,0.0,0.0);
ERROR 22007: Incorrect date value: '0.0' for column `test`.`t1`.`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';
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 1265 Data truncated 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 1265 Data truncated 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 `test`.`t1`.`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 ``.`(temporary)`.`col1` at row 1
alter table t1 modify col1 datetime;
ERROR 22007: Incorrect datetime value: '0000-00-00' for column `test`.`t1`.`col1` at row 1
alter ignore table t1 modify col1 datetime;
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 ``.`(temporary)`.`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));
ERROR 42000: Column length too big for column 'a' (max = 16383); use BLOB or TEXT instead
create table t1(a varbinary(65537));
ERROR 42000: Column length too big for column 'a' (max = 65532); 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(20) unsigned NOT NULL,
`b` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_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 `test`.`t1`.`d` at row 1
insert into t1 values (1000);
ERROR 22007: Incorrect date value: '1000' for column `test`.`t1`.`d` at row 1
insert into t1 values ('2000-10-01');
update t1 set d = 1100;
ERROR 22007: Incorrect date value: '1100' for column `test`.`t1`.`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(11) NOT NULL,
`b` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_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(11) NOT NULL,
`b` timestamp NOT NULL DEFAULT '2005-01-02 03:04:05',
PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_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_uca1400_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 STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
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)
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;
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(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_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(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_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 22007: Incorrect integer value: '-' for column `test`.`t1`.`col1` at row 1
insert into t1(col2) values('+');
ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col2` at row 1
insert into t1(col3) values('-');
ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`col3` at row 1
insert into t1(col4) values('+');
ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col4` at row 1
insert into t1(col5) values('-');
ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`col5` at row 1
insert into t1(col6) values('+');
ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col6` at row 1
insert into t1(col7) values('-');
ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`col7` at row 1
insert into t1(col8) values('+');
ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col8` at row 1
insert into t1(col9) values('-');
ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`col9` at row 1
insert into t1(col10) values('+');
ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col10` at row 1
drop table t1;
set sql_mode='traditional';
create table t1(a year);
insert into t1 values ('-');
ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`a` at row 1
insert into t1 values ('+');
ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`a` at row 1
insert into t1 values ('');
ERROR 22007: Incorrect integer value: '' for column `test`.`t1`.`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';
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';
SELECT STR_TO_DATE('2001','%Y'),CONCAT(STR_TO_DATE('2001','%Y')), STR_TO_DATE('2001','%Y')+1, STR_TO_DATE('0','%Y')+1, STR_TO_DATE('0000','%Y')+1;
STR_TO_DATE('2001','%Y') CONCAT(STR_TO_DATE('2001','%Y')) STR_TO_DATE('2001','%Y')+1 STR_TO_DATE('0','%Y')+1 STR_TO_DATE('0000','%Y')+1
2001-00-00 2001-00-00 20010001 20000001 NULL
Warnings:
Warning 1411 Incorrect datetime value: '0000' for function str_to_date
SET sql_mode='NO_ZERO_IN_DATE';
SELECT STR_TO_DATE('2001','%Y'),CONCAT(STR_TO_DATE('2001','%Y')), STR_TO_DATE('2001','%Y')+1, STR_TO_DATE('0000','%Y')+1;
STR_TO_DATE('2001','%Y') CONCAT(STR_TO_DATE('2001','%Y')) STR_TO_DATE('2001','%Y')+1 STR_TO_DATE('0000','%Y')+1
NULL NULL NULL 1
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
#
Reference in a new issue View git blame Copy permalink