Introduction to Parentheses, Logical Operators, Conversion Operators & Functions, and Date Arithmetic in GBase 8a MPP Cluster

Today, we'll discuss the usage of parentheses, logical operators, conversion operators & functions, and date arithmetic in GBase 8a MPP Cluster database.

1. Parentheses

Description: (...) Parentheses are used to specify the order of operations in an expression. Operators within parentheses are executed first.

Examples

Example 1: Without parentheses, the multiplication operation is performed before addition.

gbase> SELECT 1+2*3 FROM dual;
+-------+
| 1+2*3 |
+-------+
|     7 |
+-------+
1 row in set

Example 2: With parentheses, the addition inside the parentheses is performed before the multiplication outside.

gbase> SELECT (1+2)*3 FROM dual;
+---------+
| (1+2)*3 |
+---------+
|       9 |
+---------+
1 row in set

2. Logical Operators

Overview: In SQL, all logical operators return values of TRUE, FALSE, or NULL (UNKNOWN), represented by 1 (TRUE), 0 (FALSE), and NULL.

2.1 NOT, ! Logical Negation

Operator Description: If the operand is 0, the result is 1; if the operand is non-zero, the result is 0; if the operand is NULL, the result is NULL.

Examples

Example 1: Operand is non-zero, result is 0.

gbase> SELECT NOT 10 FROM dual;
+--------+
| NOT 10 |
+--------+
|      0 |
+--------+
1 row in set

Example 2: Operand is 0, result is 1.

gbase> SELECT NOT 0 FROM dual;
+-------+
| NOT 0 |
+-------+
|     1 |
+-------+
1 row in set

Example 3: Operand is NULL, result is NULL.

gbase> SELECT NOT NULL FROM dual;
+-----------+
| NOT NULL  |
+-----------+
|      NULL |
+-----------+
1 row in set

Example 4: Expression's value is non-zero, result is 0.

gbase> SELECT ! (1+1) FROM dual;
+---------+
| ! (1+1) |
+---------+
|       0 |
+---------+
1 row in set

Example 5: ! 1+1 is equivalent to (!1)+1, and the result is 1.

gbase> SELECT ! 1+1 FROM dual;
+-------+
| ! 1+1 |
+-------+
|     1 |
+-------+
1 row in set
gbase> SELECT (!1)+1 FROM dual;
+--------+
| (!1)+1 |
+--------+
|      1 |
+--------+
1 row in set

Example 6: ...NOT IN...

gbase> SELECT 1 NOT IN (2,3,null) FROM dual;
+---------------------+
| 1 NOT IN (2,3,null) |
+---------------------+
|                NULL |
+---------------------+
1 row in set

2.2 XOR Logical Exclusive OR

Syntax: a XOR b is equivalent to (a AND (NOT b)) OR ((NOT a) AND b)

Operator Description: If either operand is NULL, the result is NULL. For non-NULL operands:

XOR	TRUE (1)	FALSE (0)
TRUE (1)	FALSE	TRUE
FALSE (0)	TRUE	FALSE

This means that if the two values are different, the XOR result is TRUE; otherwise, it is FALSE.

Examples

Example 1: Non-NULL operands, TRUE XOR TRUE results in FALSE, i.e., 0.

gbase> SELECT 1 NOT IN (2,3,null) FROM dual;
+---------------------+
| 1 NOT IN (2,3,null) |
+---------------------+
|                NULL |
+---------------------+
1 row in set

Example 2: Non-NULL operands, TRUE XOR FALSE results in TRUE, i.e., 1.

gbase> SELECT 1 XOR 0 FROM dual;
+---------+
| 1 XOR 0 |
+---------+
|       1 |
+---------+
1 row in set

Example 3: Any operand is NULL, the result is NULL.

gbase> SELECT 1 XOR NULL FROM dual;
+-------------+
| 1 XOR NULL  |
+-------------+
|        NULL |
+-------------+
1 row in set
gbase> SELECT 0 XOR NULL FROM dual;
+-------------+
| 0 XOR NULL  |
+-------------+
|        NULL |
+-------------+
1 row in set

Example 4: a XOR b is equivalent to (a AND (NOT b)) OR ((NOT a) AND b).

gbase> SELECT 1 XOR 0 FROM dual;
+---------+
| 1 XOR 0 |
+---------+
|       1 |
+---------+
1 row in set
gbase> SELECT (1 AND (NOT 0)) OR ((NOT 1) AND 0) ;
+------------------------------------+
| (1 AND (NOT 0)) OR ((NOT 1) AND 0) |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set

Example 5: XORing a number with itself and then XORing the result with the number again gives 1.

gbase> SELECT 1 XOR 1 XOR 1 FROM dual;
+---------------+
| 1 XOR 1 XOR 1 |
+---------------+
|             1 |
+---------------+
1 row in set

3. Conversion Operators and Functions

3.1 BINARY

Operator Description: Using the BINARY operator before a string enables case-sensitive comparison.

Examples

Example 1: Without BINARY before a string, comparison is case-insensitive.

gbase> SELECT 'a' = 'A' FROM dual;
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |
+-----------+
1 row in set

gbase> SELECT 'a' = 'a ' FROM dual;
+------------+
| 'a' = 'a ' |
+------------+
|          1 |
+------------+
1 row in set

Example 2: With BINARY before a string, comparison is case-sensitive.

gbase> SELECT BINARY 'a' = 'A' FROM dual;
+------------------+
| BINARY 'a' = 'A' |
+------------------+
|                0 |
+------------------+
1 row in set

Example 3: With BINARY before a string, compare trailing spaces.

gbase> SELECT BINARY 'a' = 'a ' FROM dual;
+-------------------+
| BINARY 'a' = 'a ' |
+-------------------+
|                 0 |
+-------------------+
1 row in set

3.2 CAST and CONVERT Functions

Syntax: CAST(expr AS type), CONVERT(expr,type), CONVERT(expr USING transcoding_name)

Function Description: CAST() and CONVERT() functions are used to convert a value from one type to another.

Type can be one of the following values:

• CHAR, DATE, DATETIME, DECIMAL, TIME, NUMERIC, INT, FLOAT, DOUBLE, VARCHAR, TIMESTAMP
• CAST() and CONVERT(...USING...) are standard SQL syntax.
• CAST(str AS BINARY) is equivalent to BINARY str.
• CAST(expr AS CHAR) treats the expression as a string in the default character set.
• CAST(expr AS float(M,D)), CAST(expr AS double(M,D)), where M is a maximum value of 255, and D is a maximum value of 30.
• CAST(expr AS Float(X)) specifies length; when X < 24, it's treated as float; when 24 < X <= 53, it's treated as double with maximum length and precision.

Notes:

• Changing column type to DATE, DATETIME, or TIME using CAST() only changes the column's data type, not the values.
• Casting to varchar(0) results in an empty string; creating a table with varchar(0) from non-empty columns will result in an error.
• CAST as timestamp supports converting '2020-01-02 11:11:12.123451' to timestamp, but create as select will truncate to second level.
• Timestamp supports UTC time format strings from '1970-01-01 00:00:01' to '2038-01-10 03:14:07', but the maximum storage value is '2038-01-01 00:59:59'.
• To support microsecond precision in Timestamp, enable parameter: _gbase_timestamp_append_prec=1.

Examples

Example 1: Convert NOW() to DATE type.

gbase> SELECT CAST(NOW() AS DATE) FROM dual;
+---------------------+
| CAST(NOW() AS DATE) |
+---------------------+
| 2020-04-01          |
+---------------------+
1 row in set

Example 2: String and numeric type conversion is implicit; treat string values as numbers.

gbase> SELECT 1+'1' FROM dual;
+-------+
| 1+'1' |
+-------+
|     2 |
+-------+
1 row in set

Example 3: CAST(str AS BINARY) is equivalent to BINARY str.

gbase> SELECT CAST('a' AS BINARY) = 'a ' FROM dual;
+----------------------------+
| CAST('a' AS BINARY) = 'a ' |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set

gbase> SELECT 'A' = 'a ';
+------------+
| 'A' = 'a ' |
+------------+
|          1 |
+------------+
1 row in set

gbase> SELECT BINARY 'A' = 'a ' FROM dual;
+-------------------+
| BINARY 'A' = 'a ' |
+-------------------+
|                 0 |
+-------------------+
1 row in set

Example 4: CAST(str AS varchar(X)) Example

gbase> select cast('1.2345' as varchar) as data_varchar;
+--------------+
| data_varchar |
+--------------+
| 1.2345       |
+--------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select cast('1.2345' as varchar(10)) as data_varchar;
+--------------+
| data_varchar |
+--------------+
| 1.2345       |
+--------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select cast('1.2345' as varchar(3)) as data_varchar;
+--------------+
| data_varchar |
+--------------+
| 1.2          |
+--------------+
1 row in set, 1 warning (Elapsed: 00:00:00.00)
gbase> select cast('1.2345' as varchar(0)) as data_varchar;
+--------------+
| data_varchar |
+--------------+
|              |
+--------------+
1 row in set, 1 warning (Elapsed: 00:00:00.01)
gbase> create table t3 as select cast(a as varchar) as a from t;
Query OK, 2 rows affected (Elapsed: 00:00:00.51)
gbase> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)
gbase> create table t4 as select cast(a as varchar(0)) as a from t;
ERROR 1705 (HY000): gcluster DML error: [192.168.146.21:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: Truncated incorrect CHAR(0) value: '1'
SQL: SELECT /*192.168.146.20_6_31_2021-01-14_15:25:42*/ /*+ TID('111') */ cast(`vcname000001.testdb.t`.`a` as char(0)) AS `a` FROM `testdb`.`t_n1` `vcname000001.testdb.t` target into server (HOST '192.168.146.21,192.168.146.20', PORT 5050, USER 'root', PASSWORD '', DATABASE 'testdb', TABLE 't4_n1', COMMENT 'col_seq 0, table_host 0 0 1, scn 18, distribution 1' )

3.3 TO_SINGLE_BYTE

Syntax: TO_SINGLE_BYTE(arg)

Function Description:

The TO_SINGLE_BYTE function converts full-width characters in the input arg to half-width characters. The arg can be any type of value or column. If arg is a string containing full-width characters, these will be converted to half-width characters in the output, while other characters remain unchanged.

This function is only valid under UTF8 and GBK character sets. Currently, only 95 characters are supported for full-width to half-width conversion. These 95 characters are:

(spacing)	！	"	#	$	%	&	‘	（	）
*	+	，	－	．	/	：	；	<	=
>	？	@	[	\	]	^	_	‘	{
｜	}	～	A-Z	a-z	0-9

Note:

• Only VARCHAR, CHAR, and TEXT column types support full-width characters, and the TO_SINGLE_BYTE conversion is successful for these types.
• LONGBLOB and BLOB can store full-width characters but are stored in binary format. After TO_SINGLE_BYTE conversion, they remain full-width characters.
• BLOB type, after TO_SINGLE_BYTE conversion, becomes VARBINARY.

Example:

create table t(a int, b varchar(10), c datetime, t text, e longblob, f blob, g char(10));
gbase> insert into t values(1, 'aaaaaａ', '2011-01-01 11:11:11', 'ａａａａ', 'ａａａａ', 'ａａａａ', 'ａａａａ');  
Query OK, 1 row affected (Elapsed: 00:00:00.05)
gbase> select to_single_byte(a) as sing_a,to_single_byte(b) as sing_b, to_single_byte(c) as sing_c, to_single_byte(t) as sing_t, to_single_byte(e) as sing_e, to_single_byte(f) as sing_f, to_single_byte(g) as sing_g from t; 
+--------+--------+---------------------+--------+--------------+--------------+------------+
| sing_a | sing_b | sing_c              | sing_t | sing_e       | sing_f       | sing_g     |
+--------+--------+---------------------+--------+--------------+--------------+------------+
| 1      | aaaaaa | 2011-01-01 11:11:11 | aaaa   | ａａａａ     | ａａａａ     | aaaa       |
+--------+--------+---------------------+--------+--------------+--------------+------------+
1 row in set (Elapsed: 00:00:00.03)
gbase> create table ty as select to_single_byte(a) as sing_a,to_single_byte(b) as sing_b, to_single_byte(c) as sing_c, to_single_byte(t) as sing_t, to_single_byte(e) as sing_e, to_single_byte(f) as sing_f, to_single_byte(g) as sing_g from t;
Query OK, 1 row affected (Elapsed: 00:00:00.11)
gbase>  show create table ty \G
*************************** 1. row ***************************
      Table: ty
Create Table: CREATE TABLE "ty" (
 "sing_a" varchar(11) DEFAULT NULL,
 "sing_b" varchar(10) DEFAULT NULL,
 "sing_c" varchar(26) DEFAULT NULL,
 "sing_t" varchar(10922) DEFAULT NULL,
 "sing_e" longblob,
 "sing_f" varbinary(32767) DEFAULT NULL,
 "sing_g" varchar(10) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace'
1 row in set (Elapsed: 00:00:00.00)
gbase> select * from ty;
+--------+--------+---------------------+--------+--------------+--------------+------------+
| sing_a | sing_b | sing_c              | sing_t | sing_e       | sing_f       | sing_g     |
+--------+--------+---------------------+--------+--------------+--------------+------------+
| 1      | aaaaaa | 2011-01-01 11:11:11 | aaaa   | ａａａａ     | ａａａａ     | aaaa       |
+--------+--------+---------------------+--------+--------------+--------------+------------+
1 row in set (Elapsed: 00:00:00.02)

4 Date Arithmetic Operations

Syntax: DATE +(-) bit_expr

Equivalent to: DATE +(-) INTERVAL expr TYPE

Operation Description:

Date addition and subtraction work similarly to regular arithmetic operations, but the unit added or subtracted is days. This syntax adds (or subtracts) the specified number of days from date, datetime, or timestamp variables.

Example:

Example 1: CAST('2019-06-18' AS DATE) + 30 returns the date 30 days later.

SELECT CAST('2019-06-18' AS DATE) + 30 FROM dual;
+----------------------------------+
| CAST('2019-06-18' AS DATE) + 30 |
+----------------------------------+
| 2019-07-18                       |
+----------------------------------+
1 row in set