This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch branch-1.2-lts
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-1.2-lts by this push:
new 738149acbe [enchancement](mv) forbidden craete useless mv in fe
(#16286)
738149acbe is described below
commit 738149acbe5ad968f1f516880b0341cfae57aef5
Author: Zhengguo Yang <yangz...@gmail.com>
AuthorDate: Thu Feb 9 23:00:09 2023 +0800
[enchancement](mv) forbidden craete useless mv in fe (#16286)
forbidden create useless mv in fe
---
.../doris/alter/MaterializedViewHandler.java | 86 ++++++++++++++--
.../doris/analysis/CreateMaterializedViewStmt.java | 4 +
.../java/org/apache/doris/catalog/OlapTable.java | 4 +
.../org/apache/doris/alter/AlterJobV2Test.java | 2 +-
.../java/org/apache/doris/alter/AlterTest.java | 8 +-
.../org/apache/doris/alter/BatchRollupJobTest.java | 4 +-
.../org/apache/doris/catalog/EnvOperationTest.java | 4 +-
.../apache/doris/catalog/TempPartitionTest.java | 6 +-
.../doris/nereids/rules/mv/SelectMvIndexTest.java | 8 +-
.../nereids/rules/mv/SelectRollupIndexTest.java | 4 +-
.../planner/MaterializedViewFunctionTest.java | 8 +-
.../test_mv_useless/test_agg_mv_useless.groovy | 105 ++++++++++++++++++++
.../test_mv_useless/test_dup_mv_useless.groovy | 110 +++++++++++++++++++++
.../test_mv_useless/test_uniq_mv_useless.groovy | 72 ++++++++++++++
.../link_schema_change/test_rollup_agg_fail.groovy | 38 ++-----
.../link_schema_change/test_rollup_dup_fail.groovy | 16 +--
.../link_schema_change/test_rollup_uni_fail.groovy | 15 +--
.../rollup_p0/test_materialized_view_array.groovy | 2 +-
.../test_dup_mv_schema_change.groovy | 2 +-
19 files changed, 415 insertions(+), 83 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/alter/MaterializedViewHandler.java
b/fe/fe-core/src/main/java/org/apache/doris/alter/MaterializedViewHandler.java
index 5ea1b2a087..355b34f1e8 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/alter/MaterializedViewHandler.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/alter/MaterializedViewHandler.java
@@ -488,9 +488,25 @@ public class MaterializedViewHandler extends AlterHandler {
}
newMVColumns.add(mvColumnItem.toMVColumn(olapTable));
}
+ // check useless rollup of same key columns and same order with
base table
+ if (numOfKeys == olapTable.getBaseSchemaKeyColumns().size() &&
!addMVClause.isReplay()) {
+ boolean allKeysMatch = true;
+ for (int i = 0; i < numOfKeys; i++) {
+ if (!newMVColumns.get(i).getName()
+
.equalsIgnoreCase(olapTable.getBaseSchemaKeyColumns().get(i).getName())) {
+ allKeysMatch = false;
+ break;
+ }
+ }
+ if (allKeysMatch) {
+ throw new DdlException("MV contains all keys in base table
with same order for "
+ + "aggregation or unique table is useless.");
+ }
+ }
} else {
Set<String> partitionOrDistributedColumnName =
olapTable.getPartitionColumnNames();
partitionOrDistributedColumnName.addAll(olapTable.getDistributionColumnNames());
+ boolean hasNewColumn = false;
for (MVColumnItem mvColumnItem : mvColumnItemList) {
if
(partitionOrDistributedColumnName.contains(mvColumnItem.getBaseColumnName().toLowerCase())
&& mvColumnItem.getAggregationType() != null) {
@@ -498,6 +514,28 @@ public class MaterializedViewHandler extends AlterHandler {
+ " must be key column in mv");
}
newMVColumns.add(mvColumnItem.toMVColumn(olapTable));
+ if (mvColumnItem.isKey()) {
+ ++numOfKeys;
+ }
+ if (olapTable.getBaseColumn(mvColumnItem.getName()) == null) {
+ hasNewColumn = true;
+ }
+ }
+ // check useless rollup of same key columns and same order with
base table
+ if (!addMVClause.isReplay() && addMVClause.getMVKeysType() ==
KeysType.DUP_KEYS && !hasNewColumn) {
+ boolean allKeysMatch = true;
+ for (int i = 0; i < numOfKeys; i++) {
+ if (!newMVColumns.get(i).getName()
+
.equalsIgnoreCase(olapTable.getBaseSchema().get(i).getName())
+ && olapTable.getBaseSchema().get(i).isKey()) {
+ allKeysMatch = false;
+ break;
+ }
+ }
+ if (allKeysMatch) {
+ throw new DdlException("MV contain the columns of the base
table in prefix order for "
+ + "duplicate table is useless.");
+ }
}
}
if (KeysType.UNIQUE_KEYS == olapTable.getKeysType() &&
olapTable.hasDeleteSign()) {
@@ -529,7 +567,7 @@ public class MaterializedViewHandler extends AlterHandler {
}
public List<Column> checkAndPrepareMaterializedView(AddRollupClause
addRollupClause, OlapTable olapTable,
- long baseIndexId,
boolean changeStorageFormat)
+ long baseIndexId, boolean changeStorageFormat)
throws DdlException {
String rollupIndexName = addRollupClause.getRollupName();
List<String> rollupColumnNames = addRollupClause.getColumnNames();
@@ -612,12 +650,33 @@ public class MaterializedViewHandler extends AlterHandler
{
rollupSchema.add(new Column(olapTable.getSequenceCol()));
}
}
+
+ // check useless rollup of same key columns and same order with
base table
+ if (keysNumOfRollup == olapTable.getKeysNum()) {
+ boolean allKeysMatch = true;
+ for (int i = 0; i < keysNumOfRollup; i++) {
+ if (!rollupSchema.get(i).getName()
+
.equalsIgnoreCase(olapTable.getSchemaByIndexId(baseIndexId,
true).get(i).getName())) {
+ allKeysMatch = false;
+ break;
+ }
+ }
+ if (allKeysMatch) {
+ throw new DdlException("Rollup contains all keys in base
table with same order for "
+ + "aggregation or unique table is useless.");
+ }
+ }
} else if (KeysType.DUP_KEYS == keysType) {
// supplement the duplicate key
if (addRollupClause.getDupKeys() == null ||
addRollupClause.getDupKeys().isEmpty()) {
// check the column meta
+ boolean allColumnsMatch = true;
for (int i = 0; i < rollupColumnNames.size(); i++) {
String columnName = rollupColumnNames.get(i);
+ if
(!columnName.equalsIgnoreCase(olapTable.getSchemaByIndexId(baseIndexId,
true).get(i).getName())
+ && olapTable.getSchemaByIndexId(baseIndexId,
true).get(i).isKey()) {
+ allColumnsMatch = false;
+ }
Column baseColumn = baseColumnNameToColumn.get(columnName);
if (baseColumn == null) {
throw new DdlException("Column[" + columnName + "]
does not exist in base index");
@@ -661,26 +720,33 @@ public class MaterializedViewHandler extends AlterHandler
{
rollupColumn.setIsKey(false);
rollupColumn.setAggregationType(AggregateType.NONE, true);
}
+ if (allColumnsMatch) {
+ throw new DdlException("Rollup contain the columns of the
base table in prefix order for "
+ + "duplicate table is useless.");
+ }
} else {
/*
* eg.
* Base Table's schema is (k1,k2,k3,k4,k5) dup key (k1,k2,k3).
* The following rollup is allowed:
* 1. (k1) dup key (k1)
- * 2. (k2,k3) dup key (k2)
- * 3. (k1,k2,k3) dup key (k1,k2)
*
* The following rollup is forbidden:
* 1. (k1) dup key (k2)
* 2. (k2,k3) dup key (k3,k2)
* 3. (k1,k2,k3) dup key (k2,k3)
+ *
+ * The following rollup is useless so forbidden too:
+ * 1. (k1,k2,k3) dup key (k1,k2,k3)
+ * 3. (k1,k2,k3) dup key (k1,k2)
+ * 1. (k1) dup key (k1)
*/
// user specify the duplicate keys for rollup index
List<String> dupKeys = addRollupClause.getDupKeys();
if (dupKeys.size() > rollupColumnNames.size()) {
throw new DdlException("Num of duplicate keys should less
than or equal to num of rollup columns.");
}
-
+ boolean allColumnsMatch = true;
for (int i = 0; i < rollupColumnNames.size(); i++) {
String rollupColName = rollupColumnNames.get(i);
boolean isKey = false;
@@ -691,7 +757,11 @@ public class MaterializedViewHandler extends AlterHandler {
}
isKey = true;
}
-
+ if (!rollupColName.equalsIgnoreCase(
+ olapTable.getSchemaByIndexId(baseIndexId,
true).get(i).getName())
+ && olapTable.getSchemaByIndexId(baseIndexId,
true).get(i).isKey()) {
+ allColumnsMatch = false;
+ }
Column baseColumn =
baseColumnNameToColumn.get(rollupColName);
if (baseColumn == null) {
throw new DdlException("Column[" + rollupColName + "]
does not exist");
@@ -713,6 +783,10 @@ public class MaterializedViewHandler extends AlterHandler {
}
rollupSchema.add(oneColumn);
}
+ if (allColumnsMatch) {
+ throw new DdlException("Rollup contain the columns of the
base table in prefix order for "
+ + "duplicate table is useless.");
+ }
}
}
if (olapTable.getEnableLightSchemaChange()) {
@@ -793,7 +867,7 @@ public class MaterializedViewHandler extends AlterHandler {
}
public void processDropMaterializedView(DropMaterializedViewStmt
dropMaterializedViewStmt, Database db,
- OlapTable olapTable) throws
DdlException, MetaNotFoundException {
+ OlapTable olapTable) throws DdlException, MetaNotFoundException {
olapTable.writeLockOrDdlException();
try {
// check table state
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java
index 75e62afbad..c9ff307dcd 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java
@@ -105,6 +105,10 @@ public class CreateMaterializedViewStmt extends DdlStmt {
this.isReplay = isReplay;
}
+ public boolean isReplay() {
+ return isReplay;
+ }
+
public String getMVName() {
return mvName;
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java
index f5088db5ed..b7aee1d162 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java
@@ -597,6 +597,10 @@ public class OlapTable extends Table {
}
}
+ public List<Column> getBaseSchemaKeyColumns() {
+ return getKeyColumnsByIndexId(baseIndexId);
+ }
+
public List<Column> getKeyColumnsByIndexId(Long indexId) {
ArrayList<Column> keyColumns = Lists.newArrayList();
List<Column> allColumns = this.getSchemaByIndexId(indexId);
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/alter/AlterJobV2Test.java
b/fe/fe-core/src/test/java/org/apache/doris/alter/AlterJobV2Test.java
index db2cd16caa..3a144d1d08 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/alter/AlterJobV2Test.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/alter/AlterJobV2Test.java
@@ -125,7 +125,7 @@ public class AlterJobV2Test {
@Test
public void testRollup() throws Exception {
// 1. process a rollup job
- String alterStmtStr = "alter table test.schema_change_test add rollup
test_rollup(k1, k2);";
+ String alterStmtStr = "alter table test.schema_change_test add rollup
test_rollup(k2, k1);";
AlterTableStmt alterTableStmt = (AlterTableStmt)
UtFrameUtils.parseAndAnalyzeStmt(alterStmtStr, connectContext);
Env.getCurrentEnv().getAlterInstance().processAlterTable(alterTableStmt);
// 2. check alter job
diff --git a/fe/fe-core/src/test/java/org/apache/doris/alter/AlterTest.java
b/fe/fe-core/src/test/java/org/apache/doris/alter/AlterTest.java
index b5d435203e..553dfc6b6a 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/alter/AlterTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/alter/AlterTest.java
@@ -415,11 +415,11 @@ public class AlterTest {
alterTable(stmt, false);
waitSchemaChangeJobDone(false);
- stmt = "alter table test.tbl6 add rollup r1 (k1, k2)";
+ stmt = "alter table test.tbl6 add rollup r1 (k2, k1)";
alterTable(stmt, false);
waitSchemaChangeJobDone(true);
- stmt = "alter table test.tbl6 add rollup r2 (k1, k2), r3 (k1, k2)";
+ stmt = "alter table test.tbl6 add rollup r2 (k2, k1), r3 (k2, k1)";
alterTable(stmt, false);
waitSchemaChangeJobDone(true);
@@ -1125,7 +1125,7 @@ public class AlterTest {
@Test
public void testShowMV() throws Exception {
- createMV("CREATE MATERIALIZED VIEW test_mv as select k1 from
test.show_test;", false);
+ createMV("CREATE MATERIALIZED VIEW test_mv as select k1 from
test.show_test group by k1;", false);
waitSchemaChangeJobDone(true);
String showMvSql = "SHOW CREATE MATERIALIZED VIEW test_mv on
test.show_test;";
@@ -1133,7 +1133,7 @@ public class AlterTest {
showMvSql, connectContext);
ShowExecutor executor = new ShowExecutor(connectContext, showStmt);
Assert.assertEquals(executor.execute().getResultRows().get(0).get(2),
- "CREATE MATERIALIZED VIEW test_mv as select k1 from
test.show_test;");
+ "CREATE MATERIALIZED VIEW test_mv as select k1 from
test.show_test group by k1;");
showMvSql = "SHOW CREATE MATERIALIZED VIEW test_mv_empty on
test.show_test;";
showStmt = (ShowCreateMaterializedViewStmt)
UtFrameUtils.parseAndAnalyzeStmt(showMvSql, connectContext);
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/alter/BatchRollupJobTest.java
b/fe/fe-core/src/test/java/org/apache/doris/alter/BatchRollupJobTest.java
index c9836dd07f..987d90215b 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/alter/BatchRollupJobTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/alter/BatchRollupJobTest.java
@@ -72,7 +72,7 @@ public class BatchRollupJobTest {
Env.getCurrentEnv().createTable(createTableStmt);
// batch add 3 rollups
- String stmtStr = "alter table db1.tbl1 add rollup r1(k1) duplicate
key(k1), r2(k1, k2) duplicate key(k1), r3(k2) duplicate key(k2);";
+ String stmtStr = "alter table db1.tbl1 add rollup r1(k3) duplicate
key(k3), r2(k2, k1) duplicate key(k2), r3(k2) duplicate key(k2);";
AlterTableStmt alterTableStmt = (AlterTableStmt)
UtFrameUtils.parseAndAnalyzeStmt(stmtStr, ctx);
Env.getCurrentEnv().getAlterInstance().processAlterTable(alterTableStmt);
@@ -122,7 +122,7 @@ public class BatchRollupJobTest {
Env.getCurrentEnv().createTable(createTableStmt);
// batch add 3 rollups
- String stmtStr = "alter table db1.tbl2 add rollup r1(k1) duplicate
key(k1), r2(k1, k2) duplicate key(k1), r3(k2) duplicate key(k2);";
+ String stmtStr = "alter table db1.tbl2 add rollup r1(k3) duplicate
key(k3), r2(k2, k1) duplicate key(k2), r3(k2) duplicate key(k2);";
AlterTableStmt alterTableStmt = (AlterTableStmt)
UtFrameUtils.parseAndAnalyzeStmt(stmtStr, ctx);
Env.getCurrentEnv().getAlterInstance().processAlterTable(alterTableStmt);
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/catalog/EnvOperationTest.java
b/fe/fe-core/src/test/java/org/apache/doris/catalog/EnvOperationTest.java
index 32a88b291e..a4a8b9e59f 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/catalog/EnvOperationTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/catalog/EnvOperationTest.java
@@ -57,7 +57,7 @@ public class EnvOperationTest {
Env.getCurrentEnv().createDb(createDbStmt);
createTable("create table test.renameTest\n"
- + "(k1 int)\n"
+ + "(k1 int,k2 int)\n"
+ "distributed by hash(k1) buckets 1\n"
+ "properties(\"replication_num\" = \"1\");");
@@ -119,7 +119,7 @@ public class EnvOperationTest {
Assert.assertNotNull(db.getTableNullable("newNewTest"));
// add a rollup and test rename to a rollup name(expect throw
exception)
- String alterStmtStr = "alter table test.newNewTest add rollup r1(k1)";
+ String alterStmtStr = "alter table test.newNewTest add rollup
r1(k2,k1)";
alterTableStmt = (AlterTableStmt)
UtFrameUtils.parseAndAnalyzeStmt(alterStmtStr, connectContext);
Env.getCurrentEnv().getAlterInstance().processAlterTable(alterTableStmt);
Map<Long, AlterJobV2> alterJobs =
Env.getCurrentEnv().getMaterializedViewHandler().getAlterJobsV2();
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/catalog/TempPartitionTest.java
b/fe/fe-core/src/test/java/org/apache/doris/catalog/TempPartitionTest.java
index b1a0d45dad..96f8e77830 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/catalog/TempPartitionTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/catalog/TempPartitionTest.java
@@ -433,7 +433,7 @@ public class TempPartitionTest {
checkShowPartitionsResultNum("db2.tbl2", false, 3);
checkShowPartitionsResultNum("db2.tbl2", true, 0);
- stmtStr = "alter table db2.tbl2 add rollup r1(k1);";
+ stmtStr = "alter table db2.tbl2 add rollup r1(k2, k1);";
alterTable(stmtStr, false);
stmtStr = "alter table db2.tbl2 add temporary partition p2 values less
than('20');";
@@ -806,7 +806,7 @@ public class TempPartitionTest {
checkShowPartitionsResultNum("db4.tbl4", false, 3);
checkShowPartitionsResultNum("db4.tbl4", true, 0);
- stmtStr = "alter table db4.tbl4 add rollup r1(k1);";
+ stmtStr = "alter table db4.tbl4 add rollup r1(k2,k1);";
alterTable(stmtStr, false);
stmtStr = "alter table db4.tbl4 add temporary partition p2 values in
('1', '2', '3', '4', '5', '6');";
@@ -1170,7 +1170,7 @@ public class TempPartitionTest {
checkShowPartitionsResultNum("db5.tbl5", false, 3);
checkShowPartitionsResultNum("db5.tbl5", true, 0);
- stmtStr = "alter table db5.tbl5 add rollup r1(k1);";
+ stmtStr = "alter table db5.tbl5 add rollup r1(k2, k1);";
alterTable(stmtStr, false);
stmtStr = "alter table db5.tbl5 add temporary partition p2 values in"
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectMvIndexTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectMvIndexTest.java
index 6ec7ab3764..a1bf9dc70e 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectMvIndexTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectMvIndexTest.java
@@ -685,7 +685,7 @@ public class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implement
// don't use rollup k1_v1
addRollup("alter table agg_table add rollup k1_v1(k1, v1)");
// use rollup only_keys
- addRollup("alter table agg_table add rollup only_keys (k1, k2)
properties ('replication_num' = '1')");
+ addRollup("alter table agg_table add rollup only_keys (k2, k1)
properties ('replication_num' = '1')");
String query = "select k1, k2 from agg_table;";
// todo: `preagg` should be ture when rollup could be used.
@@ -757,11 +757,11 @@ public class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implement
@Test
public void testUniqueTableInQuery() throws Exception {
- String uniqueTable = "CREATE TABLE " + TEST_TABLE_NAME + " (k1 int, v1
int) UNIQUE KEY (k1) "
+ String uniqueTable = "CREATE TABLE " + TEST_TABLE_NAME + " (k1 int, k2
int, v1 int) UNIQUE KEY (k1, k2) "
+ "DISTRIBUTED BY HASH(k1) BUCKETS 3 PROPERTIES
('replication_num' = '1');";
createTable(uniqueTable);
- String createK1MV = "create materialized view only_k1 as select k1
from " + TEST_TABLE_NAME + " group by "
- + "k1;";
+ String createK1MV = "create materialized view only_k1 as select k2
from " + TEST_TABLE_NAME + " group by "
+ + "k2;";
createMv(createK1MV);
String query = "select * from " + TEST_TABLE_NAME + ";";
singleTableTest(query, TEST_TABLE_NAME, false);
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectRollupIndexTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectRollupIndexTest.java
index 6eb5f30160..60de7e8222 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectRollupIndexTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectRollupIndexTest.java
@@ -78,7 +78,7 @@ class SelectRollupIndexTest extends
BaseMaterializedIndexSelectTest implements P
+ ");");
addRollup("alter table t1 add rollup r1(k1)");
addRollup("alter table t1 add rollup r2(k2, v1)");
- addRollup("alter table t1 add rollup r3(k1, k2)");
+ addRollup("alter table t1 add rollup r3(k2, k1)");
createTable("CREATE TABLE `duplicate_tbl` (\n"
+ " `k1` int(11) NULL,\n"
@@ -342,7 +342,7 @@ class SelectRollupIndexTest extends
BaseMaterializedIndexSelectTest implements P
+ "\"storage_format\" = \"V2\",\n"
+ "\"disable_auto_compaction\" = \"false\"\n"
+ ");");
- addRollup("alter table t4 add rollup r1(k1, k2, v1)");
+ addRollup("alter table t4 add rollup r1(k2, k1, v1)");
singleTableTest("select k1, k2, v1 from t4", "r1", false);
singleTableTest("select k1, k2, sum(v1) from t4 group by k1, k2",
"r1", true);
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/planner/MaterializedViewFunctionTest.java
b/fe/fe-core/src/test/java/org/apache/doris/planner/MaterializedViewFunctionTest.java
index cf418a6830..fc7fc32823 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/planner/MaterializedViewFunctionTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/planner/MaterializedViewFunctionTest.java
@@ -609,7 +609,7 @@ public class MaterializedViewFunctionTest {
String aggregateTable = "create table agg_table (k1 int, k2 int, v1
bigint sum) aggregate key (k1, k2) "
+ "distributed by hash(k1) buckets 3
properties('replication_num' = '1');";
dorisAssert.withTable(aggregateTable);
- String createRollupSQL = "alter table agg_table add rollup old_key
(k1, k2) "
+ String createRollupSQL = "alter table agg_table add rollup old_key
(k2, k1) "
+ "properties ('replication_num' = '1');";
String query = "select k1, k2 from agg_table;";
dorisAssert.withRollup(createRollupSQL).query(query).explainContains("OFF",
"old_key");
@@ -659,11 +659,11 @@ public class MaterializedViewFunctionTest {
@Test
public void testUniqueTableInQuery() throws Exception {
- String uniqueTable = "CREATE TABLE " + TEST_TABLE_NAME + " (k1 int, v1
int) UNIQUE KEY (k1) "
+ String uniqueTable = "CREATE TABLE " + TEST_TABLE_NAME + " (k1 int, k2
int, v1 int) UNIQUE KEY (k1, k2) "
+ "DISTRIBUTED BY HASH(k1) BUCKETS 3 PROPERTIES
('replication_num' = '1');";
dorisAssert.withTable(uniqueTable);
- String createK1K2MV = "create materialized view only_k1 as select k1
from " + TEST_TABLE_NAME + " group by "
- + "k1;";
+ String createK1K2MV = "create materialized view only_k1 as select k2,
k1 from " + TEST_TABLE_NAME + " group by "
+ + "k2, k1;";
String query = "select * from " + TEST_TABLE_NAME + ";";
dorisAssert.withMaterializedView(createK1K2MV).query(query).explainContains(TEST_TABLE_NAME);
dorisAssert.dropTable(TEST_TABLE_NAME, true);
diff --git
a/regression-test/suites/materialized_view_p0/test_mv_useless/test_agg_mv_useless.groovy
b/regression-test/suites/materialized_view_p0/test_mv_useless/test_agg_mv_useless.groovy
new file mode 100644
index 0000000000..16bb73b2b8
--- /dev/null
+++
b/regression-test/suites/materialized_view_p0/test_mv_useless/test_agg_mv_useless.groovy
@@ -0,0 +1,105 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite ("test_agg_mv_useless") {
+ def testTable = "test_agg_mv_useless_table"
+ def getJobState = { tableName ->
+ def jobStateResult = sql """ SHOW ALTER TABLE MATERIALIZED VIEW WHERE
TableName='${testTable}' ORDER BY CreateTime DESC LIMIT 1; """
+ return jobStateResult[0][8]
+ }
+ sql """ DROP TABLE IF EXISTS ${testTable}; """
+
+ sql """
+ create table ${testTable} (
+ k1 int null,
+ k2 int null,
+ k3 int sum
+ )
+ aggregate key (k1,k2)
+ distributed BY hash(k1) buckets 3
+ properties("replication_num" = "1");
+ """
+
+ sql "insert into ${testTable} select 1,1,1;"
+ sql "insert into ${testTable} select 2,2,2;"
+ sql "insert into ${testTable} select 3,3,3;"
+
+ test {
+ sql "create materialized view k1 as select k1 from ${testTable};"
+ exception "errCode = 2,"
+ }
+
+ test {
+ sql "create materialized view k1_k2 as select k1,k2 from ${testTable};"
+ exception "errCode = 2,"
+ }
+ test {
+ sql "create materialized view k1_k2_sumk3 as select k1,k2,sum(k3)
from${testTable} group by k1,k2;"
+ exception "errCode = 2,"
+ }
+
+ test {
+ sql "create materialized view k1_k2_u12 as select k1,k2 from
${testTable} group by k1,k2;"
+ exception "errCode = 2,"
+ }
+
+ sql "create materialized view k1_u1 as select k1 from ${testTable} group
by k1;"
+ max_try_secs = 60
+ while (max_try_secs--) {
+ String res = getJobState(testTable)
+ if (res == "FINISHED") {
+ break
+ } else {
+ Thread.sleep(2000)
+ if (max_try_secs < 1) {
+ println "test timeout," + "state:" + res
+ assertEquals("FINISHED",res)
+ }
+ }
+ }
+
+ sql "create materialized view k1_k2_u21 as select k2,k1 from ${testTable}
group by k2,k1 order by k2,k1;"
+ max_try_secs = 60
+ while (max_try_secs--) {
+ String res = getJobState(testTable)
+ if (res == "FINISHED") {
+ break
+ } else {
+ Thread.sleep(2000)
+ if (max_try_secs < 1) {
+ println "test timeout," + "state:" + res
+ assertEquals("FINISHED",res)
+ }
+ }
+ }
+
+ sql "create materialized view k1_sumk3 as select k1,sum(k3) from
${testTable} group by k1;"
+ max_try_secs = 60
+ while (max_try_secs--) {
+ String res = getJobState(testTable)
+ if (res == "FINISHED") {
+ break
+ } else {
+ Thread.sleep(2000)
+ if (max_try_secs < 1) {
+ println "test timeout," + "state:" + res
+ assertEquals("FINISHED",res)
+ }
+ }
+ }
+ sql "insert into ${testTable} select 4,4,4;"
+}
diff --git
a/regression-test/suites/materialized_view_p0/test_mv_useless/test_dup_mv_useless.groovy
b/regression-test/suites/materialized_view_p0/test_mv_useless/test_dup_mv_useless.groovy
new file mode 100644
index 0000000000..a99d292e19
--- /dev/null
+++
b/regression-test/suites/materialized_view_p0/test_mv_useless/test_dup_mv_useless.groovy
@@ -0,0 +1,110 @@
+// 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_dup_mv_useless") {
+ def testTable = "test_dup_mv_useless_table"
+ def getJobState = { tableName ->
+ def jobStateResult = sql """ SHOW ALTER TABLE MATERIALIZED VIEW WHERE
TableName='${testTable}' ORDER BY CreateTime DESC LIMIT 1; """
+ return jobStateResult[0][8]
+ }
+ sql """ DROP TABLE IF EXISTS ${testTable}; """
+
+ sql """
+ create table ${testTable} (
+ k1 int null,
+ k2 int null,
+ k3 int null
+ )
+ duplicate key (k1,k2)
+ distributed BY hash(k1) buckets 3
+ properties("replication_num" = "1");
+ """
+
+ sql "insert into ${testTable} select 1,1,1;"
+ sql "insert into ${testTable} select 2,2,2;"
+ sql "insert into ${testTable} select 3,3,3;"
+
+ def result = "null"
+ test {
+ sql "create materialized view k1 as select k1 from ${testTable};"
+ exception "errCode = 2,"
+ }
+
+ test {
+ sql "create materialized view k1_k2 as select k1,k2 from ${testTable};"
+ exception "errCode = 2,"
+ }
+
+ sql "create materialized view k1_u1 as select k1 from ${testTable} group
by k1;"
+ max_try_secs = 60
+ while (max_try_secs--) {
+ String res = getJobState(testTable)
+ if (res == "FINISHED") {
+ break
+ } else {
+ Thread.sleep(2000)
+ if (max_try_secs < 1) {
+ println "test timeout," + "state:" + res
+ assertEquals("FINISHED",res)
+ }
+ }
+ }
+ sql "create materialized view k1_k2_u12 as select k1,k2 from ${testTable}
group by k1,k2;"
+ max_try_secs = 60
+ while (max_try_secs--) {
+ String res = getJobState(testTable)
+ if (res == "FINISHED") {
+ break
+ } else {
+ Thread.sleep(2000)
+ if (max_try_secs < 1) {
+ println "test timeout," + "state:" + res
+ assertEquals("FINISHED",res)
+ }
+ }
+ }
+ sql "create materialized view k1_k2_u21 as select k2,k1 from ${testTable}
group by k2,k1 order by k2,k1;"
+ max_try_secs = 60
+ while (max_try_secs--) {
+ String res = getJobState(testTable)
+ if (res == "FINISHED") {
+ break
+ } else {
+ Thread.sleep(2000)
+ if (max_try_secs < 1) {
+ println "test timeout," + "state:" + res
+ assertEquals("FINISHED",res)
+ }
+ }
+ }
+ sql " create materialized view k1_k2_sumk3 as select k1,k2,sum(k3) from
${testTable} group by k1,k2;"
+ max_try_secs = 60
+ while (max_try_secs--) {
+ String res = getJobState(testTable)
+ if (res == "FINISHED") {
+ break
+ } else {
+ Thread.sleep(2000)
+ if (max_try_secs < 1) {
+ println "test timeout," + "state:" + res
+ assertEquals("FINISHED",res)
+ }
+ }
+ }
+
+ sql "insert into ${testTable} select 4,4,4;"
+}
diff --git
a/regression-test/suites/materialized_view_p0/test_mv_useless/test_uniq_mv_useless.groovy
b/regression-test/suites/materialized_view_p0/test_mv_useless/test_uniq_mv_useless.groovy
new file mode 100644
index 0000000000..1d79d442db
--- /dev/null
+++
b/regression-test/suites/materialized_view_p0/test_mv_useless/test_uniq_mv_useless.groovy
@@ -0,0 +1,72 @@
+// 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_uniq_mv_useless") {
+ def testTable = "test_uniq_mv_useless_table"
+ def getJobState = { tableName ->
+ def jobStateResult = sql """ SHOW ALTER TABLE MATERIALIZED VIEW WHERE
TableName='${testTable}' ORDER BY CreateTime DESC LIMIT 1; """
+ return jobStateResult[0][8]
+ }
+ sql """ DROP TABLE IF EXISTS ${testTable}; """
+
+ sql """
+ create table ${testTable} (
+ k1 int null,
+ k2 int null,
+ k3 int
+ )
+ unique key (k1,k2)
+ distributed BY hash(k1) buckets 3
+ properties("replication_num" = "1");
+ """
+
+ sql "insert into ${testTable} select 1,1,1;"
+ sql "insert into ${testTable} select 2,2,2;"
+ sql "insert into ${testTable} select 3,3,3;"
+
+ test {
+ sql "create materialized view k1 as select k1 from ${testTable};"
+ exception "errCode = 2,"
+ }
+
+ test {
+ sql "create materialized view k1_k2 as select k1,k2 from ${testTable};"
+ exception "errCode = 2,"
+ }
+
+ test {
+ sql "create materialized view k1_k2_u12 as select k1,k2 from
${testTable} group by k1,k2;"
+ exception "errCode = 2,"
+ }
+
+ sql "create materialized view k1_k2_u21 as select k2,k1 from ${testTable}
group by k2,k1 order by k2,k1;"
+ max_try_secs = 60
+ while (max_try_secs--) {
+ String res = getJobState(testTable)
+ if (res == "FINISHED") {
+ break
+ } else {
+ Thread.sleep(2000)
+ if (max_try_secs < 1) {
+ println "test timeout," + "state:" + res
+ assertEquals("FINISHED",res)
+ }
+ }
+ }
+
+ sql "insert into ${testTable} select 4,4,4;"
+}
diff --git
a/regression-test/suites/rollup_p0/link_schema_change/test_rollup_agg_fail.groovy
b/regression-test/suites/rollup_p0/link_schema_change/test_rollup_agg_fail.groovy
index 598b0dfed8..cfef1eebd1 100644
---
a/regression-test/suites/rollup_p0/link_schema_change/test_rollup_agg_fail.groovy
+++
b/regression-test/suites/rollup_p0/link_schema_change/test_rollup_agg_fail.groovy
@@ -15,8 +15,8 @@
// specific language governing permissions and limitations
// under the License.
-suite ("test_rollup_add_fail") {
- def tableName = "test_rollup_add_fail"
+suite ("test_rollup_agg_fail") {
+ def tableName = "test_rollup_agg_fail"
/* agg */
sql """ DROP TABLE IF EXISTS ${tableName} FORCE"""
@@ -37,34 +37,14 @@ suite ("test_rollup_add_fail") {
BUCKETS 1
PROPERTIES ( "replication_num" = "1", "light_schema_change" =
"true" );
"""
-
- // add materialized view (failed)
- def result = "null"
- def mvName = "mv1"
- sql "create materialized view ${mvName} as select user_id, date, city,
age, sex, sum(cost) from ${tableName} group by user_id, date, city, age, sex;"
- while (!result.contains("CANCELLED")){
- result = sql "SHOW ALTER TABLE MATERIALIZED VIEW WHERE
TableName='${tableName}' ORDER BY CreateTime DESC LIMIT 1;"
- result = result.toString()
- logger.info("result: ${result}")
- if(result.contains("FINISHED")){
- assertTrue(false);
- }
- Thread.sleep(100)
+ //add rollup (failed)
+ test {
+ sql "create materialized view mv1 as select user_id, date, city, age,
sex, sum(cost) from ${tableName} group by user_id, date, city, age, sex;"
+ exception "errCode = 2"
}
-
- Thread.sleep(1000)
-
//add rollup (failed)
- result = "null"
- def rollupName = "rollup_cost"
- sql "ALTER TABLE ${tableName} ADD ROLLUP
${rollupName}(`user_id`,`date`,`city`,`age`, `sex`, cost);"
- while (!result.contains("CANCELLED")){
- result = sql "SHOW ALTER TABLE ROLLUP WHERE TableName='${tableName}'
ORDER BY CreateTime DESC LIMIT 1;"
- result = result.toString()
- logger.info("result: ${result}")
- if(result.contains("FINISHED")){
- assertTrue(false);
- }
- Thread.sleep(100)
+ test {
+ sql "ALTER TABLE ${tableName} ADD ROLLUP r1
(`user_id`,`date`,`city`,`age`, `sex`, cost);"
+ exception "errCode = 2"
}
}
diff --git
a/regression-test/suites/rollup_p0/link_schema_change/test_rollup_dup_fail.groovy
b/regression-test/suites/rollup_p0/link_schema_change/test_rollup_dup_fail.groovy
index a3a1c726b8..204a2bad3d 100644
---
a/regression-test/suites/rollup_p0/link_schema_change/test_rollup_dup_fail.groovy
+++
b/regression-test/suites/rollup_p0/link_schema_change/test_rollup_dup_fail.groovy
@@ -36,18 +36,10 @@ suite ("test_rollup_dup_fail") {
BUCKETS 1
PROPERTIES ( "replication_num" = "1", "light_schema_change" = "true" );
"""
-
//add rollup (failed)
- result = "null"
- rollupName = "rollup_cost"
- sql "ALTER TABLE ${tableName} ADD ROLLUP
${rollupName}(`user_id`,`date`,`city`,`age`,`sex`) DUPLICATE KEY
(`user_id`,`date`,`city`,`age`,`sex`);"
- while (!result.contains("CANCELLED")){
- result = sql "SHOW ALTER TABLE ROLLUP WHERE TableName='${tableName}'
ORDER BY CreateTime DESC LIMIT 1;"
- result = result.toString()
- logger.info("result: ${result}")
- if(result.contains("FINISHED")){
- assertTrue(false);
- }
- Thread.sleep(100)
+ test {
+ sql "ALTER TABLE ${tableName} ADD ROLLUP r1
(`user_id`,`date`,`city`,`age`,`sex`) DUPLICATE KEY
(`user_id`,`date`,`city`,`age`,`sex`);"
+ exception "errCode = 2"
}
+
}
diff --git
a/regression-test/suites/rollup_p0/link_schema_change/test_rollup_uni_fail.groovy
b/regression-test/suites/rollup_p0/link_schema_change/test_rollup_uni_fail.groovy
index 41f1443bff..7944c8b90a 100644
---
a/regression-test/suites/rollup_p0/link_schema_change/test_rollup_uni_fail.groovy
+++
b/regression-test/suites/rollup_p0/link_schema_change/test_rollup_uni_fail.groovy
@@ -37,17 +37,8 @@ suite ("test_rollup_uni_fail") {
BUCKETS 1
PROPERTIES ( "replication_num" = "1", "light_schema_change" =
"true");
"""
-
- result = "null"
- rollupName = "rollup_cost"
- sql "ALTER TABLE ${tableName} ADD ROLLUP
${rollupName}(`user_id`,`date`,`age`, `sex`, cost);"
- while (!result.contains("CANCELLED")){
- result = sql "SHOW ALTER TABLE ROLLUP WHERE TableName='${tableName}'
ORDER BY CreateTime DESC LIMIT 1;"
- result = result.toString()
- logger.info("result: ${result}")
- if(result.contains("FINISHED")){
- assertTrue(false);
- }
- Thread.sleep(100)
+ test {
+ sql "ALTER TABLE ${tableName} ADD ROLLUP r1 (`user_id`,`date`,`age`,
`sex`, cost);"
+ exception "errCode = 2"
}
}
diff --git
a/regression-test/suites/rollup_p0/test_materialized_view_array.groovy
b/regression-test/suites/rollup_p0/test_materialized_view_array.groovy
index 4c1990d687..f4fb3ddd88 100644
--- a/regression-test/suites/rollup_p0/test_materialized_view_array.groovy
+++ b/regression-test/suites/rollup_p0/test_materialized_view_array.groovy
@@ -66,7 +66,7 @@ suite("test_materialized_view_array", "rollup") {
create_test_table.call(tableName)
test {
- sql "CREATE MATERIALIZED VIEW idx AS select k1, k2, k3, k4, k5
from ${tableName}"
+ sql "CREATE MATERIALIZED VIEW idx AS select k2,k1, k3, k4, k5 from
${tableName}"
exception "errCode = 2, detailMessage = The array column[`k2`
array<smallint(6)> NULL] not support to create materialized view"
}
} finally {
diff --git
a/regression-test/suites/schema_change_p0/test_dup_mv_schema_change.groovy
b/regression-test/suites/schema_change_p0/test_dup_mv_schema_change.groovy
index 9e3c885a0c..1e65a55e4d 100644
--- a/regression-test/suites/schema_change_p0/test_dup_mv_schema_change.groovy
+++ b/regression-test/suites/schema_change_p0/test_dup_mv_schema_change.groovy
@@ -79,7 +79,7 @@ suite ("test_dup_mv_schema_change") {
//add materialized view
def result = "null"
def mvName = "mv1"
- sql "create materialized view ${mvName} as select user_id, date, city,
age from ${tableName};"
+ sql "create materialized view ${mvName} as select date, user_id, city,
age from ${tableName};"
while (!result.contains("FINISHED")){
result = sql "SHOW ALTER TABLE MATERIALIZED VIEW WHERE
TableName='${tableName}' ORDER BY CreateTime DESC LIMIT 1;"
result = result.toString()
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
