CREATETABLE t (a varchar(128), fulltext(a)) engine=InnoDB;
INSERTINTO t VALUES ('Alabama'),('Washington');
SELECT a FROM t FORCEINDEX(a) ORDERBY a;
ALTERTABLE t ADDPRIMARYKEY(a);
SELECT a FROM t FORCEINDEX(a) ORDERBY a;
# Cleanup
DROPTABLE t;
10.4 b54e4bf0
CREATETABLE t (a varchar(128), fulltext(a)) engine=InnoDB;
INSERTINTO t VALUES ('Alabama'),('Washington');
SELECT a FROM t FORCEINDEX(a) ORDERBY a;
a
Alabama
Washington
ALTERTABLE t ADDPRIMARYKEY(a);
SELECT a FROM t FORCEINDEX(a) ORDERBY a;
a
DROPTABLE t;
So, on a table without PK the query result is correct, but with a PK it is not.
The plans show that without PK the hint is silently ignored, while with PK the key is used:
INSERTINTO t VALUES ('Alabama'),('Washington');
EXPLAIN EXTENDED SELECT a FROM t FORCEINDEX(a) ORDERBY a;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t ALLNULLNULLNULLNULL 2 100.00 Using filesort
Warnings:
Note 1003 select `test`.`t`.`a` AS `a` from `test`.`t` FORCEINDEX (`a`) orderby `test`.`t`.`a`
ALTERTABLE t ADDPRIMARYKEY(a);
EXPLAIN EXTENDED SELECT a FROM t FORCEINDEX(a) ORDERBY a;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t indexNULL a 130 NULL 2 100.00
Warnings:
Note 1003 select `test`.`t`.`a` AS `a` from `test`.`t` FORCEINDEX (`a`) orderby `test`.`t`.`a`
Reproducible on all existing versions and on MySQL 8.0.28.
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
CREATETABLE t (a varchar(128) primarykey, fulltext(a)) engine=InnoDB;
INSERTINTO t VALUES ('Alabama'),('Washington');
SELECT a FROM t FORCEINDEX(a) ORDERBY a;
DROPTABLE 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
#0 0x000055c5289dc7eb in TABLE_LIST::process_index_hints (this=this@entry=0x7f827401d840, tbl=tbl@entry=0x7f82741917d8) at /mariadb/10.4/sql/table.cc:8209
#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
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
#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,
#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
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;
}
}
Marko Mäkelä
added a comment - 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 ;
}
}
Core was generated by `/test/preview-11.6-preview_MD200624-mariadb-11.6.0-linux-x86_64-opt/bin/mariadb'.
Program terminated with signal SIGABRT, Aborted.
#0 0x000014f4b129999b in ?? ()
[Current thread is 1 (LWP 538492)]
(gdb) bt
#0 0x000014f4b129999b in ?? ()
#1 0x0000000000000005 in ?? ()
#2 0xa757c0c272433c00 in ?? ()
#3 0x0000000000000006 in ?? ()
#4 0x000014f48a5e76c0 in ?? ()
#5 0x000014f4580165d0 in ?? ()
#6 0x000014f458016268 in ?? ()
#7 0x000014f458016350 in ?? ()
#8 0x000014f4b1242866 in ?? ()
#9 0x000014f4b13ffd30 in ?? ()
#10 0x000014f4b12268b7 in ?? ()
#11 0x0000000000000020 in ?? ()
#12 0x000056427749a957 in TABLE_LIST::process_index_hints (this=0x14f4580165d0, tbl=0x14f4580166b8)
at /test/preview-11.6-preview_opt/sql/table.cc:8894
Backtrace stopped: previous frame inner to this frame (corrupt stack?)
Testcase reduction is exceedingly slow/sporadic.
Roel Van de Paar
added a comment - I saw the following corrupt stack in preview-11.6-preview which seems to happen after TABLE_LIST::process_index_hints (ref above):
preview-11.6-preview 11.6.0 42294b8cd2cbb72c1d5da6058dd6f0c55669def7
Core was generated by `/test/preview-11.6-preview_MD200624-mariadb-11.6.0-linux-x86_64-opt/bin/mariadb'.
Program terminated with signal SIGABRT, Aborted.
#0 0x000014f4b129999b in ?? ()
[Current thread is 1 (LWP 538492)]
(gdb) bt
#0 0x000014f4b129999b in ?? ()
#1 0x0000000000000005 in ?? ()
#2 0xa757c0c272433c00 in ?? ()
#3 0x0000000000000006 in ?? ()
#4 0x000014f48a5e76c0 in ?? ()
#5 0x000014f4580165d0 in ?? ()
#6 0x000014f458016268 in ?? ()
#7 0x000014f458016350 in ?? ()
#8 0x000014f4b1242866 in ?? ()
#9 0x000014f4b13ffd30 in ?? ()
#10 0x000014f4b12268b7 in ?? ()
#11 0x0000000000000020 in ?? ()
#12 0x000056427749a957 in TABLE_LIST::process_index_hints (this=0x14f4580165d0, tbl=0x14f4580166b8)
at /test/preview-11.6-preview_opt/sql/table.cc:8894
Backtrace stopped: previous frame inner to this frame (corrupt stack?)
Testcase reduction is exceedingly slow/sporadic.
People
Sergei Petrunia
Elena Stepanova
Votes:
0Vote for this issue
Watchers:
4Start watching this issue
Dates
Created:
Updated:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
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
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:
since FT search returns rank only. In addition engine should
be able to retrieve FTS_DOC_ID column value if necessary. */
&& bitmap_is_set(table->read_set, i)
FTS_DOC_ID_COL_NAME)) {
}
}