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 信息；如果节点日志存在关键报错，请提供相关节点的日志内容或文件；如果一些业务敏感信息不便提供，请留下联系方式，我们与您私下沟通。