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

"Table Does Not Exist" Error from Recursive CTE Query Inside Function

    Details

      Description

      I'm trying to wrap a recursive cte query inside a function. The query works outside the function and the function statement itself is accepted without error. When the function is used however it reports that the table I'm trying to query inside the function does not exist. A "use" statement is used before the function definition and specifying the database.table combination does not change the result.

      Here is the exact error if that is helpful:

      ERROR 1146 (42S02) at line 33: Table 'ck2.Characters' doesn't exist

      I've attached the full SQL, but this is a isolated example that has the same problem:

      create or replace database ck2;
      use ck2;
       
      create table Characters (
          id int unsigned key,
       
          mother_id int unsigned check (mother_id != id),
          foreign key (mother_id)
              references Characters(id)
              on delete cascade,
       
          real_father_id int unsigned check(real_father_id != id),
          foreign key (real_father_id)
              references Characters(id)
              on delete cascade
      );
       
      insert into Characters values
          (0, null, null),
          (1, 0, null),
          (2, 1, null)
      ;
       
      create or replace function count_descendants(cid int unsigned) returns int unsigned return (
          with recursive Descendants as (
              select * from Characters where id = cid
              union
              select c.* from Characters as c, Descendants as d        
                  where d.id = c.mother_id or d.id = c.real_father_id
          ) select count(distinct(id)) from Descendants
          );
       
      select count_descendants(0);
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                igor Igor Babaev
                Reporter:
                iguessthislldo Fred Hornsey
              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: