Igor Babaev (Inactive) added a comment - 2020-02-01 21:34 - edited This is a setting that is simpler than the one used in the bug report and yet with this setting the bug still manifests itself though in a different way: set join_cache_level=6; set optimizer_switch= 'mrr=on' ; set optimizer_switch= 'index_condition_pushdown=on' ; (The last line is not necessary with default settings in 10.3). With the above setting the offensive query does not crash, but rather returns an empty result set and this is not correct. Here's a simple test case that demonstrates the problem: set join_cache_level=6; set optimizer_switch= 'mrr=on' ;   create table t0 ( tp int , a int , b int , c varchar (12), index idx (a,b) ) engine=myisam; insert into t0 values (1,3,30, 'yyzy' ), (1,3,30, 'yxxyy' ), (1,3,30, 'yyxy' ), (1,3,30, 'xxyy' ), (1,3,30, 'yyxz' ), (1,3,30, 'yyzz' ), (1,3,30, 'xxyzy' ), (1,3,30, 'yyyy' ), (1,3,30, 'yzzy' ), (1,93,30, 'zzzy' ), (2,3,40, 'yxx' ), (2,4,40, 'xx' ), (2,3,10, 'zxz' ), (2,3,40, 'yyx' ), (2,4,40, 'xxx' ), (2,3,10, 'zyyz' ), (2,3,40, 'xzzzz' ), (2,4,40, 'yyyxx' ), (2,3,10, 'zyz' ), (2,3,40, 'xxx' ), (2,4,40, 'xx' ), (2,3,10, 'zzz' ), (2,3,40, 'yyxzx' ), (2,4,40, 'xyx' ), (2,3,10, 'xzz' ), (2,3,40, 'xxxzz' ), (2,4,40, 'xxz' ), (2,3,10, 'zzzy' ), (2,3,40, 'zzxxx' ), (2,4,40, 'zxx' ), (2,3,10, 'yzzz' ), (2,3,40, 'xyyxx' ), (2,4,40, 'xzzzx' ), (2,3,10, 'zzxxz' ), (2,3,40, 'yzxxx' ), (2,4,40, 'xxzy' ), (2,3,10, 'zzzyx' ), (2,93,40, 'xzx' ), (2,94,40, 'xz' ), (2,93,10, 'zyyyz' ), (3,4,30, 'yx' ), (3,4,30, 'yyxxx' ), (3,4,30, 'zzyy' ), (3,4,30, 'zxyy' ), (3,4,30, 'xxyy' ), (3,4,30, 'yyzx' ), (3,4,30, 'zyyy' ), (3,4,30, 'yzy' ), (3,4,30, 'zzzyy' ), (3,94,30, 'yyz' );   create table t1 ( tp int , a int , b int , c varchar (12), index idx (a,b) ) engine=myisam partition by list (tp) ( partition p1 values in (1), partition p2 values in (2), partition p3 values in (3)); insert into t1 select * from t0;   create table t2 (a int , index idx(a)) engine=myisam; insert into t2 values (1), (2), (3), (4), (5); insert into t2 select a+10 from t2; insert into t2 select a+20 from t2;   analyze table t0,t1,t2;   select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; The tables t0 and t1 are of the same structure and populated with the same set of rows. The only difference is that t1 is partitioned. We see that the result set for the second query is empty: MariaDB [test]> select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; Empty set and that the execution employs index condition pushdown MariaDB [test]> explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+------------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+------------------------------------------------------------------------------------+ | 1 | SIMPLE | t2 | range | idx | idx | 5 | NULL | 2 | 100.00 | Using where; Using index | | 1 | SIMPLE | t1 | ref | idx | idx | 5 | test.t2.a | 5 | 100.00 | Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan | +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+------------------------------------------------------------------------------------+ At the same time for the first query the result is correct: MariaDB [test]> select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +------+------+------+-------+------+ | tp | a | b | c | a | +------+------+------+-------+------+ | 3 | 4 | 30 | yx | 4 | | 3 | 4 | 30 | yyxxx | 4 | | 3 | 4 | 30 | zzyy | 4 | | 3 | 4 | 30 | zxyy | 4 | | 3 | 4 | 30 | xxyy | 4 | | 3 | 4 | 30 | yyzx | 4 | | 3 | 4 | 30 | zyyy | 4 | | 3 | 4 | 30 | yzy | 4 | | 3 | 4 | 30 | zzzyy | 4 | +------+------+------+-------+------+ 9 rows in set and to get this result the executor uses index condition pushdown as well MariaDB [test]> explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+------------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+------------------------------------------------------------------------------------+ | 1 | SIMPLE | t2 | range | idx | idx | 5 | NULL | 2 | 100.00 | Using where; Using index | | 1 | SIMPLE | t0 | ref | idx | idx | 5 | test.t2.a | 13 | 100.00 | Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan | +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+------------------------------------------------------------------------------------+ The correct result set for the second query we get with the setting set optimizer_switch= 'index_condition_pushdown=off' ; MariaDB [test]> set optimizer_switch='index_condition_pushdown=off';   MariaDB [test]> select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +------+------+------+-------+------+ | tp | a | b | c | a | +------+------+------+-------+------+ | 3 | 4 | 30 | yx | 4 | | 3 | 4 | 30 | yyxxx | 4 | | 3 | 4 | 30 | zzyy | 4 | | 3 | 4 | 30 | zxyy | 4 | | 3 | 4 | 30 | xxyy | 4 | | 3 | 4 | 30 | yyzx | 4 | | 3 | 4 | 30 | zyyy | 4 | | 3 | 4 | 30 | yzy | 4 | | 3 | 4 | 30 | zzzyy | 4 | +------+------+------+-------+------+ 9 rows in set