[MDEV-31243] Assertion `field->orig_table->stats_is_read' failed in is_eits_usable with JOIN_CACHE_LEVEL > 2 Created: 2023-05-11  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.6, 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Michael Widenius
Resolution: Unresolved Votes: 0
Labels: affects-tests, regression, stack-smashing

Issue Links:
Problem/Incident
is caused by MDEV-26974 Improve selectivity and related costs... Closed

 Description   

Note: I don't know why table_open_cache setting makes any difference there. It doesn't have to be to @@global.table_open_cache, a constant can be used instead, I just wanted to indicate that it doesn't even change.

CREATE TABLE t1 (a INT);
 
INSERT INTO t1 VALUES (1),(2);
 
 
 
CREATE TABLE t2 (b INT);
 
INSERT INTO t2 VALUES (2),(3);
 
 
 
SET SESSION USE_STAT_TABLES= NEVER;
 
SET SESSION JOIN_CACHE_LEVEL= 3;
 
SET GLOBAL TABLE_OPEN_CACHE= @@global.table_open_cache;
 
 
 
SELECT * FROM t1 JOIN t2 ON t2.b = t1.a;
 
 
 
# Cleanup
 
DROP TABLE t1, t2;

11.0 368dd22a

 
mariadbd: /data/src/11.0/sql/sql_statistics.cc:4150: bool is_eits_usable(Field*): Assertion `field->orig_table->stats_is_read' failed.
 
230511 17:05:28 [ERROR] mysqld got signal 6 ;
 
 
 
#9  0x00007f071ae53df2 in __GI___assert_fail (assertion=0x55609a1ecee0 "field->orig_table->stats_is_read", file=0x55609a1ebd80 "/data/src/11.0/sql/sql_statistics.cc", line=4150, function=0x55609a1ecf40 "bool is_eits_usable(Field*)") at ./assert/assert.c:101
 
#10 0x000055609838dc2c in is_eits_usable (field=0x6190000f6510) at /data/src/11.0/sql/sql_statistics.cc:4150
 
#11 0x000055609821beb7 in hash_join_fanout (join=0x6290000fceb8, tab=0x6290002ee640, remaining_tables=2, rnd_records=2, hj_start_key=0x6290002efc00, stats_found=0x7f0703476a10) at /data/src/11.0/sql/sql_select.cc:8146
 
#12 0x0000556098220e81 in best_access_path (join=0x6290000fceb8, s=0x6290002ee640, remaining_tables=2, join_positions=0x6290002eec88, idx=1, disable_jbuf=false, record_count=2, pos=0x6290002ef550, loose_scan_pos=0x6290002ef690) at /data/src/11.0/sql/sql_select.cc:9093
 
#13 0x000055609822c14b in get_costs_for_tables (join=0x6290000fceb8, remaining_tables=2, idx=1, record_count=2, trace_one_table=0x7f07034772e0, pos=0x6290002eec48, store_position=0x7f0703477200, allowed_tables=0x7f0703477240, stop_on_eq_ref=false) at /data/src/11.0/sql/sql_select.cc:11047
 
#14 0x000055609822d402 in best_extension_by_limited_search (join=0x6290000fceb8, remaining_tables=2, idx=1, record_count=2, read_time=0.011180800000000001, search_depth=61, use_cond_selectivity=4, processed_eq_ref_tables=0x7f0703477600) at /data/src/11.0/sql/sql_select.cc:11304
 
#15 0x000055609822ee50 in best_extension_by_limited_search (join=0x6290000fceb8, remaining_tables=3, idx=0, record_count=1, read_time=0, search_depth=62, use_cond_selectivity=4, processed_eq_ref_tables=0x7f07034778d0) at /data/src/11.0/sql/sql_select.cc:11529
 
#16 0x0000556098227e78 in greedy_search (join=0x6290000fceb8, remaining_tables=3, search_depth=62, use_cond_selectivity=4) at /data/src/11.0/sql/sql_select.cc:10275
 
#17 0x000055609822596c in choose_plan (join=0x6290000fceb8, join_tables=3, emb_sjm_nest=0x0) at /data/src/11.0/sql/sql_select.cc:9796
 
#18 0x000055609820c35a in make_join_statistics (join=0x6290000fceb8, tables_list=..., keyuse_array=0x6290000fd220) at /data/src/11.0/sql/sql_select.cc:6085
 
#19 0x00005560981e7dda in JOIN::optimize_inner (this=0x6290000fceb8) at /data/src/11.0/sql/sql_select.cc:2577
 
#20 0x00005560981e0e47 in JOIN::optimize (this=0x6290000fceb8) at /data/src/11.0/sql/sql_select.cc:1905
 
#21 0x0000556098202ab3 in mysql_select (thd=0x62b00017a218, tables=0x6290000fa948, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2164525824, result=0x6290000fce88, unit=0x62b00017e660, select_lex=0x6290000fa2f0) at /data/src/11.0/sql/sql_select.cc:5144
 
#22 0x00005560981d26e8 in handle_select (thd=0x62b00017a218, lex=0x62b00017e588, result=0x6290000fce88, setup_tables_done_option=0) at /data/src/11.0/sql/sql_select.cc:616
 
#23 0x00005560980f7ac4 in execute_sqlcom_select (thd=0x62b00017a218, all_tables=0x6290000fa948) at /data/src/11.0/sql/sql_parse.cc:6279
 
#24 0x00005560980e5eeb in mysql_execute_command (thd=0x62b00017a218, is_called_from_prepared_stmt=false) at /data/src/11.0/sql/sql_parse.cc:3949
 
#25 0x0000556098102574 in mysql_parse (thd=0x62b00017a218, rawbuf=0x6290000fa238 "SELECT * FROM t1 JOIN t2 ON t2.b = t1.a", length=39, parser_state=0x7f0703479a20) at /data/src/11.0/sql/sql_parse.cc:8014
 
#26 0x00005560980d8460 in dispatch_command (command=COM_QUERY, thd=0x62b00017a218, packet=0x629000285219 "SELECT * FROM t1 JOIN t2 ON t2.b = t1.a", packet_length=39, blocking=true) at /data/src/11.0/sql/sql_parse.cc:1894
 
#27 0x00005560980d5183 in do_command (thd=0x62b00017a218, blocking=true) at /data/src/11.0/sql/sql_parse.cc:1407
 
#28 0x000055609859d00c in do_handle_one_connection (connect=0x6080000033b8, put_in_cache=true) at /data/src/11.0/sql/sql_connect.cc:1416
 
#29 0x000055609859c9cd in handle_one_connection (arg=0x608000003338) at /data/src/11.0/sql/sql_connect.cc:1318
 
#30 0x0000556099191324 in pfs_spawn_thread (arg=0x617000008218) at /data/src/11.0/storage/perfschema/pfs.cc:2201
 
#31 0x00007f071aea7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#32 0x00007f071af285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

Reproducible on 11.0 with at least MyISAM, InnoDB, Aria.
The failure started happening on 11.0 after this commit in 11.0:

commit 3bdc5542dc10693ec7a28add487747f43f580553
 
Author: Monty <monty@mariadb.org>
 
Date:   Mon Mar 13 02:40:24 2023 +0200
 
 
 
    MDEV-30812: Improve output cardinality estimates for hash join
 
    
    Introduces @@optimizer_switch flag: hash_join_cardinality

Update:
After another commit in 10.6 which was pushed into 10.6 main later (despite the commit date) it is also reproducible on 10.6+

commit 4329ec5d3b109cb0bcbee151b5800dc7b19d1945 (origin/bb-10.6-mdev30812)
 
Author: Sergei Petrunia
 
Date:   Thu Mar 9 17:04:07 2023 +0300
 
 
 
    MDEV-30812: Improve output cardinality estimates for hash join

however for 10.6 it requires hash_join_cardinality=on as it's not turned on by default like in 11.0.
See also test case in Roel's comment, apparently it doesn't require open table magic.

 Comments   
Comment by Roel Van de Paar [ 2023-06-13 ]

Ran into the same. Present in 11.0 only (not in 11.1). MTR Testcase:

--source include/have_innodb.inc
 
CREATE TABLE t1(c1 INT) ENGINE=InnoDB; 
CREATE TABLE t2(c2 INT) ENGINE=InnoDB;
 
SELECT * FROM t1;
 
SET join_cache_level=3,use_stat_tables='never';
 
SELECT * FROM (SELECT * FROM t2 JOIN t1) AS ta NATURAL JOIN (SELECT * FROM t2 NATURAL JOIN t1) AS tb;

Leads to:

11.0.2 368dd22a816f3b437bccd0b9ff28b9de9b1abf0a (Debug)

 
mariadbd: /test/11.0_dbg/sql/sql_statistics.cc:4150: bool is_eits_usable(Field*): Assertion `field->orig_table->stats_is_read' failed.

11.0.2 368dd22a816f3b437bccd0b9ff28b9de9b1abf0a (Debug)

 
Core was generated by `/test/MD120523-mariadb-11.0.2-linux-x86_64-dbg/bin/mariadbd --no-defaults --cor'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __pthread_kill_implementation (no_tid=0, signo=6, threadid=22856945555008)
 
    at ./nptl/pthread_kill.c:44
 
[Current thread is 1 (Thread 0x14c9cc1ed640 (LWP 964941))]
 
(gdb) bt
 
#0  __pthread_kill_implementation (no_tid=0, signo=6, threadid=22856945555008) at ./nptl/pthread_kill.c:44
 
#1  __pthread_kill_internal (signo=6, threadid=22856945555008) at ./nptl/pthread_kill.c:78
 
#2  __GI___pthread_kill (threadid=22856945555008, signo=signo@entry=6) at ./nptl/pthread_kill.c:89
 
#3  0x000014c9f1842476 in __GI_raise (sig=sig@entry=6) at ../sysdeps/posix/raise.c:26
 
#4  0x000014c9f18287f3 in __GI_abort () at ./stdlib/abort.c:79
 
#5  0x000014c9f182871b in __assert_fail_base (fmt=0x14c9f19dd150 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55e9417d7ee0 "field->orig_table->stats_is_read", file=0x55e9417d7c70 "/test/11.0_dbg/sql/sql_statistics.cc", line=4150, function=<optimized out>) at ./assert/assert.c:92
 
#6  0x000014c9f1839e96 in __GI___assert_fail (assertion=0x55e9417d7ee0 "field->orig_table->stats_is_read", file=0x55e9417d7c70 "/test/11.0_dbg/sql/sql_statistics.cc", line=4150, function=0x55e9417d7ba2 "bool is_eits_usable(Field*)") at ./assert/assert.c:101
 
#7  0x000055e940d60e7d in is_eits_usable (field=field@entry=0x14c9680769f8) at /test/11.0_dbg/sql/sql_statistics.cc:4150
 
#8  0x000055e940ceb8ff in hash_join_fanout (join=join@entry=0x14c96806fbc8, tab=tab@entry=0x14c9680800b8, remaining_tables=remaining_tables@entry=10, rnd_records=rnd_records@entry=1, hj_start_key=hj_start_key@entry=0x14c96807b338, stats_found=stats_found@entry=0x14c9cc1ea8f8) at /test/11.0_dbg/sql/sql_select.cc:8146
 
#9  0x000055e940cee8d4 in best_access_path (join=0x14c96806fbc8, s=s@entry=0x14c9680800b8, remaining_tables=remaining_tables@entry=10, join_positions=0x14c968080730, idx=idx@entry=2, disable_jbuf=disable_jbuf@entry=false, record_count=record_count@entry=1, pos=0x14c968082178, loose_scan_pos=0x14c9680822b8) at /test/11.0_dbg/sql/sql_select.cc:9093
 
#10 0x000055e940cf1951 in get_costs_for_tables (join=join@entry=0x14c96806fbc8, remaining_tables=remaining_tables@entry=10, idx=idx@entry=2, record_count=record_count@entry=1, trace_one_table=trace_one_table@entry=0x14c9cc1eac50, pos=0x14c9680806d0, pos@entry=0x14c9680806c8, store_position=store_position@entry=0x14c9cc1eac38, allowed_tables=<optimized out>, stop_on_eq_ref=<optimized out>) at /test/11.0_dbg/sql/sql_select.cc:11047
 
#11 0x000055e940cfa933 in best_extension_by_limited_search (join=join@entry=0x14c96806fbc8, remaining_tables=remaining_tables@entry=10, idx=idx@entry=2, record_count=record_count@entry=1, read_time=<optimized out>, search_depth=search_depth@entry=60, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x14c9cc1eadf0) at /test/11.0_dbg/sql/sql_select.cc:11304
 
#12 0x000055e940cfc194 in best_extension_by_limited_search (join=join@entry=0x14c96806fbc8, remaining_tables=remaining_tables@entry=14, idx=idx@entry=1, record_count=record_count@entry=1, read_time=<optimized out>, search_depth=search_depth@entry=61, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x14c9cc1eafc0) at /test/11.0_dbg/sql/sql_select.cc:11529
 
#13 0x000055e940cfc194 in best_extension_by_limited_search (join=join@entry=0x14c96806fbc8, remaining_tables=remaining_tables@entry=15, idx=idx@entry=0, record_count=record_count@entry=1, read_time=read_time@entry=0, search_depth=search_depth@entry=62, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x14c9cc1eb120) at /test/11.0_dbg/sql/sql_select.cc:11529
 
#14 0x000055e940cfdf54 in greedy_search (use_cond_selectivity=4, search_depth=62, remaining_tables=15, join=0x14c96806fbc8) at /test/11.0_dbg/sql/sql_select.cc:10275
 
#15 choose_plan (join=join@entry=0x14c96806fbc8, join_tables=<optimized out>, emb_sjm_nest=emb_sjm_nest@entry=0x0) at /test/11.0_dbg/sql/sql_select.cc:9796
 
#16 0x000055e940d24541 in make_join_statistics (join=join@entry=0x14c96806fbc8, tables_list=@0x14c9680134c0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14c9680795f8, last = 0x14c968078f90, elements = 4}, <No data fields>}, keyuse_array=keyuse_array@entry=0x14c96806ff30) at /test/11.0_dbg/sql/sql_select.cc:6085
 
#17 0x000055e940d2ba31 in JOIN::optimize_inner (this=this@entry=0x14c96806fbc8) at /test/11.0_dbg/sql/sql_select.cc:2577
 
#18 0x000055e940d2bf34 in JOIN::optimize (this=this@entry=0x14c96806fbc8) at /test/11.0_dbg/sql/sql_select.cc:1905
 
#19 0x000055e940d2c03d in mysql_select (thd=thd@entry=0x14c968000d58, tables=<optimized out>, fields=@0x14c968013560: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14c968013880, last = 0x14c96807a5b8, elements = 2}, <No data fields>}, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2164525824, result=0x14c968016cf8, unit=0x14c968004fa0, select_lex=0x14c9680132a8) at /test/11.0_dbg/sql/sql_select.cc:5144
 
#20 0x000055e940d2c822 in handle_select (thd=thd@entry=0x14c968000d58, lex=lex@entry=0x14c968004ec8, result=result@entry=0x14c968016cf8, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/11.0_dbg/sql/sql_select.cc:616
 
#21 0x000055e940c8fc09 in execute_sqlcom_select (thd=thd@entry=0x14c968000d58, all_tables=0x14c968015d88) at /test/11.0_dbg/sql/sql_parse.cc:6279
 
#22 0x000055e940c9b23f in mysql_execute_command (thd=thd@entry=0x14c968000d58, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/11.0_dbg/sql/sql_parse.cc:3949
 
#23 0x000055e940ca2599 in mysql_parse (thd=thd@entry=0x14c968000d58, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14c9cc1ec240) at /test/11.0_dbg/sql/sql_parse.cc:8014
 
#24 0x000055e940ca472d in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14c968000d58, packet=packet@entry=0x14c96800ae49 "SELECT * FROM (SELECT * FROM t2 JOIN t1) AS ta NATURAL JOIN (SELECT * FROM t2 NATURAL JOIN t1) AS tb", packet_length=packet_length@entry=100, blocking=blocking@entry=true) at /test/11.0_dbg/sql/sql_class.h:242
 
#25 0x000055e940ca658a in do_command (thd=0x14c968000d58, blocking=blocking@entry=true) at /test/11.0_dbg/sql/sql_parse.cc:1407
 
#26 0x000055e940dfb332 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55e94383b638, put_in_cache=put_in_cache@entry=true) at /test/11.0_dbg/sql/sql_connect.cc:1416
 
#27 0x000055e940dfb591 in handle_one_connection (arg=0x55e94383b638) at /test/11.0_dbg/sql/sql_connect.cc:1318
 
#28 0x000014c9f1894b43 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#29 0x000014c9f1926a00 in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

Additionally, I noticed some some stack corruption when using MTR on a UB+ASAN debug build:

11.0.2 368dd22a816f3b437bccd0b9ff28b9de9b1abf0a (Debug, UBASAN)

 
Thread 13 (LWP 3101147):
 
#0  0x0000000000000000 in ?? ()
 
Backtrace stopped: Cannot access memory at address 0x0

Comment by Roel Van de Paar [ 2023-06-13 ]

The following slight testcase variation (hash_join_cardinality turned on):

--source include/have_innodb.inc
 
CREATE TABLE t1(c1 INT) ENGINE=InnoDB; 
CREATE TABLE t2(c2 INT) ENGINE=InnoDB;
 
SELECT * FROM t1;
 
SET join_cache_level=3,use_stat_tables='never',optimizer_switch="hash_join_cardinality=on";
 
SELECT * FROM (SELECT * FROM t2 JOIN t1) AS ta NATURAL JOIN (SELECT * FROM t2 NATURAL JOIN t1) AS tb;

Produces a slightly different assert and affects more versions:

10.11.4 7c9f275ee4cd59212a85827626fbca2615d144d5 (Debug)

 
mariadbd: /test/10.11_dbg/sql/sql_statistics.cc:4111: bool is_eits_usable(Field*): Assertion `field->table->stats_is_read' failed.

10.11.4 7c9f275ee4cd59212a85827626fbca2615d144d5 (Debug)

 
Core was generated by `/test/MD120523-mariadb-10.11.4-linux-x86_64-dbg/bin/mariadbd --no-defaults --co'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __pthread_kill_implementation (no_tid=0, signo=6, threadid=23433566979648)
 
    at ./nptl/pthread_kill.c:44
 
[Current thread is 1 (Thread 0x15500d6f8640 (LWP 2588379))]
 
(gdb) bt
 
#0  __pthread_kill_implementation (no_tid=0, signo=6, threadid=23433566979648) at ./nptl/pthread_kill.c:44
 
#1  __pthread_kill_internal (signo=6, threadid=23433566979648) at ./nptl/pthread_kill.c:78
 
#2  __GI___pthread_kill (threadid=23433566979648, signo=signo@entry=6) at ./nptl/pthread_kill.c:89
 
#3  0x0000155032c42476 in __GI_raise (sig=sig@entry=6) at ../sysdeps/posix/raise.c:26
 
#4  0x0000155032c287f3 in __GI_abort () at ./stdlib/abort.c:79
 
#5  0x0000155032c2871b in __assert_fail_base (fmt=0x155032ddd150 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x559b1b5f897d "field->table->stats_is_read", file=0x559b1b5f8a68 "/test/10.11_dbg/sql/sql_statistics.cc", line=4111, function=<optimized out>) at ./assert/assert.c:92
 
#6  0x0000155032c39e96 in __GI___assert_fail (assertion=0x559b1b5f897d "field->table->stats_is_read", file=0x559b1b5f8a68 "/test/10.11_dbg/sql/sql_statistics.cc", line=4111, function=0x559b1b5f8999 "bool is_eits_usable(Field*)") at ./assert/assert.c:101
 
#7  0x0000559b1ab667b7 in is_eits_usable (field=field@entry=0x154fc80764a8) at /test/10.11_dbg/sql/sql_statistics.cc:4111
 
#8  0x0000559b1aaf6528 in hash_join_fanout (join=join@entry=0x154fc806f928, s=s@entry=0x154fc807f9e0, remaining_tables=remaining_tables@entry=10, rnd_records=rnd_records@entry=1, hj_start_key=hj_start_key@entry=0x154fc807ad30, stats_found=stats_found@entry=0x15500d6f5936) at /test/10.11_dbg/sql/sql_select.cc:7909
 
#9  0x0000559b1aafce4d in best_access_path (join=0x154fc806f928, s=s@entry=0x154fc807f9e0, remaining_tables=remaining_tables@entry=10, join_positions=0x154fc8080020, idx=idx@entry=2, disable_jbuf=disable_jbuf@entry=false, record_count=record_count@entry=1, pos=0x154fc8081678, loose_scan_pos=0x154fc8081788) at /test/10.11_dbg/sql/sql_select.cc:8646
 
#10 0x0000559b1aafee51 in get_costs_for_tables (join=join@entry=0x154fc806f928, remaining_tables=remaining_tables@entry=10, idx=idx@entry=2, record_count=record_count@entry=1, trace_one_table=trace_one_table@entry=0x15500d6f5c40, pos=0x154fc807ffc0, pos@entry=0x154fc807ffb8, store_position=store_position@entry=0x15500d6f5c28, allowed_tables=<optimized out>, stop_on_eq_ref=<optimized out>) at /test/10.11_dbg/sql/sql_select.cc:10345
 
#11 0x0000559b1ab038ed in best_extension_by_limited_search (join=join@entry=0x154fc806f928, remaining_tables=remaining_tables@entry=10, idx=idx@entry=2, record_count=record_count@entry=1, read_time=<optimized out>, search_depth=search_depth@entry=60, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x15500d6f5df0) at /test/10.11_dbg/sql/sql_select.cc:10604
 
#12 0x0000559b1ab0462a in best_extension_by_limited_search (join=join@entry=0x154fc806f928, remaining_tables=remaining_tables@entry=14, idx=idx@entry=1, record_count=record_count@entry=1, read_time=<optimized out>, search_depth=search_depth@entry=61, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x15500d6f5fd0) at /test/10.11_dbg/sql/sql_select.cc:10808
 
#13 0x0000559b1ab0462a in best_extension_by_limited_search (join=join@entry=0x154fc806f928, remaining_tables=remaining_tables@entry=15, idx=idx@entry=0, record_count=record_count@entry=1, read_time=read_time@entry=0, search_depth=search_depth@entry=62, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x15500d6f6120) at /test/10.11_dbg/sql/sql_select.cc:10808
 
#14 0x0000559b1ab059f8 in greedy_search (use_cond_selectivity=<optimized out>, search_depth=62, remaining_tables=15, join=0x154fc806f928) at /test/10.11_dbg/sql/sql_select.cc:9591
 
#15 choose_plan (join=join@entry=0x154fc806f928, join_tables=<optimized out>) at /test/10.11_dbg/sql/sql_select.cc:9157
 
#16 0x0000559b1ab2ae3e in make_join_statistics (join=join@entry=0x154fc806f928, tables_list=@0x154fc80134b0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x154fc8079058, last = 0x154fc8070420, elements = 4}, <No data fields>}, keyuse_array=keyuse_array@entry=0x154fc806fc80) at /test/10.11_dbg/sql/sql_select.cc:6014
 
#17 0x0000559b1ab31eea in JOIN::optimize_inner (this=this@entry=0x154fc806f928) at /test/10.11_dbg/sql/sql_select.cc:2539
 
#18 0x0000559b1ab323de in JOIN::optimize (this=this@entry=0x154fc806f928) at /test/10.11_dbg/sql/sql_select.cc:1875
 
#19 0x0000559b1ab324ce in mysql_select (thd=thd@entry=0x154fc8000d58, tables=0x154fc8015d68, fields=@0x154fc8013550: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x154fc8013868, last = 0x154fc8079fd0, elements = 2}, <No data fields>}, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2164525824, result=0x154fc8016cc8, unit=0x154fc8004f98, select_lex=0x154fc8013298) at /test/10.11_dbg/sql/sql_select.cc:5084
 
#20 0x0000559b1ab32c94 in handle_select (thd=thd@entry=0x154fc8000d58, lex=lex@entry=0x154fc8004ec0, result=result@entry=0x154fc8016cc8, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.11_dbg/sql/sql_select.cc:586
 
#21 0x0000559b1aa9b6b1 in execute_sqlcom_select (thd=thd@entry=0x154fc8000d58, all_tables=0x154fc8015d68) at /test/10.11_dbg/sql/sql_parse.cc:6279
 
#22 0x0000559b1aaa6c51 in mysql_execute_command (thd=thd@entry=0x154fc8000d58, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.11_dbg/sql/sql_parse.cc:3949
 
#23 0x0000559b1aaadfc0 in mysql_parse (thd=thd@entry=0x154fc8000d58, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x15500d6f7240) at /test/10.11_dbg/sql/sql_parse.cc:8017
 
#24 0x0000559b1aab0154 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x154fc8000d58, packet=packet@entry=0x154fc800ae39 "SELECT * FROM (SELECT * FROM t2 JOIN t1) AS ta NATURAL JOIN (SELECT * FROM t2 NATURAL JOIN t1) AS tb", packet_length=packet_length@entry=100, blocking=blocking@entry=true) at /test/10.11_dbg/sql/sql_class.h:243
 
#25 0x0000559b1aab2030 in do_command (thd=0x154fc8000d58, blocking=blocking@entry=true) at /test/10.11_dbg/sql/sql_parse.cc:1407
 
#26 0x0000559b1ac00248 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x559b1eba55b8, put_in_cache=put_in_cache@entry=true) at /test/10.11_dbg/sql/sql_connect.cc:1416
 
#27 0x0000559b1ac004a7 in handle_one_connection (arg=0x559b1eba55b8) at /test/10.11_dbg/sql/sql_connect.cc:1318
 
#28 0x0000155032c94b43 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#29 0x0000155032d26a00 in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

Bug confirmed present in:
MariaDB: 10.6.14 (dbg), 10.9.7 (dbg), 10.10.5 (dbg), 10.11.4 (dbg), 11.0.2 (dbg)

Across versions, two slightly different stacks are seen with the testcase:

field->table->stats_is_read|SIGABRT|is_eits_usable|hash_join_fanout|best_access_path|best_extension_by_limited_search
 
field->table->stats_is_read|SIGABRT|is_eits_usable|hash_join_fanout|best_access_path|get_costs_for_tables

This can be compared with the original 11.0-only issue which produced:

field->orig_table->stats_is_read|SIGABRT|is_eits_usable|hash_join_fanout|best_access_path|get_costs_for_tables

Comment by Roel Van de Paar [ 2023-06-30 ]

Another somewhat different stack (optimize_straight_join) with this testcase:

--source include/have_innodb.inc
 
CREATE TABLE t (a INT) ENGINE=InnoDB;
 
SELECT * FROM t;
 
SET JOIN_cache_level=4, use_stat_tables=NEVER;
 
SELECT STRAIGHT_JOIN * FROM (t AS x JOIN t AS y) JOIN t AS z ON z.a=x.a;

Also crashes 10.6 when prefixing testcase with

SET optimizer_switch="hash_join_cardinality=on";

Gives the following UniqueID's/stacks across versions (10.6-11.1, debug builds):

field->orig_table->stats_is_read|SIGABRT|is_eits_usable|hash_join_fanout|best_access_path|optimize_straight_join
 
field->table->stats_is_read|SIGABRT|is_eits_usable|hash_join_fanout|best_access_path|optimize_straight_join

Generated at Thu Feb 08 10:22:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.