Bug #96580 Using a prepare-stmts with timestampadd function will get an incorrect result
Submitted: 19 Aug 2019 2:54 Modified: 19 Aug 2019 5:07
Reporter: Haixing Weng (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:MySQL-5.7.23, 5.7.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: Prepare statements, timestampadd function

[19 Aug 2019 2:54] Haixing Weng
Description:
Using a prepare-stmts with timestampadd function will get an incorrect result

How to repeat:
mysql> prepare st2 from "select timestampadd(day, ?, ?)"; set @delta=-3; set @ts="2019-08-06 10:00:00"; execute st2 using @delta, @ts;
Query OK, 0 rows affected (2 min 28.69 sec)
Statement prepared

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-------------------------+
| timestampadd(day, ?, ?) |
+-------------------------+
| NULL                    |
+-------------------------+
1 row in set, 1 warning (9.00 sec)

While exchage order of set list, the answer is right,

mysql> prepare st2 from "select timestampadd(day, ?, ?)"; set @delta=-3; set @ts="2019-08-06 10:00:00"; execute st2 using @ts, @delta;
Query OK, 0 rows affected (6.00 sec)
Statement prepared

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-------------------------+
| timestampadd(day, ?, ?) |
+-------------------------+
| 2019-08-03 10:00:00     |
+-------------------------+
1 row in set (0.68 sec)

And this is only happened on MySQL 5.7.*. In 8.0 or 5.6 it is OK when you use timestampadd function in prepare statements.

Suggested fix:
The bug is caused by the wrong order of lex->paramlist after MySQL parsing query.

When prepare statements executed, MySQL Server set param value via the function insert_params_from_vars (5.7, In 5.6, it is insert_params_from_vars_with_log)

We can use gdb to trace the stack in SQL "prepare st2 from "select timestampadd(day, ?, ?)"; set @delta=-3; set @ts="2019-08-06 10:00:00"; execute st2 using @delta, @ts;"

Breakpoint 2, Prepared_statement::insert_params_from_vars (this=0x7f7518ae8590, varnames=..., query=0x7f757c1ee410) a
1105	  Item_param **begin= param_array;
(gdb) p (LEX_STRING*)varnames->first->info
$38 = (LEX_STRING *) 0x7f7518a2f5f0
(gdb) p *(LEX_STRING*)varnames->first->info
$39 = {str = 0x7f751893cc20 "delta", length = 5}
(gdb) p *(LEX_STRING*)varnames->first->next->info
$40 = {str = 0x7f7518021960 "ts", length = 2}
(gdb) p param_array[0]->pos_in_query
$42 = 28
(gdb) p param_array[1]->pos_in_query
$43 = 25 

We can see that  in varnames (parsed from  "execute st2 using @delta, @ts") the token order is correct ("delata" first, "ts" next)

but in param_array, the Item_param order is wrong terribly, pos 28 in SQL Query represent "ts", while 25 represent "delta". It will cause that MySQL bind value in wrong order.(MySQL find value from a HASH via the string of varnames, and set into param_array[0...n])

===================================

So how to fix it?

The param_array is made by lex->param_list.
In MySQL5.6, the item_param is pushed in lex->param_list with the order of "INPUT token", like 

#2  0x00000000008da1c8 in MYSQLparse (YYTHD=0x286fec0) at /home/hldb/hxdb/upsql5619/upsql-5619/sql/sql_yacc.yy:13432
13432	            if (!($$= item) || lex->param_list.push_back(item))
(gdb) l
13427	            {
13428	              my_error(ER_VIEW_SELECT_VARIABLE, MYF(0));
13429	              MYSQL_YYABORT;
13430	            }
13431	            item= new (thd->mem_root) Item_param((uint) (lip->get_tok_start() - thd->query()));
13432	            if (!($$= item) || lex->param_list.push_back(item))
13433	            {
13434	              my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
13435	              MYSQL_YYABORT;
13436	            }

But in MySQL5.7, lex->param_list is filled when contextualize(...)

The stack is just like:

#0  base_list::push_back (this=0x7f7518ae73f8, info=0x7f7518ae02f0) at /home/hldb/hxdb/upsql-2/sql/sql_list.h:199
#1  0x000000000105346d in List<Item_param>::push_back (this=0x7f7518ae73f8, a=0x7f7518ae02f0) at /home/hldb/hxdb/upsql-2/sql/sql_list.h:545
#2  0x00000000010202ec in Item_param::itemize (this=0x7f7518ae02f0, pc=0x7f757c1ec880, res=0x7f7518a9bce8) at /home/hldb/hxdb/upsql-2/sql/item.cc:3755
#3  0x00000000010e3458 in Item_func::itemize (this=0x7f7518a9bc40, pc=0x7f757c1ec880, res=0x7f7518952588) at /home/hldb/hxdb/upsql-2/sql/item_func.cc:163
#4  0x00000000020ab25e in PTI_expr_with_alias::itemize (this=0x7f75189524f0, pc=0x7f757c1ec880, res=0x7f757c1e9e48) at /home/hldb/hxdb/upsql-2/sql/parse_tree_items.cc:194
#5  0x0000000001f87709 in PT_item_list::contextualize (this=0x7f7518961ab0, pc=0x7f757c1ec880) at /home/hldb/hxdb/upsql-2/sql/parse_tree_helpers.h:70
#6  0x0000000001f8ac2d in PT_select_item_list::contextualize (this=0x7f7518961ab0, pc=0x7f757c1ec880) at /home/hldb/hxdb/upsql-2/sql/parse_tree_nodes.h:179
#7  0x0000000001f92ca8 in PT_select_options_and_item_list::contextualize (this=0x7f75189f4b50, pc=0x7f757c1ec880) at /home/hldb/hxdb/upsql-2/sql/parse_tree_nodes.h:2164
#8  0x0000000001f92f83 in PT_select_part2::contextualize (this=0x7f7518aa3de0, pc=0x7f757c1ec880) at /home/hldb/hxdb/upsql-2/sql/parse_tree_nodes.h:2234
#9  0x0000000001f93c15 in PT_select_init2::contextualize (this=0x7f75189d7b20, pc=0x7f757c1ec880) at /home/hldb/hxdb/upsql-2/sql/parse_tree_nodes.h:2359
#10 0x0000000001f93eeb in PT_select::contextualize (this=0x7f75189e75d0, pc=0x7f757c1ec880) at /home/hldb/hxdb/upsql-2/sql/parse_tree_nodes.h:2396
#11 0x0000000001f2bc61 in MYSQLparse (YYTHD=0x7f75180101a0) at /home/hldb/hxdb/upsql-2/sql/sql_yacc.yy:1705
#12 0x0000000001cb6990 in parse_sql (thd=0x7f75180101a0, parser_state=0x7f757c1ee270, creation_ctx=0x0) at /home/hldb/hxdb/upsql-2/sql/sql_parse.cc:7223
#13 0x0000000001d1a807 in Prepared_statement::prepare (this=0x7f7518ae8590, query_str=0x7f75189b9070 "select timestampadd(day, ?, ?)", query_length=30) at /home/hldb/hxdb/upsql-2/sql/sql_prepare.cc:3275

And  when MySQL parse timestampadd function , the yacc  process text via:

        | TIMESTAMP_ADD '(' interval_time_stamp ',' expr ',' expr ')'
          {
            $$= NEW_PTN Item_date_add_interval(@$, $7, $5, $3, 0);
          }

You can see that yacc will contextualize the second param when prepare.

=============================================================
So my suggestion is that change the order of input param in construnct function of 
Item_date_add_interval.

I can submit a patch to fix it when I should be at leisure.
[19 Aug 2019 5:07] Umesh Shastry
Hello Haixing Weng,

Thank you for the report and feedback.
Observed the issue with 5.7.27 build.

regards,
Umesh
[19 Aug 2019 5:09] Umesh Shastry
- 5.7.27

mysql> prepare st2 from "select timestampadd(day, ?, ?)"; set @delta=-3; set @ts="2019-08-06 10:00:00"; execute st2 using @delta, @ts;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-------------------------+
| timestampadd(day, ?, ?) |
+-------------------------+
| NULL                    |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------+
| Level   | Code | Message                        |
+---------+------+--------------------------------+
| Warning | 1292 | Incorrect datetime value: '-3' |
+---------+------+--------------------------------+
1 row in set (0.00 sec)

- 5.6.45, 8.0.17 seems to be fine

timestampadd(day, ?, ?)
2019-08-03 10:00:00