Hello Igor, I'm looking at your latest patch for MDEV-26301 in bb-10.4-igor and I see an obvious bug in the execution part: Split-materialized table re-fill is triggered by this code in sub_select(): if (join_tab->split_derived_to_update && !end_of_records) { table_map tab_map= join_tab->split_derived_to_update; for (uint i= 0; tab_map; i++, tab_map>>= 1) { if (tab_map & 1) join->map2table[i]->preread_init_done= false; } } (did you see Table_map_iterator class btw?) but what if the table in question uses join buffer and so uses sub_select_cache(), not sub_select()? Please find the testcase below: # 5 values create table t1(a int, b int); insert into t1 select seq,seq from seq_1_to_5; # 5 value groups of size 2 each create table t2(a int, b int, key(a)); insert into t2 select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B; # 5 value groups of size 3 each create table t3(a int, b int, key(a)); insert into t3 select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B; analyze table t1,t2,t3 persistent for all; create table t10 ( grp_id int, col1 int, key(grp_id) ); # 100 groups of 100 values each insert into t10 select A.seq, B.seq from seq_1_to_100 A, seq_1_to_100 B; # and X10 multiplier create table t11 ( col1 int, col2 int ); insert into t11 select A.seq, A.seq from seq_1_to_10 A; analyze table t10,t11 persistent for all; explain select * from ( (t1 left join t2 on t2.a=t1.b) left join t3 on t3.a=t1.b ) left join (select grp_id, count(*) from t10 left join t11 on t11.col1=t10.col1 group by grp_id) T on T.grp_id=t1.b; +------+-----------------+------------+------+---------------+--------+---------+---------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-----------------+------------+------+---------------+--------+---------+---------+------+-------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 5 | | | 1 | PRIMARY | t2 | ref | a | a | 5 | j1.t1.b | 2 | Using where | | 1 | PRIMARY | t3 | ref | a | a | 5 | j1.t1.b | 3 | Using where | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | j1.t1.b | 10 | Using where | | 2 | LATERAL DERIVED | t10 | ref | grp_id | grp_id | 5 | j1.t1.b | 100 | | | 2 | LATERAL DERIVED | t11 | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (flat, BNL join) | +------+-----------------+------------+------+---------------+--------+---------+---------+------+-------------------------------------------------+ Ok Note the query result: MariaDB [j1]> select * from -> ( -> (t1 left join t2 on t2.a=t1.b) -> left join t3 on t3.a=t1.b -> ) left join (select grp_id, count(*) -> from t10 left join t11 on t11.col1=t10.col1 -> group by grp_id) T on T.grp_id=t1.b; +------+------+------+------+------+------+--------+----------+ | a | b | a | b | a | b | grp_id | count(*) | +------+------+------+------+------+------+--------+----------+ | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 100 | | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 100 | | 1 | 1 | 1 | 1 | 1 | 3 | 1 | 100 | | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 100 | | 1 | 1 | 1 | 2 | 1 | 2 | 1 | 100 | | 1 | 1 | 1 | 2 | 1 | 3 | 1 | 100 | | 2 | 2 | 2 | 1 | 2 | 1 | 2 | 100 | | 2 | 2 | 2 | 1 | 2 | 2 | 2 | 100 | | 2 | 2 | 2 | 1 | 2 | 3 | 2 | 100 | | 2 | 2 | 2 | 2 | 2 | 1 | 2 | 100 | | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 100 | | 2 | 2 | 2 | 2 | 2 | 3 | 2 | 100 | | 3 | 3 | 3 | 1 | 3 | 1 | 3 | 100 | | 3 | 3 | 3 | 1 | 3 | 2 | 3 | 100 | | 3 | 3 | 3 | 1 | 3 | 3 | 3 | 100 | | 3 | 3 | 3 | 2 | 3 | 1 | 3 | 100 | | 3 | 3 | 3 | 2 | 3 | 2 | 3 | 100 | | 3 | 3 | 3 | 2 | 3 | 3 | 3 | 100 | | 4 | 4 | 4 | 1 | 4 | 1 | 4 | 100 | | 4 | 4 | 4 | 1 | 4 | 2 | 4 | 100 | | 4 | 4 | 4 | 1 | 4 | 3 | 4 | 100 | | 4 | 4 | 4 | 2 | 4 | 1 | 4 | 100 | | 4 | 4 | 4 | 2 | 4 | 2 | 4 | 100 | | 4 | 4 | 4 | 2 | 4 | 3 | 4 | 100 | | 5 | 5 | 5 | 1 | 5 | 1 | 5 | 100 | | 5 | 5 | 5 | 1 | 5 | 2 | 5 | 100 | | 5 | 5 | 5 | 1 | 5 | 3 | 5 | 100 | | 5 | 5 | 5 | 2 | 5 | 1 | 5 | 100 | | 5 | 5 | 5 | 2 | 5 | 2 | 5 | 100 | | 5 | 5 | 5 | 2 | 5 | 3 | 5 | 100 | +------+------+------+------+------+------+--------+----------+ 30 rows in set (0.015 sec) Now, let's make the tables not use indexes and use join buffer in the top-level select: explain select * from ( (t1 left join t2 use index() on t2.a=t1.b) left join t3 use index() on t3.a=t1.b ) left join (select grp_id, count(*) from t10 left join t11 on t11.col1=t10.col1 group by grp_id) T on T.grp_id=t1.b; +------+-----------------+------------+------+---------------+--------+---------+---------+------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-----------------+------------+------+---------------+--------+---------+---------+------+--------------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 5 | | | 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 50 | Using where; Using join buffer (flat, BNL join) | | 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 15 | Using where; Using join buffer (incremental, BNL join) | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | j1.t1.b | 10 | Using where | | 2 | LATERAL DERIVED | t10 | ref | grp_id | grp_id | 5 | j1.t1.b | 100 | | | 2 | LATERAL DERIVED | t11 | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (flat, BNL join) | +------+-----------------+------------+------+---------------+--------+---------+---------+------+--------------------------------------------------------+ select * from ( (t1 left join t2 use index() on t2.a=t1.b) left join t3 use index() on t3.a=t1.b ) left join (select grp_id, count(*) from t10 left join t11 on t11.col1=t10.col1 group by grp_id) T on T.grp_id=t1.b; +------+------+------+------+------+------+--------+----------+ | a | b | a | b | a | b | grp_id | count(*) | +------+------+------+------+------+------+--------+----------+ | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 100 | | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 100 | | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 100 | | 1 | 1 | 1 | 2 | 1 | 2 | 1 | 100 | | 1 | 1 | 1 | 1 | 1 | 3 | 1 | 100 | | 1 | 1 | 1 | 2 | 1 | 3 | 1 | 100 | | 2 | 2 | 2 | 1 | 2 | 1 | NULL | NULL | | 2 | 2 | 2 | 2 | 2 | 1 | NULL | NULL | | 2 | 2 | 2 | 1 | 2 | 2 | NULL | NULL | | 2 | 2 | 2 | 2 | 2 | 2 | NULL | NULL | | 2 | 2 | 2 | 1 | 2 | 3 | NULL | NULL | | 2 | 2 | 2 | 2 | 2 | 3 | NULL | NULL | | 3 | 3 | 3 | 1 | 3 | 1 | NULL | NULL | | 3 | 3 | 3 | 2 | 3 | 1 | NULL | NULL | | 3 | 3 | 3 | 1 | 3 | 2 | NULL | NULL | | 3 | 3 | 3 | 2 | 3 | 2 | NULL | NULL | | 3 | 3 | 3 | 1 | 3 | 3 | NULL | NULL | | 3 | 3 | 3 | 2 | 3 | 3 | NULL | NULL | | 4 | 4 | 4 | 1 | 4 | 1 | NULL | NULL | | 4 | 4 | 4 | 2 | 4 | 1 | NULL | NULL | | 4 | 4 | 4 | 1 | 4 | 2 | NULL | NULL | | 4 | 4 | 4 | 2 | 4 | 2 | NULL | NULL | | 4 | 4 | 4 | 1 | 4 | 3 | NULL | NULL | | 4 | 4 | 4 | 2 | 4 | 3 | NULL | NULL | | 5 | 5 | 5 | 1 | 5 | 1 | NULL | NULL | | 5 | 5 | 5 | 2 | 5 | 1 | NULL | NULL | | 5 | 5 | 5 | 1 | 5 | 2 | NULL | NULL | | 5 | 5 | 5 | 2 | 5 | 2 | NULL | NULL | | 5 | 5 | 5 | 1 | 5 | 3 | NULL | NULL | | 5 | 5 | 5 | 2 | 5 | 3 | NULL | NULL | +------+------+------+------+------+------+--------+----------+ 30 rows in set (0.008 sec) Bummer. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net