Introduction to GBase 8a MPP Cluster SQL Standards

This content originally appeared on DEV Community and was authored by Cong Li

Today, I would like to introduce the SQL standards of the GBase 8a database, including identifier syntax rules, comment rules, user variables, reserved words, and more.

1.1 Identifier Syntax Rules

The names of objects such as databases, tables, columns, and aliases are referred to as identifiers. This section will describe the syntax rules allowed for identifiers in the GBase 8a MPP Cluster. The following table provides an overview of the maximum length and allowable characters for each type of identifier.

Notes:

1) Apart from the restrictions listed in the table, identifiers cannot contain ASCII(0) or ASCII(255). Database, table, and column names should not end with a space.
2) If an identifier is a reserved word or contains special characters, it must always be quoted with backticks (` `) when used, e.g., SELECT * FROM `select`.id > 100.
3) If an identifier exceeds the maximum length, commands for databases, tables, columns, views, and stored procedures will generate an error, while aliases will be truncated to 256 characters for display.

In practical application systems, identifiers must not use GBase 8a MPP Cluster reserved words or contain special characters. For a list of reserved words supported by GBase 8a MPP Cluster, refer to the reserved words section below.

1.2 Identifier Qualifiers

GBase 8a MPP Cluster allows names to consist of one or more identifiers. The components of a composite name should be separated by the period character ".". The initial part of a composite name serves as a qualifier, influencing the interpretation of the subsequent identifiers in context. In GBase 8a MPP Cluster, users can reference a column using any of the following methods:

If a composite identifier needs to be quoted, each part of the identifier must be quoted separately, not as a whole. For example: `gs-table`.`gs-column` is valid, while `gs-table.gs-column` is not.

In a column reference within a statement, it is unnecessary to explicitly specify a table_name, database_name.table_name, or vc_name.database_name.table_name prefix unless the reference is ambiguous. For example:

1) If both t1 and t2 tables contain a column c, when selecting c from a query using both t1 and t2, the c field is ambiguous since it is not unique to the query's tables. Therefore, it must be specified as t1.c or t2.c.
2) When retrieving data from table t in database db1 and table t in database db2, the user must specify which database's table is being referenced using db1.t.col_name and db2.t.col_name.
3) When retrieving data from table t in database db1 in virtual cluster vc1 and table t in database db2 in virtual cluster vc2, the user must specify which virtual cluster's table is being referenced using vc1.db1.t.col_name and vc2.db2.t.col_name.

1.3 Comment Syntax

GBase 8a MPP Cluster supports three styles of comments:

• #: Single-line comment.
• --: Single-line comment starting with -- to the end of the line. Note that the -- comment requires at least one space after the second dash.
• /* comment content */: This comment style supports single-line or multi-line comments and can be placed in the middle of a line. The /**/ enclosed sequence does not need to be on the same line, allowing for multi-line comments.

Note:
The -- comment style requires at least one space after the second dash. This syntax differs slightly from standard SQL comment styles.

Examples

Example 1: Using # for comments.

gbase> SELECT 1+1 FROM t;# This comment continues to the END of line
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set

Example 2: Using -- for comments.

gbase> SELECT 1+1 FROM t;-- This comment continues to the END of line
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set

Example 3: Using /* single-line */ for comments.

gbase> SELECT 1 /* this is an in-line comment */ + 1 FROM t;
+--------+
| 1  + 1 |
+--------+
|      2 |
+--------+
1 row in set

Example 4: Using /* multi-line */ for comments.

gbase> SELECT 1+
  /* 
   this is a
   multiple-line comment
  */
   1 FROM t;
+--------------+
| 1    +     1 |
+--------------+
|            2 |
+--------------+ 
1 row in set

1.4 User Variables

GBase 8a MPP Cluster supports user variables. The lifecycle of a user variable is session-based, and it is not visible to other sessions. When the user exits, all user variables for that user are automatically released.

User variables are written as @var_name.

Variables are defined and assigned values using the SET syntax:

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

The = is the assignment operator. The expr value assigned to each variable can be a real number, string, or NULL.

You can view the value of a user variable using the SELECT syntax:

SELECT @var_name [, @var_name] ... 

Example

Example 1: Assigning values to variables using the SET statement.

gbase> SET @t1='abc',@t2=null,@t3=4;
Query OK, 0 rows affected
gbase> SELECT @t1,@t2,@t3;
+------+------+------+
| @t1  | @t2  | @t3  |
+------+------+------+
| abc  | NULL |    4 |
+------+------+------+
1 row in set

User variables can be used anywhere an expression is allowed. If a user variable is not initialized, its value is NULL.

Note: Variables cannot be used in the context of constants, such as in the LIMIT clause of a SELECT.

1.5 Reserved Words

The following table lists the SQL reserved words supported by GBase 8a MPP Cluster. Please refer to it as needed.

This content originally appeared on DEV Community and was authored by Cong Li

Print Share Comment Cite Upload Translate Updates

APA

Cong Li | Sciencx (2024-08-21T02:55:55+00:00) Introduction to GBase 8a MPP Cluster SQL Standards. Retrieved from https://www.scien.cx/2024/08/21/introduction-to-gbase-8a-mpp-cluster-sql-standards/

MLA

" » Introduction to GBase 8a MPP Cluster SQL Standards." Cong Li | Sciencx - Wednesday August 21, 2024, https://www.scien.cx/2024/08/21/introduction-to-gbase-8a-mpp-cluster-sql-standards/

HARVARD

Cong Li | Sciencx Wednesday August 21, 2024 » Introduction to GBase 8a MPP Cluster SQL Standards., viewed ,<https://www.scien.cx/2024/08/21/introduction-to-gbase-8a-mpp-cluster-sql-standards/>

VANCOUVER

Cong Li | Sciencx - » Introduction to GBase 8a MPP Cluster SQL Standards. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2024/08/21/introduction-to-gbase-8a-mpp-cluster-sql-standards/

CHICAGO

" » Introduction to GBase 8a MPP Cluster SQL Standards." Cong Li | Sciencx - Accessed . https://www.scien.cx/2024/08/21/introduction-to-gbase-8a-mpp-cluster-sql-standards/

IEEE

" » Introduction to GBase 8a MPP Cluster SQL Standards." Cong Li | Sciencx [Online]. Available: https://www.scien.cx/2024/08/21/introduction-to-gbase-8a-mpp-cluster-sql-standards/. [Accessed: ]

rf:citation

» Introduction to GBase 8a MPP Cluster SQL Standards | Cong Li | Sciencx | https://www.scien.cx/2024/08/21/introduction-to-gbase-8a-mpp-cluster-sql-standards/ |

Please log in to upload a file.

There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.