Bug #118036 execute simple sql using lateral assert fail
Submitted: 23 Apr 7:41 Modified: 14 May 7:51
Reporter: ximin liang (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S6 (Debug Builds)
Version:8.0.42 OS:Any
Assigned to: CPU Architecture:Any

[23 Apr 7:41] ximin liang 
Description:
Hello MySQL Team:
  Here is a problem about lateral, can be repeated in 8.0.42 debug version.

How to repeat:
Here is sql:

  mysql> select version();
  +--------------+
  | version()    |
  +--------------+
  | 8.0.42-debug |
  +--------------+
  1 row in set (0.00 sec)

  create table t10(b int);
  create table tp(a int,b int,c1 int);
  create table t21(a int,b int,c1 int);
  select subq_0.c0 from tp, lateral (select tp.a+ t21.b as c0 from t21 group by 1 ) as subq_0 where subq_0.c0;

  backtrace as follow:

  #0  0x00007f32cb8f6387 in raise () from /lib64/libc.so.6
#1  0x00007f32cb8f7a78 in abort () from /lib64/libc.so.6
#2  0x00007f32cb8ef1a6 in __assert_fail_base () from /lib64/libc.so.6
#3  0x00007f32cb8ef252 in __assert_fail () from /lib64/libc.so.6
#4  0x0000000003ba93b0 in operator() (__closure=0x7f32a4265940, inner_item=0x7f32100fed20)
    at /data/code/mysql-server/sql/sql_derived.cc:536
#5  0x0000000003bad1b7 in Item::walk_helper_thunk<copy_field_info(THD*, Item*, Item*)::<lambda(Item*)> >(uchar *) (this=0x7f32100fed20, 
    arg=0x7f32a4265940 "\020Y&\244\062\177") at /data/code/mysql-server/sql/item.h:2472
#6  0x000000000330ac5a in Item::walk (this=0x7f32100fed20, processor=
    (bool (Item::*)(Item * const, unsigned char *)) 0x3bad194 <Item::walk_helper_thunk<copy_field_info(THD*, Item*, Item*)::<lambda(Item*)> >(uchar *)>, walk=enum_walk::PREFIX, arg=0x7f32a4265940 "\020Y&\244\062\177") at /data/code/mysql-server/sql/item.h:2465
#7  0x00000000039336c5 in Item_func::walk (this=0x7f32100fc280, processor=
    (bool (Item::*)(Item * const, unsigned char *)) 0x3bad194 <Item::walk_helper_thunk<copy_field_info(THD*, Item*, Item*)::<lambda(Item*)> >(uchar *)>, walk=enum_walk::PREFIX, argument=0x7f32a4265940 "\020Y&\244\062\177") at /data/code/mysql-server/sql/item_func.cc:618
#8  0x0000000003bad208 in WalkItem<copy_field_info(THD*, Item*, Item*)::<lambda(Item*)> >(Item *, enum_walk, struct {...} &&) (
    item=0x7f32100fc280, walk=enum_walk::PREFIX, functor=...) at /data/code/mysql-server/sql/item.h:3594
#9  0x0000000003ba9681 in copy_field_info (thd=0x7f321017e970, orig_expr=0x7f32100fc280, cloned_expr=0x7f32101054b8)
    at /data/code/mysql-server/sql/sql_derived.cc:531
#10 0x0000000003ba9dbc in Query_block::clone_expression (this=0x7f32100fe3f0, thd=0x7f321017e970, item=0x7f32100fc280, 
    derived_table=0x7f32101011f0) at /data/code/mysql-server/sql/sql_derived.cc:795
#11 0x00000000038a2172 in Item_field::replace_with_derived_expr (this=0x7f3210101840, arg=0x7f32a4265ac0 "\360\021\020\020\062\177")
    at /data/code/mysql-server/sql/item.cc:1087
#12 0x00000000038a1468 in Item::transform (this=0x7f3210101840, transformer=&virtual Item::replace_with_derived_expr(unsigned char*), 
    arg=0x7f32a4265ac0 "\360\021\020\020\062\177") at /data/code/mysql-server/sql/item.cc:780
#13 0x000000000393393c in Item_func::transform (this=0x7f3210101a30, transformer=&virtual table offset 992, 
    argument=0x7f32a4265ac0 "\360\021\020\020\062\177") at /data/code/mysql-server/sql/item_func.cc:660
#14 0x0000000003babfbe in Condition_pushdown::replace_columns_in_cond (this=0x7f32a4265c80, cond=0x7f32a4265ca8, is_having=false)
    at /data/code/mysql-server/sql/sql_derived.cc:1432
#15 0x0000000003baaf1e in Condition_pushdown::make_cond_for_derived (this=0x7f32a4265c80)
    at /data/code/mysql-server/sql/sql_derived.cc:1118
#16 0x00000000034ebb3c in Query_block::push_conditions_to_derived_tables (this=0x7f32100fb628, thd=0x7f321017e970)
    at /data/code/mysql-server/sql/sql_resolver.cc:629
#17 0x00000000034ec300 in Query_block::apply_local_transforms (this=0x7f32100fb628, thd=0x7f321017e970, prune=true)
    at /data/code/mysql-server/sql/sql_resolver.cc:834
#18 0x00000000034eb88f in Query_block::prepare (this=0x7f32100fb628, thd=0x7f321017e970, insert_field_list=0x0)
    at /data/code/mysql-server/sql/sql_resolver.cc:593
#19 0x000000000351a94d in Sql_cmd_select::prepare_inner (this=0x7f3210102cc0, thd=0x7f321017e970)
    at /data/code/mysql-server/sql/sql_select.cc:651
#20 0x000000000351a3a9 in Sql_cmd_dml::prepare (this=0x7f3210102cc0, thd=0x7f321017e970) at /data/code/mysql-server/sql/sql_select.cc:567
#21 0x000000000351abd8 in Sql_cmd_dml::execute (this=0x7f3210102cc0, thd=0x7f321017e970) at /data/code/mysql-server/sql/sql_select.cc:710
#22 0x0000000003499c38 in mysql_execute_command (thd=0x7f321017e970, first_level=true) at /data/code/mysql-server/sql/sql_parse.cc:4724
--Type <RET> for more, q to quit, c to continue without paging--
#23 0x000000000349bf6b in dispatch_sql_command (thd=0x7f321017e970, parser_state=0x7f32a4267890)
    at /data/code/mysql-server/sql/sql_parse.cc:5385
#24 0x0000000003491e4f in dispatch_command (thd=0x7f321017e970, com_data=0x7f32a4268980, command=COM_QUERY)
    at /data/code/mysql-server/sql/sql_parse.cc:2055
#25 0x000000000348fe68 in do_command (thd=0x7f321017e970) at /data/code/mysql-server/sql/sql_parse.cc:1440
#26 0x00000000036a777d in handle_connection (arg=0xb9d2a20)
    at /data/code/mysql-server/sql/conn_handler/connection_handler_per_thread.cc:303
#27 0x0000000005637db9 in pfs_spawn_thread (arg=0xba537a0) at /data/code/mysql-server/storage/perfschema/pfs.cc:3050
#28 0x00007f32cd4faea5 in start_thread () from /lib64/libpthread.so.0
#29 0x00007f32cb9beb0d in clone () from /lib64/libc.so.6
[23 Apr 8:07] ximin liang 
seems like

Bug#36368181: Assertion failure in operator() at sql/sql_derived.cc

https://github.com/mysql/mysql-server/commit/01d93a623486665c80d2#diff-d2214219f6c3e2553e2...
[23 Apr 8:11] MySQL Verification Team 
Hello ximin liang,

Thank you for the report and test case.
Observed that 8.0.42 debug build is affected.

regards,
Umesh
[14 May 7:51] Erlend Dahl 
Fixed in 8.2.0 under the heading of

Bug#35102220 Assertion failure in Item::walk_helper_thunk at ../sql/item.h