morningman opened a new issue, #69367: URL: https://github.com/apache/doris/issues/69367
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version 4.1.3 (`doris-4.1.3-rc02-7126cf65d96`, Docker image `apache/doris:all-in-one-4.1.3`). Judging from the code, master (`9e60fab4db0`) behaves the same. ### What's Wrong? `CREATE MATERIALIZED VIEW IF NOT EXISTS` is accepted for a synchronous materialized view, but `IF NOT EXISTS` is ignored. If the view already exists, the statement fails instead of doing nothing: - With the same query as the existing view, the error is `Duplicate column name 'mv_k1'`, which does not say that the view exists. - With a different query, the error is `Materialized view[mv1] already exists`. `IF NOT EXISTS` on an asynchronous materialized view and `CREATE TABLE IF NOT EXISTS` do nothing when the object exists, as expected. The cause seems to be in [`LogicalPlanBuilder`](https://github.com/apache/doris/blob/9e60fab4db00998d490d916c4bf304b15490c1b9/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java#L1666-L1715): - `visitCreateMTMV` sends a statement without `BUILD`, `REFRESH`, `KEY`, or `DISTRIBUTED BY` to `visitCreateSyncMvCommand`. - `visitCreateSyncMvCommand` never reads `ctx.EXISTS()`, and `CreateMaterializedViewCommand` has nothing to hold it. - The asynchronous path passes `ctx.EXISTS() != null` to `CreateMTMVInfo`. ### What You Expected? When a synchronous materialized view with that name already exists on the table, `CREATE MATERIALIZED VIEW IF NOT EXISTS` should succeed and do nothing, as it does for asynchronous materialized views and tables. If `IF NOT EXISTS` is not meant for synchronous views, the parser should reject it. The syntax in the [documentation](https://doris.apache.org/docs/4.x/sql-manual/sql-statements/table-and-view/sync-materialized-view/CREATE-MATERIALIZED-VIEW) is `CREATE MATERIALIZED VIEW <materialized_view_name> [AS] <query>`, without `IF NOT EXISTS`. Accepting the clause and then ignoring it is the confusing part. ### How to Reproduce? One FE and one BE (the all-in-one image), default configuration. ```sql CREATE DATABASE IF NOT EXISTS repro; USE repro; CREATE TABLE t (k1 INT, k2 INT, v1 BIGINT) DUPLICATE KEY(k1) DISTRIBUTED BY HASH(k1) BUCKETS 1 PROPERTIES ("replication_num" = "1"); INSERT INTO t VALUES (1, 1, 10), (2, 2, 20); CREATE MATERIALIZED VIEW mv1 AS SELECT k1 AS mv_k1, SUM(v1) AS mv_sum_v1 FROM t GROUP BY k1; -- Wait until SHOW ALTER TABLE MATERIALIZED VIEW FROM repro shows FINISHED. CREATE MATERIALIZED VIEW IF NOT EXISTS mv1 AS SELECT k1 AS mv_k1, SUM(v1) AS mv_sum_v1 FROM t GROUP BY k1; -- ERROR 1105 (HY000): errCode = 2, detailMessage = Duplicate column name 'mv_k1' CREATE MATERIALIZED VIEW IF NOT EXISTS mv1 AS SELECT k2 AS mv_k2, SUM(v1) AS mv_sum_v1_2 FROM t GROUP BY k2; -- ERROR 1105 (HY000): errCode = 2, detailMessage = Materialized view[mv1] already exists ``` For comparison, on the same table: ```sql -- Query OK both times CREATE MATERIALIZED VIEW IF NOT EXISTS amv1 BUILD IMMEDIATE REFRESH AUTO ON MANUAL DISTRIBUTED BY RANDOM BUCKETS 1 PROPERTIES ("replication_num" = "1") AS SELECT k1, SUM(v1) AS s FROM t GROUP BY k1; -- Query OK CREATE TABLE IF NOT EXISTS t (k1 INT) DUPLICATE KEY(k1) DISTRIBUTED BY HASH(k1) BUCKETS 1 PROPERTIES ("replication_num" = "1"); ``` ### Anything Else? We found this while writing a lab that learners may run more than once. To make it rerunnable, the lab now runs `DROP MATERIALIZED VIEW IF EXISTS mv1 ON t` first, so the view is rebuilt on every run. ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
