diff options
Diffstat (limited to 'mysql-test/r/func_time.result')
| -rw-r--r-- | mysql-test/r/func_time.result | 132 |
1 files changed, 131 insertions, 1 deletions
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 995b5f702e2..8c156efbac1 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1941,9 +1941,139 @@ SELECT 1 FROM DUAL WHERE MINUTE(TIMEDIFF(NULL, '12:12:12')); SELECT 1 FROM DUAL WHERE SECOND(TIMEDIFF(NULL, '12:12:12')); 1 # -# MDEV-4635 Crash in UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')) +# MDEV-4511 Assertion `scale <= precision' fails on GROUP BY TIMEDIFF with incorrect types # +CREATE TABLE t1 (a DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT a FROM t1 GROUP BY TIMEDIFF('2004-06-12',a) * 1; +a +2005-05-04 +Warnings: +Warning 1292 Truncated incorrect time value: '2004-06-12' +Warning 1292 Truncated incorrect time value: '2004-06-12' +DROP TABLE t1; +CREATE TABLE t1 (a DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT a FROM t1 GROUP BY ADDTIME(a,'10')*1; +a +2000-02-23 +2005-05-04 +DROP TABLE t1; +CREATE TABLE t1 (a DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT * FROM t1 GROUP BY SEC_TO_TIME(concat(a,'10'))*1; +a +2000-02-23 +2005-05-04 +DROP TABLE t1; +CREATE TABLE t1 (a DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT * FROM t1 GROUP BY ADDTIME(timestamp('2001-01-01 00:00:00'),CAST(a AS SIGNED)&0xF)*1; +a +2005-05-04 +2000-02-23 +DROP TABLE t1; +CREATE TABLE t1 (a DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT * FROM t1 GROUP BY STR_TO_DATE(a,concat('%Y-%m-%d.%f',if(rand(),'','')))*1; +a +2000-02-23 +2005-05-04 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +STR_TO_DATE('2001-01-01', '%Y-%m-%d') AS date_only, +STR_TO_DATE('10:10:10', '%H:%i:%s') AS time_only, +STR_TO_DATE('10:10:10.123', '%H:%i:%s.%f') AS time_microsecond, +STR_TO_DATE('2001-01-01 10:10:10', '%Y-%m-%d %H:%i:%s') AS date_time, +STR_TO_DATE('2001-01-01 10:10:10.123', '%Y-%m-%d %H:%i:%s.%f') AS date_time_microsecond; +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +date_only date YES NULL +time_only time YES NULL +time_microsecond time(6) YES NULL +date_time datetime YES NULL +date_time_microsecond datetime(6) YES NULL +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +SEC_TO_TIME(1)+0.1, +SEC_TO_TIME(1.1)+0.1, +SEC_TO_TIME(1.12)+0.1, +SEC_TO_TIME(1.123456)+0.1, +SEC_TO_TIME(1.1234567)+0.1; +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +SEC_TO_TIME(1)+0.1 decimal(12,1) YES NULL +SEC_TO_TIME(1.1)+0.1 decimal(13,1) YES NULL +SEC_TO_TIME(1.12)+0.1 decimal(14,2) YES NULL +SEC_TO_TIME(1.123456)+0.1 decimal(18,6) YES NULL +SEC_TO_TIME(1.1234567)+0.1 decimal(18,6) YES NULL +DROP TABLE t1; +CREATE TABLE t1 (a DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT * FROM t1 GROUP BY FROM_UNIXTIME(concat(a,'10'))*1; +a +2000-02-23 +2005-05-04 +SELECT * FROM t1 GROUP BY (-FROM_UNIXTIME(concat(a,'10')))*1; +a +2005-05-04 +2000-02-23 +SELECT * FROM t1 GROUP BY (-FROM_UNIXTIME(concat(a,'10'))); +a +2005-05-04 +2000-02-23 +SELECT * FROM t1 GROUP BY ABS(FROM_UNIXTIME(concat(a,'10'))); +a +2000-02-23 +2005-05-04 +SELECT * FROM t1 GROUP BY @a:=(FROM_UNIXTIME(concat(a,'10'))*1); +a +2000-02-23 +2005-05-04 +DROP TABLE t1; SET TIME_ZONE='+02:00'; +# +# MDEV-6302 Wrong result set when using GROUP BY FROM_UNIXTIME(...)+0 +# +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT a, FROM_UNIXTIME(CONCAT(a,'10')) AS f1, FROM_UNIXTIME(CONCAT(a,'10'))+0 AS f2 FROM t1; +a f1 f2 +2005-05-04 1970-01-01 02:33:25 19700101023325.000000 +2000-02-23 1970-01-01 02:33:20 19700101023320.000000 +SELECT * FROM t1 GROUP BY FROM_UNIXTIME(CONCAT(a,'10'))+0; +a +2000-02-23 +2005-05-04 +DROP TABLE t1; +CREATE TABLE t1 (a DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT * FROM t1 GROUP BY FROM_UNIXTIME(concat(a,'10'))/1; +a +2000-02-23 +2005-05-04 +DROP TABLE t1; +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES ('2005-05-04'); +SELECT CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10'))) AS f2 FROM t1; +f2 +0.000000 +SELECT CHAR_LENGTH(CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10')))) AS f2 FROM t1; +f2 +8 +CREATE TABLE t2 AS SELECT CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10'))) AS f2 FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `f2` varchar(26) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +f2 +0.000000 +DROP TABLE t1,t2; +# +# MDEV-4635 Crash in UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')) +# SELECT UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')); UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')) NULL |