create view bug

Bug 反馈
create view as select …
– 建表1
CREATE TABLE table1 (
id int(0) DEFAULT NULL,
name varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
– 建表2
CREATE TABLE table2 (
id int(0) DEFAULT NULL,
name varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
– 插入数据
insert into table1(id ,name) values(1,‘a’);
insert into table2(id ,name) values(1,‘a’);
– 查询
select t1.id as i1,t2.id as i2,t3.id as i3
from (select tt.id,tt.name from (select id,name from table1) tt) as t1
LEFT JOIN table1 t2 on t1.name = t2.name
LEFT JOIN table2 t3 on t1.name = t3.name;
– 创建view
CREATE view temp_v as
select t1.id as i1,t2.id as i2,t3.id as i3
from (select tt.id,tt.name from (select id,name from temp1) tt) as t1
LEFT JOIN table1 t2 on t1.name = t2.name
LEFT JOIN table2 t3 on t1.name = t3.name;

select * from temp_v;

查询view 报错
通过查看 show create table temp_v;
发现 view 构建的select 语句有问题

现解决办法,将(select tt.id,tt.name from (select id,name from temp1) tt) 子查询单独构建view

CREATE view temp_v1 as (select tt.id,tt.name from (select id,name from table1) tt);

CREATE view temp_v as
select t1.id as i1,t2.id as i2,t3.id as i3
from temp_v1 as t1
LEFT JOIN table1 t2 on t1.name = t2.name
LEFT JOIN table2 t3 on t1.name = t3.name;

【 Bug 的影响】

【可能的问题复现步骤】

【看到的非预期行为】

【期望看到的行为】

【相关组件及具体版本】
tidb v5.0.2
【其他背景信息或者截图】
如集群拓扑,系统和内核版本,应用 app 信息等;如果问题跟 SQL 有关,请提供 SQL 语句和相关表的 Schema 信息;如果节点日志存在关键报错,请提供相关节点的日志内容或文件;如果一些业务敏感信息不便提供,请留下联系方式,我们与您私下沟通。

感谢反馈产品缺陷,可以关注 github issue 了解研发修复进度
https://github.com/pingcap/tidb/issues/25646

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。