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

Failure to resolve column's name of recursive CTE in prepared mode.

    XMLWordPrintable

    Details

      Description

      The following test fails:

      create table  folks(id int, name char(32), dob date, father int, mother int);
       
      insert into folks values
      (100, 'Me', '2000-01-01', 20, 30),
      (20, 'Dad', '1970-02-02', 10, 9),
      (30, 'Mom', '1975-03-03', 8, 7),
      (10, 'Grandpa Bill', '1940-04-05', null, null),
      (9, 'Grandma Ann', '1941-10-15', null, null),
      (25, 'Uncle Jim', '1968-11-18', 8, 7),
      (98, 'Sister Amy', '2001-06-20', 20, 30),
      (7, 'Grandma Sally', '1943-08-23', null, 6),
      (8, 'Grandpa Ben', '1940-10-21', null, null),
      (6, 'Grandgrandma Martha', '1923-05-17', null, null),
      (67, 'Cousin Eddie', '1992-02-28', 25, 27),
      (27, 'Auntie Melinda', '1971-03-29', null, null); 
       
      prepare stmt from"
      with recursive
      ancestor_ids (id)
      as
      (
        select father from folks where name = 'Me'
        union
        select mother from folks where name = 'Me'
        union
        select father from folks, ancestor_ids a  where folks.id = a.id
        union
        select mother from folks, ancestor_ids a  where folks.id = a.id
      )
      select p.* from folks as p, ancestor_ids as a where p.id = a.id;
      ";
      execute stmt;
      

      The server fails to resolve a.id when executing the above prepared statement:

      MariaDB [test]> execute stmt;
      ERROR 1054 (42S22): Unknown column 'a.id' in 'where clause'
      

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

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