[MDEV-21547] Problem trigger with OLD. or NEW. values usage Created: 2020-01-21  Updated: 2023-12-12

Status: Confirmed
Project: MariaDB Server
Component/s: Server, Stored routines
Affects Version/s: 10.4.11, 10.2, 10.3, 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3

Type: Bug Priority: Major
Reporter: Eimantas Assignee: Dmitry Shulga
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Any

Attachments: File TriggerBug.SQL    

 Description   

Have trigger functionality for data "change log" collecting in separate table.
Trigger compares OLD. with NEW. and changes are inserted into H table.

This works correctly with MySQL (5.6, 8.0 atleast).

In MariaDB this does not work.

Script provided shoud produce 4+5+4 rows in H table, and should not end up with error
Unknown column 'I4' in 'field list'

Now works only middle part of trigger - with removed WHERE clause.

 Comments   
Comment by Alice Sherepa [ 2020-01-24 ]

Thanks for the report! I repeated on current MariaDB 10.2-10.4, works correct on 5.5-10.1

create table t1 (id int);
 
insert into t1 values (111);
 
create table t2 (b1 int, b2 int);
 
 
 
delimiter $$;
 
 
 
create trigger tr1 before update on t1 for each row 
begin
 
 
 
insert into t2 select dt.o, dt.n from (select 1 o, 1 n union select old.id, new.id) as dt;
 
insert into t2 select dt.o, dt.n from (select 1 o, 1 n union select old.id, new.id) as dt where dt.o<>dt.n; # does not insert 
insert into t2 select dt.o, dt.n from (select old.id o, new.id n) as dt where dt.o<>dt.n; ##produces  Error 1054: Unknown column 'id' in 'field list'
 
 
 
end $$
 
delimiter ;$$
 
 
 
update t1 set id=4321;
 
select * from t2;
 
 
 
drop trigger tr1;
 
drop table t1,t2;

Comment by Igor Babaev [ 2020-06-05 ]

Alice,
Why did you assign this bug? Triggers are like stored procedures

Comment by Alice Sherepa [ 2020-06-25 ]

with ASAN use-after-poison:

10.4 dc68846ec5ffdd6f08d93dc3bd

 
Version: '10.4.14-MariaDB-debug-log' 
=================================================================
 
==17183==ERROR: AddressSanitizer: use-after-poison on address 0x619000090750 at pc 0x560500409f79 bp 0x7f18d0290660 sp 0x7f18d0290658
 
READ of size 1 at 0x619000090750 thread T5
 
    #0 0x560500409f78 in Field::is_null_in_record(unsigned char const*) const /10.4/sql/field.h:1185
 
    #1 0x5605003b8c74 in create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, st_mysql_const_lex_string const*, bool, bool) /10.4/sql/sql_select.cc:18646
 
    #2 0x560500524633 in select_unit::create_result_table(THD*, List<Item>*, bool, unsigned long long, st_mysql_const_lex_string const*, bool, bool, bool, unsigned int) /10.4/sql/sql_union.cc:372
 
    #3 0x5605001bb432 in mysql_derived_prepare /10.4/sql/sql_derived.cc:845
 
    #4 0x5605001b7457 in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /10.4/sql/sql_derived.cc:206
 
    #5 0x5605005afd02 in TABLE_LIST::handle_derived(LEX*, unsigned int) /10.4/sql/table.cc:8821
 
    #6 0x5605001fd217 in LEX::handle_list_of_derived(TABLE_LIST*, unsigned int) /10.4/sql/sql_lex.h:4329
 
    #7 0x5605001dc4fe in mysql_prepare_insert(THD*, TABLE_LIST*, TABLE*, List<Item>&, List<Item>*, List<Item>&, List<Item>&, enum_duplicates, Item**, bool) /10.4/sql/sql_insert.cc:1531
 
    #8 0x5605001ec89c in mysql_insert_select_prepare(THD*) /10.4/sql/sql_insert.cc:3646
 
    #9 0x560500290eb5 in mysql_execute_command(THD*) /10.4/sql/sql_parse.cc:4626
 
    #10 0x56050005ba87 in sp_instr_stmt::exec_core(THD*, unsigned int*) /10.4/sql/sp_head.cc:3687
 
    #11 0x56050005a29b in sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) /10.4/sql/sp_head.cc:3419
 
    #12 0x56050005b236 in sp_instr_stmt::execute(THD*, unsigned int*) /10.4/sql/sp_head.cc:3593
 
    #13 0x56050004cda0 in sp_head::execute(THD*, bool) /10.4/sql/sp_head.cc:1364
 
    #14 0x56050004fb56 in sp_head::execute_trigger(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, st_grant_info*) /10.4/sql/sp_head.cc:1837
 
    #15 0x5605005177c5 in Table_triggers_list::process_triggers(THD*, trg_event_type, trg_action_time_type, bool) /10.4/sql/sql_trigger.cc:2211
 
    #16 0x56050012a12e in fill_record_n_invoke_before_triggers(THD*, TABLE*, List<Item>&, List<Item>&, bool, trg_event_type) /10.4/sql/sql_base.cc:8702
 
    #17 0x560500541199 in mysql_update(THD*, TABLE_LIST*, List<Item>&, List<Item>&, Item*, unsigned int, st_order*, unsigned long long, bool, unsigned long long*, unsigned long long*) /10.4/sql/sql_update.cc:991
 
    #18 0x56050028f0a3 in mysql_execute_command(THD*) /10.4/sql/sql_parse.cc:4369
 
    #19 0x5605002a7aa4 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.4/sql/sql_parse.cc:7901
 
    #20 0x56050027f0f5 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.4/sql/sql_parse.cc:1841
 
    #21 0x56050027bb74 in do_command(THD*) /10.4/sql/sql_parse.cc:1359
 
    #22 0x560500654e30 in do_handle_one_connection(CONNECT*) /10.4/sql/sql_connect.cc:1412
 
    #23 0x5605006546d2 in handle_one_connection /10.4/sql/sql_connect.cc:1316
 
    #24 0x560501c7cf6c in pfs_spawn_thread /10.4/storage/perfschema/pfs.cc:1869
 
    #25 0x7f18d9d08fa2 in start_thread /build/glibc-vjB4T1/glibc-2.28/nptl/pthread_create.c:486
 
    #26 0x7f18d930f4ce in clone (/lib/x86_64-linux-gnu/libc.so.6+0xf94ce)

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