Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5.9, 10.4(EOL), 10.5
-
Ubuntu 18.04
MariaDB 10.5.9
Description
I used my fuzzing tool to test Mariadb , and found a bug that can result in an abortion.
Mariadb installation：
1) cd mariadb-10.5.9
2) mkdir build; cd build
3) cmake -DWITH_ASAN=ON -DWITH_ASAN_SCOPE=ON -DWITH_DEBUG=ON ../
4) make -j8 && sudo make install
How to Repeat:
export ASAN_OPTIONS=detect_leaks=0
/usr/local/mysql/bin/mysqld_safe &
/usr/local/mysql/bin/mysql -uroot -p123456(your password)
MariaDB> drop database if exists test_db;
MariaDB> create database test_db;
MariaDB> source fuzz.sql;
I have simplified the content of fuzz.sql, and I hope fuzz.sql can help you reproduce the bug and fix it. In addition, I attach the abortion report (which has its stack trace).
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
- links to
Activity
The CTE is not necessary: if I change it into derived table:
SELECT 1 |
FROM ( |
SELECT i1, a |
FROM (SELECT i1, count(1) AS a FROM t1 GROUP BY t1.i1) as not_cte |
) dt WHERE a > i1; |
the above testcase still crashes.
But if I disable Condition Pushdown from Having :
set optimizer_switch='condition_pushdown_from_having=off'; |
then the crash goes away.
The crash is in Item_field::used_tables() at this line:
if (field->table->const_table)
|
because field==NULL:
(gdb) p this->fixed
|
$2 = false
|
(gdb) p this->field
|
$4 = (Field *) 0x0
|
How do we end up with this?
Re-running the query. I can see that fix_fields() call is made for the GROUP
BY expression:
(gdb) wher
|
#0 Item_field::set_field (this=0x7fff90018278, field_par=0x7fff9001ea88) at /home/psergey/dev-git2/10.5-cp2/sql/item.cc:3055
|
#1 0x00005555561fc910 in Item_field::fix_fields (this=0x7fff90018278, thd=0x7fff90000d78, reference=0x7fff900183c8) at /home/psergey/dev-git2/10.5-cp2/sql/item.cc:5988
|
#2 0x0000555555d48c11 in Item::fix_fields_if_needed (this=0x7fff90018278, thd=0x7fff90000d78, ref=0x7fff900183c8) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:988
|
#3 0x0000555555d48c3f in Item::fix_fields_if_needed_for_scalar (this=0x7fff90018278, thd=0x7fff90000d78, ref=0x7fff900183c8) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:992
|
#4 0x0000555555f05e7d in Item::fix_fields_if_needed_for_order_by (this=0x7fff90018278, thd=0x7fff90000d78, ref=0x7fff900183c8) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:1000
|
#5 0x0000555555ef5761 in find_order_in_list (thd=0x7fff90000d78, ref_pointer_array=..., tables=0x7fff90017b80, order=0x7fff900183b8, fields=..., all_fields=..., is_group_field=true, add_to_all_fields=true, from_window_spec=false) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:24581
|
#6 0x0000555555ef5e44 in setup_group (thd=0x7fff90000d78, ref_pointer_array=..., tables=0x7fff90017b80, fields=..., all_fields=..., order=0x7fff900183b8, hidden_group_fields=0x7fff9001ba0f, from_window_spec=false) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:24786
|
#7 0x0000555555eaf83a in setup_without_group (thd=0x7fff90000d78, ref_pointer_array=..., tables=0x7fff90017b80, leaves=..., fields=..., all_fields=..., conds=0x7fff9001bb38, order=0x0, group=0x7fff900183b8, win_specs=..., win_funcs=..., hidden_group_fields=0x7fff9001ba0f, reserved=0x7fff90017674) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:751
|
#8 0x0000555555eb25d2 in JOIN::prepare (this=0x7fff9001b728, tables_init=0x7fff90017b80, conds_init=0x0, og_num=1, order_init=0x0, skip_order_by=false, group_init=0x7fff900183b8, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff90017368, unit_arg=0x7fff90018420) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:1285
|
#9 0x0000555555f87805 in st_select_lex_unit::prepare_join (this=0x7fff90018420, thd_arg=0x7fff90000d78, sl=0x7fff90017368, tmp_result=0x7fff9001b648, additional_options=0, is_union_select=false) at /home/psergey/dev-git2/10.5-cp2/sql/sql_union.cc:1088
|
#10 0x0000555555f89091 in st_select_lex_unit::prepare (this=0x7fff90018420, derived_arg=0x7fff90018c60, sel_result=0x7fff9001b648, additional_options=0) at /home/psergey/dev-git2/10.5-cp2/sql/sql_union.cc:1481
|
#11 0x0000555555e0b70e in mysql_derived_prepare (thd=0x7fff90000d78, lex=0x7fff90004e68, derived=0x7fff90018c60) at /home/psergey/dev-git2/10.5-cp2/sql/sql_derived.cc:839
|
#12 0x0000555555e09cfd in mysql_handle_single_derived (lex=0x7fff90004e68, derived=0x7fff90018c60, phases=2) at /home/psergey/dev-git2/10.5-cp2/sql/sql_derived.cc:200
|
#13 0x0000555555fbe1ee in TABLE_LIST::handle_derived (this=0x7fff90018c60, lex=0x7fff90004e68, phases=2) at /home/psergey/dev-git2/10.5-cp2/sql/table.cc:9121
|
#14 0x0000555555e25dd4 in LEX::handle_list_of_derived (this=0x7fff90004e68, table_list=0x7fff90018c60, phases=2) at /home/psergey/dev-git2/10.5-cp2/sql/sql_lex.h:4435
|
#15 0x0000555555e33fb8 in st_select_lex::handle_derived (this=0x7fff90016c30, lex=0x7fff90004e68, phases=2) at /home/psergey/dev-git2/10.5-cp2/sql/sql_lex.cc:4927
|
#16 0x0000555555fbe1b4 in TABLE_LIST::handle_derived (this=0x7fff90019b90, lex=0x7fff90004e68, phases=2) at /home/psergey/dev-git2/10.5-cp2/sql/table.cc:9118
|
#17 0x0000555555e25dd4 in LEX::handle_list_of_derived (this=0x7fff90004e68, table_list=0x7fff90019b90, phases=2) at /home/psergey/dev-git2/10.5-cp2/sql/sql_lex.h:4435
|
#18 0x0000555555e33fb8 in st_select_lex::handle_derived (this=0x7fff900166f8, lex=0x7fff90004e68, phases=2) at /home/psergey/dev-git2/10.5-cp2/sql/sql_lex.cc:4927
|
#19 0x0000555555eb1f08 in JOIN::prepare (this=0x7fff9001b0d0, tables_init=0x7fff90019b90, conds_init=0x7fff9001a500, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff900166f8, unit_arg=0x7fff90004f30) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:1195
|
#20 0x0000555555ebf28f in mysql_select (thd=0x7fff90000d78, tables=0x7fff90019b90, fields=..., conds=0x7fff9001a500, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff9001b0a8, unit=0x7fff90004f30, select_lex=0x7fff900166f8) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:4738
|
#21 0x0000555555eaec88 in handle_select (thd=0x7fff90000d78, lex=0x7fff90004e68, result=0x7fff9001b0a8, setup_tables_done_option=0) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:443
|
#22 0x0000555555e71d80 in execute_sqlcom_select (thd=0x7fff90000d78, all_tables=0x7fff90019b90) at /home/psergey/dev-git2/10.5-cp2/sql/sql_parse.cc:6313
|
#23 0x0000555555e68fcc in mysql_execute_command (thd=0x7fff90000d78) at /home/psergey/dev-git2/10.5-cp2/sql/sql_parse.cc:4009
|
#24 0x0000555555e76ba5 in mysql_parse (thd=0x7fff90000d78, rawbuf=0x7fff900165b0 "SELECT 1 FROM ( SELECT i1, a FROM (SELECT i1, count(1) AS a FROM t1 GROUP BY t1.i1) as not_cte ) dt WHERE a > i1", length=118, parser_state=0x7ffff4134510, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git2/10.5-cp2/sql/sql_parse.cc:8099
|
(gdb) p this
|
$132 = (Item_field * const) 0x7fff90018278
|
Then, ORDER::item pointer is replaced by this code in find_order_in_list():
order->item= &ref_pointer_array[counter];
|
order->in_field_list=1;
|
Thread 19 "mysqld" hit Hardware watchpoint 9: -location order->item
|
Old value = (Item **) 0x7fff900183c8
|
New value = (Item **) 0x7fff9001bcb0
|
find_order_in_list (thd=0x7fff90000d78, ref_pointer_array=..., tables=0x7fff90017b80, order=0x7fff900183b8, fields=..., all_fields=..., is_group_field=true, add_to_all_fields=true, from_window_spec=false) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:24619
|
(gdb) p order->item[0]
|
$133 = (Item_field *) 0x7fff900177f8
|
(gdb) p order->item[0]->fixed
|
$134 = true
|
the stack trace is same as above, starting from frame #5.
But then, I see that Pushdown-from-Having optimization "cleans up" the new item:
(gdb) wher
|
#0 Item_fixed_hybrid::cleanup (this=0x7fff900177f8) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:2778
|
#1 0x000055555620afe4 in Item_result_field::cleanup (this=0x7fff900177f8) at /home/psergey/dev-git2/10.5-cp2/sql/item.cc:10516
|
#2 0x00005555561eb0f4 in Item_ident::cleanup (this=0x7fff900177f8) at /home/psergey/dev-git2/10.5-cp2/sql/item.cc:689
|
#3 0x00005555561fd070 in Item_field::cleanup (this=0x7fff900177f8) at /home/psergey/dev-git2/10.5-cp2/sql/item.cc:6115
|
#4 0x00005555561eaa0c in Item::cleanup_processor (this=0x7fff900177f8, arg=0x1) at /home/psergey/dev-git2/10.5-cp2/sql/item.cc:562
|
#5 0x000055555620b880 in Item::cleanup_excluding_immutables_processor (this=0x7fff900177f8, arg=0x1) at /home/psergey/dev-git2/10.5-cp2/sql/item.cc:10704
|
#6 0x0000555555d3c7b8 in Item::walk (this=0x7fff900177f8, processor=(bool (Item::*)(Item * const, void *)) 0x55555620b838 <Item::cleanup_excluding_immutables_processor(void*)>, walk_subquery=false, arg=0x1) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:1908
|
#7 0x000055555620ece6 in Item_direct_view_ref::walk (this=0x7fff90069dd0, processor=(bool (Item::*)(Item * const, void *)) 0x55555620b838 <Item::cleanup_excluding_immutables_processor(void*)>, walk_subquery=false, arg=0x1) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:5872
|
#8 0x0000555555dd0d47 in Item_args::walk_args (this=0x7fff9006a0e8, processor=(bool (Item::*)(Item * const, void *)) 0x55555620b838 <Item::cleanup_excluding_immutables_processor(void*)>, walk_subquery=false, arg=0x1) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:2609
|
#9 0x0000555555dd141f in Item_func_or_sum::walk (this=0x7fff9006a058, processor=(bool (Item::*)(Item * const, void *)) 0x55555620b838 <Item::cleanup_excluding_immutables_processor(void*)>, walk_subquery=false, arg=0x1) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:5270
|
#10 0x0000555555e484de in st_select_lex::pushdown_from_having_into_where (this=0x7fff90017368, thd=0x7fff90000d78, having=0x0) at /home/psergey/dev-git2/10.5-cp2/sql/sql_lex.cc:11011
|
#11 0x0000555555eb5660 in JOIN::optimize_inner (this=0x7fff9001b728) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:2065
|
#12 0x0000555555eb3eaf in JOIN::optimize (this=0x7fff9001b728) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:1666
|
#13 0x0000555555e0c39f in mysql_derived_optimize (thd=0x7fff90000d78, lex=0x7fff90004e68, derived=0x7fff90018c60) at /home/psergey/dev-git2/10.5-cp2/sql/sql_derived.cc:1048
|
#14 0x0000555555e09cfd in mysql_handle_single_derived (lex=0x7fff90004e68, derived=0x7fff90018c60, phases=4) at /home/psergey/dev-git2/10.5-cp2/sql/sql_derived.cc:200
|
#15 0x0000555555eb5a25 in JOIN::optimize_inner (this=0x7fff9001b0d0) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:2126
|
#16 0x0000555555eb3eaf in JOIN::optimize (this=0x7fff9001b0d0) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:1666
|
#17 0x0000555555ebf2d2 in mysql_select (thd=0x7fff90000d78, tables=0x7fff90019b90, fields=..., conds=0x7fff9001a500, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff9001b0a8, unit=0x7fff90004f30, select_lex=0x7fff900166f8) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:4747
|
#18 0x0000555555eaec88 in handle_select (thd=0x7fff90000d78, lex=0x7fff90004e68, result=0x7fff9001b0a8, setup_tables_done_option=0) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:443
|
#19 0x0000555555e71d80 in execute_sqlcom_select (thd=0x7fff90000d78, all_tables=0x7fff90019b90) at /home/psergey/dev-git2/10.5-cp2/sql/sql_parse.cc:6313
|
#20 0x0000555555e68fcc in mysql_execute_command (thd=0x7fff90000d78) at /home/psergey/dev-git2/10.5-cp2/sql/sql_parse.cc:4009
|
#21 0x0000555555e76ba5 in mysql_parse (thd=0x7fff90000d78, rawbuf=0x7fff900165b0 "SELECT 1 FROM ( SELECT i1, a FROM (SELECT i1, count(1) AS a FROM t1 GROUP BY t1.i1) as not_cte ) dt WHERE a > i1", length=118, parser_state=0x7ffff4134510, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git2/10.5-cp2/sql/sql_parse.cc:8099
|
and then we crash:
Thread 19 "mysqld" received signal SIGSEGV, Segmentation fault.
|
0x00005555561f469f in Item_field::used_tables (this=0x7fff900177f8) at /home/psergey/dev-git2/10.5-cp2/sql/item.cc:3408
|
The condition it is trying to push is: "a > t1.i1".
The select where it is pushing it is: (SELECT i1, count(1) AS a FROM t1 GROUP BY t1.i1)
Nothing should be pushable.
The call to item->walk(&Item::cleanup_excluding_immutables_processor ...) seems to be the issue.
The problem can manifest itself when we have an item of the following structure:
Item_direct_view_ref(Item_ref(Item_sum_func(Item_basic_constant))).
Due to the following implementation of the function Item_direct_view_ref::const_item()
|
bool const_item() const { return used_tables() == 0; }
|
calls of the method const_item() return true for such items and this is, of course, not correct,
Note that calls of const_item() for items of the form Item_ref(Item_sum_func(Item_basic_constant))
correctly return false.
This is a legacy bug and it's present in 10.2 as well.
Let's try to construct a test case for the bug in 10.2 that would catch the bug in 10.2 where there is no pushdown from HAVING to WHERE. However in 10.2 we have pushdown of conditions into HAVING of materialized tables / views that also builds items of the form Item_direct_view_ref(Item_ref(Item_sum_func(Item_basic_constant))) if the pushdown happens to be from WHERE of mergeable derived tables / views.
Here is a test case that uses such pushdown:
create table t1 (a int); |
insert into t1 values (3), (7), (1), (3), (7), (7), (3); |
create view v1 as select a, sum(1) as f, sum(1) as g from t1 group by a; |
select * from (select * from v1) as dt where a=f and a=g; |
The result set from the query is:
MariaDB [test]> select * from (select * from v1) as dt where a=f and a=g;
|
+------+------+------+
|
| a | f | g |
|
+------+------+------+
|
| 3 | 3 | 3 |
|
+------+------+------+
|
1 row in set
|
though select from v1 returns
MariaDB [test]> select * from v1;
|
+------+------+------+
|
| a | f | g |
|
+------+------+------+
|
| 1 | 1 | 1 |
|
| 3 | 3 | 3 |
|
| 7 | 3 | 3 |
|
+------+------+------+
|
3 rows in set
|
If we manually push the condition a=f and a=g into v1 we have a proper result set:
MariaDB [test]> select * from (select a, sum(1) as f, sum(1) as g from t1 group by a having a=f and a=g) as dt;
|
+------+------+------+
|
| a | f | g |
|
+------+------+------+
|
| 1 | 1 | 1 |
|
| 3 | 3 | 3 |
|
+------+------+------+
|
2 rows in set
|
It's interesting that for a similar test case that uses MIN(1) instead of SUM(1) in the view
create table t1 (a int); |
insert into t1 values (3), (7), (1), (3), (7), (7), (3); |
create view v2 as select a, min(1) as f, min(1) as g from t1 group by a; |
select * from (select * from v2) as dt where a=f and a=g; |
not only the query returns an unexpected empty result
MariaDB [test]> select * from (select * from v2) as dt where a=f and a=g;
|
Empty set
|
but EXPLAIN also returns an unexpected output
MariaDB [test]> explain extended select * from (select * from v2) as dt where a=f and a=g;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible HAVING |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|
2 rows in set, 1 warning
|
|
|
MariaDB [test]> show warnings;
|
+-------+------+---------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+---------------------------------------------------------------------+
|
| Note | 1003 | select NULL AS `a`,NULL AS `f`,NULL AS `g` from `test`.`v2` where 0 |
|
+-------+------+---------------------------------------------------------------------+
|
1 row in set
|
This is a legacy bug, so I removed the marker "regression".
A patch for this bug was pushed into 10.2. It has to be merged upstream as it is.
An additional test case for 10.4 will be added after the patch has been merged into 10.4.
Thank you for the report!
Repeatable on 10.4,10,5, no crash on earlier versions.
10.4 8c73fab7f7622b5806295
210510 15:21:46 [ERROR] mysqld got signal 11 ;Server version: 10.4.19-MariaDB-debug-logsigaction.c:0(__restore_rt)[0x7fd5b83733c0]sql/item.cc:3333(Item_field::used_tables() const)[0x559e7c6eefff]sql/sql_select.cc:14074(update_depend_map_for_order(JOIN*, st_order*))[0x559e7bf97a03]sql/sql_select.cc:14182(remove_const(JOIN*, st_order*, Item*, bool, bool*))[0x559e7bf98451]sql/sql_select.cc:2294(JOIN::optimize_inner())[0x559e7bf394e9]sql/sql_select.cc:1658(JOIN::optimize())[0x559e7bf327bc]sql/sql_derived.cc:1048(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0x559e7bdac052]sql/sql_derived.cc:200(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x559e7bda640c]sql/sql_select.cc:2148(JOIN::optimize_inner())[0x559e7bf37e3c]sql/sql_select.cc:1658(JOIN::optimize())[0x559e7bf327bc]sql/sql_select.cc:4731(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x559e7bf53491]sql/sql_select.cc:436(handle_select(THD*, LEX*, select_result*, unsigned long))[0x559e7bf24896]sql/sql_parse.cc:6449(execute_sqlcom_select(THD*, TABLE_LIST*))[0x559e7be93b27]sql/sql_parse.cc:3968(mysql_execute_command(THD*))[0x559e7be8143d]sql/sql_parse.cc:7995(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x559e7be9cfcb]sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x559e7be736e1]sql/sql_parse.cc:1373(do_command(THD*))[0x559e7be7018e]sql/sql_connect.cc:1412(do_handle_one_connection(CONNECT*))[0x559e7c266e10]sql/sql_connect.cc:1317(handle_one_connection)[0x559e7c2666b4]perfschema/pfs.cc:1871(pfs_spawn_thread)[0x559e7d93e2f3]nptl/pthread_create.c:478(start_thread)[0x7fd5b8367609]x86_64/clone.S:97(__GI___clone)[0x7fd5b7f3b293]Query (0x62b0000a1290): WITH cte AS (SELECT i1, count(1) AS a FROM t1 GROUP BY t1.i1)SELECT 1 FROM (SELECT i1, a FROM cte) dt WHERE a > i1