Details
Major 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]}')
|
;
|
Attachments
Issue Links
- is blocked by
-
MDEV-23886 Reusing CTE inside a function fails with table doesn't exist
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
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
|
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