MDEV-23809: Server crash in JOIN_CACHE::free or ...

The problem was caused by use of COLLATION(AVG('x')). This is an
item whose value is a constant.
Name Resolution code called convert_const_to_int() which removed AVG('x').
However, the item representing COLLATION(...) still had with_sum_func=1.

This inconsistent state confused the code that handles grouping and
DISTINCT: JOIN::get_best_combination() decided to use one temporary
table and allocated one JOIN_TAB for it, but then
JOIN::make_aggr_tables_info() attempted to use two and made writes
beyond the end of the JOIN::join_tab array.

The fix:
- Do not replace constant expressions which contain aggregate functions.
- Add JOIN::dbug_join_tab_array_size to catch attempts to use more
  JOIN_TAB objects than we've allocated.

1 files changed, 12 insertions, 1 deletions

diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index a3c0d4d95df..fe6b8feb4de 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -340,7 +340,18 @@ static bool convert_const_to_int(THD *thd, Item_field *field_item,
       field_item->field_type() != MYSQL_TYPE_YEAR)
     return 1;
 
-  if ((*item)->const_item() && !(*item)->is_expensive())
+  /*
+    Replace (*item) with its value if the item can be computed.
+
+    Do not replace items that contain aggregate functions:
+    There can be such items that are constants, e.g. COLLATION(AVG(123)),
+    but this function is called at Name Resolution phase.
+    Removing aggregate functions may confuse query plan generation code, e.g.
+    the optimizer might conclude that the query doesn't need to do grouping
+    at all.
+  */
+  if ((*item)->const_item() && !(*item)->is_expensive() &&
+      !(*item)->with_sum_func)
   {
     TABLE *table= field->table;
     Sql_mode_save sql_mode(thd);