1drop table if exists t1,t2; 2# 3# MDEV-21958: Query having many NOT-IN clauses running forever 4# 5create table t2 ( 6pk int primary key, 7key1 int, 8col1 int, 9key (key1, pk) 10); 11insert into t2 (pk, key1) values (1,1),(2,2),(3,3),(4,4),(5,5); 12set @tmp_21958=@@optimizer_trace; 13set optimizer_trace=1; 14explain select * from t2 where key1 in (1,2,3) and pk not in (1,2,3); 15id select_type table type possible_keys key key_len ref rows Extra 161 SIMPLE t2 ALL PRIMARY,key1 NULL NULL NULL 5 Using where 17# This should show only ranges in form "(1) <= (key1) <= (1)" 18# ranges over "pk" should not be constructed. 19select json_detailed(JSON_EXTRACT(trace, '$**.ranges')) 20from information_schema.optimizer_trace; 21json_detailed(JSON_EXTRACT(trace, '$**.ranges')) 22[ 23 24 [ 25 "(1) <= (key1) <= (1)", 26 "(2) <= (key1) <= (2)", 27 "(3) <= (key1) <= (3)" 28 ] 29] 30set optimizer_trace=@tmp_21958; 31drop table t2; 32