[feature-wip][array-type] Support more sub types.

[adonis0147]

Search before asking

• I had searched in the issues and found no similar issues.

Description

This issue is a sub task of #7570 .

Support more sub types:

1. Array<Boolean>
2. Array<TinyInt>
3. Array<SmallInt>
4. Array<Int>
5. Array<BigInt>
6. Array<LargeInt>
7. Array<Char>
8. Array<VarChar>
9. Array<String>
10. Array<Decimal>
11. Array<Date>
12. Array<DateTime>

Use case

# boolean
mysql> create table boolean_table (`key` int, value array<boolean>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into boolean_table values (0, null), (1, []), (2, [true, false]), (3, [null, false, null]);
mysql> select * from boolean_table;
+------+-----------------+
| key  | value           |
+------+-----------------+
|    1 | []              |
|    0 | NULL            |
|    3 | [NULL, 0, NULL] |
|    2 | [1, 0]          |
+------+-----------------+
4 rows in set (0.06 sec)

# tinyint
mysql> create table tinyint_table (`key` int, value array<tinyint>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into tinyint_table values (0, null), (1, []), (2, [1, 2]), (3, [null, 2, null]);
mysql> select * from tinyint_table;
+------+-----------------+
| key  | value           |
+------+-----------------+
|    1 | []              |
|    0 | NULL            |
|    2 | [1, 2]          |
|    3 | [NULL, 2, NULL] |
+------+-----------------+
4 rows in set (0.01 sec)

# smallint
mysql> create table smallint_table (`key` int, value array<smallint>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into smallint_table values (0, null), (1, []), (2, [1, 2]), (3, [null, 2, null]);
mysql> select * from smallint_table;
+------+-----------------+
| key  | value           |
+------+-----------------+
|    2 | [1, 2]          |
|    3 | [NULL, 2, NULL] |
|    0 | NULL            |
|    1 | []              |
+------+-----------------+
4 rows in set (0.01 sec)

# int
mysql> create table int_table (`key` int, value array<int>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into int_table values (0, null), (1, []), (2, [1, 2]), (3, [null, 2, null]);
mysql> select * from int_table;
+------+-----------------+
| key  | value           |
+------+-----------------+
|    3 | [NULL, 2, NULL] |
|    1 | []              |
|    0 | NULL            |
|    2 | [1, 2]          |
+------+-----------------+
4 rows in set (0.02 sec)

# bigint
mysql> create table bigint_table (`key` int, value array<bigint>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into bigint_table values (0, null), (1, []), (2, [1, 2]), (3, [null, 2, null]);
mysql> select * from bigint_table;
+------+-----------------+
| key  | value           |
+------+-----------------+
|    1 | []              |
|    0 | NULL            |
|    3 | [NULL, 2, NULL] |
|    2 | [1, 2]          |
+------+-----------------+
4 rows in set (0.02 sec)

# largeint
mysql> create table largeint_table (`key` int, value array<largeint>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into largeint_table values (0, null), (1, []), (2, [1, 2]), (3, [null, 2, null]);
mysql> select * from largeint_table;
+------+-----------------+
| key  | value           |
+------+-----------------+
|    1 | []              |
|    3 | [NULL, 2, NULL] |
|    2 | [1, 2]          |
|    0 | NULL            |
+------+-----------------+
4 rows in set (0.01 sec)

# float
mysql> create table float_table (`key` int, value array<float>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into float_table values (0, null), (1, []), (2, [1.5, 2.5]), (3, [null, 2.5, null]);
mysql> select * from float_table;
+------+-------------------+
| key  | value             |
+------+-------------------+
|    1 | []                |
|    2 | [1.5, 2.5]        |
|    3 | [NULL, 2.5, NULL] |
|    0 | NULL              |
+------+-------------------+
4 rows in set (0.02 sec)

# double
mysql> create table double_table (`key` int, value array<double>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into double_table values (0, null), (1, []), (2, [1.5, 2.5]), (3, [null, 2.5, null]);
mysql> select * from double_table;
+------+-------------------+
| key  | value             |
+------+-------------------+
|    1 | []                |
|    0 | NULL              |
|    3 | [NULL, 2.5, NULL] |
|    2 | [1.5, 2.5]        |
+------+-------------------+
4 rows in set (0.02 sec)

# char
mysql> create table char_table (`key` int, value array<char(10)>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into char_table values (0, null), (1, []), (2, ['1', '2']), (3, [null, '2', null]);
mysql> select * from char_table;
+------+-------------------+
| key  | value             |
+------+-------------------+
|    1 | []                |
|    0 | NULL              |
|    3 | [NULL, '2', NULL] |
|    2 | ['1', '2']        |
+------+-------------------+
4 rows in set (0.02 sec)

# varchar
mysql> create table varchar_table (`key` int, value array<varchar>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into varchar_table values (0, null), (1, []), (2, ['1', '2']), (3, [null, '2', null]);
mysql> select * from varchar_table;
+------+-------------------+
| key  | value             |
+------+-------------------+
|    1 | []                |
|    0 | NULL              |
|    2 | ['1', '2']        |
|    3 | [NULL, '2', NULL] |
+------+-------------------+
4 rows in set (0.01 sec)

# string
mysql> create table string_table (`key` int, value array<string>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into string_table values (0, null), (1, []), (2, ['1', '2']), (3, [null, '2', null]);
mysql> select * from string_table;
+------+-------------------+
| key  | value             |
+------+-------------------+
|    1 | []                |
|    0 | NULL              |
|    2 | ['1', '2']        |
|    3 | [NULL, '2', NULL] |
+------+-------------------+
4 rows in set (0.02 sec)

# decimal
mysql> create table decimal_table (`key` int, value array<decimal(27,9)>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into decimal_table values (0, null), (1, []), (2, [1.25, 2.25]), (3, [null, 2.25, null]);
mysql> select * from decimal_table;
+------+--------------------+
| key  | value              |
+------+--------------------+
|    0 | NULL               |
|    3 | [NULL, 2.25, NULL] |
|    2 | [1.25, 2.25]       |
|    1 | []                 |
+------+--------------------+
4 rows in set (0.02 sec)

# date
mysql> create table date_table (`key` int, value array<date>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into date_table values (0, null), (1, []), (2, ['2022-05-09', '2022-05-10']), (3, [null, '2022-05-10', null]);
mysql> select * from date_table;
+------+--------------------------+
| key  | value                    |
+------+--------------------------+
|    0 | NULL                     |
|    1 | []                       |
|    2 | [2022-05-09, 2022-05-10] |
|    3 | [NULL, 2022-05-10, NULL] |
+------+--------------------------+
4 rows in set (0.02 sec)

# datetime
mysql> create table datetime_table (`key` int, value array<datetime>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into datetime_table values (0, null), (1, []), (2, ['2022-05-09 14:00:00', '2022-05-10 14:30:00']), (3, [null, '2022-05-10 14:30:00', null]);
mysql> select * from datetime_table;
+------+--------------------------------------------+
| key  | value                                      |
+------+--------------------------------------------+
|    1 | []                                         |
|    0 | NULL                                       |
|    2 | [2022-05-09 14:00:00, 2022-05-10 14:30:00] |
|    3 | [NULL, 2022-05-10 14:30:00, NULL]          |
+------+--------------------------------------------+
4 rows in set (0.02 sec)

# nested array
mysql> create table nested_table (`key` int, value array<array<int>>) duplicate key (`key`) 
    -> distributed by hash (`key`) properties ('replication_num' = '1');
mysql> insert into nested_table values (0, null), (1, []), (2, [null]), (3, [[]]), (4, [[1, 2, 3]]), 
    -> (5, [null, [4, 5, 6], null]), (6, [[1, 2, 3], [4, 5, 6], [7, 8, 9]]);
mysql> select * from nested_table;
+------+-----------------------------------+
| key  | value                             |
+------+-----------------------------------+
|    4 | [[1, 2, 3]]                       |
|    5 | [NULL, [4, 5, 6], NULL]           |
|    1 | []                                |
|    0 | NULL                              |
|    2 | [NULL]                            |
|    3 | [[]]                              |
|    6 | [[1, 2, 3], [4, 5, 6], [7, 8, 9]] |
+------+-----------------------------------+
7 rows in set (0.02 sec)

Related issues

#7570

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct