This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.1 by this push:
new b07ea89aaee branch-2.1: [Fix](nereids) fix create view and alter view
using old parser checking #43263 (#44456)
b07ea89aaee is described below
commit b07ea89aaee20e2264455827c92a7261cceb2187
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Wed Nov 27 20:12:12 2024 +0800
branch-2.1: [Fix](nereids) fix create view and alter view using old parser
checking #43263 (#44456)
Cherry-picked from #43263
Co-authored-by: feiniaofeiafei <[email protected]>
---
.../main/java/org/apache/doris/alter/Alter.java | 5 -
.../org/apache/doris/analysis/BaseViewStmt.java | 26 ++
.../main/java/org/apache/doris/catalog/Env.java | 7 -
.../trees/plans/commands/info/AlterViewInfo.java | 14 +-
.../trees/plans/commands/info/BaseViewInfo.java | 4 +
.../trees/plans/commands/info/CreateViewInfo.java | 11 +-
.../create_view_nereids/create_view_use_mv.out | 15 +
.../test_create_view_nereids.out | 397 +++++++++++++++++++++
.../data/ddl_p0/test_create_view_nereids.out | 397 ---------------------
.../create_view_nereids/create_view_use_mv.groovy | 95 +++++
.../test_create_view_nereids.groovy | 0
11 files changed, 548 insertions(+), 423 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/alter/Alter.java
b/fe/fe-core/src/main/java/org/apache/doris/alter/Alter.java
index b7801cc3ba9..5c0390e84e0 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/alter/Alter.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/alter/Alter.java
@@ -666,11 +666,6 @@ public class Alter {
view.writeLockOrDdlException();
try {
view.setInlineViewDefWithSqlMode(inlineViewDef, sqlMode);
- try {
- view.init();
- } catch (UserException e) {
- throw new DdlException("failed to init view stmt, reason="
+ e.getMessage());
- }
view.setNewFullSchema(newFullSchema);
String viewName = view.getName();
db.unregisterTable(viewName);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/BaseViewStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/BaseViewStmt.java
index e265703128d..d33c234af11 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/BaseViewStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/BaseViewStmt.java
@@ -21,20 +21,24 @@ import org.apache.doris.catalog.Column;
import org.apache.doris.catalog.Env;
import org.apache.doris.catalog.Type;
import org.apache.doris.common.AnalysisException;
+import org.apache.doris.common.DdlException;
import org.apache.doris.common.ErrorCode;
import org.apache.doris.common.ErrorReport;
import org.apache.doris.common.UserException;
+import org.apache.doris.common.util.SqlParserUtils;
import org.apache.doris.common.util.ToSqlContext;
import org.apache.doris.datasource.InternalCatalog;
import org.apache.doris.mysql.privilege.PrivPredicate;
import org.apache.doris.qe.ConnectContext;
+import com.google.common.base.Preconditions;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
+import java.io.StringReader;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;
@@ -147,6 +151,7 @@ public class BaseViewStmt extends DdlStmt {
// we don't need the slot id info, so using ToSqlContext to
remove it.
toSqlContext.setNeedSlotRefId(false);
inlineViewDef = viewDefStmt.toSql();
+ checkInlineViewDef();
}
return;
}
@@ -161,6 +166,7 @@ public class BaseViewStmt extends DdlStmt {
// we don't need the slot id info, so using ToSqlContext to remove
it.
toSqlContext.setNeedSlotRefId(false);
inlineViewDef = cloneStmt.toSql();
+ checkInlineViewDef();
}
}
@@ -172,4 +178,24 @@ public class BaseViewStmt extends DdlStmt {
throw new AnalysisException("Not support OUTFILE clause in CREATE
VIEW statement");
}
}
+
+ private void checkInlineViewDef() throws UserException {
+ Preconditions.checkNotNull(inlineViewDef);
+ SqlScanner input = new SqlScanner(new StringReader(inlineViewDef),
+ ConnectContext.get().getSessionVariable().getSqlMode());
+ SqlParser parser = new SqlParser(input);
+ ParseNode node;
+ try {
+ node = SqlParserUtils.getFirstStmt(parser);
+ } catch (Exception e) {
+ throw new DdlException(
+ String.format("Failed to parse view-definition statement
of view: %s, stmt is %s, reason is %s",
+ tableName, inlineViewDef, e.getMessage()));
+ }
+ // Make sure the view definition parses to a query statement.
+ if (!(node instanceof QueryStmt)) {
+ throw new DdlException(String.format("View definition of %s "
+ + "is not a query statement", tableName));
+ }
+ }
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/Env.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/Env.java
index de76d445ca7..a0624c407ae 100755
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/Env.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/Env.java
@@ -5358,13 +5358,6 @@ public class Env {
newView.setComment(stmt.getComment());
newView.setInlineViewDefWithSqlMode(stmt.getInlineViewDef(),
ConnectContext.get().getSessionVariable().getSqlMode());
- // init here in case the stmt string from view.toSql() has some
syntax error.
- try {
- newView.init();
- } catch (UserException e) {
- throw new DdlException("failed to init view stmt, reason=" +
e.getMessage());
- }
-
if (!((Database) db).createTableWithLock(newView, false,
stmt.isSetIfNotExists()).first) {
throw new DdlException("Failed to create view[" + tableName +
"].");
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/info/AlterViewInfo.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/info/AlterViewInfo.java
index b5e47bc0770..f065e292e82 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/info/AlterViewInfo.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/info/AlterViewInfo.java
@@ -77,18 +77,16 @@ public class AlterViewInfo extends BaseViewInfo {
/**translateToLegacyStmt*/
public AlterViewStmt translateToLegacyStmt(ConnectContext ctx) {
- List<ColWithComment> cols = Lists.newArrayList();
- for (SimpleColumnDefinition def : simpleColumnDefinitions) {
- cols.add(def.translateToColWithComment());
- }
- AlterViewStmt alterViewStmt = new
AlterViewStmt(viewName.transferToTableName(), cols,
- null);
// expand star(*) in project list and replace table name with qualifier
String rewrittenSql =
rewriteSql(ctx.getStatementContext().getIndexInSqlToString(), querySql);
-
// rewrite project alias
rewrittenSql = rewriteProjectsToUserDefineAlias(rewrittenSql);
-
+ checkViewSql(rewrittenSql);
+ List<ColWithComment> cols = Lists.newArrayList();
+ for (SimpleColumnDefinition def : simpleColumnDefinitions) {
+ cols.add(def.translateToColWithComment());
+ }
+ AlterViewStmt alterViewStmt = new
AlterViewStmt(viewName.transferToTableName(), cols, null);
alterViewStmt.setInlineViewDef(rewrittenSql);
alterViewStmt.setFinalColumns(finalCols);
return alterViewStmt;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/info/BaseViewInfo.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/info/BaseViewInfo.java
index b8332a256ef..ad8870fb367 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/info/BaseViewInfo.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/info/BaseViewInfo.java
@@ -428,4 +428,8 @@ public class BaseViewInfo {
return null;
}
}
+
+ protected void checkViewSql(String viewSql) {
+ new NereidsParser().parseSingle(viewSql);
+ }
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/info/CreateViewInfo.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/info/CreateViewInfo.java
index 93c287885ec..c9253b224af 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/info/CreateViewInfo.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/info/CreateViewInfo.java
@@ -72,18 +72,17 @@ public class CreateViewInfo extends BaseViewInfo {
/**translateToLegacyStmt*/
public CreateViewStmt translateToLegacyStmt(ConnectContext ctx) {
+ // expand star(*) in project list and replace table name with qualifier
+ String rewrittenSql =
rewriteSql(ctx.getStatementContext().getIndexInSqlToString(), querySql);
+ // rewrite project alias
+ rewrittenSql = rewriteProjectsToUserDefineAlias(rewrittenSql);
+ checkViewSql(rewrittenSql);
List<ColWithComment> cols = Lists.newArrayList();
for (SimpleColumnDefinition def : simpleColumnDefinitions) {
cols.add(def.translateToColWithComment());
}
CreateViewStmt createViewStmt = new CreateViewStmt(ifNotExists,
orReplace, viewName.transferToTableName(), cols,
comment, null);
- // expand star(*) in project list and replace table name with qualifier
- String rewrittenSql =
rewriteSql(ctx.getStatementContext().getIndexInSqlToString(), querySql);
-
- // rewrite project alias
- rewrittenSql = rewriteProjectsToUserDefineAlias(rewrittenSql);
-
createViewStmt.setInlineViewDef(rewrittenSql);
createViewStmt.setFinalColumns(finalCols);
return createViewStmt;
diff --git
a/regression-test/data/ddl_p0/create_view_nereids/create_view_use_mv.out
b/regression-test/data/ddl_p0/create_view_nereids/create_view_use_mv.out
new file mode 100644
index 00000000000..c4df7484022
--- /dev/null
+++ b/regression-test/data/ddl_p0/create_view_nereids/create_view_use_mv.out
@@ -0,0 +1,15 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !create_view_from_mv --
+\N 99.50 99.50 99.50 1 \N \N \N \N 1
\N \N
+1 208.70 109.20 99.50 3 1 3 1 1 3
\N \N
+2 218.40 109.20 109.20 2 2 2 2 2 1
\N \N
+3 298.50 99.50 99.50 3 3 6 3 3 2
\N \N
+4 99.50 99.50 99.50 1 \N 4 4 4 1
\N \N
+
+-- !alter_view_from_mv --
+\N 99.50 99.50 99.50 1 \N \N \N \N 1
\N \N
+1 208.70 109.20 99.50 3 1 3 1 1 3
\N \N
+2 218.40 109.20 109.20 2 2 2 2 2 1
\N \N
+3 298.50 99.50 99.50 3 3 6 3 3 2
\N \N
+4 99.50 99.50 99.50 1 \N 4 4 4 1
\N \N
+
diff --git
a/regression-test/data/ddl_p0/create_view_nereids/test_create_view_nereids.out
b/regression-test/data/ddl_p0/create_view_nereids/test_create_view_nereids.out
new file mode 100644
index 00000000000..5bdfe66d5aa
--- /dev/null
+++
b/regression-test/data/ddl_p0/create_view_nereids/test_create_view_nereids.out
@@ -0,0 +1,397 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !test_view_1 --
+1 [1, 2, 3]
+2 [10, -2, 8]
+3 [-1, 20, 0]
+
+-- !test_view_2 --
+1 [1, 2, 3] [1, 1, 1]
+2 [10, -2, 8] [1, 0, 1]
+3 [-1, 20, 0] [0, 1, 0]
+
+-- !test_view_3 --
+1 [1, 2, 3] [1, 2, 3] [1, 2, 3]
+2 [10, -2, 8] [10, 8] [10, 8]
+3 [-1, 20, 0] [20] [20]
+
+-- !test_view_4 --
+1 [1, 2, 3] [1, 2, 3] [1, 2, 3]
+2 [10, -2, 8] [10, 8] [10, 8]
+3 [-1, 20, 0] [20] [20]
+
+-- !test_view_5 --
+1 [1, 2, 3] [1, 1, 1]
+2 [10, -2, 8] [1, 0, 1]
+3 [-1, 20, 0] [0, 1, 0]
+
+-- !test_view_6 --
+v1 CREATE VIEW `v1` AS SELECT\n
`internal`.`regression_test_ddl_p0_create_view_nereids`.`view_column_name_test_nereids`.`error_code`,
\n 1, \n 'string', \n now(), \n
dayofyear(`internal`.`regression_test_ddl_p0_create_view_nereids`.`view_column_name_test_nereids`.`op_time`),
\n cast
(`internal`.`regression_test_ddl_p0_create_view_nereids`.`view_column_name_test_nereids`.`source`
AS BIGINT), \n
min(`internal`.`regression_test_ddl_p0_create_view_nereids`. [...]
+
+-- !test_with_as --
+1 1 2
+1 1 4
+1 3 6
+2 1 3
+2 1 4
+2 1 7
+2 3 5
+2 3 9
+2 4 2
+3 2 8
+3 5 \N
+3 5 6
+3 5 6
+3 5 8
+4 5 6
+6 \N 6
+6 7 1
+
+-- !test_with_as_sql --
+test_view_with_as CREATE VIEW `test_view_with_as` AS (\n with
`t1` as (select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`pk`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`a`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`b`
from `internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`),
`t2` as (select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`pk`
[...]
+
+-- !test_union --
+1 1 2
+1 1 2
+1 1 4
+1 1 4
+1 3 6
+1 3 6
+2 1 3
+2 1 3
+2 1 4
+2 1 4
+2 1 7
+2 1 7
+2 3 5
+2 3 5
+2 3 9
+2 3 9
+2 4 2
+2 4 2
+3 2 8
+3 2 8
+3 5 \N
+3 5 \N
+3 5 6
+3 5 6
+3 5 6
+3 5 6
+3 5 8
+3 5 8
+4 5 6
+4 5 6
+6 \N 6
+6 \N 6
+6 7 1
+6 7 1
+
+-- !test_union_sql --
+test_view_union CREATE VIEW `test_view_union` AS (select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`pk` AS
`c1`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`a` AS
`c2`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`b` AS
`c3` from
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view` Union
all SELECT
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`pk`,
`interna [...]
+
+-- !test_count_star --
+17
+
+-- !test_count_star_sql --
+test_view_count_star CREATE VIEW `test_view_count_star` AS (select count(*)
AS `c1` from
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view` having
count(*) > 0); utf8mb4 utf8mb4_0900_bin
+
+-- !test_expression --
+\N \N 6
+2 4 2
+2 4 3
+2 4 4
+2 4 4
+2 4 7
+3 5 8
+4 6 5
+4 6 6
+4 6 9
+5 7 2
+6 8 \N
+6 8 6
+6 8 6
+6 8 6
+6 8 8
+8 10 1
+
+-- !test_expression_sql --
+test_view_expression CREATE VIEW `test_view_expression` AS (select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`a`+1
AS `c1`,
abs(`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`a`)+2+1
AS `c2`,
cast(`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`b`
as varchar(10)) AS `c3` from
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`);
utf8mb4 utf8mb4_0900_bin
+
+-- !test_alias --
+\N \N 6
+2 4 2
+2 4 3
+2 4 4
+2 4 4
+2 4 7
+3 5 8
+4 6 5
+4 6 6
+4 6 9
+5 7 2
+6 8 \N
+6 8 6
+6 8 6
+6 8 6
+6 8 8
+8 10 1
+
+-- !test_alias_sql --
+test_view_alias CREATE VIEW `test_view_alias` AS (\n select
`t`.`c8` AS `c1`, `t`.`c2` AS `c2`, `t`.`c1` AS `c3` from (select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`a`+1
`c8`,abs(`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`a`)+2+1
as `c2`,
cast(`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`b`
as varchar(10)) as `c1` from
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`) [...]
+
+-- !test_star_except --
+\N 6
+1 2
+1 3
+1 4
+1 4
+1 7
+2 8
+3 5
+3 6
+3 9
+4 2
+5 \N
+5 6
+5 6
+5 6
+5 8
+7 1
+
+-- !test_star_except_sql --
+test_view_star_except CREATE VIEW `test_view_star_except` AS select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`a`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`b`
from `internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`;
utf8mb4 utf8mb4_0900_bin
+
+-- !test_create_view_from_view --
+1 1 2
+1 1 4
+1 3 6
+2 1 3
+2 1 4
+2 1 7
+2 3 5
+2 3 9
+2 4 2
+3 2 8
+3 5 \N
+3 5 6
+3 5 6
+3 5 8
+4 5 6
+6 \N 6
+6 7 1
+
+-- !test_create_view_from_view_sql --
+test_view_from_view CREATE VIEW `test_view_from_view` AS select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`test_view_with_as`.`pk`
AS `c1`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`test_view_with_as`.`a`
AS `c2`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`test_view_with_as`.`b`
AS `c3` from
`internal`.`regression_test_ddl_p0_create_view_nereids`.`test_view_with_as`;
utf8mb4 utf8mb4_0900_bin
+
+-- !test_backquote_in_view_define --
+\N 6
+1 2
+1 3
+1 4
+1 4
+1 7
+2 8
+3 5
+3 6
+3 9
+4 2
+5 \N
+5 6
+5 6
+5 6
+5 8
+7 1
+
+-- !test_backquote_in_view_define_sql --
+test_backquote_in_view_define CREATE VIEW `test_backquote_in_view_define` AS
select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`a` AS
`abc`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`b` AS
`c2` from
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`;
utf8mb4 utf8mb4_0900_bin
+
+-- !test_backquote_in_table_alias --
+\N 6
+1 2
+1 3
+1 4
+1 4
+1 7
+2 8
+3 5
+3 6
+3 9
+4 2
+5 \N
+5 6
+5 6
+5 6
+5 8
+7 1
+
+-- !test_backquote_in_table_alias_sql --
+test_backquote_in_table_alias CREATE VIEW `test_backquote_in_table_alias` AS
select `internal`.`regression_test_ddl_p0_create_view_nereids`.`ab``c`.`a` AS
`c1`, `internal`.`regression_test_ddl_p0_create_view_nereids`.`ab``c`.`b` AS
`c2` from (select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`a`,`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`b`
from `internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`)
`ab``c`; utf [...]
+
+-- !test_invalid_column_name_in_table --
+\N 6
+1 2
+1 3
+1 4
+1 4
+1 7
+2 8
+3 5
+3 6
+3 9
+4 2
+5 \N
+5 6
+5 6
+5 6
+5 8
+7 1
+
+-- !test_invalid_column_name_in_table_define_sql --
+test_invalid_column_name_in_table CREATE VIEW
`test_invalid_column_name_in_table` AS select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`a`
,`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`b`
from `internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`;
utf8mb4 utf8mb4_0900_bin
+
+-- !test_generate --
+1 10 A 30
+1 10 A 60
+2 20 B 30
+2 20 B 60
+3 30 C 30
+3 30 C 60
+4 40 D 30
+4 40 D 60
+
+-- !test_generate_sql --
+test_view_generate CREATE VIEW `test_view_generate` AS select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`id`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`value1`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`value2`,
`t1`.`age` from
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`
lateral view EXPLODE(ARRAY(30,60)) `t1` as `age`; utf8mb4 utf8mb4_0900_bin
+
+-- !test_generate_with_column --
+1 10 A 0
+2 20 B 0
+2 20 B 1
+3 30 C 0
+3 30 C 1
+3 30 C 2
+4 40 D 0
+4 40 D 1
+4 40 D 2
+4 40 D 3
+
+-- !test_generate_with_column_sql --
+test_view_generate_with_column CREATE VIEW `test_view_generate_with_column` AS
select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`id`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`value1`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`value2`,
`t1`.`age` from
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`
lateral view EXPLODE_numbers(`internal`.`regression_test [...]
+
+-- !test_col_alias --
+1 10
+2 20
+3 30
+4 40
+
+-- !test_col_alias_sql --
+test_view_col_alias CREATE VIEW `test_view_col_alias` AS select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`id`
as `c1`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`value1`
as `c2` from
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`;
utf8mb4 utf8mb4_0900_bin
+
+-- !test_col_alias_with_specific_name --
+1 10
+2 20
+3 30
+4 40
+
+-- !test_col_alias_with_specific_name_sql --
+test_view_col_alias_specific_name CREATE VIEW
`test_view_col_alias_specific_name` AS select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`id`
AS `col1`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`value1`
AS `col2` from
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`;
utf8mb4 utf8mb4_0900_bin
+
+-- !test_table_alias --
+1 10
+2 20
+3 30
+4 40
+
+-- !test_table_alias_sql --
+test_view_table_alias CREATE VIEW `test_view_table_alias` AS select `t`.`c1`,
`t`.`c2` from (\n select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`id`
as `c1`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`value1`
as `c2` from
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`
limit 10) as `t`; utf8mb4 utf8mb4_0900_bin
+
+-- !test_join_table_alias --
+1 10
+2 20
+3 30
+4 40
+
+-- !test_join_table_alias_sql --
+test_view_join_table_alias CREATE VIEW `test_view_join_table_alias` AS
select `t`.`c1`, `t`.`c2` from (\n select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`t1`.`id` as `c1`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`t1`.`value1` as `c2`
from
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`
`t1` inner join
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table2`
`t2` on `internal`.`regression_test_ddl_ [...]
+
+-- !test_alias_udf --
+1****1 1
+2****2 2
+3****3 3
+4****4 4
+
+-- !test_alias_udf_sql --
+test_view_alias_udf CREATE VIEW `test_view_alias_udf` AS (select
`regression_test_ddl_p0_create_view_nereids`.`alias_function_create_view_test`(`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`id`)
as
`c1`,abs(`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`id`)
from
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`);
utf8mb4 utf8mb4_0900_bin
+
+-- !test_alias_with_db_udf --
+1****1 1
+2****2 2
+3****3 3
+4****4 4
+
+-- !test_alias_with_db_udf_sql --
+test_view_alias_udf_with_db CREATE VIEW `test_view_alias_udf_with_db` AS
(select
`regression_test_ddl_p0_create_view_nereids`.`alias_function_create_view_test`(`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`id`)
as
`c1`,abs(`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`id`)
from
`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`);
utf8mb4 utf8mb4_0900_bin
+
+-- !test_udf_sql --
+test_view_udf CREATE VIEW `test_view_udf` AS (select
`regression_test_ddl_p0_create_view_nereids`.`alias_function_create_view_test`(`internal`.`regression_test_ddl_p0_create_view_nereids`.`create_view_table1`.`id`)
as `c1`,
`regression_test_ddl_p0_create_view_nereids`.`java_udf_create_view_test`('2011-01-01','2011-01-02'),\n
`regression_test_ddl_p0_create_view_nereids`.`java_udf_create_view_test`('2011-01-01','2011-01-03')
from `internal`.`regression_test_ddl_p0_create_view_nereid [...]
+
+-- !test_udf --
+1****1 false false
+2****2 false false
+3****3 false false
+4****4 false false
+
+-- !test_with_as_with_columns --
+1 1 2
+1 1 4
+1 3 6
+2 1 3
+2 1 4
+2 1 7
+2 3 5
+2 3 9
+2 4 2
+3 2 8
+3 5 \N
+3 5 6
+3 5 6
+3 5 8
+4 5 6
+6 \N 6
+6 7 1
+
+-- !test_with_as_with_columns_sql --
+test_view_with_as_with_columns CREATE VIEW `test_view_with_as_with_columns` AS
(\n with `t1`(`c1`,`c2`,`c3`) as (select
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`pk`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`a`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`b`
from `internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`),
`t2` as (select `internal`.`regression_test_ddl_p0 [...]
+
+-- !test_having --
+2 2
+3 3
+4 2
+4 4
+5 5
+6 3
+7 7
+8 4
+9 3
+10 5
+14 7
+15 5
+
+-- !test_having_sql --
+test_having CREATE VIEW `test_having` AS select
sum(`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`a`)
over(partition by
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`a`
order by
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`pk`)
as `c1` ,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`.`a`
from `internal`.`regression_test_ddl_p0_create_view_nereids`.`mal_test_view`
group by grouping s [...]
+
+-- !complicated_view1 --
+1 100 1
+2 200 1
+
+-- !complicated_view1_sql --
+test_view_complicated CREATE VIEW `test_view_complicated` AS SELECT
`internal`.`regression_test_ddl_p0_create_view_nereids`.`t`.`id`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`t`.`value3`,
`t`.`row_num` FROM (\n SELECT
`internal`.`regression_test_ddl_p0_create_view_nereids`.`t1`.`id`,
`internal`.`regression_test_ddl_p0_create_view_nereids`.`tt`.`value3`,
ROW_NUMBER() OVER (PARTITION BY
`internal`.`regression_test_ddl_p0_create_view_nereids`.`t1`.`id` ORDER BY
`inter [...]
+
+-- !nullable --
+1 小区A 10 1 2024-09-01T09:00 2024-09-01T10:00
+2 小区B 11 1 2024-09-01T09:00 2024-09-01T10:00
+3 小区C \N 1 2024-09-01T09:00 2024-09-01T10:00
+
+-- !nullable_view_with_cols --
+1 小区A 10 1 2024-09-01T09:00 2024-09-01T10:00
+2 小区B 11 1 2024-09-01T09:00 2024-09-01T10:00
+3 小区C \N 1 2024-09-01T09:00 2024-09-01T10:00
+
diff --git a/regression-test/data/ddl_p0/test_create_view_nereids.out
b/regression-test/data/ddl_p0/test_create_view_nereids.out
deleted file mode 100644
index 1b2ba19d937..00000000000
--- a/regression-test/data/ddl_p0/test_create_view_nereids.out
+++ /dev/null
@@ -1,397 +0,0 @@
--- This file is automatically generated. You should know what you did if you
want to edit this
--- !test_view_1 --
-1 [1, 2, 3]
-2 [10, -2, 8]
-3 [-1, 20, 0]
-
--- !test_view_2 --
-1 [1, 2, 3] [1, 1, 1]
-2 [10, -2, 8] [1, 0, 1]
-3 [-1, 20, 0] [0, 1, 0]
-
--- !test_view_3 --
-1 [1, 2, 3] [1, 2, 3] [1, 2, 3]
-2 [10, -2, 8] [10, 8] [10, 8]
-3 [-1, 20, 0] [20] [20]
-
--- !test_view_4 --
-1 [1, 2, 3] [1, 2, 3] [1, 2, 3]
-2 [10, -2, 8] [10, 8] [10, 8]
-3 [-1, 20, 0] [20] [20]
-
--- !test_view_5 --
-1 [1, 2, 3] [1, 1, 1]
-2 [10, -2, 8] [1, 0, 1]
-3 [-1, 20, 0] [0, 1, 0]
-
--- !test_view_6 --
-v1 CREATE VIEW `v1` AS SELECT\n
`internal`.`regression_test_ddl_p0`.`view_column_name_test_nereids`.`error_code`,
\n 1, \n 'string', \n now(), \n
dayofyear(`internal`.`regression_test_ddl_p0`.`view_column_name_test_nereids`.`op_time`),
\n cast
(`internal`.`regression_test_ddl_p0`.`view_column_name_test_nereids`.`source`
AS BIGINT), \n
min(`internal`.`regression_test_ddl_p0`.`view_column_name_test_nereids`.`timestamp`)
OVER (\n ORDER BY \n [...]
-
--- !test_with_as --
-1 1 2
-1 1 4
-1 3 6
-2 1 3
-2 1 4
-2 1 7
-2 3 5
-2 3 9
-2 4 2
-3 2 8
-3 5 \N
-3 5 6
-3 5 6
-3 5 8
-4 5 6
-6 \N 6
-6 7 1
-
--- !test_with_as_sql --
-test_view_with_as CREATE VIEW `test_view_with_as` AS (\n with
`t1` as (select `internal`.`regression_test_ddl_p0`.`mal_test_view`.`pk`,
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a`,
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`b` from
`internal`.`regression_test_ddl_p0`.`mal_test_view`), `t2` as (select
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`pk`,
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a`,
`internal`.`regression_test_ddl_p0`.`mal_ [...]
-
--- !test_union --
-1 1 2
-1 1 2
-1 1 4
-1 1 4
-1 3 6
-1 3 6
-2 1 3
-2 1 3
-2 1 4
-2 1 4
-2 1 7
-2 1 7
-2 3 5
-2 3 5
-2 3 9
-2 3 9
-2 4 2
-2 4 2
-3 2 8
-3 2 8
-3 5 \N
-3 5 \N
-3 5 6
-3 5 6
-3 5 6
-3 5 6
-3 5 8
-3 5 8
-4 5 6
-4 5 6
-6 \N 6
-6 \N 6
-6 7 1
-6 7 1
-
--- !test_union_sql --
-test_view_union CREATE VIEW `test_view_union` AS (select
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`pk` AS `c1`,
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a` AS `c2`,
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`b` AS `c3` from
`internal`.`regression_test_ddl_p0`.`mal_test_view` Union all SELECT
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`pk`,
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a`,
`internal`.`regression_test_ddl_p0`.`mal_test_view` [...]
-
--- !test_count_star --
-17
-
--- !test_count_star_sql --
-test_view_count_star CREATE VIEW `test_view_count_star` AS (select count(*)
AS `c1` from `internal`.`regression_test_ddl_p0`.`mal_test_view` having
count(*) > 0); utf8mb4 utf8mb4_0900_bin
-
--- !test_expression --
-\N \N 6
-2 4 2
-2 4 3
-2 4 4
-2 4 4
-2 4 7
-3 5 8
-4 6 5
-4 6 6
-4 6 9
-5 7 2
-6 8 \N
-6 8 6
-6 8 6
-6 8 6
-6 8 8
-8 10 1
-
--- !test_expression_sql --
-test_view_expression CREATE VIEW `test_view_expression` AS (select
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a`+1 AS `c1`,
abs(`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a`)+2+1 AS `c2`,
cast(`internal`.`regression_test_ddl_p0`.`mal_test_view`.`b` as varchar(10)) AS
`c3` from `internal`.`regression_test_ddl_p0`.`mal_test_view`); utf8mb4
utf8mb4_0900_bin
-
--- !test_alias --
-\N \N 6
-2 4 2
-2 4 3
-2 4 4
-2 4 4
-2 4 7
-3 5 8
-4 6 5
-4 6 6
-4 6 9
-5 7 2
-6 8 \N
-6 8 6
-6 8 6
-6 8 6
-6 8 8
-8 10 1
-
--- !test_alias_sql --
-test_view_alias CREATE VIEW `test_view_alias` AS (\n select
`t`.`c8` AS `c1`, `t`.`c2` AS `c2`, `t`.`c1` AS `c3` from (select
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a`+1
`c8`,abs(`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a`)+2+1 as `c2`,
cast(`internal`.`regression_test_ddl_p0`.`mal_test_view`.`b` as varchar(10)) as
`c1` from `internal`.`regression_test_ddl_p0`.`mal_test_view`) `t`);
utf8mb4 utf8mb4_0900_bin
-
--- !test_star_except --
-\N 6
-1 2
-1 3
-1 4
-1 4
-1 7
-2 8
-3 5
-3 6
-3 9
-4 2
-5 \N
-5 6
-5 6
-5 6
-5 8
-7 1
-
--- !test_star_except_sql --
-test_view_star_except CREATE VIEW `test_view_star_except` AS select
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a`,
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`b` from
`internal`.`regression_test_ddl_p0`.`mal_test_view`; utf8mb4
utf8mb4_0900_bin
-
--- !test_create_view_from_view --
-1 1 2
-1 1 4
-1 3 6
-2 1 3
-2 1 4
-2 1 7
-2 3 5
-2 3 9
-2 4 2
-3 2 8
-3 5 \N
-3 5 6
-3 5 6
-3 5 8
-4 5 6
-6 \N 6
-6 7 1
-
--- !test_create_view_from_view_sql --
-test_view_from_view CREATE VIEW `test_view_from_view` AS select
`internal`.`regression_test_ddl_p0`.`test_view_with_as`.`pk` AS `c1`,
`internal`.`regression_test_ddl_p0`.`test_view_with_as`.`a` AS `c2`,
`internal`.`regression_test_ddl_p0`.`test_view_with_as`.`b` AS `c3` from
`internal`.`regression_test_ddl_p0`.`test_view_with_as`; utf8mb4
utf8mb4_0900_bin
-
--- !test_backquote_in_view_define --
-\N 6
-1 2
-1 3
-1 4
-1 4
-1 7
-2 8
-3 5
-3 6
-3 9
-4 2
-5 \N
-5 6
-5 6
-5 6
-5 8
-7 1
-
--- !test_backquote_in_view_define_sql --
-test_backquote_in_view_define CREATE VIEW `test_backquote_in_view_define` AS
select `internal`.`regression_test_ddl_p0`.`mal_test_view`.`a` AS `abc`,
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`b` AS `c2` from
`internal`.`regression_test_ddl_p0`.`mal_test_view`; utf8mb4
utf8mb4_0900_bin
-
--- !test_backquote_in_table_alias --
-\N 6
-1 2
-1 3
-1 4
-1 4
-1 7
-2 8
-3 5
-3 6
-3 9
-4 2
-5 \N
-5 6
-5 6
-5 6
-5 8
-7 1
-
--- !test_backquote_in_table_alias_sql --
-test_backquote_in_table_alias CREATE VIEW `test_backquote_in_table_alias` AS
select `internal`.`regression_test_ddl_p0`.`ab``c`.`a` AS `c1`,
`internal`.`regression_test_ddl_p0`.`ab``c`.`b` AS `c2` from (select
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a`,`internal`.`regression_test_ddl_p0`.`mal_test_view`.`b`
from `internal`.`regression_test_ddl_p0`.`mal_test_view`) `ab``c`; utf8mb4
utf8mb4_0900_bin
-
--- !test_invalid_column_name_in_table --
-\N 6
-1 2
-1 3
-1 4
-1 4
-1 7
-2 8
-3 5
-3 6
-3 9
-4 2
-5 \N
-5 6
-5 6
-5 6
-5 8
-7 1
-
--- !test_invalid_column_name_in_table_define_sql --
-test_invalid_column_name_in_table CREATE VIEW
`test_invalid_column_name_in_table` AS select
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a`
,`internal`.`regression_test_ddl_p0`.`mal_test_view`.`b` from
`internal`.`regression_test_ddl_p0`.`mal_test_view`; utf8mb4 utf8mb4_0900_bin
-
--- !test_generate --
-1 10 A 30
-1 10 A 60
-2 20 B 30
-2 20 B 60
-3 30 C 30
-3 30 C 60
-4 40 D 30
-4 40 D 60
-
--- !test_generate_sql --
-test_view_generate CREATE VIEW `test_view_generate` AS select
`internal`.`regression_test_ddl_p0`.`create_view_table1`.`id`,
`internal`.`regression_test_ddl_p0`.`create_view_table1`.`value1`,
`internal`.`regression_test_ddl_p0`.`create_view_table1`.`value2`, `t1`.`age`
from `internal`.`regression_test_ddl_p0`.`create_view_table1` lateral view
EXPLODE(ARRAY(30,60)) `t1` as `age`; utf8mb4 utf8mb4_0900_bin
-
--- !test_generate_with_column --
-1 10 A 0
-2 20 B 0
-2 20 B 1
-3 30 C 0
-3 30 C 1
-3 30 C 2
-4 40 D 0
-4 40 D 1
-4 40 D 2
-4 40 D 3
-
--- !test_generate_with_column_sql --
-test_view_generate_with_column CREATE VIEW `test_view_generate_with_column` AS
select `internal`.`regression_test_ddl_p0`.`create_view_table1`.`id`,
`internal`.`regression_test_ddl_p0`.`create_view_table1`.`value1`,
`internal`.`regression_test_ddl_p0`.`create_view_table1`.`value2`, `t1`.`age`
from `internal`.`regression_test_ddl_p0`.`create_view_table1` lateral view
EXPLODE_numbers(`internal`.`regression_test_ddl_p0`.`create_view_table1`.`id`)
`t1` as `age`; utf8mb4 utf8mb4_0900_bin
-
--- !test_col_alias --
-1 10
-2 20
-3 30
-4 40
-
--- !test_col_alias_sql --
-test_view_col_alias CREATE VIEW `test_view_col_alias` AS select
`internal`.`regression_test_ddl_p0`.`create_view_table1`.`id` as `c1`,
`internal`.`regression_test_ddl_p0`.`create_view_table1`.`value1` as `c2` from
`internal`.`regression_test_ddl_p0`.`create_view_table1`; utf8mb4
utf8mb4_0900_bin
-
--- !test_col_alias_with_specific_name --
-1 10
-2 20
-3 30
-4 40
-
--- !test_col_alias_with_specific_name_sql --
-test_view_col_alias_specific_name CREATE VIEW
`test_view_col_alias_specific_name` AS select
`internal`.`regression_test_ddl_p0`.`create_view_table1`.`id` AS `col1`,
`internal`.`regression_test_ddl_p0`.`create_view_table1`.`value1` AS `col2`
from `internal`.`regression_test_ddl_p0`.`create_view_table1`; utf8mb4
utf8mb4_0900_bin
-
--- !test_table_alias --
-1 10
-2 20
-3 30
-4 40
-
--- !test_table_alias_sql --
-test_view_table_alias CREATE VIEW `test_view_table_alias` AS select `t`.`c1`,
`t`.`c2` from (\n select
`internal`.`regression_test_ddl_p0`.`create_view_table1`.`id` as `c1`,
`internal`.`regression_test_ddl_p0`.`create_view_table1`.`value1` as `c2` from
`internal`.`regression_test_ddl_p0`.`create_view_table1` limit 10) as `t`;
utf8mb4 utf8mb4_0900_bin
-
--- !test_join_table_alias --
-1 10
-2 20
-3 30
-4 40
-
--- !test_join_table_alias_sql --
-test_view_join_table_alias CREATE VIEW `test_view_join_table_alias` AS
select `t`.`c1`, `t`.`c2` from (\n select
`internal`.`regression_test_ddl_p0`.`t1`.`id` as `c1`,
`internal`.`regression_test_ddl_p0`.`t1`.`value1` as `c2` from
`internal`.`regression_test_ddl_p0`.`create_view_table1` `t1` inner join
`internal`.`regression_test_ddl_p0`.`create_view_table2` `t2` on
`internal`.`regression_test_ddl_p0`.`t1`.`id`=`internal`.`regression_test_ddl_p0`.`t2`.`id`
limit 10) as `t`; ut [...]
-
--- !test_alias_udf --
-1****1 1
-2****2 2
-3****3 3
-4****4 4
-
--- !test_alias_udf_sql --
-test_view_alias_udf CREATE VIEW `test_view_alias_udf` AS (select
`regression_test_ddl_p0`.`alias_function_create_view_test`(`internal`.`regression_test_ddl_p0`.`create_view_table1`.`id`)
as `c1`,abs(`internal`.`regression_test_ddl_p0`.`create_view_table1`.`id`)
from `internal`.`regression_test_ddl_p0`.`create_view_table1`); utf8mb4
utf8mb4_0900_bin
-
--- !test_alias_with_db_udf --
-1****1 1
-2****2 2
-3****3 3
-4****4 4
-
--- !test_alias_with_db_udf_sql --
-test_view_alias_udf_with_db CREATE VIEW `test_view_alias_udf_with_db` AS
(select
`regression_test_ddl_p0`.`alias_function_create_view_test`(`internal`.`regression_test_ddl_p0`.`create_view_table1`.`id`)
as `c1`,abs(`internal`.`regression_test_ddl_p0`.`create_view_table1`.`id`)
from `internal`.`regression_test_ddl_p0`.`create_view_table1`); utf8mb4
utf8mb4_0900_bin
-
--- !test_udf_sql --
-test_view_udf CREATE VIEW `test_view_udf` AS (select
`regression_test_ddl_p0`.`alias_function_create_view_test`(`internal`.`regression_test_ddl_p0`.`create_view_table1`.`id`)
as `c1`,
`regression_test_ddl_p0`.`java_udf_create_view_test`('2011-01-01','2011-01-02'),\n
`regression_test_ddl_p0`.`java_udf_create_view_test`('2011-01-01','2011-01-03')
from `internal`.`regression_test_ddl_p0`.`create_view_table1`); utf8mb4
utf8mb4_0900_bin
-
--- !test_udf --
-1****1 false false
-2****2 false false
-3****3 false false
-4****4 false false
-
--- !test_with_as_with_columns --
-1 1 2
-1 1 4
-1 3 6
-2 1 3
-2 1 4
-2 1 7
-2 3 5
-2 3 9
-2 4 2
-3 2 8
-3 5 \N
-3 5 6
-3 5 6
-3 5 8
-4 5 6
-6 \N 6
-6 7 1
-
--- !test_with_as_with_columns_sql --
-test_view_with_as_with_columns CREATE VIEW `test_view_with_as_with_columns` AS
(\n with `t1`(`c1`,`c2`,`c3`) as (select
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`pk`,
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a`,
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`b` from
`internal`.`regression_test_ddl_p0`.`mal_test_view`), `t2` as (select
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`pk`,
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a`, [...]
-
--- !test_having --
-2 2
-3 3
-4 2
-4 4
-5 5
-6 3
-7 7
-8 4
-9 3
-10 5
-14 7
-15 5
-
--- !test_having_sql --
-test_having CREATE VIEW `test_having` AS select
sum(`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a`) over(partition by
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a` order by
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`pk`) as `c1` ,
`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a` from
`internal`.`regression_test_ddl_p0`.`mal_test_view` group by grouping
sets((`internal`.`regression_test_ddl_p0`.`mal_test_view`.`a`),(`internal`.`regression_test_ddl_p0`.`
[...]
-
--- !complicated_view1 --
-1 100 1
-2 200 1
-
--- !complicated_view1_sql --
-test_view_complicated CREATE VIEW `test_view_complicated` AS SELECT
`internal`.`regression_test_ddl_p0`.`t`.`id`,
`internal`.`regression_test_ddl_p0`.`t`.`value3`, `t`.`row_num` FROM (\n
SELECT `internal`.`regression_test_ddl_p0`.`t1`.`id`,
`internal`.`regression_test_ddl_p0`.`tt`.`value3`, ROW_NUMBER() OVER (PARTITION
BY `internal`.`regression_test_ddl_p0`.`t1`.`id` ORDER BY
`internal`.`regression_test_ddl_p0`.`tt`.`value3` DESC) as `row_num`\n FROM
(SELECT `internal`.`regress [...]
-
--- !nullable --
-1 小区A 10 1 2024-09-01T09:00 2024-09-01T10:00
-2 小区B 11 1 2024-09-01T09:00 2024-09-01T10:00
-3 小区C \N 1 2024-09-01T09:00 2024-09-01T10:00
-
--- !nullable_view_with_cols --
-1 小区A 10 1 2024-09-01T09:00 2024-09-01T10:00
-2 小区B 11 1 2024-09-01T09:00 2024-09-01T10:00
-3 小区C \N 1 2024-09-01T09:00 2024-09-01T10:00
-
diff --git
a/regression-test/suites/ddl_p0/create_view_nereids/create_view_use_mv.groovy
b/regression-test/suites/ddl_p0/create_view_nereids/create_view_use_mv.groovy
new file mode 100644
index 00000000000..295b195aa58
--- /dev/null
+++
b/regression-test/suites/ddl_p0/create_view_nereids/create_view_use_mv.groovy
@@ -0,0 +1,95 @@
+// 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("create_view_use_mv") {
+ sql "drop table if exists orders"
+ sql """create table orders ( o_orderkey bigint null, o_custkey int null,
o_orderstatus varchar(1) null,
+ o_totalprice decimal(15,2) null, o_orderpriority varchar(15) null,
o_clerk varchar(15) null, o_shippriority int null,
+ o_comment varchar(79) null, o_orderdate date not null) engine=olap
duplicate key(o_orderkey,o_custkey)
+ comment 'olap' distributed by hash(o_orderkey) buckets 96
properties("replication_num"="1")"""
+
+ sql "drop table if exists lineitem"
+ sql """create table lineitem (
+ l_orderkey bigint null, l_linenumber int null, l_partkey int null,
l_suppkey int null, l_quantity decimal(15,2) null,l_extendedprice decimal(15,2)
null,
+ l_discount decimal(15,2) null, l_tax decimal(15,2) null,
l_returnflag varchar(1) null, l_linestatus varchar(1) null, l_commitdate date
null, l_receiptdate date null,
+ l_shipnstruct varchar(25) null, l_shipmode varchar(10) null,
l_comment varchar(44) null,l_shipdate date not null) engine=olap
+ duplicate key(l_orderkey, l_linenumber,l_partkey, l_suppkey)
+ distributed by hash(l_orderkey) buckets 96
+ properties("replication_num"="1");"""
+
+ sql """insert into orders values (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy',
'2023-10-17'),(1, null, 'o', 109.2, 'c','d',2, 'mm', '2023-10-17'),
+ (3, 3, null, 99.5, 'a', 'b', 1, 'yy', '2023-10-19'),
+ (1, 2, 'o', null, 'a', 'b', 1, 'yy', '2023-10-20'),
+ (2, 3, 'k', 109.2, null,'d',2, 'mm', '2023-10-21'),
+ (3, 1, 'k', 99.5, 'a', null, 1, 'yy', '2023-10-22'),
+ (1, 3, 'o', 99.5, 'a', 'b', null, 'yy', '2023-10-19'),
+ (2, 1, 'o', 109.2, 'c','d',2, null, '2023-10-18'),
+ (3, 2, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (4, 5, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-19');"""
+
+ sql """insert into lineitem values(null, 1, 2, 3, 5.5, 6.5, 7.5, 8.5, 'o',
'k', '2023-10-17', '2023-10-17', 'a', 'b', 'yyyyyyyyy', '2023-10-17'),(1, null,
3, 1, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-18', '2023-10-18', 'a', 'b',
'yyyyyyyyy', '2023-10-17'),
+ (3, 3, null, 2, 7.5, 8.5, 9.5, 10.5, 'k', 'o', '2023-10-19', '2023-10-19',
'c', 'd', 'xxxxxxxxx', '2023-10-19'),
+ (1, 2, 3, null, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17',
'a', 'b', 'yyyyyyyyy', '2023-10-17'),
+ (2, 3, 2, 1, 5.5, 6.5, 7.5, 8.5, 'o', 'k', null, '2023-10-18', 'a', 'b',
'yyyyyyyyy', '2023-10-18'),
+ (3, 1, 1, 2, 7.5, 8.5, 9.5, 10.5, 'k', 'o', '2023-10-19', null, 'c', 'd',
'xxxxxxxxx', '2023-10-19'),
+ (1, 3, 2, 2, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17',
'a', 'b', 'yyyyyyyyy', '2023-10-17');"""
+
+ createMV("""
+ CREATE MATERIALIZED VIEW t_mv_mv AS select
+ o_orderkey,
+ sum(o_totalprice) as sum_total,
+ max(o_totalprice) as max_total,
+ min(o_totalprice) as min_total,
+ count(*) as count_all,
+ bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey IN (1,
3) then o_custkey else null end)) cnt_1,
+ bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey IN (2)
then o_custkey else null end)) as cnt_2
+ from orders group by o_orderkey
+ """)
+
+ sql "drop view if exists t_mv_v_view"
+ sql """CREATE VIEW t_mv_v_view (k1, k2, k3, k4, k5, k6, v1, v2, v3, v4,
v5, v6) as
+ select `mv_o_orderkey` as k1, `mva_SUM__``o_totalprice``` as k2,
`mva_MAX__``o_totalprice``` as k3,
+ `mva_MIN__``o_totalprice``` as k4, `mva_SUM__CASE WHEN 1 IS NULL THEN 0
ELSE 1 END` as k5, l_orderkey,
+ sum(`mv_o_orderkey`) as sum_total,
+ max(`mv_o_orderkey`) as max_total,
+ min(`mv_o_orderkey`) as min_total,
+ count(`mva_SUM__``o_totalprice```) as count_all,
+ bitmap_union(to_bitmap(case when mv_o_orderkey > 1 then
`mva_SUM__``o_totalprice``` else null end)) cnt_1,
+ bitmap_union(to_bitmap(case when mv_o_orderkey > 2 then
`mva_MAX__``o_totalprice``` else null end)) as cnt_2
+ from orders index t_mv_mv
+ left join lineitem on lineitem.l_orderkey = orders.mv_o_orderkey
+ group by
+ k1, k2, k3, k4, k5, l_orderkey, mv_o_orderkey"""
+ qt_create_view_from_mv "select * from t_mv_v_view order by 1"
+
+ sql "drop view if exists v_for_alter"
+ sql "CREATE VIEW v_for_alter AS SELECT * FROM orders"
+ sql """ALTER VIEW v_for_alter as
+ select `mv_o_orderkey` as k1, `mva_SUM__``o_totalprice``` as k2,
`mva_MAX__``o_totalprice``` as k3,
+ `mva_MIN__``o_totalprice``` as k4, `mva_SUM__CASE WHEN 1 IS NULL THEN 0
ELSE 1 END` as k5, l_orderkey,
+ sum(`mv_o_orderkey`) as sum_total,
+ max(`mv_o_orderkey`) as max_total,
+ min(`mv_o_orderkey`) as min_total,
+ count(`mva_SUM__``o_totalprice```) as count_all,
+ bitmap_union(to_bitmap(case when mv_o_orderkey > 1 then
`mva_SUM__``o_totalprice``` else null end)) cnt_1,
+ bitmap_union(to_bitmap(case when mv_o_orderkey > 2 then
`mva_MAX__``o_totalprice``` else null end)) as cnt_2
+ from orders index t_mv_mv
+ left join lineitem on lineitem.l_orderkey = orders.mv_o_orderkey
+ group by
+ k1, k2, k3, k4, k5, l_orderkey, mv_o_orderkey"""
+ qt_alter_view_from_mv "select * from v_for_alter order by 1"
+}
\ No newline at end of file
diff --git a/regression-test/suites/ddl_p0/test_create_view_nereids.groovy
b/regression-test/suites/ddl_p0/create_view_nereids/test_create_view_nereids.groovy
similarity index 100%
rename from regression-test/suites/ddl_p0/test_create_view_nereids.groovy
rename to
regression-test/suites/ddl_p0/create_view_nereids/test_create_view_nereids.groovy
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
