OceanBase V4.2.1 告警 “Variable assignment in order by items will cause uncertain behavior”

近日一客户使用Oceanbase V4.2的环境中，OCP监控平台提示一个严重级别的信息”Variable assignment in order by items will cause uncertain behavior“ 带变量的order by 导致不确定性，文件源码src\sql\resolver\dml\ob_select_resolver.cpp，下面记录一下该错误的原因。

OB社区版找到的源码报错信息

src\sql\resolver\dml\ob_select_resolver.cpp 文件中并没有找到该代码，有一段描述函数但提示信息并不一致，OB的代码更新较快，可能是我的版本不匹配。

int ObSelectResolver::resolve_order_item(const ParseNode &sort_node, OrderItem &order_item)

src\sql\resolver\dml\ob_dml_resolver.cpp文件中有匹配的错误信息

int ObDMLResolver::resolve_order_item(const ParseNode &sort_node, OrderItem &order_item)
{
  int ret = OB_SUCCESS;
  ObRawExpr *expr;
  if (OB_FAIL(ObResolverUtils::set_direction_by_mode(sort_node, order_item))) {
    LOG_WARN("failed to set direction by mode", K(ret));
  }
  if (OB_FAIL(ret)) {
    // do nothing
  } else if (OB_UNLIKELY(sort_node.children_[0]->type_ == T_INT)) {
    ret = OB_ERR_PARSER_SYNTAX;
    SQL_RESV_LOG(WARN, "index order item not support in update");
  } else if (OB_FAIL(resolve_sql_expr(*(sort_node.children_[0]), expr))) {
    SQL_RESV_LOG(WARN, "resolve sql expression failed", K(ret));
  } else {
    // check if order by item has var assign expr, which will cause uncertain behavior
    if (OB_NOT_NULL(expr) && expr->has_flag(CNT_ASSIGN_EXPR)) {
      LOG_USER_WARN(OB_ERR_DEPRECATED_SYNTAX, "Setting user variables within expressions",
        "SET variable=expression, ... or SELECT expression(s) INTO variables(s)");
      if (OB_NOT_NULL(session_info_) && OB_NOT_NULL(session_info_->get_cur_exec_ctx()) &&
          OB_NOT_NULL(session_info_->get_cur_exec_ctx()->get_sql_ctx())) {
        const ObSqlCtx *sql_ctx = session_info_->get_cur_exec_ctx()->get_sql_ctx();
        LOG_ERROR("Variable assignment in order by items will cause uncertain behavior",
                  K(ObString(sql_ctx->sql_id_)));
      }
    }
    order_item.expr_ = expr;
  }
  return ret;
}

原因: 是Order by中使用了赋值的表达式，所以才会提示这种排序会导致排序的不确定性行为报错。

MySQL中演示一下order by中的赋值

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.36    |
+-----------+

mysql> use anbob
Database changed
mysql> create table st(id int primary key, name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into st value (1,'anbob'),(2,'weejar'),(3,'tom');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into st value (31,'anbob'),(22,'weejar'),(43,'tom');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from st;
+----+--------+
| id | name   |
+----+--------+
|  1 | anbob  |
|  2 | weejar |
|  3 | tom    |
| 22 | weejar |
| 31 | anbob  |
| 43 | tom    |
+----+--------+
6 rows in set (0.00 sec)

mysql> select * from st order by @x:=id;
+----+--------+
| id | name   |
+----+--------+
|  1 | anbob  |
|  2 | weejar |
|  3 | tom    |
| 22 | weejar |
| 31 | anbob  |
| 43 | tom    |
+----+--------+
6 rows in set, 1 warning (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
|   43 |
+------+
1 row in set (0.00 sec)

mysql> select * from st order by @x:=id desc ;
+----+--------+
| id | name   |
+----+--------+
| 43 | tom    |
| 31 | anbob  |
| 22 | weejar |
|  3 | tom    |
|  2 | weejar |
|  1 | anbob  |
+----+--------+
6 rows in set, 1 warning (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
|   43 |
+------+
1 row in set (0.00 sec)

Note: 看上去像最大值，但其实并不是，或者也不是最后一个，而有随机性，我们增加一个列再演示。

mysql> alter table st add column score int;

mysql> update st set score=mod(id,10);
Query OK, 6 rows affected (0.00 sec)

mysql> update st set score=100 where id=2;
Query OK, 1 row affected (0.00 sec)


mysql> select * from st order by @x:=score;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | anbob  |     1 |
| 31 | anbob  |     1 |
| 22 | weejar |     2 |
|  3 | tom    |     3 |
| 43 | tom    |     3 |
|  2 | weejar |   100 |
+----+--------+-------+
6 rows in set, 1 warning (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

Note: 可见3 并不是输出的最大值，也不是最后一个值。

这本身就是一种错误的语法使用，但执行时并不报错，而是给了warning.显示warning如下


mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                              |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

这种sql 级的用户变量已经被遗弃，建议使用set或 into的方法，即使使用sql 级@xx变量，也应该是放在select 后，而非order by，这种@xx 临时变量在MySQL或SQL Server中较常见。

mysql> select @x:=max(score) from st;
+----------------+
| @x:=max(score) |
+----------------+
|            100 |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

Oceanbase中复现该问题

select * from t1 order by @x := id2;

Note: 从OB OCP的告警信息在租户1006, SQL_ID可以从GV$OB_SQL_AUDIT中确认报错SQL.

解决方法

修正错误SQL。

— over —

Leave a Comment Cancel reply