[MDEV-25206] Crash with CREATE VIEW .. SELECT with non-existing field in ON condition Created: 2021-03-19  Updated: 2022-05-21  Resolved: 2021-03-22

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Views
Affects Version/s: 10.4, 10.5, 10.6
Fix Version/s: 10.4.19, 10.5.10

Type: Bug Priority: Blocker
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: regression

Issue Links:
Relates
relates to MDEV-26214 INSERT from SELECT crashes server on ... Closed

 Description    

CREATE TABLE t1 (a INT);
 
INSERT INTO t1 VALUES (1),(2); # Optional, fails either way
 
CREATE TABLE t2 (b INT);
 
INSERT INTO t2 VALUES (3),(4); # Optional, fails either way
 
CREATE VIEW v AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b;

10.4 44d70c01f

 
#3  <signal handler called>
 
#4  0x000055d25f7a578a in Item_field::fix_outer_field (this=0x7f2fdc0163d0, thd=0x7f2fdc000d90, from_field=0x7f2ff34edff0, reference=0x7f2fdc016690) at /data/src/10.4-bug/sql/item.cc:5447
 
#5  0x000055d25f7a7d2f in Item_field::fix_fields (this=0x7f2fdc0163d0, thd=0x7f2fdc000d90, reference=0x7f2fdc016690) at /data/src/10.4-bug/sql/item.cc:5861
 
#6  0x000055d25eeb2e05 in Item::fix_fields_if_needed (this=0x7f2fdc0163d0, thd=0x7f2fdc000d90, ref=0x7f2fdc016690) at /data/src/10.4-bug/sql/item.h:964
 
#7  0x000055d25f853037 in Item_func::fix_fields (this=0x7f2fdc016600, thd=0x7f2fdc000d90, ref=0x7f2fdc015cc8) at /data/src/10.4-bug/sql/item_func.cc:352
 
#8  0x000055d25eeb2e05 in Item::fix_fields_if_needed (this=0x7f2fdc016600, thd=0x7f2fdc000d90, ref=0x7f2fdc015cc8) at /data/src/10.4-bug/sql/item.h:964
 
#9  0x000055d25eeb2e65 in Item::fix_fields_if_needed_for_scalar (this=0x7f2fdc016600, thd=0x7f2fdc000d90, ref=0x7f2fdc015cc8) at /data/src/10.4-bug/sql/item.h:968
 
#10 0x000055d25efba9d3 in Item::fix_fields_if_needed_for_bool (this=0x7f2fdc016600, thd=0x7f2fdc000d90, ref=0x7f2fdc015cc8) at /data/src/10.4-bug/sql/item.h:972
 
#11 0x000055d25efb2a22 in setup_on_expr (thd=0x7f2fdc000d90, table=0x7f2fdc015c68, is_update=false) at /data/src/10.4-bug/sql/sql_base.cc:8343
 
#12 0x000055d25efb3450 in setup_conds (thd=0x7f2fdc000d90, tables=0x7f2fdc015568, leaves=..., conds=0x7f2fdc017e10) at /data/src/10.4-bug/sql/sql_base.cc:8460
 
#13 0x000055d25f15fb8f in setup_without_group (thd=0x7f2fdc000d90, ref_pointer_array=..., tables=0x7f2fdc015568, leaves=..., fields=..., all_fields=..., conds=0x7f2fdc017e10, order=0x0, group=0x0, win_specs=..., win_funcs=..., hidden_group_fields=0x7f2fdc017cef, reserved=0x7f2fdc01528c) at /data/src/10.4-bug/sql/sql_select.cc:689
 
#14 0x000055d25f164fa0 in JOIN::prepare (this=0x7f2fdc017a08, tables_init=0x7f2fdc015568, wild_num=1, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f2fdc014f88, unit_arg=0x7f2fdc004cc8) at /data/src/10.4-bug/sql/sql_select.cc:1243
 
#15 0x000055d25f2d256a in st_select_lex_unit::prepare_join (this=0x7f2fdc004cc8, thd_arg=0x7f2fdc000d90, sl=0x7f2fdc014f88, tmp_result=0x0, additional_options=0, is_union_select=false) at /data/src/10.4-bug/sql/sql_union.cc:646
 
#16 0x000055d25f2d5056 in st_select_lex_unit::prepare (this=0x7f2fdc004cc8, derived_arg=0x0, sel_result=0x0, additional_options=0) at /data/src/10.4-bug/sql/sql_union.cc:993
 
#17 0x000055d25f2f6f4f in mysql_create_view (thd=0x7f2fdc000d90, views=0x7f2fdc0148c0, mode=VIEW_CREATE_NEW) at /data/src/10.4-bug/sql/sql_view.cc:534
 
#18 0x000055d25f0e8745 in mysql_execute_command (thd=0x7f2fdc000d90) at /data/src/10.4-bug/sql/sql_parse.cc:6001
 
#19 0x000055d25f0f452c in mysql_parse (thd=0x7f2fdc000d90, rawbuf=0x7f2fdc014788 "CREATE VIEW v AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b", length=56, parser_state=0x7f2ff34ef550, is_com_multi=false, is_next_command=false) at /data/src/10.4-bug/sql/sql_parse.cc:7989
 
#20 0x000055d25f0ca38e in dispatch_command (command=COM_QUERY, thd=0x7f2fdc000d90, packet=0x7f2fdc00abe1 "CREATE VIEW v AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b", packet_length=56, is_com_multi=false, is_next_command=false) at /data/src/10.4-bug/sql/sql_parse.cc:1855
 
#21 0x000055d25f0c7246 in do_command (thd=0x7f2fdc000d90) at /data/src/10.4-bug/sql/sql_parse.cc:1373
 
#22 0x000055d25f3b9466 in do_handle_one_connection (connect=0x55d264d62900) at /data/src/10.4-bug/sql/sql_connect.cc:1412
 
#23 0x000055d25f3b8dd7 in handle_one_connection (arg=0x55d264d62900) at /data/src/10.4-bug/sql/sql_connect.cc:1316
 
#24 0x000055d2606949bb in pfs_spawn_thread (arg=0x55d264d3b700) at /data/src/10.4-bug/storage/perfschema/pfs.cc:1869
 
#25 0x00007f2ffdda6609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#26 0x00007f2ffd611293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Reproducible with at least MyISAM and InnoDB.

The failure appeared in 10.4 branch after this merge:

commit 44d70c01f0aef419bc1325f0cba6a46085042646 (HEAD)
 
Merge: 126725421e5 867724fd304
 
Author: Marko Mäkelä
 
Date:   Fri Mar 19 11:42:44 2021 +0200
 
 
 
    Merge 10.3 into 10.4

But it's not reproducible on 10.3 with the provided test case, so I can't say which commit from the merge caused it (or whether it was a result of a bad merge).

 Comments   
Comment by Igor Babaev [ 2021-03-20 ]

The following query is also crashes the server in the same way

CREATE TABLE t3 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b;

as well as

CREATE TABLE t4 (c int);
 
INSERT INTO t4 SELECT * FROM t1 JOIN t2 ON t1.x > t2.b;

Comment by Oleksandr Byelkin [ 2021-03-21 ]

ok to push

Comment by Igor Babaev [ 2021-03-22 ]

A fix of this bug was pushed into 10.4

Comment by Roel Van de Paar [ 2021-03-24 ]

I also see this bug in 10.6, build from today.

CREATE TABLE t2 (c INT,d INT);
 
CREATE TABLE t (c CHAR(1)KEY,c2 CHAR(1));
 
ALTER TABLE t ADD COLUMN b INT;
 
CREATE VIEW v2 AS SELECT b FROM t2 JOIN t ON t2.b=t.a;

Leads to:

10.6.0 2e31b2ffe9b443b029e51961ef6b0617df380388 (Debug)

 
Core was generated by `/test/MD230321-mariadb-10.6.0-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
 
Program terminated with signal SIGSEGV, Segmentation fault.
 
#0  __pthread_kill (threadid=<optimized out>, signo=signo@entry=11)
 
    at ../sysdeps/unix/sysv/linux/pthread_kill.c:56
 
[Current thread is 1 (Thread 0x15056c0a9700 (LWP 3201248))]
 
(gdb) bt
 
#0  __pthread_kill (threadid=<optimized out>, signo=signo@entry=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:56
 
#1  0x0000555a3989fdd1 in my_write_core (sig=sig@entry=11) at /test/10.6_dbg/mysys/stacktrace.c:424
 
#2  0x0000555a39040d31 in handle_fatal_signal (sig=11) at /test/10.6_dbg/sql/signal_handler.cc:331
 
#3  <signal handler called>
 
#4  Item_field::fix_outer_field (this=this@entry=0x150520015848, thd=thd@entry=0x150520000db8, from_field=from_field@entry=0x15056c0a6f60, reference=reference@entry=0x150520015b70) at /test/10.6_dbg/sql/item.cc:5518
 
#5  0x0000555a3907c502 in Item_field::fix_fields (this=0x150520015848, thd=0x150520000db8, reference=0x150520015b70) at /test/10.6_dbg/sql/item.cc:5934
 
#6  0x0000555a390d5479 in Item::fix_fields_if_needed (ref=0x150520015b70, thd=0x150520000db8, this=0x150520015848) at /test/10.6_dbg/sql/item.h:988
 
#7  Item_func::fix_fields (this=0x150520015ad8, thd=0x150520000db8, ref=<optimized out>) at /test/10.6_dbg/sql/item_func.cc:352
 
#8  0x0000555a38cf5f40 in Item::fix_fields_if_needed (ref=0x150520015130, thd=0x150520000db8, this=0x150520015ad8) at /test/10.6_dbg/sql/item.h:996
 
#9  Item::fix_fields_if_needed_for_scalar (ref=0x150520015130, thd=0x150520000db8, this=0x150520015ad8) at /test/10.6_dbg/sql/item.h:992
 
#10 Item::fix_fields_if_needed_for_bool (ref=0x150520015130, thd=0x150520000db8, this=0x150520015ad8) at /test/10.6_dbg/sql/item.h:996
 
#11 setup_on_expr (thd=thd@entry=0x150520000db8, table=0x1505200150d0, table@entry=0x1505200149c0, is_update=is_update@entry=false) at /test/10.6_dbg/sql/sql_base.cc:8293
 
#12 0x0000555a38cf635d in setup_conds (thd=thd@entry=0x150520000db8, tables=tables@entry=0x1505200149c0, leaves=@0x1505200145e8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x150520017470, last = 0x150520017480, elements = 2}, <No data fields>}, conds=conds@entry=0x150520017308) at /test/10.6_dbg/sql/sql_base.cc:8410
 
#13 0x0000555a38de7558 in setup_without_group (reserved=0x1505200146b4, hidden_group_fields=0x1505200171df, win_funcs=@0x1505200147f0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x555a3a348260 <end_of_list>, last = 0x1505200147f0, elements = 0}, <No data fields>}, win_specs=@0x1505200147d8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x555a3a348260 <end_of_list>, last = 0x1505200147d8, elements = 0}, <No data fields>}, group=0x0, order=0x0, conds=0x150520017308, all_fields=@0x150520017220: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x150520014978, last = 0x150520014978, elements = 1}, <No data fields>}, fields=@0x1505200144f8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x150520014978, last = 0x150520014978, elements = 1}, <No data fields>}, leaves=@0x1505200145e8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x150520017470, last = 0x150520017480, elements = 2}, <No data fields>}, tables=0x1505200149c0, ref_pointer_array=<optimized out>, thd=0x150520000db8) at /test/10.6_dbg/sql/sql_select.cc:696
 
#14 JOIN::prepare (this=this@entry=0x150520016ef8, tables_init=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=<optimized out>, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x1505200143a8, unit_arg=0x150520004f90) at /test/10.6_dbg/sql/sql_select.cc:1248
 
#15 0x0000555a38e6c8c4 in st_select_lex_unit::prepare_join (this=this@entry=0x150520004f90, thd_arg=0x150520000db8, sl=sl@entry=0x1505200143a8, tmp_result=tmp_result@entry=0x0, additional_options=additional_options@entry=0, is_union_select=is_union_select@entry=false) at /test/10.6_dbg/sql/sql_union.cc:1088
 
#16 0x0000555a38e70ed4 in st_select_lex_unit::prepare (this=this@entry=0x150520004f90, derived_arg=0x0, sel_result=sel_result@entry=0x0, additional_options=additional_options@entry=0) at /test/10.6_dbg/sql/sql_union.cc:1481
 
#17 0x0000555a38e7ef6d in mysql_create_view (thd=thd@entry=0x150520000db8, views=views@entry=0x150520013cd0, mode=VIEW_CREATE_NEW) at /test/10.6_dbg/sql/sql_view.cc:542
 
#18 0x0000555a38d860fe in mysql_execute_command (thd=thd@entry=0x150520000db8) at /test/10.6_dbg/sql/sql_parse.cc:5775
 
#19 0x0000555a38d6d264 in mysql_parse (thd=thd@entry=0x150520000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x15056c0a8410) at /test/10.6_dbg/sql/sql_parse.cc:8004
 
#20 0x0000555a38d7be6a in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x150520000db8, packet=packet@entry=0x15052000b359 "CREATE VIEW v2 AS SELECT b FROM t2 JOIN t ON t2.b=t.a", packet_length=packet_length@entry=53, blocking=blocking@entry=true) at /test/10.6_dbg/sql/sql_class.h:1331
 
#21 0x0000555a38d7f245 in do_command (thd=0x150520000db8, blocking=blocking@entry=true) at /test/10.6_dbg/sql/sql_parse.cc:1399
 
#22 0x0000555a38ed8466 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x555a3c446838, put_in_cache=put_in_cache@entry=true) at /test/10.6_dbg/sql/sql_connect.cc:1410
 
#23 0x0000555a38ed8a6b in handle_one_connection (arg=arg@entry=0x555a3c446838) at /test/10.6_dbg/sql/sql_connect.cc:1312
 
#24 0x0000555a39382eb9 in pfs_spawn_thread (arg=0x555a3c35cd48) at /test/10.6_dbg/storage/perfschema/pfs.cc:2201
 
#25 0x0000150574a38609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#26 0x0000150574627293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Bug confirmed present in:
MariaDB: 10.6.0 (dbg), 10.6.0 (opt)

Comment by Roel Van de Paar [ 2021-03-31 ]

In new build 1/4/21:

10.6.0 1bd4115841ecded24217e5d753ed4d9822b4cffd (Debug)

 
10.6.0>CREATE VIEW v2 AS SELECT b FROM t2 JOIN t ON t2.b=t.a;
 
ERROR 1054 (42S22): Unknown column 't2.b' in 'on clause'

Generated at Thu Feb 08 09:35:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.