Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
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.
Attachments
Issue Links
- is caused by
-
MDEV-26974 Improve selectivity and related costs in optimizer
-
- Closed
-
Ran into the same. Present in 11.0 only (not in 11.1). MTR Testcase:
--source include/have_innodb.inc
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