Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-782

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: 1.1.0
    • Fix Version/s: 1.1.3
    • Component/s: MariaDB Server
    • Labels:
      None

      Description

      Build tested: Github source 1.1.0-1
      [root@localhost mariadb-columnstore-server]# git show
      commit 594ef1807a5d6cba45cf7c2bed03cccdc32f177a
      Merge: a5f191d ce815f9
      Author: David.Hall <david.hall@mariadb.com>
      Date: Thu Jun 8 10:12:50 2017 -0500
      [root@localhost mariadb-columnstore-engine]# git show
      commit ebaf24473c0838989bf504a7c104c511b876fcb8
      Author: david hill <david.hill@mariadb.com>
      Date: Fri Jun 16 16:53:48 2017 -0500

      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:
                dleeyh Daniel Lee
                Reporter:
                dleeyh Daniel Lee
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: