MDEV-16152 Expressions with INTERVAL return bad results in some cases

4 files changed, 103 insertions, 0 deletions

diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result
index 2772f850ce9..4f7000be7b2 100644
--- a/mysql-test/main/func_time.result
+++ b/mysql-test/main/func_time.result
@@ -3484,3 +3484,30 @@ t1	CREATE TABLE `t1` (
   `c5` varchar(100) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1;
+#
+# MDEV-16152 Expressions with INTERVAL return bad results in some cases
+#
+SELECT TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1,
+-INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2;
+c1	c2
+1991-01-01 10:20:30	1991-01-01 10:20:30
+SELECT TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1,
+INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2,
++INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3;
+c1	c2	c3
+2011-01-01 10:20:30	2011-01-01 10:20:30	2011-01-01 10:20:30
+EXPLAIN EXTENDED SELECT
+TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1,
+-INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select TIMESTAMP'2001-01-01 10:20:30' - interval '10' year AS `c1`,TIMESTAMP'2001-01-01 10:20:30' - interval '10' year AS `c2`
+EXPLAIN EXTENDED SELECT
+TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1,
+INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2,
++INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select TIMESTAMP'2001-01-01 10:20:30' + interval '10' year AS `c1`,TIMESTAMP'2001-01-01 10:20:30' + interval '10' year AS `c2`,TIMESTAMP'2001-01-01 10:20:30' + interval '10' year AS `c3`
diff --git a/mysql-test/main/func_time.test b/mysql-test/main/func_time.test
index 5417cb20a92..b3a114a7a03 100644
--- a/mysql-test/main/func_time.test
+++ b/mysql-test/main/func_time.test
@@ -2057,3 +2057,23 @@ EXECUTE IMMEDIATE
 USING NULL, '10', 10, 10.0, 10e0, TIME'10:20:30';
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-16152 Expressions with INTERVAL return bad results in some cases
+--echo #
+
+SELECT TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1,
+      -INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2;
+
+SELECT TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1,
+       INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2,
+      +INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3;
+
+EXPLAIN EXTENDED SELECT
+  TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1,
+  -INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2;
+
+EXPLAIN EXTENDED SELECT
+  TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1,
+  INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2,
+  +INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3;
diff --git a/mysql-test/suite/compat/oracle/r/func_time.result b/mysql-test/suite/compat/oracle/r/func_time.result
new file mode 100644
index 00000000000..063163400ca
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/func_time.result
@@ -0,0 +1,31 @@
+SET sql_mode=ORACLE;
+#
+# Start of 10.3 tests
+#
+#
+# MDEV-16152 Expressions with INTERVAL return bad results in some cases
+#
+SELECT TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1,
+-INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2;
+c1	c2
+1991-01-01 10:20:30	1991-01-01 10:20:30
+SELECT TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1,
+INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2,
++INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3;
+c1	c2	c3
+2011-01-01 10:20:30	2011-01-01 10:20:30	2011-01-01 10:20:30
+EXPLAIN EXTENDED SELECT
+TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1,
+-INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select TIMESTAMP'2001-01-01 10:20:30' - interval '10' year AS "c1",TIMESTAMP'2001-01-01 10:20:30' - interval '10' year AS "c2"
+EXPLAIN EXTENDED SELECT
+TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1,
+INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2,
++INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select TIMESTAMP'2001-01-01 10:20:30' + interval '10' year AS "c1",TIMESTAMP'2001-01-01 10:20:30' + interval '10' year AS "c2",TIMESTAMP'2001-01-01 10:20:30' + interval '10' year AS "c3"
diff --git a/mysql-test/suite/compat/oracle/t/func_time.test b/mysql-test/suite/compat/oracle/t/func_time.test
new file mode 100644
index 00000000000..c1174f7f395
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/func_time.test
@@ -0,0 +1,25 @@
+SET sql_mode=ORACLE;
+
+--echo #
+--echo # Start of 10.3 tests
+--echo #
+
+--echo #
+--echo # MDEV-16152 Expressions with INTERVAL return bad results in some cases
+--echo #
+
+SELECT TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1,
+      -INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2;
+
+SELECT TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1,
+       INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2,
+      +INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3;
+
+EXPLAIN EXTENDED SELECT
+  TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1,
+  -INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2;
+
+EXPLAIN EXTENDED SELECT
+  TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1,
+  INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2,
+  +INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3;