This is an automated email from the ASF dual-hosted git repository.
eldenmoon pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new aa9b1dc0436 [chore](case) add cases for complex type doc (#53126)
aa9b1dc0436 is described below
commit aa9b1dc04368c267804b50f92239b5cbe4c5f35c
Author: Sun Chenyang <[email protected]>
AuthorDate: Wed Jul 16 16:27:42 2025 +0800
[chore](case) add cases for complex type doc (#53126)
---
.../basic-elements/data-types/array-md.out | Bin 0 -> 1835 bytes
.../basic-elements/data-types/map-md.out | Bin 0 -> 1930 bytes
.../basic-elements/data-types/struct-md.out | Bin 0 -> 1724 bytes
.../basic-elements/data-types/array-md.groovy | 713 +++++++++++++++++++++
.../basic-elements/data-types/map-md.groovy | 473 ++++++++++++++
.../basic-elements/data-types/struct-md.groovy | 527 +++++++++++++++
6 files changed, 1713 insertions(+)
diff --git
a/regression-test/data/doc/sql-manual/basic-elements/data-types/array-md.out
b/regression-test/data/doc/sql-manual/basic-elements/data-types/array-md.out
new file mode 100644
index 00000000000..1c16f081b33
Binary files /dev/null and
b/regression-test/data/doc/sql-manual/basic-elements/data-types/array-md.out
differ
diff --git
a/regression-test/data/doc/sql-manual/basic-elements/data-types/map-md.out
b/regression-test/data/doc/sql-manual/basic-elements/data-types/map-md.out
new file mode 100644
index 00000000000..b107c606b2a
Binary files /dev/null and
b/regression-test/data/doc/sql-manual/basic-elements/data-types/map-md.out
differ
diff --git
a/regression-test/data/doc/sql-manual/basic-elements/data-types/struct-md.out
b/regression-test/data/doc/sql-manual/basic-elements/data-types/struct-md.out
new file mode 100644
index 00000000000..13a3459bd80
Binary files /dev/null and
b/regression-test/data/doc/sql-manual/basic-elements/data-types/struct-md.out
differ
diff --git
a/regression-test/suites/doc/sql-manual/basic-elements/data-types/array-md.groovy
b/regression-test/suites/doc/sql-manual/basic-elements/data-types/array-md.groovy
new file mode 100644
index 00000000000..836adbec972
--- /dev/null
+++
b/regression-test/suites/doc/sql-manual/basic-elements/data-types/array-md.groovy
@@ -0,0 +1,713 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("array-md", "p0") {
+
+ def tableName = "array_table"
+ sql """
+ drop table if exists ${tableName};
+ """
+ sql """ set enable_decimal256 = true; """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_boolean ARRAY<BOOLEAN>,
+ array_tinyint ARRAY<TINYINT>,
+ array_smallint ARRAY<SMALLINT>,
+ array_int ARRAY<INT>,
+ array_bigint ARRAY<BIGINT>,
+ array_largeint ARRAY<LARGEINT>,
+ array_float ARRAY<FLOAT>,
+ array_double ARRAY<DOUBLE>,
+ array_decimal32 ARRAY<DECIMAL(7, 2)>,
+ array_decimal64 ARRAY<DECIMAL(15, 3)>,
+ array_decimal128 ARRAY<DECIMAL(25, 5)>,
+ array_decimal256 ARRAY<DECIMAL(40, 7)>,
+ array_string ARRAY<STRING>,
+ array_varchar ARRAY<VARCHAR(10)>,
+ array_char ARRAY<CHAR(10)>,
+ array_date ARRAY<DATE>,
+ array_datetime ARRAY<DATETIME>,
+ array_ipv4 ARRAY<IPV4>,
+ array_ipv6 ARRAY<IPV6>,
+ array_struct ARRAY<STRUCT<id: INT, name: STRING>>,
+ array_array ARRAY<ARRAY<INT>>,
+ array_map ARRAY<MAP<STRING, INT>>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """
+ INSERT INTO ${tableName} (id, array_boolean, array_tinyint,
array_smallint, array_int, array_bigint, array_largeint, array_float,
array_double, array_decimal32, array_decimal64, array_decimal128,
array_decimal256, array_string, array_varchar, array_char, array_date,
array_datetime, array_ipv4, array_ipv6, array_struct, array_array, array_map)
VALUES
+ (1, [true, false, true], [1, 2, 3], [1, 2, 3], [1, 2, 3], [1, 2, 3],
[1, 2, 3], [1.1, 2.2, 3.3], [1.1, 2.2, 3.3], [1.1, 2.2, 3.3], [1.1, 2.2, 3.3],
[1.1, 2.2, 3.3], [1.1, 2.2, 3.3], ['hello', 'world'], ['hello', 'world'],
['hello', 'world'], ['2021-01-01', '2021-01-02', '2021-01-03'], ['2021-01-01
00:00:00', '2021-01-02 00:00:00', '2021-01-03 00:00:00'], ['192.168.1.1',
'192.168.1.2', '192.168.1.3'], ['::1', '::2', '::3'], ARRAY(STRUCT(1, 'John'),
STRUCT(2, 'Jane')), [[1, 2, 3], [...]
+ """
+
+ qt_sql """
+ SELECT * FROM ${tableName};
+ """
+
+ sql """DROP TABLE IF EXISTS ${tableName};"""
+ test {
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_nested
ARRAY<ARRAY<ARRAY<ARRAY<ARRAY<ARRAY<ARRAY<ARRAY<ARRAY<ARRAY<ARRAY<ARRAY<INT>>>>>>>>>>>>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Type exceeds the maximum nesting depth of 9"
+ }
+
+ test {
+ sql """ SELECT CAST(ARRAY(1, 2, 3) AS STRING) """
+ exception "can not cast from origin type ARRAY<TINYINT> to target
type=TEXT"
+ }
+
+ qt_sql """ SELECT CAST("[1,2,3]" AS ARRAY<INT>) """
+
+ qt_sql """ SELECT CAST([1,2,3] AS ARRAY<STRING>) """
+
+ sql """ DROP TABLE IF EXISTS varaint_table; """
+ sql """
+ CREATE TABLE IF NOT EXISTS varaint_table (
+ id INT,
+ var VARIANT
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """ INSERT INTO varaint_table (id, var) VALUES (1, '[1,2,3]') """
+
+ qt_sql """ SELECT cast(var as ARRAY<INT>) FROM varaint_table; """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_int ARRAY<INT> REPLACE_IF_NOT_NULL,
+ array_string ARRAY<STRING> REPLACE,
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_int ARRAY<INT> SUM
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type SUM is not compatible with primitive type
ARRAY<INT>"
+ }
+
+ test{
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_int ARRAY<INT> MIN
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type MIN is not compatible with primitive type
ARRAY<INT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_int ARRAY<INT> MAX
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type MAX is not compatible with primitive type
ARRAY<INT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_int ARRAY<INT> HLL_UNION
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type HLL_UNION is not compatible with primitive
type ARRAY<INT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_int ARRAY<INT> BITMAP_UNION
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type BITMAP_UNION is not compatible with
primitive type ARRAY<INT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_int ARRAY<INT> QUANTILE_UNION
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type QUANTILE_UNION is not compatible with
primitive type ARRAY<INT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ array_int ARRAY<INT>,
+ array_value ARRAY<STRING>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(array_int)
+ DISTRIBUTED BY HASH(array_int) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Array can only be used in the non-key column of the
duplicate table at present"
+ }
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_int ARRAY<INT>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_int) VALUES (1, [1, 2, 3]),
(2, [1, 2, 3]) """
+
+ qt_sql """ select array_int, count(*) from ${tableName} group by array_int
order by array_int """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_int ARRAY<ARRAY<INT>>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_int) VALUES (1, [[1, 2, 3],
[1, 2, 3]]), (2, [[1, 2, 3], [2, 2, 3]]) """
+
+ test {
+ sql """ select array_int, count(*) from ${tableName} group by
array_int order by array_int """
+ exception "Doris hll, bitmap, array, map, struct, jsonb, variant
column must use with specific function, and don't support filter, group by or
order by. please run 'help hll' or 'help bitmap' or 'help array' or 'help map'
or 'help struct' or 'help jsonb' or 'help variant' in your mysql client"
+ }
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_struct ARRAY<STRUCT<id: INT, name: STRING>>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_struct) VALUES (1,
ARRAY(STRUCT(1, 'John'), STRUCT(2, 'Jane'))), (2, ARRAY(STRUCT(1, 'John'),
STRUCT(2, 'Jane'))) """
+
+ test {
+ sql """ select array_struct, count(*) from ${tableName} group by
array_struct order by array_struct """
+ exception "Doris hll, bitmap, array, map, struct, jsonb, variant
column must use with specific function, and don't support filter, group by or
order by. please run 'help hll' or 'help bitmap' or 'help array' or 'help map'
or 'help struct' or 'help jsonb' or 'help variant' in your mysql client"
+ }
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_map ARRAY<MAP<STRING, INT>>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_map) VALUES (1,
ARRAY(MAP('key1', 1), MAP('key2', 2))), (2, ARRAY(MAP('key1', 1), MAP('key2',
2))) """
+
+ test {
+ sql """ select array_map, count(*) from ${tableName} group by
array_map order by array_map """
+ exception "Doris hll, bitmap, array, map, struct, jsonb, variant
column must use with specific function, and don't support filter, group by or
order by. please run 'help hll' or 'help bitmap' or 'help array' or 'help map'
or 'help struct' or 'help jsonb' or 'help variant' in your mysql client"
+ }
+
+ sql """ DROP TABLE IF EXISTS array_table_1; """
+ sql """
+ CREATE TABLE IF NOT EXISTS array_table_1 (
+ id INT,
+ array_int ARRAY<INT>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO array_table_1 (id, array_int) VALUES (1, [1, 2, 3]),
(2, [1, 2, 3]) """
+
+ sql """ DROP TABLE IF EXISTS array_table_2; """
+ sql """
+ CREATE TABLE IF NOT EXISTS array_table_2 (
+ id INT,
+ array_int ARRAY<INT>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO array_table_2 (id, array_int) VALUES (1, [1, 2, 3]),
(2, [1, 2, 3]) """
+ qt_sql """ select * from array_table_1 join array_table_2 on
array_contains(array_table_1.array_int,array_table_2.id) order by
array_table_1.id, array_table_2.id """
+
+ test {
+ sql """ select * from array_table_1 join array_table_2 on
array_table_1.array_int = array_table_2.array_int """
+ exception""
+ }
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_int ARRAY<INT>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_int) VALUES (1, [1, 2, 3]),
(2, [1, 2, 4]), (3, [1, 2, 5]) """
+
+ qt_sql """ select array_int, count(*) from ${tableName} group by array_int
order by array_int """
+
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_string ARRAY<STRING>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_string) VALUES (1, ['hello',
'world']), (2, ['hello', 'world']), (3, ['hello', 'world']) """
+
+ qt_sql """ select array_string, count(*) from ${tableName} group by
array_string order by array_string """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_float ARRAY<FLOAT>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_float) VALUES (1, [1.1, 2.2,
3.3]), (2, [1.1, 2.2, 3.3]) """
+
+ qt_sql """ select array_float, count(*) from ${tableName} group by
array_float order by array_float """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_double ARRAY<DOUBLE>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_double) VALUES (1, [1.1, 2.2,
3.3]), (2, [1.1, 2.2, 3.3]) """
+
+ qt_sql """ select array_double, count(*) from ${tableName} group by
array_double order by array_double """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_decimal32 ARRAY<DECIMAL(7, 2)>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_decimal32) VALUES (1, [1.1,
2.2, 3.3]), (2, [1.1, 2.2, 3.3]) """
+
+ qt_sql """ select array_decimal32, count(*) from ${tableName} group by
array_decimal32 order by array_decimal32 """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_decimal64 ARRAY<DECIMAL(15, 3)>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_decimal64) VALUES (1, [1.1,
2.2, 3.3]), (2, [1.1, 2.2, 3.3]) """
+
+ qt_sql """ select array_decimal64, count(*) from ${tableName} group by
array_decimal64 order by array_decimal64 """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_decimal128 ARRAY<DECIMAL(25, 5)>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_decimal128) VALUES (1, [1.1,
2.2, 3.3]), (2, [1.1, 2.2, 3.3]) """
+
+ qt_sql """ select array_decimal128, count(*) from ${tableName} group by
array_decimal128 order by array_decimal128 """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_decimal256 ARRAY<DECIMAL(40, 8)>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_decimal256) VALUES (1, [1.1,
2.2, 3.3]), (2, [1.1, 2.2, 3.3]) """
+
+ qt_sql """ select array_decimal256, count(*) from ${tableName} group by
array_decimal256 order by array_decimal256 """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_date ARRAY<DATE>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_date) VALUES (1,
['2021-01-01', '2021-01-02', '2021-01-03']), (2, ['2021-01-01', '2021-01-02',
'2021-01-03']) """
+
+ qt_sql """ select array_date, count(*) from ${tableName} group by
array_date order by array_date """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_datetime ARRAY<DATETIME>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_datetime) VALUES (1,
['2021-01-01 00:00:00', '2021-01-02 00:00:00', '2021-01-03 00:00:00']), (2,
['2021-01-01 00:00:00', '2021-01-02 00:00:00', '2021-01-03 00:00:00']) """
+
+ qt_sql """ select array_datetime, count(*) from ${tableName} group by
array_datetime order by array_datetime """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_ipv4 ARRAY<IPV4>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_ipv4) VALUES (1,
['192.168.1.1', '192.168.1.2', '192.168.1.3']), (2, ['192.168.1.1',
'192.168.1.2', '192.168.1.3']) """
+
+ qt_sql """ select array_ipv4, count(*) from ${tableName} group by
array_ipv4 order by array_ipv4 """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_ipv6 ARRAY<IPV6>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_ipv6) VALUES (1,
['2001:0db8:85a3:0000:0000:8a2e:0370:7334',
'2001:0db8:85a3:0000:0000:8a2e:0370:7334']), (2,
['2001:0db8:85a3:0000:0000:8a2e:0370:7334',
'2001:0db8:85a3:0000:0000:8a2e:0370:7334']) """
+
+ qt_sql """ select array_ipv6, count(*) from ${tableName} group by
array_ipv6 order by array_ipv6 """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ str STRING
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """ INSERT INTO ${tableName} (id, str) VALUES (1, 'hello'), (2,
'world'), (3, 'hello') """
+
+ qt_sql """ SELECT ARRAY(str, id) FROM ${tableName} """
+
+ test {
+ sql """ SELECT [str, id] FROM ${tableName} """
+ exception "errCode = 2"
+ }
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_varchar ARRAY<VARCHAR(10)>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, array_varchar) VALUES (1, ['hello',
'world']), (2, ['hello', 'world']), (3, ['hello', 'world']) """
+ test {
+ sql """ ALTER TABLE ${tableName} MODIFY COLUMN array_varchar
ARRAY<VARCHAR(10)> DEFAULT NULL """
+ exception "Nothing is changed. please check your alter stmt"
+ }
+
+ test {
+ sql """ ALTER TABLE ${tableName} MODIFY COLUMN array_varchar
ARRAY<VARCHAR(9)> """
+ exception "Shorten type length is prohibited, srcType=varchar(10),
dstType=varchar(9)"
+ }
+
+ sql """ ALTER TABLE ${tableName} MODIFY COLUMN array_varchar
ARRAY<VARCHAR(20)> """
+
+
+ qt_sql """ select array_varchar[0], array_varchar[1], array_varchar[2],
array_varchar[3] from ${tableName} """
+ qt_sql """ select element_at(array_varchar, 0), element_at(array_varchar,
1), element_at(array_varchar, 2), element_at(array_varchar, 3) from
${tableName} """
+
+ sql """ DROP TABLE IF EXISTS array_table; """
+ sql """
+ CREATE TABLE `array_table` (
+ `k` int NOT NULL,
+ `array_column` array<STRING>,
+ INDEX idx_array_column (array_column) USING INVERTED
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k`)
+ DISTRIBUTED BY HASH(`k`) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+ sql """ insert into array_table values (1, ['ab', 'cd', 'ef']), (2, ['gh',
'ij', 'kl']), (3, ['mn', 'op', 'qr']); """
+
+ profile("test_profile_1") {
+ sql """ set enable_common_expr_pushdown = true; """
+ sql """ set enable_common_expr_pushdown_for_inverted_index = true; """
+ sql """ set enable_pipeline_x_engine = true;"""
+ sql """ set enable_profile = true;"""
+ sql """ set profile_level = 2;"""
+ run {
+ qt_sql_inv """/* test_profile_1 */
+ select count() from array_table where
ARRAY_CONTAINS(array_column, 'ef');
+ """
+ }
+
+ check { profileString, exception ->
+ log.info(profileString)
+ assertTrue(profileString.contains("RowsInvertedIndexFiltered: 2"))
+ }
+ }
+
+ profile("test_profile_2") {
+ sql """ set enable_common_expr_pushdown = true; """
+ sql """ set enable_common_expr_pushdown_for_inverted_index = true; """
+ sql """ set enable_pipeline_x_engine = true;"""
+ sql """ set enable_profile = true;"""
+ sql """ set profile_level = 2;"""
+ run {
+ qt_sql """/* test_profile_2 */
+ select count() from array_table where
ARRAYS_OVERLAP(array_column, ['ab', 'op']);
+ """
+ }
+
+ check { profileString, exception ->
+ log.info(profileString)
+ assertTrue(profileString.contains("RowsInvertedIndexFiltered: 1"))
+ }
+ }
+
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_int ARRAY<INT>,
+ INDEX idx_array_int (array_int) USING INVERTED
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql """ insert into ${tableName} values (1, [1, 2, 3]), (2, [4, 5, 6]),
(3, [7, 8, 9]); """
+
+ test {
+ sql """ delete from ${tableName} where array_contains(array_int, 5);
"""
+ exception "Can not apply delete condition to column type: array<int>"
+ }
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ two_dim_array ARRAY<ARRAY<INT>>,
+ three_dim_array ARRAY<ARRAY<ARRAY<STRING>>>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES (1, [[1, 2, 3], [4, 5, 6]],
[[['ab', 'cd', 'ef'], ['gh', 'ij', 'kl']], [['mn', 'op', 'qr'], ['st', 'uv',
'wx']]]) """
+ sql """ INSERT INTO ${tableName} VALUES (2, ARRAY(ARRAY(1, 2, 3), ARRAY(4,
5, 6)), ARRAY(ARRAY(ARRAY('ab', 'cd', 'ef'), ARRAY('gh', 'ij', 'kl')),
ARRAY(ARRAY('mn', 'op', 'qr'), ARRAY('st', 'uv', 'wx')))) """
+
+ qt_sql """ SELECT two_dim_array[1][2], three_dim_array[1][1][2] FROM
${tableName} ORDER BY id """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_map ARRAY<MAP<STRING, INT>>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} VALUES (1, ARRAY(MAP('key1', 1),
MAP('key2', 2))) """
+ sql """ INSERT INTO ${tableName} VALUES (2, ARRAY(MAP('key1', 1),
MAP('key2', 2))) """
+
+ qt_sql """ SELECT array_map[1], array_map[2] FROM ${tableName} ORDER BY id
"""
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ array_struct ARRAY<STRUCT<id: INT, name: STRING>>,
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} VALUES (1, ARRAY(STRUCT(1, 'John'),
STRUCT(2, 'Jane'))) """
+ sql """ INSERT INTO ${tableName} VALUES (2, ARRAY(STRUCT(1, 'John'),
STRUCT(2, 'Jane'))) """
+
+ qt_sql """ SELECT array_struct[1], array_struct[2] FROM ${tableName} ORDER
BY id """
+
+}
\ No newline at end of file
diff --git
a/regression-test/suites/doc/sql-manual/basic-elements/data-types/map-md.groovy
b/regression-test/suites/doc/sql-manual/basic-elements/data-types/map-md.groovy
new file mode 100644
index 00000000000..1c42df5f1f2
--- /dev/null
+++
b/regression-test/suites/doc/sql-manual/basic-elements/data-types/map-md.groovy
@@ -0,0 +1,473 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("map-md", "p0") {
+
+ def tableName = "map_table"
+ sql """
+ drop table if exists ${tableName};
+ """
+ sql """ set enable_decimal256 = true; """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_string_int MAP<STRING, INT>,
+ map_string_string MAP<STRING, STRING>,
+ map_string_boolean MAP<STRING, BOOLEAN>,
+ map_string_tinyint MAP<STRING, TINYINT>,
+ map_string_smallint MAP<STRING, SMALLINT>,
+ map_string_bigint MAP<STRING, BIGINT>,
+ map_string_largeint MAP<STRING, LARGEINT>,
+ map_string_float MAP<STRING, FLOAT>,
+ map_string_double MAP<STRING, DOUBLE>,
+ map_string_decimal32 MAP<STRING, DECIMAL(7, 2)>,
+ map_string_decimal64 MAP<STRING, DECIMAL(15, 3)>,
+ map_string_decimal128 MAP<STRING, DECIMAL(25, 5)>,
+ map_string_decimal256 MAP<STRING, DECIMAL(40, 8)>,
+ map_string_date MAP<STRING, DATE>,
+ map_string_datetime MAP<STRING, DATETIME>,
+ map_string_ipv4 MAP<STRING, IPV4>,
+ map_string_ipv6 MAP<STRING, IPV6>,
+ map_string_array MAP<STRING, ARRAY<INT>>,
+ map_string_struct MAP<STRING, STRUCT<id: INT, name: STRING>>,
+ map_string_map MAP<STRING, MAP<STRING, INT>>,
+ map_string_char MAP<STRING, CHAR(10)>,
+ map_string_varchar MAP<STRING, VARCHAR(10)>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """
+ INSERT INTO ${tableName} VALUES
+ (1, MAP('key1', 1, 'key2', 2), MAP('key1', 'value1', 'key2',
'value2'), MAP('key1', true, 'key2', false),
+ MAP('key1', 1, 'key2', 2), MAP('key1', 1, 'key2', 2), MAP('key1', 1,
'key2', 2), MAP('key1', 1, 'key2', 2), MAP('key1', 1.1, 'key2', 2.2),
MAP('key1', 1.1, 'key2', 2.2),
+ MAP('key1', 1.1, 'key2', 2.2), MAP('key1', 1.1, 'key2', 2.2),
MAP('key1', 1.1, 'key2', 2.2), MAP('key1', 1.1, 'key2', 2.2), MAP('key1',
'2021-01-01', 'key2', '2021-01-02'),
+ MAP('key1', '2021-01-01 00:00:00', 'key2', '2021-01-02 00:00:00'),
MAP('key1', '192.168.1.1', 'key2', '192.168.1.2'), MAP('key1', '::1', 'key2',
'::2'),
+ MAP('key1', [1, 2, 3], 'key2', [4, 5, 6]), MAP('key1', STRUCT(1,
'John'), 'key2', STRUCT(2, 'Jane')),
+ MAP('key1', MAP('nested_key1', 1), 'key2', MAP('nested_key2', 2)),
MAP('key1', '1234567890', 'key2', '1234567890'), MAP('key1', '1234567890',
'key2', '1234567890'));
+ """
+
+ qt_sql """
+ SELECT * FROM ${tableName};
+ """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_bool_string MAP<BOOLEAN, STRING>,
+ map_tinyint_string MAP<TINYINT, STRING>,
+ map_smallint_string MAP<SMALLINT, STRING>,
+ map_bigint_string MAP<BIGINT, STRING>,
+ map_largeint_string MAP<LARGEINT, STRING>,
+ map_float_string MAP<FLOAT, STRING>,
+ map_double_string MAP<DOUBLE, STRING>,
+ map_decimal32_string MAP<DECIMAL(7, 2), STRING>,
+ map_decimal64_string MAP<DECIMAL(15, 3), STRING>,
+ map_decimal128_string MAP<DECIMAL(25, 5), STRING>,
+ map_decimal256_string MAP<DECIMAL(40, 8), STRING>,
+ map_date_string MAP<DATE, STRING>,
+ map_datetime_string MAP<DATETIME, STRING>,
+ map_ipv4_string MAP<IPV4, STRING>,
+ map_ipv6_string MAP<IPV6, STRING>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """
+ INSERT INTO ${tableName} VALUES
+ (1, MAP(true, 'value1', false, 'value2'), MAP(1, 'value1', 2,
'value2'), MAP(1, 'value1', 2, 'value2'), MAP(1, 'value1', 2, 'value2'),
+ MAP(1, 'value1', 2, 'value2'), MAP(1.1, 'value1', 2.2, 'value2'),
MAP(1.1, 'value1', 2.2, 'value2'), MAP(1.1, 'value1', 2.2, 'value2'),
+ MAP(1.1, 'value1', 2.2, 'value2'), MAP(1.1, 'value1', 2.2, 'value2'),
MAP(1.1, 'value1', 2.2, 'value2'), MAP('2021-01-01', 'value1', '2021-01-02',
'value2'),
+ MAP('2021-01-01 00:00:00', 'value1', '2021-01-02 00:00:00', 'value2'),
MAP('192.168.1.1', 'value1', '192.168.1.2', 'value2'),
+ MAP('::1', 'value1', '::2', 'value2'));
+ """
+
+ qt_sql """
+ SELECT * FROM ${tableName};
+ """
+
+ sql """DROP TABLE IF EXISTS ${tableName};"""
+
+ test {
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_nested MAP<STRING, MAP<STRING, MAP<STRING, MAP<STRING,
MAP<STRING, MAP<STRING, MAP<STRING, MAP<STRING, MAP<STRING, MAP<STRING,
MAP<STRING, MAP<STRING, INT>>>>>>>>>>>>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Type exceeds the maximum nesting depth of 9"
+ }
+
+ test {
+ sql """ SELECT CAST(MAP('key1', 1, 'key2', 2) AS STRING) """
+ exception "can not cast from origin type MAP<VARCHAR(4),TINYINT> to
target type=TEXT"
+ }
+
+ qt_sql """ SELECT CAST(MAP('key1', 1, 'key2', 2) AS MAP<STRING, STRING>)
"""
+
+ qt_sql """ SELECT CAST('{"key1":1,"key2":2}' AS MAP<STRING, INT>) """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_string_int MAP<STRING, INT> REPLACE_IF_NOT_NULL,
+ map_string_string MAP<STRING, STRING> REPLACE,
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_string_int MAP<STRING, INT> SUM
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type SUM is not compatible with primitive type
MAP<TEXT,INT>"
+ }
+
+ test{
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_string_int MAP<STRING, INT> MIN
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type MIN is not compatible with primitive type
MAP<TEXT,INT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_string_int MAP<STRING, INT> MAX
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type MAX is not compatible with primitive type
MAP<TEXT,INT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_string_int MAP<STRING, INT> HLL_UNION
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type HLL_UNION is not compatible with primitive
type MAP<TEXT,INT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_string_int MAP<STRING, INT> BITMAP_UNION
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type BITMAP_UNION is not compatible with
primitive type MAP<TEXT,INT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_string_int MAP<STRING, INT> QUANTILE_UNION
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type QUANTILE_UNION is not compatible with
primitive type MAP<TEXT,INT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ map_string_int MAP<STRING, INT>,
+ map_value MAP<STRING, STRING>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(map_string_int)
+ DISTRIBUTED BY HASH(map_string_int) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Map can only be used in the non-key column of the duplicate
table at present"
+ }
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_string_int MAP<STRING, INT>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, map_string_int) VALUES (1,
MAP('key1', 1, 'key2', 2)), (2, MAP('key1', 1, 'key2', 2)) """
+
+ test {
+ sql """ select map_string_int, count(*) from ${tableName} group by
map_string_int order by map_string_int """
+ exception "Doris hll, bitmap, array, map, struct, jsonb, variant
column must use with specific function, and don't support filter, group by or
order by. please run 'help hll' or 'help bitmap' or 'help array' or 'help map'
or 'help struct' or 'help jsonb' or 'help variant' in your mysql client"
+ }
+
+
+ sql """ DROP TABLE IF EXISTS map_table_1; """
+ sql """
+ CREATE TABLE IF NOT EXISTS map_table_1 (
+ id INT,
+ map_string_int MAP<STRING, INT>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO map_table_1 (id, map_string_int) VALUES (1,
MAP('key1', 1, 'key2', 2)), (2, MAP('key1', 1, 'key2', 2)) """
+
+ sql """ DROP TABLE IF EXISTS map_table_2; """
+ sql """
+ CREATE TABLE IF NOT EXISTS map_table_2 (
+ id INT,
+ map_string_int MAP<STRING, INT>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO map_table_2 (id, map_string_int) VALUES (1,
MAP('key1', 1, 'key2', 2)), (2, MAP('key1', 1, 'key2', 2)) """
+
+ test {
+ sql """ select * from map_table_1 join map_table_2 on
map_table_1.map_string_int = map_table_2.map_string_int """
+ exception "comparison predicate could not contains complex type:
(map_string_int = map_string_int)"
+ }
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_string_int MAP<STRING, INT>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, map_string_int) VALUES (1,
MAP('key1', 1, 'key2', 2)), (2, MAP('key1', 1, 'key2', 3)), (3, MAP('key1', 1,
'key2', 4)) """
+
+ test {
+ sql """ DELETE FROM ${tableName} WHERE
map_contains_key(map_string_int, 'key1') """
+ exception "Can not apply delete condition to column type:
map<text,int>"
+ }
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_string_string MAP<STRING, STRING>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, map_string_string) VALUES (1,
MAP('key1', 'value1', 'key2', 'value2')), (2, MAP('key1', 'value1', 'key2',
'value2')), (3, MAP('key1', 'value1', 'key2', 'value2')) """
+
+ qt_sql """ select map_string_string['key1'], map_string_string['key2'],
map_string_string['key3'] from ${tableName} order by id """
+
+ qt_sql """ SELECT element_at(map_string_string, 'key1') FROM ${tableName}
order by id """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_string_int MAP<STRING, INT>,
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql """ insert into ${tableName} values (1, MAP('key1', 1, 'key2', 2)),
(2, MAP('key3', 3, 'key4', 4)), (3, MAP('key5', 5, 'key6', 6)); """
+
+ test {
+ sql """ delete from ${tableName} where
map_contains_key(map_string_int, 'key1'); """
+ exception "Can not apply delete condition to column type:
map<text,int>"
+ }
+
+ qt_sql """ SELECT MAP('Alice', 21, 'Bob', 23);"""
+
+ qt_sql """ SELECT {'Alice': 20};"""
+
+ qt_sql """ SELECT {'Alice': 20}['Alice']; """
+
+ qt_sql """ SELECT ELEMENT_AT({'Alice': 20}, 'Alice');"""
+
+
+ qt_sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_nested MAP<STRING, MAP<STRING, INT>>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES (1, MAP('key1', MAP('key2', 1,
'key3', 2)))"""
+ sql """ INSERT INTO ${tableName} VALUES (2, MAP('key1', MAP('key2', 1,
'key3', 2))) """
+
+ qt_sql """ SELECT map_nested['key1']['key2'] FROM ${tableName} order by id
"""
+
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_array MAP<STRING, ARRAY<INT>>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES (1, MAP('key1', [1, 2, 3])), (2,
MAP('key1', [4, 5, 6])) """
+
+ qt_sql """ SELECT map_array['key1'][1] FROM ${tableName} order by id """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ map_struct MAP<STRING, STRUCT<id: INT, name: STRING>>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} VALUES (1, MAP('key1', STRUCT(1, 'John'),
'key2', STRUCT(3, 'Jane'))) """
+
+ qt_sql """ SELECT STRUCT_ELEMENT(map_struct['key1'], 1),
STRUCT_ELEMENT(map_struct['key1'], 'name') FROM ${tableName} order by id """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE ${tableName} (
+ `k` INT NOT NULL,
+ `map_varchar_int` MAP<VARCHAR(10), INT>,
+ `map_int_varchar` MAP<INT, VARCHAR(10)>,
+ `map_varchar_varchar` MAP<VARCHAR(10), VARCHAR(10)>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k`)
+ DISTRIBUTED BY HASH(`k`) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql """ ALTER TABLE ${tableName} MODIFY COLUMN map_varchar_int
MAP<VARCHAR(20), INT>; """
+ sql """ ALTER TABLE ${tableName} MODIFY COLUMN map_int_varchar MAP<INT,
VARCHAR(20)>; """
+ sql """ ALTER TABLE ${tableName} MODIFY COLUMN map_varchar_varchar
MAP<VARCHAR(20), VARCHAR(20)>; """
+
+ qt_sql """ DESC ${tableName} """
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE ${tableName} (
+ `k` INT NOT NULL,
+ `map_varchar_int` MAP<VARCHAR(10), INT>,
+ INDEX map_varchar_int_index (map_varchar_int) USING INVERTED
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k`)
+ DISTRIBUTED BY HASH(`k`) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+ exception "MAP<VARCHAR(10),INT> is not supported in INVERTED index.
invalid index: map_varchar_int_index"
+ }
+}
diff --git
a/regression-test/suites/doc/sql-manual/basic-elements/data-types/struct-md.groovy
b/regression-test/suites/doc/sql-manual/basic-elements/data-types/struct-md.groovy
new file mode 100644
index 00000000000..b6ea1a0876d
--- /dev/null
+++
b/regression-test/suites/doc/sql-manual/basic-elements/data-types/struct-md.groovy
@@ -0,0 +1,527 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("struct-md", "p0") {
+
+ def tableName = "struct_table"
+ sql """
+ drop table if exists ${tableName};
+ """
+ sql """ set enable_decimal256 = true; """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_basic STRUCT<id: INT, name: STRING>,
+ struct_string_boolean STRUCT<name: STRING, active: BOOLEAN>,
+ struct_multi_types STRUCT<tinyint_col: TINYINT, smallint_col:
SMALLINT, bigint_col: BIGINT, largeint_col: LARGEINT>,
+ struct_float_double STRUCT<float_col: FLOAT, double_col: DOUBLE>,
+ struct_decimals STRUCT<decimal32_col: DECIMAL(7, 2),
decimal64_col: DECIMAL(15, 3), decimal128_col: DECIMAL(25, 5), decimal256_col:
DECIMAL(40, 8)>,
+ struct_datetime STRUCT<date_col: DATE, datetime_col: DATETIME>,
+ struct_network STRUCT<ipv4_col: IPV4, ipv6_col: IPV6>,
+ struct_array STRUCT<id: INT, tags: ARRAY<STRING>>,
+ struct_map STRUCT<id: INT, attributes: MAP<STRING, INT>>,
+ struct_nested STRUCT<person: STRUCT<name: STRING, age: INT>,
address: STRUCT<city: STRING, zip: INT>>,
+ struct_char_varchar STRUCT<char_col: CHAR(10), varchar_col:
VARCHAR(10) COMMENT 'test_coment'>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """
+ INSERT INTO ${tableName} VALUES
+ (1, STRUCT(1, 'John'), STRUCT('Alice', true), STRUCT(1, 2, 3, 4),
STRUCT(1.1, 2.2),
+ STRUCT(1.1, 2.2, 3.3, 4.4), STRUCT('2021-01-01', '2021-01-01
00:00:00'),
+ STRUCT('192.168.1.1', '::1'), STRUCT(1, ['tag1', 'tag2']), STRUCT(1,
MAP('key1', 1, 'key2', 2)),
+ STRUCT(STRUCT('John', 25), STRUCT('New York', 10001)),
STRUCT('1234567890', '1234567890'));
+ """
+
+ qt_sql """
+ SELECT * FROM ${tableName};
+ """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_all_types STRUCT<
+ bool_col: BOOLEAN,
+ tinyint_col: TINYINT,
+ smallint_col: SMALLINT,
+ int_col: INT,
+ bigint_col: BIGINT,
+ largeint_col: LARGEINT,
+ float_col: FLOAT,
+ double_col: DOUBLE,
+ decimal32_col: DECIMAL(7, 2),
+ decimal64_col: DECIMAL(15, 3),
+ decimal128_col: DECIMAL(25, 5),
+ decimal256_col: DECIMAL(40, 8),
+ string_col: STRING,
+ date_col: DATE,
+ datetime_col: DATETIME,
+ ipv4_col: IPV4,
+ ipv6_col: IPV6
+ >
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """
+ INSERT INTO ${tableName} VALUES
+ (1, STRUCT(true, 1, 2, 3, 4, 5, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 'test',
'2021-01-01', '2021-01-01 00:00:00', '192.168.1.1', '::1'));
+ """
+
+ qt_sql """
+ SELECT * FROM ${tableName};
+ """
+
+ sql """DROP TABLE IF EXISTS ${tableName};"""
+
+ test {
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_nested STRUCT<
+ level1: STRUCT<
+ level2: STRUCT<
+ level3: STRUCT<
+ level4: STRUCT<
+ level5: STRUCT<
+ level6: STRUCT<
+ level7: STRUCT<
+ level8: STRUCT<
+ level9: STRUCT<
+ level10: STRUCT<
+ level11: INT
+ >
+ >
+ >
+ >
+ >
+ >
+ >
+ >
+ >
+ >
+ >
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Type exceeds the maximum nesting depth of 9"
+ }
+
+ test {
+ sql """ SELECT CAST(STRUCT(1, 'John') AS STRING) """
+ exception "can not cast from origin type STRUCT<StructField (
name=col1, dataType=TINYINT, nullable=true ),StructField ( name=col2,
dataType=VARCHAR(4), nullable=true )> to target type=TEXT"
+ }
+
+ qt_sql """ SELECT CAST(STRUCT(1, 'John') AS STRUCT<id: INT, name: STRING>)
"""
+
+ qt_sql """ SELECT CAST('{"id":1,"name":"John"}' AS STRUCT<id: INT, name:
STRING>) """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_basic STRUCT<id: INT, name: STRING> REPLACE_IF_NOT_NULL,
+ struct_other STRUCT<active: BOOLEAN, score: DOUBLE> REPLACE,
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_basic STRUCT<id: INT, name: STRING> SUM
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type SUM is not compatible with primitive type
STRUCT<id:INT,name:TEXT>"
+ }
+
+ test{
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_basic STRUCT<id: INT, name: STRING> MIN
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type MIN is not compatible with primitive type
STRUCT<id:INT,name:TEXT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_basic STRUCT<id: INT, name: STRING> MAX
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type MAX is not compatible with primitive type
STRUCT<id:INT,name:TEXT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_basic STRUCT<id: INT, name: STRING> HLL_UNION
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type HLL_UNION is not compatible with primitive
type STRUCT<id:INT,name:TEXT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_basic STRUCT<id: INT, name: STRING> BITMAP_UNION
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type BITMAP_UNION is not compatible with
primitive type STRUCT<id:INT,name:TEXT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_basic STRUCT<id: INT, name: STRING> QUANTILE_UNION
+ ) ENGINE=OLAP
+ AGGREGATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Aggregate type QUANTILE_UNION is not compatible with
primitive type STRUCT<id:INT,name:TEXT>"
+ }
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ struct_basic STRUCT<id: INT, name: STRING>,
+ struct_value STRUCT<active: BOOLEAN, score: DOUBLE>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(struct_basic)
+ DISTRIBUTED BY HASH(struct_basic) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ exception "Struct can only be used in the non-key column of the
duplicate table at present"
+ }
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_basic STRUCT<id: INT, name: STRING>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, struct_basic) VALUES (1, STRUCT(1,
'John')), (2, STRUCT(2, 'Jane')) """
+
+ test {
+ sql """ select struct_basic, count(*) from ${tableName} group by
struct_basic order by struct_basic """
+ exception "Doris hll, bitmap, array, map, struct, jsonb, variant
column must use with specific function, and don't support filter, group by or
order by. please run 'help hll' or 'help bitmap' or 'help array' or 'help map'
or 'help struct' or 'help jsonb' or 'help variant' in your mysql client"
+ }
+
+ sql """ DROP TABLE IF EXISTS struct_table_1; """
+ sql """
+ CREATE TABLE IF NOT EXISTS struct_table_1 (
+ id INT,
+ struct_basic STRUCT<id: INT, name: STRING>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO struct_table_1 (id, struct_basic) VALUES (1, STRUCT(1,
'John')), (2, STRUCT(2, 'Jane')) """
+
+ sql """ DROP TABLE IF EXISTS struct_table_2; """
+ sql """
+ CREATE TABLE IF NOT EXISTS struct_table_2 (
+ id INT,
+ struct_basic STRUCT<id: INT, name: STRING>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO struct_table_2 (id, struct_basic) VALUES (1, STRUCT(1,
'John')), (2, STRUCT(2, 'Jane')) """
+
+ test {
+ sql """ select * from struct_table_1 join struct_table_2 on
struct_table_1.struct_basic = struct_table_2.struct_basic """
+ exception "comparison predicate could not contains complex type:
(struct_basic = struct_basic)"
+ }
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_basic STRUCT<id: INT, name: STRING>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, struct_basic) VALUES (1, STRUCT(1,
'John')), (2, STRUCT(2, 'Jane')), (3, STRUCT(3, 'Bob')) """
+
+ test {
+ sql """ DELETE FROM ${tableName} WHERE struct_element(struct_basic, 1)
= 1 """
+ exception "Can not apply delete condition to column type:
struct<id:int,name:text>"
+ }
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_basic STRUCT<id: INT, name: STRING>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """ INSERT INTO ${tableName} (id, struct_basic) VALUES (1, STRUCT(1,
'John')), (2, STRUCT(2, 'Jane')), (3, STRUCT(3, 'Bob')) """
+
+ qt_sql """ select struct_element(struct_basic, 1),
struct_element(struct_basic, 2), struct_element(struct_basic, 'id'),
struct_element(struct_basic, 'name') from ${tableName} order by id """
+
+ qt_sql """ SELECT STRUCT_ELEMENT(struct_basic, 1),
STRUCT_ELEMENT(struct_basic, 'name') FROM ${tableName} order by id """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_basic STRUCT<id: INT, name: STRING, active: BOOLEAN>,
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql """ insert into ${tableName} values (1, STRUCT(1, 'John', true)), (2,
STRUCT(2, 'Jane', false)), (3, STRUCT(3, 'Bob', true)); """
+
+ test {
+ sql """ delete from ${tableName} where struct_element(struct_basic,
'active') = true; """
+ exception "Can not apply delete condition to column type:
struct<id:int,name:text,active:boolean>"
+ }
+
+ qt_sql """ SELECT STRUCT(1, 'Alice', true);"""
+
+ qt_sql """ SELECT STRUCT(1, 'Alice', true) as person;"""
+
+ qt_sql """ SELECT STRUCT_ELEMENT(STRUCT(1, 'Alice', true), 1); """
+
+ qt_sql """ SELECT STRUCT_ELEMENT(STRUCT(1, 'Alice', true), 2);"""
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_nested STRUCT<person: STRUCT<name: STRING, age: INT>,
address: STRUCT<city: STRING, zip: INT>>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES (1, STRUCT(STRUCT('John', 25),
STRUCT('New York', 10001)))"""
+ sql """ INSERT INTO ${tableName} VALUES (2, STRUCT(STRUCT('Jane', 30),
STRUCT('Los Angeles', 90001))) """
+
+ qt_sql """ SELECT STRUCT_ELEMENT(STRUCT_ELEMENT(struct_nested, 'person'),
'name'), STRUCT_ELEMENT(STRUCT_ELEMENT(struct_nested, 'address'), 'city') FROM
${tableName} order by id """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_array STRUCT<id: INT, tags: ARRAY<STRING>>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES (1, STRUCT(1, ['tag1', 'tag2',
'tag3'])), (2, STRUCT(2, ['tag4', 'tag5'])) """
+
+ qt_sql """ SELECT STRUCT_ELEMENT(struct_array, 'id'),
STRUCT_ELEMENT(struct_array, 'tags') FROM ${tableName} order by id """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_map STRUCT<id: INT, attributes: MAP<STRING, INT>>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES (1, STRUCT(1, MAP('key1', 1,
'key2', 2))) """
+
+ qt_sql """ SELECT STRUCT_ELEMENT(struct_map, 'id'),
STRUCT_ELEMENT(struct_map, 'attributes') FROM ${tableName} order by id """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE ${tableName} (
+ `k` INT NOT NULL,
+ `struct_varchar` STRUCT<name: VARCHAR(10), age: INT>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k`)
+ DISTRIBUTED BY HASH(`k`) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql """ ALTER TABLE ${tableName} MODIFY COLUMN struct_varchar STRUCT<name:
VARCHAR(20), age: INT>; """
+
+ qt_sql """ DESC ${tableName} """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ id INT,
+ struct_complex STRUCT<
+ basic_info: STRUCT<name: STRING, age: INT>,
+ contact: STRUCT<email: STRING, phone: STRING>,
+ preferences: STRUCT<tags: ARRAY<STRING>, settings: MAP<STRING,
INT>>,
+ metadata: STRUCT<
+ created_at: DATETIME,
+ updated_at: DATETIME,
+ stats: STRUCT<views: INT, clicks: INT>
+ >
+ >
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES (1, STRUCT(
+ STRUCT('John', 25),
+ STRUCT('[email protected]', '1234567890'),
+ STRUCT(['tag1', 'tag2'], MAP('setting1', 1, 'setting2', 2)),
+ STRUCT('2021-01-01 00:00:00', '2021-01-02 00:00:00', STRUCT(100, 50))
+ )) """
+
+ qt_sql """ SELECT
+ STRUCT_ELEMENT(STRUCT_ELEMENT(struct_complex, 'basic_info'), 'name'),
+ STRUCT_ELEMENT(STRUCT_ELEMENT(struct_complex, 'contact'), 'email'),
+ STRUCT_ELEMENT(STRUCT_ELEMENT(STRUCT_ELEMENT(struct_complex,
'metadata'), 'stats'), 'views')
+ FROM ${tableName} order by id """
+
+ test {
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE ${tableName} (
+ `k` INT NOT NULL,
+ `struct_varchar` STRUCT<name: VARCHAR(10), age: INT>,
+ INDEX struct_varchar_index (struct_varchar) USING INVERTED
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k`)
+ DISTRIBUTED BY HASH(`k`) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+ exception "STRUCT<StructField ( name=name, dataType=VARCHAR(10),
nullable=true ),StructField ( name=age, dataType=INT, nullable=true )> is not
supported in INVERTED index. invalid index: struct_varchar_index"
+ }
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE ${tableName} (
+ `k` INT NOT NULL,
+ `struct_varchar` STRUCT<name: VARCHAR(10), age: INT>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k`)
+ DISTRIBUTED BY HASH(`k`) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+ sql """ ALTER TABLE ${tableName} MODIFY COLUMN struct_varchar STRUCT<name:
VARCHAR(10), age: INT, id: INT>; """
+
+ qt_sql """ DESC ${tableName}; """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
