EVOLUTION-MANAGER

Edit File: old-mode.result

drop table if exists t1,t2; create table t1 (a int, b varchar(200), c text not null) checksum=1; create table t2 (a int, b varchar(200), c text not null) checksum=0; insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, ""); insert t2 select * from t1; checksum table t1, t2; Table Checksum test.t1 2948697075 test.t2 2948697075 checksum table t1, t2 quick; Table Checksum test.t1 NULL test.t2 NULL checksum table t1, t2 extended; Table Checksum test.t1 2948697075 test.t2 2948697075 drop table t1,t2; SHOW PROCESSLIST; Id User Host db Command Time State Info <Id> root <Host> test Query <Time> <State> SHOW PROCESSLIST # # MDEV-5372 Make "CAST(time_expr AS DATETIME)" compatible with the SQL Standard) # set @@old_mode=zero_date_time_cast; SELECT CAST(TIME'-10:30:30' AS DATETIME); CAST(TIME'-10:30:30' AS DATETIME) NULL Warnings: Warning 1292 Truncated incorrect datetime value: '-10:30:30' SELECT CAST(TIME'10:20:30' AS DATETIME); CAST(TIME'10:20:30' AS DATETIME) 0000-00-00 10:20:30 SELECT CAST(TIME'830:20:30' AS DATETIME); CAST(TIME'830:20:30' AS DATETIME) 0000-01-03 14:20:30 CREATE TABLE t1 (a DATETIME); INSERT IGNORE INTO t1 VALUES (TIME'-10:20:30'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 INSERT INTO t1 VALUES (TIME'10:20:30'); INSERT INTO t1 VALUES (TIME'830:20:30'); SELECT * FROM t1; a 0000-00-00 00:00:00 0000-00-00 10:20:30 0000-01-03 14:20:30 DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP); INSERT IGNORE INTO t1 VALUES (TIME'-10:20:30'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 INSERT IGNORE INTO t1 VALUES (TIME'10:20:30'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 INSERT IGNORE INTO t1 VALUES (TIME'830:20:30'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 SELECT * FROM t1; a 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 DROP TABLE t1; CREATE TABLE t1 (a TIME); INSERT INTO t1 VALUES (TIME'-10:20:30'); INSERT INTO t1 VALUES (TIME'10:20:30'); INSERT INTO t1 VALUES (TIME'830:20:30'); SELECT a, CAST(a AS DATETIME), TO_DAYS(a) FROM t1; a CAST(a AS DATETIME) TO_DAYS(a) -10:20:30 NULL NULL 10:20:30 0000-00-00 10:20:30 NULL 830:20:30 0000-01-03 14:20:30 NULL Warnings: Warning 1292 Truncated incorrect datetime value: '-10:20:30' Warning 1264 Out of range value for column 'a' at row 1 Warning 1264 Out of range value for column 'a' at row 2 Warning 1264 Out of range value for column 'a' at row 3 DROP TABLE t1; SELECT TO_DAYS(TIME'-10:20:30'); TO_DAYS(TIME'-10:20:30') NULL Warnings: Warning 1292 Truncated incorrect datetime value: '-10:20:30' SELECT TO_DAYS(TIME'10:20:30'); TO_DAYS(TIME'10:20:30') NULL Warnings: Warning 1292 Truncated incorrect datetime value: '10:20:30' SELECT TO_DAYS(TIME'830:20:30'); TO_DAYS(TIME'830:20:30') 3 CREATE TABLE t1 (a DATETIME, b TIME); INSERT INTO t1 VALUES (NULL, '00:20:12'); INSERT INTO t1 VALUES (NULL, '-00:20:12'); SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1; IF(1,ADDDATE(IFNULL(a,b),0),1) NULL NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00 00:20:12' Warning 1292 Truncated incorrect datetime value: '-00:20:12' DROP TABLE t1; # # MDEV-6649 Different warnings for TIME and TIME(N) when @@old_mode=zero_date_time_cast # SET @@global.mysql56_temporal_format=true; SET @@old_mode=zero_date_time_cast; CREATE TABLE t1 (a TIME,b TIME(1)); INSERT INTO t1 VALUES (TIME'830:20:30',TIME'830:20:30'); SELECT TO_DAYS(a), TO_DAYS(b) FROM t1; TO_DAYS(a) TO_DAYS(b) NULL NULL Warnings: Warning 1264 Out of range value for column 'a' at row 1 Warning 1264 Out of range value for column 'b' at row 1 DROP TABLE t1; SET @@global.mysql56_temporal_format=false; SET @@old_mode=zero_date_time_cast; CREATE TABLE t1 (a TIME,b TIME(1)); INSERT INTO t1 VALUES (TIME'830:20:30',TIME'830:20:30'); SELECT TO_DAYS(a), TO_DAYS(b) FROM t1; TO_DAYS(a) TO_DAYS(b) NULL NULL Warnings: Warning 1264 Out of range value for column 'a' at row 1 Warning 1264 Out of range value for column 'b' at row 1 DROP TABLE t1; SET @@global.mysql56_temporal_format=DEFAULT; set time_zone='Europe/Moscow'; set global mysql56_temporal_format=false; create table t1 (a timestamp); set timestamp=1288477526; insert t1 values (null); insert t1 values (); set timestamp=1288481126; insert t1 values (null); insert t1 values (); select a, unix_timestamp(a) from t1; a unix_timestamp(a) 2010-10-31 02:25:26 1288477526 2010-10-31 02:25:26 1288477526 2010-10-31 02:25:26 1288481126 2010-10-31 02:25:26 1288481126 set global mysql56_temporal_format=true; select a, unix_timestamp(a) from t1; a unix_timestamp(a) 2010-10-31 02:25:26 1288477526 2010-10-31 02:25:26 1288477526 2010-10-31 02:25:26 1288481126 2010-10-31 02:25:26 1288481126 alter table t1 modify a timestamp; select a, unix_timestamp(a) from t1; a unix_timestamp(a) 2010-10-31 02:25:26 1288477526 2010-10-31 02:25:26 1288477526 2010-10-31 02:25:26 1288481126 2010-10-31 02:25:26 1288481126 drop table t1; set global mysql56_temporal_format=false; create table t1 (a timestamp); set timestamp=1288477526; insert t1 values (null); set timestamp=1288481126; insert t1 values (null); select a, unix_timestamp(a) from t1; a unix_timestamp(a) 2010-10-31 02:25:26 1288477526 2010-10-31 02:25:26 1288481126 set global mysql56_temporal_format=true; select a, unix_timestamp(a) from t1; a unix_timestamp(a) 2010-10-31 02:25:26 1288477526 2010-10-31 02:25:26 1288481126 create table t2 (a timestamp); insert t2 select a from t1; select a, unix_timestamp(a) from t2; a unix_timestamp(a) 2010-10-31 02:25:26 1288477526 2010-10-31 02:25:26 1288481126 drop table t1, t2; set time_zone=DEFAULT;