MDEV-25636: Bug report: abortion in sql/sql_parse.cc:6294mariadb-10.2.43bb-10.2-compatibility

The asserion failure was caused by this query

  select /*id=1*/ from t1
  where
   col= ( select /*id=2*/ from ... where corr_cond1
          union
          select /*id=4*/ from ... where corr_cond2)

Here,
- select with id=2 was correlated due to corr_cond1.
- select with id=4 was initially correlated due to corr_cond2, but then
  the optimizer optimized away the correlation, making the select with id=4
  uncorrelated.

However, since select with id=2 remained correlated, the execution had to
re-compute the whole UNION. When it tried to execute select with id=4, it
hit an assertion  (join buffer already free'd).

This is because select with id=4 has freed its execution structures after
it has been executed once. The select is uncorrelated, so it did not expect
it would need to be executed for the second time.

Fixed this by adding this logic in
st_select_lex::optimize_unflattened_subqueries():

  If a member of a UNION is correlated, mark all its members as
  correlated, so that they are prepared to be executed multiple times.

1 files changed, 15 insertions, 1 deletions

diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 125bbfe1bfd..47ff2836aba 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -3898,7 +3898,21 @@ bool st_select_lex::optimize_unflattened_subqueries(bool const_only)
       }
       if (empty_union_result)
         subquery_predicate->no_rows_in_result();
-      if (!is_correlated_unit)
+
+      if (is_correlated_unit)
+      {
+        /*
+          Some parts of UNION are not correlated. This means we will need to
+          re-execute the whole UNION every time. Mark all parts of the UNION
+          as correlated so that they are prepared to be executed multiple
+          times (if we don't do that, some part of the UNION may free its
+          execution data at the end of first execution and crash on the second
+          execution)
+        */
+        for (SELECT_LEX *sl= un->first_select(); sl; sl= sl->next_select())
+          sl->uncacheable |= UNCACHEABLE_DEPENDENT;
+      }
+      else
         un->uncacheable&= ~UNCACHEABLE_DEPENDENT;
       subquery_predicate->is_correlated= is_correlated_unit;
     }