OB update sql依赖自定义会话变量未生效问题

OceanBase 技术问题
#1

版本

准生产环境，5.7.25-OceanBase_CE-v4.3.5.5

问题

在oceanbase update语句中依赖会话变量做排序计数未生效（修改update语句格式后能正常更新），该sql在mysql中行为正常

Your MySQL connection id is 607220
Server version: 5.6.25 OceanBase_CE 4.3.5.5 
SELECT * FROM `test_session_variable`  where xx_id in(3741066) limit 5;
+-----+---------+---------+
| id  | xx_code | xx_id   |
+-----+---------+---------+
| 208 |     208 | 3741066 |
| 209 |     209 | 3741066 |
| 210 |     210 | 3741066 |
| 211 |     211 | 3741066 |
| 212 |     212 | 3741066 |
+-----+---------+---------+
5 rows in set (0.00 sec)

#分别在oceanbase和mysql执行下面update 进行排序计数
update
        test_session_variable it
        set
        it.xx_id = 3741066,

        it.xx_code =
        (
        select @rownum := @rownum + 10 as rownum
        from
        (select @rownum := 66670 ) r, (select t.id from test_session_variable t where xx_id in(3741066)) temp
        where temp.id = it.id
        )
        where it.xx_id in (3741066);
oceanbase xx_code未被更新 mysql xx_code被正确更新
image-20260205112735851 image-20260205133837732

临时修改update语句格式后能正常更新

update
    test_session_variable it
    CROSS JOIN (select @rownum := 66670) r
set
    it.xx_id = 3741066,
    it.xx_code = (@rownum := @rownum + 10)
where it.xx_id in (3741066);


SELECT * FROM `test_session_variable`  where xx_id in(3741066) limit 20;
+-----+---------+---------+
| id  | xx_code | xx_id   |
+-----+---------+---------+
| 208 |   66680 | 3741066 |
| 209 |   66690 | 3741066 |
| 210 |   66700 | 3741066 |
| 211 |   66710 | 3741066 |
| 212 |   66720 | 3741066 |
| 213 |   66730 | 3741066 |
| 214 |   66740 | 3741066 |
| 215 |   66750 | 3741066 |
| 216 |   66760 | 3741066 |
| 217 |   66770 | 3741066 |

分析

第一个update中仅是select子句查排序计数会话变量是正常

mysql> select  @rownum := @rownum + 10 as rownum
    ->         from
    ->         (select @rownum := 66670 ) r, (select t.id from test_session_variable t where xx_id in(3741066)) temp
    -> ;
+--------+
| rownum |
+--------+
|  66680 |
|  66690 |
|  66700 |
|  66710 |
|  66720 |
|  66730 |
|  66740 |
|  66750 |
|  66760 |

当加上了update set语句，应该出现了行为变化，这子句只执行了一次 select @rownum := @rownum + 10 as rownum

 it.xx_code =
    (
    select @rownum := @rownum + 10 as rownum
    from
    (select @rownum := 66670 ) r, (select t.id from test_session_variable t where xx_id in(3741066)) temp
    where temp.id = it.id
    )

临时修改后的update语句，应该先笛卡尔积关联生成了“临时表”，再set逐行更新

    test_session_variable it
    CROSS JOIN (select @rownum := 66670) r
    
    set
    it.xx_code = (@rownum := @rownum + 10)

复现

CREATE TABLE `test_session_variable` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `xx_code` int(11) DEFAULT NULL,
  `xx_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (208,208,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (209,209,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (210,210,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (211,211,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (212,212,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (213,213,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (214,214,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (215,215,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (216,216,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (217,217,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (218,218,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (219,219,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (220,220,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (221,221,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (222,222,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (223,223,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (224,224,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (225,225,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (226,226,3741066);
INSERT INTO `test_session_variable` (`id`,`xx_code`,`xx_id`) VALUES (227,227,3741066);
...

update
        test_session_variable it
        set
        it.xx_id = 3741066,

        it.xx_code =
        (
        select @rownum := @rownum + 10 as rownum
        from
        (select @rownum := 66670 ) r, (select t.id from test_session_variable t where xx_id in(3741066)) temp
        where temp.id = it.id
        )
        where it.xx_id in (3741066);

用户自定义变量-V4.5.0-OceanBase 数据库文档-分布式数据库使用文档

2 个赞
#3

@论坛小助手 分析上面问题的根本原因

#4

根据给定的信息，OceanBase 与 MySQL 在处理用户自定义变量时存在一定的行为差异，特别是涉及到复杂 SQL 语句和并行执行时。在您的问题中，update 语句使用了用户自定义变量 @rownum 进行排序计数，但在 OceanBase 中未能生效，而在 MySQL 中能正常工作。

根本原因分析：

  1. 并行执行的限制：

    • OceanBase 在并行执行时无法保证用户自定义变量的计算顺序和一致性。这意味着在并行执行的环境中，@rownum 的值可能会因为并行任务的不确定顺序而出现不一致的情况。
    • 在您最初的 update 语句中，@rownum 的赋值操作是在子查询中进行的，而子查询的结果会被物化到临时表中，然后与主表进行连接。这种情况下，@rownum 的值可能在并行执行中无法正确传递。

  2. 初始化和计算顺序：

    • 在 MySQL 中，@rownum 的初始化和计算顺序是确定的，即使在子查询中也能保证其按预期顺序执行。
    • 而在 OceanBase 中，@rownum 的初始化和计算顺序可能因优化器的选择而有所不同，特别是在并行执行和子查询物化的情况下。

  3. 子查询物化：

    • 当用户变量赋值操作出现在 FROM 子查询中时，MySQL 会禁止子查询之间的合并，并将包含用户变量的子查询结果物化到临时表中。这种行为在 OceanBase 中也有类似的实现，但结果可能不完全一致。

解决方法：

  • 修改 update 语句格式：
    • 如您所述，通过将 @rownum 的初始化和赋值操作放在 CROSS JOIN 中，可以确保每次更新时 @rownum 的值按预期顺序递增。
    • 示例：
      update
    test_session_variable it
    CROSS JOIN (select @rownum := 66670) r
set
    it.xx_id = 3741066,
    it.xx_code = (@rownum := @rownum + 10)
where it.xx_id in (3741066);

总结：

  • 在处理复杂 SQL 语句和用户自定义变量时，OceanBase 和 MySQL 之间存在行为差异，特别是在并行执行和子查询物化的情况下。
  • 通过调整 SQL 语句的结构，可以确保在 OceanBase 中也能实现预期的更新效果。

更多信息请查看:

  1. 常见的 SQL 调优方式
#5

应该是在处理关联子查询（correlated subquery）内部派生表（derived table）的 rescan 行为上存在本质差异，导致用户变量 @rownum 在每次子查询重新执行时被重置。
这边反馈给内核同学看看具体原因

1 个赞