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
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Summary | Assertion `field->orig_table->stats_is_read' failed in is_eits_usable | Assertion `field->orig_table->stats_is_read' failed in is_eits_usable with EITS=NEVER and JOIN_CACHE_LEVEL > 2 |
| Summary | Assertion `field->orig_table->stats_is_read' failed in is_eits_usable with EITS=NEVER and JOIN_CACHE_LEVEL > 2 | Assertion `field->orig_table->stats_is_read' failed in is_eits_usable with JOIN_CACHE_LEVEL > 2 |
| Link |
This issue is caused by |
| Labels | regression | regression stack-smashing |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Labels | regression stack-smashing | not-11.1 regression stack-smashing |
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
|
| Labels | not-11.1 regression stack-smashing | regression stack-smashing |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.9 [ 26905 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 11.1 [ 28549 ] | |
| Affects Version/s | 10.6 [ 24028 ] | |
| Affects Version/s | 10.9 [ 26905 ] | |
| Affects Version/s | 10.10 [ 27530 ] | |
| Affects Version/s | 10.11 [ 27614 ] | |
| Affects Version/s | 11.1 [ 28549 ] | |
| 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.
{code:sql} 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; {code} {noformat:title=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 {noformat} Reproducible on 11.0 with at least MyISAM, InnoDB, Aria. The failure started happening on 11.0 after this commit in 11.0: {noformat} commit 3bdc5542dc10693ec7a28add487747f43f580553 Author: Monty <monty@mariadb.org> Date: Mon Mar 13 02:40:24 2023 +0200 Introduces @@optimizer_switch flag: hash_join_cardinality {noformat} |
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.
{code:sql} 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; {code} {noformat:title=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 {noformat} Reproducible on 11.0 with at least MyISAM, InnoDB, Aria. The failure started happening on 11.0 after this commit in 11.0: {noformat} commit 3bdc5542dc10693ec7a28add487747f43f580553 Author: Monty <monty@mariadb.org> Date: Mon Mar 13 02:40:24 2023 +0200 Introduces @@optimizer_switch flag: hash_join_cardinality {noformat} {color:red}Update:{color} After another commit in 10.6 it is also reproducible on 10.6+ {noformat} commit 4329ec5d3b109cb0bcbee151b5800dc7b19d1945 (origin/bb-10.6-mdev30812) Author: Sergei Petrunia Date: Thu Mar 9 17:04:07 2023 +0300 {noformat} 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|https://jira.mariadb.org/browse/MDEV-31243?focusedCommentId=261341&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-261341], apparently it doesn't require open table magic. |
| 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.
{code:sql} 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; {code} {noformat:title=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 {noformat} Reproducible on 11.0 with at least MyISAM, InnoDB, Aria. The failure started happening on 11.0 after this commit in 11.0: {noformat} commit 3bdc5542dc10693ec7a28add487747f43f580553 Author: Monty <monty@mariadb.org> Date: Mon Mar 13 02:40:24 2023 +0200 Introduces @@optimizer_switch flag: hash_join_cardinality {noformat} {color:red}Update:{color} After another commit in 10.6 it is also reproducible on 10.6+ {noformat} commit 4329ec5d3b109cb0bcbee151b5800dc7b19d1945 (origin/bb-10.6-mdev30812) Author: Sergei Petrunia Date: Thu Mar 9 17:04:07 2023 +0300 {noformat} 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|https://jira.mariadb.org/browse/MDEV-31243?focusedCommentId=261341&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-261341], apparently it doesn't require open table magic. |
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.
{code:sql} 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; {code} {noformat:title=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 {noformat} Reproducible on 11.0 with at least MyISAM, InnoDB, Aria. The failure started happening on 11.0 after this commit in 11.0: {noformat} commit 3bdc5542dc10693ec7a28add487747f43f580553 Author: Monty <monty@mariadb.org> Date: Mon Mar 13 02:40:24 2023 +0200 Introduces @@optimizer_switch flag: hash_join_cardinality {noformat} {color:red}Update:{color} 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+ {noformat} commit 4329ec5d3b109cb0bcbee151b5800dc7b19d1945 (origin/bb-10.6-mdev30812) Author: Sergei Petrunia Date: Thu Mar 9 17:04:07 2023 +0300 {noformat} 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|https://jira.mariadb.org/browse/MDEV-31243?focusedCommentId=261341&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-261341], apparently it doesn't require open table magic. |
| Labels | regression stack-smashing | affects-tests regression stack-smashing |
| Comment |
[ Another somewhat different stack ({{optimize_straight_join}}) with this testcase:
{code:sql} --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; {code} ] |
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
|
| Fix Version/s | 10.9 [ 26905 ] |
| Fix Version/s | 10.10 [ 27530 ] |
| Fix Version/s | 11.0 [ 28320 ] |
| Fix Version/s | 11.1 [ 28549 ] |
Ran into the same. Present in 11.0 only (not in 11.1). MTR Testcase:
--source include/have_innodb.incLeads 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:81Additionally, 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