diff options
Diffstat (limited to 'mysql-test/main/old-mode.result')
| -rw-r--r-- | mysql-test/main/old-mode.result | 259 |
1 files changed, 259 insertions, 0 deletions
diff --git a/mysql-test/main/old-mode.result b/mysql-test/main/old-mode.result new file mode 100644 index 00000000..daa2a4dc --- /dev/null +++ b/mysql-test/main/old-mode.result @@ -0,0 +1,259 @@ +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 not null default now()); +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 not null default now()); +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; +# +# MDEV-13995 MAX(timestamp) returns a wrong result near DST change +# +SET global mysql56_temporal_format=false; +SET time_zone='+00:00'; +CREATE TABLE t1 (a TIMESTAMP(0)); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526) /*summer time in Moscow*/); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526+3599) /*winter time in Moscow*/); +SET time_zone='Europe/Moscow'; +SELECT a, COALESCE(a), UNIX_TIMESTAMP(a) FROM t1; +a COALESCE(a) UNIX_TIMESTAMP(a) +2010-10-31 02:25:26 2010-10-31 02:25:26 1288477526 +2010-10-31 02:25:25 2010-10-31 02:25:25 1288481125 +SELECT MIN(a), UNIX_TIMESTAMP(MIN(a)) AS a FROM t1; +MIN(a) a +2010-10-31 02:25:26 1288477526 +SELECT MAX(a), UNIX_TIMESTAMP(MAX(a)) AS a FROM t1; +MAX(a) a +2010-10-31 02:25:25 1288481125 +SELECT t1.a, UNIX_TIMESTAMP(t1.a), t2.a, UNIX_TIMESTAMP(t2.a) FROM t1 t1, t1 t2 WHERE t1.a=t2.a; +a UNIX_TIMESTAMP(t1.a) a UNIX_TIMESTAMP(t2.a) +2010-10-31 02:25:26 1288477526 2010-10-31 02:25:26 1288477526 +2010-10-31 02:25:25 1288481125 2010-10-31 02:25:25 1288481125 +ALTER TABLE t1 MODIFY a TIMESTAMP(1); +SELECT a, COALESCE(a), UNIX_TIMESTAMP(a) FROM t1; +a COALESCE(a) UNIX_TIMESTAMP(a) +2010-10-31 02:25:26.0 2010-10-31 02:25:26.0 1288477526.0 +2010-10-31 02:25:25.0 2010-10-31 02:25:25.0 1288481125.0 +SELECT MIN(a), UNIX_TIMESTAMP(MIN(a)) AS a FROM t1; +MIN(a) a +2010-10-31 02:25:26.0 1288477526.0 +SELECT MAX(a), UNIX_TIMESTAMP(MAX(a)) AS a FROM t1; +MAX(a) a +2010-10-31 02:25:25.0 1288481125.0 +SELECT t1.a, UNIX_TIMESTAMP(t1.a), t2.a, UNIX_TIMESTAMP(t2.a) FROM t1 t1, t1 t2 WHERE t1.a=t2.a; +a UNIX_TIMESTAMP(t1.a) a UNIX_TIMESTAMP(t2.a) +2010-10-31 02:25:26.0 1288477526.0 2010-10-31 02:25:26.0 1288477526.0 +2010-10-31 02:25:25.0 1288481125.0 2010-10-31 02:25:25.0 1288481125.0 +DROP TABLE t1; +SET time_zone=DEFAULT; +SET global mysql56_temporal_format=true; +# +# MDEV-26765 UNIX_TIMESTAMP(CURRENT_TIME()) return null ?!? +# +SET old_mode=zero_date_time_cast; +SET @@time_zone='+00:00'; +SET timestamp=1234567; +SELECT CURRENT_TIMESTAMP; +CURRENT_TIMESTAMP +1970-01-15 06:56:07 +SELECT UNIX_TIMESTAMP(CURRENT_TIME()); +UNIX_TIMESTAMP(CURRENT_TIME()) +NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '06:56:07' +SELECT UNIX_TIMESTAMP(TIME'06:56:07'); +UNIX_TIMESTAMP(TIME'06:56:07') +NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '06:56:07' +SELECT UNIX_TIMESTAMP(TIME'10:20:30'); +UNIX_TIMESTAMP(TIME'10:20:30') +NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '10:20:30' +CREATE OR REPLACE TABLE t1 (a TIME); +INSERT INTO t1 VALUES (TIME'06:56:07'),('10:20:30'); +SELECT UNIX_TIMESTAMP(a) FROM t1 ORDER BY a; +UNIX_TIMESTAMP(a) +NULL +NULL +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +Warning 1264 Out of range value for column 'a' at row 2 +DROP TABLE t1; +SET @@time_zone=DEFAULT; +SET TIMESTAMP=DEFAULT; |