maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Varun,

First: I can still get the assertion failure if I run this:

set @@sql_mode='only_full_group_by';
select min(1 mod a1), bit_or(a2) over () from t1;

As far as I understand, the query is not valid for 
only_full_group_by mode. (Window functions as such are ok, 
but here window function is using a2, which cannot be accessed in
the post-grouping context).

More input below.

On Wed, Jul 08, 2020 at 02:58:31PM +0530, Varun wrote:
> revision-id: c776cad5f8ecf2675510deeb55724d7255a52503 (mariadb-10.4.11-267-gc776cad5f8e)
> parent(s): cc0dca366357651ddb549e31a12b1ecd39c7380e
> author: Varun Gupta
> committer: Varun Gupta
> timestamp: 2020-07-08 14:58:17 +0530
> message:
> 
> MDEV-22702: Assertion `!field->is_null()' failed in my_decimal::my_decimal
> 
> With implicit grouping with window functions, we need to make sure that all the
> fields inside the window functions are nullable as any non-aggregated field can
> produce a NULL value.

...
> diff --git a/sql/sql_lex.h b/sql/sql_lex.h
> index 0983cea44d0..16a0b3b08a6 100644
> --- a/sql/sql_lex.h
> +++ b/sql/sql_lex.h
> @@ -1510,6 +1510,8 @@ class st_select_lex: public st_select_lex_node
>    }
>  
>    bool have_window_funcs() const { return (window_funcs.elements !=0); }
> +  uint32 get_number_of_window_funcs() const
> +  { return (uint32)window_funcs.elements; }

Why use uint32 when n_sum_items is uint, and elements is also uint?
I suggest just keep using uint.

>    ORDER *find_common_window_func_partition_fields(THD *thd);
>  
>    bool cond_pushdown_is_allowed() const
> diff --git a/sql/sql_select.cc b/sql/sql_select.cc
> index 0ca5ab23288..05463efe9a5 100644
> --- a/sql/sql_select.cc
> +++ b/sql/sql_select.cc
> @@ -1201,6 +1201,18 @@ JOIN::prepare(TABLE_LIST *tables_init,
>          break;
>        }
>      }
> +    /*
> +      If the query has a window function with an aggregate function,
> +      then also we have a mix of elements with and without grouping.
> +      Window function can be in the ORDER BY clause too so the check
> +      is made separately.

Wording could be better.
I don't understand what did you mean to say about ORDER BY. can you elaborate?

> +      Window function is inherited from Item_sum so each window function is
> +      also registered as a sum item, so need to check that we have an
> +      explicit aggregate function also in the query.

what's "explicit aggregate" ?  A regular aggregate function (i.e. not a window
function)? 

> +    */
> +    if (select_lex->have_window_funcs() &&
> +        select_lex->get_number_of_window_funcs() < select_lex->n_sum_items)
> +      mixed_implicit_grouping= true;
>    }
>  
>    table_count= select_lex->leaf_tables.elements;

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog