【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】3.1.4-OceanBase CE
【问题描述】
上图是官方文档，红色标注的地方，其中“直接对子查询进行操作”这句描述的不太清楚。按自我理解，对比mysql 8.0 和 ob mysql租户执行都是成功的。
测试的用例有：
- 子查询作为column的value：
CREATE TABLE T1 (ID int, NAME VARCHAR(30), DOC VARCHAR(30), PRIMARY KEY(ID));
CREATE TABLE T2 (ID int, FID int, NAME VARCHAR(30), DOC VARCHAR(30), PRIMARY KEY(ID));
INSERT INTO T1 VALUES (1, ‘A’, ‘TEST1’);
INSERT INTO T1 VALUES (2, ‘B’, ‘TEST2’);
INSERT INTO T1 VALUES (3, ‘C’, ‘TEST3’);
INSERT INTO T2 VALUES (1, 1, ‘A’, ‘TESTA’);
INSERT INTO T2 VALUES (2, 2, ‘B’, ‘TESTB’);
INSERT INTO T2 VALUES (3, 3, ‘C’, ‘TESTC’);
INSERT INTO T2 VALUES (4, NULL, ‘A’, ‘TESTD’);
update t1 a set a.name=(select b.name from t2 b where b.id=a.id) where name is not null;
-
子查询作为参与join的表：
CREATE TABLE PicksPoints (UserCompetitionIDint,CompetitionIDint);
INSERT INTO PicksPoints (UserCompetitionID,CompetitionID) VALUES (1, 1), (1, 2), (1, 3), (1, 2), (1, 3), (1, 3), (1, 2);
CREATE TABLE Competition (CompetitionIDint,NumberOfTeamsint);
INSERT INTO Competition (CompetitionID,NumberOfTeams) VALUES (1, 0), (2, 0), (3, 0);
Update Competition as C
inner join (
select CompetitionId, count(*) as NumberOfTeams
from PicksPoints as p
where UserCompetitionID is not NULL
group by CompetitionID
) as A on C.CompetitionID = A.CompetitionID
set C.NumberOfTeams = A.NumberOfTeams
; -
子查询同样作为参与join的表，换一种写法：
CREATE TABLE items ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, wholesale DECIMAL(6,2) NOT NULL DEFAULT 0.00, retail DECIMAL(6,2) NOT NULL DEFAULT 0.00, quantity BIGINT NOT NULL DEFAULT 0 );
insert into items values (1, 1200, 5000, 90);
UPDATE items,
(SELECT id, retail / wholesale AS markup, quantity FROM items)
AS discounted
SET items.retail = items.retail * 0.9
WHERE discounted.markup >= 1.3
AND discounted.quantity < 100
AND items.id = discounted.id;
能给个不支持的用例吗？如果说““直接对子查询进行操作”是类似以下这样的写法：
UPDATE (SELECT T1.DOC DOC_1, T2.DOC DOC_2 FROM T1, T2 WHERE T1.ID = T2.FID)
SET DOC_2 = DOC_1;
这是oracle的语法，mysql和ob mysql租户是都不支持的。
【复现路径】
【问题现象及影响】
【附件】