[MDEV-25896] SEL_ARG weight mismatch, Assertion `computed_weight == weight' in SEL_ARG::verify_weight Created: 2021-06-11  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.5, 10.6, 10.11, 11.0

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None


 Description    

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY (pk), KEY(a,b)) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES (1,1,1),(2,2,2);
 
 
 
SET OPTIMIZER_MAX_SEL_ARG_WEIGHT= 10;
 
 
 
SELECT a FROM t1 WHERE a < 50 AND pk IN (7,3) AND b IN (35,3) OR a = 17;
 
 
 
# Cleanup
 
DROP TABLE t1;

10.5 2c6d5c92

 
2021-06-11 15:34:40 4 [ERROR] SEL_ARG weight mismatch: computed 3 have 7
 
 
 
mariadbd: /data/src/10.5/sql/opt_range.cc:10086: uint SEL_ARG::verify_weight(): Assertion `computed_weight == weight' failed.
 
210611 15:34:40 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007fe40717cf36 in __GI___assert_fail (assertion=0x565492b1e33e "computed_weight == weight", file=0x565492b1c400 "/data/src/10.5/sql/opt_range.cc", line=10086, function=0x565492b1e358 "uint SEL_ARG::verify_weight()") at assert.c:101
 
#8  0x00005654920420c3 in SEL_ARG::verify_weight (this=0x7fe3d4054e60) at /data/src/10.5/sql/opt_range.cc:10086
 
#9  0x0000565492042150 in key_or_with_limit (param=0x7fe4003d59b0, keyno=1, key1=0x7fe3d4054698, key2=0x7fe3d4054c88) at /data/src/10.5/sql/opt_range.cc:10107
 
#10 0x0000565492041194 in tree_or (param=0x7fe4003d59b0, tree1=0x7fe3d4054608, tree2=0x7fe3d4054bf8) at /data/src/10.5/sql/opt_range.cc:9750
 
#11 0x000056549203cfe4 in Item_cond::get_mm_tree (this=0x7fe3d4017348, param=0x7fe4003d59b0, cond_ptr=0x7fe3d4019e30) at /data/src/10.5/sql/opt_range.cc:8360
 
#12 0x000056549202ea91 in SQL_SELECT::test_quick_select (this=0x7fe3d4019e28, thd=0x7fe3d4000db8, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false, remove_false_parts_of_where=true, only_single_index_range_scan=false) at /data/src/10.5/sql/opt_range.cc:2881
 
#13 0x0000565491b75e55 in get_quick_record_count (thd=0x7fe3d4000db8, select=0x7fe3d4019e28, table=0x7fe3d4226aa8, keys=0x7fe3d4018be8, limit=18446744073709551615) at /data/src/10.5/sql/sql_select.cc:4805
 
#14 0x0000565491b787ee in make_join_statistics (join=0x7fe3d4017d80, tables_list=..., keyuse_array=0x7fe3d4018070) at /data/src/10.5/sql/sql_select.cc:5537
 
#15 0x0000565491b6cc18 in JOIN::optimize_inner (this=0x7fe3d4017d80) at /data/src/10.5/sql/sql_select.cc:2294
 
#16 0x0000565491b6a705 in JOIN::optimize (this=0x7fe3d4017d80) at /data/src/10.5/sql/sql_select.cc:1666
 
#17 0x0000565491b75b34 in mysql_select (thd=0x7fe3d4000db8, tables=0x7fe3d4015a10, fields=..., conds=0x7fe3d4017348, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fe3d4017d58, unit=0x7fe3d4004f70, select_lex=0x7fe3d40153f8) at /data/src/10.5/sql/sql_select.cc:4747
 
#18 0x0000565491b65531 in handle_select (thd=0x7fe3d4000db8, lex=0x7fe3d4004ea8, result=0x7fe3d4017d58, setup_tables_done_option=0) at /data/src/10.5/sql/sql_select.cc:443
 
#19 0x0000565491b27a73 in execute_sqlcom_select (thd=0x7fe3d4000db8, all_tables=0x7fe3d4015a10) at /data/src/10.5/sql/sql_parse.cc:6310
 
#20 0x0000565491b1ed9b in mysql_execute_command (thd=0x7fe3d4000db8) at /data/src/10.5/sql/sql_parse.cc:4006
 
#21 0x0000565491b2c922 in mysql_parse (thd=0x7fe3d4000db8, rawbuf=0x7fe3d4015310 "SELECT a FROM t1 WHERE a < 50 AND pk IN (7,3) AND b IN (35,3) OR a = 17", length=71, parser_state=0x7fe4003d7490, is_com_multi=false, is_next_command=false) at /data/src/10.5/sql/sql_parse.cc:8096
 
#22 0x0000565491b188cb in dispatch_command (command=COM_QUERY, thd=0x7fe3d4000db8, packet=0x7fe3d400b5c9 "SELECT a FROM t1 WHERE a < 50 AND pk IN (7,3) AND b IN (35,3) OR a = 17", packet_length=71, is_com_multi=false, is_next_command=false) at /data/src/10.5/sql/sql_parse.cc:1891
 
#23 0x0000565491b170bd in do_command (thd=0x7fe3d4000db8) at /data/src/10.5/sql/sql_parse.cc:1370
 
#24 0x0000565491cc6fb0 in do_handle_one_connection (connect=0x565495dcf958, put_in_cache=true) at /data/src/10.5/sql/sql_connect.cc:1410
 
#25 0x0000565491cc6d13 in handle_one_connection (arg=0x565495e8c2e8) at /data/src/10.5/sql/sql_connect.cc:1312
 
#26 0x000056549222bd79 in pfs_spawn_thread (arg=0x565495dcd168) at /data/src/10.5/storage/perfschema/pfs.cc:2201
 
#27 0x00007fe407694609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#28 0x00007fe407268293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Reproducible on 10.5, 10.6. Not applicable to 10.4.
No obvious problem on a non-debug build (no error message in the log, either).
Not reproducible with the test case above and MyISAM/Aria instead of InnoDB.
EXPLAIN on debug build also fails.
EXPLAIN from a non-debug build:

EXPLAIN EXTENDED SELECT a FROM t1 WHERE a < 50 AND pk IN (7,3) AND b IN (35,3) OR a = 17;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	SIMPLE	t1	range	PRIMARY,a	a	5	NULL	2	100.00	Using where; Using index
 
Warnings:
 
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 50 and `test`.`t1`.`pk` in (7,3) and `test`.`t1`.`b` in (35,3) or `test`.`t1`.`a` = 17



 Comments   
Comment by Sergei Petrunia [ 2022-08-07 ]

This is not fixed by fix for MDEV-29242.

Comment by Sergei Petrunia [ 2022-08-08 ]

Ok, it's a genuine problem that affects pruning

The SEL_ARG graph has this shape:

   part=0
 
 
 
  SEL_ARG1 ---\
 
    |          \
 
  SEL_ARG2      +-->SEL_ARG4--... 
    |          /
 
  SEL_ARG3 ---/

Both SEL_ARG1 and SEL_ARG2 have pointers to SEL_ARG4.

prune_sel_arg_graph(SEL_ARG1) calls
prune_sel_arg_graph(SEL_ARG4), and the last call
updates the weight:

          sel_arg->weight -= (old_weight - cur->next_key_part->weight);

The weight of the whole tree is changed accordingly.
But the problem is that SEL_ARG4 is present in the graph TWICE, so when
we're cutting off SEL_ARG4->next_key_part, that has an effect in multiple places.

An easy solution to this is to re-compute the weight from scratch after the cut-off.
But we need to make sure this doesn't cause a slowdown like in MDEV-25020...

Comment by Alice Sherepa [ 2023-01-11 ]

Similar problem, but with AND:

--source include/have_innodb.inc 
 
 
CREATE TABLE t1 ( id int, b1 int, i1 int, d1 TIMESTAMP, PRIMARY KEY (id,i1,b1,d1), KEY (id,b1,d1)) engine=innodb;
 
insert ignore into t1 values (1,2,3,'1970-01-01');
 
 
 
explain select * from t1
 
where 
 d1 between 'w' and 'th'
 
 and b1 not in (9, 255, 2) 
 and (id not in (-87, 1) and b1 not in (255, 1))
 
 and i1 in (213, 1)
 
 and id in (1, 255, 6);

10.5 cad33ded19e45a0187754

 
2023-01-11 16:59:40 4 [ERROR] SEL_ARG weight mismatch: computed 20 have 18
 
 
 
mariadbd: /10.5/src/sql/opt_range.cc:10088: uint SEL_ARG::verify_weight(): Assertion `computed_weight == weight' failed.
 
230111 16:49:40 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.5.19-MariaDB-debug-log
 
 
 
??:0(__assert_fail)[0x7f24c1f7cfd6]
 
sql/opt_range.cc:10090(SEL_ARG::verify_weight())[0x55bf2e0b561d]
 
sql/opt_range.cc:10072(SEL_ARG::verify_weight())[0x55bf2e0b5459]
 
sql/opt_range.cc:10131(key_and_with_limit(RANGE_OPT_PARAM*, unsigned int, SEL_ARG*, SEL_ARG*, unsigned int))[0x55bf2e0b573c]
 
sql/opt_range.cc:9209(and_range_trees(RANGE_OPT_PARAM*, SEL_TREE*, SEL_TREE*, SEL_TREE*))[0x55bf2e0b0305]
 
sql/opt_range.cc:9321(tree_and(RANGE_OPT_PARAM*, SEL_TREE*, SEL_TREE*))[0x55bf2e0b0c65]
 
sql/opt_range.cc:8341(Item_cond_and::get_mm_tree(RANGE_OPT_PARAM*, Item**))[0x55bf2e0a77b1]
 
sql/opt_range.cc:2885(SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool, bool))[0x55bf2e0837a2]
 
sql/sql_select.cc:4870(get_quick_record_count(THD*, SQL_SELECT*, TABLE*, Bitmap<64u> const*, unsigned long long))[0x55bf2d542e8b]
 
sql/sql_select.cc:5597(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55bf2d549d59]
 
sql/sql_select.cc:2337(JOIN::optimize_inner())[0x55bf2d5283cd]
 
sql/sql_select.cc:1695(JOIN::optimize())[0x55bf2d5218a7]
 
sql/sql_select.cc:4812(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55bf2d542590]
 
sql/sql_select.cc:27729(mysql_explain_union(THD*, st_select_lex_unit*, select_result*))[0x55bf2d5ec847]
 
sql/sql_parse.cc:6256(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55bf2d47a58f]
 
sql/sql_parse.cc:4008(mysql_execute_command(THD*))[0x55bf2d469de9]
 
sql/sql_parse.cc:8089(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55bf2d4860b3]
 
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55bf2d45bff6]
 
sql/sql_parse.cc:1375(do_command(THD*))[0x55bf2d458974]
 
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x55bf2d8aad2b]
 
sql/sql_connect.cc:1320(handle_one_connection)[0x55bf2d8aa68f]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55bf2e52350a]
 
nptl/pthread_create.c:478(start_thread)[0x7fd430d90609]
 
??:0(clone)[0x7fd430961133]
 
 
 
Query (0x62b0000852a8): explain select * from t1
 
where 
d1 between 'w' and 'th'
 
 and b1 not in (9, 255, 2) 
and (id not in (-87, 1) and b1 not in (255, 1))
 
and i1 in (213, 1)
 
and id in (1, 255, 6)

Generated at Thu Feb 08 09:41:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.