MDEV-20025: ADD_MONTHS() Oracle function

Author: woqutech

5 files changed, 142 insertions, 3 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;
diff --git a/mysql-test/suite/compat/oracle/t/func_add_months.test b/mysql-test/suite/compat/oracle/t/func_add_months.test
new file mode 100644
index 00000000000..ca9391ef824
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/func_add_months.test
@@ -0,0 +1,38 @@
+--echo 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');
+
+# some normal case
+SELECT c1, ADD_MONTHS(c2, 2), ADD_MONTHS(c3, 2), ADD_MONTHS(c5, 2) FROM t1;
+SELECT c1, ADD_MONTHS(c2, 15), ADD_MONTHS(c3, 200), ADD_MONTHS(c5, 2000) FROM t1;
+SELECT c1, ADD_MONTHS(c2, 0), ADD_MONTHS(c3, -200), ADD_MONTHS(c5, -2) FROM t1;
+SELECT c1, ADD_MONTHS(c2, -15), ADD_MONTHS(c3, -111), ADD_MONTHS(c5, 2) FROM t1;
+
+# for time type, it will be overflow
+SELECT ADD_MONTHS(c4, 11) FROM t1 WHERE c1 = 1;
+
+UPDATE t1 SET c2=ADD_MONTHS(c2, 2);
+SELECT c2 FROM t1;
+
+EXPLAIN EXTENDED SELECT c1, ADD_MONTHS(c2, -15) FROM t1 WHERE c1 = 1;
+
+# string type can be convert to datetime type
+SELECT ADD_MONTHS("2000-10-10", 12);
+SELECT ADD_MONTHS("2000:10:10", 12);
+
+# number type can not be convert datetime type
+SELECT ADD_MONTHS(2000, 12);
+
+# last day of the month
+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);
+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);
+
+DROP TABLE t1;
diff --git a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
index 2f259ccae12..91ae3cb40e1 100644
--- a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
+++ b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
@@ -8,5 +8,5 @@ SELECT 1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1
 ####################################
 SELECT event_name, digest, digest_text, sql_text FROM events_statements_history_long;
 event_name	digest	digest_text	sql_text
-statement/sql/select	bb82f2829bcdfd9ac1e53f7b27829d36	SELECT ? + ? + 	SELECT ...
-statement/sql/truncate	fd6a2d48e5fda6a3f990cb8810136546	TRUNCATE TABLE 	truncat...
+statement/sql/select	cf4e66f3ef1e0c9905538c220053cbda	SELECT ? + ? + 	SELECT ...
+statement/sql/truncate	f874a3c85ab326928c042dbd544916f9	TRUNCATE TABLE 	truncat...
diff --git a/sql/lex.h b/sql/lex.h
index e945ec6a8d0..20e5e405374 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -748,6 +748,7 @@ static SYMBOL symbols[] = {
 
 static SYMBOL sql_functions[] = {
   { "ADDDATE",		SYM(ADDDATE_SYM)},
+  { "ADD_MONTHS",	SYM(ADD_MONTHS_SYM)},
   { "BIT_AND",		SYM(BIT_AND)},
   { "BIT_OR",		SYM(BIT_OR)},
   { "BIT_XOR",		SYM(BIT_XOR)},
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 3471b99aec7..931e5175f48 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -749,6 +749,7 @@ End SQL_MODE_ORACLE_SPECIFIC */
 %token  <kwd>  ACTION                        /* SQL-2003-N */
 %token  <kwd>  ADMIN_SYM                     /* SQL-2003-N */
 %token  <kwd>  ADDDATE_SYM                   /* MYSQL-FUNC */
+%token  <kwd>  ADD_MONTHS_SYM                /* Oracle FUNC*/
 %token  <kwd>  AFTER_SYM                     /* SQL-2003-N */
 %token  <kwd>  AGAINST
 %token  <kwd>  AGGREGATE_SYM
@@ -10242,7 +10243,14 @@ function_call_keyword:
   discouraged.
 */
 function_call_nonkeyword:
-          ADDDATE_SYM '(' expr ',' expr ')'
+          ADD_MONTHS_SYM '(' expr ',' expr ')'
+          {
+            $$= new (thd->mem_root) Item_date_add_interval(thd, $3, $5,
+                                                           INTERVAL_MONTH, 0);
+            if (unlikely($$ == NULL))
+              MYSQL_YYABORT;
+          }
+        | ADDDATE_SYM '(' expr ',' expr ')'
           {
             $$= new (thd->mem_root) Item_date_add_interval(thd, $3, $5,
                                                              INTERVAL_DAY, 0);
@@ -15870,6 +15878,7 @@ keyword_sp_var_and_label:
           ACTION
         | ACCOUNT_SYM
         | ADDDATE_SYM
+        | ADD_MONTHS_SYM
         | ADMIN_SYM
         | AFTER_SYM
         | AGAINST