This is an automated email from the ASF dual-hosted git repository.
eldenmoon pushed a commit to branch variant-sparse
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/variant-sparse by this push:
new ce387384284 [fix](case) and case sc and insert into select for variant
(#50278)
ce387384284 is described below
commit ce3873842840fbff1ea8df47c6c4a0e42e0c111e
Author: Sun Chenyang <suncheny...@selectdb.com>
AuthorDate: Thu Apr 24 15:17:11 2025 +0800
[fix](case) and case sc and insert into select for variant (#50278)
---
.../variant_github_events_index_type_p2/load.out | Bin 28422 -> 28513 bytes
.../predefine/test_prdefine_insert_into_select.out | Bin 3574 -> 5877 bytes
.../predefine/test_predefine_schema_change.out | Bin 0 -> 2195 bytes
.../load.groovy | 119 +++++++++++++--------
.../test_prdefine_insert_into_select.groovy | 21 +++-
.../predefine/test_predefine_schema_change.groovy | 53 +++++++++
6 files changed, 142 insertions(+), 51 deletions(-)
diff --git a/regression-test/data/variant_github_events_index_type_p2/load.out
b/regression-test/data/variant_github_events_index_type_p2/load.out
index 04c1b568405..4bc0ce42f6c 100644
Binary files
a/regression-test/data/variant_github_events_index_type_p2/load.out and
b/regression-test/data/variant_github_events_index_type_p2/load.out differ
diff --git
a/regression-test/data/variant_p0/predefine/test_prdefine_insert_into_select.out
b/regression-test/data/variant_p0/predefine/test_prdefine_insert_into_select.out
index 32b98feff53..99d6c6401dd 100644
Binary files
a/regression-test/data/variant_p0/predefine/test_prdefine_insert_into_select.out
and
b/regression-test/data/variant_p0/predefine/test_prdefine_insert_into_select.out
differ
diff --git
a/regression-test/data/variant_p0/predefine/test_predefine_schema_change.out
b/regression-test/data/variant_p0/predefine/test_predefine_schema_change.out
new file mode 100644
index 00000000000..4a6ac1184bf
Binary files /dev/null and
b/regression-test/data/variant_p0/predefine/test_predefine_schema_change.out
differ
diff --git
a/regression-test/suites/variant_github_events_index_type_p2/load.groovy
b/regression-test/suites/variant_github_events_index_type_p2/load.groovy
index f8688cf2d0d..02ddc5517b1 100644
--- a/regression-test/suites/variant_github_events_index_type_p2/load.groovy
+++ b/regression-test/suites/variant_github_events_index_type_p2/load.groovy
@@ -165,7 +165,8 @@ suite("test_variant_github_events_index_type_p2",
"nonConcurrent,p2"){
MATCH_NAME 'payload.issue.number' : int,
MATCH_NAME 'payload.comment.body' : string,
MATCH_NAME 'type.name' : string
- > NULL
+ > NULL,
+ INDEX idx_var (`v`) USING INVERTED PROPERTIES("parser" =
"english", "support_phrase" = "true")
)
DUPLICATE KEY(`k`)
DISTRIBUTED BY HASH(k) BUCKETS 4
@@ -178,28 +179,12 @@ suite("test_variant_github_events_index_type_p2",
"nonConcurrent,p2"){
load_json_data.call(table_name, """${getS3Url() +
'/regression/gharchive.m/2015-01-01-2.json'}""")
load_json_data.call(table_name, """${getS3Url() +
'/regression/gharchive.m/2015-01-01-3.json'}""")
-
-
- // // // build inverted index at middle of loading the data
- // // // ADD INDEX
- sql """ ALTER TABLE github_events ADD INDEX idx_var (`v`) USING INVERTED
PROPERTIES("parser" = "english", "support_phrase" = "true") """
- wait_for_latest_op_on_table_finish("github_events", timeout)
-
// // // 2022
load_json_data.call(table_name, """${getS3Url() +
'/regression/gharchive.m/2022-11-07-16.json'}""")
load_json_data.call(table_name, """${getS3Url() +
'/regression/gharchive.m/2022-11-07-10.json'}""")
load_json_data.call(table_name, """${getS3Url() +
'/regression/gharchive.m/2022-11-07-22.json'}""")
load_json_data.call(table_name, """${getS3Url() +
'/regression/gharchive.m/2022-11-07-23.json'}""")
- if (!isCloudMode()) {
- // BUILD INDEX and expect state is FINISHED
- sql """ BUILD INDEX idx_var ON github_events"""
- def state = wait_for_last_build_index_on_table_finish("github_events",
timeout)
- assertEquals("FINISHED", state)
- }
-
-
-
// // // add bloom filter at the end of loading data
// def tablets = sql_return_maparray """ show tablets from github_events;
"""
@@ -212,36 +197,78 @@ suite("test_variant_github_events_index_type_p2",
"nonConcurrent,p2"){
qt_sql """select cast(v["payload"]["pull_request"]["additions"] as int)
from github_events where cast(v["repo"]["name"] as string) =
'xpressengine/xe-core' order by 1;"""
qt_sql """select * from github_events where cast(v["repo"]["name"] as
string) = 'xpressengine/xe-core' order by 1 limit 10"""
sql """select * from github_events order by k limit 10"""
+ qt_sql """select count() from github_events where v["repo"]["name"] match
'xpressengine' """
qt_sql """select count() from github_events where v["repo"]["name"] match
'apache';"""
- // sql "DROP TABLE IF EXISTS github_events2"
- // sql """
- // CREATE TABLE IF NOT EXISTS github_events2 (
- // k bigint,
- // v variant<
- // MATCH_NAME 'repo.name' : string,
- // MATCH_NAME 'payload.pull_request.additions' : int,
- // MATCH_NAME 'actor.login' : string,
- // MATCH_NAME 'type' : string,
- // MATCH_NAME 'payload.action' : string,
- // MATCH_NAME 'created_at' : datetime,
- // MATCH_NAME 'payload.issue.number' : int,
- // MATCH_NAME 'payload.comment.body' : string,
- // MATCH_NAME 'type.name' : string
- // > null,
- // INDEX idx_repo_name(v) USING INVERTED PROPERTIES("parser" =
"unicode", "field_pattern" = "repo.name") COMMENT ''
- // )
- // UNIQUE KEY(`k`)
- // DISTRIBUTED BY HASH(k) BUCKETS 4
- // properties("replication_num" = "1", "disable_auto_compaction" =
"false", "variant_enable_flatten_nested" = "false", "bloom_filter_columns" =
"v", "variant_max_subcolumns_count" = "${rand_subcolumns_count}");
- // """
- // sql """insert into github_events2 select * from github_events order by
k"""
- // sql """select v['payload']['commits'] from github_events order by k ;"""
- // sql """select v['payload']['commits'] from github_events2 order by k
;"""
- // qt_sql """select count() from github_events2"""
- // // query with inverted index
- // sql """ set enable_match_without_inverted_index = false """
- // qt_sql """select count() from github_events2 where
cast(v["repo"]["name"] match 'xpressengine'"""
- // qt_sql """select count() from github_events2 where v["repo"]["name"]
match 'apache';"""
+
+ sql "DROP TABLE IF EXISTS github_events2"
+ sql """
+ CREATE TABLE IF NOT EXISTS github_events2 (
+ k bigint,
+ v variant<
+ MATCH_NAME 'repo.name' : string,
+ MATCH_NAME 'payload.pull_request.additions' : int,
+ MATCH_NAME 'actor.login' : string,
+ MATCH_NAME 'type' : string,
+ MATCH_NAME 'payload.action' : string,
+ MATCH_NAME 'created_at' : datetime,
+ MATCH_NAME 'payload.issue.number' : int,
+ MATCH_NAME 'payload.comment.body' : string,
+ MATCH_NAME 'type.name' : string
+ > null,
+ INDEX idx_repo_name(v) USING INVERTED PROPERTIES("parser" =
"english", "field_pattern" = "repo.name") COMMENT ''
+ )
+ UNIQUE KEY(`k`)
+ DISTRIBUTED BY HASH(k) BUCKETS 4
+ properties("replication_num" = "1", "disable_auto_compaction" =
"false", "variant_enable_flatten_nested" = "false", "bloom_filter_columns" =
"v", "variant_max_subcolumns_count" = "${rand_subcolumns_count}");
+ """
+ sql """insert into github_events2 select * from github_events order by k"""
+ sql """select v['payload']['commits'] from github_events order by k ;"""
+ sql """select v['payload']['commits'] from github_events2 order by k ;"""
+ qt_sql """select count() from github_events2"""
+ // query with inverted index
+ sql """ set enable_match_without_inverted_index = false """
+ qt_sql """select count() from github_events2 where v["repo"]["name"]
match 'xpressengine' """
+ qt_sql """select count() from github_events2 where v["repo"]["name"]
match 'apache';"""
+
+
+ sql "DROP TABLE IF EXISTS github_events3"
+ sql """
+ CREATE TABLE IF NOT EXISTS github_events3 (
+ k bigint,
+ v variant<
+ MATCH_NAME 'repo.name' : string,
+ MATCH_NAME 'payload.pull_request.additions' : int,
+ MATCH_NAME 'actor.login' : string,
+ MATCH_NAME 'type' : string,
+ MATCH_NAME 'payload.action' : string,
+ MATCH_NAME 'created_at' : datetime,
+ MATCH_NAME 'payload.issue.number' : int,
+ MATCH_NAME 'payload.comment.body' : string,
+ MATCH_NAME 'type.name' : string
+ > null,
+ INDEX idx_repo_name(v) USING INVERTED PROPERTIES("parser" =
"english", "field_pattern" = "repo.name") COMMENT ''
+ )
+ DUPLICATE KEY(`k`)
+ DISTRIBUTED BY HASH(k) BUCKETS 4
+ properties("replication_num" = "1", "disable_auto_compaction" =
"false", "variant_enable_flatten_nested" = "false", "bloom_filter_columns" =
"v", "variant_max_subcolumns_count" = "${rand_subcolumns_count}");
+ """
+ sql """insert into github_events3 select * from github_events order by k"""
+ // query with inverted index
+ sql """ set enable_match_without_inverted_index = false """
+ qt_sql """select count() from github_events3 where v["repo"]["name"]
match 'xpressengine' """
+ qt_sql """select count() from github_events3 where v["repo"]["name"]
match 'apache';"""
+
+
+ sql """ drop table if exists github_events4 """
+ sql """ create table github_events4 like github_events """
+ sql """ insert into github_events4 select * from github_events order by k
"""
+ sql """ drop table github_events """
+ sql """ alter table github_events4 rename github_events """
+ sql """ alter table github_events set ("bloom_filter_columns" = "v"); """
+ waitForSchemaChangeDone {
+ sql """ SHOW ALTER TABLE COLUMN WHERE TableName='github_events' ORDER
BY createtime DESC LIMIT 1 """
+ time 600
+ }
// specify schema
// sql "alter table github_events2 modify column v
variant<`payload.comment.id`:int,`payload.commits.url`:text,`payload.forkee.has_pages`:tinyint>"
diff --git
a/regression-test/suites/variant_p0/predefine/test_prdefine_insert_into_select.groovy
b/regression-test/suites/variant_p0/predefine/test_prdefine_insert_into_select.groovy
index 643bb952974..d7f384e6d8d 100644
---
a/regression-test/suites/variant_p0/predefine/test_prdefine_insert_into_select.groovy
+++
b/regression-test/suites/variant_p0/predefine/test_prdefine_insert_into_select.groovy
@@ -22,11 +22,14 @@ suite("test_predefine_insert_into_select", "p0"){
`var` variant<
MATCH_NAME 'a' : date,
MATCH_NAME 'b' : decimal(20,12),
- MATCH_NAME 'c' : datetime
- > NULL
+ MATCH_NAME 'c' : datetime,
+ MATCH_NAME 'd' : string
+ > NULL,
+ INDEX idx_a_b (var) USING INVERTED PROPERTIES("field_pattern"="d",
"parser"="unicode", "support_phrase" = "true") COMMENT ''
) ENGINE=OLAP DUPLICATE KEY(`id`) DISTRIBUTED BY HASH(`id`)
BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default:
1", "disable_auto_compaction" = "true", "variant_max_subcolumns_count" = "2")"""
- sql """insert into fromTable values(1, '{"a": "2025-04-16", "b":
123.123456789012, "c": "2025-04-17T09::09::09Z", "d": "2025-04-18", "e":
"2025-04-19", "f": "2025-04-20", "g": "2025-04-21", "h": "2025-04-22", "i":
"2025-04-23", "j": "2025-04-24", "k": "2025-04-25", "l": "2025-04-26", "m":
"2025-04-27", "n": "2025-04-28", "o": "2025-04-29", "p": "2025-04-30"}');"""
+ sql """insert into fromTable values(1, '{"a": "2025-04-16", "b":
123.123456789012, "c": "2025-04-17T09::09::09Z", "d": 123, "e": "2025-04-19",
"f": "2025-04-20", "g": "2025-04-21", "h": "2025-04-22", "i": "2025-04-23",
"j": "2025-04-24", "k": "2025-04-25", "l": "2025-04-26", "m": "2025-04-27",
"n": "2025-04-28", "o": "2025-04-29", "p": "2025-04-30"}');"""
+ sql """insert into fromTable values(1, '{"a": "2025-04-16", "b":
123.123456789012, "c": "2025-04-17T09::09::09Z", "d": 123, "e": "2025-04-19",
"f": "2025-04-20", "g": "2025-04-21", "h": "2025-04-22", "i": "2025-04-23",
"j": "2025-04-24", "k": "2025-04-25", "l": "2025-04-26", "m": "2025-04-27",
"n": "2025-04-28", "o": "2025-04-29", "p": "2025-04-30"}');"""
sql "DROP TABLE IF EXISTS toTable_without_define"
sql """CREATE TABLE toTable_without_define (
@@ -34,7 +37,7 @@ suite("test_predefine_insert_into_select", "p0"){
`var` variant NULL
) ENGINE=OLAP DUPLICATE KEY(`id`) DISTRIBUTED BY HASH(`id`)
BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default:
1", "disable_auto_compaction" = "true", "variant_max_subcolumns_count" = "2")"""
- sql """insert into toTable_without_define values(1, '{"a": "2025-04-16",
"b": 123.123456789012, "c": "2025-04-17T09::09::09Z", "d": "2025-04-18", "e":
"2025-04-19", "f": "2025-04-20", "g": "2025-04-21", "h": "2025-04-22", "i":
"2025-04-23", "j": "2025-04-24", "k": "2025-04-25", "l": "2025-04-26", "m":
"2025-04-27", "n": "2025-04-28", "o": "2025-04-29", "p": "2025-04-30"}');"""
+ sql """insert into toTable_without_define values(1, '{"a": "2025-04-16",
"b": 123.123456789012, "c": "2025-04-17T09::09::09Z", "d": 123, "e":
"2025-04-19", "f": "2025-04-20", "g": "2025-04-21", "h": "2025-04-22", "i":
"2025-04-23", "j": "2025-04-24", "k": "2025-04-25", "l": "2025-04-26", "m":
"2025-04-27", "n": "2025-04-28", "o": "2025-04-29", "p": "2025-04-30"}');"""
sql """ insert into toTable_without_define select id, cast(var as string)
from fromTable"""
boolean findException = false
@@ -58,7 +61,7 @@ suite("test_predefine_insert_into_select", "p0"){
> NULL
) ENGINE=OLAP DUPLICATE KEY(`id`) DISTRIBUTED BY HASH(`id`)
BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default:
1", "disable_auto_compaction" = "true", "variant_max_subcolumns_count" = "2")"""
- sql """insert into toTable_with_define values(1, '{"a": "2025-04-16", "b":
123.123456789012, "c": "2025-04-17T09::09::09Z", "d": "2025-04-18", "e":
"2025-04-19", "f": "2025-04-20", "g": "2025-04-21", "h": "2025-04-22", "i":
"2025-04-23", "j": "2025-04-24", "k": "2025-04-25", "l": "2025-04-26", "m":
"2025-04-27", "n": "2025-04-28", "o": "2025-04-29", "p": "2025-04-30"}');"""
+ sql """insert into toTable_with_define values(1, '{"a": "2025-04-16", "b":
123.123456789012, "c": "2025-04-17T09::09::09Z", "d": 123, "e": "2025-04-19",
"f": "2025-04-20", "g": "2025-04-21", "h": "2025-04-22", "i": "2025-04-23",
"j": "2025-04-24", "k": "2025-04-25", "l": "2025-04-26", "m": "2025-04-27",
"n": "2025-04-28", "o": "2025-04-29", "p": "2025-04-30"}');"""
sql """ insert into toTable_with_define select id, cast(var as string)
from fromTable"""
@@ -79,4 +82,12 @@ suite("test_predefine_insert_into_select", "p0"){
qt_sql """ insert into toTable select * from fromTable"""
qt_sql """ select * from toTable"""
qt_sql """ select variant_type(var) from toTable"""
+
+ sql """insert into toTable values(1, '{"a": "2025-04-16", "b":
123.123456789012, "c": "2025-04-17T09::09::09Z", "d": 123, "e": "2025-04-19",
"f": "2025-04-20", "g": "2025-04-21", "h": "2025-04-22", "i": "2025-04-23",
"j": "2025-04-24", "k": "2025-04-25", "l": "2025-04-26", "m": "2025-04-27",
"n": "2025-04-28", "o": "2025-04-29", "p": "2025-04-30"}');"""
+
+ qt_sql """ select variant_type(var) from toTable"""
+
+ sql """ set enable_match_without_inverted_index = false """
+ sql """ set enable_common_expr_pushdown = true """
+ qt_sql """ select count() from toTable where cast (var['d'] as string)
match '123' """
}
\ No newline at end of file
diff --git
a/regression-test/suites/variant_p0/predefine/test_predefine_schema_change.groovy
b/regression-test/suites/variant_p0/predefine/test_predefine_schema_change.groovy
new file mode 100644
index 00000000000..d2129ad5294
--- /dev/null
+++
b/regression-test/suites/variant_p0/predefine/test_predefine_schema_change.groovy
@@ -0,0 +1,53 @@
+// 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_predefine_schema_change", "p0"){
+ def tableName = "test_predefine_schema_change"
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql """CREATE TABLE ${tableName} (
+ `id` bigint NULL,
+ `var` variant<
+ MATCH_NAME 'a' : date,
+ MATCH_NAME 'b' : decimal(20,12),
+ MATCH_NAME 'c' : datetime,
+ MATCH_NAME 'd' : string
+ > NULL,
+ `col1` varchar(100) NOT NULL,
+ INDEX idx_a_b (var) USING INVERTED PROPERTIES("field_pattern"="d",
"parser"="unicode", "support_phrase" = "true") COMMENT ''
+ ) ENGINE=OLAP DUPLICATE KEY(`id`) DISTRIBUTED BY HASH(`id`)
+ BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default:
1", "disable_auto_compaction" = "true", "variant_max_subcolumns_count" = "2")"""
+ sql """insert into ${tableName} values(1, '{"a": "2025-04-16", "b":
123.123456789012, "c": "2025-04-17T09::09::09Z", "d": 123, "e": "2025-04-19",
"f": "2025-04-20", "g": "2025-04-21", "h": "2025-04-22", "i": "2025-04-23",
"j": "2025-04-24", "k": "2025-04-25", "l": "2025-04-26", "m": "2025-04-27",
"n": "2025-04-28", "o": "2025-04-29", "p": "2025-04-30"}', 'col');"""
+ sql """insert into ${tableName} values(1, '{"a": "2025-04-16", "b":
123.123456789012, "c": "2025-04-17T09::09::09Z", "d": 123, "e": "2025-04-19",
"f": "2025-04-20", "g": "2025-04-21", "h": "2025-04-22", "i": "2025-04-23",
"j": "2025-04-24", "k": "2025-04-25", "l": "2025-04-26", "m": "2025-04-27",
"n": "2025-04-28", "o": "2025-04-29", "p": "2025-04-30"}', 'col');"""
+
+ sql """ set enable_match_without_inverted_index = false """
+ sql """ set enable_common_expr_pushdown = true """
+ qt_sql """ select count() from ${tableName} where cast (var['d'] as
string) match '123' """
+ qt_sql """ select * from ${tableName} """
+ qt_sql """ select variant_type(var) from ${tableName} """
+
+ sql """ alter table ${tableName} modify column col1 varchar(200) NULL """
+
+ waitForSchemaChangeDone {
+ sql """ SHOW ALTER TABLE COLUMN WHERE TableName='${tableName}' ORDER
BY createtime DESC LIMIT 1 """
+ time 60
+ }
+
+ qt_sql """ select count() from ${tableName} where cast (var['d'] as
string) match '123' """
+ qt_sql """ select * from ${tableName} """
+ qt_sql """ select variant_type(var) from ${tableName} """
+
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
