Red Hat Enterprise Linux release 8.6 (Ootpa)

sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

optimizer_switch = index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on

I could not repeat it, I am getting the warning and row_count shows 2(expected) or -1 in case warnings are switched on (which is expected, -1 for statements which return a result set: as show warnings)

ok, strange, but why is the warning occurring? I assume you are talking about the scripts in file update_bug_example_1.sql

the warning is a bug. it's caused by internal reasons and shouldn't be shown to a user. We'll fix it

Thank you for the information Sergei.

Hello serg,

https://github.com/MariaDB/server/commit/4212c890962e2d81318a91fd46090b70ef2a2169

I'm afraid this patch has some flaws:

• It's too specific to a certain data type (DATE/DATETIME).
• It's fixing the consequences by masking errors in field_copy(). I prefer the source of the problem to be fixed: the original column does not have a DEFAULT, therefore create_tmp_table() should not even try to copy the default from the original field to the temporary field.

This patch seems to fix the reason of the problem (and is not data type specific):

diff --git a/sql/sql_select.cc b/sql/sql_select.cc

index f313d571b5b..4cf02cfefad 100644

--- a/sql/sql_select.cc

+++ b/sql/sql_select.cc

@@ -18241,7 +18241,8 @@ Field *Item_field::create_tmp_field_ex(TABLE *table,

   src->set_field(field);

   if (!(result= create_tmp_field_from_item_field(table, NULL, param)))

     return NULL;

-  if (field->eq_def(result))

+  if (!(field->flags & NO_DEFAULT_VALUE_FLAG) &&

+      field->eq_def(result))

     src->set_default_field(field);

   return result;

 }

olap.test failed for me with this patch. I think it should be recorded.

Ok to push.

As discussed on slack, the fix should actually mix both:

• The fix by serg
  https://github.com/MariaDB/server/commit/4212c890962e2d81318a91fd46090b70ef2a2169
  to handle joins on DATE columns with DEFAULT '0000-00-00' in all sql_modes.

• The proposed fix by bar
  https://github.com/MariaDB/server/commit/e4a462e9466162cab5632948e021a8bd8bd5cd2f
  to avoid copying of the DEFAULT value by calling field_conv() when the original field does not really have a DEFAULT.
  This also fixes wrongly recorded test results in olap.test.

Note: The fix only fixes the warning, as explained above.