It looks like it is important to have range access, explain of incorrect execution:
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 range NULL b 10 NULL 10 90.00 Using where; Using index for group-by
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select min(`test`.`t1`.`a`) AS `MIN(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` > (select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 0))) group by `test`.`t1`.`b`
If reduce test suite table to INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0); it works correctly with other explain:
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL b 10 NULL 6 100.00 Using where; Using index
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select min(`test`.`t1`.`a`) AS `MIN(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` > (select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 0))) group by `test`.`t1`.`b`
It looks like it is important to have range access, explain of incorrect execution:
+id select_type table type possible_keys key key_len ref rows filtered Extra .`t1`.` a`) AS `MIN(a) `,`test` .`t1`.` b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` > (select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 0))) group by `test`.`t1`.`b`
+1 PRIMARY t1 range NULL b 10 NULL 10 90.00 Using where; Using index for group-by
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select min(`test`
If reduce test suite table to INSERT INTO t1 VALUES (0,99), (9,99), (4,0),( 7,0),(99, 0),(7,0) ,(8,0), (99,0), (1,0); it works correctly with other explain:
+id select_type table type possible_keys key key_len ref rows filtered Extra .`t1`.` a`) AS `MIN(a) `,`test` .`t1`.` b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` > (select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 0))) group by `test`.`t1`.`b`
+1 PRIMARY t1 index NULL b 10 NULL 6 100.00 Using where; Using index
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select min(`test`