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 &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@mariadb.org