help wantedseverity/minorsig/sql-infratype/bug
仓库指标
- Star
- (40,090 star)
- PR 合并指标
- (平均合并 14天 4小时) (30 天内合并 346 个 PR)
描述
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
create table tn (id int primary key);
create view as select * from (select * from tn for update) as tn;
create view tv1 as select * from (select * from tn for update) as tn;
show create view tv1;
2. What did you expect to see? (Required)
No FOR UPDATE in show create view
mysql> show create view tv1;
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| tv1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`127.0.0.1` SQL SECURITY DEFINER VIEW `tv1` (`id`) AS SELECT `test`.`tn`.`id` AS `id` FROM (SELECT `test`.`tn`.`id` AS `id` FROM `test`.`tn`) AS `tn` | utf8mb4 | utf8mb4_0900_ai_ci |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
3. What did you see instead (Required)
FOR UPDATE still exists. Scalar subquery and correlated subquery also have the some problem
mysql> show create view tv1;
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| tv1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`127.0.0.1` SQL SECURITY DEFINER VIEW `tv1` (`id`) AS SELECT `test`.`tn`.`id` AS `id` FROM (SELECT `test`.`tn`.`id` AS `id` FROM `test`.`tn` FOR UPDATE) AS `tn` | utf8mb4 | utf8mb4_0900_ai_ci |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
4. What is your TiDB version? (Required)
master