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

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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.12, 10.2(EOL)
    • 10.2.13
    • Optimizer - CTE
    • None
    • 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

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MCOL-782 Non-recursive Common Table Expressions used in view caused an error

          • Major - Major loss of function.
          • Closed

          Activity

            Descending order - Click to sort in ascending order
            igor Igor Babaev (Inactive) added a comment -

            A fix for this bug was pushed into the 10.2 tree.

            igor Igor Babaev (Inactive) added a comment - A fix for this bug was pushed into the 10.2 tree.
            alice Alice Sherepa added a comment - 

            CREATE TABLE t1(i1 int);
            		 
            SELECT * FROM t1 WHERE t1.i1 IN (WITH tt AS (SELECT 1) SELECT * FROM tt);
            		 
            CREATE VIEW v1 AS SELECT * FROM t1 WHERE t1.i1 IN (WITH tt AS (SELECT 1) SELECT * FROM tt);
            		 
            SELECT * FROM v1;


            MariaDB [test]> SELECT * FROM t1 WHERE t1.i1 IN (WITH tt AS (SELECT 1) SELECT * FROM tt);
            		 
            Empty set (0.00 sec)
            		 
             
            		 
            MariaDB [test]> CREATE VIEW v1 AS SELECT * FROM t1 WHERE t1.i1 IN (WITH tt AS (SELECT 1) SELECT * FROM tt);
            		 
            Query OK, 0 rows affected (0.05 sec)
            		 
             
            		 
            MariaDB [test]> SELECT * FROM v1;
            		 
            ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
            		 
            Error (Code 1146): Table 'test.tt' doesn't exist

            alice Alice Sherepa added a comment - CREATE TABLE t1(i1 int ); SELECT * FROM t1 WHERE t1.i1 IN ( WITH tt AS ( SELECT 1) SELECT * FROM tt); CREATE VIEW v1 AS SELECT * FROM t1 WHERE t1.i1 IN ( WITH tt AS ( SELECT 1) SELECT * FROM tt); SELECT * FROM v1; MariaDB [test]> SELECT * FROM t1 WHERE t1.i1 IN (WITH tt AS (SELECT 1) SELECT * FROM tt); Empty set (0.00 sec)   MariaDB [test]> CREATE VIEW v1 AS SELECT * FROM t1 WHERE t1.i1 IN (WITH tt AS (SELECT 1) SELECT * FROM tt); Query OK, 0 rows affected (0.05 sec)   MariaDB [test]> SELECT * FROM v1; ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Error (Code 1146): Table 'test.tt' doesn't exist

            People

              igor Igor Babaev (Inactive)
              David.Hall David Hall (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.