Uncovering the Risks of Variable Assignment in ORDER BY Clauses

Preface

In OCP's alarm center, a severe alarm may appear when a variable assignment expression (e.g., @var := expr) is used in an ORDER BY clause. This usage is semantically ambiguous and can lead to unexpected execution behavior.

The article analyzes the source code, provides a case study, and recommends proper coding practices.

1. Alarm Details

Alarm Content

Variable assignment in order by items will cause uncertain behavior

Trigger Condition

The alarm is triggered when the parser detects an ORDER BY clause that contains an assignment expression such as @var := expr.

2. Source Code Analysis

The alarm originates from the SQL parsing module function ObSelectResolver::resolve_order_item. The relevant code fragment is:

if (OB_SUCC(ret) && OB_NOT_NULL(order_item.expr_) && order_item.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_)));
  }
}

Trigger Logic Explanation

order_item.expr_ : the expression inside the current ORDER BY item.

has_flag(CNT_ASSIGN_EXPR) : the flag is set when the expression contains an assignment operator :=.

Trigger Condition : when an ORDER BY expression includes an assignment, the alarm is raised.

The alarm fires only when order_item.expr_ is an assignment expression (i.e., contains :=).

Key Point : assignment must appear inside the ORDER BY expression; assignments in the SELECT list do not trigger this alarm.

3. Reproducing the Issue

Create a table to store student scores:

-- Create table
CREATE TABLE student_scores (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(50),
  score INT
);

-- Insert data
INSERT INTO student_scores VALUES
(1, 'Alice', 88),
(2, 'Bob', 95),
(3, 'Charlie', 90),
(4, 'Diana', 92);

-- Query that assigns the current highest score to @x
SELECT student_id, student_name, score
FROM student_scores
ORDER BY @x := score;

Executing the query triggers the alarm and produces an error similar to:

ERROR issue_dba_error (ob_log.cpp:1875) [9202][T1006_L0_G0][T1006][YB420ABA3A0F-0006360014353929-0-0] [lt=13][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=0, file="ob_select_resolver.cpp", line_no=1757, info="Variable assignment in order by items will cause uncertain behavior")

4. Why This Pattern Is Not Recommended

The result set is ordered by score, but the variable @x ends up with the value 92 instead of the expected 95. This occurs because @x receives the value from the last row whose assignment expression is actually evaluated, not necessarily the last row in the sorted result. Consequently, mixing assignment with ordering introduces nondeterministic behavior that can cause serious business logic errors.

Recommended Approach 1: Separate Max Query

SELECT @x := MAX(score) FROM student_scores;
SELECT @x;

Recommended Approach 2: Explicit Assignment While Preserving Order

SET @x := 0;

SELECT student_id, student_name, score, @x := score AS assign_x
FROM student_scores
ORDER BY score;

SELECT @x;

5. Summary

Assignments should be avoided inside ORDER BY clauses; variable‑assignment logic should be kept separate from sorting logic. Sorting is for presentation, while assignments are for data extraction, and mixing them can lead to unpredictable outcomes.

The syntax is accepted in MySQL and OceanBase MySQL tenants but is rejected in PostgreSQL, Oracle, and OceanBase Oracle tenants. Even where it is allowed, the behavior may not match expectations, so it is best to avoid.

Thought Questions

1. Why does the row with score 95 not perform the assignment?

Row 1 (score=88) -> @x := 88
Row 2 (score=90) -> @x := 90
Row 3 (score=92) -> @x := 92
Row 4 (score=95) -> [Why no assignment?]

2. Why does inserting another row make the result conform to expectations?

INSERT INTO student_scores VALUES (5, 'Elon', 99);
SELECT student_id, student_name, score
FROM student_scores
ORDER BY @x := score;
SELECT @x;