diff options
Diffstat (limited to 'mysql-test/suite/compat/oracle/r/func_add_months.result')
| -rw-r--r-- | mysql-test/suite/compat/oracle/r/func_add_months.result | 91 |
1 files changed, 91 insertions, 0 deletions
diff --git a/mysql-test/suite/compat/oracle/r/func_add_months.result b/mysql-test/suite/compat/oracle/r/func_add_months.result new file mode 100644 index 00000000000..0502c20f74e --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/func_add_months.result @@ -0,0 +1,91 @@ +Test for ADD_MONTHS +CREATE TABLE t1(c1 int, c2 datetime, c3 date, c4 time, c5 timestamp); +INSERT INTO t1 VALUES (1, '2011-11-12 12:10:11', '2011-11-12', '12:10:11', '2011-11-12 12:10:11'); +INSERT INTO t1 VALUES (2, '2021-11-12 00:23:12', '2021-11-12', '00:23:12', '2021-11-12 00:23:12'); +INSERT INTO t1 VALUES (3, '2011-01-22 16:45:45', '2011-01-22', '16:45:45', '2011-01-22 16:45:45'); +INSERT INTO t1 VALUES (4, '2031-05-12 04:11:34', '2031-05-12', '04:11:34', '2031-05-12 04:11:34'); +INSERT INTO t1 VALUES (5, '2031-09-02 08:15:22', '2031-09-02', '08:15:22', '2031-09-02 08:15:22'); +INSERT INTO t1 VALUES (6, '0000-09-02 00:00:00', '0000-09-02', '00:00:00', '1980-09-02 00:00:00'); +INSERT INTO t1 VALUES (7, '9999-09-02', '9999-09-02', '00:00:00', '1980-09-02'); +SELECT c1, ADD_MONTHS(c2, 2), ADD_MONTHS(c3, 2), ADD_MONTHS(c5, 2) FROM t1; +c1 ADD_MONTHS(c2, 2) ADD_MONTHS(c3, 2) ADD_MONTHS(c5, 2) +1 2012-01-12 12:10:11 2012-01-12 2012-01-12 12:10:11 +2 2022-01-12 00:23:12 2022-01-12 2022-01-12 00:23:12 +3 2011-03-22 16:45:45 2011-03-22 2011-03-22 16:45:45 +4 2031-07-12 04:11:34 2031-07-12 2031-07-12 04:11:34 +5 2031-11-02 08:15:22 2031-11-02 2031-11-02 08:15:22 +6 0000-11-02 00:00:00 0000-11-02 1980-11-02 00:00:00 +7 9999-11-02 00:00:00 9999-11-02 1980-11-02 00:00:00 +SELECT c1, ADD_MONTHS(c2, 15), ADD_MONTHS(c3, 200), ADD_MONTHS(c5, 2000) FROM t1; +c1 ADD_MONTHS(c2, 15) ADD_MONTHS(c3, 200) ADD_MONTHS(c5, 2000) +1 2013-02-12 12:10:11 2028-07-12 2178-07-12 12:10:11 +2 2023-02-12 00:23:12 2038-07-12 2188-07-12 00:23:12 +3 2012-04-22 16:45:45 2027-09-22 2177-09-22 16:45:45 +4 2032-08-12 04:11:34 2048-01-12 2198-01-12 04:11:34 +5 2032-12-02 08:15:22 2048-05-02 2198-05-02 08:15:22 +6 0001-12-02 00:00:00 0017-05-02 2147-05-02 00:00:00 +7 NULL NULL 2147-05-02 00:00:00 +Warnings: +Warning 1441 Datetime function: datetime field overflow +Warning 1441 Datetime function: datetime field overflow +SELECT c1, ADD_MONTHS(c2, 0), ADD_MONTHS(c3, -200), ADD_MONTHS(c5, -2) FROM t1; +c1 ADD_MONTHS(c2, 0) ADD_MONTHS(c3, -200) ADD_MONTHS(c5, -2) +1 2011-11-12 12:10:11 1995-03-12 2011-09-12 12:10:11 +2 2021-11-12 00:23:12 2005-03-12 2021-09-12 00:23:12 +3 2011-01-22 16:45:45 1994-05-22 2010-11-22 16:45:45 +4 2031-05-12 04:11:34 2014-09-12 2031-03-12 04:11:34 +5 2031-09-02 08:15:22 2015-01-02 2031-07-02 08:15:22 +6 0000-09-02 00:00:00 NULL 1980-07-02 00:00:00 +7 9999-09-02 00:00:00 9983-01-02 1980-07-02 00:00:00 +Warnings: +Warning 1441 Datetime function: datetime field overflow +SELECT c1, ADD_MONTHS(c2, -15), ADD_MONTHS(c3, -111), ADD_MONTHS(c5, 2) FROM t1; +c1 ADD_MONTHS(c2, -15) ADD_MONTHS(c3, -111) ADD_MONTHS(c5, 2) +1 2010-08-12 12:10:11 2002-08-12 2012-01-12 12:10:11 +2 2020-08-12 00:23:12 2012-08-12 2022-01-12 00:23:12 +3 2009-10-22 16:45:45 2001-10-22 2011-03-22 16:45:45 +4 2030-02-12 04:11:34 2022-02-12 2031-07-12 04:11:34 +5 2030-06-02 08:15:22 2022-06-02 2031-11-02 08:15:22 +6 NULL NULL 1980-11-02 00:00:00 +7 9998-06-02 00:00:00 9990-06-02 1980-11-02 00:00:00 +Warnings: +Warning 1441 Datetime function: datetime field overflow +Warning 1441 Datetime function: datetime field overflow +SELECT ADD_MONTHS(c4, 11) FROM t1 WHERE c1 = 1; +ADD_MONTHS(c4, 11) +NULL +Warnings: +Warning 1441 Datetime function: time field overflow +UPDATE t1 SET c2=ADD_MONTHS(c2, 2); +SELECT c2 FROM t1; +c2 +2012-01-12 12:10:11 +2022-01-12 00:23:12 +2011-03-22 16:45:45 +2031-07-12 04:11:34 +2031-11-02 08:15:22 +0000-11-02 00:00:00 +9999-11-02 00:00:00 +EXPLAIN EXTENDED SELECT c1, ADD_MONTHS(c2, -15) FROM t1 WHERE c1 = 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` + interval -15 month AS `ADD_MONTHS(c2, -15)` from `test`.`t1` where `test`.`t1`.`c1` = 1 +SELECT ADD_MONTHS("2000-10-10", 12); +ADD_MONTHS("2000-10-10", 12) +2001-10-10 +SELECT ADD_MONTHS("2000:10:10", 12); +ADD_MONTHS("2000:10:10", 12) +2001-10-10 +SELECT ADD_MONTHS(2000, 12); +ADD_MONTHS(2000, 12) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2000' +SELECT ADD_MONTHS('2011-01-31', 1), ADD_MONTHS('2012-01-31', 1), ADD_MONTHS('2012-01-31', 2), ADD_MONTHS('2012-01-31', 3); +ADD_MONTHS('2011-01-31', 1) ADD_MONTHS('2012-01-31', 1) ADD_MONTHS('2012-01-31', 2) ADD_MONTHS('2012-01-31', 3) +2011-02-28 2012-02-29 2012-03-31 2012-04-30 +SELECT ADD_MONTHS('2011-01-30', 1), ADD_MONTHS('2012-01-30', 1), ADD_MONTHS('2012-01-30', 2), ADD_MONTHS('2012-01-30', 3); +ADD_MONTHS('2011-01-30', 1) ADD_MONTHS('2012-01-30', 1) ADD_MONTHS('2012-01-30', 2) ADD_MONTHS('2012-01-30', 3) +2011-02-28 2012-02-29 2012-03-30 2012-04-30 +DROP TABLE t1; |