[MDEV-14574] Assertion `!join->select_lex->master_unit()->derived || join->select_lex->master_unit()->derived->is_materialized_derived()' failed upon EXPLAIN INSERT .. SELECT into join view Created: 2017-12-05  Updated: 2023-12-14

Status: Confirmed
Project: MariaDB Server
Component/s: Admin statements, Data Manipulation - Insert, Views
Affects Version/s: 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: not-10.5+

Issue Links:
Relates
relates to MDEV-33010 Crash MariaDB Server after repeating ... Confirmed

 Description    

CREATE TABLE t (f INT);
 
CREATE ALGORITHM = MERGE VIEW v AS SELECT a2.f FROM t AS a1, t AS a2;
 
EXPLAIN INSERT INTO v (f) SELECT 1;
 
 
 
# Cleanup
 
DROP VIEW v;
 
DROP TABLE t;

10.1 1eca49577e979220f3ab663a7e46e0eb70d728c4

 
mysqld: /data/src/10.1/sql/sql_select.cc:24686: int JOIN::save_explain_data_intern(Explain_query*, bool, bool, bool, const char*): Assertion `!join->select_lex->master_unit()->derived || join->select_lex->master_unit()->derived->is_materialized_derived()' failed.
 
181013 19:28:17 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007fcfc5e6cee2 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
 
#8  0x000055eac6d39964 in JOIN::save_explain_data_intern (this=0x7fcfbd046b60, output=0x7fcfbd047948, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at /data/src/10.1/sql/sql_select.cc:24685
 
#9  0x000055eac6d01269 in JOIN::save_explain_data (this=0x7fcfbd046b60, output=0x7fcfbd047948, can_overwrite=false, need_tmp_table=false, need_order=false, distinct=false) at /data/src/10.1/sql/sql_select.cc:2531
 
#10 0x000055eac6cfc07d in JOIN::optimize (this=0x7fcfbd046b60) at /data/src/10.1/sql/sql_select.cc:1066
 
#11 0x000055eac6d04906 in mysql_select (thd=0x7fcfbecd5070, rref_pointer_array=0x7fcfbd0444d0, tables=0x7fcfbd045190, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416184068, result=0x7fcfbd047268, unit=0x7fcfbd043b28, select_lex=0x7fcfbd044228) at /data/src/10.1/sql/sql_select.cc:3485
 
#12 0x000055eac6d3a67b in mysql_explain_union (thd=0x7fcfbecd5070, unit=0x7fcfbd043b28, result=0x7fcfbd047268) at /data/src/10.1/sql/sql_select.cc:24955
 
#13 0x000055eac6d3a3ff in select_describe (join=0x7fcfbd047308, need_tmp_table=false, need_order=false, distinct=false, message=0x55eac761fe97 "No tables used") at /data/src/10.1/sql/sql_select.cc:24909
 
#14 0x000055eac6d0176e in JOIN::exec_inner (this=0x7fcfbd047308) at /data/src/10.1/sql/sql_select.cc:2616
 
#15 0x000055eac6d01403 in JOIN::exec (this=0x7fcfbd047308) at /data/src/10.1/sql/sql_select.cc:2562
 
#16 0x000055eac6d04999 in mysql_select (thd=0x7fcfbecd5070, rref_pointer_array=0x7fcfbecd9550, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416184068, result=0x7fcfbd047268, unit=0x7fcfbecd8ba8, select_lex=0x7fcfbecd92a8) at /data/src/10.1/sql/sql_select.cc:3499
 
#17 0x000055eac6d3a67b in mysql_explain_union (thd=0x7fcfbecd5070, unit=0x7fcfbecd8ba8, result=0x7fcfbd047268) at /data/src/10.1/sql/sql_select.cc:24955
 
#18 0x000055eac6cc2e4b in mysql_execute_command (thd=0x7fcfbecd5070) at /data/src/10.1/sql/sql_parse.cc:4037
 
#19 0x000055eac6ccd625 in mysql_parse (thd=0x7fcfbecd5070, rawbuf=0x7fcfbd043088 "EXPLAIN INSERT INTO v (f) SELECT 1", length=34, parser_state=0x7fcfc7ef05e0) at /data/src/10.1/sql/sql_parse.cc:7465
 
#20 0x000055eac6cbbe98 in dispatch_command (command=COM_QUERY, thd=0x7fcfbecd5070, packet=0x7fcfc17f9071 "EXPLAIN INSERT INTO v (f) SELECT 1", packet_length=34) at /data/src/10.1/sql/sql_parse.cc:1495
 
#21 0x000055eac6cbac1d in do_command (thd=0x7fcfbecd5070) at /data/src/10.1/sql/sql_parse.cc:1124
 
#22 0x000055eac6df5606 in do_handle_one_connection (thd_arg=0x7fcfbecd5070) at /data/src/10.1/sql/sql_connect.cc:1330
 
#23 0x000055eac6df536a in handle_one_connection (arg=0x7fcfbecd5070) at /data/src/10.1/sql/sql_connect.cc:1242
 
#24 0x000055eac71b3208 in pfs_spawn_thread (arg=0x7fcfc5439ef0) at /data/src/10.1/storage/perfschema/pfs.cc:1861
 
#25 0x00007fcfc7b70494 in start_thread (arg=0x7fcfc7ef1b00) at pthread_create.c:333
 
#26 0x00007fcfc5f2993f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Not reproducible on 10.0.
No obvious problems on a non-debug build.

 Comments   
Comment by Elena Stepanova [ 2023-06-09 ]

Here is a special variation of apparently the same use case which produces a SIGSEGV in optimize_keyuse instead of the reported assertion.
Given that any changes to the test case convert it back to the reported assertion, and that again only 10.4 is affected, I assume it's the same or a closely related problem.

CREATE TABLE t1 (a INT, b INT, c INT, d INT, KEY(d));
 
INSERT INTO t1 VALUES (1,2,3,4),(5,6,7,8);
 
CREATE TABLE t2 (e INT, f INT, g INT, h INT, KEY (f));
 
INSERT INTO t2 VALUES (1,2,3,4),(5,6,7,8);
 
CREATE VIEW v AS SELECT a, b, c, d FROM t1, t2 WHERE t1.d <=> t2.g AND t2.e <=> 215;
 
 
 
EXPLAIN INSERT INTO v (a, b, c, d) SELECT * FROM t2 WHERE h > 0 ORDER BY e, f, g, h;
 
 
 
# Cleanup
 
DROP VIEW v;
 
DROP TABLE t1, t2;

10.4 f5dceafd

 
#3  <signal handler called>
 
#4  0x00005641cad66837 in optimize_keyuse (join=0x62900024bd20, keyuse_array=0x62900024c010) at /data/src/10.4/sql/sql_select.cc:7119
 
#5  0x00005641cad599ee in make_join_statistics (join=0x62900024bd20, tables_list=..., keyuse_array=0x62900024c010) at /data/src/10.4/sql/sql_select.cc:5686
 
#6  0x00005641cad377da in JOIN::optimize_inner (this=0x62900024bd20) at /data/src/10.4/sql/sql_select.cc:2368
 
#7  0x00005641cad30791 in JOIN::optimize (this=0x62900024bd20) at /data/src/10.4/sql/sql_select.cc:1711
 
#8  0x00005641cad51665 in mysql_select (thd=0x62b00005b208, tables=0x629000249228, wild_num=0, fields=..., conds=0x62900024aba8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416184068, result=0x62900024c988, unit=0x62b000065370, select_lex=0x62b000066f28) at /data/src/10.4/sql/sql_select.cc:4812
 
#9  0x00005641cadf7f7a in mysql_explain_union (thd=0x62b00005b208, unit=0x62b000065370, result=0x62900024c988) at /data/src/10.4/sql/sql_select.cc:27707
 
#10 0x00005641cadf74a9 in select_describe (join=0x62900024ca40, need_tmp_table=false, need_order=true, distinct=false, message=0x0) at /data/src/10.4/sql/sql_select.cc:27661
 
#11 0x00005641cad4fb42 in JOIN::exec_inner (this=0x62900024ca40) at /data/src/10.4/sql/sql_select.cc:4567
 
#12 0x00005641cad4d7c4 in JOIN::exec (this=0x62900024ca40) at /data/src/10.4/sql/sql_select.cc:4387
 
#13 0x00005641cad51856 in mysql_select (thd=0x62b00005b208, tables=0x62b0000635f8, wild_num=1, fields=..., conds=0x62b000063ee8, og_num=4, order=0x62b000064ac0, group=0x0, having=0x0, proc_param=0x0, select_options=2416184068, result=0x62900024c988, unit=0x62b00005f140, select_lex=0x62b000062fb0) at /data/src/10.4/sql/sql_select.cc:4826
 
#14 0x00005641cadf7f7a in mysql_explain_union (thd=0x62b00005b208, unit=0x62b00005f140, result=0x62900024c988) at /data/src/10.4/sql/sql_select.cc:27707
 
#15 0x00005641cac846e8 in mysql_execute_command (thd=0x62b00005b208) at /data/src/10.4/sql/sql_parse.cc:4725
 
#16 0x00005641cac9b463 in mysql_parse (thd=0x62b00005b208, rawbuf=0x62b000062228 "EXPLAIN INSERT INTO v (a, b, c, d) SELECT * FROM t2 WHERE h > 0 ORDER BY e, f, g, h", length=83, parser_state=0x7fc5d08c2860, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:8008
 
#17 0x00005641cac717a6 in dispatch_command (command=COM_QUERY, thd=0x62b00005b208, packet=0x629000230209 "EXPLAIN INSERT INTO v (a, b, c, d) SELECT * FROM t2 WHERE h > 0 ORDER BY e, f, g, h", packet_length=83, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1857
 
#18 0x00005641cac6e315 in do_command (thd=0x62b00005b208) at /data/src/10.4/sql/sql_parse.cc:1378
 
#19 0x00005641cb06d0ba in do_handle_one_connection (connect=0x6080000009a8) at /data/src/10.4/sql/sql_connect.cc:1420
 
#20 0x00005641cb06c9d1 in handle_one_connection (arg=0x6080000009a8) at /data/src/10.4/sql/sql_connect.cc:1324
 
#21 0x00005641cbcd9aee in pfs_spawn_thread (arg=0x615000003508) at /data/src/10.4/storage/perfschema/pfs.cc:1869
 
#22 0x00007fc5d86a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#23 0x00007fc5d87285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

Generated at Thu Feb 08 08:14:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.