I devised a test case that first prepares the statement then executes it, to not have to rely on --ps flag for mtr.

create table t(a decimal(35,10), b int);

insert into t values (1, 10), (2, 20), (3, 30);

prepare stmt from "SELECT (CASE WHEN sum(t.a) over (partition by t.b)=1 THEN 1000 ELSE 300 END) AS a FROM t";

execute stmt;

So we execute the query:

prepare stmt from "SELECT (CASE WHEN sum(t.a) over (partition by t.b)=1 THEN 1000 ELSE 300 END) AS a FROM t";

We're going through JOIN::prepare and reach the setup_fields call:

if (setup_fields(thd, ref_ptrs, fields_list, MARK_COLUMNS_READ, &all_fields, 1))

Currently all_fields contains just one element:

(gdb) p all_fields

$1 = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fffd402e188, last= 0x7fffd402e188, 

    elements = 1}, <No data fields>}

(gdb) p dbug_print_item((Item*)all_fields->first->info)

$6 = 0x1d8b1b0 <dbug_item_print_buf> "case when sum(t.a) over ( partition by t.b) = 1 then 1000 else 300 end"

So we have a case item with an equality comparison that features a window function.

Let's look at how the window function specifications look at this point.

(gdb) p select_lex->window_funcs

$7 = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fffd402dc80, last = 0x7fffd402dc80, 

    elements = 1}, <No data fields>}

(gdb) p select_lex->window_specs

$8 = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fffd402dba0, last = 0x7fffd402dba0, 

    elements = 1}, <No data fields>}

(gdb) p dbug_print_item(*((Window_spec*)(select_lex->window_specs->first->info))->partition_list->first->item)

$18 = 0x1d8b1b0 <dbug_item_print_buf> "t.b"

So the partition list has an Item_field referencing t.b. This is from the select statement's parsing stage.

Now we go past setup_fields call and reach setup_without_group. The all_fields list now contains 3 elements, which are:

(gdb) p dbug_print_item((Item*)all_fields->first->info)

$25 = 0x1d8b1b0 <dbug_item_print_buf> "sum(t.a) over ( partition by t.b)"

(gdb) p dbug_print_item((Item*)all_fields->first->next->info)

$29 = 0x1d8b1b0 <dbug_item_print_buf> "t.a"

(gdb) p dbug_print_item((Item*)all_fields->first->next->next->info)

$30 = 0x1d8b1b0 <dbug_item_print_buf> "case when sum(t.a) over ( partition by t.b) = 1 then 1000 else 300 end"

So the fields now are:

all_fields[0] -> The window function

all_fields[1] -> The parameter within the window function

all_fields[2] -> The case item.

The ref_ptrs array now has:

ref_ptrs[0] The case item.

ref_ptrs[1] The parameter within the window function

ref_ptrs[2] The window function

ref_ptrs[3] The equality item within the case item.   // Note that this is here as a side effect of MDEV-12336, this technically should be ignored.

Running through setup_without_group, we call setup_windows which calls setup_group to set up partitions, which calls find_order_in_list.
find_order_in_list uses *order->item as a search "key". Currently it is the Item_field t.b, as we saw earlier.

find_order_in_list concludes that the order item is not in the field list and proceeds to add it to all_fields.
Notice that ref_ptrs[3] is actually "free" as all_fields only contains 3 elements.

Now ref_ptrs[3] is overwritten (line 22210 in sql/sql_select.cc) with the "fixed" Item_field t.b. And we add this Item_field to all_fields.

So ref_ptrs[3] is the window function partition by field t.b

all_fields[0] -> Field t.b

all_fields[1] -> The window function

all_fields[2] -> The parameter within the window function

all_fields[3] -> The case item.

KEY NOTE: As a side effect of this procedure, the order structure in PARTITION BY has the item member point to &ref_pointer_array[3]!
So, whenever the value of ref_pointer_array[3] changes, the partition by item within the window function changes as well. Where does this happen?

Going forward with running the statement:

execute stmt;

In Item::split_sum_func2 we end up overriding ref_pointer_array[3] to Item_func_eq, but since the Item_func_eq contains a window function, we don't
add it to the all_fields list. Previously during prepare this is fine as we don't have anyone using ref_pointer_array[3]. By overriding ref_pointer_array[3] now, we change the PARTITION BY order item of the window function to point to Item_func_eq.

Now this order->item subsequently has "with_window_func" flag set to true and thus fails our condition that window functions are not allowed in partition by context.

SOLUTION:
When doing any work with ref_ptrs array, never override a value with something that is not valid as we may end up messing references from when the
statement was prepared. If we only set the correct value every time, we will not change any references, as we will be setting the same value
the references were pointing to from the statement prepare phase.

An ideal solution would be to never have to do any setting of ref_ptrs_array post prepare (which seems to be the intent for the array in the first place), but this may need to be targeted for a 10.3 or 10.4 release.

CC psergey igor sanja varun

Patch that fixes the problem:

diff --git a/sql/item.cc b/sql/item.cc
index df615b5ace9..05e5fd1ce45 100644
— a/sql/item.cc
+++ b/sql/item.cc

@@ -1920,6 +1920,9 @@ void Item::split_sum_func2(THD *thd, Ref_ptr_array ref_pointer_array,

       point to the temporary table.

     */

     split_sum_func(thd, ref_pointer_array, fields, split_flags);

+    if (type() == FUNC_ITEM) {

+      return;

+    }

   }

   else

   {

@@ -1979,9 +1982,6 @@ void Item::split_sum_func2(THD *thd, Ref_ptr_array ref_pointer_array,

                                         &ref_pointer_array[el], 0, name))))

       return;                                   // fatal_error is set

   }

-  else if (type() == FUNC_ITEM && 

-           ((Item_func *) this)->with_window_func)

-    return;

   else

   {

     if (!(item_ref= (new (thd->mem_root)

Another thing to note: This bug was not apparent immediately after MDEV-12336. This was due to a change in SELECT_LEX::select_n_having_items counter.

This counter is used to allocate an appropriately sized array during setup_ref_array. It is called before calling setup_fields which calls split_sum_func.

Before MDEV-13064, we would count the items generated by split_sum_func during prepare and add them to select_n_having_items counter. During prepared statement execution, select_n_having will be different when calling setup_ref_array than it was during previous statement prepare. This leads to a reallocation of ref_ptr_array and thus our overriding never happened.

Post MDEV-13064, we have this overriding behaviour apparent as no reallocation occurs.

All is OK, but add comments please