maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Sergei,

On Jan 13, Sergei Petrunia wrote:
> revision-id: 0bef50e50b5 (mariadb-10.4.27-33-g0bef50e50b5)
> parent(s): 5db970fc760
> author: Sergei Petrunia
> committer: Sergei Petrunia
> timestamp: 2023-01-04 16:50:12 +0300
> message:
> 
> MDEV-20501: Assertion `maybe_null || !null_value' failed in Item_func_round::date_op
> 
> When the optimizer finds a constant (or system) table $TBL which is empty
> or has no matching row, it would set table->null_row=true. This is done
> even for tables with table->maybe_null==0.
> 
> Then, it would proceed to perform query optimization phases (what for?)
> which will attempt to evaluate Item expressions referring to $TBL.
> 
> Eventually some Item expression will get the value of $TBL.not_null_field,
> get SQL NULL and fail an assertion.
> 
> Fixed by not performing any query optimization steps after we've got
> constant/empty tables with no matching rows.
> 
> Test result changes contain a lot of changes like
> - ... Impossible WHERE noticed after reading const tables
> + ... no matching row in const table
> 
> as well as other changes caused by slightly-different processing of
> the special case of empty constant tables.
> 
> diff --git a/sql/sql_select.cc b/sql/sql_select.cc
> index 6a441c5047b..57ffd58f8b4 100644
> --- a/sql/sql_select.cc
> +++ b/sql/sql_select.cc
> @@ -2382,6 +2382,9 @@ int JOIN::optimize_stage2()
>    if (subq_exit_fl)
>      goto setup_subq_exit;
>  
> +  if (zero_result_cause)
> +    goto setup_subq_exit;

In many other cases it's, like

	zero_result_cause= "No matching min/max row";
        subq_exit_fl= true;

that is, subq_exit_fl is set whenever zero_result_cause is set.
Meaning, perhaps you should do the same in your new code below
and then you wouldn't need those lines you've added above?

>    if (unlikely(thd->check_killed()))
>      DBUG_RETURN(1);
>  
> @@ -5392,7 +5398,32 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
>        }
>      }
>    } while (ref_changed);
> - 
> +
> +  /*
> +    ConstRowNotFoundShortcut-1:
> +    Some constant/system tables have mo matching rows. This means that
> +    the join operation output will be empty.
> +    Short-cut further optimization steps.
> +    Note that some query plan steps will still be performed to handle
> +    implicit grouping, join result setup, etc.
> +    See also: ConstRowNotFoundShortcut-{2,3}.
> +
> +    Do not do this if we're optimizing for some UNION's fake_select_lex.
> +    We might be running UglySubqueryReoptimization (grep for name) and
> +    in this case constant tables are not reliable.
> +  */
> +  if ((join->const_table_map & ~found_const_table_map) &&
> +       !(join->select_lex->master_unit() &&
> +         join->select_lex->master_unit()->fake_select_lex == join->select_lex))

why wouldn't you check for `describe` here? "reoptimization" hack is
only needed for EXPLAIN.

> +  {
> +    join->zero_result_cause= "no matching row in const table";
> +    join->table_count=0;
> +    join->const_tables= 0;
> +    join->join_tab= NULL;
> +
> +    DBUG_RETURN(0);
> +  }
> +
>    join->sort_by_table= get_sort_by_table(join->order, join->group_list,
>                                           join->select_lex->leaf_tables,
>                                           join->const_table_map);

Regards,
Sergei
VP of MariaDB Server Engineering
and security@xxxxxxxxxxx