This is an automated email from the ASF dual-hosted git repository.
morningman 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 1a00e5e9abc [bug](parquet)Fix the problem that the parquet reader
reads the missing sub-columns of the struct and fails. (#38718)
1a00e5e9abc is described below
commit 1a00e5e9abcd77a14233360075c54ed662798ec1
Author: daidai <[email protected]>
AuthorDate: Wed Aug 7 16:41:20 2024 +0800
[bug](parquet)Fix the problem that the parquet reader reads the missing
sub-columns of the struct and fails. (#38718)
## Proposed changes
Fix the problem that the parquet reader reads the missing sub-columns of
the struct and fails.
for example :
suppose we have a column `array<struct<a:int>>` . And this column has
two data
```
[{1},{2},{3}]
[{4},{5}]
```
Then we add a subcolumn b to the struct . Now the column structure is
like this `array<struct<a:int,b:string>>`
The expected data for the query is as follows, instead of an error :
```
[{1,null},{2,null},{3,null}]
[{4,null},{5,null}]
```
---
.../exec/format/parquet/vparquet_column_reader.cpp | 22 +-
.../scripts/create_preinstalled_scripts/run66.hql | 22 ++
.../test_hive_struct_add_column_orc/000000_0 | Bin 0 -> 578 bytes
.../000000_0_copy_1 | Bin 0 -> 778 bytes
.../000000_0_copy_2 | Bin 0 -> 940 bytes
.../000000_0_copy_3 | Bin 0 -> 996 bytes
.../000000_0_copy_4 | Bin 0 -> 1127 bytes
.../000000_0_copy_5 | Bin 0 -> 1393 bytes
.../test_hive_struct_add_column_parquet/000000_0 | Bin 0 -> 1008 bytes
.../000000_0_copy_1 | Bin 0 -> 1098 bytes
.../000000_0_copy_2 | Bin 0 -> 1423 bytes
.../000000_0_copy_3 | Bin 0 -> 1445 bytes
.../000000_0_copy_4 | Bin 0 -> 1704 bytes
.../000000_0_copy_5 | Bin 0 -> 2130 bytes
.../hive/test_hive_struct_add_column.out | 411 +++++++++++++++++++++
.../hive/test_hive_struct_add_column.groovy | 169 +++++++++
16 files changed, 616 insertions(+), 8 deletions(-)
diff --git a/be/src/vec/exec/format/parquet/vparquet_column_reader.cpp
b/be/src/vec/exec/format/parquet/vparquet_column_reader.cpp
index 4efa6c60e47..c51a51bac3c 100644
--- a/be/src/vec/exec/format/parquet/vparquet_column_reader.cpp
+++ b/be/src/vec/exec/format/parquet/vparquet_column_reader.cpp
@@ -352,9 +352,6 @@ Status ScalarColumnReader::_read_nested_column(ColumnPtr&
doris_column, DataType
SCOPED_RAW_TIMER(&_decode_null_map_time);
auto* nullable_column = const_cast<vectorized::ColumnNullable*>(
static_cast<const
vectorized::ColumnNullable*>(doris_column.get()));
-
- // auto* nullable_column =
reinterpret_cast<vectorized::ColumnNullable*>(
- // (*std::move(src_column)).mutate().get());
data_column = nullable_column->get_nested_column_ptr();
map_data_column = &(nullable_column->get_null_map_data());
} else {
@@ -723,7 +720,7 @@ Status StructColumnReader::read_column_data(ColumnPtr&
doris_column, DataTypePtr
const DataTypeStruct* doris_struct_type =
reinterpret_cast<const
DataTypeStruct*>(remove_nullable(type).get());
- bool least_one_reader = false;
+ int not_missing_column_id = -1;
std::vector<size_t> missing_column_idxs {};
_read_column_names.clear();
@@ -744,8 +741,8 @@ Status StructColumnReader::read_column_data(ColumnPtr&
doris_column, DataTypePtr
select_vector.reset();
size_t field_rows = 0;
bool field_eof = false;
- if (!least_one_reader) {
- least_one_reader = true;
+ if (not_missing_column_id == -1) {
+ not_missing_column_id = i;
RETURN_IF_ERROR(_child_readers[doris_name]->read_column_data(
doris_field, doris_type, select_vector, batch_size,
&field_rows, &field_eof,
is_dict_filter));
@@ -765,12 +762,21 @@ Status StructColumnReader::read_column_data(ColumnPtr&
doris_column, DataTypePtr
}
}
- if (!least_one_reader) {
+ if (not_missing_column_id == -1) {
// TODO: support read struct which columns are all missing
return Status::Corruption("Not support read struct '{}' which columns
are all missing",
_field_schema->name);
}
+ // This missing_column_sz is not *read_rows. Because read_rows returns
the number of rows.
+ // For example: suppose we have a column array<struct<a:int,b:string>>,
+ // where b is a newly added column, that is, a missing column.
+ // There are two rows of data in this column,
+ // [{1,null},{2,null},{3,null}]
+ // [{4,null},{5,null}]
+ // When you first read subcolumn a, you read 5 data items and the value
of *read_rows is 2.
+ // You should insert 5 records into subcolumn b instead of 2.
+ auto missing_column_sz =
doris_struct.get_column(not_missing_column_id).size();
// fill missing column with null or default value
for (auto idx : missing_column_idxs) {
auto& doris_field = doris_struct.get_column_ptr(idx);
@@ -778,7 +784,7 @@ Status StructColumnReader::read_column_data(ColumnPtr&
doris_column, DataTypePtr
DCHECK(doris_type->is_nullable());
auto* nullable_column = reinterpret_cast<vectorized::ColumnNullable*>(
(*std::move(doris_field)).mutate().get());
- nullable_column->insert_null_elements(*read_rows);
+ nullable_column->insert_null_elements(missing_column_sz);
}
if (null_map_ptr != nullptr) {
diff --git
a/docker/thirdparties/docker-compose/hive/scripts/create_preinstalled_scripts/run66.hql
b/docker/thirdparties/docker-compose/hive/scripts/create_preinstalled_scripts/run66.hql
new file mode 100644
index 00000000000..bc0ac3327ea
--- /dev/null
+++
b/docker/thirdparties/docker-compose/hive/scripts/create_preinstalled_scripts/run66.hql
@@ -0,0 +1,22 @@
+use `default`;
+
+create table test_hive_struct_add_column_orc (
+ `id` int,
+ `name` string,
+ `details` struct<age:int,city:string,email:string,phone:int>,
+ `sex` int,
+ `complex` array<struct<a:int,b:struct<aa:string,bb:int>>>
+)
+STORED AS ORC
+LOCATION
'/user/doris/preinstalled_data/orc_table/test_hive_struct_add_column_orc';
+
+create table test_hive_struct_add_column_parquet (
+ `id` int,
+ `name` string,
+ `details` struct<age:int,city:string,email:string,phone:int>,
+ `sex` int,
+ `complex` array<struct<a:int,b:struct<aa:string,bb:int>>>
+)
+STORED AS parquet
+LOCATION
'/user/doris/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet';
+
diff --git
a/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0
new file mode 100644
index 00000000000..df41136523a
Binary files /dev/null and
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0
differ
diff --git
a/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_1
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_1
new file mode 100644
index 00000000000..d6a1045e1d6
Binary files /dev/null and
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_1
differ
diff --git
a/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_2
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_2
new file mode 100644
index 00000000000..46cb7e8647c
Binary files /dev/null and
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_2
differ
diff --git
a/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_3
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_3
new file mode 100644
index 00000000000..a92fdd7f2d9
Binary files /dev/null and
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_3
differ
diff --git
a/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_4
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_4
new file mode 100644
index 00000000000..b8f4d0e0658
Binary files /dev/null and
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_4
differ
diff --git
a/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_5
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_5
new file mode 100644
index 00000000000..21dd6d9e00e
Binary files /dev/null and
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/orc_table/test_hive_struct_add_column_orc/000000_0_copy_5
differ
diff --git
a/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0
new file mode 100644
index 00000000000..6a22c41be14
Binary files /dev/null and
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0
differ
diff --git
a/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_1
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_1
new file mode 100644
index 00000000000..136ae51ba3b
Binary files /dev/null and
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_1
differ
diff --git
a/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_2
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_2
new file mode 100644
index 00000000000..fbfdca32d4f
Binary files /dev/null and
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_2
differ
diff --git
a/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_3
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_3
new file mode 100644
index 00000000000..20ebbc6beef
Binary files /dev/null and
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_3
differ
diff --git
a/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_4
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_4
new file mode 100644
index 00000000000..7cb59513acc
Binary files /dev/null and
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_4
differ
diff --git
a/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_5
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_5
new file mode 100644
index 00000000000..3dca31eb184
Binary files /dev/null and
b/docker/thirdparties/docker-compose/hive/scripts/preinstalled_data/parquet_table/test_hive_struct_add_column_parquet/000000_0_copy_5
differ
diff --git
a/regression-test/data/external_table_p0/hive/test_hive_struct_add_column.out
b/regression-test/data/external_table_p0/hive/test_hive_struct_add_column.out
new file mode 100644
index 00000000000..869d6574fe9
--- /dev/null
+++
b/regression-test/data/external_table_p0/hive/test_hive_struct_add_column.out
@@ -0,0 +1,411 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !desc --
+id int Yes true \N
+name text Yes true \N
+details struct<age:int,city:text,email:text,phone:int> Yes true
\N
+sex int Yes true \N
+complex array<struct<a:int,b:struct<aa:text,bb:int>>> Yes true
\N
+
+-- !test_1 --
+1 Alice {"age":25, "city":"New York", "email":null, "phone":null}
\N \N
+2 Blice {"age":26, "city":"New York New York", "email":null,
"phone":null} \N \N
+3 Clice {"age":27, "city":"New York New York New York", "email":null,
"phone":null} \N \N
+4 Dlice {"age":28, "city":"New York New York New York New York",
"email":null, "phone":null} \N \N
+5 Elice {"age":29, "city":"New York New York New York New York New
York", "email":null, "phone":null} \N \N
+11 AAlice {"age":125, "city":"acity", "email":"[email protected]",
"phone":null} \N \N
+12 BBlice {"age":126, "city":"bcity", "email":"[email protected]",
"phone":null} \N \N
+13 CClice {"age":127, "city":"ccity", "email":"[email protected]",
"phone":null} \N \N
+14 DDlice {"age":128, "city":"dcity", "email":"[email protected]",
"phone":null} \N \N
+15 EElice {"age":129, "city":"ecity", "email":null, "phone":null} \N
\N
+21 Charlie {"age":218, "city":"San Francisco",
"email":"[email protected]", "phone":123} \N \N
+22 Charlie {"age":228, "city":"San-Francisco",
"email":"[email protected]", "phone":1234} \N \N
+23 Charlie {"age":238, "city":"SanxFrancisco",
"email":"[email protected]", "phone":12345} \N \N
+24 Charlie {"age":248, "city":"San888Francisco",
"email":"[email protected]", "phone":123456} \N \N
+25 Charlie {"age":258, "city":"San0000Francisco",
"email":"[email protected]", "phone":null} \N \N
+31 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 0 \N
+32 Bob {"age":30, "city":"Los Angeles", "email":"[email protected]",
"phone":789012} 0 \N
+33 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 1 \N
+34 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 0 \N
+35 Eve {"age":27, "city":"Seattle", "email":"[email protected]",
"phone":null} \N \N
+41 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 1 [{"a":1, "b":null}, {"a":1, "b":null}]
+42 Bob {"age":30, "city":"Los Angeles", "email":"[email protected]",
"phone":789012} 1 [{"a":2, "b":null}, {"a":1, "b":null}]
+43 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 2 [{"a":3,
"b":null}, {"a":1, "b":null}]
+44 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 1 [{"a":4, "b":null}, {"a":1, "b":null}]
+45 Eve {"age":27, "city":"Seattle", "email":"[email protected]",
"phone":654321} 2 [{"a":5, "b":null}, {"a":1, "b":null}]
+51 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 1 [{"a":1, "b":{"aa":"foo", "bb":100}}, {"a":1,
"b":{"aa":"foo", "bb":100}}]
+52 Bob {"age":30, "city":"Los Angeles", "email":"[email protected]",
"phone":789012} 2 [{"a":2, "b":{"aa":"bar", "bb":200}}]
+53 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 1 [{"a":3,
"b":{"aa":"baz", "bb":300}}]
+54 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 2 [{"a":8, "b":{"aa":"qux", "bb":400}}]
+55 Eve {"age":27, "city":"Seattle", "email":"[email protected]",
"phone":654321} 1 [{"a":5, "b":{"aa":"abcd", "bb":500}}, {"a":5,
"b":{"aa":"abcdffff", "bb":5000}}, {"a":5, "b":{"aa":"abcdtttt", "bb":500000}}]
+
+-- !test_2 --
+1 Alice {"age":25, "city":"New York", "email":null, "phone":null}
\N \N
+
+-- !test_3 --
+1 Alice {"age":25, "city":"New York", "email":null, "phone":null}
\N \N
+2 Blice {"age":26, "city":"New York New York", "email":null,
"phone":null} \N \N
+3 Clice {"age":27, "city":"New York New York New York", "email":null,
"phone":null} \N \N
+4 Dlice {"age":28, "city":"New York New York New York New York",
"email":null, "phone":null} \N \N
+5 Elice {"age":29, "city":"New York New York New York New York New
York", "email":null, "phone":null} \N \N
+11 AAlice {"age":125, "city":"acity", "email":"[email protected]",
"phone":null} \N \N
+12 BBlice {"age":126, "city":"bcity", "email":"[email protected]",
"phone":null} \N \N
+13 CClice {"age":127, "city":"ccity", "email":"[email protected]",
"phone":null} \N \N
+14 DDlice {"age":128, "city":"dcity", "email":"[email protected]",
"phone":null} \N \N
+15 EElice {"age":129, "city":"ecity", "email":null, "phone":null} \N
\N
+21 Charlie {"age":218, "city":"San Francisco",
"email":"[email protected]", "phone":123} \N \N
+22 Charlie {"age":228, "city":"San-Francisco",
"email":"[email protected]", "phone":1234} \N \N
+23 Charlie {"age":238, "city":"SanxFrancisco",
"email":"[email protected]", "phone":12345} \N \N
+24 Charlie {"age":248, "city":"San888Francisco",
"email":"[email protected]", "phone":123456} \N \N
+25 Charlie {"age":258, "city":"San0000Francisco",
"email":"[email protected]", "phone":null} \N \N
+31 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 0 \N
+32 Bob {"age":30, "city":"Los Angeles", "email":"[email protected]",
"phone":789012} 0 \N
+33 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 1 \N
+34 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 0 \N
+35 Eve {"age":27, "city":"Seattle", "email":"[email protected]",
"phone":null} \N \N
+
+-- !test_4 --
+41 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 1 [{"a":1, "b":null}, {"a":1, "b":null}]
+42 Bob {"age":30, "city":"Los Angeles", "email":"[email protected]",
"phone":789012} 1 [{"a":2, "b":null}, {"a":1, "b":null}]
+43 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 2 [{"a":3,
"b":null}, {"a":1, "b":null}]
+44 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 1 [{"a":4, "b":null}, {"a":1, "b":null}]
+45 Eve {"age":27, "city":"Seattle", "email":"[email protected]",
"phone":654321} 2 [{"a":5, "b":null}, {"a":1, "b":null}]
+51 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 1 [{"a":1, "b":{"aa":"foo", "bb":100}}, {"a":1,
"b":{"aa":"foo", "bb":100}}]
+52 Bob {"age":30, "city":"Los Angeles", "email":"[email protected]",
"phone":789012} 2 [{"a":2, "b":{"aa":"bar", "bb":200}}]
+53 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 1 [{"a":3,
"b":{"aa":"baz", "bb":300}}]
+54 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 2 [{"a":8, "b":{"aa":"qux", "bb":400}}]
+55 Eve {"age":27, "city":"Seattle", "email":"[email protected]",
"phone":654321} 1 [{"a":5, "b":{"aa":"abcd", "bb":500}}, {"a":5,
"b":{"aa":"abcdffff", "bb":5000}}, {"a":5, "b":{"aa":"abcdtttt", "bb":500000}}]
+
+-- !test_5 --
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+
+-- !test_6 --
+[{"a":1, "b":null}, {"a":1, "b":null}]
+[{"a":2, "b":null}, {"a":1, "b":null}]
+[{"a":3, "b":null}, {"a":1, "b":null}]
+[{"a":4, "b":null}, {"a":1, "b":null}]
+[{"a":5, "b":null}, {"a":1, "b":null}]
+[{"a":1, "b":{"aa":"foo", "bb":100}}, {"a":1, "b":{"aa":"foo", "bb":100}}]
+[{"a":2, "b":{"aa":"bar", "bb":200}}]
+[{"a":3, "b":{"aa":"baz", "bb":300}}]
+[{"a":8, "b":{"aa":"qux", "bb":400}}]
+[{"a":5, "b":{"aa":"abcd", "bb":500}}, {"a":5, "b":{"aa":"abcdffff",
"bb":5000}}, {"a":5, "b":{"aa":"abcdtttt", "bb":500000}}]
+
+-- !test_7 --
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+
+-- !test_8 --
+[{"a":1, "b":null}, {"a":1, "b":null}]
+[{"a":2, "b":null}, {"a":1, "b":null}]
+[{"a":3, "b":null}, {"a":1, "b":null}]
+[{"a":4, "b":null}, {"a":1, "b":null}]
+[{"a":5, "b":null}, {"a":1, "b":null}]
+[{"a":1, "b":{"aa":"foo", "bb":100}}, {"a":1, "b":{"aa":"foo", "bb":100}}]
+[{"a":2, "b":{"aa":"bar", "bb":200}}]
+[{"a":3, "b":{"aa":"baz", "bb":300}}]
+[{"a":8, "b":{"aa":"qux", "bb":400}}]
+[{"a":5, "b":{"aa":"abcd", "bb":500}}, {"a":5, "b":{"aa":"abcdffff",
"bb":5000}}, {"a":5, "b":{"aa":"abcdtttt", "bb":500000}}]
+
+-- !test_9 --
+0
+0
+0
+
+-- !test_10 --
+1
+1
+1
+1
+1
+1
+1
+
+-- !test_11 --
+2
+2
+2
+2
+
+-- !test_12 --
+43 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 2 [{"a":3,
"b":null}, {"a":1, "b":null}]
+45 Eve {"age":27, "city":"Seattle", "email":"[email protected]",
"phone":654321} 2 [{"a":5, "b":null}, {"a":1, "b":null}]
+52 Bob {"age":30, "city":"Los Angeles", "email":"[email protected]",
"phone":789012} 2 [{"a":2, "b":{"aa":"bar", "bb":200}}]
+54 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 2 [{"a":8, "b":{"aa":"qux", "bb":400}}]
+
+-- !test_13 --
+
+-- !test_14 --
+53 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 1 [{"a":3,
"b":{"aa":"baz", "bb":300}}]
+54 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 2 [{"a":8, "b":{"aa":"qux", "bb":400}}]
+
+-- !test_15 --
+41 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 1 [{"a":1, "b":null}, {"a":1, "b":null}] {"a":1,
"b":null}
+51 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 1 [{"a":1, "b":{"aa":"foo", "bb":100}}, {"a":1,
"b":{"aa":"foo", "bb":100}}] {"a":1, "b":{"aa":"foo", "bb":100}}
+
+-- !test_16 --
+[{"a":2, "b":null}, {"a":1, "b":null}]
+
+-- !test_17 --
+{"age":25, "city":"New York", "email":"[email protected]", "phone":123456}
+{"age":25, "city":"New York", "email":"[email protected]", "phone":123456}
+{"age":25, "city":"New York", "email":"[email protected]", "phone":123456}
+
+-- !test_18 --
+{"age":25, "city":"New York", "email":null, "phone":null}
+
+-- !test_19 --
+{"age":25, "city":"New York", "email":"[email protected]", "phone":123456}
31
+{"age":25, "city":"New York", "email":"[email protected]", "phone":123456}
41
+{"age":25, "city":"New York", "email":"[email protected]", "phone":123456}
51
+
+-- !test_20 --
+{"age":25, "city":"New York", "email":null, "phone":null} 1
+
+-- !test_21 --
+0 3
+2 4
+1 7
+\N 16
+
+-- !desc --
+id int Yes true \N
+name text Yes true \N
+details struct<age:int,city:text,email:text,phone:int> Yes true
\N
+sex int Yes true \N
+complex array<struct<a:int,b:struct<aa:text,bb:int>>> Yes true
\N
+
+-- !test_1 --
+1 Alice {"age":25, "city":"New York", "email":null, "phone":null}
\N \N
+2 Blice {"age":26, "city":"New York New York", "email":null,
"phone":null} \N \N
+3 Clice {"age":27, "city":"New York New York New York", "email":null,
"phone":null} \N \N
+4 Dlice {"age":28, "city":"New York New York New York New York",
"email":null, "phone":null} \N \N
+5 Elice {"age":29, "city":"New York New York New York New York New
York", "email":null, "phone":null} \N \N
+11 AAlice {"age":125, "city":"acity", "email":"[email protected]",
"phone":null} \N \N
+12 BBlice {"age":126, "city":"bcity", "email":"[email protected]",
"phone":null} \N \N
+13 CClice {"age":127, "city":"ccity", "email":"[email protected]",
"phone":null} \N \N
+14 DDlice {"age":128, "city":"dcity", "email":"[email protected]",
"phone":null} \N \N
+15 EElice {"age":129, "city":"ecity", "email":null, "phone":null} \N
\N
+21 Charlie {"age":218, "city":"San Francisco",
"email":"[email protected]", "phone":123} \N \N
+22 Charlie {"age":228, "city":"San-Francisco",
"email":"[email protected]", "phone":1234} \N \N
+23 Charlie {"age":238, "city":"SanxFrancisco",
"email":"[email protected]", "phone":12345} \N \N
+24 Charlie {"age":248, "city":"San888Francisco",
"email":"[email protected]", "phone":123456} \N \N
+25 Charlie {"age":258, "city":"San0000Francisco",
"email":"[email protected]", "phone":null} \N \N
+31 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 0 \N
+32 Bob {"age":30, "city":"Los Angeles", "email":"[email protected]",
"phone":789012} 0 \N
+33 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 1 \N
+34 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 0 \N
+35 Eve {"age":27, "city":"Seattle", "email":"[email protected]",
"phone":null} \N \N
+41 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 1 [{"a":1, "b":null}, {"a":1, "b":null}]
+42 Bob {"age":30, "city":"Los Angeles", "email":"[email protected]",
"phone":789012} 1 [{"a":2, "b":null}, {"a":1, "b":null}]
+43 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 2 [{"a":3,
"b":null}, {"a":1, "b":null}]
+44 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 1 [{"a":4, "b":null}, {"a":1, "b":null}]
+45 Eve {"age":27, "city":"Seattle", "email":"[email protected]",
"phone":654321} 2 [{"a":5, "b":null}, {"a":1, "b":null}]
+51 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 1 [{"a":1, "b":{"aa":"foo", "bb":100}}, {"a":1,
"b":{"aa":"foo", "bb":100}}]
+52 Bob {"age":30, "city":"Los Angeles", "email":"[email protected]",
"phone":789012} 2 [{"a":2, "b":{"aa":"bar", "bb":200}}]
+53 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 1 [{"a":3,
"b":{"aa":"baz", "bb":300}}]
+54 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 2 [{"a":8, "b":{"aa":"qux", "bb":400}}]
+55 Eve {"age":27, "city":"Seattle", "email":"[email protected]",
"phone":654321} 1 [{"a":5, "b":{"aa":"abcd", "bb":500}}, {"a":5,
"b":{"aa":"abcdffff", "bb":5000}}, {"a":5, "b":{"aa":"abcdtttt", "bb":500000}}]
+
+-- !test_2 --
+1 Alice {"age":25, "city":"New York", "email":null, "phone":null}
\N \N
+
+-- !test_3 --
+1 Alice {"age":25, "city":"New York", "email":null, "phone":null}
\N \N
+2 Blice {"age":26, "city":"New York New York", "email":null,
"phone":null} \N \N
+3 Clice {"age":27, "city":"New York New York New York", "email":null,
"phone":null} \N \N
+4 Dlice {"age":28, "city":"New York New York New York New York",
"email":null, "phone":null} \N \N
+5 Elice {"age":29, "city":"New York New York New York New York New
York", "email":null, "phone":null} \N \N
+11 AAlice {"age":125, "city":"acity", "email":"[email protected]",
"phone":null} \N \N
+12 BBlice {"age":126, "city":"bcity", "email":"[email protected]",
"phone":null} \N \N
+13 CClice {"age":127, "city":"ccity", "email":"[email protected]",
"phone":null} \N \N
+14 DDlice {"age":128, "city":"dcity", "email":"[email protected]",
"phone":null} \N \N
+15 EElice {"age":129, "city":"ecity", "email":null, "phone":null} \N
\N
+21 Charlie {"age":218, "city":"San Francisco",
"email":"[email protected]", "phone":123} \N \N
+22 Charlie {"age":228, "city":"San-Francisco",
"email":"[email protected]", "phone":1234} \N \N
+23 Charlie {"age":238, "city":"SanxFrancisco",
"email":"[email protected]", "phone":12345} \N \N
+24 Charlie {"age":248, "city":"San888Francisco",
"email":"[email protected]", "phone":123456} \N \N
+25 Charlie {"age":258, "city":"San0000Francisco",
"email":"[email protected]", "phone":null} \N \N
+31 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 0 \N
+32 Bob {"age":30, "city":"Los Angeles", "email":"[email protected]",
"phone":789012} 0 \N
+33 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 1 \N
+34 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 0 \N
+35 Eve {"age":27, "city":"Seattle", "email":"[email protected]",
"phone":null} \N \N
+
+-- !test_4 --
+41 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 1 [{"a":1, "b":null}, {"a":1, "b":null}]
+42 Bob {"age":30, "city":"Los Angeles", "email":"[email protected]",
"phone":789012} 1 [{"a":2, "b":null}, {"a":1, "b":null}]
+43 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 2 [{"a":3,
"b":null}, {"a":1, "b":null}]
+44 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 1 [{"a":4, "b":null}, {"a":1, "b":null}]
+45 Eve {"age":27, "city":"Seattle", "email":"[email protected]",
"phone":654321} 2 [{"a":5, "b":null}, {"a":1, "b":null}]
+51 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 1 [{"a":1, "b":{"aa":"foo", "bb":100}}, {"a":1,
"b":{"aa":"foo", "bb":100}}]
+52 Bob {"age":30, "city":"Los Angeles", "email":"[email protected]",
"phone":789012} 2 [{"a":2, "b":{"aa":"bar", "bb":200}}]
+53 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 1 [{"a":3,
"b":{"aa":"baz", "bb":300}}]
+54 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 2 [{"a":8, "b":{"aa":"qux", "bb":400}}]
+55 Eve {"age":27, "city":"Seattle", "email":"[email protected]",
"phone":654321} 1 [{"a":5, "b":{"aa":"abcd", "bb":500}}, {"a":5,
"b":{"aa":"abcdffff", "bb":5000}}, {"a":5, "b":{"aa":"abcdtttt", "bb":500000}}]
+
+-- !test_5 --
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+
+-- !test_6 --
+[{"a":1, "b":null}, {"a":1, "b":null}]
+[{"a":2, "b":null}, {"a":1, "b":null}]
+[{"a":3, "b":null}, {"a":1, "b":null}]
+[{"a":4, "b":null}, {"a":1, "b":null}]
+[{"a":5, "b":null}, {"a":1, "b":null}]
+[{"a":1, "b":{"aa":"foo", "bb":100}}, {"a":1, "b":{"aa":"foo", "bb":100}}]
+[{"a":2, "b":{"aa":"bar", "bb":200}}]
+[{"a":3, "b":{"aa":"baz", "bb":300}}]
+[{"a":8, "b":{"aa":"qux", "bb":400}}]
+[{"a":5, "b":{"aa":"abcd", "bb":500}}, {"a":5, "b":{"aa":"abcdffff",
"bb":5000}}, {"a":5, "b":{"aa":"abcdtttt", "bb":500000}}]
+
+-- !test_7 --
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+
+-- !test_8 --
+[{"a":1, "b":null}, {"a":1, "b":null}]
+[{"a":2, "b":null}, {"a":1, "b":null}]
+[{"a":3, "b":null}, {"a":1, "b":null}]
+[{"a":4, "b":null}, {"a":1, "b":null}]
+[{"a":5, "b":null}, {"a":1, "b":null}]
+[{"a":1, "b":{"aa":"foo", "bb":100}}, {"a":1, "b":{"aa":"foo", "bb":100}}]
+[{"a":2, "b":{"aa":"bar", "bb":200}}]
+[{"a":3, "b":{"aa":"baz", "bb":300}}]
+[{"a":8, "b":{"aa":"qux", "bb":400}}]
+[{"a":5, "b":{"aa":"abcd", "bb":500}}, {"a":5, "b":{"aa":"abcdffff",
"bb":5000}}, {"a":5, "b":{"aa":"abcdtttt", "bb":500000}}]
+
+-- !test_9 --
+0
+0
+0
+
+-- !test_10 --
+1
+1
+1
+1
+1
+1
+1
+
+-- !test_11 --
+2
+2
+2
+2
+
+-- !test_12 --
+43 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 2 [{"a":3,
"b":null}, {"a":1, "b":null}]
+45 Eve {"age":27, "city":"Seattle", "email":"[email protected]",
"phone":654321} 2 [{"a":5, "b":null}, {"a":1, "b":null}]
+52 Bob {"age":30, "city":"Los Angeles", "email":"[email protected]",
"phone":789012} 2 [{"a":2, "b":{"aa":"bar", "bb":200}}]
+54 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 2 [{"a":8, "b":{"aa":"qux", "bb":400}}]
+
+-- !test_13 --
+
+-- !test_14 --
+53 Charlie {"age":28, "city":"San Francisco",
"email":"[email protected]", "phone":456789} 1 [{"a":3,
"b":{"aa":"baz", "bb":300}}]
+54 David {"age":32, "city":"Chicago", "email":"[email protected]",
"phone":987654} 2 [{"a":8, "b":{"aa":"qux", "bb":400}}]
+
+-- !test_15 --
+41 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 1 [{"a":1, "b":null}, {"a":1, "b":null}] {"a":1,
"b":null}
+51 Alice {"age":25, "city":"New York", "email":"[email protected]",
"phone":123456} 1 [{"a":1, "b":{"aa":"foo", "bb":100}}, {"a":1,
"b":{"aa":"foo", "bb":100}}] {"a":1, "b":{"aa":"foo", "bb":100}}
+
+-- !test_16 --
+[{"a":2, "b":null}, {"a":1, "b":null}]
+
+-- !test_17 --
+{"age":25, "city":"New York", "email":"[email protected]", "phone":123456}
+{"age":25, "city":"New York", "email":"[email protected]", "phone":123456}
+{"age":25, "city":"New York", "email":"[email protected]", "phone":123456}
+
+-- !test_18 --
+{"age":25, "city":"New York", "email":null, "phone":null}
+
+-- !test_19 --
+{"age":25, "city":"New York", "email":"[email protected]", "phone":123456}
31
+{"age":25, "city":"New York", "email":"[email protected]", "phone":123456}
41
+{"age":25, "city":"New York", "email":"[email protected]", "phone":123456}
51
+
+-- !test_20 --
+{"age":25, "city":"New York", "email":null, "phone":null} 1
+
+-- !test_21 --
+0 3
+2 4
+1 7
+\N 16
+
diff --git
a/regression-test/suites/external_table_p0/hive/test_hive_struct_add_column.groovy
b/regression-test/suites/external_table_p0/hive/test_hive_struct_add_column.groovy
new file mode 100644
index 00000000000..143a98195d1
--- /dev/null
+++
b/regression-test/suites/external_table_p0/hive/test_hive_struct_add_column.groovy
@@ -0,0 +1,169 @@
+// 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("test_hive_struct_add_column",
"all_types,p0,external,hive,external_docker,external_docker_hive") {
+
+
+ String enabled = context.config.otherConfigs.get("enableHiveTest")
+ if (enabled != null && enabled.equalsIgnoreCase("true")) {
+ String hivePrefix ="hive3";
+ setHivePrefix(hivePrefix)
+ String externalEnvIp = context.config.otherConfigs.get("externalEnvIp")
+ String hmsPort = context.config.otherConfigs.get(hivePrefix +
"HmsPort")
+ String hdfs_port = context.config.otherConfigs.get(hivePrefix +
"HdfsPort")
+
+ String catalog_name = "test_hive_struct_add_column"
+ sql """drop catalog if exists ${catalog_name};"""
+ sql """
+ create catalog if not exists ${catalog_name} properties (
+ 'type'='hms',
+ 'hadoop.username' = 'hadoop',
+ 'fs.defaultFS' = 'hdfs://${externalEnvIp}:${hdfs_port}',
+ 'hive.metastore.uris' = 'thrift://${externalEnvIp}:${hmsPort}'
+ );
+ """
+
+ sql """use `${catalog_name}`.`default`"""
+
+ qt_desc """ desc test_hive_struct_add_column_orc;"""
+ qt_test_1 """ select * from test_hive_struct_add_column_orc order by
id;"""
+ qt_test_2 """ select * from test_hive_struct_add_column_orc where id
= 1 order by id;"""
+ qt_test_3 """ select * from test_hive_struct_add_column_orc where
complex is null order by id;"""
+ qt_test_4 """ select * from test_hive_struct_add_column_orc where
complex is not null order by id"""
+ qt_test_5 """ select complex from test_hive_struct_add_column_orc
where complex is null order by id """
+ qt_test_6 """ select complex from test_hive_struct_add_column_orc
where complex is not null order by id """
+ qt_test_7 """select complex from test_hive_struct_add_column_orc
where complex is null order by id; """
+ qt_test_8 """select complex from test_hive_struct_add_column_orc
where complex is not null order by id;"""
+ qt_test_9 """select sex from test_hive_struct_add_column_orc where
sex = 0 order by id;"""
+ qt_test_10 """select sex from test_hive_struct_add_column_orc where
sex = 1 order by id;"""
+ qt_test_11 """select sex from test_hive_struct_add_column_orc where
sex = 2 order by id;"""
+ qt_test_12 """select * from test_hive_struct_add_column_orc where
sex = 2 order by id; """
+ qt_test_13 """select * from test_hive_struct_add_column_orc where
id =sex order by id;"""
+ qt_test_14 """select * from test_hive_struct_add_column_orc where
id -52=sex order by id;"""
+ qt_test_15 """select *,complex[1] from
test_hive_struct_add_column_orc where struct_element(complex[1],1) = 1 order by
id;"""
+ qt_test_16 """ select complex from test_hive_struct_add_column_orc
where struct_element(complex[1],1) = 2 and struct_element(complex[1],2) is null
order by id ; """
+ qt_test_17 """select details from test_hive_struct_add_column_orc
where struct_element(details,1) = 25 and struct_element(details,4) is not null
order by id;"""
+ qt_test_18 """select details from test_hive_struct_add_column_orc
where struct_element(details,1) = 25 and struct_element(details,4) is null
order by id;"""
+ qt_test_19 """ select details,id from
test_hive_struct_add_column_orc where struct_element(details,1) = 25 and
struct_element(details,4) is not null order by id ;"""
+ qt_test_20 """ select details,id from
test_hive_struct_add_column_orc where struct_element(details,1) = 25 and
struct_element(details,4) is null order by id;"""
+ qt_test_21 """ select sex,count(*) from
test_hive_struct_add_column_orc group by sex order by count(*);"""
+
+
+
+ qt_desc """ desc test_hive_struct_add_column_parquet;"""
+ qt_test_1 """ select * from test_hive_struct_add_column_parquet order
by id;"""
+ qt_test_2 """ select * from test_hive_struct_add_column_parquet where
id = 1 order by id;"""
+ qt_test_3 """ select * from test_hive_struct_add_column_parquet where
complex is null order by id;"""
+ qt_test_4 """ select * from test_hive_struct_add_column_parquet where
complex is not null order by id"""
+ qt_test_5 """ select complex from test_hive_struct_add_column_parquet
where complex is null order by id """
+ qt_test_6 """ select complex from test_hive_struct_add_column_parquet
where complex is not null order by id """
+ qt_test_7 """select complex from test_hive_struct_add_column_parquet
where complex is null order by id; """
+ qt_test_8 """select complex from test_hive_struct_add_column_parquet
where complex is not null order by id;"""
+ qt_test_9 """select sex from test_hive_struct_add_column_parquet
where sex = 0 order by id;"""
+ qt_test_10 """select sex from test_hive_struct_add_column_parquet
where sex = 1 order by id;"""
+ qt_test_11 """select sex from test_hive_struct_add_column_parquet
where sex = 2 order by id;"""
+ qt_test_12 """select * from test_hive_struct_add_column_parquet
where sex = 2 order by id; """
+ qt_test_13 """select * from test_hive_struct_add_column_parquet
where id =sex order by id;"""
+ qt_test_14 """select * from test_hive_struct_add_column_parquet
where id -52=sex order by id;"""
+ qt_test_15 """select *,complex[1] from
test_hive_struct_add_column_parquet where struct_element(complex[1],1) = 1
order by id;"""
+ qt_test_16 """ select complex from
test_hive_struct_add_column_parquet where struct_element(complex[1],1) = 2 and
struct_element(complex[1],2) is null order by id ; """
+ qt_test_17 """select details from
test_hive_struct_add_column_parquet where struct_element(details,1) = 25 and
struct_element(details,4) is not null order by id;"""
+ qt_test_18 """select details from
test_hive_struct_add_column_parquet where struct_element(details,1) = 25 and
struct_element(details,4) is null order by id;"""
+ qt_test_19 """ select details,id from
test_hive_struct_add_column_parquet where struct_element(details,1) = 25 and
struct_element(details,4) is not null order by id ;"""
+ qt_test_20 """ select details,id from
test_hive_struct_add_column_parquet where struct_element(details,1) = 25 and
struct_element(details,4) is null order by id;"""
+ qt_test_21 """ select sex,count(*) from
test_hive_struct_add_column_parquet group by sex order by count(*);"""
+
+
+
+ sql """drop catalog if exists ${catalog_name}"""
+ }
+}
+
+/*
+drop table user_info_orc;
+CREATE TABLE user_info_orc (
+ id INT,
+ name STRING,
+ details STRUCT<age:INT, city:STRING>
+)
+stored as orc;
+INSERT INTO TABLE user_info_orc
+VALUES
+ (1, 'Alice', named_struct('age', 25, 'city', 'New York')),
+ (2, 'Blice', named_struct('age', 26, 'city', 'New York New York')),
+ (3, 'Clice', named_struct('age', 27, 'city', 'New York New York New
York')),
+ (4, 'Dlice', named_struct('age', 28, 'city', 'New York New York New York
New York')),
+ (5, 'Elice', named_struct('age', 29, 'city', 'New York New York New York
New York New York'));
+ALTER TABLE user_info_orc CHANGE COLUMN details details STRUCT<age:INT,
city:STRING, email:STRING>;
+INSERT INTO TABLE user_info_orc
+VALUES
+ (11, 'AAlice', named_struct('age', 125, 'city', 'acity', 'email',
'[email protected]')),
+ (12, 'BBlice', named_struct('age', 126, 'city', 'bcity', 'email',
'[email protected]')),
+ (13, 'CClice', named_struct('age', 127, 'city', 'ccity', 'email',
'[email protected]')),
+ (14, 'DDlice', named_struct('age', 128, 'city', 'dcity', 'email',
'[email protected]')),
+ (15, 'EElice', named_struct('age', 129, 'city', 'ecity', 'email', NULL));
+ALTER TABLE user_info_orc CHANGE COLUMN details details STRUCT<age:INT,
city:STRING, email:STRING, phone:int>;
+INSERT INTO user_info_orc
+VALUES
+ (21, 'Charlie', named_struct('age', 218, 'city', 'San Francisco', 'email',
'[email protected]','phone',123)),
+ (22, 'Charlie', named_struct('age', 228, 'city', 'San-Francisco', 'email',
'[email protected]','phone',1234)),
+ (23, 'Charlie', named_struct('age', 238, 'city', 'SanxFrancisco', 'email',
'[email protected]','phone',12345)),
+ (24, 'Charlie', named_struct('age', 248, 'city', 'San888Francisco',
'email', '[email protected]','phone',123456)),
+ (25, 'Charlie', named_struct('age', 258, 'city', 'San0000Francisco',
'email', '[email protected]','phone',NULL));
+desc user_info_orc;
+ALTER TABLE user_info_orc add columns (sex int);
+INSERT INTO TABLE user_info_orc
+VALUES
+ (31, 'Alice', named_struct('age', 25, 'city', 'New York', 'email',
'[email protected]', 'phone', 123456),0),
+ (32, 'Bob', named_struct('age', 30, 'city', 'Los Angeles', 'email',
'[email protected]', 'phone', 789012),0),
+ (33, 'Charlie', named_struct('age', 28, 'city', 'San Francisco', 'email',
'[email protected]', 'phone', 456789),1),
+ (34, 'David', named_struct('age', 32, 'city', 'Chicago', 'email',
'[email protected]', 'phone', 987654),0),
+ (35, 'Eve', named_struct('age', 27, 'city', 'Seattle', 'email',
'[email protected]', 'phone', NULL),NULL);
+ALTER TABLE user_info_orc add columns (complex array<struct<a:int>>);
+INSERT INTO TABLE user_info_orc
+VALUES
+ (41,'Alice', named_struct('age', 25, 'city', 'New York', 'email',
'[email protected]', 'phone', 123456), 1, array(named_struct('a',
1),named_struct('a', 1))),
+ (42,'Bob', named_struct('age', 30, 'city', 'Los Angeles', 'email',
'[email protected]', 'phone', 789012), 1, array(named_struct('a',
2),named_struct('a', 1))),
+ (43,'Charlie', named_struct('age', 28, 'city', 'San Francisco', 'email',
'[email protected]', 'phone', 456789), 2, array(named_struct('a',
3),named_struct('a', 1))),
+ (44,'David', named_struct('age', 32, 'city', 'Chicago', 'email',
'[email protected]', 'phone', 987654), 1, array(named_struct('a',
4),named_struct('a', 1))),
+ (45,'Eve', named_struct('age', 27, 'city', 'Seattle', 'email',
'[email protected]', 'phone', 654321), 2, array(named_struct('a',
5),named_struct('a', 1)));
+
+ALTER TABLE user_info_orc CHANGE COLUMN complex complex
array<struct<a:int,b:struct<aa:string,bb:int>>>;
+INSERT INTO TABLE user_info_orc
+VALUES
+ (51, 'Alice', named_struct('age', 25, 'city', 'New York', 'email',
'[email protected]', 'phone', 123456), 1, array(named_struct('a', 1, 'b',
named_struct('aa', 'foo', 'bb', 100)),named_struct('a', 1, 'b',
named_struct('aa', 'foo', 'bb', 100)))),
+ (52, 'Bob', named_struct('age', 30, 'city', 'Los Angeles', 'email',
'[email protected]', 'phone', 789012), 2, array(named_struct('a', 2, 'b',
named_struct('aa', 'bar', 'bb', 200)))),
+ (53, 'Charlie', named_struct('age', 28, 'city', 'San Francisco', 'email',
'[email protected]', 'phone', 456789), 1, array(named_struct('a', 3, 'b',
named_struct('aa', 'baz', 'bb', 300)))),
+ (54, 'David', named_struct('age', 32, 'city', 'Chicago', 'email',
'[email protected]', 'phone', 987654), 2, array(named_struct('a', 8, 'b',
named_struct('aa', 'qux', 'bb', 400)))),
+ (55, 'Eve', named_struct('age', 27, 'city', 'Seattle', 'email',
'[email protected]', 'phone', 654321), 1, array(named_struct('a', 5, 'b',
named_struct('aa', 'abcd', 'bb', 500)),named_struct('a', 5, 'b',
named_struct('aa', 'abcdffff', 'bb', 5000)),named_struct('a', 5, 'b',
named_struct('aa', 'abcdtttt', 'bb', 500000))));
+
+
+cp user_info_orc/ => test_hive_struct_add_column_orc/
+
+create table test_hive_struct_add_column_orc (
+ `id` int,
+ `name` string,
+ `details` struct<age:int,city:string,email:string,phone:int>,
+ `sex` int,
+ `complex` array<struct<a:int,b:struct<aa:string,bb:int>>>
+)
+STORED AS ORC;
+LOCATION
'/user/doris/preinstalled_data/orc_table/test_hive_struct_add_column_orc';
+
+*/
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
