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.
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.