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

Non-recursive Common Table Expressions used in view caused an error

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2, 10.2.12
    • Fix Version/s: 10.2.13
    • Component/s: Optimizer - CTE
    • Labels:
      None
    • Environment:
      Centos 7

      Description

      It works as a stand alone query, or in procedures, but failed in views.

      This query worked:

      select * from nation n ,region r where n.n_regionkey = r.r_regionkey and r.r_regionkey in (with t as (select * from region where r_regionkey <=3) select r_regionkey from t where r_name <> "ASIA");
      

      This procedure worked:

      DELIMITER //
      CREATE PROCEDURE shownations ()
      BEGIN
      select * from nation n ,region r where n.n_regionkey = r.r_regionkey and r.r_regionkey in (with t as (select * from region where r_regionkey <=3) select r_regionkey from t where r_name <> "ASIA");
      END //
      DELIMITER ;
       
      call shownations;
      

      This view did not work:

       
      MariaDB [mytest]> create view v as select * from nation n ,region r where n.n_regionkey = r.r_regionkey and r.r_regionkey in (with t as (select * from region where r_regionkey <=3) select r_regionkey from t where r_name <> "ASIA");
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [mytest]> select * from v;
      ERROR 1356 (HY000): View 'mytest.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                igor Igor Babaev
                Reporter:
                David.Hall David Hall
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: