diff options
Diffstat (limited to 'mysql-test/main/type_datetime_hires.result')
| -rw-r--r-- | mysql-test/main/type_datetime_hires.result | 543 |
1 files changed, 541 insertions, 2 deletions
diff --git a/mysql-test/main/type_datetime_hires.result b/mysql-test/main/type_datetime_hires.result index 38e2c2a5ac8..ebb9c6032b9 100644 --- a/mysql-test/main/type_datetime_hires.result +++ b/mysql-test/main/type_datetime_hires.result @@ -17,8 +17,8 @@ a 2010-12-11 01:02:03.456 2010-12-11 03:04:05.789 2010-12-11 15:47:11.123 -select truncate(a, 6) from t1; -truncate(a, 6) +select cast(a AS double(30,6)) from t1; +cast(a AS double(30,6)) 0.000000 20101211002003.120000 20101211010203.457031 @@ -352,3 +352,542 @@ NULL 978307200.0000 DROP TABLE t1; SET @@time_zone=DEFAULT; +# +# Start of 10.4 tests +# +# +# MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() +# +CREATE TABLE t1 (a1 DATETIME(6), a2 DATETIME(6) NOT NULL); +CREATE TABLE t2 AS SELECT +ROUND(a1) AS r1, +ROUND(a2) AS r2, +TRUNCATE(a1,0) AS t1, +TRUNCATE(a2,0) AS t2 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `r1` datetime DEFAULT NULL, + `r2` datetime DEFAULT NULL, + `t1` datetime DEFAULT NULL, + `t2` datetime NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a DATETIME(6)); +INSERT INTO t1 VALUES +('0000-00-00 00:00:00.999999'), +('0000-00-00 23:59:59.999999'), +('0000-00-01 00:00:00.999999'), +('0000-00-01 23:59:59.999999'), +('0000-00-31 23:59:59.999999'), +('0000-01-01 00:00:00.999999'), +('0000-01-01 23:59:59.999999'), +('0000-01-31 23:59:59.999999'), +('0000-02-28 23:59:59.999999'), +('0000-12-31 23:59:59.999999'), +('0001-01-01 00:00:00.999999'), +('0001-02-28 23:59:59.999999'), +('0001-12-31 23:59:59.999999'), +('0004-02-28 23:59:59.999999'), +('0004-02-29 23:59:59.999999'), +('2000-02-29 23:59:59.999999'), +('2000-12-31 23:59:59.999999'), +('9999-12-31 23:59:59.999999'); +SELECT a, TRUNCATE(a,0) FROM t1; +a TRUNCATE(a,0) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59 +SELECT a, TRUNCATE(a,1) FROM t1; +a TRUNCATE(a,1) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.9 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.9 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.9 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.9 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.9 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.9 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.9 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.9 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.9 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.9 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.9 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.9 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.9 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.9 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.9 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.9 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.9 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.9 +SELECT a, TRUNCATE(a,2) FROM t1; +a TRUNCATE(a,2) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.99 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.99 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.99 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.99 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.99 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.99 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.99 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.99 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.99 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.99 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.99 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.99 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.99 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.99 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.99 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.99 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.99 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.99 +SELECT a, TRUNCATE(a,3) FROM t1; +a TRUNCATE(a,3) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999 +SELECT a, TRUNCATE(a,4) FROM t1; +a TRUNCATE(a,4) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.9999 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.9999 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.9999 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.9999 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.9999 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.9999 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.9999 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.9999 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.9999 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.9999 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.9999 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.9999 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.9999 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.9999 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.9999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.9999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.9999 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.9999 +SELECT a, TRUNCATE(a,5) FROM t1; +a TRUNCATE(a,5) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.99999 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.99999 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.99999 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.99999 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.99999 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.99999 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.99999 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.99999 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.99999 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.99999 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.99999 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.99999 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.99999 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.99999 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.99999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.99999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.99999 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.99999 +SELECT a, TRUNCATE(a,6) FROM t1; +a TRUNCATE(a,6) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999999 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999999 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999999 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999999 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999999 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999999 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999999 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999999 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999999 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999999 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999999 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999999 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999999 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999999 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 +SELECT a, TRUNCATE(a,7) FROM t1; +a TRUNCATE(a,7) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999999 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999999 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999999 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999999 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999999 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999999 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999999 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999999 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999999 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999999 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999999 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999999 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999999 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999999 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 +SELECT a, TRUNCATE(a,-1) FROM t1; +a TRUNCATE(a,-1) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59 +SELECT a, TRUNCATE(a,-6) FROM t1; +a TRUNCATE(a,-6) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59 +SELECT a, ROUND(a) FROM t1; +a ROUND(a) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,0) FROM t1; +a ROUND(a,0) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,1) FROM t1; +a ROUND(a,1) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01.0 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01.0 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01.0 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01.0 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00.0 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00.0 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00.0 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00.0 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.0 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.0 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.9 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,2) FROM t1; +a ROUND(a,2) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01.00 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01.00 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01.00 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01.00 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00.00 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00.00 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00.00 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00.00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.00 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.99 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,3) FROM t1; +a ROUND(a,3) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01.000 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01.000 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01.000 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01.000 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00.000 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00.000 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00.000 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00.000 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.000 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.000 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,4) FROM t1; +a ROUND(a,4) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01.0000 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01.0000 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01.0000 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01.0000 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00.0000 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00.0000 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00.0000 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00.0000 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.0000 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.0000 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.9999 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,5) FROM t1; +a ROUND(a,5) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01.00000 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01.00000 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01.00000 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01.00000 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00.00000 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00.00000 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00.00000 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00.00000 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.00000 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.00000 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.99999 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,6) FROM t1; +a ROUND(a,6) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999999 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999999 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999999 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999999 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999999 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999999 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999999 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999999 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999999 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999999 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999999 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999999 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999999 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999999 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 +SELECT a, ROUND(a,7) FROM t1; +a ROUND(a,7) +0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999999 +0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999999 +0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999999 +0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999999 +0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999999 +0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999999 +0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999999 +0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999999 +0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999999 +0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999999 +0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999999 +0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999999 +0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999999 +0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999999 +0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 +SELECT a, ROUND(a,-1) FROM t1; +a ROUND(a,-1) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +SELECT a, ROUND(a,-6) FROM t1; +a ROUND(a,-6) +0000-00-00 00:00:00.999999 0000-00-00 00:00:01 +0000-00-00 23:59:59.999999 NULL +0000-00-01 00:00:00.999999 0000-00-01 00:00:01 +0000-00-01 23:59:59.999999 NULL +0000-00-31 23:59:59.999999 NULL +0000-01-01 00:00:00.999999 0000-01-01 00:00:01 +0000-01-01 23:59:59.999999 NULL +0000-01-31 23:59:59.999999 NULL +0000-02-28 23:59:59.999999 NULL +0000-12-31 23:59:59.999999 NULL +0001-01-01 00:00:00.999999 0001-01-01 00:00:01 +0001-02-28 23:59:59.999999 0001-03-01 00:00:00 +0001-12-31 23:59:59.999999 0002-01-01 00:00:00 +0004-02-28 23:59:59.999999 0004-02-29 00:00:00 +0004-02-29 23:59:59.999999 0004-03-01 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +9999-12-31 23:59:59.999999 9999-12-31 23:59:59 +Warnings: +Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) +Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) +Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) +Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) +DROP TABLE t1; +# +# End of 10.4 tests +# |