User-defined variables|V4.4.2| docs|Distributed Database

User-defined variables

Last Updated：2026-03-06 07:02:42 Updated

Values stored in user-defined variables can be referenced by other statements to pass values from one statement to another.

User variables are written in the format @var_name, where var_name consists of letters, numbers, ., _, and $. If a user variable name is referenced as a string or identifier (for example, @'my-obvar', @"my-obvar", or @`my-obvar`), it can contain other characters. User variable names are case-insensitive and can be up to 64 characters long.

User-defined variables are session-specific. A user-defined variable defined by one client is not visible or usable by other clients. When the client exits, all variables defined for the session are automatically released.

One way to set a user-defined variable is by using the SET statement:

SET @var_name = expr [, @var_name = expr] ...

For more information, see SET.

For the SET statement, both = and := can be used as assignment operators.

User variables can be assigned values of the following data types: integers, decimals, floating-point numbers, binary or non-binary strings, or NULL. Assigning decimal or floating-point values does not preserve the precision or scale of the value. Values of other data types are converted to the allowed data type. For example, time or spatial data types are converted to binary strings.

To read a user-defined variable, you can use the SELECT statement.

If a user variable is assigned a non-binary (character) string value, it has the same character set and collation as the string. Hexadecimal or bit values assigned to a user variable are treated as binary strings. To assign a hexadecimal or bit value as a number to a user variable, you can add 0 or use the CAST function CAST(... AS UNSIGNED):

obclient> SET @v1 = X'43';
obclient> SET @v2 = X'43'+0;
obclient> SET @v3 = CAST(X'43' AS UNSIGNED);
obclient> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| C    |   67 |   67 |
+------+------+------+
1 row in set

obclient> SET @v1 = b'1000011';
obclient> SET @v2 = b'1000011'+0;
obclient> SET @v3 = CAST(b'1000011' AS UNSIGNED);
obclient> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| C    |   67 |   67 |
+------+------+------+
1 row in set

If the value of a user variable is selected in the result set, it is returned to the client as a string. If an uninitialized variable is referenced, its value is NULL and its type is string.

Limitations

User-defined variables in OceanBase Database have the following limitations:

In early versions of OceanBase Database, you can assign values to user variables in statements other than SET. This feature may be removed in later versions.
The evaluation order of expressions involving user variables is undefined. For example, in SELECT @a, @a:=@a+123, it is not guaranteed that @a will be calculated before the assignment expression is executed. To avoid this issue, do not assign a value to a variable and read the value of the variable in the same statement.
When executing statements in parallel, the correctness of user-defined variables cannot be guaranteed. You must use the /*+USE_DAS(...)*/ hint to specify sequential execution. In later versions, this feature will be optimized so that sequential execution is automatically performed without specifying the hint. In OceanBase Database 4.1.x and 4.2.0, you must use the /*+USE_DAS(...)*/ hint to control sequential execution. In OceanBase Database 4.2.1 and later, sequential execution is automatically performed without specifying the hint.

MySQL compatibility

OceanBase Database has a single-node execution engine similar to MySQL, but it also has a parallel scheduling engine for distributed queries. Changing user variables during execution can lead to inconsistent values read by parallel tasks. To ensure correct user variable behavior in parallel execution, the values must be consistent across all nodes. This synchronization overhead is significant in distributed scenarios, so full compatibility with MySQL is not guaranteed.
Reading user variables without using the @var:=val syntax is compatible with MySQL. For example, SELECT c1, c2 FROM t1 WHERE c1=@var; is compatible.
Real-time modification of user variables using := during SQL execution results in undefined behavior, which depends on the execution order. OceanBase Database V4.2.0 and earlier are not guaranteed to be fully compatible with MySQL in such cases.
In OceanBase Database V4.2.0, using := to modify user variables during SQL execution is compatible with MySQL 5.6, 5.7, and 8.0 in specific scenarios. In other cases, the results may vary based on the execution plan, potentially differing from MySQL's behavior.
- When used in the INSERT VALUES clause, for example, INSERT INTO t1 VALUES(@a:=1, @a);. If a user variable is assigned a value in an SQL statement and the execution result depends on the previous value of the variable, there may be compatibility differences with MySQL when the execution fails and is retried, due to changes in the initial value of the user variable.

Initializing user variables

If a user-defined variable is not initialized, its default value is NULL. To simulate window function behavior, you typically initialize user variables in the FROM subquery using SELECT @var:=val FROM dual, and join it with the main query. For example:

SELECT A.c1, (@rownum:= @rownum+1) AS rownum FROM t1 A,(SELECT @rownum:=0) B;
SELECT @rownum;

If the query uses the NESTED LOOP JOIN algorithm and B is the inner table, and the outer table has no data, OceanBase Database will use a short-circuit execution strategy and will not calculate B. Therefore, the initialization of user variables on B will not be executed, unlike MySQL, which always executes SELECT @var:=val FROM dual regardless of whether the outer table is empty.

--MySQL will execute rownum:=0 regardless of whether t1 contains data
MySQL> SELECT A.c1, (@rownum:= @rownum+1) AS rownum FROM t1 A,(SELECT @rownum:=0) B;
Empty set, 2 warnings

MySQL> SELECT @rownum;
+---------+
| @rownum |
+---------+
|       0 |
+---------+
1 row in set

--In OceanBase Database V4.2 and earlier, if Nested Loop Join is used and t1 is empty, rownum:=0 will not be executed
obclient> SELECT /*+use_nl(A, B)*/ A.c1, (@rownum:= @rownum+1) AS rownum FROM t1 A,(SELECT @rownum:=0) B;
Empty set

obclient> SELECT @rownum;
+---------+
| @rownum |
+---------+
| NULL    |
+---------+
1 row in set

To be compatible with MySQL, OceanBase treats user variable assignment statements in the FROM clause without an actual table as initialization operations. These initialization statements are prioritized and executed at the beginning of the statement, following the specified order. For example:

SELECT /*+use_nl(t1, A)*/ * FROM t1, (SELECT @rownum:=0 FROM DUAL) A;
--SELECT @rownum:=0 is a variable initialization operation and is executed first

SELECT * FROM t1 WHERE (SELECT @rownum:=0 FROM DUAL)=0;
--SELECT @rownum:=0 is not in the FROM clause and is not an initialization statement. Its execution depends on whether t1 contains data

SELECT /*+use_nl(t1, A)*/ * FROM t1, (SELECT @rownum:=(SELECT c1 FROM t2)) A;
--SELECT @rownum:=(SELECT c1 FROM t1) is in the FROM clause but accesses the actual table t2, so it is not an initialization statement

SELECT * FROM t1 WHERE (SELECT rownum FROM (SELECT @rownum:=0) t)=0;
--SELECT @rownum:=0 is in the FROM clause and is an initialization statement

@var:=val in FROM subqueries

When user variable assignment occurs in a FROM subquery, MySQL prohibits the merging of FROM subqueries and no longer iterates data references between layers in a streaming manner. Instead, it materializes the results of FROM subqueries containing := into temporary tables for upper-level queries to access. For example:

MySQL> CREATE TABLE t1(c1 INT);
Query OK, 0 rows affected

MySQL> INSERT INTO t1 VALUES(1), (2), (3);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

MySQL> SELECT @a, c1 FROM (SELECT @a:=c1, c1 FROM t1) t;
+------+------+
| @a   | c1   |
+------+------+
|    3 |    1 |
|    3 |    2 |
|    3 |    3 |
+------+------+
3 rows in set, 1 warning

The final result shows that SELECT @a:=c1, c1 FROM t1 is materialized before the outer query SELECT @a, c1 FROM t is executed.

MySQL's behavior is stable and is used to simulate various grouping calculations in window functions. OceanBase Database is compatible with this behavior.

When user variable := operations occur in FROM subqueries, the OB optimizer prohibits VIEW MERGE rewriting between layers and adds MATERIAL operators between subqueries to block streaming data calculations, ensuring compatibility with MySQL.

obclient>EXPLAIN SELECT @a, c1 FROM (SELECT @a:=c1, c1 FROM t1) t;
+---------------------------------------------------------------------+
| Query Plan                                                          |
+---------------------------------------------------------------------+
| ===============================================================     |
| |ID|OPERATOR                       |NAME|EST.ROWS|EST.TIME(us)|     |
| ---------------------------------------------------------------     |
| |0 |MATERIAL                       |    |1       |4           |     |
| |1 |└─SUBPLAN SCAN                 |t   |1       |4           |     |
| |2 |  └─DISTRIBUTED TABLE FULL SCAN|t1  |1       |4           |     |
| ===============================================================     |
| Outputs & filters:                                                  |
| -------------------------------------                               |
|   0 - output([('a')], [t.c1]), filter(nil)                          |
|   1 - output([t.c1]), filter(nil)                                   |
|       access([t.c1])                                                |
|   2 - output([(T_OP_ASSIGN, 'a', t1.c1)], [t1.c1]), filter(nil)     |
|       access([t1.c1]), partitions(p0)                               |
|       is_index_back=false, is_global_index=false,                   |
|       range_key([t1.__pk_increment]), range(MIN ; MAX)always true   |
+---------------------------------------------------------------------+
16 rows in set

Note that using := in FROM subqueries may cause the subquery to be evaluated and materialized early, which can affect optimizations such as pushing join conditions down to base tables. This can prevent the use of index conditions on inner tables in NESTED LOOP JOINs, impacting execution efficiency. For example:

CREATE TABLE t1(a INT, b INT);
CREATE TABLE t2(a INT PRIMARY KEY, b INT);
obclient> EXPLAIN SELECT /*+use_nl(t1 t)*/ * FROM t1 JOIN (SELECT @b:=b AS b, a FROM t2) t ON t1.a=t.a WHERE t1.b=1;
+------------------------------------------------------------------------------------+
| Query Plan                                                                         |
+------------------------------------------------------------------------------------+
| =================================================================                  |
| |ID|OPERATOR                         |NAME|EST.ROWS|EST.TIME(us)|                  |
| -----------------------------------------------------------------                  |
| |0 |NESTED-LOOP JOIN                 |    |1       |8           |                  |
| |1 |├─DISTRIBUTED TABLE FULL SCAN    |t1  |1       |4           |                  |
| |2 |└─MATERIAL                       |    |1       |4           |                  |
| |3 |  └─SUBPLAN SCAN                 |t   |1       |4           |                  |
| |4 |    └─DISTRIBUTED TABLE FULL SCAN|t2  |1       |4           |                  |
| =================================================================                  |
| Outputs & filters:                                                                 |
| -------------------------------------                                              |
|   0 - output([t1.a], [t1.b], [t.b], [t.a]), filter(nil)                            |
|       conds([t1.a = t.a]), nl_params_(nil), use_batch=false                        |
|   1 - output([t1.a], [t1.b]), filter([t1.b = 1])                                   |
|       access([t1.a], [t1.b]), partitions(p0)                                       |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],  |
|       range_key([t1.__pk_increment]), range(MIN ; MAX)always true                  |
|   2 - output([t.b], [t.a]), filter(nil)                                            |
|   3 - output([t.a], [t.b]), filter(nil)                                            |
|       access([t.a], [t.b])                                                         |
|   4 - output([(T_OP_ASSIGN, 'b', t2.b)], [t2.a]), filter(nil)                      |
|       access([t2.a], [t2.b]), partitions(p0)                                       |
|       is_index_back=false, is_global_index=false,                                  |
|       range_key([t2.a]), range(MIN ; MAX)always true                               |
+------------------------------------------------------------------------------------+
24 rows in set

@var:=val in DML statements

OceanBase Database allows the use of user variables := in MySQL-compatible mode to extract new and old values during DML execution, reducing client-server interactions and simulating the DML RETURNING INTO feature of Oracle-compatible mode. However, there are some limitations:

Due to internal execution constraints, the order of expression evaluation in DML statements may not strictly follow the user-defined order. Therefore, avoid assigning and accessing the same user variable in DML clauses. For example: INSERT INTO t1(c1, c2) VALUES(@a:=1, @a+1); UPDATE t1 SET c1=@a:=1, c2=@a+1 WHERE c1=1; In both examples, @a:=1 may not be executed before @a+1.
Using @var:=val in DML statements is only compatible with MySQL when affected_row=1. When affected_row>1, the execution order of SQL data may differ from MySQL, leading to potential differences in user variable results.

@var:=val in the SELECT clause

In some business scenarios, expressions may be redundantly calculated within the same clause, and the results can be reused by assigning values to user variables and referencing them later. For example, in the query SELECT @a:=(c1+1), @a+c2, @a*c3 FROM t1, the results of the first expression are reused in the subsequent expressions. However, the MySQL official documentation explicitly states that the execution order of expressions containing user variables cannot be guaranteed, so the results may not meet user expectations.

After investigating MySQL's specific behavior, we found that when the above query appears in the SELECT clause of the top-level query or in the SELECT clause of a FROM subquery, the execution order of the expressions follows the user-defined order. In other clauses, this is not guaranteed. For example:

SELECT @a:=(c1+1), @a+c2, @a*c3 FROM t1;
-- In the top-level query, the expressions are executed in the user-defined order.

SELECT * FROM (SELECT @a:=(c1+1), @a+c2, @a*c3 FROM t1) t;
-- In the FROM subquery, the expressions are executed in the user-defined order.

SELECT * FROM t1 WHERE (c1, c2) in (SELECT @a:=c1, @a+1 FROM t2);
-- In the WHERE subquery, the execution order of expressions containing user variables is not guaranteed, leading to potentially unpredictable query results.

OceanBase Database only supports the deterministic behavior of MySQL. It may not be compatible with non-deterministic behaviors.

@var:=val in the WHERE clause

The result of the := operation in the WHERE clause depends on the execution order of the predicate conditions. MySQL's behavior is implementation-dependent, and OceanBase Database does not guarantee compatibility in such scenarios. For example:

SELECT * FROM t1 WHERE (@a:=c1)>10;
-- The query results and the final value of @a depend on the execution, and are not guaranteed to be compatible with MySQL.

SELECT * FROM t1 WHERE c1 in (SELECT @a:=1 FROM t2);
-- In the WHERE clause, the results depend on the implementation and are not guaranteed to be compatible with MySQL.

@var:=val in the ORDER BY clause

The ORDER BY clause reorders the data, which disrupts the iteration order. When user variables := are used in the ORDER BY clause, the results may be unpredictable. OceanBase Database does not guarantee complete compatibility with MySQL in such scenarios. For example:

CREATE TABLE t1(a INT PRIMARY KEY, b INT);
INSERT INTO t1 VALUES(1, 1), (2, 1), (3, 1), (4, 1), (5, 5), (6, 6);

-- MySQL behavior
MySQL> SELECT @rownum:=@rownum+1 AS rn, MAX(a), sum(a) FROM t1, (SELECT @rownum:=1) t GROUP BY b ORDER BY rn;
+------+--------+--------+
| rn   | max(a) | sum(a) |
+------+--------+--------+
|    2 |      4 |     10 |
|    3 |      5 |      5 |
|    4 |      6 |      6 |
+------+--------+--------+
3 rows in set, 2 warnings

-- When the ORDER BY clause is in ascending order, the final value of @rownum points to the last row value, which is 4.
MySQL> SELECT @rownum;
+---------+
| @rownum |
+---------+
|       4 |
+---------+
1 row in set

MySQL> SELECT @rownum:=@rownum+1 AS rn, MAX(a), SUM(a) FROM t1, (SELECT @rownum:=1) t GROUP BY b ORDER BY rn desc;
+------+--------+--------+
| rn   | max(a) | sum(a) |
+------+--------+--------+
|    4 |      6 |      6 |
|    3 |      5 |      5 |
|    2 |      4 |     10 |
+------+--------+--------+
3 rows in set, 2 warnings

-- When the ORDER BY clause is in descending order, the final value of @rownum points to the last row value, which is 2.
MySQL> SELECT @rownum;
+---------+
| @rownum |
+---------+
|       2 |
+---------+
1 row in set

-- OceanBase Database behavior
obclient> SELECT @rownum:=@rownum+1 AS rn, MAX(a), SUM(a) FROM t1, (SELECT @rownum:=1) t GROUP BY b ORDER BY rn;
+------+--------+--------+
| rn   | max(a) | sum(a) |
+------+--------+--------+
|    2 |      4 |     10 |
|    3 |      5 |      5 |
|    4 |      6 |      6 |
+------+--------+--------+
3 rows in set

-- When the ORDER BY clause is in ascending order, the final value of @rownum points to the last row value, which is 4.
obclient> SELECT @rownum;
+---------+
| @rownum |
+---------+
|       4 |
+---------+
1 row in set

obclient> SELECT @rownum:=@rownum+1 AS rn, MAX(a), SUM(a) FROM t1, (SELECT @rownum:=1) t GROUP BY b ORDER BY rn desc;
+------+--------+--------+
| rn   | max(a) | sum(a) |
+------+--------+--------+
|    4 |      6 |      6 |
|    3 |      5 |      5 |
|    2 |      4 |     10 |
+------+--------+--------+
3 rows in set

-- When the ORDER BY clause is in descending order, the final value of @rownum points to the first row value, which is still 4.
obclient> SELECT @rownum;
+---------+
| @rownum |
+---------+
|       4 |
+---------+
1 row in set

Therefore, if user-defined variables := are used in the ORDER BY clause for assignment operations, or if columns resulting from := operations are indirectly referenced for sorting, OceanBase Database cannot guarantee that the results will remain compatible with MySQL. Therefore, do not use the user-defined variable after the ORDER BY clause for further operations.

@var:=val prevents query parallelism

When a query contains the @var:=val operation, although OceanBase Database cannot fully align with all MySQL behaviors, it must ensure that queries involving such operations can be self-explained. Therefore, in queries with @var:=val assignments, the execution process is forced to be serial. As a result, parallel execution for these queries will not take effect, for example:

obclient> EXPLAIN EXTENDED_NOADDR SELECT /*+parallel(2)*/ @a:=c1 FROM t1;
+-------------------------------------------------------------------+
| Query Plan                                                        |
+-------------------------------------------------------------------+
| ===========================================================       |
| |ID|OPERATOR                   |NAME|EST.ROWS|EST.TIME(us)|       |
| -----------------------------------------------------------       |
| |0 |DISTRIBUTED TABLE FULL SCAN|t1  |1       |4           |       |
| ===========================================================       |
| Outputs & filters:                                                |
| -------------------------------------                             |
|   0 - output([(T_OP_ASSIGN, 'a', t1.c1)]), filter(nil)            |
|       access([t1.c1]), partitions(p0)                             |
|       is_index_back=false, is_global_index=false,                 |
|       range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
| Used Hint:                                                        |
| -------------------------------------                             |
|   /*+                                                             |
|                                                                   |
|       PARALLEL(2)                                                 |
|   */                                                              |
| Qb name trace:                                                    |
| -------------------------------------                             |
|   stmt_id:0, stmt_type:T_EXPLAIN                                  |
|   stmt_id:1, SEL$1                                                |
| Outline Data:                                                     |
| -------------------------------------                             |
|   /*+                                                             |
|       BEGIN_OUTLINE_DATA                                          |
|       FULL(@"SEL$1" "test"."t1"@"SEL$1")                          |
|       USE_DAS(@"SEL$1" "test"."t1"@"SEL$1")                       |
|       PARALLEL(2)                                                 |
|       OPTIMIZER_FEATURES_ENABLE('4.0.0.0')                        |
|       END_OUTLINE_DATA                                            |
|   */                                                              |
| Optimization Info:                                                |
| -------------------------------------                             |
|   t1:                                                             |
|       table_rows:1                                                |
|       physical_range_rows:1                                       |
|       logical_range_rows:1                                        |
|       index_back_rows:0                                           |
|       output_rows:1                                               |
|       table_dop:1                                                 |
|       dop_method:DAS DOP                                          |
|       avaiable_index_name:[t1]                                    |
|       stats version:0                                             |
|       dynamic sampling level:0                                    |
|   Plan Type:                                                      |
|       LOCAL                                                       |
|   Note:                                                           |
|       Degree of Parallelism is 1 because of hint                  |
+-------------------------------------------------------------------+
48 rows in set