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:
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.
Bug
Major
I devised a test case that first prepares the statement then executes it, to not have to rely on --ps flag for mtr.
So we execute the query:
We're going through JOIN::prepare and reach the setup_fields call:
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 functionall_fields[1] -> The parameter within the window functionall_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 functionref_ptrs[2] The window functionref_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.ball_fields[1] -> The window functionall_fields[2] -> The parameter within the window functionall_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:
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.