Details
Description
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; |
|
10.6 fe89df42 |
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
|
The failure started happening when the new optimizer switch was introduced in MDEV-30812:
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
|
Attachments
Issue Links
- is caused by
-
MDEV-30812 Improve output cardinality estimates for hash join
-
- Closed
-
Activity
Its crashing here:
bool is_eits_usable(Field *field) |
{
|
Column_statistics* col_stats= field->read_stats;
|
|
// check if column_statistics was allocated for this field |
if (!col_stats) |
return false; |
|
=> DBUG_ASSERT(field->table->stats_is_read);
|
it's the derived table:
(gdb) p field->table->alias.Ptr
|
$103 = 0x7fffd401b038 "sq"
|
(gdb) set print obj
|
(gdb) p field->table->file
|
$106 = (ha_heap *) 0x7fffd409beb0
|
But we didn't go the "return false" route, because:
(gdb) p field->read_stats
|
$108 = (Column_statistics *) 0x7fffd4099668
|
which is surprising... derived table has EITS statistics?
... Yes, the EITS statistics was accidentally copied here:
(gdb) wher 7
|
#0 Field::make_new_field (this=0x7fffd408c958, root=0x7fffd409af20, new_table=0x7fffd409ac30, keep_type=false) at /home/psergey/dev-git2/10.6-cp/sql/field.cc:2515
|
#1 0x0000555556230749 in Field_varstring::make_new_field (this=0x7fffd408c958, root=0x7fffd409af20, new_table=0x7fffd409ac30, keep_type=false) at /home/psergey/dev-git2
|
#2 0x000055555621b367 in Field::create_tmp_field (this=0x7fffd408c958, mem_root=0x7fffd409af20, new_table=0x7fffd409ac30, maybe_null_arg=true) at /home/psergey/dev-git2
|
#3 0x0000555555f31040 in Item_field::create_tmp_field_from_item_field (this=0x7fffd4019088, root=0x7fffd409af20, new_table=0x7fffd409ac30, orig_item=0x0, param=0x7ffff0
|
#4 0x0000555555f311ae in Item_field::create_tmp_field_ex (this=0x7fffd4019088, root=0x7fffd409af20, table=0x7fffd409ac30, src=0x7ffff072af70, param=0x7ffff072af64) at /
|
#5 0x0000555555f31b07 in create_tmp_field (table=0x7fffd409ac30, item=0x7fffd4019088, copy_func=0x7ffff072b018, from_field=0x7fffd409b618, default_field=0x7fffd409b5e8,
|
#6 0x0000555555f337b0 in Create_tmp_table::add_fields (this=0x7ffff072b110, thd=0x7fffd4000d78, table=0x7fffd409ac30, param=0x7fffd401d570, fields=@0x7fffd401b018: {<ba
|
Field *Field::make_new_field(MEM_ROOT *root, TABLE *new_table,
|
bool keep_type __attribute__((unused)))
|
{
|
Field *tmp;
|
if (!(tmp= (Field*) memdup_root(root,(char*) this,size_of())))
|
return 0;
|
(gdb) p this->table->alias.Ptr
|
$65 = 0x7fffd4016cc8 "t2"
|
(gdb) p this->field_name
|
$66 = {str = 0x7fffd4081029 "b", length = 1}
|
(gdb) p this->read_stats
|
$67 = (Column_statistics *) 0x7fffd4099668
|
(gdb) p tmp->read_stats
|
$68 = (Column_statistics *) 0x7fffd4099668
|
That's why field->table->stats_is_read=false but field->read_stats has something...
commit a24f2bb50ba4a0dd4127455f7fcdfed584937f36 (HEAD -> bb-10.6-release, origin/bb-10.6-release)
|
Author: Sergei Petrunia <sergey@mariadb.com>
|
Date: Fri May 5 13:55:42 2023 +0300
|
|
|
MDEV-31199: Assertion `field->table->stats_is_read' fails with hash_join_cardinality=on
|
It's crashing, because it is trying to get the histogram of the column of the derived (i.e. temporary) table sq