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

Cannot use CTE in trigger definition

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5.8, 10.2, 10.3, 10.4, 10.5, 10.4.32
    • 10.4, 10.5
    • Triggers
    • 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

          Activity

            People

              igor Igor Babaev
              travis84 Travis Leith
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.