ob 4.0文档中有关mysql兼容性问题

【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】3.1.4-OceanBase CE
【问题描述】


上图是官方文档,红色标注的地方,其中“直接对子查询进行操作”这句描述的不太清楚。按自我理解,对比mysql 8.0 和 ob mysql租户执行都是成功的。

测试的用例有:

  1. 子查询作为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;

  1. 子查询作为参与join的表:
    CREATE TABLE PicksPoints (UserCompetitionID int, CompetitionID int);
    INSERT INTO PicksPoints (UserCompetitionID, CompetitionID) VALUES (1, 1), (1, 2), (1, 3), (1, 2), (1, 3), (1, 3), (1, 2);
    CREATE TABLE Competition (CompetitionID int, NumberOfTeams int);
    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
    ;

  2. 子查询同样作为参与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租户是都不支持的。

【复现路径】
【问题现象及影响】

【附件】

1 个赞

能描述下您的问题吗

1 个赞

刚刚写了一半页面退出了,已更新。

1 个赞

能回复下吗?

顶一下~~~

非常感谢您的建议,我们内部评估一下。