[MDEV-14969] Non-recursive Common Table Expressions used in view caused an error - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.12, 10.2(EOL)
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

blocks

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

Closed

Activity

Ascending order - Click to sort in descending order

David Hall (Inactive) created issue - 2018-01-16 20:20

David Hall (Inactive) made changes - 2018-01-16 20:20

Field	Original Value	New Value
Rank		Ranked higher

David Hall (Inactive) made changes - 2018-01-16 20:23

Link

This issue blocks ~~MCOL-782~~ [ ~~MCOL-782~~ ]

Elena Stepanova made changes - 2018-01-16 20:27

Assignee

Alice Sherepa [ alice ]

Alice Sherepa made changes - 2018-01-17 11:02

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

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

This query worked:
{code:sql}
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");
{code}

This procedure worked:
{code:sql}
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;
{code}

This view did not work:

{noformat}

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
{noformat}

Alice Sherepa added a comment - 2018-01-17 12:51

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 Sherepa added a comment - 2018-01-17 12:51 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 Sherepa made changes - 2018-01-17 12:52

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2018-01-17 12:52

Fix Version/s

10.2 [ 14601 ]

Alice Sherepa made changes - 2018-01-17 12:52

Affects Version/s

10.2 [ 14601 ]

Alice Sherepa made changes - 2018-01-17 12:52

Assignee

Alice Sherepa [ alice ]

Galina Shalygina [ shagalla ]

Igor Babaev (Inactive) made changes - 2018-01-17 17:42

Assignee

Galina Shalygina [ shagalla ]

Igor Babaev [ igor ]

Igor Babaev (Inactive) made changes - 2018-01-18 18:32

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Igor Babaev (Inactive) added a comment - 2018-01-19 02:57

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

Igor Babaev (Inactive) added a comment - 2018-01-19 02:57 A fix for this bug was pushed into the 10.2 tree.

Igor Babaev (Inactive) made changes - 2018-01-19 02:57

issue.field.resolutiondate

2018-01-19 02:57:43.0

2018-01-19 02:57:43.623

Igor Babaev (Inactive) made changes - 2018-01-19 02:57

Fix Version/s		10.2.13 [ 22910 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:46

Workflow

MariaDB v3 [ 84912 ]

MariaDB v4 [ 153580 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: David Hall (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2018-01-16 20:20

Updated:: 2018-01-19 02:57

Resolved:: 2018-01-19 02:57

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration