Embedded CTE in view will be prefixed with DB name

[MimeLyc]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Create users and grant some permissions.(root user)

CREATE USER 'db_a'@'%';
CREATE USER 'db_b'@'%';

GRANT ALL PRIVILEGES ON `db_a`.* TO 'db_a'@'%'
GRANT ALL PRIVILEGES ON `db_b`.* TO 'db_a'@'%'
GRANT ALL PRIVILEGES ON `db_b`.* TO 'db_b'@'%'
GRANT ALL PRIVILEGES ON `db_b`.* TO 'db_b'@'%'

 SHOW GRANTS FOR 'db_a'@'%';
+------------------------------------------------+
| Grants for db_a@%                              |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'db_a'@'%'               |
| GRANT ALL PRIVILEGES ON `db_a`.* TO 'db_a'@'%' |
| GRANT ALL PRIVILEGES ON `db_b`.* TO 'db_a'@'%' |
+------------------------------------------------+

 SHOW GRANTS FOR 'db_b'@'%';
+------------------------------------------------+
| Grants for db_b@%                              |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'db_b'@'%'               |
| GRANT ALL PRIVILEGES ON `db_a`.* TO 'db_b'@'%' |
| GRANT ALL PRIVILEGES ON `db_b`.* TO 'db_b'@'%' |
+------------------------------------------------+

Create views.(root user)

create database db_a;
create database db_b;

use db_a;
 CREATE TABLE `tmp_table1` (                                 
   `id` decimal(18,0) NOT NULL,                              
   `row_1` varchar(255) DEFAULT NULL,                        
   PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */     
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

 create  ALGORITHM=UNDEFINED DEFINER=`db_a`@`%` SQL SECURITY DEFINER VIEW view_test_v1 as (
                         with rs1 as(
                            select otn.*
                             from tmp_table1 otn
                          )
                        select ojt.* from rs1 ojt
                        )

2. What did you expect to see? (Required)

use db_b;
select * from db_a.view_test_v1;

-- Get result

3. What did you see instead (Required)

show create table db_a.view_test_v1
-- CREATE ALGORITHM=UNDEFINED DEFINER=`db_a`@`%` SQL SECURITY DEFINER VIEW `view_test_v1` (`row_1`) AS (WITH `rs1` AS (SELECT `db_a`.`otn`.`id` AS `id`,`db_a`.`otn`.`row_1` AS `row_1` FROM `db_a`.`tmp_table1` AS `otn`), `rs2` AS (SELECT `oon`.`row_1` AS `row_1` FROM `db_a`.`tmp_table1` AS `oon` LEFT JOIN `rs1` AS `tfn` ON `oon`.`row_1`=`tfn`.`row_1`) SELECT `db_a`.`ojt`.`row_1` AS `row_1` FROM `rs2` AS `ojt`) 

...And if I query this view from db_a, the following error occurs:

use db_b;
select * from db_a.view_test_v1;

(1356, "View 'db_a.view_test_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them")

4. What is your TiDB version? (Required)

+-----------------------------------------------------------+
| TIDB_VERSION() |
+-----------------------------------------------------------+
| Release Version: v8.1.0 |
| Edition: Community |
| Git Commit Hash: 945d07c |
| Git Branch: HEAD |
| UTC Build Time: 2024-05-21 03:51:57 |
| GoVersion: go1.21.10 |
| Race Enabled: false |
| Check Table Before Drop: false |
| Store: tikv |
+-----------------------------------------------------------+

[mayjiang0203]

/severity major

[MimeLyc]

Seems like this bug will only happen when there is any table aliase.

[harry1129]

This can be reproduced in TiDB v6.5.11.

[harry1129]

The problem will cause the query to information_schema.columns to return no results and produce a warning message.

mysql> use test;
Database changed
mysql> select * from test.view_test_v1;
Empty set (0.00 sec)

mysql> use test1;
Database changed
mysql> select * from test.view_test_v1;
mysql> select * from information_schema.columns where table_name='view_test_v1';
Empty set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                             |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1356 | View 'test.view_test_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.5.11
Edition: Community
Git Commit Hash: 3f2073261bf9f454c3c677d76d120c6ab40ca05c
Git Branch: HEAD
UTC Build Time: 2024-09-18 04:07:03
GoVersion: go1.19.13
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)