Introduction to GBase 8c B Compatibility Library (2)
Tags: database
Published: 2024-07-12 07:52:17

With the support of the Dolphin plugin, the GBase 8c B Compatibility Database (dbcompatibility='B', hereafter referred to as the B compatibility library) has greatly enhanced its compatibility with MySQL in terms of data types. Here is a look at the common data types:

## Numerical Types

Compared to the native GBase 8c syntax, Dolphin makes several modifications to numerical types:

1. **INT/TINYINT/SMALLINT/BIGINT**: 
   - Added support for optional modifiers (n), allowing the usage of TINYINT(n)/SMALLINT(n)/BIGINT(n). The modifier n has no practical significance and does not affect any behavior.

2. **MEDIUMINT(n)**: 
   - A new data type added as an alias for INT4. The modifier n has no practical effect. It occupies 4 bytes of storage space with a range of -2,147,483,648 to +2,147,483,647.

3. **FIXED[(p[,s])]**: 
   - Introduced as an alias for the NUMERIC type. Users can specify precision. Every four decimal digits occupy two bytes, plus an additional eight-byte overhead for the entire data. Without specified precision, it supports a maximum of 131,072 digits before the decimal point and 16,383 digits after.

4. **float4(p[,s])**: 
   - A new addition, equivalent to dec(p[,s]).

5. **double**: 
   - Introduced as an alias for float8.

6. **float4/float**: 
   - Added support for optional modifiers (n), allowing the usage of float4(n)/float(n). When n is between [1,24], it represents a single-precision floating point; when n is between [25,53], it represents a double-precision floating point.

7. **decimal/dec/fixed/numeric**: 
   - When precision is not specified, the default precision is (10,0), meaning 10 total digits with 0 decimal places.

8. **UNSIGNED INT/TINYINT/SMALLINT/BIGINT**: 
   - Compared to regular integers, the highest bit is a digit bit rather than a sign bit. Additionally, in GBase 8c, TINYINT is unsigned by default, whereas in the B library, it is signed by default.

9. **zerofill attribute modifier**: 
   - Only syntactically supported with no actual zero-filling effect. Equivalent to the UNSIGNED modifier.

10. **cast function type conversion**:
    - Added parameters signed/unsigned. `cast as unsigned` converts the type to uint8, and `cast as signed` converts the type to int8.

11. **float(p,s), double(p,s), real(p,s), double precision(p,s)**: 
    - These syntaxes are roughly equivalent to dec(p,s). Unlike dec(p,s), the p and s for float(p,s), real(p,s), and double precision(p,s) must be integers, while double(p,s) is entirely equivalent to dec(p,s). The rounding method used is round-half-up.

**Table 1 Integer Types**

| Name          | Description                                          | Storage Space | Range                           |
|---------------|------------------------------------------------------|---------------|---------------------------------|
| TINYINT(n)    | Tiny integer, alias for INT1. n has no practical effect. | 1 byte        | -128 to +127                    |
| SMALLINT(n)   | Small integer, alias for INT2. n has no practical effect. | 2 bytes       | -32768 to +32767                |
| INTEGER(n)    | Common integer, alias for INT4. n has no practical effect. | 4 bytes       | -2147483648 to +2147483647      |
| MEDIUMINT(n)  | Alias for INT4. n has no practical effect.           | 4 bytes       | -2147483648 to +2147483647      |
| BIGINT(n)     | Large integer, alias for INT8. n has no practical effect. | 8 bytes       | -9223372036854775808 to +9223372036854775807 |
| TINYINT(n) UNSIGNED | Unsigned tiny integer, alias for UINT1. n has no practical effect. | 1 byte        | 0 to 255                        |
| SMALLINT(n) UNSIGNED | Unsigned small integer, alias for UINT2. n has no practical effect. | 2 bytes       | 0 to +65535                     |

## Examples

**1. Creating a table with TINYINT(n), SMALLINT(n), MEDIUMINT(n), BIGINT(n) data types**

```sql
CREATE TABLE int_type_t1
(
  IT_COL1 TINYINT(10),
  IT_COL2 SMALLINT(20),
  IT_COL3 MEDIUMINT(30),
  IT_COL4 BIGINT(40),
  IT_COL5 INTEGER(50)
);
```

**2. Viewing the table structure**

```sql
\d int_type_t1
```

**Result:**

```plaintext
Table"public.int_type_t1"
Column|Type|Modifiers
---------+----------+-----------
IT_COL1|tinyint|
IT_COL2|smallint|
IT_COL3|integer|
IT_COL4|bigint|
IT_COL5|integer|
```

**3. Creating a table with zerofill attribute fields**

```sql
CREATE TABLE int_type_t2
(
  IT_COL1 TINYINT(10) zerofill,
  IT_COL2 SMALLINT(20) unsigned zerofill,
  IT_COL3 MEDIUMINT(30) unsigned,
  IT_COL4 BIGINT(40) zerofill,
  IT_COL5 INTEGER(50) zerofill
);
```

**4. Viewing the table structure**

```sql
\d int_type_t2
```

**Result:**

```plaintext
Table"public.int_type_t2"
Column|Type|Modifiers
---------+-------+-----------
IT_COL1|uint1|
IT_COL2|uint2|
IT_COL3|uint4|
IT_COL4|uint8|
IT_COL5|uint4|
```

**5. Using cast unsigned to convert an expression to uint8 type**

```sql
SELECT CAST(1-2 AS unsigned);
```

**Result:**

```plaintext
uint8
----------------------
18446744073709551615
(1row)
```

**6. Using cast signed to convert an expression to int8 type**

```sql
SELECT CAST(1-2 AS signed);
```

**Result:**

```plaintext
int8
------
-1
(1row)
```

**Table 2: Arbitrary Precision Types**

| Name                | Description                                                                                      | Storage Space                                                                                             | Range                                                                                         |
|---------------------|--------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------|
| DECIMAL[(p[,s])] <br><br> FIXED[(p[,s])] <br><br> FIXED[(p[,s])]| Precision p ranges from [1,1000], scale s ranges from [0,p]. Note: p is the total number of digits, s is the number of decimal places. | User-defined precision. Each four decimal digits occupy two bytes, plus an additional eight-byte overhead. | If precision is not specified, it defaults to (10,0), meaning a maximum of 10 digits before the decimal point and 0 digits after. |
| NUMBER[(p[,s])]     | Alias for NUMERIC type.                                                                          | User-defined precision. Each four decimal digits occupy two bytes, plus an additional eight-byte overhead. | If precision is not specified, it allows up to 131,072 digits before the decimal point and up to 16,383 digits after.        |

## Examples

**1. Create a table with columns of types FIXED(p,s), FIXED, DECIMAL, and NUMBER.**

```sql
CREATE TABLE dec_type_t1
(
    DEC_COL1 FIXED,
    DEC_COL2 FIXED(20,5),
    DEC_COL3 DECIMAL,
    DEC_COL4 NUMBER
);
```

**2. View the table structure.**

```sql
\d dec_type_t1
```

The result is:

```plaintext
Table "public.dec_type_t1"
 Column  |      Type      | Modifiers
---------+----------------+-----------
 DEC_COL1 | numeric(10,0)  |
 DEC_COL2 | numeric(20,5)  |
 DEC_COL3 | numeric(10,0)  |
 DEC_COL4 | numeric        |
```

**Table 3 Floating Point Types**

| Name                                                                                          | Description                                                                                                                                                                                                                       | Storage Space                       | Range                                                                                                                                                                                |
|-----------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| FLOAT[(p)] <br> FLOAT4[(p)]                                                                   | Floating point, not precise. Precision p ranges from [1,53].                                                                                                                                | 4 bytes or 8 bytes                  | When p is between [1,24], REAL is used internally. When p is between [25,53], DOUBLE PRECISION is used internally. If precision is not specified, REAL is used internally.             |
| DOUBLE PRECISION <br> FLOAT8 <br> DOUBLE                                                      | Double precision floating point, not precise.                                                                                                                                                                                     | 8 bytes                             | -79E+308~79E+308, with 15 decimal digits precision.                                                                                                                                  |
| FLOAT4(p,s)                                                                                   | Precision p ranges from [1,1000], scale s ranges from [0,p].<br><br> Note: p is the total number of digits, s is the number of decimal places. Equivalent to dec(p,s).                                                            | User-declared precision. Each four decimal digits occupy two bytes, plus an additional overhead of eight bytes for the entire data. | -                                                                                                                                                                                   |
| FLOAT(p,s) <br> DOUBLE(p,s) <br> REAL(p,s) <br> DOUBLE PRECISION(p,s)                         | Precision p ranges from [1,1000], scale s ranges from [0,p].<br><br> Note: p is the total number of digits, s is the number of decimal places. FLOAT(p,s), REAL(p,s), and DOUBLE PRECISION(p,s) are roughly equivalent to dec(p,s). p and s must be integers, while DOUBLE(p,s) is completely equivalent to dec(p,s). Rounding mode is round half up. | User-declared precision. Each four decimal digits occupy two bytes, plus an additional overhead of eight bytes for the entire data. |                                                              |

## Character Types

Compared to the native GBase8c syntax, dolphin has made the following modifications to character types:

1. Modified the meaning of n in CHARACTER/NCHAR types, where n indicates the character length instead of the byte length.

2. All character data types ignore trailing spaces when compared, such as in `WHERE` clause filtering or `JOIN` conditions. For example, `'a'::text = 'a'::text` evaluates to true. Note that for VARCHAR, VARCHAR2, NVARCHAR, TEXT, and CLOB types, trailing spaces are ignored in hash joins and hash aggregates only when the GUC parameter `string_hash_compatible` is set to `on`.

3. Added `NATIONAL VARCHAR(n)`, an alias for the `NVARCHAR2(n)` type, where n indicates the character length.

4. Added support for an optional modifier (n) in `TEXT`, allowing the usage of `TEXT(n)`. The n has no practical significance and does not affect any behavior.

5. Added `TINYTEXT(n)`, `MEDIUMTEXT(n)`, and `LONGTEXT(n)` data types, which are aliases for `TEXT`. The n has no practical significance and does not affect any behavior.

**Table 4 Character Types**

| Name                                                                                              | Description                                                                                          | Storage Space                        |
|---------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------|--------------------------------------|
| CHAR(n) <br> CHARACTER(n) <br> NCHAR(n)                                                           | Fixed-length string, padded with spaces if necessary. n specifies the character length. If n is not specified, the default length is 1. | Up to 10MB.                          |
| NATIONAL <br> VARCHAR(n)                                                                          | Variable-length string. Alias for NVARCHAR2(n) type. n specifies the character length.               | Up to 10MB.                          |
| TEXT(n) <br> TINYTEXT(n) <br> MEDIUMTEXT(n) <br> LONGTEXT(n)                                      | Variable-length string. n has no practical meaning and does not affect any behavior.                 | Up to 1GB-1, but considering column descriptor header size and tuple size limitations, the maximum size may be less than 1GB-1. |

## Examples

**1. Create a test table and insert data**

```sql
CREATE TABLE char_type_t1
(
    CT_COL1 CHARACTER(4),
    CT_COL2 TEXT(10),
    CT_COL3 TINYTEXT(11),
    CT_COL4 MEDIUMTEXT(12),
    CT_COL5 LONGTEXT(13)
);
```

**2. Check the table structure**

```sql
\d char_type_t1
```

Result:

```
Table "public.char_type_t1"
Column  | Type         | Modifiers
--------+--------------+-----------
CT_COL1 | character(4) |
CT_COL2 | text         |
CT_COL3 | text         |
CT_COL4 | text         |
CT_COL5 | text         |
```

**3. Insert data into the table**

```sql
INSERT INTO char_type_t1 VALUES ('4 characters');
```

**4. Query the data**

```sql
SELECT CT_COL1, length(CT_COL1) FROM char_type_t1;
```

Result:

```
CT_COL1       | length
--------------+--------
4 characters  | 12
(1 row)
```