[MCOL-782] Non-recursive Common Table Expressions used in view caused an error Created: 2017-06-21  Updated: 2018-02-14  Resolved: 2018-02-14

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.1.0
Fix Version/s: 1.1.3

Type: Bug Priority: Major
Reporter: Daniel Lee (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-14969 Non-recursive Common Table Expression... Closed
Relates
relates to MCOL-598 Add CTE functionality to Columnstore Closed
Epic Link: ColumnStore Compatibility Improvements
Sprint: 2017-25, 2018-01, 2018-03, 2018-04

 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



 Comments   
Comment by David Hall (Inactive) [ 2018-01-16 ]

This appears to be a problem in the server. MDEV-14969 has been opened.

Comment by Andrew Hutchings (Inactive) [ 2018-01-18 ]

Moving out of sprint as any work on this is depends on the MDEV

Comment by Andrew Hutchings (Inactive) [ 2018-02-02 ]

Added to current sprint to be tested once 10.2.13 is merged.

Comment by David Hall (Inactive) [ 2018-02-13 ]

The fix for MDEV-14969 appears to have fixed the problem

Comment by Daniel Lee (Inactive) [ 2018-02-14 ]

Build verified: Github source 1.1.3-1

[root@localhost ~]# cat mariadb-columnstore-1.1.3-1-centos7.x86_64.bin.tar.gz.txt
/root/columnstore/mariadb-columnstore-server
commit 2c627de3cd7426e2a2e62e4d71c611dc22b54ec4
Merge: e5499e5 ea05e61
Author: David.Hall <david.hall@mariadb.com>
Date: Tue Feb 13 15:01:15 2018 -0600

Merge pull request #95 from mariadb-corporation/MCOL-1214

MCOL-1214 Merge MariaDB 10.2.13

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 4220ab0df3f2f2f03615878b713d5e5b2cc7b8a9
Merge: d45dc5f 24f12dc
Author: benthompson15 <ben.thompson@mariadb.com>
Date: Mon Feb 12 17:24:28 2018 -0600

Merge pull request #404 from mariadb-corporation/fix-sig-handler

Fix sig handler for ExeMgr

Generated at Thu Feb 08 02:23:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.