summaryrefslogtreecommitdiff
path: root/mysql-test/main/strict.result
diff options
context:
space:
mode:
authorMichael Widenius <monty@mariadb.org>2018-03-09 14:05:35 +0200
committerMonty <monty@mariadb.org>2018-03-29 13:59:44 +0300
commita7abddeffa6a760ce948c2dfb007cdf3f1a369d5 (patch)
tree70eb743fa965a17380bbc0ac88ae79ca1075b896 /mysql-test/main/strict.result
parentab1941266c59a19703a74b5593cf3f508a5752d7 (diff)
downloadmariadb-git-a7abddeffa6a760ce948c2dfb007cdf3f1a369d5.tar.gz
Create 'main' test directory and move 't' and 'r' there
Diffstat (limited to 'mysql-test/main/strict.result')
-rw-r--r--mysql-test/main/strict.result1530
1 files changed, 1530 insertions, 0 deletions
diff --git a/mysql-test/main/strict.result b/mysql-test/main/strict.result
new file mode 100644
index 00000000000..315bb9dc1b5
--- /dev/null
+++ b/mysql-test/main/strict.result
@@ -0,0 +1,1530 @@
+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 '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';
+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 '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 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 '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';
+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 '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 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
+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 '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 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
+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 1265 Data truncated 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';
+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 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 '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 22007: Incorrect date value: '2004-09-31 15:30:00' for column 'col1' at row 1
+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 22007: Incorrect date value: '2003-02-29 15:30:00' for column 'col1' at row 1
+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 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col2' at row 1
+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 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col2' at row 1
+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 '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 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col3' at row 1
+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 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col3' at row 1
+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('2004-0-10');
+ERROR 22007: Incorrect date value: '2004-0-10' 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 (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';
+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 'col1' at row 1
+INSERT INTO t1 (col1) VALUES ('a59b');
+ERROR 22007: 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 22007: Incorrect integer value: '' for column 'col1' at row 1
+INSERT INTO t1 (col1) VALUES ('a59b');
+ERROR 22007: 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 22007: Incorrect integer value: '' for column 'col1' at row 1
+INSERT INTO t1 (col1) VALUES ('a59b');
+ERROR 22007: 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 22007: Incorrect integer value: '' for column 'col1' at row 1
+INSERT INTO t1 (col1) VALUES ('a59b');
+ERROR 22007: 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 22007: Incorrect integer value: '' for column 'col1' at row 1
+INSERT INTO t1 (col1) VALUES ('a59b');
+ERROR 22007: 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 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 'col1' at row 1
+INSERT INTO t1 (col1) VALUES ('a59b');
+ERROR 22007: Incorrect decimal 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);
+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 'col1' at row 1
+INSERT INTO t1 (col1) VALUES ('a59b');
+ERROR 22007: Incorrect double 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 (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;
+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 22007: Incorrect double value: '' for column 'col1' at row 1
+INSERT INTO t1 (col1) VALUES ('a59b');
+ERROR 22007: Incorrect double 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 (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 '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';
+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 '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
+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 '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 = 65532); 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=latin1
+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(11) NOT NULL,
+ `b` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+ PRIMARY KEY (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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=latin1
+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=latin1
+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=latin1 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=latin1 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 'col1' at row 1
+insert into t1(col2) values('+');
+ERROR 22007: Incorrect integer value: '+' for column 'col2' at row 1
+insert into t1(col3) values('-');
+ERROR 22007: Incorrect integer value: '-' for column 'col3' at row 1
+insert into t1(col4) values('+');
+ERROR 22007: Incorrect integer value: '+' for column 'col4' at row 1
+insert into t1(col5) values('-');
+ERROR 22007: Incorrect integer value: '-' for column 'col5' at row 1
+insert into t1(col6) values('+');
+ERROR 22007: Incorrect integer value: '+' for column 'col6' at row 1
+insert into t1(col7) values('-');
+ERROR 22007: Incorrect integer value: '-' for column 'col7' at row 1
+insert into t1(col8) values('+');
+ERROR 22007: Incorrect integer value: '+' for column 'col8' at row 1
+insert into t1(col9) values('-');
+ERROR 22007: Incorrect integer value: '-' for column 'col9' at row 1
+insert into t1(col10) values('+');
+ERROR 22007: 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 22007: Incorrect integer value: '-' for column 'a' at row 1
+insert into t1 values ('+');
+ERROR 22007: Incorrect integer value: '+' for column 'a' at row 1
+insert into t1 values ('');
+ERROR 22007: 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';
+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
+#
View Lorry Controller Status