SingleStore Managed Service

Remarks
  • Refer to user-defined variables as @user_defined_variable.

  • User-defined variables are case-insensitive, which means @rollno is the same as @RollNo.

  • These variables are session-specific; they expire once the session ends. Also, a session variable defined by a user cannot be viewed or referenced by another session.

  • The datatype of a user-defined variable is inferred automatically.

  • The INFORMATION_SCHEMA.USER_VARIABLES table contains a list of all the user-defined variables.

Warning

Do not use the SET command to define or assign values to user-defined variables. The feature SET @myvar = <value> is not supported.

SET @pi = 3.14;

SHOW WARNINGS;
****
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                         |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1706 | Feature 'Updating User Defined Variables using SET query' is not supported by MemSQL. Execution will continue, but the feature will be ignored. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
Limitations of User-Defined Variables
  • A user-defined variable must be assigned a value before it can be referenced in a query.

    Notice

    Currently, SingleStore DB does not support initializing (or updating) user-defined variables using a SET query. Although the SET @var = value query does not throw an error, use the SELECT ... INTO statement instead to initialize a user-defined variable.

  • The SELECT ... INTO statement must return only a single row.

  • These variables cannot be used directly as an identifier or part of an identifier in a SingleStore DB query, for example SELECT `@col_name` FROM table_name is not allowed.

    Important

    An exception to this rule is a column defined as CREATE TABLE t (`@col_name` INT). In this case SELECT `@col_name` FROM t returns the value from the column.

  • These variables cannot be used in the GROUP BY or ORDER BY clause.

  • User-defined variables cannot be referenced in stored procedures, computed columns, user-defined functions, table-valued functions, or views.

  • If a query uses @ in a LOAD DATA statement, SingleStore DB interprets it as a reference to a LOAD DATA assignment instead of a user-defined variable.