Join optimizer prunes good join orders

--source include/have_sequence.inc

create table t1 (a int);

insert into t1 select seq from seq_1_to_10000;

create table t2 (

  a int,

  b int,

  index(a)

);

insert into t2

select

  A.seq,

  B.seq

from

  seq_1_to_10000 A,

  seq_1_to_100 B;

create table t3 (

  a int,

  b int,

  index(a)

);

insert into t3

select

  A.seq,

  B.seq

from

  seq_1_to_10000 A,

  seq_1_to_10 B;

analyze table t1,t2,t3;

explain format=json

select *

from

t1

join t3 on t3.a=t1.a and t3.b in (1,2,3,4)

join t2 on t2.a=t1.a and t2.b in (1,2,3,4);

EXPLAIN

{

  "query_block": {

    "select_id": 1,

    "cost": 5090.535084,

...

explain format=json

select *

from

t1

join t2 on t2.a=t1.a and t2.b in (1,2,3,4)

join t3 on t3.a=t1.a and t3.b in (1,2,3,4);

EXPLAIN

{

  "query_block": {

    "select_id": 1,

    "cost": 5090.535084,

...

explain format=json

select straight_join *   # set optimizer_prune_level=0 also works

from

t1

join t2 on t2.a=t1.a and t2.b in (1,2,3,4)

join t3 on t3.a=t1.a and t3.b in (1,2,3,4);

EXPLAIN

{

  "query_block": {

    "select_id": 1,

    "cost": 1759.088784,