Fix for BUG#21007.

The problem was that store_top_level_join_columns() incorrectly assumed
that the left/right neighbor of a nested join table reference can be only
at the same level in the join tree.

The fix checks if the current nested join table reference has no immediate
left/right neighbor, and if so chooses the left/right neighbors of the
nearest upper level, where these references are != NULL.


mysql-test/r/group_min_max.result:
  Test for BUG#21007.
mysql-test/t/group_min_max.test:
  Test for BUG#21007.
sql/sql_base.cc:
  After computing and materializing the columns of all NATURAL joins in a FROM clause,
  the procedure store_top_level_join_columns() has to change the current natural join
  into a leaf table reference for name resolution. For this it needs to make the left
  neighbor point to the natural join table reference, and the natural join itself point
  to its left neighbor.
  
  This fix correctly determines the left/right neighbors of a table reference, even if
  the neghbors are at higher levels in the nested join tree. The rule is that if a table
  reference has no immediate left/right neighbors, we recursively pick the left/right
  neighbor of the level(s) above.

1 files changed, 25 insertions, 13 deletions

diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 5383bb52aaa..7f9076bb46e 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -4041,36 +4041,48 @@ store_top_level_join_columns(THD *thd, TABLE_LIST *table_ref,
   if (table_ref->nested_join)
   {
     List_iterator_fast<TABLE_LIST> nested_it(table_ref->nested_join->join_list);
-    TABLE_LIST *cur_left_neighbor= nested_it++;
-    TABLE_LIST *cur_right_neighbor= NULL;
+    TABLE_LIST *same_level_left_neighbor= nested_it++;
+    TABLE_LIST *same_level_right_neighbor= NULL;
+    /* Left/right-most neighbors, possibly at higher levels in the join tree. */
+    TABLE_LIST *real_left_neighbor, *real_right_neighbor;
 
-    while (cur_left_neighbor)
+    while (same_level_left_neighbor)
     {
-      TABLE_LIST *cur_table_ref= cur_left_neighbor;
-      cur_left_neighbor= nested_it++;
+      TABLE_LIST *cur_table_ref= same_level_left_neighbor;
+      same_level_left_neighbor= nested_it++;
       /*
         The order of RIGHT JOIN operands is reversed in 'join list' to
         transform it into a LEFT JOIN. However, in this procedure we need
         the join operands in their lexical order, so below we reverse the
-        join operands. Notice that this happens only in the first loop, and
-        not in the second one, as in the second loop cur_left_neighbor == NULL.
-        This is the correct behavior, because the second loop
-        sets cur_table_ref reference correctly after the join operands are
+        join operands. Notice that this happens only in the first loop,
+        and not in the second one, as in the second loop
+        same_level_left_neighbor == NULL.
+        This is the correct behavior, because the second loop sets
+        cur_table_ref reference correctly after the join operands are
         swapped in the first loop.
       */
-      if (cur_left_neighbor &&
+      if (same_level_left_neighbor &&
           cur_table_ref->outer_join & JOIN_TYPE_RIGHT)
       {
         /* This can happen only for JOIN ... ON. */
         DBUG_ASSERT(table_ref->nested_join->join_list.elements == 2);
-        swap_variables(TABLE_LIST*, cur_left_neighbor, cur_table_ref);
+        swap_variables(TABLE_LIST*, same_level_left_neighbor, cur_table_ref);
       }
 
+      /*
+        Pick the parent's left and right neighbors if there are no immediate
+        neighbors at the same level.
+      */
+      real_left_neighbor=  (same_level_left_neighbor) ?
+                           same_level_left_neighbor : left_neighbor;
+      real_right_neighbor= (same_level_right_neighbor) ?
+                           same_level_right_neighbor : right_neighbor;
+
       if (cur_table_ref->nested_join &&
           store_top_level_join_columns(thd, cur_table_ref,
-                                       cur_left_neighbor, cur_right_neighbor))
+                                       real_left_neighbor, real_right_neighbor))
         goto err;
-      cur_right_neighbor= cur_table_ref;
+      same_level_right_neighbor= cur_table_ref;
     }
   }