Elena Stepanova added a comment - 2016-02-21 21:02 Thanks for the report. The problem appeared in 10.1 tree with this commit: commit 86377d078ef130d3da32c5da31131e519822e139 Author: Monty <monty@mariadb.org> Date: Sun Jul 5 12:39:46 2015 +0300   Fixes done while working on MDEV-4119: Fixed several optimizer issues relatied to GROUP BY: a) Refering to a SELECT column in HAVING sometimes calculated it twice, which caused problems with non determinstic functions b) Removing duplicate fields and constants from GROUP BY was done too late for "using index for group by" optimization to work c) EXPLAIN SELECT ... GROUP BY did wrongly show 'Using filesort' in some cases involving "Using index for group-by" a) was fixed by: - Changed last argument to Item::split_sum_func2() from bool to int to allow more flags - Added flag argument to Item::split_sum_func() to allow on to specify if the item was in the SELECT part - Mark all split_sum_func() calls from SELECT with SPLIT_SUM_SELECT - Changed split_sum_func2() to do nothing if called with an argument that is not a sum function and doesn't include sum functions, if we are not an argument to SELECT. This ensures that in a case like select a*sum(b) as f1 from t1 where a=1 group by c having f1 <= 10; That 'a' in the SELECT part is stored as a reference in the temporary table togeher with sum(b) while the 'a' in having isn't (not needed as 'a' is already a reference to a column in the result) b) was fixed by: - Added an extra remove_const() pass for GROUP BY arguments before make_join_statistics() in case of one table SELECT. This allowes get_best_group_min_max() to optimize things better. c) was fixed by: - Added test for group by optimization in JOIN::exec_inner for select->quick->get_type() == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX item.cc: - Simplifed Item::split_sum_func2() - Split test to make them faster and easier to read - Changed last argument to Item::split_sum_func2() from bool to int to allow more flags - Added flag argument to Item::split_sum_func() to allow on to specify if the item was in the SELECT part - Changed split_sum_func2() to do nothing if called with an argument that is not a sum function and doesn't include sum functions, if we a re not an argument to SELECT. opt_range.cc: - Simplified get_best_group_min_max() by calcuating first how many group_by elements. - Use join->group instead of join->group_list to test if group by, as join->group_list may be NULL if everything was optimized away. sql_select.cc: - Added an extra remove_const() pass for GROUP BY arguments before make_join_statistics() in case of one table SELECT. - Use group instead of group_list to test if group by, as group_list may be NULL if everything was optimized away. - Moved printing of "Error in remove_const" to remove_const() instead of having it in caller. - Simplified some if tests by re-ordering code. - update_depend_map_for_order() and remove_const() fixed to handle the case where make_join_statistics() has not yet been called (join->join_tab is 0 in this case) Stack trace from 10.1 commit fd8e846a3b049903706267d58e6d8e61eea97df8 #3 <signal handler called> #4 0x00005567bf053102 in st_key::actual_rec_per_key (this=0x7f83a1798268, i=4294967295) at /src/10.1/sql/table.cc:7416 #5 0x00005567bf2cac82 in cost_group_min_max (table=0x7f83a143c470, index_info=0x7f83a1798268, used_key_parts=0, group_key_parts=0, range_tree=0x0, index_tree=0x0, quick_prefix_records=0, have_min=false, have_max=false, read_cost=0x7f83b7f7dab0, records=0x7f83b7f7dc10) at /src/10.1/sql/opt_range.cc:13108 #6 0x00005567bf2c9799 in get_best_group_min_max (param=0x7f83b7f7f5e0, tree=0x0, read_time=1.2) at /src/10.1/sql/opt_range.cc:12466 #7 0x00005567bf2b30ae in SQL_SELECT::test_quick_select (this=0x7f83a1427858, thd=0x7f83ae0f7bf0, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false, remove_false_parts_of_where=true) at /src/10.1/sql/opt_range.cc:2561 #8 0x00005567bef9e4e1 in get_quick_record_count (thd=0x7f83ae0f7bf0, select=0x7f83a1427858, table=0x7f83a143c470, keys=0x7f83a1427058, limit=18446744073709551615) at /src/10.1/sql/sql_select.cc:3488 #9 0x00005567befa0956 in make_join_statistics (join=0x7f83a1426850, tables_list=..., keyuse_array=0x7f83a1426b80) at /src/10.1/sql/sql_select.cc:4108 #10 0x00005567bef96cf6 in JOIN::optimize_inner (this=0x7f83a1426850) at /src/10.1/sql/sql_select.cc:1374 #11 0x00005567bef95bbc in JOIN::optimize (this=0x7f83a1426850) at /src/10.1/sql/sql_select.cc:1036 #12 0x00005567bef9e251 in mysql_select (thd=0x7f83ae0f7bf0, rref_pointer_array=0x7f83ae0fbf80, tables=0x7f83a1426088, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x0, group=0x7f83a1426720, having=0x0, proc_param=0x0, select_options=2147748609, result=0x7f83a1426830, unit=0x7f83ae0fb608, select_lex=0x7f83ae0fbd08) at /src/10.1/sql/sql_select.cc:3437 #13 0x00005567bef93d89 in handle_select (thd=0x7f83ae0f7bf0, lex=0x7f83ae0fb540, result=0x7f83a1426830, setup_tables_done_option=0) at /src/10.1/sql/sql_select.cc:384 #14 0x00005567bef64133 in execute_sqlcom_select (thd=0x7f83ae0f7bf0, all_tables=0x7f83a1426088) at /src/10.1/sql/sql_parse.cc:5903 #15 0x00005567bef5a20d in mysql_execute_command (thd=0x7f83ae0f7bf0) at /src/10.1/sql/sql_parse.cc:2962 #16 0x00005567bef6777a in mysql_parse (thd=0x7f83ae0f7bf0, rawbuf=0x7f83a1425e88 "select distinct a from t group by 'a'", length=37, parser_state=0x7f83b7f815e0) at /src/10.1/sql/sql_parse.cc:7303 #17 0x00005567bef56474 in dispatch_command (command=COM_QUERY, thd=0x7f83ae0f7bf0, packet=0x7f83b1b873b1 "select distinct a from t group by 'a'", packet_length=37) at /src/10.1/sql/sql_parse.cc:1488 #18 0x00005567bef551a6 in do_command (thd=0x7f83ae0f7bf0) at /src/10.1/sql/sql_parse.cc:1109 #19 0x00005567bf08a798 in do_handle_one_connection (thd_arg=0x7f83ae0f7bf0) at /src/10.1/sql/sql_connect.cc:1349 #20 0x00005567bf08a4fc in handle_one_connection (arg=0x7f83ae0f7bf0) at /src/10.1/sql/sql_connect.cc:1261 #21 0x00005567bf790cb6 in pfs_spawn_thread (arg=0x7f83ae3835f0) at /src/10.1/storage/perfschema/pfs.cc:1860 #22 0x00007f83b7c040a4 in start_thread () from /lib64/libpthread.so.0 #23 0x00007f83b5d7204d in clone () from /lib64/libc.so.6

Michael Widenius added a comment - 2016-04-28 18:19 Fix pushed into 10.1