[MDEV-34506] 2nd execution name resolution problem with pushdown into unions - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.5.25, 10.11.8, 10.6.18, 11.1.5, 11.2.4, 11.4.2, 11.5.1, 11.6.0
Fix Version/s: 10.5.26, 10.6.19, 10.11.9, 11.1.6, 11.2.5, 11.4.3, 11.5.2, 11.6.1
Component/s: Optimizer, Prepared Statements
Labels:
None

Description

create table t1 (c1 int, c2 int, c3 int);

insert into t1 values (1,2,3),(4,5,6);

create table t2 like t1;

insert into t2 values (7,8,9),(10,11,12);

create view v1 (f1, f2, f3) as select * from (

  select c1, c2, sum(c3) as s from t1 group by c1, c2 having s > 2

  union

  select c1, c2, sum(c3) as u from t2 group by c1, c2 having u > 3 ) d2;

prepare stmt from 'select * from v1 where f2 > 0';

execute stmt;

execute stmt;

first execution gives the correct answer

+------+------+------+

| f1   | f2   | f3   |

+------+------+------+

|    1 |    2 |    3 |

|    4 |    5 |    6 |

|    7 |    8 |    9 |

|   10 |   11 |   12 |

+------+------+------+

2nd execution does not

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

I have solved this problem in the linked MDEV, but it will be worth backporting the fix to earlier versions.

Attachments

Issue Links

relates to

MDEV-31466 Add optional correlation column list for derived tables

Closed

Activity

Ascending order - Click to sort in descending order

Rex Johnston created issue - 2024-07-01 23:29

Rex Johnston made changes - 2024-07-01 23:29

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-31466~~ [ ~~MDEV-31466~~ ]

Rex Johnston made changes - 2024-07-01 23:30

Assignee

Rex Johnston [ JIRAUSER52533 ]

Rex Johnston made changes - 2024-07-02 00:33

Description

{code:sql}
create table t1 (c1 int, c2 int, c3 int);
insert into t1 values (1,2,3),(4,5,6);
create table t2 like t1;
insert into t2 values (7,8,9),(10,11,12);
create view v1 (f1, f2, f3) as select * from ( select c1, c2, sum(c3) as s from t1 group by c1, c2 having s > 2 union select c1, c2, sum(c3) as u from t2 group by c1, c2 having u > 3 ) d2;
prepare stmt from 'select * from v1 where f2 > 0';
execute stmt;
execute stmt;
{code}

first execution give the correct answer

{noformat}
+------+------+------+
| f1 | f2 | f3 |
+------+------+------+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
| 10 | 11 | 12 |
+------+------+------+
{noformat}

2nd execution does not

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

I have solved this problem in the linked MDEV, but it will be worth backporting the fix to earlier versions.

{code:sql}
create table t1 (c1 int, c2 int, c3 int);
insert into t1 values (1,2,3),(4,5,6);
create table t2 like t1;
insert into t2 values (7,8,9),(10,11,12);
create view v1 (f1, f2, f3) as select * from ( select c1, c2, sum(c3) as s from t1 group by c1, c2 having s > 2 union select c1, c2, sum(c3) as u from t2 group by c1, c2 having u > 3 ) d2;
prepare stmt from 'select * from v1 where f2 > 0';
execute stmt;
execute stmt;
{code}

first execution gives the correct answer

{noformat}
+------+------+------+
| f1 | f2 | f3 |
+------+------+------+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
| 10 | 11 | 12 |
+------+------+------+
{noformat}

2nd execution does not

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

I have solved this problem in the linked MDEV, but it will be worth backporting the fix to earlier versions.

Rex Johnston made changes - 2024-07-02 00:37

Affects Version/s		11.5.1 [ 29634 ]
Affects Version/s		11.4.2 [ 29633 ]
Affects Version/s		11.2.4 [ 29631 ]
Affects Version/s		11.1.5 [ 29629 ]
Affects Version/s		10.11.8 [ 29630 ]
Affects Version/s		10.6.18 [ 29627 ]
Affects Version/s		11.6.0 [ 29839 ]

Rex Johnston made changes - 2024-07-02 00:38

Fix Version/s		10.11.9 [ 29834 ]
Fix Version/s		11.1.6 [ 29835 ]
Fix Version/s		11.2.5 [ 29836 ]
Fix Version/s		11.4.3 [ 29837 ]
Fix Version/s		11.5.2 [ 29838 ]
Fix Version/s	10.7 [ 24805 ]

Rex Johnston made changes - 2024-07-02 01:36

Description

{code:sql}
create table t1 (c1 int, c2 int, c3 int);
insert into t1 values (1,2,3),(4,5,6);
create table t2 like t1;
insert into t2 values (7,8,9),(10,11,12);
create view v1 (f1, f2, f3) as select * from ( select c1, c2, sum(c3) as s from t1 group by c1, c2 having s > 2 union select c1, c2, sum(c3) as u from t2 group by c1, c2 having u > 3 ) d2;
prepare stmt from 'select * from v1 where f2 > 0';
execute stmt;
execute stmt;
{code}

first execution gives the correct answer

{noformat}
+------+------+------+
| f1 | f2 | f3 |
+------+------+------+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
| 10 | 11 | 12 |
+------+------+------+
{noformat}

2nd execution does not

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

I have solved this problem in the linked MDEV, but it will be worth backporting the fix to earlier versions.

{code:sql}
create table t1 (c1 int, c2 int, c3 int);
insert into t1 values (1,2,3),(4,5,6);
create table t2 like t1;
insert into t2 values (7,8,9),(10,11,12);
create view v1 (f1, f2, f3) as select * from (
  select c1, c2, sum(c3) as s from t1 group by c1, c2 having s > 2
  union
  select c1, c2, sum(c3) as u from t2 group by c1, c2 having u > 3 ) d2;
prepare stmt from 'select * from v1 where f2 > 0';
execute stmt;
execute stmt;
{code}

first execution gives the correct answer

{noformat}
+------+------+------+
| f1 | f2 | f3 |
+------+------+------+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
| 10 | 11 | 12 |
+------+------+------+
{noformat}

2nd execution does not

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

I have solved this problem in the linked MDEV, but it will be worth backporting the fix to earlier versions.

Rex Johnston made changes - 2024-07-02 01:38

Status

Open [ 1 ]

In Progress [ 3 ]

Rex Johnston made changes - 2024-07-02 18:47

Assignee	Rex Johnston [ JIRAUSER52533 ]	Igor Babaev [ igor ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2024-07-15 08:34

Fix Version/s		10.11 [ 27614 ]
Fix Version/s		11.2 [ 28603 ]
Fix Version/s		11.4 [ 29301 ]
Fix Version/s		11.5 [ 29506 ]
Fix Version/s	10.11.9 [ 29834 ]
Fix Version/s	11.1.6 [ 29835 ]
Fix Version/s	11.2.5 [ 29836 ]
Fix Version/s	11.4.3 [ 29837 ]
Fix Version/s	11.5.2 [ 29838 ]

Rex Johnston made changes - 2024-07-25 01:28

Assignee

Igor Babaev [ igor ]

Rex Johnston [ JIRAUSER52533 ]

Rex Johnston made changes - 2024-07-25 01:28

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Rex Johnston made changes - 2024-07-25 01:28

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Igor Babaev (Inactive) made changes - 2024-07-29 21:13

Status

In Progress [ 3 ]

Needs Feedback [ 10501 ]

Rex Johnston made changes - 2024-07-29 21:15

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.11 [ 27614 ]
Fix Version/s	11.2 [ 28603 ]
Fix Version/s	11.4 [ 29301 ]
Fix Version/s	11.5 [ 29506 ]
Resolution		Incomplete [ 4 ]
Status	Needs Feedback [ 10501 ]	Closed [ 6 ]

Rex Johnston made changes - 2024-07-29 21:16

Resolution	Incomplete [ 4 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Rex Johnston made changes - 2024-07-29 21:16

Assignee	Rex Johnston [ JIRAUSER52533 ]	Igor Babaev [ igor ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Igor Babaev (Inactive) made changes - 2024-07-29 21:19

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Igor Babaev (Inactive) made changes - 2024-07-29 21:19

Assignee

Igor Babaev [ igor ]

Rex Johnston [ JIRAUSER52533 ]

Rex Johnston made changes - 2024-07-29 21:20

Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.11 [ 27614 ]
Fix Version/s		11.2 [ 28603 ]
Fix Version/s		11.4 [ 29301 ]
Fix Version/s		11.5 [ 29506 ]
Fix Version/s		11.6 [ 29515 ]
Fix Version/s	N/A [ 14700 ]

Rex Johnston made changes - 2024-07-29 22:08

Fix Version/s		10.5.26 [ 29832 ]
Fix Version/s		10.6.19 [ 29833 ]
Fix Version/s		10.11.9 [ 29834 ]
Fix Version/s		11.1.6 [ 29835 ]
Fix Version/s		11.2.5 [ 29836 ]
Fix Version/s		11.4.3 [ 29837 ]
Fix Version/s		11.5.2 [ 29838 ]
Fix Version/s		11.6.1 [ 29847 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.11 [ 27614 ]
Fix Version/s	11.2 [ 28603 ]
Fix Version/s	11.4 [ 29301 ]
Fix Version/s	11.5 [ 29506 ]
Fix Version/s	11.6 [ 29515 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

People

Assignee:: Rex Johnston

Reporter:: Rex Johnston

Votes:: 1 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2024-07-01 23:29

Updated:: 2024-07-30 04:01

Resolved:: 2024-07-29 22:08

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