[MDEV-31243] Assertion `field->orig_table->stats_is_read' failed in is_eits_usable with JOIN_CACHE_LEVEL > 2 - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
Fix Version/s: 10.6, 10.11
Component/s: Optimizer
Labels:

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

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2023-05-11 14:09

Elena Stepanova made changes - 2023-05-11 14:09

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

Elena Stepanova made changes - 2023-05-11 14:10

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

Elena Stepanova made changes - 2023-05-12 19:11

Link

This issue is caused by ~~MDEV-26974~~ [ ~~MDEV-26974~~ ]

Roel Van de Paar made changes - 2023-06-13 07:47

Labels

regression

regression stack-smashing

Roel Van de Paar made changes - 2023-06-13 07:47

Status

Open [ 1 ]

Confirmed [ 10101 ]

Roel Van de Paar made changes - 2023-06-13 07:59

Labels

regression stack-smashing

not-11.1 regression stack-smashing

Roel Van de Paar made changes - 2023-06-13 08:09

Labels

not-11.1 regression stack-smashing

regression stack-smashing

Elena Stepanova made changes - 2023-06-13 12:01

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 ~~MDEV-30812~~: Improve output cardinality estimates for hash join 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 ~~MDEV-30812~~: Improve output cardinality estimates for hash join 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 ~~MDEV-30812~~: Improve output cardinality estimates for hash join {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.

Elena Stepanova made changes - 2023-06-13 12:03

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

    ~~MDEV-30812~~: Improve output cardinality estimates for hash join

    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

    ~~MDEV-30812~~: Improve output cardinality estimates for hash join
{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

    ~~MDEV-30812~~: Improve output cardinality estimates for hash join

    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

    ~~MDEV-30812~~: Improve output cardinality estimates for hash join
{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.

Roel Van de Paar made changes - 2023-06-30 05:38

Labels

regression stack-smashing

affects-tests regression stack-smashing

Roel Van de Paar made changes - 2023-06-30 05:42

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} ]

Julien Fritsch made changes - 2023-11-28 10:24

Fix Version/s

10.9 [ 26905 ]

Julien Fritsch made changes - 2023-11-28 10:30

Fix Version/s

10.10 [ 27530 ]

Julien Fritsch made changes - 2024-05-03 14:40

Fix Version/s

11.0 [ 28320 ]

Julien Fritsch made changes - 2024-09-10 15:35

Fix Version/s

11.1 [ 28549 ]

People

Assignee:: Michael Widenius

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2023-05-11 14:09

Updated:: 2024-09-10 15:35

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration