在TiDB 5.0.1版本,能够建立视图,但是访问视图内容时出错

【TiDB 版本】
TiDB 5.0.1

【问题描述】
在TiDB 5.0.1版本,能够建立视图,但是访问视图内容时出错。

(1)建立3张表:专业表(zy_tab)、班级表(bj_tab)、学生表(st_tab)
drop table if exists zy_tab; – 专业表
create table if not exists zy_tab (
zy_code int, – 专业代码
zy_name varchar(100) – 专业名称
);
insert into zy_tab values (1,‘网络工程’);
insert into zy_tab values (2,‘机电工程’);
commit;

drop table if exists bj_tab; – 班级表
create table if not exists bj_tab (
bj_code int, – 班级代码
bj_name varchar(100), – 班级名称
bj_addr varchar(100), – 班级地址
bj_person_count int, – 班级人数
zy_code int – 专业代码
);
insert into bj_tab values (1,‘一班’,‘深圳’,2,1);
insert into bj_tab values (2,‘二班’,‘大连’,1,2);
commit;

drop table if exists st_tab; – 学生表
create table if not exists st_tab (
st_code int, – 学生代码
st_name varchar(100), – 学生姓名
bj_code int – 班级代码
);

insert into st_tab values (101,‘王小明’,1);
insert into st_tab values (102,‘李小红’,1);
insert into st_tab values (201,‘赵刚’,2);
commit;

(2)建立视图:
drop view if exists v_st_2;
create or replace view v_st_2
as
select st.st_name,bj.bj_name,zy.zy_name
from (
select bj_code, – 班级代码
bj_name, – 班级名称
zy_code – 专业代码
from bj_tab as b
where b.bj_code = 1
) as bj
left join zy_tab as zy on zy.zy_code = bj.zy_code
left join st_tab as st on bj.bj_code = st.bj_code;

(3)访问视图:
select * from v_st_2;

结果系统报告错误,不能正常访问视图内容:

1105 - line 1 column 150 near “AS bj LEFT JOIN scott.zy_tab AS zy ON zy.zy_code=bj.zy_code) LEFT JOIN scott.st_tab AS st ON bj.bj_code=st.bj_code
时间: 0.003s

(4)补充说明:
在TiDB未升级,在TiDB 4.0.12版本时,可以正常访问视图内容,系统没有报告这个错误。

嗯嗯,我这里也复现这个问题了,这边先看下,后面有进展会跟帖回复 ~~

您好 ,这里看到这个报错可能是在 v5.0.1 版本,带有子查询的 create view 在创建后,DDL 语句中多了一对括号导致,如下:

请评估将 view 的 DDL 的语句改为下述 SQL ,是否满足业务需求,暂时跳过这个报错:

drop view if exists v_st_4;
create or replace view v_st_4
as
select st.st_name,bj.bj_name,zy.zy_name
from  bj_tab bj
left join zy_tab as zy on zy.zy_code = bj.zy_code 
left join st_tab as st on bj.bj_code = st.bj_code
where bj.bj_code = 1;

针对这个问题,这边定位具体的原因后,会跟帖回复,感谢 ~

是的,我们也确认过了,用v_st_4这样方式改的话,可以跳过这个报错。
只是我们有大量的视图都采用了v_st_2的写法,如果实施改动的话,投入会很大。
我们正在考虑,暂时退回到v4.0.12版本。
感谢您的帮助。。。

嗯嗯,明白,这个问题,如果有了相关的 issue ,这边会及时跟帖,感谢反馈 ~

以下测试,在v5.0.1版本和v4.0.12版本完成:
(1)在v5.0.1版本,使用show create table,获取v_st_2的信息:
show create table v_st_2;

得到下面的信息:
CREATE ALGORITHM = UNDEFINED DEFINER = scotter @10.162.10.170 SQL SECURITY DEFINER VIEW v_st_2 ( st_name, bj_name, zy_name ) AS
SELECT
st.st_name,
bj.bj_name,
zy.zy_name
FROM
((
SELECT
bj_code,
bj_name,
zy_code
FROM
scott.bj_tab AS b
WHERE
b.bj_code = 1
) AS bj
LEFT JOIN scott.zy_tab AS zy ON zy.zy_code = bj.zy_code
)
LEFT JOIN scott.st_tab AS st ON bj.bj_code = st.bj_code

(2)在v5.0.1版本,执行(1)获取的SQL:
SELECT
st.st_name,
bj.bj_name,
zy.zy_name
FROM
((
SELECT
bj_code,
bj_name,
zy_code
FROM
scott.bj_tab AS b
WHERE
b.bj_code = 1
) AS bj
LEFT JOIN scott.zy_tab AS zy ON zy.zy_code = bj.zy_code
)
LEFT JOIN scott.st_tab AS st ON bj.bj_code = st.bj_code;

会报告下面的错误:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 15 column 8 near “AS bj
LEFT JOIN scott.zy_tab AS zy ON zy.zy_code = bj.zy_code
)
LEFT JOIN scott.st_tab AS st ON bj.bj_code = st.bj_code
时间: 0.002s

(3)在v4.0.12版本,执行(1)获取的SQL:
SELECT
st.st_name,
bj.bj_name,
zy.zy_name
FROM
((
SELECT
bj_code,
bj_name,
zy_code
FROM
scott.bj_tab AS b
WHERE
b.bj_code = 1
) AS bj
LEFT JOIN scott.zy_tab AS zy ON zy.zy_code = bj.zy_code
)
LEFT JOIN scott.st_tab AS st ON bj.bj_code = st.bj_code;

能够返回正确的结果:
王小明 一班 网络工程
李小红 一班 网络工程

(4)在v5.0.1版本,把(1)获取的SQL,去掉一层括号,再来执行:
SELECT
st.st_name,
bj.bj_name,
zy.zy_name
FROM
( – 此处去掉了左括号
SELECT
bj_code,
bj_name,
zy_code
FROM
scott.bj_tab AS b
WHERE
b.bj_code = 1
) AS bj
LEFT JOIN scott.zy_tab AS zy ON zy.zy_code = bj.zy_code
– 此处去掉了右括号
LEFT JOIN scott.st_tab AS st ON bj.bj_code = st.bj_code;

能够返回正确的结果:
王小明 一班 网络工程
李小红 一班 网络工程

(5)在v4.0.12版本,使用show create table,获取v_st_2的信息:
CREATE ALGORITHM = UNDEFINED DEFINER = scott @10.162.10.170 SQL SECURITY DEFINER VIEW v_st_2 ( st_name, bj_name, zy_name ) AS SELECT
st.st_name,
bj.bj_name,
zy.zy_name
FROM
((
SELECT
bj_code,
bj_name,
zy_code
FROM
( scott.bj_tab AS b )
WHERE
b.bj_code = 1
) AS bj
LEFT JOIN scott.zy_tab AS zy ON zy.zy_code = bj.zy_code
)
LEFT JOIN scott.st_tab AS st ON bj.bj_code = st.bj_code

这里获取的v_st_2信息 与 在v5.0.1版本时获取的信息是一样的。

(6)综上:
a.使用show create table,在v5.0.1版本和v4.0.12版本,得到的信息是一样的。FROM 后面都是连续的两层括号;
b.在v4.0.12版本,支持执行FROM后面有两个括号的SQL;
c.在v5.0.1版本,不支持执行FROM后面有两个括号的SQL,仅支持FROM后面有1个括号的SQL。

以上,请参考,感谢您的帮助!

:handshake:

上面的问题确认是一个带修复的问题,后续可关注下面的 issue ,了解这个问题的处理进展:

https://github.com/pingcap/tidb/issues/24771