[MDEV-24488] Cannot use CTE in trigger definition Created: 2020-12-24  Updated: 2023-08-27

Status: Confirmed
Project: MariaDB Server
Component/s: Triggers
Affects Version/s: 10.5.8, 10.2, 10.3, 10.4, 10.5, 10.4.32
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Travis Leith Assignee: Igor Babaev
Resolution: Unresolved Votes: 1
Labels: CTEs, Triggers, recursive
Environment:

Ubuntu running on WSL

Issue Links:
Blocks
is blocked by MDEV-23886 Reusing CTE inside a function fails w... Closed
Relates
relates to MDEV-32022 ERROR 1054 (42S22): Unknown column 'X... Open

 Description   

The use of a recursive CTE in trigger body leads to an error.

Reproduction:

create or replace table base_data(
 
    id int auto_increment not null primary key,
 
    record_data longtext not null check (json_valid(record_data))
 
);
 
 
 
insert into base_data(record_data)
 
values
 
('{"x":1, "y":[2,3,4]}')
 
;
 
 
 
create or replace table mv_integers as
 
with recursive array_values as (
 
    select 
        json_extract(b.record_data, '$.y[*]') val
 
        , b.id
 
        , json_length(json_extract(b.record_data, '$.y[*]')) n
 
        from base_data b
 
), rec_elems as (
 
    select av.id, 0 as i, json_extract(av.val, '$[0]') elem
 
    from array_values av
 
    where av.n > 0
 
    union all
 
    select av.id, e.i + 1, json_extract(av.val, concat('$[', e.i + 1, ']'))
 
    from array_values av
 
        inner join rec_elems e on av.id = e.id
 
    where (i + 1) < av.n
 
)
 
select r.id record_id, r.elem extracted_integer, 'y' as 'comes_from'
 
from rec_elems r
 
union all
 
select b.id, json_value(b.record_data, '$.x'), 'x'
 
from base_data b
 
;
 
 
 
create or replace trigger maintain_mv_integers
 
after insert on base_data for each row 
insert mv_integers(record_id, extracted_integer, comes_from)
 
with recursive array_values as (
 
    select 
        json_extract(new.record_data, '$.y[*]') val
 
        , new.id
 
        , json_length(json_extract(new.record_data, '$.y[*]')) n
 
), rec_elems as (
 
    select av.id, 0 as i, json_extract(av.val, '$[0]') elem
 
    from array_values av
 
    where av.n > 0
 
    union all
 
    select av.id, e.i + 1, json_extract(av.val, concat('$[', e.i + 1, ']'))
 
    from array_values av
 
        inner join rec_elems e on av.id = e.id
 
    where (i + 1) < av.n
 
)
 
select r.id record_id, r.elem extracted_integer, 'y' as 'comes_from'
 
from rec_elems r
 
union all
 
select new.id, json_value(new.record_data, '$.x'), 'x'
 
;
 
 
 
insert into base_data(record_data)
 
values
 
('{"x":5, "y":[6,7,4]}')
 
;



 Comments   
Comment by Travis Leith [ 2020-12-24 ]

a workaround is to put all logic in a stored procedure and call the SP from the trigger: https://stackoverflow.com/questions/65431349/how-can-i-create-a-trigger-to-unpack-json-data/65437469#65437469

Comment by Alice Sherepa [ 2020-12-24 ]

Thank you for the report!

create table t1(a int);
 
create table t2(a int);
 
 
 
create trigger tr1 after insert on t1 for each row 
insert into t2
 
    with cte1 as (select new.a), cte2 as (select a from cte1 union select 1 from cte1) select * from cte2;
 
 
insert into t1 values (1);
 
 
 
drop table t1,t2;

On debug version of 10.2 test fails with the assertion `!new_row || (lex->trg_chistics.event == TRG_EVENT_INSERT || lex->trg_chistics.event == TRG_EVENT_UPDATE)', on 10.3-10.5 it returns error "1054: Unknown column 'a' in 'NEW'" (on non-debug builds error on 10.2-10.5)

10.2

 
Version: '10.2.37-MariaDB-debug-log' 
mysqld: 10.2/src/sql/sql_yacc.yy:14293: int MYSQLparse(THD*): Assertion `!new_row || (lex->trg_chistics.event == TRG_EVENT_INSERT || lex->trg_chistics.event == TRG_EVENT_UPDATE)' failed.
 
201224 13:26:45 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.2.37-MariaDB-debug-log
 
 
 
linux/raise.c:51(__GI_raise)[0x7f07f8aa118b]
 
stdlib/abort.c:81(__GI_abort)[0x7f07f8a80859]
 
intl/loadmsgcat.c:509(get_sysdep_segment_value)[0x7f07f8a80729]
 
:0(__GI___assert_fail)[0x7f07f8a91f36]
 
sql/sql_yacc.yy:14297(MYSQLparse(THD*))[0x560b3815892c]
 
sql/sql_parse.cc:10061(parse_sql(THD*, Parser_state*, Object_creation_ctx*, bool))[0x560b37f45402]
 
sql/sql_cte.cc:847(With_element::clone_parsed_spec(THD*, TABLE_LIST*))[0x560b3810d297]
 
sql/sql_cte.cc:1125(TABLE_LIST::set_as_with_table(THD*, With_element*))[0x560b3810dcbb]
 
sql/sql_base.cc:3444(open_and_process_table(THD*, TABLE_LIST*, unsigned int*, unsigned int, Prelocking_strategy*, bool, Open_table_context*))[0x560b37ec2410]
 
sql/sql_base.cc:4081(open_tables(THD*, DDL_options_st const&, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*))[0x560b37ec386f]
 
sql/sql_base.cc:4850(open_and_lock_tables(THD*, DDL_options_st const&, TABLE_LIST*, bool, unsigned int, Prelocking_strategy*))[0x560b37ec5134]
 
sql/sql_base.h:507(open_and_lock_tables(THD*, TABLE_LIST*, bool, unsigned int))[0x560b37e8a659]
 
sql/sql_parse.cc:4274(mysql_execute_command(THD*))[0x560b37f351a7]
 
sql/sp_head.cc:3332(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x560b37e8499a]
 
sql/sp_head.cc:3095(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x560b37e83ffb]
 
sql/sp_head.cc:3248(sp_instr_stmt::execute(THD*, unsigned int*))[0x560b37e8463f]
 
sql/sp_head.cc:1326(sp_head::execute(THD*, bool))[0x560b37e7f613]
 
sql/sp_head.cc:1742(sp_head::execute_trigger(THD*, st_mysql_lex_string const*, st_mysql_lex_string const*, st_grant_info*))[0x560b37e80380]
 
sql/sql_trigger.cc:2209(Table_triggers_list::process_triggers(THD*, trg_event_type, trg_action_time_type, bool))[0x560b3801b761]
 
sql/sql_insert.cc:1955(write_record(THD*, TABLE*, st_copy_info*))[0x560b37f10304]
 
sql/sql_insert.cc:1066(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool))[0x560b37f0dbc3]
 
sql/sql_parse.cc:4193(mysql_execute_command(THD*))[0x560b37f34c66]
 
sql/sql_parse.cc:7762(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x560b37f40321]
 
sql/sql_parse.cc:1830(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x560b37f2e61a]
 
sql/sql_parse.cc:1381(do_command(THD*))[0x560b37f2d115]
 
sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x560b38087514]
 
sql/sql_connect.cc:1242(handle_one_connection)[0x560b38087279]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x560b388af7d4]
 
nptl/pthread_create.c:478(start_thread)[0x7f07f8fa3609]
 
x86_64/clone.S:97(__GI___clone)[0x7f07f8b7d293]
 
 
 
Query (0x7f07e007dc58): insert into t2
 
with cte1 as (select new.a), cte2 as (select a from cte1 union select 1 from cte1) select * from cte2

Comment by Igor Babaev [ 2021-05-05 ]

This bug will be fixed by the patch for MDEV-23886.

Comment by Daniel Black [ 2023-08-27 ]

igor, retested the original test case and alice's simplification and both still fail as described.

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