|
For the following SQL, I think that the problem is that in JOIN::optimize_stage2() we have tab->table->keys_in_use_for_order_by containing the value 2.
--source include/have_innodb.inc
|
CREATE TABLE t (a varchar(128) primary key, fulltext(a)) engine=InnoDB;
|
INSERT INTO t VALUES ('Alabama'),('Washington');
|
SELECT a FROM t FORCE INDEX(a) ORDER BY a;
|
DROP TABLE t;
|
As far as I understand, the bitmap value 2 refers to index 1 of tab->table->key_info: the FULLTEXT INDEX on the column a, right after index 0 (the PRIMARY key).
There is a suspicious assignment below. Shouldn’t only totally ordered indexes be allowed for ORDER BY and GROUP BY? For example, SPATIAL INDEX is only partially ordered, and HASH INDEX is not ordered at all. I do not know about FULLTEXT INDEX in MyISAM or Aria, but I have hard time imagining how an inverted index could possibly be used for ORDER BY or GROUP BY.
|
10.4 161ce045a71e306768d4609bdc35788fa5ea2a71
|
Thread 3 hit Hardware watchpoint 6: -location tab.table.keys_in_use_for_order_by.buffer[0]
|
|
Old value = 0
|
New value = 3
|
0x000055c5289dc7eb in TABLE_LIST::process_index_hints (this=this@entry=0x7f827401d840, tbl=tbl@entry=0x7f82741917d8) at /mariadb/10.4/sql/table.cc:8209
|
8209 tbl->keys_in_use_for_query= tbl->keys_in_use_for_group_by=
|
(rr) backtrace
|
#0 0x000055c5289dc7eb in TABLE_LIST::process_index_hints (this=this@entry=0x7f827401d840, tbl=tbl@entry=0x7f82741917d8) at /mariadb/10.4/sql/table.cc:8209
|
#1 0x000055c5288a42d7 in setup_table_attributes (thd=thd@entry=0x7f827400ae18, table_list=0x7f827401d840, first_select_table=<optimized out>, first_select_table@entry=0x0, tablenr=@0x7f82640ad81c: 0)
|
at /mariadb/10.4/sql/sql_base.cc:7876
|
#2 0x000055c5288a7651 in setup_tables (thd=thd@entry=0x7f827400ae18, context=0x7f827401df88, from_clause=0x7f827401e110, tables=tables@entry=0x7f827401d840,
|
leaves=@0x7f827401e188: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f827401f078, last = 0x7f827401f078, elements = 1}, <No data fields>}, select_insert=select_insert@entry=false,
|
full_table_list=<optimized out>) at /mariadb/10.4/sql/sql_base.cc:7970
|
#3 0x000055c5288a7b08 in setup_tables_and_check_access (thd=thd@entry=0x7f827400ae18, context=<optimized out>, from_clause=<optimized out>, tables=tables@entry=0x7f827401d840,
|
leaves=@0x7f827401e188: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f827401f078, last = 0x7f827401f078, elements = 1}, <No data fields>}, select_insert=select_insert@entry=false,
|
want_access_first=2, want_access=1, full_table_list=true) at /mariadb/10.4/sql/sql_base.cc:8089
|
#4 0x000055c5288ddcf4 in mysql_prepare_insert_check_table (thd=thd@entry=0x7f827400ae18, table_list=table_list@entry=0x7f827401d840,
|
fields=@0x7f827400faf0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55c529220510 <end_of_list>, last = 0x7f827400faf0, elements = 0}, <No data fields>},
|
select_insert=select_insert@entry=false) at /mariadb/10.4/sql/sql_insert.cc:1451
|
#5 0x000055c5288dfa12 in mysql_prepare_insert (thd=thd@entry=0x7f827400ae18, table_list=table_list@entry=0x7f827401d840, table=table@entry=0x0,
|
fields=@0x7f827400faf0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55c529220510 <end_of_list>, last = 0x7f827400faf0, elements = 0}, <No data fields>}, values=values@entry=0x7f827401e3f0,
|
update_fields=@0x7f827400fb20: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55c529220510 <end_of_list>, last = 0x7f827400fb20, elements = 0}, <No data fields>},
|
update_values=@0x7f827400fb08: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55c529220510 <end_of_list>, last = 0x7f827400fb08, elements = 0}, <No data fields>}, duplic=DUP_ERROR,
|
where=0x7f82640adb28, select_insert=false) at /mariadb/10.4/sql/sql_insert.cc:1583
|
#6 0x000055c5288e5567 in mysql_insert (thd=thd@entry=0x7f827400ae18, table_list=0x7f827401d840,
|
fields=@0x7f827400faf0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55c529220510 <end_of_list>, last = 0x7f827400faf0, elements = 0}, <No data fields>},
|
values_list=@0x7f827400fb38: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f827401e4c0, last = 0x7f827401e5b0, elements = 2}, <No data fields>},
|
update_fields=@0x7f827400fb20: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55c529220510 <end_of_list>, last = 0x7f827400fb20, elements = 0}, <No data fields>},
|
update_values=@0x7f827400fb08: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55c529220510 <end_of_list>, last = 0x7f827400fb08, elements = 0}, <No data fields>}, duplic=DUP_ERROR,
|
ignore=false) at /mariadb/10.4/sql/sql_insert.cc:776
|
#7 0x000055c528910086 in mysql_execute_command (thd=thd@entry=0x7f827400ae18) at /mariadb/10.4/sql/sql_parse.cc:4613
|
#8 0x000055c528915999 in mysql_parse (thd=thd@entry=0x7f827400ae18, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7f82640ae3c0, is_com_multi=is_com_multi@entry=false,
|
is_next_command=is_next_command@entry=false) at /mariadb/10.4/sql/sql_parse.cc:8010
|
#9 0x000055c528917263 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f827400ae18, packet=packet@entry=0x7f8274014359 "INSERT INTO t VALUES ('Alabama'),('Washington')",
|
packet_length=packet_length@entry=47, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /mariadb/10.4/sql/sql_parse.cc:1857
|
Later on, we seem to wrongly disqualify the PRIMARY KEY from being used in ORDER BY, leaving only the incorrect alternative of using the FULLTEXT INDEX:
|
10.4 161ce045a71e306768d4609bdc35788fa5ea2a71
|
(rr) continue
|
Continuing.
|
|
Thread 3 hit Hardware watchpoint 6: -location tab.table.keys_in_use_for_order_by.buffer[0]
|
|
Old value = 3
|
New value = 2
|
Bitmap<64u>::intersect (map2=<optimized out>, this=<optimized out>) at /mariadb/10.4/sql/sql_bitmap.h:181
|
181 for (size_t i= 0; i < ARRAY_ELEMENTS; i++)
|
(rr) backtrace
|
#0 Bitmap<64u>::intersect (map2=<optimized out>, this=<optimized out>) at /mariadb/10.4/sql/sql_bitmap.h:181
|
#1 TABLE_LIST::process_index_hints (this=this@entry=0x7f827401de50, tbl=tbl@entry=0x7f82741917d8) at /mariadb/10.4/sql/table.cc:8323
|
#2 0x000055c5288a42d7 in setup_table_attributes (thd=thd@entry=0x7f827400ae18, table_list=0x7f827401de50, first_select_table=<optimized out>, first_select_table@entry=0x0, tablenr=@0x7f82640ad52c: 0)
|
at /mariadb/10.4/sql/sql_base.cc:7876
|
#3 0x000055c5288a7651 in setup_tables (thd=thd@entry=0x7f827400ae18, context=0x7f827401d840, from_clause=0x7f827401d9c8, tables=tables@entry=0x7f827401de50,
|
leaves=@0x7f827401da40: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f827401f728, last = 0x7f827401f728, elements = 1}, <No data fields>}, select_insert=select_insert@entry=false,
|
full_table_list=<optimized out>) at /mariadb/10.4/sql/sql_base.cc:7970
|
#4 0x000055c5288a7b08 in setup_tables_and_check_access (thd=0x7f827400ae18, context=<optimized out>, from_clause=<optimized out>, tables=0x7f827401de50,
|
leaves=@0x7f827401da40: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f827401f728, last = 0x7f827401f728, elements = 1}, <No data fields>}, select_insert=select_insert@entry=false,
|
want_access_first=1, want_access=1, full_table_list=false) at /mariadb/10.4/sql/sql_base.cc:8089
|
#5 0x000055c5289604d1 in JOIN::prepare (this=this@entry=0x7f827401f048, tables_init=tables_init@entry=0x7f827401de50, wild_num=wild_num@entry=0, conds_init=conds_init@entry=0x0, og_num=og_num@entry=1,
|
order_init=order_init@entry=0x7f827401eec8, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f827401d7f0, unit_arg=0x7f827400ebb8)
|
at /mariadb/10.4/sql/sql_select.cc:1263
|
#6 0x000055c528977f9c in mysql_select (thd=thd@entry=0x7f827400ae18, tables=0x7f827401de50, wild_num=0,
|
fields=@0x7f827401d950: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f827401ddc0, last = 0x7f827401ddc0, elements = 1}, <No data fields>}, conds=0x0, og_num=1, order=0x7f827401eec8,
|
group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f827401f020, unit=0x7f827400ebb8, select_lex=0x7f827401d7f0) at /mariadb/10.4/sql/sql_select.cc:4789
|
#7 0x000055c528978170 in handle_select (thd=thd@entry=0x7f827400ae18, lex=lex@entry=0x7f827400eaf8, result=result@entry=0x7f827401f020, setup_tables_done_option=setup_tables_done_option@entry=0)
|
at /mariadb/10.4/sql/sql_select.cc:442
|
#8 0x000055c528906204 in execute_sqlcom_select (thd=thd@entry=0x7f827400ae18, all_tables=0x7f827401de50) at /mariadb/10.4/sql/sql_parse.cc:6473
|
#9 0x000055c52890ea0d in mysql_execute_command (thd=thd@entry=0x7f827400ae18) at /mariadb/10.4/sql/sql_parse.cc:3976
|
#10 0x000055c528915999 in mysql_parse (thd=thd@entry=0x7f827400ae18, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7f82640ae3c0, is_com_multi=is_com_multi@entry=false,
|
is_next_command=is_next_command@entry=false) at /mariadb/10.4/sql/sql_parse.cc:8010
|
#11 0x000055c528917263 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f827400ae18, packet=packet@entry=0x7f8274014359 "SELECT a FROM t FORCE INDEX(a) ORDER BY a",
|
packet_length=packet_length@entry=41, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /mariadb/10.4/sql/sql_parse.cc:1857
|
A little later during the execution of SELECT, the FULLTEXT INDEX will be wrongly chosen:
|
10.4 161ce045a71e306768d4609bdc35788fa5ea2a71
|
(rr) continue
|
Continuing.
|
|
Thread 3 hit Breakpoint 7, ha_innobase::change_active_index (this=0x7f82741925f0, keynr=1) at /mariadb/10.4/storage/innobase/handler/ha_innodb.cc:9533
|
9533 {
|
(rr) backtrace
|
#0 ha_innobase::change_active_index (this=0x7f82741925f0, keynr=1) at /mariadb/10.4/storage/innobase/handler/ha_innodb.cc:9533
|
#1 0x000055c528bcc59f in ha_innobase::index_init (this=<optimized out>, keynr=<optimized out>) at /mariadb/10.4/storage/innobase/handler/ha_innodb.cc:9186
|
#2 0x000055c52878b3a5 in handler::ha_index_init (this=0x7f82741925f0, idx=1, sorted=true) at /mariadb/10.4/sql/handler.h:3184
|
#3 0x000055c528965eb6 in join_read_first (tab=0x7f8274020308) at /mariadb/10.4/sql/sql_select.cc:21898
|
#4 0x000055c52894ca60 in sub_select (join=0x7f827401f048, join_tab=0x7f8274020308, end_of_records=false) at /mariadb/10.4/sql/sql_select.cc:20886
|
#5 0x000055c52895f93c in do_select (join=join@entry=0x7f827401f048, procedure=<optimized out>) at /mariadb/10.4/sql/sql_select.cc:20412
|
#6 0x000055c528978e76 in JOIN::exec_inner (this=this@entry=0x7f827401f048) at /mariadb/10.4/sql/sql_select.cc:4605
|
#7 0x000055c528978f1c in JOIN::exec (this=this@entry=0x7f827401f048) at /mariadb/10.4/sql/sql_select.cc:4387
|
#8 0x000055c528977df5 in mysql_select (thd=thd@entry=0x7f827400ae18, tables=0x7f827401de50, wild_num=0,
|
fields=@0x7f827401d950: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f827401ddc0, last = 0x7f827401ddc0, elements = 1}, <No data fields>}, conds=0x0, og_num=1, order=0x7f827401eec8,
|
group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x7f827401f020, unit=0x7f827400ebb8, select_lex=0x7f827401d7f0) at /mariadb/10.4/sql/sql_select.cc:4826
|
#9 0x000055c528978170 in handle_select (thd=thd@entry=0x7f827400ae18, lex=lex@entry=0x7f827400eaf8, result=result@entry=0x7f827401f020, setup_tables_done_option=setup_tables_done_option@entry=0)
|
at /mariadb/10.4/sql/sql_select.cc:442
|
#10 0x000055c528906204 in execute_sqlcom_select (thd=thd@entry=0x7f827400ae18, all_tables=0x7f827401de50) at /mariadb/10.4/sql/sql_parse.cc:6473
|
As far as I understand, no index scan has been implemented for FULLTEXT INDEX. It is only supposed to be used via special access methods. In ha_innobase::change_active_index() there is the following piece of code, which perhaps should be augmented with a suitable assertion as part of fixing this bug:
/* Initialization of search_tuple is not needed for FT index
|
since FT search returns rank only. In addition engine should
|
be able to retrieve FTS_DOC_ID column value if necessary. */
|
if (m_prebuilt->index->type & DICT_FTS) {
|
for (uint i = 0; i < table->s->fields; i++) {
|
if (m_prebuilt->read_just_key
|
&& bitmap_is_set(table->read_set, i)
|
&& !strcmp(table->s->field[i]->field_name.str,
|
FTS_DOC_ID_COL_NAME)) {
|
m_prebuilt->fts_doc_id_in_read_set = true;
|
break;
|
}
|
}
|
|