[MDEV-25643] Assertion `table->no_keyread || !table->covering_keys.is_set(tab->index) || table->file->keyread == tab->index' failed in join_read_first Created: 2021-05-10  Updated: 2023-06-01  Resolved: 2023-01-24

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions, Parser
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.11.2, 10.3.38, 10.4.28, 10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5, 10.10.3

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-25632 Bug report: abortion in sql/sql_plugi... Closed
PartOf
is part of MDEV-30052 Crash with a query containing nested ... Closed
Relates
relates to MDEV-23948 Assertion `table->no_keyread || !tabl... Open
relates to MDEV-25636 Bug report: abortion in sql/sql_parse... Closed
relates to MDEV-28799 SIGSEGV in JOIN_CACHE::reset_join and... Closed

 Description   

test case is derived from MDEV-25636:

--source include/have_innodb.inc
 
 
CREATE TABLE t1 (i1 int primary key)engine=innodb;
 
INSERT INTO t1 VALUES (62),(66);
 
CREATE TABLE t2 (i1 int) engine=innodb;
 
 
SELECT 1 FROM t1 
WHERE t1.i1 =( SELECT t1.i1 FROM t2  UNION 
SELECT dt1.i1 FROM (t1 AS dt1 ) window w1 as (partition by t1.i1) limit 1);

10.2 d0785f773188b5f0eebb313

 
#3  <signal handler called>
 
#4  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#5  0x00007f0dabfad859 in __GI_abort () at abort.c:79
 
#6  0x00007f0dabfad729 in __assert_fail_base (fmt=0x7f0dac143588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x5591aab2f5f0 "table->no_keyread || !table->covering_keys.is_set(tab->index) || table->file->keyread == tab->index", file=0x5591aab2cab8 "/10.2/src/sql/sql_select.cc", line=19851, function=<optimized out>) at assert.c:92
 
#7  0x00007f0dabfbef36 in __GI___assert_fail (assertion=0x5591aab2f5f0 "table->no_keyread || !table->covering_keys.is_set(tab->index) || table->file->keyread == tab->index", file=0x5591aab2cab8 "/10.2/src/sql/sql_select.cc", line=19851, function=0x5591aab2f5d0 "int join_read_first(JOIN_TAB*)") at assert.c:101
 
#8  0x00005591aa0f3e2e in join_read_first (tab=0x7f0d501756b8) at /10.2/src/sql/sql_select.cc:19851
 
#9  0x00005591aa0f18e2 in sub_select (join=0x7f0d50016e80, join_tab=0x7f0d501756b8, end_of_records=false) at /10.2/src/sql/sql_select.cc:18871
 
#10 0x00005591aa0f0ea6 in do_select (join=0x7f0d50016e80, procedure=0x0) at /10.2/src/sql/sql_select.cc:18418
 
#11 0x00005591aa0caa87 in JOIN::exec_inner (this=0x7f0d50016e80) at /10.2/src/sql/sql_select.cc:3651
 
#12 0x00005591aa0c9f2e in JOIN::exec (this=0x7f0d50016e80) at /10.2/src/sql/sql_select.cc:3446
 
#13 0x00005591aa17115d in st_select_lex_unit::exec (this=0x7f0d50013490) at /10.2/src/sql/sql_union.cc:1073
 
#14 0x00005591aa3d943b in subselect_union_engine::exec (this=0x7f0d50015da0) at /10.2/src/sql/item_subselect.cc:4030
 
#15 0x00005591aa3cdca1 in Item_subselect::exec (this=0x7f0d50015c20) at /10.2/src/sql/item_subselect.cc:770
 
#16 0x00005591aa3cfadd in Item_singlerow_subselect::val_int (this=0x7f0d50015c20) at /10.2/src/sql/item_subselect.cc:1391
 
#17 0x00005591a9fa569d in Item::val_int_result (this=0x7f0d50015c20) at /10.2/src/sql/item.h:1274
 
#18 0x00005591aa33491b in Item_cache_int::cache_value (this=0x7f0d501761c8) at /10.2/src/sql/item.cc:9701
 
#19 0x00005591aa33f3c6 in Item_cache_wrapper::cache (this=0x7f0d5009db50) at /10.2/src/sql/item.cc:8362
 
#20 0x00005591aa330829 in Item_cache_wrapper::val_int (this=0x7f0d5009db50) at /10.2/src/sql/item.cc:8416
 
#21 0x00005591aa345abf in Arg_comparator::compare_int_signed (this=0x7f0d50015e98) at /10.2/src/sql/item_cmpfunc.cc:970
 
#22 0x00005591aa359cc4 in Arg_comparator::compare (this=0x7f0d50015e98) at /10.2/src/sql/item_cmpfunc.h:87
 
#23 0x00005591aa348117 in Item_func_eq::val_int (this=0x7f0d50015dd8) at /10.2/src/sql/item_cmpfunc.cc:1803
 
#24 0x00005591aa0f1d22 in evaluate_join_record (join=0x7f0d50016038, join_tab=0x7f0d5009a298, error=0) at /10.2/src/sql/sql_select.cc:18969
 
#25 0x00005591aa0f1af8 in sub_select (join=0x7f0d50016038, join_tab=0x7f0d5009a298, end_of_records=false) at /10.2/src/sql/sql_select.cc:18913
 
#26 0x00005591aa0f0ea6 in do_select (join=0x7f0d50016038, procedure=0x0) at /10.2/src/sql/sql_select.cc:18418
 
#27 0x00005591aa0caa87 in JOIN::exec_inner (this=0x7f0d50016038) at /10.2/src/sql/sql_select.cc:3651
 
#28 0x00005591aa0c9f2e in JOIN::exec (this=0x7f0d50016038) at /10.2/src/sql/sql_select.cc:3446
 
#29 0x00005591aa0cb108 in mysql_select (thd=0x7f0d50000d90, tables=0x7f0d50012958, wild_num=0, fields=..., conds=0x7f0d50015dd8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f0d50016018, unit=0x7f0d50004988, select_lex=0x7f0d500050c8) at /10.2/src/sql/sql_select.cc:3849
 
#30 0x00005591aa0bf25c in handle_select (thd=0x7f0d50000d90, lex=0x7f0d500048c8, result=0x7f0d50016018, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361
 
#31 0x00005591aa089771 in execute_sqlcom_select (thd=0x7f0d50000d90, all_tables=0x7f0d50012958) at /10.2/src/sql/sql_parse.cc:6274
 
#32 0x00005591aa0802e5 in mysql_execute_command (thd=0x7f0d50000d90) at /10.2/src/sql/sql_parse.cc:3585
 
#33 0x00005591aa08d52c in mysql_parse (thd=0x7f0d50000d90, rawbuf=0x7f0d500126f8 "SELECT 1 FROM t1 \nWHERE t1.i1 =( SELECT t1.i1 FROM t2  UNION \nSELECT dt1.i1 FROM (t1 AS dt1 ) window w1 as (partition by t1.i1) limit 1)", length=136, parser_state=0x7f0da62cb570, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7796
 
#34 0x00005591aa07b756 in dispatch_command (command=COM_QUERY, thd=0x7f0d50000d90, packet=0x7f0d50008b51 "", packet_length=136, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
 
#35 0x00005591aa07a251 in do_command (thd=0x7f0d50000d90) at /10.2/src/sql/sql_parse.cc:1381
 
#36 0x00005591aa1d588e in do_handle_one_connection (connect=0x5591ad5d2a40) at /10.2/src/sql/sql_connect.cc:1336
 
#37 0x00005591aa1d55f3 in handle_one_connection (arg=0x5591ad5d2a40) at /10.2/src/sql/sql_connect.cc:1241
 
#38 0x00005591aaa011a8 in pfs_spawn_thread (arg=0x5591ad5b5e40) at /10.2/src/storage/perfschema/pfs.cc:1869
 
#39 0x00007f0dac4d0609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#40 0x00007f0dac0aa293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

no visible effect on a non-debug build.

 Comments   
Comment by Alice Sherepa [ 2021-05-10 ]

test case from MDEV-25634:

CREATE TABLE t1 (i3 int NOT NULL, i1 int , i2 int , i4 int , PRIMARY key(i2));
 
INSERT INTO t1 VALUES (6,72,98,98),(46,1,6952,0);
 
 
 
SELECT i1 FROM t1 
WHERE t1.i3 =
 
  (SELECT ref_4.i2 FROM t1 AS ref_4
 
   	WHERE t1.i2 > (SELECT i3 FROM t1 ORDER BY i3 LIMIT 1 OFFSET 4)
 
   UNION 
   SELECT ref_6.i2
 
   FROM (t1 AS ref_5 JOIN t1 AS ref_6 ON (ref_6.i1 > ref_6.i2))
 
   WHERE (t1.i2 >= t1.i2));

It fails on 5.5-10.5,
on 5.5-10.1 - sql_select.cc:18372: enum_nested_loop_state sub_select_cache(JOIN*, JOIN_TAB*, bool): Assertion `cache != __null' failed.
on 10.2-10.5 - Assertion `table->no_keyread || !table->covering_keys.is_set(tab->index) || table->file->keyread == tab->index'
on non-debug build:

Version: '10.5.10-MariaDB' 
210510 14:36:53 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.5.10-MariaDB
 
 
 
Thread pointer: 0x7f83a8007938
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7f84f016cdd8 thread_stack 0x49000
 
mysys/stacktrace.c:213(my_print_stacktrace)[0x55bcfec6ddce]
 
sql/signal_handler.cc:224(handle_fatal_signal)[0x55bcfe730705]
 
??:0(__restore_rt)[0x7f84f3032730]
 
sql/sql_join_cache.h:679(sub_select_cache(JOIN*, st_join_table*, bool))[0x55bcfe57de5a]
 
sql/sql_select.cc:21045(evaluate_join_record(JOIN*, st_join_table*, int))[0x55bcfe5703c8]
 
sql/sql_select.cc:20829(sub_select(JOIN*, st_join_table*, bool))[0x55bcfe57da17]
 
sql/sql_select.cc:20355(do_select)[0x55bcfe5a209f]
 
sql/sql_select.cc:4284(JOIN::exec())[0x55bcfe5a2390]
 
sql/sql_union.cc:2236(st_select_lex_unit::exec())[0x55bcfe5e9ad4]
 
sql/item_subselect.cc:4104(subselect_union_engine::exec())[0x55bcfe7d3a2e]
 
sql/item_subselect.cc:834(Item_subselect::exec())[0x55bcfe7d44cd]
 
sql/item_subselect.cc:1441(Item_singlerow_subselect::val_int())[0x55bcfe7d3d6e]
 
sql/item.cc:9917(Item_cache_int::cache_value())[0x55bcfe742e98]
 
sql/item.cc:8704(Item_cache_wrapper::cache())[0x55bcfe75d0d3]
 
sql/item.cc:8758(Item_cache_wrapper::val_int())[0x55bcfe756888]
 
sql/item_cmpfunc.cc:943(Arg_comparator::compare_int_signed())[0x55bcfe75f868]
 
sql/item_cmpfunc.cc:1777(Item_func_eq::val_int())[0x55bcfe75fe9b]
 
sql/sql_select.cc:20922(evaluate_join_record(JOIN*, st_join_table*, int))[0x55bcfe570181]
 
sql/sql_select.cc:20860(sub_select(JOIN*, st_join_table*, bool))[0x55bcfe57da8d]
 
sql/sql_select.cc:20355(do_select)[0x55bcfe5a209f]
 
sql/sql_select.cc:4284(JOIN::exec())[0x55bcfe5a2390]
 
sql/sql_select.cc:4761(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*))[0x55bcfe5a0698]
 
sql/sql_select.cc:443(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55bcfe5a0ffe]
 
sql/sql_parse.cc:6313(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55bcfe541b39]
 
sql/sql_parse.cc:6055(mysql_execute_command(THD*))[0x55bcfe54b2ce]
 
sql/sql_parse.cc:8116(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55bcfe53d1be]
 
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55bcfe5487ea]
 
sql/sql_parse.cc:1370(do_command(THD*))[0x55bcfe549724]
 
sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x55bcfe6321a0]
 
sql/sql_connect.cc:1312(handle_one_connection)[0x55bcfe63257d]
 
perfschema/pfs.cc:2204(pfs_spawn_thread)[0x55bcfe9632eb]
 
nptl/pthread_create.c:487(start_thread)[0x7f84f3027fa3]
 
x86_64/clone.S:97(clone)[0x7f84f2c324cf]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7f83a80a5720): SELECT i1 FROM t1 
WHERE t1.i3 =
 
  (SELECT ref_4.i2 FROM t1 AS ref_4
 
   WHERE t1.i2 > (SELECT i3 FROM t1 ORDER BY i3 LIMIT 1 OFFSET 4)
 
   UNION 
   SELECT ref_6.i2
 
   FROM (t1 AS ref_5 JOIN t1 AS ref_6 ON (ref_6.i1 > ref_6.i2))
 
   WHERE (t1.i2 >= t1.i2))

Generated at Thu Feb 08 09:39:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.