Assertion `field->table->stats_is_read' fails with hash_join_cardinality=on

CREATE TABLE t1 (a VARCHAR(255));

INSERT INTO t1 VALUES ('u'),('uu');

CREATE TABLE t2 (b VARCHAR(255)) CHARACTER SET utf8mb4;

INSERT INTO t2 VALUES ('x'),('xx');

CREATE TABLE t3 (c VARCHAR(255));

INSERT INTO t3 VALUES ('z'),('zz');

ANALYZE TABLE t1, t2, t3 PERSISTENT FOR ALL; # Optional, fails either way

SET OPTIMIZER_SWITCH = 'hash_join_cardinality=on';

SET JOIN_CACHE_LEVEL = 3;

SELECT t1.* FROM t1 JOIN (SELECT DISTINCT b FROM t2 JOIN t3) sq ON sq.b = t1.a;

# Cleanup

DROP TABLE t1, t2, t3;

mariadbd: /data/src/10.6-bug/sql/sql_statistics.cc:3951: bool is_eits_usable(Field*): Assertion `field->table->stats_is_read' failed.

230505 12:42:59 [ERROR] mysqld got signal 6 ;

#9  0x00007f1331e53df2 in __GI___assert_fail (assertion=0x564a7d9ddf05 "field->table->stats_is_read", file=0x564a7d9ddb60 "/data/src/10.6-bug/sql/sql_statistics.cc", line=3951, function=0x564a7d9ddf21 "bool is_eits_usable(Field*)") at ./assert/assert.c:101

#10 0x0000564a7cc9da9a in is_eits_usable (field=0x7f131c07f240) at /data/src/10.6-bug/sql/sql_statistics.cc:3951

#11 0x0000564a7cc117a8 in hash_join_fanout (join=0x7f131c01a6c8, s=0x7f131c2290a8, remaining_tables=2, rnd_records=4, hj_start_key=0x7f131c229e70, stats_found=0x7f132c95e757) at /data/src/10.6-bug/sql/sql_select.cc:7798

#12 0x0000564a7cc14016 in best_access_path (join=0x7f131c01a6c8, s=0x7f131c2290a8, remaining_tables=2, join_positions=0x7f131c2296b0, idx=1, disable_jbuf=false, record_count=2, pos=0x7f131c2297f8, loose_scan_pos=0x7f132c95eb90) at /data/src/10.6-bug/sql/sql_select.cc:8531

#13 0x0000564a7cc18fe9 in best_extension_by_limited_search (join=0x7f131c01a6c8, remaining_tables=2, idx=1, record_count=2, read_time=2.4097656249999999, search_depth=61, prune_level=1, use_cond_selectivity=4) at /data/src/10.6-bug/sql/sql_select.cc:10334

#14 0x0000564a7cc196d4 in best_extension_by_limited_search (join=0x7f131c01a6c8, remaining_tables=3, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=4) at /data/src/10.6-bug/sql/sql_select.cc:10440

#15 0x0000564a7cc16d03 in greedy_search (join=0x7f131c01a6c8, remaining_tables=3, search_depth=62, prune_level=1, use_cond_selectivity=4) at /data/src/10.6-bug/sql/sql_select.cc:9471

#16 0x0000564a7cc15ee7 in choose_plan (join=0x7f131c01a6c8, join_tables=3) at /data/src/10.6-bug/sql/sql_select.cc:9031

#17 0x0000564a7cc0c767 in make_join_statistics (join=0x7f131c01a6c8, tables_list=..., keyuse_array=0x7f131c01a9f0) at /data/src/10.6-bug/sql/sql_select.cc:5963

#18 0x0000564a7cbffa21 in JOIN::optimize_inner (this=0x7f131c01a6c8) at /data/src/10.6-bug/sql/sql_select.cc:2509

#19 0x0000564a7cbfd244 in JOIN::optimize (this=0x7f131c01a6c8) at /data/src/10.6-bug/sql/sql_select.cc:1848

#20 0x0000564a7cc09145 in mysql_select (thd=0x7f131c000dc8, tables=0x7f131c015d78, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f131c01a6a0, unit=0x7f131c005138, select_lex=0x7f131c015758) at /data/src/10.6-bug/sql/sql_select.cc:5055

#21 0x0000564a7cbf79fd in handle_select (thd=0x7f131c000dc8, lex=0x7f131c005070, result=0x7f131c01a6a0, setup_tables_done_option=0) at /data/src/10.6-bug/sql/sql_select.cc:559

#22 0x0000564a7cbb5ab1 in execute_sqlcom_select (thd=0x7f131c000dc8, all_tables=0x7f131c015d78) at /data/src/10.6-bug/sql/sql_parse.cc:6273

#23 0x0000564a7cbac9e5 in mysql_execute_command (thd=0x7f131c000dc8, is_called_from_prepared_stmt=false) at /data/src/10.6-bug/sql/sql_parse.cc:3949

#24 0x0000564a7cbbaa8a in mysql_parse (thd=0x7f131c000dc8, rawbuf=0x7f131c015660 "SELECT t1.* FROM t1 JOIN (SELECT DISTINCT b FROM t2 JOIN t3) sq ON sq.b = t1.a", length=78, parser_state=0x7f132c960380) at /data/src/10.6-bug/sql/sql_parse.cc:8036

#25 0x0000564a7cba656b in dispatch_command (command=COM_QUERY, thd=0x7f131c000dc8, packet=0x7f131c00b8f9 "SELECT t1.* FROM t1 JOIN (SELECT DISTINCT b FROM t2 JOIN t3) sq ON sq.b = t1.a", packet_length=78, blocking=true) at /data/src/10.6-bug/sql/sql_parse.cc:1896

#26 0x0000564a7cba4ec4 in do_command (thd=0x7f131c000dc8, blocking=true) at /data/src/10.6-bug/sql/sql_parse.cc:1409

#27 0x0000564a7cd76eb4 in do_handle_one_connection (connect=0x564a805de298, put_in_cache=true) at /data/src/10.6-bug/sql/sql_connect.cc:1416

#28 0x0000564a7cd76c29 in handle_one_connection (arg=0x564a805b6938) at /data/src/10.6-bug/sql/sql_connect.cc:1318

#29 0x0000564a7d2cf666 in pfs_spawn_thread (arg=0x564a8059da88) at /data/src/10.6-bug/storage/perfschema/pfs.cc:2201

#30 0x00007f1331ea7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442

#31 0x00007f1331f285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

commit 4329ec5d3b109cb0bcbee151b5800dc7b19d1945 2eb7bf1ec301d43a7e72b8720f6ee153dc52b6c9

Author:     Sergei Petrunia

AuthorDate: Thu Mar 9 17:04:07 2023 +0300

Commit:     Sergei Petrunia

CommitDate: Fri Apr 28 16:24:08 2023 +0300

    MDEV-30812: Improve output cardinality estimates for hash join

    Introduce @@optimizer_switch flag: hash_join_cardinality