Optimization for equi-joins of derived tables with window functions

create or replace table t1 (a int, b int, c int, index(a, b, c));

create or replace table t2 (a int, b int, c int, index(a, b, c));

insert into t1 select seq, seq, seq from seq_1_to_500000;

insert into t2 select seq, seq, seq from seq_1_to_500000;

explain

select * from 

(

select t1.a a, t2.b b, t2.c, sum(t2.c) over ( partition by a,b,c )

from t1 join t2

where 

t1.a=t2.a and t1.b=t2.b

) v1 join ( select 1000000 as a, 1000000 as b, 1000000 as c ) v2 using (a,b,c);

+------+-------------+------------+--------+---------------+------+---------+---------------------+--------+-------------------------------------------+

| id   | select_type | table      | type   | possible_keys | key  | key_len | ref                 | rows   | Extra                                     |

+------+-------------+------------+--------+---------------+------+---------+---------------------+--------+-------------------------------------------+

|    1 | PRIMARY     | <derived3> | system | NULL          | NULL | NULL    | NULL                |      1 |                                           |

|    1 | PRIMARY     | <derived2> | ref    | key0          | key0 | 15      | const,const,const   |     10 |                                           |

|    3 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL                |   NULL | No tables used                            |

|    2 | DERIVED     | t1         | index  | a             | a    | 15      | NULL                | 500000 | Using where; Using index; Using temporary |

|    2 | DERIVED     | t2         | ref    | a             | a    | 10      | test.t1.a,test.t1.b |      1 | Using index                               |

+------+-------------+------------+--------+---------------+------+---------+---------------------+--------+-------------------------------------------+

MariaDB [test]> select count(*) from

    -> (

    -> select t1.a a, t2.b b, t2.c, sum(t2.c) over ( partition by a,b,c )

    -> from t1 join t2

    -> where

    -> t1.a=t2.a and t1.b=t2.b

    -> ) v1 join ( select 1000000 as a, 1000000 as b, 1000000 as c ) v2 using (a,b,c);

+----------+

| count(*) |

+----------+

|        0 |

+----------+

1 row in set (2.27 sec)