Details
Major Description
Note: the regression label and "blocker" priority are preliminary. The failure started happening after the below-mentioned commit, but since it added the assertion which is now failing, I don't know whether the underlying problem existed before. If the analysis confirms that it did, please remove the label and feel free to adjust the priority accordingly.
CREATE TABLE t1 (a INT, b INT); |
INSERT INTO t1 VALUES (1,2),(3,4); |
|
|
CREATE TABLE t2 (c INT); |
INSERT INTO t2 VALUES (5),(6); |
|
|
SET OPTIMIZER_USE_CONDITION_SELECTIVITY = 1; |
|
|
SELECT * FROM |
(SELECT t1.* FROM t1 WHERE t1.a IN (SELECT MAX(t2.c) FROM t2 JOIN t1)) AS sq1, |
(SELECT t2.* FROM t2 JOIN t1 ON (t1.b IN (SELECT t1.b FROM t2 STRAIGHT_JOIN t1))) AS sq2; |
|
|
# Cleanup
|
DROP TABLE t1, t2; |
|
10.6 bf0a54df |
mariadbd: /data/src/10.6/sql/sql_select.cc:7657: double apply_selectivity_for_table(JOIN_TAB*, uint, bool*): Assertion `s->table->opt_range_condition_rows <= s->found_records' failed.
|
230610 1:08:53 [ERROR] mysqld got signal 6 ;
|
|
|
#9 0x00007f2700853df2 in __GI___assert_fail (assertion=0x555878f72700 "s->table->opt_range_condition_rows <= s->found_records", file=0x555878f6df80 "/data/src/10.6/sql/sql_select.cc", line=7657, function=0x555878f72760 "double apply_selectivity_for_table(JOIN_TAB*, uint, bool*)") at ./assert/assert.c:101
|
#10 0x000055587700a8dd in apply_selectivity_for_table (s=0x62d00019af90, use_cond_selectivity=1, force_estimate=0x7f26f911b330) at /data/src/10.6/sql/sql_select.cc:7657
|
#11 0x00005558770107c3 in best_access_path (join=0x629000277270, s=0x62d00019af90, remaining_tables=63, join_positions=0x62d00019bd88, idx=0, disable_jbuf=true, record_count=1, pos=0x62d00019bd88, loose_scan_pos=0x7f26f911b790) at /data/src/10.6/sql/sql_select.cc:8661
|
#12 0x0000555877019818 in best_extension_by_limited_search (join=0x629000277270, remaining_tables=63, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=1) at /data/src/10.6/sql/sql_select.cc:10350
|
#13 0x00005558770154a2 in greedy_search (join=0x629000277270, remaining_tables=63, search_depth=62, prune_level=1, use_cond_selectivity=1) at /data/src/10.6/sql/sql_select.cc:9487
|
#14 0x00005558770131b9 in choose_plan (join=0x629000277270, join_tables=63) at /data/src/10.6/sql/sql_select.cc:9047
|
#15 0x0000555876ffd00e in make_join_statistics (join=0x629000277270, tables_list=..., keyuse_array=0x629000277598) at /data/src/10.6/sql/sql_select.cc:5977
|
#16 0x0000555876fd95d0 in JOIN::optimize_inner (this=0x629000277270) at /data/src/10.6/sql/sql_select.cc:2529
|
#17 0x0000555876fd25cb in JOIN::optimize (this=0x629000277270) at /data/src/10.6/sql/sql_select.cc:1868
|
#18 0x0000555876ff3e70 in mysql_select (thd=0x62b00007e218, tables=0x62b000089aa8, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x629000277240, unit=0x62b000082588, select_lex=0x62b000085410) at /data/src/10.6/sql/sql_select.cc:5069
|
#19 0x0000555876fc3d7e in handle_select (thd=0x62b00007e218, lex=0x62b0000824c0, result=0x629000277240, setup_tables_done_option=0) at /data/src/10.6/sql/sql_select.cc:559
|
#20 0x0000555876f28f4d in execute_sqlcom_select (thd=0x62b00007e218, all_tables=0x62b000089aa8) at /data/src/10.6/sql/sql_parse.cc:6273
|
#21 0x0000555876f17626 in mysql_execute_command (thd=0x62b00007e218, is_called_from_prepared_stmt=false) at /data/src/10.6/sql/sql_parse.cc:3949
|
#22 0x0000555876f33ec5 in mysql_parse (thd=0x62b00007e218, rawbuf=0x62b000085238 "SELECT * FROM\n(SELECT t1.* FROM t1 WHERE t1.a IN (SELECT MAX(t2.c) FROM t2 JOIN t1)) AS sq1, \n(SELECT t2.* FROM t2 JOIN t1 ON (t1.b IN (SELECT t1.b FROM t2 STRAIGHT_JOIN t1))) AS sq2", length=182, parser_state=0x7f26f911da30) at /data/src/10.6/sql/sql_parse.cc:8036
|
#23 0x0000555876f09d0c in dispatch_command (command=COM_QUERY, thd=0x62b00007e218, packet=0x62900025d219 "SELECT * FROM\n(SELECT t1.* FROM t1 WHERE t1.a IN (SELECT MAX(t2.c) FROM t2 JOIN t1)) AS sq1, \n(SELECT t2.* FROM t2 JOIN t1 ON (t1.b IN (SELECT t1.b FROM t2 STRAIGHT_JOIN t1))) AS sq2", packet_length=182, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1896
|
#24 0x0000555876f06a40 in do_command (thd=0x62b00007e218, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1409
|
#25 0x0000555877373afe in do_handle_one_connection (connect=0x608000002db8, put_in_cache=true) at /data/src/10.6/sql/sql_connect.cc:1416
|
#26 0x00005558773734bf in handle_one_connection (arg=0x608000002d38) at /data/src/10.6/sql/sql_connect.cc:1318
|
#27 0x0000555877fd0866 in pfs_spawn_thread (arg=0x617000005b98) at /data/src/10.6/storage/perfschema/pfs.cc:2201
|
#28 0x00007f27008a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
|
#29 0x00007f27009285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
|
Reproducible with at least MyISAM, InnoDB, Aria, on 10.6-10.11.
Also reproducible with empty tables.
Could not reproduce with the provided test case on 11.x.
The failure started happening after this commit in 10.6.13:
commit 4329ec5d3b109cb0bcbee151b5800dc7b19d1945
|
Author: Sergei Petrunia
|
Date: Thu Mar 9 17:04:07 2023 +0300
|
|
|
MDEV-30812: Improve output cardinality estimates for hash join
|
|
Introduce @@optimizer_switch flag: hash_join_cardinality
|