Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24488

Cannot use CTE in trigger definition

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              travis84 Travis Leith
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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