Values stored in user-defined variables can be referenced by other statements, enabling the passing of values from one statement to another.
User variables are written in the format @var_name, where the variable name 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`), the name can contain other characters. User variable names are case-insensitive and have a maximum length of 64 characters.
User-defined variables are session-specific. A user variable defined by one client is not visible or available to other clients. When that client exits, all variables for the specified client session are automatically released.
One way to set user-defined variables is to use 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.
You can assign values of the following data types to user variables: integer, decimal, floating-point, binary or non-binary string, or NULL. Assignments of decimal and floating-point values do not preserve the precision or number of decimal places of the value. Values of other data types are converted to the allowed data type. For example, values of time or spatial data types are converted to binary strings.
To read a user-defined variable, you can use the SELECT statement.
If a non-binary (character) string value is assigned to a user variable, it has the same character set and collation as the string. Hexadecimal or bitwise values assigned to a user variable are treated as binary strings. To assign a hexadecimal or bitwise value as a number to a user variable, you can add 0s 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 AI Database have the following limitations:
- OceanBase AI Database allows assigning values to user variables in statements other than
SET, but this feature may be removed in future versions. - The evaluation order of expressions involving user variables is undefined. For example, in the statement
SELECT @a, @a:=@a+123, it is not guaranteed that@ais evaluated first and then the assignment expression is executed. To avoid this issue, do not assign a value to a variable and read its value in the same statement.
MySQL compatibility notes
- In addition to a standalone execution engine similar to MySQL's, OceanBase AI Database also features a parallel scheduling engine for handling distributed queries. Changing the value of a user variable during execution can lead to inconsistencies in the values read by parallel tasks. To ensure correct user variable behavior under parallel execution, it is first necessary to guarantee variable value consistency across all nodes during execution. However, the synchronization overhead in this distributed scenario is prohibitively high, making it impossible to achieve full compatibility with MySQL in distributed environments.
- In SQL statements, do not use the
@var:=valsyntax to read user variables. This is compatible with MySQL. For example:SELECT c1, c2 FROM t1 WHERE c1=@var;. - If you use the
:=operator to modify the value of a user variable during SQL execution, the behavior of the user variable becomes undefined. The specific outcome depends on the execution order. - When using the
:=operator to modify user variable values during SQL execution, OceanBase AI Database maintains compatibility with MySQL 5.6, 5.7, and 8.0 in the following scenarios. In other cases, the behavior depends on the specific execution plan type and may differ from MySQL's behavior.- In the
INSERT VALUESclause, 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 variable's previous value, a compatibility issue may arise when retrying a failed execution, as the assignment result may differ from MySQL due to the change in the user variable's initial value.
- In the
Initialize user variables
If a user-defined variable is not initialized with a value, its default value in the database is NULL. Therefore, when using user-defined variables to simulate window functions, you typically initialize the variables in a subquery within the FROM clause of the SQL statement using SELECT @var:=val from dual, forming a JOIN query 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 chooses to use the NESTED LOOP JOIN algorithm and table B is used as the inner table for the JOIN, when the outer table data is an empty set, the OceanBase execution engine will adopt a short-circuit execution strategy and will not drive table B to perform calculations. As a result, the user variable initialization operations on table B will not be executed. In contrast, MySQL will execute initialization statements such as SELECT @var:=val from dual regardless of whether the outer table is empty or not.
--In MySQL, the initialization action rownum:=0 is executed regardless of whether data exists in t1.
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
obclient> SELECT @rownum;
+---------+
| @rownum |
+---------+
| NULL |
+---------+
1 row in set
To be compatible with MySQL, OceanBase AI Database treats assignment statements for user variables that appear in the FROM clause and do not reference an entity table as user variable initialization operations. At the beginning of statement execution, these initialization assignments are evaluated in the order specified in the statement. For example:
SELECT /*+use_nl(t1, A)*/ * FROM t1, (SELECT @rownum:=0 FROM DUAL) A;
--SELECT @rownum:=0 initializes the variable. Execute this clause first.
SELECT * FROM t1 WHERE (SELECT @rownum:=0 FROM DUAL)=0;
--SELECT @rownum:=0 is not in the FROM clause; it is not an initialization statement. Whether it is executed depends on whether there is data in t1.
SELECT /*+use_nl(t1, A)*/ * FROM t1, (SELECT @rownum:=(SELECT c1 FROM t2)) A;
--Although the SELECT @rownum:=(SELECT c1 FROM t1) statement is located in the FROM clause, it accesses the entity table t2 and is not an initialization statement.
SELECT * FROM t1 WHERE (SELECT rownum FROM (SELECT @rownum:=0) t)=0;
--SELECT @rownum:=0, which is located in the FROM clause, is an initialization statement.
@var:=val in a FROM subquery
When a user variable assignment appears in a FROM subquery, MySQL prohibits merging between FROM subqueries. Moreover, the data reference relationship between the two levels of queries is no longer iterated in a streaming manner. Instead, the results of all FROM subqueries containing the user variable := assignment are materialized into temporary tables for access by the upper-level query. 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 execution result shows that the SELECT @a:=c1, c1 FROM t1 is materialized first, and then the outer query SELECT @a, c1 FROM t is executed.
The result of this MySQL feature is stable and is used by users to simulate various grouping calculations of window functions. OceanBase is compatible with this behavior.
When a user-defined variable assignment := operation appears in a FROM subquery, the OB optimizer disables VIEW MERGE rewrite between FROM subqueries at different levels and adds a MATERIALOPERATOR between each subquery to block data stream computation between queries, thereby ensuring compatibility with MySQL's behavior.
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 a FROM subquery may cause the subquery to be evaluated and materialized in advance, which can affect optimizations such as pushing JOIN conditions from the original statement down to base tables. This prevents the NESTED LOOP JOIN from utilizing index conditions on inner tables, 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 AI Database allows you to use the user variable := statement in a user tenant to retrieve old and new values during DML execution, thereby optimizing client-server interactions. However, the following limitations apply:
- Due to internal execution limitations, DML cannot guarantee that the computation order of expressions strictly matches the user-defined order. Therefore, do not assign values to or access the same user variable in multiple DML clauses. For example, in the following statements:
INSERT INTO t1(c1, c2) VALUES(@a:=1, @a+1);UPDATE t1 SET c1=@a:=1, c2=@a+1 WHERE c1=1;andINSERT INTO t1(c1, c2) VALUES(@a:=1, @a+1);UPDATE t1 SET c1=@a:=1, c2=@a+1 WHERE c1=1;, it is impossible to guarantee that@a:=1is executed before@a+1. - Using
@var:=valin DML is only compatible with MySQL when DML affected_row=1. When affected_row>1, the execution order of SQL data may differ from MySQL, potentially leading to inconsistent results for user variables.
@var:=val in the SELECT clause
In some business scenarios, duplicate and redundant calculations may occur for certain expressions within the same clause. To reuse the results of these calculations, user variables can be assigned values and then referenced later on, as shown in the example SELECT @a:=(c1+1), @a+c2, @a*c3 FROM t1. However, the official MySQL documentation explicitly states that in such cases, the execution order of expressions containing user variables cannot be guaranteed, which means the results may not meet user expectations.
Based on research into specific MySQL behavior, it has been concluded that when the preceding queries appear in the SELECT clause of the top-level query, or when the execution order of expressions in the SELECT clause of a FROM subquery matches the user-defined order, this consistency is guaranteed. However, this is not guaranteed in other clauses. For example:
SELECT @a:=(c1+1), @a+c2, @a*c3 FROM t1;
--Appears in top-level queries, and expressions are executed in the user-defined order.
SELECT * FROM (SELECT @a:=(c1+1), @a+c2, @a*c3 FROM t1) t;
--Appears in the FROM subquery, and the expression is executed in the user-defined order.
SELECT * FROM t1 WHERE (c1, c2) in (SELECT @a:=c1, @a+1 FROM t2);
--Expressions containing user variables in WHERE subqueries are not guaranteed to be executed in the user-defined order, which may lead to uncertain query results.
OceanBase AI Database is compatible only with MySQL's deterministic behavior and may be incompatible with MySQL's non-deterministic behavior.
@var:=val in the WHERE clause
User variables := The execution result of an operation in the WHERE clause depends on the order in which predicate conditions are evaluated. MySQL's behavior in this regard is implementation-dependent, and OceanBase does not guarantee compatibility with such scenarios. For example:
SELECT * FROM t1 WHERE (@a:=c1)>10;
--The query result and the final value of @a are execution-related and do not comply with MySQL compatibility.
SELECT * FROM t1 WHERE c1 in (SELECT @a:=1 FROM t2);
--Located in the WHERE clause. The result depends on the implementation and is not compatible with MySQL.
@var:=val in an ORDER BY clause
The ORDER BY clause rearranges data, which disrupts the iteration order. Including a user variable := in the ORDER BY clause can lead to unpredictable results. OceanBase AI Database cannot achieve complete compatibility with MySQL when user variables are used in the ORDER BY clause. When user variables are used in the ORDER BY clause, their behavior is defined by the implementation. 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 specifies ascending order, the final rownum value points to the last row, 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 specifies a descending order, the final rownum value points to the last row, which is 2.
MySQL> SELECT @rownum;
+---------+
| @rownum |
+---------+
| 2 |
+---------+
1 row in set
--OceanBase 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 specifies ascending order, the final rownum value points to the last row, 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 specifies a descending order, the final rownum value points to the first row, which is still 4.
obclient> SELECT @rownum;
+---------+
| @rownum |
+---------+
| 4 |
+---------+
1 row in set
Therefore, if you use a user-defined variable := in the ORDER BY clause for assignment operations, or indirectly reference a column that has undergone a user-defined variable := operation for sorting, OceanBase cannot guarantee that the result of the user-defined variable remains compatible with MySQL after the sorting operation. Thus, do not use this user-defined variable again for related operations after the ORDER BY clause.
@var:=val will prevent the query from being executed in parallel
When a query contains the @var:=val operation, although OceanBase cannot guarantee compatibility with MySQL behavior in all scenarios, it must ensure that queries containing such operations are self-explanatory. Therefore, for queries with @var:=val assignment operations, the execution pipeline is enforced to be serial. Explicit parallel execution will not take effect for such queries. 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