[MDEV-36055] RESEARCH: Independent LEFT JOINs should be reordereable - Jira

Consider a query starting with table t1 and doing two LEFT JOINs to it:

set optimizer_trace=1;

explain

select *

from

t1

  left join t3 on t3.key1=t1.a

  left join t2 on t2.key1=t1.a;

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

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

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

|    1 | SIMPLE      | t1    | ALL  | NULL            | NULL    | NULL    | NULL    | 10   |             |

|    1 | SIMPLE      | t3    | ref  | t3_key2         | t3_key2 | 5       | j1.t1.a | 100  | Using where |

|    1 | SIMPLE      | t2    | ref  | t2_key1,t3_key1 | t2_key1 | 5       | j1.t1.a | 10   | Using where |

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

The optimizer trace shows that t1-t3-t2 is the only acceptable join order:

            "table_dependencies": [

                "table": "t1",

                "row_may_be_null": false,

                "map_bit": 0,

                "depends_on_map_bits": []

},

                "table": "t3",

                "row_may_be_null": true,

                "map_bit": 1,

                "depends_on_map_bits": ["0"]

},

                "table": "t2",

                "row_may_be_null": true,

                "map_bit": 2,

                "depends_on_map_bits": ["0", "1"]

But is t1-t2-t3 order also allowed?

Notes

MySQL also has this limitation, and it seems so does PostgreSQL.

t1

  left join t3 on t3.key1=t1.a

  left join t2 on t2.key1=t1.a

equivalent to

t1

  left join t2 on t2.key1=t1.a

  left join t3 on t3.key1=t1.a

relates to

MDEV-13817 add support for oracle outer join syntax - the ( + )

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.