feature: support t left join (with nested_join) on from clause

[hustjieke]

Is your feature request related to a problem? Please describe.

Before backport code on #1434, we should support left join on from clause:

mysql> create table t4(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t5(a int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t6(a int);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t4 left join (t5 left join t6 on t5.a=t6.a) on t4.a=t5.a;
ERROR 6 (HY000): The query includes syntax that is not supported by the storage engine. Either restructure the query with supported syntax, or enable the MySQL core::Query Path in config file to execute the query with reduced performance.

mysql> select * from t4 left join (t5 join t6) on t4.a = t5.a;
ERROR 6 (HY000): The query includes syntax that is not supported by the storage engine. Either restructure the query with supported syntax, or enable the MySQL core::Query Path in config file to execute the query with reduced performance.

For statement next is ok:

mysql> select * from t4 join (t5 left join t6 on t5.a=t6.a) on t4.a=t5.a;
Empty set (0.00 sec)

mysql> select * from t4 join (t5 join t6 on t5.a=t6.a) on t4.a=t5.a;
Empty set (0.00 sec)

Describe the solution you'd like

Describe alternatives you've considered

Additional context

[hustjieke]

debug:

In function AddJoins():

      std::string ext_alias = std::string(table_name ? table_name : "") + std::string(":") + std::string(table_alias);
      table_alias2index_ptr.insert(std::make_pair(ext_alias, std::make_pair(tab.n, join_ptr->table)));

table_alias2index_ptr only insert one ext_alias = t5:t5.
That makes return false in later execute function FieldUnmysterify() as it == it_end:

  auto it = table_alias2index_ptr.lower_bound(ext_alias);
  auto it_end = table_alias2index_ptr.upper_bound(ext_alias);
  if (it == table_alias2index_ptr.end())
    return false;
  for (; it != it_end; it++) {
  ...
  ...
  }
  return false;

Then get error condtion CondID(-1) and return kToMySQL.
We should find out why table_alias2index_ptr insert only one element.

[hustjieke]

Simplify sqls:

mysql> create table t1(a1 int, bi int);
Query OK, 0 rows affected (0.04 sec)

mysql> create table t2(a2 int, b2 int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table t3(a int, b int);
Query OK, 0 rows affected (0.03 sec)

mysql> select * from t1 left join (t2 join t3) on t1.a1=t2.a2;
ERROR 6 (HY000): The query includes syntax that is not supported by the storage engine. Either restructure the query with supported syntax, or enable the MySQL core::Query Path in config file to execute the query with reduced performance.

After debugging, I get:
In 8.0, table_alias2index_ptr has two elements, keys are nested join without t1: t2, t3 .
But int 5.7, table_alias2index_ptr has three elements, keys are: t1, t2, t3.

[hustjieke]

Root Cause:
processing order on t1, t2, t3 has changed in funciton AddJoins():
t1 is not a nested_join table, but t2, t3 are nested_join table.

In 5.7, process t1 first, then t2, t3.

code fragment in AddJoins()

QueryRouteTo Query::AddJoins(...) {
  while ((join_ptr = li++) != nullptr) reversed.push_back(join_ptr);
  size_t size = reversed.size();
  for (unsigned int i = 0; i < size; i++) {
    join_ptr = reversed[size - i - 1];
    if (join_ptr->nested_join) {
      if (QueryRouteTo::kToMySQL == BuildCondsIfPossible(join_ptr->join_cond(), cond_id, tmp_table, join_type))
      ...
    else {
      ...
      table_alias2index_ptr.insert(std::make_pair(ext_alias, std::make_pair(tab.n, join_ptr->table)));
    }
}

variables join: struct is List<Item>.
function calls order:
process TABLE_LIST(t1)--->not nested_join--->table_alias2index_ptr
process TABLE_LIST(t2, t3)--->is niested_join--->recursive execution AddJoins()--->t2--->not nested_join--->table_alias2index_ptr---> for loop t3 --->not nested_join--->table_alias2index_ptr

We get keys in table_alias2index_ptr: t1, t2, t3.

In 8.0, the code are changes when adapted from 5.7, code fragment:

  for (TABLE_LIST *join_ptr : join) {
    if (join_ptr->nested_join) {
      if (QueryRouteTo::kToMySQL == BuildCondsIfPossible(join_ptr->join_cond(), cond_id, tmp_table, join_type))
      ...
    else {
      ...
      table_alias2index_ptr.insert(std::make_pair(ext_alias, std::make_pair(tab.n, join_ptr->table)));
    }
}

variables join: struct is mem_root_deque<Item *>.
function calls order:
process TABLE_LIST(t2, t3)--->is niested_join--->recursive execution AddJoins()--->t2--->not nested_join--->table_alias2index_ptr---> for loop t3 --->not nested_join--->table_alias2index_ptr--->return false when doing FieldUnmysterify.

How to fix?

change

  for (TABLE_LIST *join_ptr : join) {

to

  for (int i = join.size() - 1; i >= 0; i--) {
    TABLE_LIST* join_ptr = join[i];

The process order now is the same with 5.7.