port Bug#34890862 Bug#35410465 Bug#34616553

Bug#34890862: Mysql server crash : `item->hidden == hidden' in assert_consistent_hidden_flags

This is regression from Bug#34616553. When deciding to not re-resolve a merged order by expression, the order by expression was always added as a hidden item. However if the order by expression is part of the visible fields list of the query block where the order by is merged, the view ref in the select expression would be marked visible where as the order by item which is also the referenced item by the select expression would be marked hidden. This leads to an assert later when it is detected that there is a mismatch in the hidden flag for the view ref and the underlying referenced item. Solution is to not add a hidden element when the order by item is found in the visible fields list of the query block where the order by is merged to.

Bug#35410465: Subquery ORDER BY is ignored

This is a regression from Bug#34890862. That fix made a change for already resolved ORDER BY items to not be re-resolved (it is usually the case when a derived table is merged). As part of the change, when comparing the order by items to the visible fields in the outer query block, it compared the order by item with the referenced item in the ref_item_array. For the case in bugpage, the order by item itself is a reference. This makes the comparison fail.
So we now compare the real_item() of the order by expression to the real_item() of the ref_item_array elements.
Also, when a hidden item is added, the original fix missed to set order->item to point to the correct ref array item like what is done in find_order_in_list().

Bug#34616553: SEGV (Item_subselect::print() at sql/item_subselect.cc:833) reported by ASan

The problem was introduced before version 8.0.22 and fixed in community version 8.0.33.
For this problem to happen, it needs several conditions to be met:

1. A mergeable derived table having a subquery in select list which is referenced by ORDER BY through an alias. This makes the ORDER BY reference the select expression when it gets resolved.
2. Needs to have a subquery with the same name in the query block where the derived table gets merged.
3. Needs to have window function referencing the subquery through an alias in PARTITION BY or ORDER BY clause. This makes it take a special path while resolving the ordering item to point to the view reference (from the merged derived table) than the one in the select list.
   When the derived table having the order by referencing the subquery in select expression gets merged, order by is moved to the outer query block. This makes another call to resolve the order by item. This time, it cleans up the order by item since it resolves against the select list again. Later, window function's partition by clause resolves against the view reference pointing to the subquery which was deleted previously leading to problems later during optimization.
   Solution is to not do the resolving again for a merged order by expression. We now have a check for a non constant expression that is already resolved and for such a order by expression, we just add a hidden element to the merged query block instead of re-resolving it.

sql/item_subselect.cc から始め、issue で説明されている、マージされた派生テーブルの ORDER BY と window-function の解決パスを追跡してください。すでに解決済みの式、real_item() 参照、hidden item の扱いを、3つの回帰の説明と比較してください。完了とは、マージされた式を誤って再解決することなく、報告されたクラッシュ、assertion、無視される ORDER BY という3つの回帰が対処されていることを意味します。