Variables

Description

In Doris, variables are divided into system variables and user variables. Both are case-insensitive.

System variables affect the behavior of Doris. Both system variables and user variables can be used in user queries.

System Variables

System variables are a set of variables predefined by Doris to control database behavior and performance. The main features are as follows:

• Variable Types:

  • Read-only variables: These variables are set by the system and cannot be modified by users, such as version, current_timestamp, etc.

  • Modifiable variables: Users can modify the values of these variables at runtime, such as exec_mem_limit, time_zone, etc.

• Scope:

  • Global variables (Global): Affect all sessions and set with SET GLOBAL.

  • Session variables (Session): Only affect the current session and set with SET.

  • Some variables have both global and session scopes.

• Access Methods:

  • Use SHOW VARIABLES to view all system variables.

  • Use SHOW VARIABLES LIKE 'pattern' to view specific variables by pattern matching.

• Persistence:

  • Modifications to global variables are reset to default values after a system restart; session restarts do not reset to default values.

  • Changes to session variables are lost after the session ends.

User-Defined Variables

User-defined variables are a mechanism for temporarily storing data within a session. The main features are as follows:

• Naming Rules:

  • Must be prefixed with @.

  • Variable names can include letters, numbers, and underscores.

  • Case-insensitive.

• Scope:

  • Only valid within the current session.

  • Automatically destroyed after the session ends.

  • Variables with the same name in different sessions are independent of each other.

• Assignment Methods:

  • Use SET @var_name = value syntax for assignment.

  • Supports assigning values using expression results.

• Data Types:

  • Can store numbers (integers, floating-point numbers).

  • Can store strings.

  • Can store date-time values.

  • Can store NULL values.

  • The type is automatically determined at assignment.

System Variable Definitions and Query Statements

• SHOW VARIABLES

  You can view variables with SHOW VARIABLES LIKE 'variable_name'.

  SHOW VARIABLES LIKE '%time_zone%';
  +------------------+----------------+----------------+---------+
  | Variable_name    | Value          | Default_Value  | Changed |
  +------------------+----------------+----------------+---------+
  | system_time_zone | Asia/Hong_Kong | Asia/Hong_Kong | 0       |
  | time_zone        | Asia/Hong_Kong | Asia/Hong_Kong | 0       |
  +------------------+----------------+----------------+---------+

  Or view all variables with SHOW VARIABLES.

  SHOW VARIABLES
  +------------------------------------------------------------------+---------------------------------------+---------------------------------------+---------+
  | Variable_name                                                    | Value                                 | Default_Value                         | Changed |
  +------------------------------------------------------------------+---------------------------------------+---------------------------------------+---------+
  | DML_PLAN_RETRY_TIMES                                             | 3                                     | 3                                     | 0       |
  | adaptive_pipeline_task_serial_read_on_limit                      | 10000                                 | 10000                                 | 0       |
  | allow_modify_materialized_view                              | false                                 | false                                 | 0       |
  | allow_partition_column_nullable                            | true                                  | true                                  | 0       |
  | analyze_timeout                                           | 43200                                 | 43200                                 | 0       |
  | version                                                      | 5.7.99                                | 5.7.99                                | 0       |
  | version_comment                                                | Doris version doris0.0.0--de61c5823 | Doris version doris-0.0--de61c5823 | 0       |
  | wait_full_block_schedule_times                                 | 2                                     | 2                                     | 0       |
  | wait_timeout                                               | 28800                                 | 28800                                 | 0       |
  | workload_group                                                 |                                       |                                       | 0       |
  +------------------------------------------------------------------+---------------------------------------+---------------------------------------+---------+
  360 rows in set (0.01 sec)

• SET

  Some variables can be set to take effect globally or only in the current session.

  Set to take effect only in the current session with SET. For example:

  SET exec_mem_limit = 137438953472;
  SHOW VARIABLES LIKE '%exec_mem_limit%';
  +----------------+--------------+---------------+---------+
  | Variable_name  | Value        | Default_Value | Changed |
  +----------------+--------------+---------------+---------+
  | exec_mem_limit | 137438953472 | 2147483648    | 1       |
  +----------------+--------------+---------------+---------+
  1 row in set (0.01 sec)
  SET forward_to_master = true;
  SHOW VARIABLES LIKE '%forward_to_master%';
  +-------------------+-------+---------------+---------+
  | Variable_name     | Value | Default_Value | Changed |
  +-------------------+-------+---------------+---------+
  | forward_to_master | true  | true          | 0       |
  +-------------------+-------+---------------+---------+
  1 row in set (0.00 sec)
  SET time_zone = "Asia/Shanghai";
  SHOW VARIABLES LIKE '%time_zone%';
  +------------------+----------------+----------------+---------+
  | Variable_name    | Value          | Default_Value  | Changed |
  +------------------+----------------+----------------+---------+
  | time_zone        | Asia/Shanghai  | Asia/Hong_Kong | 1       |
  | system_time_zone | Asia/Hong_Kong | Asia/Hong_Kong | 0       |
  +------------------+----------------+----------------+---------+
  2 rows in set (0.00 sec)

  Set globally

  SET GLOBAL exec_mem_limit = 137438953472;
  SHOW VARIABLES LIKE '%exec_mem_limit%';
  +----------------+--------------+---------------+---------+
  | Variable_name  | Value        | Default_Value | Changed |
  +----------------+--------------+---------------+---------+
  | exec_mem_limit | 137438953472 | 2147483648    | 1       |
  +----------------+--------------+---------------+---------+
  1 row in set (0.01 sec)

• UNSET

  Syntax:

  UNSET (GLOBAL | SESSION | LOCAL)? VARIABLE (ALL | identifier)
  unset global variable exec_mem_limit;
  SHOW VARIABLES LIKE '%exec_mem_limit%';
  +----------------+------------+---------------+---------+
  | Variable_name  | Value      | Default_Value | Changed |
  +----------------+------------+---------------+---------+
  | exec_mem_limit | 2147483648 | 2147483648    | 0       |
  +----------------+------------+---------------+---------+
  1 row in set (0.00 sec)

User Variable Definition and Query Statements

User-defined variables can be defined with the following statement:

SET @var_name = constant_value|constant_expr;

Setting examples:

SET @v1 = "A";
SET @v2 = 32+33;
SET @v3 = str_to_date("2024-12-29 10:11:12", '%Y-%m-%d %H:%i:%s');

Can be used in queries

SELECT @v1, @v2, @v3;
+------+------+--------------------+
| @v1  | @v2  | @v3                 |
+------+------+--------------------+
| A    |   65 | 2024-12-29 10:11:12 |
+------+------+--------------------+
1 row in set (0.01 sec)