[MDEV-31681] [ERROR] Duplicated key: used_range_estimates, Assertion `is_uniq_key' failure with optimizer_trace Created: 2023-07-13  Updated: 2023-07-13

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 11.0, 11.1
Fix Version/s: 11.0, 11.1

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


 Description    

--source include/have_innodb.inc
 
 
 
CREATE TABLE t (pk INT PRIMARY KEY, a INT, KEY(a)) ENGINE=InnoDB;
 
INSERT INTO t VALUES (1,5),(2,1);
 
 
 
SET OPTIMIZER_TRACE= 'enabled=on';
 
SELECT * FROM t WHERE (7, 8) NOT IN (SELECT pk, a FROM t WHERE a != 9);
 
 
 
# Cleanup
 
DROP TABLE t;

11.0 51a85e7e

 
2023-07-13 19:03:28 4 [ERROR] Duplicated key: used_range_estimates
 
 
 
mariadbd: /data/src/11.0/sql/my_json_writer.cc:163: Json_writer& Json_writer::add_member(const char*, size_t): Assertion `is_uniq_key' failed.
 
230713 19:03:28 [ERROR] mysqld got signal 6 ;
 
 
 
#9  0x00007f74e5c53df2 in __GI___assert_fail (assertion=0x560c80cbc4a0 "is_uniq_key", file=0x560c80cbc0c0 "/data/src/11.0/sql/my_json_writer.cc", line=163, function=0x560c80cbc3c0 "Json_writer& Json_writer::add_member(const char*, size_t)") at ./assert/assert.c:101
 
#10 0x0000560c7efed2d3 in Json_writer::add_member (this=0x61200008ecc0, name=0x560c80a8b640 "used_range_estimates", len=20) at /data/src/11.0/sql/my_json_writer.cc:163
 
#11 0x0000560c7efecd19 in Json_writer::add_member (this=0x61200008ecc0, name=0x560c80a8b640 "used_range_estimates") at /data/src/11.0/sql/my_json_writer.cc:136
 
#12 0x0000560c7e6b4674 in Json_writer_object::add_member (this=0x7f74d60da750, name=0x560c80a8b640 "used_range_estimates") at /data/src/11.0/sql/my_json_writer.h:438
 
#13 0x0000560c7e6b4aa0 in Json_writer_object::add (this=0x7f74d60da750, name=0x560c80a8b640 "used_range_estimates", value=true) at /data/src/11.0/sql/my_json_writer.h:472
 
#14 0x0000560c7eaf7716 in best_access_path (join=0x62900022b870, s=0x62900022c378, remaining_tables=1, join_positions=0x62900022ca18, idx=0, disable_jbuf=true, record_count=1, pos=0x62900022ccc0, loose_scan_pos=0x62900022ce10) at /data/src/11.0/sql/sql_select.cc:8899
 
#15 0x0000560c7eb0494a in get_costs_for_tables (join=0x62900022b870, remaining_tables=1, idx=0, record_count=1, trace_one_table=0x7f74d60dae70, pos=0x62900022c9e8, store_position=0x7f74d60dad90, allowed_tables=0x7f74d60dadd0, stop_on_eq_ref=false) at /data/src/11.0/sql/sql_select.cc:11116
 
#16 0x0000560c7eb05c09 in best_extension_by_limited_search (join=0x62900022b870, remaining_tables=1, idx=0, record_count=1, read_time=0, search_depth=62, use_cond_selectivity=4, processed_eq_ref_tables=0x7f74d60db050) at /data/src/11.0/sql/sql_select.cc:11373
 
#17 0x0000560c7eb0065e in greedy_search (join=0x62900022b870, remaining_tables=1, search_depth=62, use_cond_selectivity=4) at /data/src/11.0/sql/sql_select.cc:10344
 
#18 0x0000560c7eafe0bc in choose_plan (join=0x62900022b870, join_tables=1, emb_sjm_nest=0x0) at /data/src/11.0/sql/sql_select.cc:9863
 
#19 0x0000560c7eb975e4 in JOIN::reoptimize (this=0x62900022b870, added_where=0x62900022e9d8, join_tables=1, save_to=0x7f74d60db720) at /data/src/11.0/sql/sql_select.cc:31375
 
#20 0x0000560c7efa02e3 in JOIN::choose_subquery_plan (this=0x62900022b870, join_tables=1) at /data/src/11.0/sql/opt_subselect.cc:6775
 
#21 0x0000560c7eae41e7 in make_join_statistics (join=0x62900022b870, tables_list=..., keyuse_array=0x62900022bbd8) at /data/src/11.0/sql/sql_select.cc:6156
 
#22 0x0000560c7eabf81c in JOIN::optimize_inner (this=0x62900022b870) at /data/src/11.0/sql/sql_select.cc:2607
 
#23 0x0000560c7eab8889 in JOIN::optimize (this=0x62900022b870) at /data/src/11.0/sql/sql_select.cc:1935
 
#24 0x0000560c7e94554b in st_select_lex::optimize_unflattened_subqueries (this=0x6290000fa330, const_only=true) at /data/src/11.0/sql/sql_lex.cc:4899
 
#25 0x0000560c7ef99e68 in JOIN::optimize_constant_subqueries (this=0x6290000fdbc8) at /data/src/11.0/sql/opt_subselect.cc:5854
 
#26 0x0000560c7eabb7df in JOIN::optimize_inner (this=0x6290000fdbc8) at /data/src/11.0/sql/sql_select.cc:2265
 
#27 0x0000560c7eab8889 in JOIN::optimize (this=0x6290000fdbc8) at /data/src/11.0/sql/sql_select.cc:1935
 
#28 0x0000560c7eada31d in mysql_select (thd=0x62b00017a218, tables=0x6290000fa988, fields=..., conds=0x6290000fd198, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2164525824, result=0x6290000fdb98, unit=0x62b00017e660, select_lex=0x6290000fa330) at /data/src/11.0/sql/sql_select.cc:5168
 
#29 0x0000560c7eaaa02c in handle_select (thd=0x62b00017a218, lex=0x62b00017e588, result=0x6290000fdb98, setup_tables_done_option=0) at /data/src/11.0/sql/sql_select.cc:626
 
#30 0x0000560c7e9cf792 in execute_sqlcom_select (thd=0x62b00017a218, all_tables=0x6290000fa988) at /data/src/11.0/sql/sql_parse.cc:6279
 
#31 0x0000560c7e9bdbb9 in mysql_execute_command (thd=0x62b00017a218, is_called_from_prepared_stmt=false) at /data/src/11.0/sql/sql_parse.cc:3949
 
#32 0x0000560c7e9da242 in mysql_parse (thd=0x62b00017a218, rawbuf=0x6290000fa238 "SELECT * FROM t WHERE (7, 8) NOT IN (SELECT pk, a FROM t WHERE a != 9)", length=70, parser_state=0x7f74d60dda20) at /data/src/11.0/sql/sql_parse.cc:8019
 
#33 0x0000560c7e9b012e in dispatch_command (command=COM_QUERY, thd=0x62b00017a218, packet=0x629000285219 "SELECT * FROM t WHERE (7, 8) NOT IN (SELECT pk, a FROM t WHERE a != 9)", packet_length=70, blocking=true) at /data/src/11.0/sql/sql_parse.cc:1894
 
#34 0x0000560c7e9ace47 in do_command (thd=0x62b00017a218, blocking=true) at /data/src/11.0/sql/sql_parse.cc:1407
 
#35 0x0000560c7ee76e32 in do_handle_one_connection (connect=0x608000003938, put_in_cache=true) at /data/src/11.0/sql/sql_connect.cc:1416
 
#36 0x0000560c7ee767f3 in handle_one_connection (arg=0x6080000038b8) at /data/src/11.0/sql/sql_connect.cc:1318
 
#37 0x0000560c7fa6fcaa in pfs_spawn_thread (arg=0x617000008218) at /data/src/11.0/storage/perfschema/pfs.cc:2201
 
#38 0x00007f74e5ca7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#39 0x00007f74e5d285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

The assertion failure only affects on debug builds of course, but the preceding error happens on release builds as well.
Reproducible on 11.x.
Not reproducible with the provided test case on 10.x.
Generated at Thu Feb 08 10:25:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.