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

Assertion `table->no_keyread || !table->covering_keys.is_set(tab->index) || table->file->keyread == tab->index' failed in join_read_first

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.4
    • Fix Version/s: 10.4
    • Component/s: Optimizer
    • Labels:
      None

      Description

      SET SESSION join_buffer_space_limit= 131072;
      SET SESSION optimizer_switch= 'optimize_join_buffer_size=off';
      SET SESSION join_cache_level= 4;
       
      CREATE TABLE t1 (pk INT PRIMARY KEY, f INT, KEY(f), KEY(pk,f)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1,8),(2,NULL);
       
      CREATE TABLE t2 (x INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (1),(2);
       
      SELECT a1.* FROM t1 AS a1, t1 AS a2, t2 WHERE a1.f <=> a2.pk AND a2.f = 0;
      

      10.4 c2ac0ce1

      mysqld: /data/src/10.4/sql/sql_select.cc:21404: int join_read_first(JOIN_TAB*): Assertion `table->no_keyread || !table->covering_keys.is_set(tab->index) || table->file->keyread == tab->index' failed.
      201012 21:28:03 [ERROR] mysqld got signal 6 ;
       
      #7  0x00007eff4286ff36 in __GI___assert_fail (assertion=0x55a71b9cfd90 "table->no_keyread || !table->covering_keys.is_set(tab->index) || table->file->keyread == tab->index", file=0x55a71b9cc3f8 "/data/src/10.4/sql/sql_select.cc", line=21404, function=0x55a71b9cfdf8 "int join_read_first(JOIN_TAB*)") at assert.c:101
      #8  0x000055a71ac891ff in join_read_first (tab=0x7eff2c067500) at /data/src/10.4/sql/sql_select.cc:21404
      #9  0x000055a71ac869cf in sub_select (join=0x7eff2c016318, join_tab=0x7eff2c067500, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:20405
      #10 0x000055a71ac87156 in evaluate_join_record (join=0x7eff2c016318, join_tab=0x7eff2c067158, error=0) at /data/src/10.4/sql/sql_select.cc:20628
      #11 0x000055a71ac86a32 in sub_select (join=0x7eff2c016318, join_tab=0x7eff2c067158, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:20408
      #12 0x000055a71ac85ef8 in do_select (join=0x7eff2c016318, procedure=0x0) at /data/src/10.4/sql/sql_select.cc:19946
      #13 0x000055a71ac5ac07 in JOIN::exec_inner (this=0x7eff2c016318) at /data/src/10.4/sql/sql_select.cc:4478
      #14 0x000055a71ac59d36 in JOIN::exec (this=0x7eff2c016318) at /data/src/10.4/sql/sql_select.cc:4260
      #15 0x000055a71ac5b48a in mysql_select (thd=0x7eff2c000d90, tables=0x7eff2c013b18, wild_num=1, fields=..., conds=0x7eff2c015890, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7eff2c0162f0, unit=0x7eff2c004cc0, select_lex=0x7eff2c013540) at /data/src/10.4/sql/sql_select.cc:4695
      #16 0x000055a71ac4ae9b in handle_select (thd=0x7eff2c000d90, lex=0x7eff2c004c00, result=0x7eff2c0162f0, setup_tables_done_option=0) at /data/src/10.4/sql/sql_select.cc:410
      #17 0x000055a71ac10738 in execute_sqlcom_select (thd=0x7eff2c000d90, all_tables=0x7eff2c013b18) at /data/src/10.4/sql/sql_parse.cc:6355
      #18 0x000055a71ac06d88 in mysql_execute_command (thd=0x7eff2c000d90) at /data/src/10.4/sql/sql_parse.cc:3889
      #19 0x000055a71ac14743 in mysql_parse (thd=0x7eff2c000d90, rawbuf=0x7eff2c013448 "SELECT a1.* FROM t1 AS a1, t1 AS a2, t2 WHERE a1.f <=> a2.pk AND a2.f = 0", length=73, parser_state=0x7eff3c840550, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7896
      #20 0x000055a71ac00c4e in dispatch_command (command=COM_QUERY, thd=0x7eff2c000d90, packet=0x7eff2c0087a1 "", packet_length=73, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1834
      #21 0x000055a71abff4b6 in do_command (thd=0x7eff2c000d90) at /data/src/10.4/sql/sql_parse.cc:1352
      #22 0x000055a71ad8d2cb in do_handle_one_connection (connect=0x55a71d7e7130) at /data/src/10.4/sql/sql_connect.cc:1412
      #23 0x000055a71ad8d014 in handle_one_connection (arg=0x55a71d7e7130) at /data/src/10.4/sql/sql_connect.cc:1316
      #24 0x000055a71b7a5b4a in pfs_spawn_thread (arg=0x55a71d6ecd40) at /data/src/10.4/storage/perfschema/pfs.cc:1869
      #25 0x00007eff42d87609 in start_thread (arg=<optimized out>) at pthread_create.c:477
      #26 0x00007eff4295b293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
      

      No obvious problem on a non-debug build (including a non-debug ASAN build).

      The failure is reproducible on 10.4, not on 10.3 or 10.5. However, on 10.5 the plan is essentially different, maybe adjustments to the query or data can be made so that the problem shows up on 10.5 as well.

      Plan on 10.4 (failing)

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	a1	index	f	pk	9	NULL	2	100.00	Using index
      1	SIMPLE	a2	hash_index|filter	PRIMARY,f,pk	#hash#PRIMARY:pk|f	4:9|5	test.a1.f	2 (50%)	50.00	Using where; Using index; Using rowid filter
      1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1003	select `test`.`a1`.`pk` AS `pk`,`test`.`a1`.`f` AS `f` from `test`.`t1` `a1` join `test`.`t1` `a2` join `test`.`t2` where `test`.`a2`.`f` = 0 and `test`.`a1`.`f` <=> `test`.`a2`.`pk`
      

      Plan on 10.5 (not failing)

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	a2	ref	PRIMARY,f,pk	f	5	const	1	100.00	Using index condition
      1	SIMPLE	a1	index	f	pk	9	NULL	2	100.00	Using where; Using index
      1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1003	select `test`.`a1`.`pk` AS `pk`,`test`.`a1`.`f` AS `f` from `test`.`t1` `a1` join `test`.`t1` `a2` join `test`.`t2` where `test`.`a2`.`f` = 0 and `test`.`a1`.`f` <=> `test`.`a2`.`pk`
      

      There was a similar assertion failure on 10.5 presumably fixed in the scope of MDEV-22077, but the test case above doesn't reproduce it on 10.5 even before the patch for MDEV-22077.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: