MariaDB crashes. The log is attached. A dump of the data is attached.
Expected behaviour: The query returns country name with the highest count of distinct religions.
Reproducibility: On macOS 12.6, case insensitive file system, with a fresh installation of MariaDB 10.7.3 from Homebrew I only get a misleading error message (the db remains responsive to other queries):
2003 - Can't connect to MySQL server on '127.0.0.1' (61 "Connection refused")
Severity: It causes a crash on a select query → marking as Critical.
What I checked:
I renamed `Country` column name to not clash with `country` table. It didn't have any impact.
When I change `LIMIT 1` to `LIMIT 2`, the query works.
MDEV-24210SIGSEGV in QUICK_GROUP_MIN_MAX_SELECT::is_agg_distinct from st_join_table::is_using_agg_loose_index_scan on SELECT FROM a table with subpartitions
FROM (SELECT a FROM t2 GROUP BY a ORDER BY COUNT(DISTINCT b) LIMIT 1) dt
JOIN t1 ON dt.a=t1.b;
eval $query;
DROP TABLES t1, t2;
Must be run with
--valgrind
option for MariaDB bb-10.3-MDEV-30143 built with -DWITH_VALGRIND=ON.
Oleg Smirnov
added a comment - Valgrind complains on particular value best_key->key==1. Adding the following initialization before the condition eliminates the Valgrind error:
sql_select.cc
if (s->quick && best_key && s->quick->index == best_key->key && best_key->key == 1)
{
s->table->quick_key_parts[best_key->key]= 0;
}
Json_writer_object trace_access_scan(thd);
if ((records >= s->found_records || best > s->read_time) && // (1)
!(best_key && best_key->key == MAX_KEY) && // (2)
!(s->quick && best_key && s->quick->index == best_key->key && // (2)
best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2)
!((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) && // (3)
! s->table->covering_keys.is_clear_all() && best_key && !s->quick) &&// (3)
!(s->table->force_index && best_key && !s->quick) && // (4)
!(best_key && s->table->pos_in_table_list->jtbm_subselect)) // (5)
Setting data breakpoint at memory pointed by s->table->quick_key_parts I can see it is bzero'ed along with the whole TABLE structure here:
table.cc
enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share,
const LEX_CSTRING *alias, uint db_stat, uint prgflag,
uint ha_open_flags, TABLE *outparam,
bool is_create_table, List<String> *partitions_to_open)
{
...
bzero((char*) outparam, sizeof(*outparam));
...
}
And no other changes are made to quick_key_parts after that (at least reported by gdb).
Test case to reproduce:
--source include/have_innodb.inc
CREATE TABLE t1 (a varchar(35), b varchar(4)) ENGINE=InnoDB;
INSERT INTO t1 VALUES
('Albania','AXA'),('Australia','AUS'),('American Samoa','AMSA'),('Bahamas','BS');
CREATE TABLE t2 (a varchar(4), b varchar(50), PRIMARY KEY (b,a), KEY (a)) ENGINE=InnoDB;
INSERT INTO t2 VALUES
('BERM','African Methodist Episcopal'),('AUS','Anglican'),('BERM','Anglican'),('BS','Anglican'),('BS','Baptist'),('BS','Methodist');
let query=
SELECT t1.a
FROM (SELECT a FROM t2 GROUP BY a ORDER BY COUNT(DISTINCT b) LIMIT 1) dt
JOIN t1 ON dt.a=t1.b;
eval $query;
DROP TABLES t1, t2;
Must be run with
--valgrind
option for MariaDB bb-10.3- MDEV-30143 built with -DWITH_VALGRIND=ON.
Thank you very much for the report!
I repeated as described on 10.3-10.10, with InnoDB.
--source include/have_innodb.inc
10.3-10.11:
10.3 4e9206736c403206915c
221202 10:49:11 [ERROR] mysqld got signal 11 ;
Server version: 10.3.38-MariaDB-debug-log
sql/signal_handler.cc:231(handle_fatal_signal)[0x55908b22d842]
sigaction.c:0(__restore_rt)[0x7f53a1743420]
sql/sql_select.h:529(st_join_table::is_using_agg_loose_index_scan())[0x55908ac342d7]
sql/sql_select.cc:3521(JOIN::create_postjoin_aggr_table(st_join_table*, List<Item>*, st_order*, bool, bool, bool))[0x55908ab73637]
sql/sql_select.cc:3085(JOIN::make_aggr_tables_info())[0x55908ab6e655]
sql/sql_select.cc:2720(JOIN::optimize_stage2())[0x55908ab69fa6]
sql/sql_select.cc:1535(JOIN::optimize())[0x55908ab5df78]
sql/sql_derived.cc:962(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0x55908a9ef628]
sql/sql_derived.cc:193(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x55908a9ea8cd]
sql/table.cc:8563(TABLE_LIST::handle_derived(LEX*, unsigned int))[0x55908adcfc3b]
sql/sql_lex.h:4032(LEX::handle_list_of_derived(TABLE_LIST*, unsigned int))[0x55908aa2ff04]
sql/sql_lex.cc:4184(st_select_lex::handle_derived(LEX*, unsigned int))[0x55908aa50e73]
sql/sql_select.cc:2050(JOIN::optimize_stage2())[0x55908ab632de]
sql/sql_select.cc:2026(JOIN::optimize_inner())[0x55908ab62dfa]
sql/sql_select.cc:1542(JOIN::optimize())[0x55908ab5e02c]
sql/sql_select.cc:4368(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*))[0x55908ab7b707]
sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55908ab519c5]
sql/sql_parse.cc:6340(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55908aac2633]
sql/sql_parse.cc:3871(mysql_execute_command(THD*))[0x55908aab0668]
sql/sql_parse.cc:7855(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55908aacc314]
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55908aaa3291]
sql/sql_parse.cc:1398(do_command(THD*))[0x55908aa9fdb0]
sql/sql_connect.cc:1404(do_handle_one_connection(CONNECT*))[0x55908ae75f7d]
sql/sql_connect.cc:1310(handle_one_connection)[0x55908ae75837]
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55908c4a82ef]
nptl/pthread_create.c:478(start_thread)[0x7f53a1737609]
Query (0x62b000000290): SELECT t1.id
FROM (SELECT b FROM t2 GROUP BY b ORDER BY COUNT(DISTINCT id) LIMIT 1 ) dt
JOIN t1 ON dt.b=t1.a
bb-11.0 3135acf0bba521cd032c
mariadbd: /10.11/sql/sql_select.cc:7793: double matching_candidates_in_table(JOIN_TAB*, bool, uint): Assertion `table_records == 0 || sel <= s->table->opt_range_condition_rows / table_records' failed.
221202 10:58:11 [ERROR] mysqld got signal 6 ;
Server version: 11.0.1-MariaDB-debug-log
??:0(__assert_fail)[0x7f0e5ac35fd6]
sql/sql_select.cc:7796(matching_candidates_in_table(st_join_table*, bool, unsigned int))[0x560d1e0eb538]
sql/sql_select.cc:8964(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x560d1e0f2a76]
sql/sql_select.cc:10664(get_costs_for_tables(JOIN*, unsigned long long, unsigned int, double, Json_writer_object*, st_join_table**, SORT_POSITION**, unsigned long long*, bool))[0x560d1e0fc372]
sql/sql_select.cc:10921(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned long long*))[0x560d1e0fd6d4]
sql/sql_select.cc:9897(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int))[0x560d1e0f8001]
sql/sql_select.cc:9413(choose_plan(JOIN*, unsigned long long, TABLE_LIST*))[0x560d1e0f596b]
sql/sql_select.cc:6041(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x560d1e0dd116]
sql/sql_select.cc:2556(JOIN::optimize_inner())[0x560d1e0b8e89]
sql/sql_select.cc:1890(JOIN::optimize())[0x560d1e0b1f66]
sql/sql_select.cc:5100(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x560d1e0d3840]
sql/sql_select.cc:608(handle_select(THD*, LEX*, select_result*, unsigned long long))[0x560d1e0a39bd]
sql/sql_parse.cc:6263(execute_sqlcom_select(THD*, TABLE_LIST*))[0x560d1dfc7654]
sql/sql_parse.cc:3947(mysql_execute_command(THD*, bool))[0x560d1dfb5f4d]
sql/sql_parse.cc:7998(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x560d1dfd23e4]
sql/sql_parse.cc:1896(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x560d1dfa86aa]
sql/sql_parse.cc:1407(do_command(THD*, bool))[0x560d1dfa53e6]
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x560d1e46cee4]
sql/sql_connect.cc:1320(handle_one_connection)[0x560d1e46c841]
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x560d1f0a2d36]
nptl/pthread_create.c:478(start_thread)[0x7f0e5b150609]
Query (0x6290001092a8): SELECT t1.id
FROM (SELECT b FROM t2 GROUP BY b ORDER BY COUNT(DISTINCT id) LIMIT 1 ) dt
JOIN t1 ON dt.b=t1.a