[MDEV-24488] Cannot use CTE in trigger definition - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.5.8, 10.4.32, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.5, 10.6, 10.11
Component/s: Optimizer, Triggers
Labels:
- CTEs
- Triggers
- recursive
Environment:
Ubuntu running on WSL

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

MDEV-32022 ERROR 1054 (42S22): Unknown column 'X' in 'NEW' in trigger

Closed

Activity

Ascending order - Click to sort in descending order

Travis Leith created issue - 2020-12-24 11:17

Travis Leith added a comment - 2020-12-24 11:20

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

Travis Leith added a comment - 2020-12-24 11:20 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

Alice Sherepa added a comment - 2020-12-24 12:31 - edited

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

Alice Sherepa added a comment - 2020-12-24 12:31 - edited 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

Alice Sherepa made changes - 2020-12-24 12:32

Field	Original Value	New Value
Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]

Alice Sherepa made changes - 2020-12-24 12:32

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]

Alice Sherepa made changes - 2020-12-24 12:32

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2020-12-24 12:32

Assignee

Igor Babaev [ igor ]

Sergei Golubchik made changes - 2021-04-29 14:00

Summary

Cannot use recursive CTE in trigger definition

Cannot use CTE in trigger definition

Igor Babaev (Inactive) added a comment - 2021-05-05 23:24

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

Igor Babaev (Inactive) added a comment - 2021-05-05 23:24 This bug will be fixed by the patch for MDEV-23886 .

Igor Babaev (Inactive) made changes - 2021-05-05 23:26

Link

This issue is blocked by ~~MDEV-23886~~ [ ~~MDEV-23886~~ ]

Sergei Golubchik made changes - 2021-12-06 21:36

Workflow

MariaDB v3 [ 117355 ]

MariaDB v4 [ 144327 ]

Ralf Gebhardt made changes - 2022-08-04 08:44

Fix Version/s

10.2 [ 14601 ]

Julien Fritsch made changes - 2023-04-27 14:02

Fix Version/s

10.3 [ 22126 ]

Daniel Black made changes - 2023-08-27 04:02

Affects Version/s

10.4.32 [ 29300 ]

Daniel Black added a comment - 2023-08-27 04:03

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

Daniel Black added a comment - 2023-08-27 04:03 igor , retested the original test case and alice 's simplification and both still fail as described.

Daniel Black made changes - 2023-08-27 04:30

Link

This issue relates to ~~MDEV-32022~~ [ ~~MDEV-32022~~ ]

Julien Fritsch made changes - 2024-09-10 15:04

Fix Version/s

10.4 [ 22408 ]

Alice Sherepa made changes - 2024-10-23 14:03

Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.11 [ 27614 ]

Michael Widenius made changes - 9 hours ago

Component/s

Optimizer [ 10200 ]

Michael Widenius made changes - 9 hours ago

Assignee

Igor Babaev [ igor ]

People

Assignee:: Unassigned

Reporter:: Travis Leith

Votes:: 1 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2020-12-24 11:17

Updated:: 9 hours ago

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration