Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-31885

Wrong result upon forcing fulltext index on InnoDB table

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.4(EOL), 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
    • 10.5, 10.6, 10.11
    • Optimizer

    Description

      --source include/have_innodb.inc
       
      CREATE TABLE t (a varchar(128), fulltext(a)) engine=InnoDB;
      INSERT INTO t VALUES ('Alabama'),('Washington');
      SELECT a FROM t FORCE INDEX(a) ORDER BY a;
      ALTER TABLE t ADD PRIMARY KEY(a);
      SELECT a FROM t FORCE INDEX(a) ORDER BY a;
       
      # Cleanup
      DROP TABLE t;
      

      10.4 b54e4bf0

      CREATE TABLE t (a varchar(128), fulltext(a)) engine=InnoDB;
      INSERT INTO t VALUES ('Alabama'),('Washington');
      SELECT a FROM t FORCE INDEX(a) ORDER BY a;
      a
      Alabama
      Washington
      ALTER TABLE t ADD PRIMARY KEY(a);
      SELECT a FROM t FORCE INDEX(a) ORDER BY a;
      a
      DROP TABLE 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:

      INSERT INTO t VALUES ('Alabama'),('Washington');
      EXPLAIN EXTENDED SELECT a FROM t FORCE INDEX(a) ORDER BY a;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	2	100.00	Using filesort
      Warnings:
      Note	1003	select `test`.`t`.`a` AS `a` from `test`.`t` FORCE INDEX (`a`) order by `test`.`t`.`a`
      ALTER TABLE t ADD PRIMARY KEY(a);
      EXPLAIN EXTENDED SELECT a FROM t FORCE INDEX(a) ORDER BY a;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t	index	NULL	a	130	NULL	2	100.00	
      Warnings:
      Note	1003	select `test`.`t`.`a` AS `a` from `test`.`t` FORCE INDEX (`a`) order by `test`.`t`.`a`
      

      Reproducible on all existing versions and on MySQL 8.0.28.

      Attachments

        Activity

          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;
          			}
          		}
          

          marko 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 ; } }

          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.

          Roel 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

            psergei Sergei Petrunia
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start 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.