diff options
-rw-r--r-- | mysql-test/main/func_time.result | 27 | ||||
-rw-r--r-- | mysql-test/main/func_time.test | 20 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/r/func_time.result | 31 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/t/func_time.test | 25 | ||||
-rw-r--r-- | sql/sql_yacc.yy | 26 | ||||
-rw-r--r-- | sql/sql_yacc_ora.yy | 26 |
6 files changed, 141 insertions, 14 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; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 9e6d53883ad..157bca1def2 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -9776,6 +9776,25 @@ bit_expr: if (unlikely($$ == NULL)) MYSQL_YYABORT; } + | INTERVAL_SYM expr interval '+' expr %prec INTERVAL_SYM + /* we cannot put interval before - */ + { + $$= new (thd->mem_root) Item_date_add_interval(thd, $5, $2, $3, 0); + if (unlikely($$ == NULL)) + MYSQL_YYABORT; + } + | '+' INTERVAL_SYM expr interval '+' expr %prec NEG + { + $$= new (thd->mem_root) Item_date_add_interval(thd, $6, $3, $4, 0); + if (unlikely($$ == NULL)) + MYSQL_YYABORT; + } + | '-' INTERVAL_SYM expr interval '+' expr %prec NEG + { + $$= new (thd->mem_root) Item_date_add_interval(thd, $6, $3, $4, 1); + if (unlikely($$ == NULL)) + MYSQL_YYABORT; + } | bit_expr '*' bit_expr %prec '*' { $$= new (thd->mem_root) Item_func_mul(thd, $1, $3); @@ -10133,13 +10152,6 @@ simple_expr: if (unlikely($$ == NULL)) MYSQL_YYABORT; } - | INTERVAL_SYM expr interval '+' expr %prec INTERVAL_SYM - /* we cannot put interval before - */ - { - $$= new (thd->mem_root) Item_date_add_interval(thd, $5, $2, $3, 0); - if (unlikely($$ == NULL)) - MYSQL_YYABORT; - } ; function_call_keyword_timestamp: diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index a3e76c6b80a..18e1e5e9771 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -9526,6 +9526,25 @@ bit_expr: if (unlikely($$ == NULL)) MYSQL_YYABORT; } + | INTERVAL_SYM expr interval '+' expr %prec INTERVAL_SYM + /* we cannot put interval before - */ + { + $$= new (thd->mem_root) Item_date_add_interval(thd, $5, $2, $3, 0); + if (unlikely($$ == NULL)) + MYSQL_YYABORT; + } + | '+' INTERVAL_SYM expr interval '+' expr %prec NEG + { + $$= new (thd->mem_root) Item_date_add_interval(thd, $6, $3, $4, 0); + if (unlikely($$ == NULL)) + MYSQL_YYABORT; + } + | '-' INTERVAL_SYM expr interval '+' expr %prec NEG + { + $$= new (thd->mem_root) Item_date_add_interval(thd, $6, $3, $4, 1); + if (unlikely($$ == NULL)) + MYSQL_YYABORT; + } | bit_expr '*' bit_expr %prec '*' { $$= new (thd->mem_root) Item_func_mul(thd, $1, $3); @@ -9935,13 +9954,6 @@ simple_expr: if (unlikely($$ == NULL)) MYSQL_YYABORT; } - | INTERVAL_SYM expr interval '+' expr %prec INTERVAL_SYM - /* we cannot put interval before - */ - { - $$= new (thd->mem_root) Item_date_add_interval(thd, $5, $2, $3, 0); - if (unlikely($$ == NULL)) - MYSQL_YYABORT; - } ; /* |