This is an automated email from the ASF dual-hosted git repository.
jianliangqi 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 1f9372558d [improve](regression case) Add more inverted index
regression case (#18589)
1f9372558d is described below
commit 1f9372558d92e1543164a649c0bec06dcf6816a1
Author: YueW <[email protected]>
AuthorDate: Wed Apr 12 20:40:55 2023 +0800
[improve](regression case) Add more inverted index regression case (#18589)
1. add more inverted index regression case for unique mow
2. add inverted index case with different data types
---
.../ssb_unique_sql_zstd/sql/q1.1.out | 4 +
.../ssb_unique_sql_zstd/sql/q1.2.out | 4 +
.../ssb_unique_sql_zstd/sql/q1.3.out | 4 +
.../ssb_unique_sql_zstd/sql/q2.1.out | 43 ++++
.../ssb_unique_sql_zstd/sql/q2.2.out | 11 +
.../ssb_unique_sql_zstd/sql/q2.3.out | 4 +
.../ssb_unique_sql_zstd/sql/q3.1.out | 28 +++
.../ssb_unique_sql_zstd/sql/q3.2.out | 51 ++++
.../ssb_unique_sql_zstd/sql/q3.3.out | 4 +
.../ssb_unique_sql_zstd/sql/q3.4.out | 3 +
.../ssb_unique_sql_zstd/sql/q4.1.out | 8 +
.../ssb_unique_sql_zstd/sql/q4.2.out | 3 +
.../ssb_unique_sql_zstd/sql/q4.3.out | 3 +
.../data/inverted_index_p0/test_inverted_index.out | 115 +++++++++
.../test_mow_with_null_sequence.out | 141 +++++++++++
.../unique_with_mow/test_unique_mow_sequence.out | 25 ++
.../ssb_unique_sql_zstd/ddl/customer_create.sql | 25 ++
.../ssb_unique_sql_zstd/ddl/customer_delete.sql | 1 +
.../ssb_unique_sql_zstd/ddl/date_create.sql | 31 +++
.../ssb_unique_sql_zstd/ddl/date_delete.sql | 1 +
.../ssb_unique_sql_zstd/ddl/lineorder_create.sql | 41 ++++
.../ssb_unique_sql_zstd/ddl/lineorder_delete.sql | 1 +
.../ssb_unique_sql_zstd/ddl/part_create.sql | 19 ++
.../ssb_unique_sql_zstd/ddl/part_delete.sql | 1 +
.../ssb_unique_sql_zstd/ddl/supplier_create.sql | 19 ++
.../ssb_unique_sql_zstd/ddl/supplier_delete.sql | 1 +
.../ssb_unique_sql_zstd/load.groovy | 80 ++++++
.../ssb_unique_sql_zstd/sql/q1.1.sql | 24 ++
.../ssb_unique_sql_zstd/sql/q1.2.sql | 24 ++
.../ssb_unique_sql_zstd/sql/q1.3.sql | 25 ++
.../ssb_unique_sql_zstd/sql/q2.1.sql | 26 ++
.../ssb_unique_sql_zstd/sql/q2.2.sql | 27 ++
.../ssb_unique_sql_zstd/sql/q2.3.sql | 26 ++
.../ssb_unique_sql_zstd/sql/q3.1.sql | 28 +++
.../ssb_unique_sql_zstd/sql/q3.2.sql | 28 +++
.../ssb_unique_sql_zstd/sql/q3.3.sql | 30 +++
.../ssb_unique_sql_zstd/sql/q3.4.sql | 30 +++
.../ssb_unique_sql_zstd/sql/q4.1.sql | 30 +++
.../ssb_unique_sql_zstd/sql/q4.2.sql | 31 +++
.../ssb_unique_sql_zstd/sql/q4.3.sql | 29 +++
.../inverted_index_p0/test_inverted_index.groovy | 270 ++++++++++++++++++++
.../test_mow_with_null_sequence.groovy | 148 +++++++++++
.../unique_with_mow/test_pk_uk_case.groovy | 271 +++++++++++++++++++++
.../test_primary_key_simple_case.groovy | 116 +++++++++
.../test_unique_mow_sequence.groovy | 91 +++++++
45 files changed, 1925 insertions(+)
diff --git
a/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.1.out
b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.1.out
new file mode 100644
index 0000000000..92604403fd
--- /dev/null
+++ b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.1.out
@@ -0,0 +1,4 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !q1.1 --
+\N
+
diff --git
a/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.2.out
b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.2.out
new file mode 100644
index 0000000000..22731ac444
--- /dev/null
+++ b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.2.out
@@ -0,0 +1,4 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !q1.2 --
+\N
+
diff --git
a/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.3.out
b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.3.out
new file mode 100644
index 0000000000..71908d1f12
--- /dev/null
+++ b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.3.out
@@ -0,0 +1,4 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !q1.3 --
+\N
+
diff --git
a/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.1.out
b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.1.out
new file mode 100644
index 0000000000..9d56f6e633
--- /dev/null
+++ b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.1.out
@@ -0,0 +1,43 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !q2.1 --
+29165996 1992 MFGR#121
+23120066 1992 MFGR#1210
+52982362 1992 MFGR#1211
+30954680 1992 MFGR#1212
+15288453 1992 MFGR#1213
+7655070 1992 MFGR#1214
+22246540 1992 MFGR#1215
+19716439 1992 MFGR#1216
+43666251 1992 MFGR#1217
+22759602 1992 MFGR#1218
+23318799 1992 MFGR#1219
+74056106 1992 MFGR#122
+51050565 1992 MFGR#1220
+38878674 1992 MFGR#1221
+16558051 1992 MFGR#1222
+26690787 1992 MFGR#1223
+76498594 1992 MFGR#1224
+32608903 1992 MFGR#1225
+47636685 1992 MFGR#1226
+27691433 1992 MFGR#1227
+32513490 1992 MFGR#1228
+35514258 1992 MFGR#1229
+17199862 1992 MFGR#123
+24678908 1992 MFGR#1230
+26231337 1992 MFGR#1231
+36330900 1992 MFGR#1232
+24946678 1992 MFGR#1233
+36431683 1992 MFGR#1234
+39368479 1992 MFGR#1235
+44456974 1992 MFGR#1236
+31443810 1992 MFGR#1237
+49003021 1992 MFGR#1238
+31379822 1992 MFGR#1239
+24245603 1992 MFGR#124
+49870826 1992 MFGR#1240
+28194770 1992 MFGR#125
+40503844 1992 MFGR#126
+36027836 1992 MFGR#127
+35881895 1992 MFGR#128
+21732451 1992 MFGR#129
+
diff --git
a/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.2.out
b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.2.out
new file mode 100644
index 0000000000..debe195012
--- /dev/null
+++ b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.2.out
@@ -0,0 +1,11 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !q2.2 --
+28235270 1992 MFGR#2221
+64071827 1992 MFGR#2222
+48591160 1992 MFGR#2223
+20416501 1992 MFGR#2224
+74950776 1992 MFGR#2225
+60628045 1992 MFGR#2226
+39273349 1992 MFGR#2227
+66658087 1992 MFGR#2228
+
diff --git
a/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.3.out
b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.3.out
new file mode 100644
index 0000000000..40b3220406
--- /dev/null
+++ b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.3.out
@@ -0,0 +1,4 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !q2.3 --
+89380397 1992 MFGR#2239
+
diff --git
a/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.1.out
b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.1.out
new file mode 100644
index 0000000000..a50f6a20d5
--- /dev/null
+++ b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.1.out
@@ -0,0 +1,28 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !q3.1 --
+JAPAN CHINA 1992 637991852
+VIETNAM CHINA 1992 621845377
+INDONESIA CHINA 1992 621316255
+CHINA CHINA 1992 614550901
+INDIA CHINA 1992 561966207
+INDIA INDONESIA 1992 487449629
+INDONESIA INDONESIA 1992 477417717
+JAPAN INDONESIA 1992 476513261
+JAPAN VIETNAM 1992 468999429
+INDONESIA JAPAN 1992 465870469
+VIETNAM INDONESIA 1992 462424521
+INDIA JAPAN 1992 412186106
+JAPAN JAPAN 1992 399179790
+VIETNAM JAPAN 1992 395247587
+JAPAN INDIA 1992 393835589
+CHINA INDONESIA 1992 352903905
+CHINA INDIA 1992 348359904
+VIETNAM VIETNAM 1992 342176333
+INDIA VIETNAM 1992 334582962
+INDIA INDIA 1992 329354089
+CHINA JAPAN 1992 327558220
+CHINA VIETNAM 1992 324763767
+INDONESIA INDIA 1992 310417666
+VIETNAM INDIA 1992 296225919
+INDONESIA VIETNAM 1992 278083418
+
diff --git
a/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.2.out
b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.2.out
new file mode 100644
index 0000000000..1109fa3ce8
--- /dev/null
+++ b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.2.out
@@ -0,0 +1,51 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !q3.2 --
+UNITED ST4 UNITED ST0 1992 34626982
+UNITED ST4 UNITED ST3 1992 29767238
+UNITED ST1 UNITED ST9 1992 25644597
+UNITED ST2 UNITED ST0 1992 23943154
+UNITED ST4 UNITED ST9 1992 21189183
+UNITED ST0 UNITED ST0 1992 18293852
+UNITED ST7 UNITED ST3 1992 17996772
+UNITED ST9 UNITED ST3 1992 17863433
+UNITED ST1 UNITED ST7 1992 17410800
+UNITED ST2 UNITED ST3 1992 15331073
+UNITED ST5 UNITED ST9 1992 14448179
+UNITED ST1 UNITED ST3 1992 13938002
+UNITED ST5 UNITED ST6 1992 12398029
+UNITED ST9 UNITED ST2 1992 12370917
+UNITED ST2 UNITED ST9 1992 12343455
+UNITED ST5 UNITED ST0 1992 12301234
+UNITED ST6 UNITED ST0 1992 11900889
+UNITED ST4 UNITED ST2 1992 11696334
+UNITED ST4 UNITED ST6 1992 11369008
+UNITED ST1 UNITED ST6 1992 11000283
+UNITED ST1 UNITED ST0 1992 10878084
+UNITED ST4 UNITED ST7 1992 10151573
+UNITED ST5 UNITED ST2 1992 9917834
+UNITED ST7 UNITED ST7 1992 9715656
+UNITED ST6 UNITED ST6 1992 8685228
+UNITED ST2 UNITED ST2 1992 8313714
+UNITED ST1 UNITED ST2 1992 8004700
+UNITED ST2 UNITED ST7 1992 7759164
+UNITED ST0 UNITED ST7 1992 7137641
+UNITED ST9 UNITED ST7 1992 6703890
+UNITED ST6 UNITED ST9 1992 6597261
+UNITED ST7 UNITED ST2 1992 6125476
+UNITED ST7 UNITED ST6 1992 6058017
+UNITED ST5 UNITED ST3 1992 5862031
+UNITED ST8 UNITED ST9 1992 5690491
+UNITED ST7 UNITED ST9 1992 5403152
+UNITED ST9 UNITED ST0 1992 4816370
+UNITED ST9 UNITED ST9 1992 4234523
+UNITED ST3 UNITED ST3 1992 4080199
+UNITED ST5 UNITED ST7 1992 3936271
+UNITED ST8 UNITED ST0 1992 3574169
+UNITED ST0 UNITED ST3 1992 3201624
+UNITED ST3 UNITED ST9 1992 2614811
+UNITED ST8 UNITED ST7 1992 2373825
+UNITED ST9 UNITED ST6 1992 2066609
+UNITED ST7 UNITED ST0 1992 1882015
+UNITED ST6 UNITED ST3 1992 1873819
+UNITED ST6 UNITED ST2 1992 291566
+
diff --git
a/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.3.out
b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.3.out
new file mode 100644
index 0000000000..6f33841912
--- /dev/null
+++ b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.3.out
@@ -0,0 +1,4 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !q3.3 --
+UNITED KI5 UNITED KI1 1992 4397192
+
diff --git
a/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.4.out
b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.4.out
new file mode 100644
index 0000000000..3738fc2859
--- /dev/null
+++ b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.4.out
@@ -0,0 +1,3 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !q3.4 --
+
diff --git
a/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.1.out
b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.1.out
new file mode 100644
index 0000000000..00bc9ddd7c
--- /dev/null
+++ b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.1.out
@@ -0,0 +1,8 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !q4.1 --
+1992 ARGENTINA 533196600
+1992 BRAZIL 684224630
+1992 CANADA 532686194
+1992 PERU 586223155
+1992 UNITED STATES 682387184
+
diff --git
a/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.2.out
b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.2.out
new file mode 100644
index 0000000000..30fae3d4bf
--- /dev/null
+++ b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.2.out
@@ -0,0 +1,3 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !q4.2 --
+
diff --git
a/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.3.out
b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.3.out
new file mode 100644
index 0000000000..741016a89d
--- /dev/null
+++ b/regression-test/data/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.3.out
@@ -0,0 +1,3 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !q4.3 --
+
diff --git a/regression-test/data/inverted_index_p0/test_inverted_index.out
b/regression-test/data/inverted_index_p0/test_inverted_index.out
new file mode 100644
index 0000000000..06e2370635
--- /dev/null
+++ b/regression-test/data/inverted_index_p0/test_inverted_index.out
@@ -0,0 +1,115 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+k1 TINYINT Yes true \N
+k2 SMALLINT Yes true \N
+k3 INT Yes true \N
+k4 BIGINT Yes false \N NONE
+k5 CHAR(1) Yes false \N NONE
+k6 VARCHAR(1) Yes false \N NONE
+k7 DATE Yes false \N NONE
+k8 DATETIME Yes false \N NONE
+k9 LARGEINT Yes false \N NONE
+k10 DECIMAL(9, 0) Yes false \N NONE
+k11 BOOLEAN Yes false \N NONE
+k12 DATE Yes false \N NONE
+k13 DATETIME Yes false \N NONE
+k14 DATETIME Yes false \N NONE
+k15 DATETIME Yes false \N NONE
+
+-- !sql --
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index1 k1
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index2 k2
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index3 k3
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index4 k4
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index5 k5
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index6 k6
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index7 k7
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index8 k8
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index9 k9
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index10 k10
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index11 k11
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index12 k12
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index13 k13
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index14 k14
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_dup
index15 k15
INVERTED
+
+-- !sql --
+1 1 1 1 1 1 2022-05-31
2022-05-31T10:00 1 1 true 2022-05-31
2022-05-31T10:00 2022-05-31T10:00:00.111 2022-05-31T10:00:00.111111
+
+-- !sql --
+k1 TINYINT Yes true \N
+k2 SMALLINT Yes true \N
+k3 INT Yes true \N
+k4 BIGINT Yes true \N
+k5 CHAR(1) Yes true \N
+k6 VARCHAR(1) Yes true \N
+k7 DATE Yes true \N
+k8 DATETIME Yes true \N
+k9 LARGEINT Yes true \N
+k10 DECIMAL(9, 0) Yes true \N
+k11 BOOLEAN Yes true \N
+k12 DATE Yes true \N
+k13 DATETIME Yes true \N
+k14 DATETIME Yes true \N
+k15 DATETIME Yes true \N
+v1 INT Yes false \N SUM
+
+-- !sql --
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index1 k1
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index2 k2
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index3 k3
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index4 k4
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index5 k5
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index6 k6
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index7 k7
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index8 k8
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index9 k9
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index10 k10
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index11 k11
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index12 k12
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index13 k13
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index14 k14
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_agg
index15 k15
INVERTED
+
+-- !sql --
+1 1 1 1 1 1 2022-05-31
2022-05-31T10:00 1 1 true 2022-05-31
2022-05-31T10:00 2022-05-31T10:00:00.111 2022-05-31T10:00:00.111111
1
+
+-- !sql --
+k1 TINYINT Yes true \N
+k2 SMALLINT Yes true \N
+k3 INT Yes true \N
+k4 BIGINT Yes true \N
+k5 CHAR(1) Yes true \N
+k6 VARCHAR(1) Yes true \N
+k7 DATE Yes true \N
+k8 DATETIME Yes true \N
+k9 LARGEINT Yes true \N
+k10 DECIMAL(9, 0) Yes true \N
+k11 BOOLEAN Yes true \N
+k12 DATE Yes false \N NONE
+k13 DATETIME Yes false \N NONE
+k14 DATETIME Yes false \N NONE
+k15 DATETIME Yes false \N NONE
+v1 INT Yes false \N NONE
+
+-- !sql --
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index1 k1
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index2 k2
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index3 k3
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index4 k4
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index5 k5
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index6 k6
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index7 k7
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index8 k8
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index9 k9
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index10 k10
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index11 k11
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index12 k12
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index13 k13
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index14 k14
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index15 k15
INVERTED
+default_cluster:regression_test_inverted_index_p0.test_inverted_index_unique
index16 v1
INVERTED
+
+-- !sql --
+1 1 1 1 1 1 2022-05-31
2022-05-31T10:00 1 1 true 2022-05-31
2022-05-31T10:00 2022-05-31T10:00:00.111 2022-05-31T10:00:00.111111
1
+
diff --git
a/regression-test/data/inverted_index_p0/unique_with_mow/test_mow_with_null_sequence.out
b/regression-test/data/inverted_index_p0/unique_with_mow/test_mow_with_null_sequence.out
new file mode 100644
index 0000000000..3f84eee48a
--- /dev/null
+++
b/regression-test/data/inverted_index_p0/unique_with_mow/test_mow_with_null_sequence.out
@@ -0,0 +1,141 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql1 --
+a zhang si address2 2022-10-20
+aa1234 li yi ke address4 2022-12-11
+aa1235 li wu address6 \N
+ab zhang si yi address6 2022-11-20
+
+-- !sql2 --
+a zhang si address2 2022-10-20
+
+-- !sql3 --
+ab zhang si yi address6 2022-11-20
+
+-- !sql4 --
+aa1234 li yi ke address4 2022-12-11
+aa1235 li wu address6 \N
+ab zhang si yi address6 2022-11-20
+
+-- !sql5 --
+a zhang si address2 2022-10-20
+ab zhang si yi address6 2022-11-20
+
+-- !sql6 --
+a zhang si address2 2022-10-20
+ab zhang si yi address6 2022-11-20
+
+-- !sql7 --
+
+-- !sql8 --
+ab zhang si yi address6 2022-11-20
+
+-- !sql9 --
+aa1234 li yi ke address4 2022-12-11
+aa1235 li wu address6 \N
+
+-- !sql10 --
+aa1234 li yi ke address4 2022-12-11
+
+-- !sql11 --
+aa1235 li wu address6 \N
+
+-- !sql12 --
+aa1234 li yi ke address4 2022-12-11
+
+-- !sql13 --
+aa1235 li wu address6 \N
+
+-- !sql14 --
+
+-- !sql15 --
+
+-- !sql16 --
+a zhang si address2 2022-10-20
+ab zhang si yi address6 2022-11-20
+
+-- !sql17 --
+a zhang si address2 2022-10-20
+
+-- !sql18 --
+ab zhang si yi address6 2022-11-20
+
+-- !sql19 --
+ab zhang si yi address6 2022-11-20
+
+-- !sql20 --
+aa1234 li yi ke address4 2022-12-11
+
+-- !sql21 --
+aa1234 li yi ke address4 2022-12-11
+
+-- !sql22 --
+a zhang si address2 100
+aa1234 wu ke address4 -1
+aa1235 zhang ke address6 -1
+aa1236 wu yi address6 0
+ab zhang yi address6 110
+
+-- !sql23 --
+a zhang si address2 100
+
+-- !sql24 --
+ab zhang yi address6 110
+
+-- !sql25 --
+aa1234 wu ke address4 -1
+aa1235 zhang ke address6 -1
+aa1236 wu yi address6 0
+ab zhang yi address6 110
+
+-- !sql26 --
+a zhang si address2 100
+aa1235 zhang ke address6 -1
+ab zhang yi address6 110
+
+-- !sql27 --
+a zhang si address2 100
+aa1235 zhang ke address6 -1
+ab zhang yi address6 110
+
+-- !sql28 --
+
+-- !sql29 --
+a zhang si address2 100
+ab zhang yi address6 110
+
+-- !sql30 --
+
+-- !sql31 --
+
+-- !sql32 --
+
+-- !sql33 --
+
+-- !sql34 --
+aa1234 wu ke address4 -1
+aa1236 wu yi address6 0
+
+-- !sql35 --
+aa1234 wu ke address4 -1
+aa1236 wu yi address6 0
+
+-- !sql36 --
+aa1236 wu yi address6 0
+
+-- !sql37 --
+a zhang si address2 100
+
+-- !sql38 --
+
+-- !sql39 --
+ab zhang yi address6 110
+
+-- !sql40 --
+ab zhang yi address6 110
+
+-- !sql41 --
+
+-- !sql42 --
+
+-- !sql43 --
+
diff --git
a/regression-test/data/inverted_index_p0/unique_with_mow/test_unique_mow_sequence.out
b/regression-test/data/inverted_index_p0/unique_with_mow/test_unique_mow_sequence.out
new file mode 100644
index 0000000000..65b68e4152
--- /dev/null
+++
b/regression-test/data/inverted_index_p0/unique_with_mow/test_unique_mow_sequence.out
@@ -0,0 +1,25 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+1 Customer#000000001 j5JsirBM9P MOROCCO 0 MOROCCO AFRICA
25-989-741-2988 BUILDING
+2 Customer#000000002 487LW1dovn6Q4dMVym JORDAN 1 JORDAN
MIDDLE EAST 23-768-687-3665 AUTOMOBILE
+3 Customer#000000003 fkRGN8n ARGENTINA7 ARGENTINA AMERICA
11-719-748-3364 AUTOMOBILE
+4 Customer#000000004 4u58h f EGYPT 4 EGYPT MIDDLE EAST
14-128-190-5944 MACHINERY
+5 Customer#000000005 hwBtxkoBF qSW4KrI CANADA 5 CANADA
AMERICA 13-750-942-6364 HOUSEHOLD
+
+-- !sql --
+2996 Customer#000002996 PFd,H,pC PERU 1 PERU AMERICA
27-412-836-3763 FURNITURE
+2997 Customer#000002997 LiVKxN3lQHLunID ALGERIA 0 ALGERIA AFRICA
10-600-583-9608 FURNITURE
+2998 Customer#000002998 waJRUwjblh3sJbglX9gS9w PERU 7 PERU
AMERICA 27-747-219-4938 AUTOMOBILE
+2999 Customer#000002999 HaPy4sQ MiANd0pR5uA7 VIETNAM 5 VIETNAM
ASIA 31-297-683-9811 MACHINERY
+3000 Customer#000003000 ,5Yw1O EGYPT 4 EGYPT MIDDLE EAST
14-645-615-5901 FURNITURE
+
+-- !sql --
+1 Customer#000000001 j5JsirBM9P MOROCCO 0 MOROCCO AFRICA
25-989-741-2988 BUILDING
+
+-- !sql --
+3000 Customer#000003000 ,5Yw1O EGYPT 4 EGYPT MIDDLE EAST
14-645-615-5901 FURNITURE
+
+-- !sql --
+
+-- !sql --
+
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/customer_create.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/customer_create.sql
new file mode 100644
index 0000000000..25b25e5bb1
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/customer_create.sql
@@ -0,0 +1,25 @@
+CREATE TABLE IF NOT EXISTS `customer` (
+ `c_custkey` int(11) NOT NULL COMMENT "",
+ `c_name` varchar(26) NOT NULL COMMENT "",
+ `c_address` varchar(41) NOT NULL COMMENT "",
+ `c_city` varchar(11) NOT NULL COMMENT "",
+ `c_nation` varchar(16) NOT NULL COMMENT "",
+ `c_region` varchar(13) NOT NULL COMMENT "",
+ `c_phone` varchar(16) NOT NULL COMMENT "",
+ `c_mktsegment` varchar(11) NOT NULL COMMENT "",
+ INDEX c_custkey_idx(c_custkey) USING INVERTED COMMENT 'c_custkey index',
+ INDEX c_name_idx(c_name) USING INVERTED COMMENT 'c_name index',
+ INDEX c_address_idx(c_address) USING INVERTED COMMENT 'c_address index',
+ INDEX c_city_idx(c_city) USING INVERTED COMMENT 'c_city index',
+ INDEX c_nation_idx(c_nation) USING INVERTED COMMENT 'c_nation index',
+ INDEX c_region_idx(c_region) USING INVERTED COMMENT 'c_region index',
+ INDEX c_phone_idx(c_phone) USING INVERTED COMMENT 'c_phone index',
+ INDEX c_mktsegment_idx(c_mktsegment) USING INVERTED COMMENT 'c_mktsegment
index'
+)
+UNIQUE KEY (`c_custkey`)
+DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 10
+PROPERTIES (
+"compression"="zstd",
+"replication_num" = "1",
+"enable_unique_key_merge_on_write" = "true"
+);
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/customer_delete.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/customer_delete.sql
new file mode 100644
index 0000000000..fe22a226fe
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/customer_delete.sql
@@ -0,0 +1 @@
+truncate table customer;
\ No newline at end of file
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/date_create.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/date_create.sql
new file mode 100644
index 0000000000..3971e0e04a
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/date_create.sql
@@ -0,0 +1,31 @@
+CREATE TABLE IF NOT EXISTS `date` (
+ `d_datekey` int(11) NOT NULL COMMENT "",
+ `d_date` varchar(20) NOT NULL COMMENT "",
+ `d_dayofweek` varchar(10) NOT NULL COMMENT "",
+ `d_month` varchar(11) NOT NULL COMMENT "",
+ `d_year` int(11) NOT NULL COMMENT "",
+ `d_yearmonthnum` int(11) NOT NULL COMMENT "",
+ `d_yearmonth` varchar(9) NOT NULL COMMENT "",
+ `d_daynuminweek` int(11) NOT NULL COMMENT "",
+ `d_daynuminmonth` int(11) NOT NULL COMMENT "",
+ `d_daynuminyear` int(11) NOT NULL COMMENT "",
+ `d_monthnuminyear` int(11) NOT NULL COMMENT "",
+ `d_weeknuminyear` int(11) NOT NULL COMMENT "",
+ `d_sellingseason` varchar(14) NOT NULL COMMENT "",
+ `d_lastdayinweekfl` int(11) NOT NULL COMMENT "",
+ `d_lastdayinmonthfl` int(11) NOT NULL COMMENT "",
+ `d_holidayfl` int(11) NOT NULL COMMENT "",
+ `d_weekdayfl` int(11) NOT NULL COMMENT "",
+ INDEX d_datekey_idx(d_datekey) USING INVERTED COMMENT 'd_datekey index',
+ INDEX d_date_idx(d_date) USING INVERTED COMMENT 'd_date index',
+ INDEX d_dayofweek_idx(d_dayofweek) USING INVERTED COMMENT 'd_dayofweek
index',
+ INDEX d_month_idx(d_month) USING INVERTED COMMENT 'd_month index',
+ INDEX d_year_idx(d_year) USING INVERTED COMMENT 'd_year index'
+)
+UNIQUE KEY (`d_datekey`)
+DISTRIBUTED BY HASH(`d_datekey`) BUCKETS 1
+PROPERTIES (
+"compression"="zstd",
+"replication_num" = "1",
+"enable_unique_key_merge_on_write" = "true"
+);
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/date_delete.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/date_delete.sql
new file mode 100644
index 0000000000..12933cbbad
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/date_delete.sql
@@ -0,0 +1 @@
+truncate table `date`;
\ No newline at end of file
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/lineorder_create.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/lineorder_create.sql
new file mode 100644
index 0000000000..8d2a200f30
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/lineorder_create.sql
@@ -0,0 +1,41 @@
+CREATE TABLE IF NOT EXISTS `lineorder` (
+ `lo_orderdate` int(11) NOT NULL COMMENT "",
+ `lo_orderkey` bigint(20) NOT NULL COMMENT "",
+ `lo_linenumber` bigint(20) NOT NULL COMMENT "",
+ `lo_custkey` int(11) NOT NULL COMMENT "",
+ `lo_partkey` int(11) NOT NULL COMMENT "",
+ `lo_suppkey` int(11) NOT NULL COMMENT "",
+ `lo_orderpriority` varchar(16) NOT NULL COMMENT "",
+ `lo_shippriority` int(11) NOT NULL COMMENT "",
+ `lo_quantity` bigint(20) NOT NULL COMMENT "",
+ `lo_extendedprice` bigint(20) NOT NULL COMMENT "",
+ `lo_ordtotalprice` bigint(20) NOT NULL COMMENT "",
+ `lo_discount` bigint(20) NOT NULL COMMENT "",
+ `lo_revenue` bigint(20) NOT NULL COMMENT "",
+ `lo_supplycost` bigint(20) NOT NULL COMMENT "",
+ `lo_tax` bigint(20) NOT NULL COMMENT "",
+ `lo_commitdate` bigint(20) NOT NULL COMMENT "",
+ `lo_shipmode` varchar(11) NOT NULL COMMENT "",
+ INDEX lo_orderdate_idx(lo_orderdate) USING INVERTED COMMENT 'lo_orderdate
index',
+ INDEX lo_orderkey_idx(lo_orderkey) USING INVERTED COMMENT 'lo_orderkey
index',
+ INDEX lo_linenumber_idx(lo_linenumber) USING INVERTED COMMENT 'lo_linenumber
index',
+ INDEX lo_custkey_idx(lo_custkey) USING INVERTED COMMENT 'lo_custkey index',
+ INDEX lo_partkey_idx(lo_partkey) USING INVERTED COMMENT 'lo_partkey index',
+ INDEX lo_orderpriority_idx(lo_orderpriority) USING INVERTED COMMENT
'lo_orderpriority index',
+ INDEX lo_ordtotalprice_idx(lo_ordtotalprice) USING INVERTED COMMENT
'lo_ordtotalprice index'
+)
+UNIQUE KEY (`lo_orderdate`,`lo_orderkey`,`lo_linenumber`)
+PARTITION BY RANGE(`lo_orderdate`)
+(PARTITION p1992 VALUES [("-2147483648"), ("19930101")),
+PARTITION p1993 VALUES [("19930101"), ("19940101")),
+PARTITION p1994 VALUES [("19940101"), ("19950101")),
+PARTITION p1995 VALUES [("19950101"), ("19960101")),
+PARTITION p1996 VALUES [("19960101"), ("19970101")),
+PARTITION p1997 VALUES [("19970101"), ("19980101")),
+PARTITION p1998 VALUES [("19980101"), ("19990101")))
+DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 48
+PROPERTIES (
+"compression"="zstd",
+"replication_num" = "1",
+"enable_unique_key_merge_on_write" = "true"
+);
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/lineorder_delete.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/lineorder_delete.sql
new file mode 100644
index 0000000000..329e040060
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/lineorder_delete.sql
@@ -0,0 +1 @@
+truncate table lineorder;
\ No newline at end of file
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/part_create.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/part_create.sql
new file mode 100644
index 0000000000..695c872c6f
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/part_create.sql
@@ -0,0 +1,19 @@
+CREATE TABLE IF NOT EXISTS `part` (
+ `p_partkey` int(11) NOT NULL COMMENT "",
+ `p_name` varchar(23) NOT NULL COMMENT "",
+ `p_mfgr` varchar(7) NOT NULL COMMENT "",
+ `p_category` varchar(8) NOT NULL COMMENT "",
+ `p_brand` varchar(10) NOT NULL COMMENT "",
+ `p_color` varchar(12) NOT NULL COMMENT "",
+ `p_type` varchar(26) NOT NULL COMMENT "",
+ `p_size` int(11) NOT NULL COMMENT "",
+ `p_container` varchar(11) NOT NULL COMMENT "",
+ INDEX p_partkey_idx(p_partkey) USING INVERTED COMMENT 'p_partkey index'
+)
+UNIQUE KEY (`p_partkey`)
+DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 10
+PROPERTIES (
+"compression"="zstd",
+"replication_num" = "1",
+"enable_unique_key_merge_on_write" = "true"
+);
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/part_delete.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/part_delete.sql
new file mode 100644
index 0000000000..02c6abd253
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/part_delete.sql
@@ -0,0 +1 @@
+truncate table `part`;
\ No newline at end of file
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/supplier_create.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/supplier_create.sql
new file mode 100644
index 0000000000..2ef4545455
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/supplier_create.sql
@@ -0,0 +1,19 @@
+CREATE TABLE IF NOT EXISTS `supplier` (
+ `s_suppkey` int(11) NOT NULL COMMENT "",
+ `s_name` varchar(26) NOT NULL COMMENT "",
+ `s_address` varchar(26) NOT NULL COMMENT "",
+ `s_city` varchar(11) NOT NULL COMMENT "",
+ `s_nation` varchar(16) NOT NULL COMMENT "",
+ `s_region` varchar(13) NOT NULL COMMENT "",
+ `s_phone` varchar(16) NOT NULL COMMENT "",
+ INDEX s_suppkey_idx(s_suppkey) USING INVERTED COMMENT 's_suppkey index',
+ INDEX s_name_idx(s_name) USING INVERTED COMMENT 's_name index',
+ INDEX s_address_idx(s_address) USING INVERTED COMMENT 's_address index'
+)
+UNIQUE KEY (`s_suppkey`)
+DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 10
+PROPERTIES (
+"compression"="zstd",
+"replication_num" = "1",
+"enable_unique_key_merge_on_write" = "true"
+);
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/supplier_delete.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/supplier_delete.sql
new file mode 100644
index 0000000000..39e663134c
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/ddl/supplier_delete.sql
@@ -0,0 +1 @@
+truncate table `supplier`;
\ No newline at end of file
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/load.groovy
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/load.groovy
new file mode 100644
index 0000000000..417b5fa64c
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/load.groovy
@@ -0,0 +1,80 @@
+// 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.
+
+// Most of the cases are copied from
https://github.com/trinodb/trino/tree/master
+// /testing/trino-product-tests/src/main/resources/sql-tests/testcases
+// and modified by Doris.
+
+// Note: To filter out tables from sql files, use the following one-liner
comamnd
+// sed -nr 's/.*tables: (.*)$/\1/gp' /path/to/*.sql | sed -nr 's/,/\n/gp' |
sort | uniq
+suite("load") {
+ def tables = ["customer", "lineorder", "part", "date", "supplier"]
+ def columns =
["""c_custkey,c_name,c_address,c_city,c_nation,c_region,c_phone,c_mktsegment,no_use""",
+
"""lo_orderkey,lo_linenumber,lo_custkey,lo_partkey,lo_suppkey,lo_orderdate,lo_orderpriority,
+
lo_shippriority,lo_quantity,lo_extendedprice,lo_ordtotalprice,lo_discount,
+
lo_revenue,lo_supplycost,lo_tax,lo_commitdate,lo_shipmode,lo_dummy""",
+
"""p_partkey,p_name,p_mfgr,p_category,p_brand,p_color,p_type,p_size,p_container,p_dummy""",
+
"""d_datekey,d_date,d_dayofweek,d_month,d_year,d_yearmonthnum,d_yearmonth,
+
d_daynuminweek,d_daynuminmonth,d_daynuminyear,d_monthnuminyear,d_weeknuminyear,
+
d_sellingseason,d_lastdayinweekfl,d_lastdayinmonthfl,d_holidayfl,d_weekdayfl,d_dummy""",
+
"""s_suppkey,s_name,s_address,s_city,s_nation,s_region,s_phone,s_dummy"""]
+
+ for (String table in tables) {
+ sql new File("""${context.file.parent}/ddl/${table}_create.sql""").text
+ sql new File("""${context.file.parent}/ddl/${table}_delete.sql""").text
+ }
+ def i = 0
+ for (String tableName in tables) {
+ streamLoad {
+ // a default db 'regression_test' is specified in
+ // ${DORIS_HOME}/conf/regression-conf.groovy
+ table tableName
+
+ // default label is UUID:
+ // set 'label' UUID.randomUUID().toString()
+
+ // default column_separator is specify in doris fe config, usually
is '\t'.
+ // this line change to ','
+ set 'column_separator', '|'
+ set 'compress_type', 'GZ'
+ set 'columns', columns[i]
+
+
+ // relate to
${DORIS_HOME}/regression-test/data/demo/streamload_input.csv.
+ // also, you can stream load a http stream, e.g.
http://xxx/some.csv
+ file """${getS3Url()}/regression/ssb/sf0.1/${tableName}.tbl.gz"""
+
+ time 10000 // limit inflight 10s
+
+ // stream load action will check result, include Success status,
and NumberTotalRows == NumberLoadedRows
+
+ // if declared a check callback, the default check condition will
ignore.
+ // So you must check all condition
+ check { result, exception, startTime, endTime ->
+ if (exception != null) {
+ throw exception
+ }
+ log.info("Stream load result: ${result}".toString())
+ def json = parseJson(result)
+ assertEquals("success", json.Status.toLowerCase())
+ assertEquals(json.NumberTotalRows, json.NumberLoadedRows)
+ assertTrue(json.NumberLoadedRows > 0 && json.LoadBytes > 0)
+ }
+ }
+ i++
+ }
+}
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.1.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.1.sql
new file mode 100644
index 0000000000..4ef15e93ea
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.1.sql
@@ -0,0 +1,24 @@
+-- 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.
+
+SELECT SUM(lo_extendedprice*lo_discount) AS
+REVENUE
+FROM lineorder, date
+WHERE lo_orderdate = d_datekey
+AND d_year = 1993
+AND lo_discount BETWEEN 1 AND 3
+AND lo_quantity < 25;
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.2.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.2.sql
new file mode 100644
index 0000000000..1b8442bd93
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.2.sql
@@ -0,0 +1,24 @@
+-- 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.
+
+SELECT SUM(lo_extendedprice*lo_discount) AS
+REVENUE
+FROM lineorder, date
+WHERE lo_orderdate = d_datekey
+AND d_yearmonth = 'Jan1994'
+AND lo_discount BETWEEN 4 AND 6
+AND lo_quantity BETWEEN 26 AND 35;
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.3.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.3.sql
new file mode 100644
index 0000000000..ed6e51b1cf
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q1.3.sql
@@ -0,0 +1,25 @@
+-- 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.
+
+SELECT SUM(lo_extendedprice*lo_discount) AS
+REVENUE
+FROM lineorder, date
+WHERE lo_orderdate = d_datekey
+AND d_weeknuminyear= 6
+AND d_year = 1994
+AND lo_discount BETWEEN 5 AND 7
+AND lo_quantity BETWEEN 26 AND 35;
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.1.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.1.sql
new file mode 100644
index 0000000000..e1a1f52d18
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.1.sql
@@ -0,0 +1,26 @@
+-- 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.
+
+SELECT SUM(lo_revenue), d_year, p_brand
+FROM lineorder, date, part, supplier
+WHERE lo_orderdate = d_datekey
+AND lo_partkey = p_partkey
+AND lo_suppkey = s_suppkey
+AND p_category = 'MFGR#12'
+AND s_region = 'AMERICA'
+GROUP BY d_year, p_brand
+ORDER BY d_year, p_brand;
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.2.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.2.sql
new file mode 100644
index 0000000000..3db6170119
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.2.sql
@@ -0,0 +1,27 @@
+-- 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.
+
+SELECT SUM(lo_revenue), d_year, p_brand
+FROM lineorder, date, part, supplier
+WHERE lo_orderdate = d_datekey
+AND lo_partkey = p_partkey
+AND lo_suppkey = s_suppkey
+AND p_brand BETWEEN 'MFGR#2221'
+AND 'MFGR#2228'
+AND s_region = 'ASIA'
+GROUP BY d_year, p_brand
+ORDER BY d_year, p_brand;
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.3.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.3.sql
new file mode 100644
index 0000000000..b70ca90666
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q2.3.sql
@@ -0,0 +1,26 @@
+-- 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.
+
+SELECT SUM(lo_revenue), d_year, p_brand
+FROM lineorder, date, part, supplier
+WHERE lo_orderdate = d_datekey
+AND lo_partkey = p_partkey
+AND lo_suppkey = s_suppkey
+AND p_brand = 'MFGR#2239'
+AND s_region = 'EUROPE'
+GROUP BY d_year, p_brand
+ORDER BY d_year, p_brand;
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.1.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.1.sql
new file mode 100644
index 0000000000..70f17d789b
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.1.sql
@@ -0,0 +1,28 @@
+-- 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.
+
+SELECT c_nation, s_nation, d_year,
+SUM(lo_revenue) AS REVENUE
+FROM customer, lineorder, supplier, date
+WHERE lo_custkey = c_custkey
+AND lo_suppkey = s_suppkey
+AND lo_orderdate = d_datekey
+AND c_region = 'ASIA'
+AND s_region = 'ASIA'
+AND d_year >= 1992 AND d_year <= 1997
+GROUP BY c_nation, s_nation, d_year
+ORDER BY d_year ASC, REVENUE DESC;
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.2.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.2.sql
new file mode 100644
index 0000000000..a416fbea8b
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.2.sql
@@ -0,0 +1,28 @@
+-- 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.
+
+SELECT c_city, s_city, d_year, sum(lo_revenue)
+AS REVENUE
+FROM customer, lineorder, supplier, date
+WHERE lo_custkey = c_custkey
+AND lo_suppkey = s_suppkey
+AND lo_orderdate = d_datekey
+AND c_nation = 'UNITED STATES'
+AND s_nation = 'UNITED STATES'
+AND d_year >= 1992 AND d_year <= 1997
+GROUP BY c_city, s_city, d_year
+ORDER BY d_year ASC, REVENUE DESC;
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.3.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.3.sql
new file mode 100644
index 0000000000..98e29b72e7
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.3.sql
@@ -0,0 +1,30 @@
+-- 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.
+
+SELECT c_city, s_city, d_year, SUM(lo_revenue)
+AS REVENUE
+FROM customer, lineorder, supplier, date
+WHERE lo_custkey = c_custkey
+AND lo_suppkey = s_suppkey
+AND lo_orderdate = d_datekey
+AND (c_city='UNITED KI1'
+OR c_city='UNITED KI5')
+AND (s_city='UNITED KI1'
+OR s_city='UNITED KI5')
+AND d_year >= 1992 AND d_year <= 1997
+GROUP BY c_city, s_city, d_year
+ORDER BY d_year ASC, REVENUE DESC;
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.4.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.4.sql
new file mode 100644
index 0000000000..65fe992ca4
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q3.4.sql
@@ -0,0 +1,30 @@
+-- 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.
+
+SELECT c_city, s_city, d_year, SUM(lo_revenue)
+AS REVENUE
+FROM customer, lineorder, supplier, date
+WHERE lo_custkey = c_custkey
+AND lo_suppkey = s_suppkey
+AND lo_orderdate = d_datekey
+AND (c_city='UNITED KI1'
+OR c_city='UNITED KI5')
+AND (s_city='UNITED KI1'
+OR s_city='UNITED KI5')
+AND d_yearmonth = 'Dec1997'
+GROUP BY c_city, s_city, d_year
+ORDER BY d_year ASC, REVENUE DESC;
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.1.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.1.sql
new file mode 100644
index 0000000000..bdcd730bf9
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.1.sql
@@ -0,0 +1,30 @@
+-- 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.
+
+SELECT d_year, c_nation,
+SUM(lo_revenue - lo_supplycost) AS PROFIT
+FROM date, customer, supplier, part, lineorder
+WHERE lo_custkey = c_custkey
+AND lo_suppkey = s_suppkey
+AND lo_partkey = p_partkey
+AND lo_orderdate = d_datekey
+AND c_region = 'AMERICA'
+AND s_region = 'AMERICA'
+AND (p_mfgr = 'MFGR#1'
+OR p_mfgr = 'MFGR#2')
+GROUP BY d_year, c_nation
+ORDER BY d_year, c_nation;
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.2.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.2.sql
new file mode 100644
index 0000000000..24c82cf682
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.2.sql
@@ -0,0 +1,31 @@
+-- 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.
+
+SELECT d_year, s_nation, p_category,
+SUM(lo_revenue - lo_supplycost) AS PROFIT
+FROM date, customer, supplier, part, lineorder
+WHERE lo_custkey = c_custkey
+AND lo_suppkey = s_suppkey
+AND lo_partkey = p_partkey
+AND lo_orderdate = d_datekey
+AND c_region = 'AMERICA'
+AND s_region = 'AMERICA'
+AND (d_year = 1997 OR d_year = 1998)
+AND (p_mfgr = 'MFGR#1'
+OR p_mfgr = 'MFGR#2')
+GROUP BY d_year, s_nation, p_category
+ORDER BY d_year, s_nation, p_category;
diff --git
a/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.3.sql
b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.3.sql
new file mode 100644
index 0000000000..0dcc08bd26
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/ssb_unique_sql_zstd/sql/q4.3.sql
@@ -0,0 +1,29 @@
+-- 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.
+
+SELECT d_year, s_city, p_brand,
+SUM(lo_revenue - lo_supplycost) AS PROFIT
+FROM date, customer, supplier, part, lineorder
+WHERE lo_custkey = c_custkey
+AND lo_suppkey = s_suppkey
+AND lo_partkey = p_partkey
+AND lo_orderdate = d_datekey
+AND s_nation = 'UNITED STATES'
+AND (d_year = 1997 OR d_year = 1998)
+AND p_category = 'MFGR#14'
+GROUP BY d_year, s_city, p_brand
+ORDER BY d_year, s_city, p_brand;
diff --git
a/regression-test/suites/inverted_index_p0/test_inverted_index.groovy
b/regression-test/suites/inverted_index_p0/test_inverted_index.groovy
new file mode 100644
index 0000000000..cd2b2ca639
--- /dev/null
+++ b/regression-test/suites/inverted_index_p0/test_inverted_index.groovy
@@ -0,0 +1,270 @@
+// 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_inverted_index", "inverted_index") {
+ def tbName1 = "test_inverted_index_dup"
+
+ def getJobState = { tableName ->
+ def jobStateResult = sql """ SHOW ALTER TABLE COLUMN WHERE
TableName='${tableName}' ORDER BY createtime DESC LIMIT 1 """
+ return jobStateResult[0][9]
+ }
+ sql "DROP TABLE IF EXISTS ${tbName1}"
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tbName1} (
+ k1 TINYINT,
+ k2 SMALLINT,
+ k3 INT,
+ k4 BIGINT,
+ k5 CHAR,
+ k6 VARCHAR,
+ k7 DATE,
+ k8 DATETIME,
+ k9 LARGEINT,
+ k10 DECIMAL,
+ k11 BOOLEAN,
+ k12 DATEV2,
+ k13 DATETIMEV2,
+ k14 DATETIMEV2(3),
+ k15 DATETIMEV2(6)
+ )
+ DISTRIBUTED BY HASH(k1) BUCKETS 5 properties("replication_num" =
"1");
+ """
+
+ sql """
+ ALTER TABLE ${tbName1}
+ ADD INDEX index1 (k1) USING INVERTED,
+ ADD INDEX index2 (k2) USING INVERTED,
+ ADD INDEX index3 (k3) USING INVERTED,
+ ADD INDEX index4 (k4) USING INVERTED,
+ ADD INDEX index5 (k5) USING INVERTED,
+ ADD INDEX index6 (k6) USING INVERTED,
+ ADD INDEX index7 (k7) USING INVERTED,
+ ADD INDEX index8 (k8) USING INVERTED,
+ ADD INDEX index9 (k9) USING INVERTED,
+ ADD INDEX index10 (k10) USING INVERTED,
+ ADD INDEX index11 (k11) USING INVERTED,
+ ADD INDEX index12 (k12) USING INVERTED,
+ ADD INDEX index13 (k13) USING INVERTED,
+ ADD INDEX index14 (k14) USING INVERTED,
+ ADD INDEX index15 (k15) USING INVERTED;
+ """
+ int max_try_secs = 60
+ while (max_try_secs--) {
+ String res = getJobState(tbName1)
+ if (res == "FINISHED") {
+ sleep(3000)
+ break
+ } else {
+ Thread.sleep(1000)
+ if (max_try_secs < 1) {
+ println "test timeout," + "state:" + res
+ assertEquals("FINISHED", res)
+ }
+ }
+ }
+
+ sql "insert into ${tbName1}
values(1,1,1,1,'1','1','2022-05-31','2022-05-31
10:00:00',1,1.0,1,'2022-05-31','2022-05-31 10:00:00.111111','2022-05-31
10:00:00.111111','2022-05-31 10:00:00.111111');"
+ qt_sql "desc ${tbName1};"
+ qt_sql "SHOW INDEX FROM ${tbName1};"
+ qt_sql "select * from ${tbName1};"
+
+ sql "DROP INDEX IF EXISTS index1 ON ${tbName1};"
+ max_try_secs = 60
+ while (max_try_secs--) {
+ String res = getJobState(tbName1)
+ if (res == "FINISHED") {
+ sleep(3000)
+ break
+ } else {
+ Thread.sleep(1000)
+ if (max_try_secs < 1) {
+ println "test timeout," + "state:" + res
+ assertEquals("FINISHED", res)
+ }
+ }
+ }
+ sql "DROP TABLE ${tbName1} FORCE;"
+
+
+ def tbName2 = "test_inverted_index_agg"
+ sql "DROP TABLE IF EXISTS ${tbName2}"
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tbName2} (
+ k1 TINYINT,
+ k2 SMALLINT,
+ k3 INT,
+ k4 BIGINT,
+ k5 CHAR,
+ k6 VARCHAR,
+ k7 DATE,
+ k8 DATETIME,
+ k9 LARGEINT,
+ k10 DECIMAL,
+ k11 BOOLEAN,
+ k12 DATEV2,
+ k13 DATETIMEV2,
+ k14 DATETIMEV2(3),
+ k15 DATETIMEV2(6),
+ v1 INT SUM
+ )
+ AGGREGATE KEY(k1,k2,k3,k4,k5,k6,k7,k8,k9,k10,k11,k12,k13,k14,k15)
+ DISTRIBUTED BY HASH(k1) BUCKETS 5 properties("replication_num" =
"1");
+ """
+
+ sql """
+ ALTER TABLE ${tbName2}
+ ADD INDEX index1 (k1) USING INVERTED,
+ ADD INDEX index2 (k2) USING INVERTED,
+ ADD INDEX index3 (k3) USING INVERTED,
+ ADD INDEX index4 (k4) USING INVERTED,
+ ADD INDEX index5 (k5) USING INVERTED,
+ ADD INDEX index6 (k6) USING INVERTED,
+ ADD INDEX index7 (k7) USING INVERTED,
+ ADD INDEX index8 (k8) USING INVERTED,
+ ADD INDEX index9 (k9) USING INVERTED,
+ ADD INDEX index10 (k10) USING INVERTED,
+ ADD INDEX index11 (k11) USING INVERTED,
+ ADD INDEX index12 (k12) USING INVERTED,
+ ADD INDEX index13 (k13) USING INVERTED,
+ ADD INDEX index14 (k14) USING INVERTED,
+ ADD INDEX index15 (k15) USING INVERTED;
+ """
+ max_try_secs = 60
+ while (max_try_secs--) {
+ String res = getJobState(tbName2)
+ if (res == "FINISHED") {
+ sleep(3000)
+ break
+ } else {
+ Thread.sleep(1000)
+ if (max_try_secs < 1) {
+ println "test timeout," + "state:" + res
+ assertEquals("FINISHED",res)
+ }
+ }
+ }
+ test{
+ sql "ALTER TABLE ${tbName2} ADD INDEX index16 (v1) USING INVERTED;"
+ exception "errCode = 2, detailMessage = INVERTED index only used in
columns of DUP_KEYS table or UNIQUE_KEYS table with merge_on_write enabled or
key columns of AGG_KEYS table. invalid column: v1"
+ }
+
+ sql "insert into ${tbName2}
values(1,1,1,1,'1','1','2022-05-31','2022-05-31
10:00:00',1,1.0,1,'2022-05-31','2022-05-31 10:00:00.111111','2022-05-31
10:00:00.111111','2022-05-31 10:00:00.111111',1);"
+ qt_sql "desc ${tbName2};"
+ qt_sql "SHOW INDEX FROM ${tbName2};"
+ qt_sql "select * from ${tbName2};"
+
+ sql "DROP INDEX IF EXISTS index1 ON ${tbName2};"
+ max_try_secs = 60
+ while (max_try_secs--) {
+ String res = getJobState(tbName2)
+ if (res == "FINISHED") {
+ sleep(3000)
+ break
+ } else {
+ Thread.sleep(1000)
+ if (max_try_secs < 1) {
+ println "test timeout," + "state:" + res
+ assertEquals("FINISHED",res)
+ }
+ }
+ }
+ sql "DROP TABLE ${tbName2} FORCE;"
+
+ def tbName3 = "test_inverted_index_unique"
+ sql "DROP TABLE IF EXISTS ${tbName3}"
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tbName3} (
+ k1 TINYINT,
+ k2 SMALLINT,
+ k3 INT,
+ k4 BIGINT,
+ k5 CHAR,
+ k6 VARCHAR,
+ k7 DATE,
+ k8 DATETIME,
+ k9 LARGEINT,
+ k10 DECIMAL,
+ k11 BOOLEAN,
+ k12 DATEV2,
+ k13 DATETIMEV2,
+ k14 DATETIMEV2(3),
+ k15 DATETIMEV2(6),
+ v1 INT
+ )
+ UNIQUE KEY(k1,k2,k3,k4,k5,k6,k7,k8,k9,k10,k11)
+ DISTRIBUTED BY HASH(k1) BUCKETS 5
+ PROPERTIES (
+ "replication_num" = "1",
+ "enable_unique_key_merge_on_write" = "true"
+ );
+ """
+
+ sql """
+ ALTER TABLE ${tbName3}
+ ADD INDEX index1 (k1) USING INVERTED,
+ ADD INDEX index2 (k2) USING INVERTED,
+ ADD INDEX index3 (k3) USING INVERTED,
+ ADD INDEX index4 (k4) USING INVERTED,
+ ADD INDEX index5 (k5) USING INVERTED,
+ ADD INDEX index6 (k6) USING INVERTED,
+ ADD INDEX index7 (k7) USING INVERTED,
+ ADD INDEX index8 (k8) USING INVERTED,
+ ADD INDEX index9 (k9) USING INVERTED,
+ ADD INDEX index10 (k10) USING INVERTED,
+ ADD INDEX index11 (k11) USING INVERTED,
+ ADD INDEX index12 (k12) USING INVERTED,
+ ADD INDEX index13 (k13) USING INVERTED,
+ ADD INDEX index14 (k14) USING INVERTED,
+ ADD INDEX index15 (k15) USING INVERTED,
+ ADD INDEX index16 (v1) USING INVERTED;
+ """
+ max_try_secs = 60
+ while (max_try_secs--) {
+ String res = getJobState(tbName3)
+ if (res == "FINISHED") {
+ sleep(3000)
+ break
+ } else {
+ Thread.sleep(1000)
+ if (max_try_secs < 1) {
+ println "test timeout," + "state:" + res
+ assertEquals("FINISHED",res)
+ }
+ }
+ }
+
+ sql "insert into ${tbName3}
values(1,1,1,1,'1','1','2022-05-31','2022-05-31
10:00:00',1,1.0,1,'2022-05-31','2022-05-31 10:00:00.111111','2022-05-31
10:00:00.111111','2022-05-31 10:00:00.111111',1);"
+ qt_sql "desc ${tbName3};"
+ qt_sql "SHOW INDEX FROM ${tbName3};"
+ qt_sql "select * from ${tbName3};"
+
+ sql "DROP INDEX IF EXISTS index1 ON ${tbName3};"
+ max_try_secs = 60
+ while (max_try_secs--) {
+ String res = getJobState(tbName3)
+ if (res == "FINISHED") {
+ sleep(3000)
+ break
+ } else {
+ Thread.sleep(1000)
+ if (max_try_secs < 1) {
+ println "test timeout," + "state:" + res
+ assertEquals("FINISHED",res)
+ }
+ }
+ }
+ sql "DROP TABLE ${tbName3} FORCE;"
+}
diff --git
a/regression-test/suites/inverted_index_p0/unique_with_mow/test_mow_with_null_sequence.groovy
b/regression-test/suites/inverted_index_p0/unique_with_mow/test_mow_with_null_sequence.groovy
new file mode 100644
index 0000000000..92615e44d4
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/unique_with_mow/test_mow_with_null_sequence.groovy
@@ -0,0 +1,148 @@
+// 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_mow_with_null_sequence", "inverted_index") {
+ def tableName = "test_null_sequence"
+ sql """ DROP TABLE IF EXISTS $tableName """
+ sql """
+ CREATE TABLE `$tableName` (
+ `c_custkey` varchar(20) NOT NULL COMMENT "",
+ `c_name` varchar(20) NOT NULL COMMENT "",
+ `c_address` varchar(20) NOT NULL COMMENT "",
+ `c_date` date NULL COMMENT "",
+ INDEX c_custkey_idx(c_custkey) USING INVERTED COMMENT
'c_custkey index',
+ INDEX c_name_idx(c_name) USING INVERTED
PROPERTIES("parser"="english") COMMENT 'c_name index',
+ INDEX c_address_idx(c_address) USING INVERTED
PROPERTIES("parser"="standard") COMMENT 'c_address index',
+ INDEX c_date_index(c_date) USING INVERTED COMMENT
'c_date_index index'
+ )
+ UNIQUE KEY (`c_custkey`)
+ DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 10
+ PROPERTIES (
+ "function_column.sequence_col" = 'c_date',
+ "replication_num" = "1",
+ "enable_unique_key_merge_on_write" = "true"
+ );
+ """
+
+
+ sql """ insert into $tableName values('a', 'zhang san', 'address1',
NULL) """
+ sql """ insert into $tableName values('a', 'zhang si', 'address2',
'2022-10-20') """
+ sql """ insert into $tableName values('a', 'li si', 'address3', NULL)
"""
+ sql """ insert into $tableName values('ab', 'zhang yi', 'address4',
NULL) """
+ sql """ insert into $tableName values('ab', 'li yi', 'address5',
'2022-10-20') """
+ sql """ insert into $tableName values('ab', 'zhang si yi', 'address6',
'2022-11-20') """
+ sql """ insert into $tableName values('ab', 'zhang ke', 'address6',
'2022-11-15') """
+ sql """ insert into $tableName values('aa1234', 'li yi ke',
'address4', '2022-12-11') """
+ sql """ insert into $tableName values('aa1234', 'li ke', 'address5',
NULL) """
+ sql """ insert into $tableName values('aa1235', 'li wu', 'address6',
NULL) """
+
+ qt_sql1 """ select * from $tableName order by c_custkey """
+ qt_sql2 """ select * from $tableName where c_custkey='a' order by
c_custkey """
+ qt_sql3 """ select * from $tableName where c_custkey='ab' order by
c_custkey """
+ qt_sql4 """ select * from $tableName where c_custkey>'aa' order by
c_custkey """
+
+ qt_sql5 """ select * from $tableName where c_name match_any 'zhang'
order by c_custkey """
+ qt_sql6 """ select * from $tableName where c_name match_any 'zhang'
and c_date is not null order by c_custkey """
+ qt_sql7 """ select * from $tableName where c_name match_any 'zhang'
and c_date is null order by c_custkey """
+ qt_sql8 """ select * from $tableName where c_name match_any 'zhang'
and c_date > '2022-10-20' order by c_custkey """
+
+ qt_sql9 """ select * from $tableName where c_name match_any 'li' order
by c_custkey """
+ qt_sql10 """ select * from $tableName where c_name match_any 'li' and
c_date is not null order by c_custkey """
+ qt_sql11 """ select * from $tableName where c_name match_any 'li' and
c_date is null order by c_custkey """
+ qt_sql12 """ select * from $tableName where c_name match_any 'li' and
c_date > '2022-10-20' order by c_custkey """
+
+ qt_sql13 """ select * from $tableName where c_name match_any 'wu'
order by c_custkey """
+ qt_sql14 """ select * from $tableName where c_name match_any 'wu' and
c_date is not null order by c_custkey """
+ qt_sql15 """ select * from $tableName where c_name match_any 'wu' and
c_date > '2022-10-20' order by c_custkey """
+
+ qt_sql16 """ select * from $tableName where c_name match_all 'zhang
si' order by c_custkey """
+ qt_sql17 """ select * from $tableName where c_name match_all 'zhang
si' and c_date < '2022-11-20' order by c_custkey """
+ qt_sql18 """ select * from $tableName where c_name match_all 'zhang
yi' order by c_custkey """
+ qt_sql19 """ select * from $tableName where c_name match_all 'zhang
yi' and c_date is not null order by c_custkey """
+
+ qt_sql20 """ select * from $tableName where c_name match_all 'li yi'
order by c_custkey """
+ qt_sql21 """ select * from $tableName where c_name match_all 'li yi'
and (c_date < '2022-11-20' or c_date >= '2022-12-11') order by c_custkey """
+
+ sql """ DROP TABLE IF EXISTS $tableName """
+
+ sql """
+ CREATE TABLE `$tableName` (
+ `c_custkey` varchar(20) NOT NULL COMMENT "",
+ `c_name` varchar(20) NOT NULL COMMENT "",
+ `c_address` varchar(20) NOT NULL COMMENT "",
+ `c_int` int NULL COMMENT "",
+ INDEX c_custkey_idx(c_custkey) USING INVERTED COMMENT
'c_custkey index',
+ INDEX c_name_idx(c_name) USING INVERTED
PROPERTIES("parser"="english") COMMENT 'c_name index',
+ INDEX c_address_idx(c_address) USING INVERTED
PROPERTIES("parser"="standard") COMMENT 'c_address index',
+ INDEX c_int_index(c_int) USING INVERTED COMMENT
'c_int_index index'
+ )
+ UNIQUE KEY (`c_custkey`)
+ DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 10
+ PROPERTIES (
+ "function_column.sequence_col" = 'c_int',
+ "replication_num" = "1",
+ "enable_unique_key_merge_on_write" = "true"
+ );
+ """
+ sql """ insert into $tableName values('a', 'zhang san', 'address1',
NULL) """
+ sql """ insert into $tableName values('a', 'zhang si', 'address2',
100) """
+ sql """ insert into $tableName values('a', 'li si', 'address3', NULL)
"""
+
+ sql """ insert into $tableName values('ab', 'li san', 'address4',
NULL) """
+ sql """ insert into $tableName values('ab', 'li yi', 'address5', -10)
"""
+ sql """ insert into $tableName values('ab', 'zhang yi', 'address6',
110) """
+ sql """ insert into $tableName values('ab', 'zhang san yi',
'address6', 100) """
+
+ sql """ insert into $tableName values('aa1234', 'wu ke', 'address4',
-1) """
+ sql """ insert into $tableName values('aa1234', 'li ke', 'address5',
NULL) """
+
+ sql """ insert into $tableName values('aa1235', 'li yi ke',
'address6', NULL) """
+ sql """ insert into $tableName values('aa1235', 'zhang ke',
'address6', -1) """
+
+ sql """ insert into $tableName values('aa1236', 'wu si', 'address6',
NULL) """
+ sql """ insert into $tableName values('aa1236', 'wu yi', 'address6',
0) """
+ sql """ insert into $tableName values('aa1236', 'zhang ke',
'address6', NULL) """
+
+ qt_sql22 """ select * from $tableName order by c_custkey """
+ qt_sql23 """ select * from $tableName where c_custkey='a' order by
c_custkey """
+ qt_sql24 """ select * from $tableName where c_custkey='ab' order by
c_custkey """
+ qt_sql25 """ select * from $tableName where c_custkey>'aa' order by
c_custkey """
+
+ qt_sql26 """ select * from $tableName where c_name match_any 'zhang'
order by c_custkey """
+ qt_sql27 """ select * from $tableName where c_name match_any 'zhang'
and c_int is not null order by c_custkey """
+ qt_sql28 """ select * from $tableName where c_name match_any 'zhang'
and c_int is null order by c_custkey """
+ qt_sql29 """ select * from $tableName where c_name match_any 'zhang'
and c_int > 0 order by c_custkey """
+
+ qt_sql30 """ select * from $tableName where c_name match_any 'li'
order by c_custkey """
+ qt_sql31 """ select * from $tableName where c_name match_any 'li' and
c_int is not null order by c_custkey """
+ qt_sql32 """ select * from $tableName where c_name match_any 'li' and
c_int is null order by c_custkey """
+ qt_sql33 """ select * from $tableName where c_name match_any 'li' and
c_int < 0 order by c_custkey """
+
+ qt_sql34 """ select * from $tableName where c_name match_any 'wu'
order by c_custkey """
+ qt_sql35 """ select * from $tableName where c_name match_any 'wu' and
c_int is not null order by c_custkey """
+ qt_sql36 """ select * from $tableName where c_name match_any 'wu' and
c_int >= 0 order by c_custkey """
+
+ qt_sql37 """ select * from $tableName where c_name match_all 'zhang
si' order by c_custkey """
+ qt_sql38 """ select * from $tableName where c_name match_all 'zhang
si' and c_int < 0 order by c_custkey """
+ qt_sql39 """ select * from $tableName where c_name match_all 'zhang
yi' order by c_custkey """
+ qt_sql40 """ select * from $tableName where c_name match_all 'zhang
yi' and c_int > 100 order by c_custkey """
+
+ qt_sql41 """ select * from $tableName where c_name match_all 'li yi'
order by c_custkey """
+ qt_sql42 """ select * from $tableName where c_name match_all 'li yi'
and c_int < 0 order by c_custkey """
+ qt_sql43 """ select * from $tableName where c_name match_all 'li yi'
and (c_int < 0 or c_int is null) order by c_custkey """
+
+}
diff --git
a/regression-test/suites/inverted_index_p0/unique_with_mow/test_pk_uk_case.groovy
b/regression-test/suites/inverted_index_p0/unique_with_mow/test_pk_uk_case.groovy
new file mode 100644
index 0000000000..05ce6f56a8
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/unique_with_mow/test_pk_uk_case.groovy
@@ -0,0 +1,271 @@
+// 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;
+import java.util.Random;
+import org.apache.commons.lang.RandomStringUtils;
+import java.util.Date;
+import java.text.SimpleDateFormat;
+import java.time.LocalDate;
+import java.time.LocalDateTime;
+import java.util.Map;
+import java.util.UUID;
+import java.time.format.DateTimeFormatter;
+
+suite("test_pk_uk_case", "inverted_index") {
+ def tableNamePk = "primary_key_pk_uk"
+ def tableNameUk = "unique_key_pk_uk"
+
+ onFinish {
+ try_sql("DROP TABLE IF EXISTS ${tableNamePk}")
+ try_sql("DROP TABLE IF EXISTS ${tableNameUk}")
+ }
+
+ sql """ DROP TABLE IF EXISTS ${tableNamePk} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableNamePk} (
+ L_ORDERKEY INTEGER NOT NULL,
+ L_PARTKEY INTEGER NOT NULL,
+ L_SUPPKEY INTEGER NOT NULL,
+ L_LINENUMBER INTEGER NOT NULL,
+ L_QUANTITY DECIMAL(15,2) NOT NULL,
+ L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
+ L_DISCOUNT DECIMAL(15,2) NOT NULL,
+ L_TAX DECIMAL(15,2) NOT NULL,
+ L_RETURNFLAG CHAR(1) NOT NULL,
+ L_LINESTATUS CHAR(1) NOT NULL,
+ L_SHIPDATE DATE NOT NULL,
+ L_COMMITDATE DATE NOT NULL,
+ L_RECEIPTDATE DATE NOT NULL,
+ L_SHIPINSTRUCT CHAR(60) NOT NULL,
+ L_SHIPMODE CHAR(60) NOT NULL,
+ L_COMMENT VARCHAR(60) NOT NULL,
+ INDEX L_ORDERKEY_idx(L_ORDERKEY) USING INVERTED COMMENT 'L_ORDERKEY
index',
+ INDEX L_PARTKEY_idx(L_PARTKEY) USING INVERTED COMMENT 'L_PARTKEY
index',
+ INDEX L_SUPPKEY_idx(L_SUPPKEY) USING INVERTED COMMENT 'L_SUPPKEY
index',
+ INDEX L_LINENUMBER_idx(L_LINENUMBER) USING INVERTED COMMENT
'L_LINENUMBER index',
+ INDEX L_QUANTITY_idx(L_QUANTITY) USING INVERTED COMMENT 'L_QUANTITY
index',
+ INDEX L_RETURNFLAG_idx(L_RETURNFLAG) USING INVERTED COMMENT
'L_RETURNFLAG index',
+ INDEX L_SHIPDATE_idx(L_SHIPDATE) USING INVERTED COMMENT 'L_SHIPDATE
index'
+ )
+ UNIQUE KEY(L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER)
+ DISTRIBUTED BY HASH(L_ORDERKEY) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1",
+ "enable_unique_key_merge_on_write" = "true"
+ )
+ """
+
+ sql """ DROP TABLE IF EXISTS ${tableNameUk} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableNameUk} (
+ L_ORDERKEY INTEGER NOT NULL,
+ L_PARTKEY INTEGER NOT NULL,
+ L_SUPPKEY INTEGER NOT NULL,
+ L_LINENUMBER INTEGER NOT NULL,
+ L_QUANTITY DECIMAL(15,2) NOT NULL,
+ L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
+ L_DISCOUNT DECIMAL(15,2) NOT NULL,
+ L_TAX DECIMAL(15,2) NOT NULL,
+ L_RETURNFLAG CHAR(1) NOT NULL,
+ L_LINESTATUS CHAR(1) NOT NULL,
+ L_SHIPDATE DATE NOT NULL,
+ L_COMMITDATE DATE NOT NULL,
+ L_RECEIPTDATE DATE NOT NULL,
+ L_SHIPINSTRUCT CHAR(60) NOT NULL,
+ L_SHIPMODE CHAR(60) NOT NULL,
+ L_COMMENT VARCHAR(60) NOT NULL
+ )
+ UNIQUE KEY(L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER)
+ DISTRIBUTED BY HASH(L_ORDERKEY) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ """
+
+ Random rd = new Random()
+ def order_key = rd.nextInt(1000)
+ def part_key = rd.nextInt(1000)
+ def sub_key = 13
+ def line_num = 29
+ def decimal = rd.nextFloat()
+ def city = RandomStringUtils.randomAlphabetic(10)
+ def name = UUID.randomUUID().toString()
+ def date =
DateTimeFormatter.ofPattern("yyyy-MM-dd").format(LocalDateTime.now())
+ for (int idx = 0; idx < 10; idx++) {
+ order_key = rd.nextInt(10)
+ part_key = rd.nextInt(10)
+ decimal = rd.nextFloat()
+ city = RandomStringUtils.randomAlphabetic(10)
+ name = UUID.randomUUID().toString()
+ sql """ INSERT INTO ${tableNamePk} VALUES
+ ($order_key, $part_key, $sub_key, $line_num,
+ $decimal, $decimal, $decimal, $decimal, '1', '1', '$date',
'$date', '$date', '$name', '$name', '$city')
+ """
+ sql """ INSERT INTO ${tableNameUk} VALUES
+ ($order_key, $part_key, $sub_key, $line_num,
+ $decimal, $decimal, $decimal, $decimal, '1', '1', '$date',
'$date', '$date', '$name', '$name', '$city')
+ """
+
+ order_key = rd.nextInt(10)
+ part_key = rd.nextInt(10)
+ decimal = rd.nextFloat()
+ city = RandomStringUtils.randomAlphabetic(10)
+ name = UUID.randomUUID().toString()
+ sql """ INSERT INTO ${tableNamePk} VALUES
+ ($order_key, $part_key, $sub_key, $line_num,
+ $decimal, $decimal, $decimal, $decimal, '1', '1', '$date',
'$date', '$date', '$name', '$name', '$city')
+ """
+ sql """ INSERT INTO ${tableNameUk} VALUES
+ ($order_key, $part_key, $sub_key, $line_num,
+ $decimal, $decimal, $decimal, $decimal, '1', '1', '$date',
'$date', '$date', '$name', '$name', '$city')
+ """
+
+ order_key = rd.nextInt(10)
+ part_key = rd.nextInt(10)
+ decimal = rd.nextFloat()
+ city = RandomStringUtils.randomAlphabetic(10)
+ name = UUID.randomUUID().toString()
+ sql """ INSERT INTO ${tableNamePk} VALUES
+ ($order_key, $part_key, $sub_key, $line_num,
+ $decimal, $decimal, $decimal, $decimal, '1', '1', '$date',
'$date', '$date', '$name', '$name', '$city')
+ """
+ sql """ INSERT INTO ${tableNameUk} VALUES
+ ($order_key, $part_key, $sub_key, $line_num,
+ $decimal, $decimal, $decimal, $decimal, '1', '1', '$date',
'$date', '$date', '$name', '$name', '$city')
+ """
+
+ order_key = rd.nextInt(10)
+ part_key = rd.nextInt(10)
+ decimal = rd.nextFloat()
+ city = RandomStringUtils.randomAlphabetic(10)
+ name = UUID.randomUUID().toString()
+ sql """ INSERT INTO ${tableNamePk} VALUES
+ ($order_key, $part_key, $sub_key, $line_num,
+ $decimal, $decimal, $decimal, $decimal, '1', '1', '$date',
'$date', '$date', '$name', '$name', '$city')
+ """
+ sql """ INSERT INTO ${tableNameUk} VALUES
+ ($order_key, $part_key, $sub_key, $line_num,
+ $decimal, $decimal, $decimal, $decimal, '1', '1', '$date',
'$date', '$date', '$name', '$name', '$city')
+ """
+
+ order_key = rd.nextInt(10)
+ part_key = rd.nextInt(10)
+ decimal = rd.nextFloat()
+ city = RandomStringUtils.randomAlphabetic(10)
+ name = UUID.randomUUID().toString()
+ sql """ INSERT INTO ${tableNamePk} VALUES
+ ($order_key, $part_key, $sub_key, $line_num,
+ $decimal, $decimal, $decimal, $decimal, '1', '1', '$date',
'$date', '$date', '$name', '$name', '$city')
+ """
+ sql """ INSERT INTO ${tableNameUk} VALUES
+ ($order_key, $part_key, $sub_key, $line_num,
+ $decimal, $decimal, $decimal, $decimal, '1', '1', '$date',
'$date', '$date', '$name', '$name', '$city')
+ """
+
+ // insert batch key
+ order_key = rd.nextInt(10)
+ part_key = rd.nextInt(10)
+ decimal = rd.nextFloat()
+ city = RandomStringUtils.randomAlphabetic(10)
+ name = UUID.randomUUID().toString()
+ sql """ INSERT INTO ${tableNamePk} VALUES
+ ($order_key, $part_key, $sub_key, $line_num, $decimal, $decimal,
$decimal, $decimal, '1', '1', '$date', '$date', '$date', '$name', '$name',
'$city'),
+ ($order_key, $part_key, $sub_key, $line_num, $decimal, $decimal,
$decimal, $decimal, '1', '1', '$date', '$date', '$date', '$name', '$name',
'$city'),
+ ($order_key, $part_key, $sub_key, $line_num, $decimal, $decimal,
$decimal, $decimal, '1', '1', '$date', '$date', '$date', '$name', '$name',
'$city'),
+ ($order_key, $part_key, $sub_key, $line_num, $decimal, $decimal,
$decimal, $decimal, '1', '1', '$date', '$date', '$date', '$name', '$name',
'$city')
+ """
+ sql """ INSERT INTO ${tableNameUk} VALUES
+ ($order_key, $part_key, $sub_key, $line_num, $decimal, $decimal,
$decimal, $decimal, '1', '1', '$date', '$date', '$date', '$name', '$name',
'$city'),
+ ($order_key, $part_key, $sub_key, $line_num, $decimal, $decimal,
$decimal, $decimal, '1', '1', '$date', '$date', '$date', '$name', '$name',
'$city'),
+ ($order_key, $part_key, $sub_key, $line_num, $decimal, $decimal,
$decimal, $decimal, '1', '1', '$date', '$date', '$date', '$name', '$name',
'$city'),
+ ($order_key, $part_key, $sub_key, $line_num, $decimal, $decimal,
$decimal, $decimal, '1', '1', '$date', '$date', '$date', '$name', '$name',
'$city')
+ """
+
+ // count(*)
+ result0 = sql """ SELECT count(*) FROM ${tableNamePk}; """
+ result1 = sql """ SELECT count(*) FROM ${tableNameUk}; """
+ logger.info("result:" + result0[0][0] + "|" + result1[0][0])
+ assertTrue(result0[0]==result1[0])
+ if (result0[0][0]!=result1[0][0]) {
+ logger.info("result:" + result0[0][0] + "|" + result1[0][0])
+ }
+
+ result0 = sql """ SELECT
+ l_returnflag,
+ l_linestatus,
+ sum(l_quantity)
AS sum_qty,
+ sum(l_extendedprice)
AS sum_base_price,
+ sum(l_extendedprice * (1 - l_discount))
AS sum_disc_price,
+ sum(l_extendedprice * (1 - l_discount) * (1 +
l_tax)) AS sum_charge,
+ avg(l_quantity)
AS avg_qty,
+ avg(l_extendedprice)
AS avg_price,
+ avg(l_discount)
AS avg_disc,
+ count(*)
AS count_order
+ FROM
+ ${tableNamePk}
+ WHERE
+ l_shipdate <= DATE '2023-01-01' - INTERVAL '90' DAY
+ GROUP BY
+ l_returnflag,
+ l_linestatus
+ ORDER BY
+ l_returnflag,
+ l_linestatus
+ """
+ result1 = sql """ SELECT
+ l_returnflag,
+ l_linestatus,
+ sum(l_quantity)
AS sum_qty,
+ sum(l_extendedprice)
AS sum_base_price,
+ sum(l_extendedprice * (1 - l_discount))
AS sum_disc_price,
+ sum(l_extendedprice * (1 - l_discount) * (1 +
l_tax)) AS sum_charge,
+ avg(l_quantity)
AS avg_qty,
+ avg(l_extendedprice)
AS avg_price,
+ avg(l_discount)
AS avg_disc,
+ count(*)
AS count_order
+ FROM
+ ${tableNameUk}
+ WHERE
+ l_shipdate <= DATE '2023-01-01' - INTERVAL '90' DAY
+ GROUP BY
+ l_returnflag,
+ l_linestatus
+ ORDER BY
+ l_returnflag,
+ l_linestatus
+ """
+ assertTrue(result0.size()==result1.size())
+ for (int i = 0; i < result0.size(); ++i) {
+ for (j = 0; j < result0[0].size(); j++) {
+ logger.info("result: " + result0[i][j] + "|" + result1[i][j])
+ assertTrue(result0[i][j]==result1[i][j])
+ }
+ }
+
+ // delete
+ if (idx % 10 == 0) {
+ order_key = rd.nextInt(10)
+ part_key = rd.nextInt(10)
+ result0 = sql """ SELECT count(*) FROM ${tableNamePk} where
L_ORDERKEY < $order_key and L_PARTKEY < $part_key; """
+ result1 = sql """ SELECT count(*) FROM ${tableNameUk} where
L_ORDERKEY < $order_key and L_PARTKEY < $part_key"""
+ logger.info("result:" + result0[0][0] + "|" + result1[0][0])
+ sql "DELETE FROM ${tableNamePk} where L_ORDERKEY < $order_key and
L_PARTKEY < $part_key"
+ sql "DELETE FROM ${tableNameUk} where L_ORDERKEY < $order_key and
L_PARTKEY < $part_key"
+ }
+ }
+}
diff --git
a/regression-test/suites/inverted_index_p0/unique_with_mow/test_primary_key_simple_case.groovy
b/regression-test/suites/inverted_index_p0/unique_with_mow/test_primary_key_simple_case.groovy
new file mode 100644
index 0000000000..b0ffda2975
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/unique_with_mow/test_primary_key_simple_case.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_primary_key_simple_case", "inverted_index") {
+ def tableName = "primary_key_simple_case"
+ onFinish {
+ try_sql("DROP TABLE IF EXISTS ${tableName}")
+ }
+
+ 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 "用户性别",
+ `last_visit_date` DATETIME DEFAULT "1970-01-01 00:00:00" COMMENT
"用户最后一次访问时间",
+ `last_update_date` DATETIME DEFAULT "1970-01-01 00:00:00" COMMENT
"用户最后一次更新时间",
+ `last_visit_date_not_null` DATETIME NOT NULL DEFAULT "1970-01-01
00:00:00" COMMENT "用户最后一次访问时间",
+ `cost` BIGINT DEFAULT "0" COMMENT "用户总消费",
+ `max_dwell_time` INT DEFAULT "0" COMMENT "用户最大停留时间",
+ `min_dwell_time` INT DEFAULT "99999" COMMENT "用户最小停留时间",
+ INDEX user_id_idx(user_id) USING INVERTED COMMENT 'user_id index',
+ INDEX date_idx(date) USING INVERTED COMMENT 'date index',
+ INDEX city_idx(city) USING INVERTED COMMENT 'city index',
+ INDEX age_idx(age) USING INVERTED COMMENT 'age index',
+ INDEX sex_idx(sex) USING INVERTED COMMENT 'sex index')
+ UNIQUE KEY(`user_id`, `date`, `city`, `age`, `sex`) DISTRIBUTED BY
HASH(`user_id`)
+ PROPERTIES ( "replication_num" = "1",
+ "enable_unique_key_merge_on_write" = "true");
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (1, '2017-10-01', 'Beijing', 10, 1, '2020-01-01', '2020-01-01',
'2020-01-01', 1, 30, 20)
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (2, '2017-10-01', 'Beijing', 10, 1, '2020-01-02', '2020-01-02',
'2020-01-02', 1, 31, 21)
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (3, '2017-10-01', 'Beijing', 10, 1, '2020-01-03', '2020-01-03',
'2020-01-03', 1, 32, 20)
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (4, '2017-10-01', 'Beijing', 10, 1, '2020-01-03', '2020-01-03',
'2020-01-03', 1, 32, 22)
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (5, '2017-10-01', 'Beijing', 10, 1, NULL, NULL, '2020-01-05', 1,
34, 20)
+ """
+
+ result = sql """ SELECT * FROM ${tableName} t ORDER BY user_id; """
+ assertTrue(result.size() == 5)
+ assertTrue(result[0].size() == 11)
+
+ // insert a duplicate key
+ sql """ INSERT INTO ${tableName} VALUES
+ (5, '2017-10-01', 'Beijing', 10, 1, NULL, NULL, '2020-01-05', 1,
34, 21)
+ """
+ result = sql """ SELECT * FROM ${tableName} t ORDER BY user_id; """
+ assertTrue(result.size() == 5)
+ assertTrue(result[4][10] == 21)
+
+ // insert a duplicate key
+ sql """ INSERT INTO ${tableName} VALUES
+ (5, '2017-10-01', 'Beijing', 10, 1, NULL, NULL, '2020-01-05', 1,
34, 22)
+ """
+ result = sql """ SELECT * FROM ${tableName} t ORDER BY user_id; """
+ assertTrue(result.size() == 5)
+ logger.info("fuck: " + result.size())
+ assertTrue(result[4][10] == 22)
+
+ result = sql """ SELECT * FROM ${tableName} t where user_id = 5; """
+ assertTrue(result.size() == 1)
+ assertTrue(result[0][10] == 22)
+
+ result = sql """ SELECT COUNT(*) FROM ${tableName};"""
+ assertTrue(result.size() == 1)
+ assertTrue(result[0][0] == 5)
+
+ // insert a new key
+ sql """ INSERT INTO ${tableName} VALUES
+ (6, '2017-10-01', 'Beijing', 10, 1, NULL, NULL, '2020-01-05', 1,
34, 22)
+ """
+ result = sql """ SELECT * FROM ${tableName} t ORDER BY user_id; """
+ assertTrue(result.size() == 6)
+
+ // insert batch key
+ sql """ INSERT INTO ${tableName} VALUES
+ (7, '2017-10-01', 'Beijing', 10, 1, NULL, NULL, '2020-01-05', 1,
34, 22),
+ (7, '2017-10-01', 'Beijing', 10, 1, NULL, NULL, '2020-01-05', 1,
34, 23),
+ (7, '2017-10-01', 'Beijing', 10, 1, NULL, NULL, '2020-01-05', 1,
34, 24),
+ (7, '2017-10-01', 'Beijing', 10, 1, NULL, NULL, '2020-01-05', 1,
34, 25)
+ """
+ result = sql """ SELECT * FROM ${tableName} t ORDER BY user_id; """
+ assertTrue(result.size() == 7)
+ assertTrue(result[6][10] == 25)
+}
diff --git
a/regression-test/suites/inverted_index_p0/unique_with_mow/test_unique_mow_sequence.groovy
b/regression-test/suites/inverted_index_p0/unique_with_mow/test_unique_mow_sequence.groovy
new file mode 100644
index 0000000000..9c461f7371
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/unique_with_mow/test_unique_mow_sequence.groovy
@@ -0,0 +1,91 @@
+// 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_unique_mow_sequence", "inverted_index") {
+ def tableName = "test_mow_sequence"
+ sql """ DROP TABLE IF EXISTS $tableName """
+ sql """
+ CREATE TABLE `$tableName` (
+ `c_custkey` int(11) NOT NULL COMMENT "",
+ `c_name` varchar(26) NOT NULL COMMENT "",
+ `c_address` varchar(41) NOT NULL COMMENT "",
+ `c_city` varchar(11) NOT NULL COMMENT "",
+ `c_nation` varchar(16) NOT NULL COMMENT "",
+ `c_region` varchar(13) NOT NULL COMMENT "",
+ `c_phone` varchar(16) NOT NULL COMMENT "",
+ `c_mktsegment` varchar(11) NOT NULL COMMENT "",
+ INDEX c_custkey_idx(c_custkey) USING INVERTED COMMENT
'c_custkey index',
+ INDEX c_name_idx(c_name) USING INVERTED COMMENT 'c_name
index',
+ INDEX c_address_idx(c_address) USING INVERTED COMMENT
'c_address index',
+ INDEX c_city_idx(c_city) USING INVERTED COMMENT 'c_city
index',
+ INDEX c_nation_idx(c_nation) USING INVERTED COMMENT
'c_nation index',
+ INDEX c_region_idx(c_region) USING INVERTED COMMENT
'c_region index',
+ INDEX c_phone_idx(c_phone) USING INVERTED COMMENT 'c_phone
index',
+ INDEX c_mktsegment_idx(c_mktsegment) USING INVERTED
COMMENT 'c_mktsegment index'
+ )
+ UNIQUE KEY (`c_custkey`)
+ DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 10
+ PROPERTIES (
+ "function_column.sequence_type" = 'int',
+ "compression"="zstd",
+ "replication_num" = "1",
+ "enable_unique_key_merge_on_write" = "true"
+ );
+ """
+
+ streamLoad {
+ table "${tableName}"
+
+ set 'column_separator', '|'
+ set 'compress_type', 'GZ'
+ set 'columns',
'c_custkey,c_name,c_address,c_city,c_nation,c_region,c_phone,c_mktsegment,no_use'
+ set 'function_column.sequence_col', 'c_custkey'
+
+ file """${getS3Url()}/regression/ssb/sf0.1/customer.tbl.gz"""
+
+ time 10000 // limit inflight 10s
+
+ // stream load action will check result, include Success status,
and NumberTotalRows == NumberLoadedRows
+
+ // if declared a check callback, the default check condition will
ignore.
+ // So you must check all condition
+ check { result, exception, startTime, endTime ->
+ if (exception != null) {
+ throw exception
+ }
+ log.info("Stream load result: ${result}".toString())
+ def json = parseJson(result)
+ assertEquals("success", json.Status.toLowerCase())
+ assertEquals(json.NumberTotalRows, json.NumberLoadedRows)
+ assertTrue(json.NumberLoadedRows > 0 && json.LoadBytes > 0)
+ }
+ }
+
+ sql "sync"
+
+ order_qt_sql "select * from $tableName where c_custkey < 6;"
+
+ order_qt_sql "select * from $tableName where c_custkey > 2995;"
+
+ qt_sql "select * from $tableName where c_custkey = 1;"
+
+ qt_sql "select * from $tableName where c_custkey = 3000;"
+
+ qt_sql "select * from $tableName where c_custkey = 3001;"
+
+ qt_sql "select * from $tableName where c_custkey = 0;"
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
