You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by ma...@apache.org on 2025/01/07 06:21:48 UTC
(cloudberry) 07/11: Fix syntax error with CREATE MATERIALIZED VIEW
This is an automated email from the ASF dual-hosted git repository.
maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 9581fa61b75dec8ef8823303b1dfb4ac4afe2f74
Author: Brent Doil <bd...@vmware.com>
AuthorDate: Fri Feb 3 14:07:27 2023 -0500
Fix syntax error with CREATE MATERIALIZED VIEW
Allow DISTRIBUTED BY when using the CREATE MATERIALIZED VIEW
IF NOT EXISTS SYNTAX.
Resolves error:
CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest AS SELECT * FROM mvtest_tvvmv DISTRIBUTED RANDOMLY;
ERROR: syntax error at or near "DISTRIBUTED"
Update matview regress tests to test `IF NOT EXISTS` with `DISTRIBUTED BY`
Authored-by: Brent Doil <bd...@vmware.com>
---
src/test/regress/expected/matview.out | 4 ++--
src/test/regress/expected/matview_ao.out | 6 +++---
src/test/regress/expected/matview_optimizer.out | 4 ++--
src/test/regress/sql/matview.sql | 4 ++--
src/test/regress/sql/matview_ao.sql | 6 +++---
5 files changed, 12 insertions(+), 12 deletions(-)
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 55e16a03f3..f0b1ad9c3d 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -19,7 +19,7 @@ SELECT * FROM mvtest_tv ORDER BY type;
-- create a materialized view with no data, and confirm correct behavior
EXPLAIN (costs off)
- CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA distributed by(type);
+ CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA distributed by(type);
QUERY PLAN
------------------------------------------------------
HashAggregate
@@ -30,7 +30,7 @@ EXPLAIN (costs off)
Optimizer: Postgres query optimizer
(6 rows)
-CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA distributed by(type);
+CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA distributed by(type);
SELECT relispopulated FROM pg_class WHERE oid = 'mvtest_tm'::regclass;
relispopulated
----------------
diff --git a/src/test/regress/expected/matview_ao.out b/src/test/regress/expected/matview_ao.out
index b194122459..7999cf2990 100644
--- a/src/test/regress/expected/matview_ao.out
+++ b/src/test/regress/expected/matview_ao.out
@@ -7,7 +7,7 @@ INSERT INTO t_matview_ao VALUES
(3, 'y', 5),
(4, 'y', 7),
(5, 'z', 11);
-CREATE MATERIALIZED VIEW m_heap AS SELECT type, sum(amt) AS totamt FROM t_matview_ao GROUP BY type WITH NO DATA distributed by(type);
+CREATE MATERIALIZED VIEW IF NOT EXISTS m_heap AS SELECT type, sum(amt) AS totamt FROM t_matview_ao GROUP BY type WITH NO DATA distributed by(type);
CREATE UNIQUE INDEX m_heap_index ON m_heap(type);
SELECT * from m_heap;
ERROR: materialized view "m_heap" has not been populated
@@ -54,7 +54,7 @@ SELECT * FROM m_heap;
z | 11
(3 rows)
-CREATE MATERIALIZED VIEW m_ao with (appendonly=true) AS SELECT type, sum(amt) AS totamt FROM t_matview_ao GROUP BY type WITH NO DATA distributed by(type);
+CREATE MATERIALIZED VIEW IF NOT EXISTS m_ao with (appendonly=true) AS SELECT type, sum(amt) AS totamt FROM t_matview_ao GROUP BY type WITH NO DATA distributed by(type);
SELECT * from m_ao;
ERROR: materialized view "m_ao" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.
@@ -80,7 +80,7 @@ SELECT * FROM m_ao;
z | 11
(3 rows)
-CREATE MATERIALIZED VIEW m_aocs with (appendonly=true, orientation=column) AS SELECT type, sum(amt) AS totamt FROM t_matview_ao GROUP BY type WITH NO DATA distributed by(type);
+CREATE MATERIALIZED VIEW IF NOT EXISTS m_aocs with (appendonly=true, orientation=column) AS SELECT type, sum(amt) AS totamt FROM t_matview_ao GROUP BY type WITH NO DATA distributed by(type);
SELECT * from m_aocs;
ERROR: materialized view "m_aocs" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.
diff --git a/src/test/regress/expected/matview_optimizer.out b/src/test/regress/expected/matview_optimizer.out
index c54b1943ec..a0c61de98d 100644
--- a/src/test/regress/expected/matview_optimizer.out
+++ b/src/test/regress/expected/matview_optimizer.out
@@ -19,7 +19,7 @@ SELECT * FROM mvtest_tv ORDER BY type;
-- create a materialized view with no data, and confirm correct behavior
EXPLAIN (costs off)
- CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA distributed by(type);
+ CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA distributed by(type);
QUERY PLAN
------------------------------------------------------------
GroupAggregate
@@ -32,7 +32,7 @@ EXPLAIN (costs off)
Optimizer: Pivotal Optimizer (GPORCA) version 3.83.0
(8 rows)
-CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA distributed by(type);
+CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA distributed by(type);
SELECT relispopulated FROM pg_class WHERE oid = 'mvtest_tm'::regclass;
relispopulated
----------------
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index 76739f281a..ead969f8e4 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -14,8 +14,8 @@ SELECT * FROM mvtest_tv ORDER BY type;
-- create a materialized view with no data, and confirm correct behavior
EXPLAIN (costs off)
- CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA distributed by(type);
-CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA distributed by(type);
+ CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA distributed by(type);
+CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA distributed by(type);
SELECT relispopulated FROM pg_class WHERE oid = 'mvtest_tm'::regclass;
SELECT * FROM mvtest_tm ORDER BY type;
REFRESH MATERIALIZED VIEW mvtest_tm;
diff --git a/src/test/regress/sql/matview_ao.sql b/src/test/regress/sql/matview_ao.sql
index c3d072f288..412017c82c 100644
--- a/src/test/regress/sql/matview_ao.sql
+++ b/src/test/regress/sql/matview_ao.sql
@@ -8,7 +8,7 @@ INSERT INTO t_matview_ao VALUES
(4, 'y', 7),
(5, 'z', 11);
-CREATE MATERIALIZED VIEW m_heap AS SELECT type, sum(amt) AS totamt FROM t_matview_ao GROUP BY type WITH NO DATA distributed by(type);
+CREATE MATERIALIZED VIEW IF NOT EXISTS m_heap AS SELECT type, sum(amt) AS totamt FROM t_matview_ao GROUP BY type WITH NO DATA distributed by(type);
CREATE UNIQUE INDEX m_heap_index ON m_heap(type);
SELECT * from m_heap;
REFRESH MATERIALIZED VIEW CONCURRENTLY m_heap;
@@ -23,7 +23,7 @@ select relispopulated from gp_dist_random('pg_class') where relname = 'm_heap';
REFRESH MATERIALIZED VIEW m_heap;
SELECT * FROM m_heap;
-CREATE MATERIALIZED VIEW m_ao with (appendonly=true) AS SELECT type, sum(amt) AS totamt FROM t_matview_ao GROUP BY type WITH NO DATA distributed by(type);
+CREATE MATERIALIZED VIEW IF NOT EXISTS m_ao with (appendonly=true) AS SELECT type, sum(amt) AS totamt FROM t_matview_ao GROUP BY type WITH NO DATA distributed by(type);
SELECT * from m_ao;
REFRESH MATERIALIZED VIEW m_ao;
SELECT * FROM m_ao;
@@ -33,7 +33,7 @@ REFRESH MATERIALIZED VIEW m_ao;
SELECT * FROM m_ao;
-CREATE MATERIALIZED VIEW m_aocs with (appendonly=true, orientation=column) AS SELECT type, sum(amt) AS totamt FROM t_matview_ao GROUP BY type WITH NO DATA distributed by(type);
+CREATE MATERIALIZED VIEW IF NOT EXISTS m_aocs with (appendonly=true, orientation=column) AS SELECT type, sum(amt) AS totamt FROM t_matview_ao GROUP BY type WITH NO DATA distributed by(type);
SELECT * from m_aocs;
REFRESH MATERIALIZED VIEW m_aocs;
SELECT * FROM m_aocs;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org