This is an automated email from the ASF dual-hosted git repository.
kxiao pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
commit 6a95750303c5c27982b3cd007701687637a9029c
Author: YueW <45946325+tany...@users.noreply.github.com>
AuthorDate: Sat Jul 8 22:23:09 2023 +0800
[opt](regression case) add more index change regression case (#21633)
---
.../index_change/test_index_change_1.out | 26 +++++
.../index_change/test_index_change_2.out | 43 ++++++++
.../index_change/test_index_change_3.out | 43 ++++++++
.../index_change/test_index_change_4.out | 43 ++++++++
.../index_change/test_index_change_5.out | 43 ++++++++
.../index_change/test_index_change_6.out | 43 ++++++++
.../index_change/test_index_change_1.groovy | 105 ++++++++++++++++++
.../index_change/test_index_change_2.groovy | 115 ++++++++++++++++++++
.../index_change/test_index_change_3.groovy | 117 ++++++++++++++++++++
.../index_change/test_index_change_4.groovy | 116 ++++++++++++++++++++
.../index_change/test_index_change_5.groovy | 109 +++++++++++++++++++
.../index_change/test_index_change_6.groovy | 121 +++++++++++++++++++++
12 files changed, 924 insertions(+)
diff --git
a/regression-test/data/inverted_index_p0/index_change/test_index_change_1.out
b/regression-test/data/inverted_index_p0/index_change/test_index_change_1.out
new file mode 100644
index 0000000000..008afcb51e
--- /dev/null
+++
b/regression-test/data/inverted_index_p0/index_change/test_index_change_1.out
@@ -0,0 +1,26 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select1 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+5 2017-10-02 Shenzhen China 10 1 teacher
+6 2017-10-03 Hongkong China 10 1 Architectural designer
+
+-- !select2 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+
+-- !select3 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+
+-- !select4 --
+
+-- !select5 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select6 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
diff --git
a/regression-test/data/inverted_index_p0/index_change/test_index_change_2.out
b/regression-test/data/inverted_index_p0/index_change/test_index_change_2.out
new file mode 100644
index 0000000000..ab3e0c076c
--- /dev/null
+++
b/regression-test/data/inverted_index_p0/index_change/test_index_change_2.out
@@ -0,0 +1,43 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select1 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+5 2017-10-02 Shenzhen China 10 1 teacher
+6 2017-10-03 Hongkong China 10 1 Architectural designer
+
+-- !select2 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+
+-- !select3 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+
+-- !select4 --
+
+-- !select5 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select6 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select7 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+
+-- !select8 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+
+-- !select9 --
+
+-- !select10 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select11 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
diff --git
a/regression-test/data/inverted_index_p0/index_change/test_index_change_3.out
b/regression-test/data/inverted_index_p0/index_change/test_index_change_3.out
new file mode 100644
index 0000000000..ab3e0c076c
--- /dev/null
+++
b/regression-test/data/inverted_index_p0/index_change/test_index_change_3.out
@@ -0,0 +1,43 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select1 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+5 2017-10-02 Shenzhen China 10 1 teacher
+6 2017-10-03 Hongkong China 10 1 Architectural designer
+
+-- !select2 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+
+-- !select3 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+
+-- !select4 --
+
+-- !select5 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select6 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select7 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+
+-- !select8 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+
+-- !select9 --
+
+-- !select10 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select11 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
diff --git
a/regression-test/data/inverted_index_p0/index_change/test_index_change_4.out
b/regression-test/data/inverted_index_p0/index_change/test_index_change_4.out
new file mode 100644
index 0000000000..ab3e0c076c
--- /dev/null
+++
b/regression-test/data/inverted_index_p0/index_change/test_index_change_4.out
@@ -0,0 +1,43 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select1 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+5 2017-10-02 Shenzhen China 10 1 teacher
+6 2017-10-03 Hongkong China 10 1 Architectural designer
+
+-- !select2 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+
+-- !select3 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+
+-- !select4 --
+
+-- !select5 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select6 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select7 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+
+-- !select8 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+
+-- !select9 --
+
+-- !select10 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select11 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
diff --git
a/regression-test/data/inverted_index_p0/index_change/test_index_change_5.out
b/regression-test/data/inverted_index_p0/index_change/test_index_change_5.out
new file mode 100644
index 0000000000..ab3e0c076c
--- /dev/null
+++
b/regression-test/data/inverted_index_p0/index_change/test_index_change_5.out
@@ -0,0 +1,43 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select1 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+5 2017-10-02 Shenzhen China 10 1 teacher
+6 2017-10-03 Hongkong China 10 1 Architectural designer
+
+-- !select2 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+
+-- !select3 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+
+-- !select4 --
+
+-- !select5 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select6 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select7 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+
+-- !select8 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+
+-- !select9 --
+
+-- !select10 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select11 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
diff --git
a/regression-test/data/inverted_index_p0/index_change/test_index_change_6.out
b/regression-test/data/inverted_index_p0/index_change/test_index_change_6.out
new file mode 100644
index 0000000000..ab3e0c076c
--- /dev/null
+++
b/regression-test/data/inverted_index_p0/index_change/test_index_change_6.out
@@ -0,0 +1,43 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select1 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+5 2017-10-02 Shenzhen China 10 1 teacher
+6 2017-10-03 Hongkong China 10 1 Architectural designer
+
+-- !select2 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+
+-- !select3 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+
+-- !select4 --
+
+-- !select5 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select6 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select7 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+4 2017-10-02 Beijing China 10 0 Both a teacher and a
scientist
+
+-- !select8 --
+1 2017-10-01 Beijing China 10 1 Software Developer
+2 2017-10-01 Beijing China 10 1 Communication Engineer
+
+-- !select9 --
+
+-- !select10 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
+-- !select11 --
+3 2017-10-01 Shanghai China 10 1 electrical engineer
+
diff --git
a/regression-test/suites/inverted_index_p0/index_change/test_index_change_1.groovy
b/regression-test/suites/inverted_index_p0/index_change/test_index_change_1.groovy
new file mode 100644
index 0000000000..7fa805c0ba
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/index_change/test_index_change_1.groovy
@@ -0,0 +1,105 @@
+// 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.
+
+import org.codehaus.groovy.runtime.IOGroovyMethods
+
+suite("test_index_change_1") {
+ def timeout = 60000
+ def delta_time = 1000
+ def alter_res = "null"
+ def useTime = 0
+ def wait_for_latest_op_on_table_finish = { table_name, OpTimeout ->
+ for(int t = delta_time; t <= OpTimeout; t += delta_time){
+ alter_res = sql """SHOW ALTER TABLE COLUMN WHERE TableName =
"${table_name}" ORDER BY CreateTime DESC LIMIT 1;"""
+ alter_res = alter_res.toString()
+ if(alter_res.contains("FINISHED")) {
+ sleep(3000) // wait change table state to normal
+ logger.info(table_name + " latest alter job finished, detail:
" + alter_res)
+ break
+ }
+ useTime = t
+ sleep(delta_time)
+ }
+ assertTrue(useTime <= OpTimeout, "wait_for_latest_op_on_table_finish
timeout")
+ }
+
+ def tableName = "test_index_change_1"
+
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ `user_id` LARGEINT NOT NULL COMMENT "用户id",
+ `date` DATE NOT NULL COMMENT "数据灌入日期时间",
+ `city` VARCHAR(20) COMMENT "用户所在城市",
+ `age` SMALLINT COMMENT "用户年龄",
+ `sex` TINYINT COMMENT "用户性别",
+ `note` TEXT COMMENT "备注")
+ DUPLICATE KEY(`user_id`, `date`, `city`, `age`, `sex`) DISTRIBUTED BY
HASH(`user_id`)
+ PROPERTIES ( "replication_num" = "1" );
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (1, '2017-10-01', 'Beijing China', 10, 1, 'Software Developer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (2, '2017-10-01', 'Beijing China', 10, 1, 'Communication Engineer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (3, '2017-10-01', 'Shanghai China', 10, 1, 'electrical engineer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (4, '2017-10-02', 'Beijing China', 10, 0, 'Both a teacher and a
scientist')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (5, '2017-10-02', 'Shenzhen China', 10, 1, 'teacher')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (6, '2017-10-03', 'Hongkong China', 10, 1, 'Architectural designer')
+ """
+
+ qt_select1 """ SELECT * FROM ${tableName} t ORDER BY
user_id,date,city,age,sex; """
+
+
//TabletId,ReplicaId,BackendId,SchemaHash,Version,LstSuccessVersion,LstFailedVersion,LstFailedTime,LocalDataSize,RemoteDataSize,RowCount,State,LstConsistencyCheckTime,CheckVersion,VersionCount,QueryHits,PathHash,MetaUrl,CompactionStatus
+ String[][] tablets = sql """ show tablets from ${tableName}; """
+ // create inverted index
+ sql """ CREATE INDEX idx_user_id ON ${tableName}(`user_id`) USING INVERTED
"""
+ sql """ CREATE INDEX idx_note ON ${tableName}(`note`) USING INVERTED
PROPERTIES("parser"="english") """
+ sql """ CREATE INDEX idx_city ON ${tableName}(`city`) USING INVERTED
PROPERTIES("parser"="english") """
+
+ // create bitmap index
+ sql """ CREATE INDEX idx_sex ON ${tableName}(`sex`) USING BITMAP """
+ wait_for_latest_op_on_table_finish(tableName, timeout)
+
+ def show_result = sql "show index from ${tableName}"
+ logger.info("show index from " + tableName + " result: " + show_result)
+ assertEquals(show_result.size(), 4)
+ assertEquals(show_result[0][2], "idx_user_id")
+ assertEquals(show_result[1][2], "idx_note")
+ assertEquals(show_result[2][2], "idx_city")
+ assertEquals(show_result[3][2], "idx_sex")
+
+ qt_select2 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing'
ORDER BY user_id; """
+ qt_select3 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing' and
sex = 1 ORDER BY user_id; """
+ qt_select4 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer'
and sex = 0 ORDER BY user_id; """
+ qt_select5 """ SELECT * FROM ${tableName} t WHERE note MATCH_PHRASE
'electrical engineer' ORDER BY user_id; """
+ qt_select6 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer
Developer' AND city match_all 'Shanghai China' ORDER BY user_id; """
+}
diff --git
a/regression-test/suites/inverted_index_p0/index_change/test_index_change_2.groovy
b/regression-test/suites/inverted_index_p0/index_change/test_index_change_2.groovy
new file mode 100644
index 0000000000..e80b805204
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/index_change/test_index_change_2.groovy
@@ -0,0 +1,115 @@
+// 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.
+
+import org.codehaus.groovy.runtime.IOGroovyMethods
+
+suite("test_index_change_2") {
+ def timeout = 60000
+ def delta_time = 1000
+ def alter_res = "null"
+ def useTime = 0
+ def wait_for_build_index_on_partition_finish = { table_name, OpTimeout ->
+ for(int t = delta_time; t <= OpTimeout; t += delta_time){
+ alter_res = sql """SHOW BUILD INDEX WHERE TableName =
"${table_name}";"""
+ def expected_finished_num = alter_res.size();
+ def finished_num = 0;
+ for (int i = 0; i < expected_finished_num; i++) {
+ logger.info(table_name + " build index job state: " +
alter_res[i][7] + i)
+ if (alter_res[i][7] == "FINISHED") {
+ ++finished_num;
+ }
+ }
+ if (finished_num == expected_finished_num) {
+ logger.info(table_name + " all build index jobs finished,
detail: " + alter_res)
+ break
+ }
+ useTime = t
+ sleep(delta_time)
+ }
+ assertTrue(useTime <= OpTimeout,
"wait_for_latest_build_index_on_partition_finish timeout")
+ }
+
+ def tableName = "test_index_change_2"
+
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ `user_id` LARGEINT NOT NULL COMMENT "用户id",
+ `date` DATE NOT NULL COMMENT "数据灌入日期时间",
+ `city` VARCHAR(20) COMMENT "用户所在城市",
+ `age` SMALLINT COMMENT "用户年龄",
+ `sex` TINYINT COMMENT "用户性别",
+ `note` TEXT COMMENT "备注",
+ INDEX idx_user_id (`user_id`) USING INVERTED COMMENT '',
+ INDEX idx_note (`note`) USING INVERTED PROPERTIES("parser" =
"english") COMMENT ''
+ )
+ DUPLICATE KEY(`user_id`, `date`, `city`, `age`, `sex`) DISTRIBUTED BY
HASH(`user_id`)
+ PROPERTIES ( "replication_num" = "1" );
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (1, '2017-10-01', 'Beijing China', 10, 1, 'Software Developer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (2, '2017-10-01', 'Beijing China', 10, 1, 'Communication Engineer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (3, '2017-10-01', 'Shanghai China', 10, 1, 'electrical engineer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (4, '2017-10-02', 'Beijing China', 10, 0, 'Both a teacher and a
scientist')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (5, '2017-10-02', 'Shenzhen China', 10, 1, 'teacher')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (6, '2017-10-03', 'Hongkong China', 10, 1, 'Architectural designer')
+ """
+
+ qt_select1 """ SELECT * FROM ${tableName} t ORDER BY
user_id,date,city,age,sex; """
+ qt_select2 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing'
ORDER BY user_id; """
+ qt_select3 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing' and
sex = 1 ORDER BY user_id; """
+ qt_select4 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer'
and sex = 0 ORDER BY user_id; """
+ qt_select5 """ SELECT * FROM ${tableName} t WHERE note MATCH_PHRASE
'electrical engineer' ORDER BY user_id; """
+ qt_select6 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer
Developer' AND city match_all 'Shanghai China' ORDER BY user_id; """
+
+
//TabletId,ReplicaId,BackendId,SchemaHash,Version,LstSuccessVersion,LstFailedVersion,LstFailedTime,LocalDataSize,RemoteDataSize,RowCount,State,LstConsistencyCheckTime,CheckVersion,VersionCount,QueryHits,PathHash,MetaUrl,CompactionStatus
+ String[][] tablets = sql """ show tablets from ${tableName}; """
+ // create inverted index idx_city
+ sql """ CREATE INDEX idx_city ON ${tableName}(`city`) USING INVERTED
PROPERTIES("parser"="english") """
+
+ // drop inverted index idx_user_id, idx_note
+ sql """ DROP INDEX idx_user_id ON ${tableName} """
+ sql """ DROP INDEX idx_note ON ${tableName} """
+ wait_for_build_index_on_partition_finish(tableName, timeout)
+
+ def show_result = sql "show index from ${tableName}"
+ logger.info("show index from " + tableName + " result: " + show_result)
+ assertEquals(show_result.size(), 1)
+ assertEquals(show_result[0][2], "idx_city")
+
+ qt_select7 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing'
ORDER BY user_id; """
+ qt_select8 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing' and
sex = 1 ORDER BY user_id; """
+ qt_select9 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer'
and sex = 0 ORDER BY user_id; """
+ qt_select10 """ SELECT * FROM ${tableName} t WHERE note MATCH_PHRASE
'electrical engineer' ORDER BY user_id; """
+ qt_select11 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer
Developer' AND city match_all 'Shanghai China' ORDER BY user_id; """
+}
diff --git
a/regression-test/suites/inverted_index_p0/index_change/test_index_change_3.groovy
b/regression-test/suites/inverted_index_p0/index_change/test_index_change_3.groovy
new file mode 100644
index 0000000000..deb77a028e
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/index_change/test_index_change_3.groovy
@@ -0,0 +1,117 @@
+// 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.
+
+import org.codehaus.groovy.runtime.IOGroovyMethods
+
+suite("test_index_change_3") {
+ def timeout = 60000
+ def delta_time = 1000
+ def alter_res = "null"
+ def useTime = 0
+ def wait_for_build_index_on_partition_finish = { table_name, OpTimeout ->
+ for(int t = delta_time; t <= OpTimeout; t += delta_time){
+ alter_res = sql """SHOW BUILD INDEX WHERE TableName =
"${table_name}";"""
+ def expected_finished_num = alter_res.size();
+ def finished_num = 0;
+ for (int i = 0; i < expected_finished_num; i++) {
+ logger.info(table_name + " build index job state: " +
alter_res[i][7] + i)
+ if (alter_res[i][7] == "FINISHED") {
+ ++finished_num;
+ }
+ }
+ if (finished_num == expected_finished_num) {
+ logger.info(table_name + " all build index jobs finished,
detail: " + alter_res)
+ break
+ }
+ useTime = t
+ sleep(delta_time)
+ }
+ assertTrue(useTime <= OpTimeout,
"wait_for_latest_build_index_on_partition_finish timeout")
+ }
+
+ def tableName = "test_index_change_3"
+
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ `user_id` LARGEINT NOT NULL COMMENT "用户id",
+ `date` DATE NOT NULL COMMENT "数据灌入日期时间",
+ `city` VARCHAR(20) COMMENT "用户所在城市",
+ `age` SMALLINT COMMENT "用户年龄",
+ `sex` TINYINT COMMENT "用户性别",
+ `note` TEXT COMMENT "备注",
+ INDEX idx_user_id (`user_id`) USING INVERTED COMMENT '',
+ INDEX idx_note (`note`) USING INVERTED PROPERTIES("parser" =
"english") COMMENT ''
+ )
+ DUPLICATE KEY(`user_id`, `date`, `city`, `age`, `sex`) DISTRIBUTED BY
HASH(`user_id`)
+ PROPERTIES ( "replication_num" = "1" );
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (1, '2017-10-01', 'Beijing China', 10, 1, 'Software Developer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (2, '2017-10-01', 'Beijing China', 10, 1, 'Communication Engineer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (3, '2017-10-01', 'Shanghai China', 10, 1, 'electrical engineer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (4, '2017-10-02', 'Beijing China', 10, 0, 'Both a teacher and a
scientist')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (5, '2017-10-02', 'Shenzhen China', 10, 1, 'teacher')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (6, '2017-10-03', 'Hongkong China', 10, 1, 'Architectural designer')
+ """
+
+ qt_select1 """ SELECT * FROM ${tableName} t ORDER BY
user_id,date,city,age,sex; """
+ qt_select2 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing'
ORDER BY user_id; """
+ qt_select3 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing' and
sex = 1 ORDER BY user_id; """
+ qt_select4 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer'
and sex = 0 ORDER BY user_id; """
+ qt_select5 """ SELECT * FROM ${tableName} t WHERE note MATCH_PHRASE
'electrical engineer' ORDER BY user_id; """
+ qt_select6 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer
Developer' AND city match_all 'Shanghai China' ORDER BY user_id; """
+
+
//TabletId,ReplicaId,BackendId,SchemaHash,Version,LstSuccessVersion,LstFailedVersion,LstFailedTime,LocalDataSize,RemoteDataSize,RowCount,State,LstConsistencyCheckTime,CheckVersion,VersionCount,QueryHits,PathHash,MetaUrl,CompactionStatus
+ String[][] tablets = sql """ show tablets from ${tableName}; """
+ // create inverted index idx_city
+ sql """ CREATE INDEX idx_city ON ${tableName}(`city`) USING INVERTED
PROPERTIES("parser"="english") """
+ // build index
+ sql """ BUILD INDEX idx_city ON ${tableName} """
+
+ // drop inverted index idx_user_id, idx_note
+ sql """ DROP INDEX idx_city ON ${tableName} """
+ sql """ DROP INDEX idx_note ON ${tableName} """
+ wait_for_build_index_on_partition_finish(tableName, timeout)
+
+ def show_result = sql "show index from ${tableName}"
+ logger.info("show index from " + tableName + " result: " + show_result)
+ assertEquals(show_result.size(), 1)
+ assertEquals(show_result[0][2], "idx_user_id")
+
+ qt_select7 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing'
ORDER BY user_id; """
+ qt_select8 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing' and
sex = 1 ORDER BY user_id; """
+ qt_select9 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer'
and sex = 0 ORDER BY user_id; """
+ qt_select10 """ SELECT * FROM ${tableName} t WHERE note MATCH_PHRASE
'electrical engineer' ORDER BY user_id; """
+ qt_select11 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer
Developer' AND city match_all 'Shanghai China' ORDER BY user_id; """
+}
diff --git
a/regression-test/suites/inverted_index_p0/index_change/test_index_change_4.groovy
b/regression-test/suites/inverted_index_p0/index_change/test_index_change_4.groovy
new file mode 100644
index 0000000000..78c24ad660
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/index_change/test_index_change_4.groovy
@@ -0,0 +1,116 @@
+// 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.
+
+import org.codehaus.groovy.runtime.IOGroovyMethods
+
+suite("test_index_change_4") {
+ def timeout = 60000
+ def delta_time = 1000
+ def alter_res = "null"
+ def useTime = 0
+ def wait_for_build_index_on_partition_finish = { table_name, OpTimeout ->
+ for(int t = delta_time; t <= OpTimeout; t += delta_time){
+ alter_res = sql """SHOW BUILD INDEX WHERE TableName =
"${table_name}";"""
+ def expected_finished_num = alter_res.size();
+ def finished_num = 0;
+ for (int i = 0; i < expected_finished_num; i++) {
+ logger.info(table_name + " build index job state: " +
alter_res[i][7] + i)
+ if (alter_res[i][7] == "FINISHED") {
+ ++finished_num;
+ }
+ }
+ if (finished_num == expected_finished_num) {
+ logger.info(table_name + " all build index jobs finished,
detail: " + alter_res)
+ break
+ }
+ useTime = t
+ sleep(delta_time)
+ }
+ assertTrue(useTime <= OpTimeout,
"wait_for_latest_build_index_on_partition_finish timeout")
+ }
+
+ def tableName = "test_index_change_4"
+
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ `user_id` LARGEINT NOT NULL COMMENT "用户id",
+ `date` DATE NOT NULL COMMENT "数据灌入日期时间",
+ `city` VARCHAR(20) COMMENT "用户所在城市",
+ `age` SMALLINT COMMENT "用户年龄",
+ `sex` TINYINT COMMENT "用户性别",
+ `note` TEXT COMMENT "备注",
+ INDEX idx_user_id (`user_id`) USING INVERTED COMMENT '',
+ INDEX idx_note (`note`) USING INVERTED PROPERTIES("parser" =
"english") COMMENT ''
+ )
+ DUPLICATE KEY(`user_id`, `date`, `city`, `age`, `sex`) DISTRIBUTED BY
HASH(`user_id`)
+ PROPERTIES ( "replication_num" = "1" );
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (1, '2017-10-01', 'Beijing China', 10, 1, 'Software Developer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (2, '2017-10-01', 'Beijing China', 10, 1, 'Communication Engineer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (3, '2017-10-01', 'Shanghai China', 10, 1, 'electrical engineer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (4, '2017-10-02', 'Beijing China', 10, 0, 'Both a teacher and a
scientist')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (5, '2017-10-02', 'Shenzhen China', 10, 1, 'teacher')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (6, '2017-10-03', 'Hongkong China', 10, 1, 'Architectural designer')
+ """
+
+ qt_select1 """ SELECT * FROM ${tableName} t ORDER BY
user_id,date,city,age,sex; """
+ qt_select2 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing'
ORDER BY user_id; """
+ qt_select3 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing' and
sex = 1 ORDER BY user_id; """
+ qt_select4 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer'
and sex = 0 ORDER BY user_id; """
+ qt_select5 """ SELECT * FROM ${tableName} t WHERE note MATCH_PHRASE
'electrical engineer' ORDER BY user_id; """
+ qt_select6 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer
Developer' AND city match_all 'Shanghai China' ORDER BY user_id; """
+
+
//TabletId,ReplicaId,BackendId,SchemaHash,Version,LstSuccessVersion,LstFailedVersion,LstFailedTime,LocalDataSize,RemoteDataSize,RowCount,State,LstConsistencyCheckTime,CheckVersion,VersionCount,QueryHits,PathHash,MetaUrl,CompactionStatus
+ String[][] tablets = sql """ show tablets from ${tableName}; """
+ // drop inverted index idx_user_id, idx_note
+ sql """ DROP INDEX idx_user_id ON ${tableName} """
+ sql """ DROP INDEX idx_note ON ${tableName} """
+ // create inverted index idx_city
+ sql """ CREATE INDEX idx_note ON ${tableName}(`note`) USING INVERTED
PROPERTIES("parser"="english") """
+ // build index
+ sql """ BUILD INDEX idx_note ON ${tableName} """
+ wait_for_build_index_on_partition_finish(tableName, timeout)
+
+ def show_result = sql "show index from ${tableName}"
+ logger.info("show index from " + tableName + " result: " + show_result)
+ assertEquals(show_result.size(), 1)
+ assertEquals(show_result[0][2], "idx_note")
+
+ qt_select7 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing'
ORDER BY user_id; """
+ qt_select8 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing' and
sex = 1 ORDER BY user_id; """
+ qt_select9 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer'
and sex = 0 ORDER BY user_id; """
+ qt_select10 """ SELECT * FROM ${tableName} t WHERE note MATCH_PHRASE
'electrical engineer' ORDER BY user_id; """
+ qt_select11 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer
Developer' AND city match_all 'Shanghai China' ORDER BY user_id; """
+}
diff --git
a/regression-test/suites/inverted_index_p0/index_change/test_index_change_5.groovy
b/regression-test/suites/inverted_index_p0/index_change/test_index_change_5.groovy
new file mode 100644
index 0000000000..4817d6f2d5
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/index_change/test_index_change_5.groovy
@@ -0,0 +1,109 @@
+// 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.
+
+import org.codehaus.groovy.runtime.IOGroovyMethods
+
+suite("test_index_change_5") {
+ def timeout = 60000
+ def delta_time = 1000
+ def alter_res = "null"
+ def useTime = 0
+ def wait_for_latest_op_on_table_finish = { table_name, OpTimeout ->
+ for(int t = delta_time; t <= OpTimeout; t += delta_time){
+ alter_res = sql """SHOW ALTER TABLE COLUMN WHERE TableName =
"${table_name}" ORDER BY CreateTime DESC LIMIT 1;"""
+ alter_res = alter_res.toString()
+ if(alter_res.contains("FINISHED")) {
+ sleep(3000) // wait change table state to normal
+ logger.info(table_name + " latest alter job finished, detail:
" + alter_res)
+ break
+ }
+ useTime = t
+ sleep(delta_time)
+ }
+ assertTrue(useTime <= OpTimeout, "wait_for_latest_op_on_table_finish
timeout")
+ }
+
+ def tableName = "test_index_change_5"
+
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ `user_id` LARGEINT NOT NULL COMMENT "用户id",
+ `date` DATE NOT NULL COMMENT "数据灌入日期时间",
+ `city` VARCHAR(20) COMMENT "用户所在城市",
+ `age` SMALLINT COMMENT "用户年龄",
+ `sex` TINYINT COMMENT "用户性别",
+ `note` TEXT COMMENT "备注",
+ INDEX idx_user_id (`user_id`) USING INVERTED COMMENT '',
+ INDEX idx_note (`note`) USING INVERTED PROPERTIES("parser" =
"english") COMMENT ''
+ )
+ DUPLICATE KEY(`user_id`, `date`, `city`, `age`, `sex`) DISTRIBUTED BY
HASH(`user_id`)
+ PROPERTIES ( "replication_num" = "1" );
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (1, '2017-10-01', 'Beijing China', 10, 1, 'Software Developer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (2, '2017-10-01', 'Beijing China', 10, 1, 'Communication Engineer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (3, '2017-10-01', 'Shanghai China', 10, 1, 'electrical engineer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (4, '2017-10-02', 'Beijing China', 10, 0, 'Both a teacher and a
scientist')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (5, '2017-10-02', 'Shenzhen China', 10, 1, 'teacher')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (6, '2017-10-03', 'Hongkong China', 10, 1, 'Architectural designer')
+ """
+
+ qt_select1 """ SELECT * FROM ${tableName} t ORDER BY
user_id,date,city,age,sex; """
+ qt_select2 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing'
ORDER BY user_id; """
+ qt_select3 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing' and
sex = 1 ORDER BY user_id; """
+ qt_select4 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer'
and sex = 0 ORDER BY user_id; """
+ qt_select5 """ SELECT * FROM ${tableName} t WHERE note MATCH_PHRASE
'electrical engineer' ORDER BY user_id; """
+ qt_select6 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer
Developer' AND city match_all 'Shanghai China' ORDER BY user_id; """
+
+
//TabletId,ReplicaId,BackendId,SchemaHash,Version,LstSuccessVersion,LstFailedVersion,LstFailedTime,LocalDataSize,RemoteDataSize,RowCount,State,LstConsistencyCheckTime,CheckVersion,VersionCount,QueryHits,PathHash,MetaUrl,CompactionStatus
+ String[][] tablets = sql """ show tablets from ${tableName}; """
+ // drop inverted index idx_user_id, idx_note
+ sql """ DROP INDEX idx_user_id ON ${tableName} """
+ sql """ DROP INDEX idx_note ON ${tableName} """
+ // create bitmap index
+ sql """ CREATE INDEX idx_sex ON ${tableName}(`sex`) USING BITMAP """
+ wait_for_latest_op_on_table_finish(tableName, timeout)
+ // wait_for_build_index_on_partition_finish(tableName, timeout)
+
+ def show_result = sql "show index from ${tableName}"
+ logger.info("show index from " + tableName + " result: " + show_result)
+ assertEquals(show_result.size(), 1)
+ assertEquals(show_result[0][2], "idx_sex")
+
+ qt_select7 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing'
ORDER BY user_id; """
+ qt_select8 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing' and
sex = 1 ORDER BY user_id; """
+ qt_select9 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer'
and sex = 0 ORDER BY user_id; """
+ qt_select10 """ SELECT * FROM ${tableName} t WHERE note MATCH_PHRASE
'electrical engineer' ORDER BY user_id; """
+ qt_select11 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer
Developer' AND city match_all 'Shanghai China' ORDER BY user_id; """
+}
diff --git
a/regression-test/suites/inverted_index_p0/index_change/test_index_change_6.groovy
b/regression-test/suites/inverted_index_p0/index_change/test_index_change_6.groovy
new file mode 100644
index 0000000000..c839fae0d0
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/index_change/test_index_change_6.groovy
@@ -0,0 +1,121 @@
+// 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.
+
+import org.codehaus.groovy.runtime.IOGroovyMethods
+
+suite("test_index_change_6") {
+ def timeout = 60000
+ def delta_time = 1000
+ def alter_res = "null"
+ def useTime = 0
+ def wait_for_build_index_on_partition_finish = { table_name, OpTimeout ->
+ for(int t = delta_time; t <= OpTimeout; t += delta_time){
+ alter_res = sql """SHOW BUILD INDEX WHERE TableName =
"${table_name}";"""
+ def expected_finished_num = alter_res.size();
+ def finished_num = 0;
+ for (int i = 0; i < expected_finished_num; i++) {
+ logger.info(table_name + " build index job state: " +
alter_res[i][7] + i)
+ if (alter_res[i][7] == "FINISHED") {
+ ++finished_num;
+ }
+ }
+ if (finished_num == expected_finished_num) {
+ logger.info(table_name + " all build index jobs finished,
detail: " + alter_res)
+ break
+ }
+ useTime = t
+ sleep(delta_time)
+ }
+ assertTrue(useTime <= OpTimeout,
"wait_for_latest_build_index_on_partition_finish timeout")
+ }
+
+ def tableName = "test_index_change_6"
+
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ `user_id` LARGEINT NOT NULL COMMENT "用户id",
+ `date` DATE NOT NULL COMMENT "数据灌入日期时间",
+ `city` VARCHAR(20) COMMENT "用户所在城市",
+ `age` SMALLINT COMMENT "用户年龄",
+ `sex` TINYINT COMMENT "用户性别",
+ `note` TEXT COMMENT "备注",
+ INDEX idx_user_id (`user_id`) USING INVERTED COMMENT '',
+ INDEX idx_note (`note`) USING INVERTED PROPERTIES("parser" =
"english") COMMENT ''
+ )
+ DUPLICATE KEY(`user_id`, `date`, `city`, `age`, `sex`) DISTRIBUTED BY
HASH(`user_id`)
+ PROPERTIES ( "replication_num" = "1" );
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (1, '2017-10-01', 'Beijing China', 10, 1, 'Software Developer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (2, '2017-10-01', 'Beijing China', 10, 1, 'Communication Engineer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (3, '2017-10-01', 'Shanghai China', 10, 1, 'electrical engineer')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (4, '2017-10-02', 'Beijing China', 10, 0, 'Both a teacher and a
scientist')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (5, '2017-10-02', 'Shenzhen China', 10, 1, 'teacher')
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (6, '2017-10-03', 'Hongkong China', 10, 1, 'Architectural designer')
+ """
+
+ qt_select1 """ SELECT * FROM ${tableName} t ORDER BY
user_id,date,city,age,sex; """
+ qt_select2 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing'
ORDER BY user_id; """
+ qt_select3 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing' and
sex = 1 ORDER BY user_id; """
+ qt_select4 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer'
and sex = 0 ORDER BY user_id; """
+ qt_select5 """ SELECT * FROM ${tableName} t WHERE note MATCH_PHRASE
'electrical engineer' ORDER BY user_id; """
+ qt_select6 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer
Developer' AND city match_all 'Shanghai China' ORDER BY user_id; """
+
+
//TabletId,ReplicaId,BackendId,SchemaHash,Version,LstSuccessVersion,LstFailedVersion,LstFailedTime,LocalDataSize,RemoteDataSize,RowCount,State,LstConsistencyCheckTime,CheckVersion,VersionCount,QueryHits,PathHash,MetaUrl,CompactionStatus
+ String[][] tablets = sql """ show tablets from ${tableName}; """
+ // drop inverted index idx_user_id, idx_note
+ sql """ DROP INDEX idx_user_id ON ${tableName} """
+ sql """ DROP INDEX idx_note ON ${tableName} """
+
+ // create inverted index
+ sql """ CREATE INDEX idx_user_id ON ${tableName}(`user_id`) USING INVERTED
"""
+ sql """ CREATE INDEX idx_note ON ${tableName}(`note`) USING INVERTED
PROPERTIES("parser" = "english") """
+
+ // build inverted index
+ sql """ BUILD INDEX idx_user_id ON ${tableName} """
+ sql """ BUILD INDEX idx_note ON ${tableName} """
+ wait_for_build_index_on_partition_finish(tableName, timeout)
+
+ def show_result = sql "show index from ${tableName}"
+ logger.info("show index from " + tableName + " result: " + show_result)
+ assertEquals(show_result.size(), 2)
+ assertEquals(show_result[0][2], "idx_user_id")
+ assertEquals(show_result[1][2], "idx_note")
+
+ qt_select7 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing'
ORDER BY user_id; """
+ qt_select8 """ SELECT * FROM ${tableName} t WHERE city MATCH 'beijing' and
sex = 1 ORDER BY user_id; """
+ qt_select9 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer'
and sex = 0 ORDER BY user_id; """
+ qt_select10 """ SELECT * FROM ${tableName} t WHERE note MATCH_PHRASE
'electrical engineer' ORDER BY user_id; """
+ qt_select11 """ SELECT * FROM ${tableName} t WHERE note MATCH 'engineer
Developer' AND city match_all 'Shanghai China' ORDER BY user_id; """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
