src/test/regress/expected/incremental_matview.out

-- create a table to use as a basis for views and materialized views in various combinations
CREATE TABLE mv_base_a (i int, j int) DISTRIBUTED BY (i);
INSERT INTO mv_base_a VALUES
  (1,10),
  (2,20),
  (3,30),
  (4,40),
  (5,50);
CREATE TABLE mv_base_b (i int, k int) DISTRIBUTED BY (i);
INSERT INTO mv_base_b VALUES
  (1,101),
  (2,102),
  (3,103),
  (4,104);
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_1 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) WITH NO DATA;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
ERROR:  materialized view "mv_ivm_1" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.
REFRESH MATERIALIZED VIEW mv_ivm_1;
SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
 i | j  |  k  
---+----+-----
 1 | 10 | 101
 2 | 20 | 102
 3 | 30 | 103
 4 | 40 | 104
(4 rows)

-- REFRESH WITH NO DATA
BEGIN;
CREATE FUNCTION dummy_ivm_trigger_func() RETURNS TRIGGER AS $$
  BEGIN
    RETURN NULL;
  END
$$ language plpgsql;
CREATE CONSTRAINT TRIGGER dummy_ivm_trigger AFTER INSERT
ON mv_base_a FROM mv_ivm_1 FOR EACH ROW
EXECUTE PROCEDURE dummy_ivm_trigger_func();
SELECT COUNT(*)
FROM pg_depend pd INNER JOIN pg_trigger pt ON pd.objid = pt.oid
WHERE pd.classid = 'pg_trigger'::regclass AND pd.refobjid = 'mv_ivm_1'::regclass;
 count 
-------
    17
(1 row)

REFRESH MATERIALIZED VIEW mv_ivm_1 WITH NO DATA;
SELECT COUNT(*)
FROM pg_depend pd INNER JOIN pg_trigger pt ON pd.objid = pt.oid
WHERE pd.classid = 'pg_trigger'::regclass AND pd.refobjid = 'mv_ivm_1'::regclass;
 count 
-------
     1
(1 row)

ROLLBACK;
-- immediate maintenance
BEGIN;
INSERT INTO mv_base_b VALUES(5,105);
SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
 i | j  |  k  
---+----+-----
 1 | 10 | 101
 2 | 20 | 102
 3 | 30 | 103
 4 | 40 | 104
 5 | 50 | 105
(5 rows)

UPDATE mv_base_a SET j = 0 WHERE i = 1;
SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
 i | j  |  k  
---+----+-----
 1 |  0 | 101
 2 | 20 | 102
 3 | 30 | 103
 4 | 40 | 104
 5 | 50 | 105
(5 rows)

DELETE FROM mv_base_b WHERE (i,k) = (5,105);
SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
 i | j  |  k  
---+----+-----
 1 |  0 | 101
 2 | 20 | 102
 3 | 30 | 103
 4 | 40 | 104
(4 rows)

ROLLBACK;
SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
 i | j  |  k  
---+----+-----
 1 | 10 | 101
 2 | 20 | 102
 3 | 30 | 103
 4 | 40 | 104
(4 rows)

-- rename of IVM columns
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_rename AS SELECT DISTINCT * FROM mv_base_a;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
ALTER MATERIALIZED VIEW mv_ivm_rename RENAME COLUMN __ivm_count__ TO xxx;
ERROR:  IVM column can not be renamed
DROP MATERIALIZED VIEW mv_ivm_rename;
-- unique index on IVM columns
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_unique AS SELECT DISTINCT * FROM mv_base_a;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE UNIQUE INDEX ON mv_ivm_unique(__ivm_count__);
ERROR:  unique index creation on IVM columns is not supported
CREATE UNIQUE INDEX ON mv_ivm_unique((__ivm_count__));
ERROR:  unique index creation on IVM columns is not supported
CREATE UNIQUE INDEX ON mv_ivm_unique((__ivm_count__ + 1));
ERROR:  unique index creation on IVM columns is not supported
DROP MATERIALIZED VIEW mv_ivm_unique;
-- TRUNCATE a base table in join views
BEGIN;
TRUNCATE mv_base_a;
SELECT * FROM mv_ivm_1;
 i | j | k 
---+---+---
(0 rows)

ROLLBACK;
BEGIN;
TRUNCATE mv_base_b;
SELECT * FROM mv_ivm_1;
 i | j | k 
---+---+---
(0 rows)

ROLLBACK;
-- some query syntax
BEGIN;
CREATE FUNCTION ivm_func() RETURNS int LANGUAGE 'sql'
       AS 'SELECT 1' IMMUTABLE;
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_func AS SELECT * FROM ivm_func();
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'ivm_func' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_no_tbl AS SELECT 1;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named '?column?' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
ROLLBACK;
-- result of materialized view have DISTINCT clause or the duplicate result.
BEGIN;
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_duplicate AS SELECT j FROM mv_base_a;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'j' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_distinct AS SELECT DISTINCT j FROM mv_base_a;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'j' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO mv_base_a VALUES(6,20);
SELECT * FROM mv_ivm_duplicate ORDER BY 1;
 j  
----
 10
 20
 20
 30
 40
 50
(6 rows)

SELECT * FROM mv_ivm_distinct ORDER BY 1;
 j  
----
 10
 20
 30
 40
 50
(5 rows)

DELETE FROM mv_base_a WHERE (i,j) = (2,20);
SELECT * FROM mv_ivm_duplicate ORDER BY 1;
 j  
----
 10
 20
 30
 40
 50
(5 rows)

SELECT * FROM mv_ivm_distinct ORDER BY 1;
 j  
----
 10
 20
 30
 40
 50
(5 rows)

ROLLBACK;
-- support SUM(), COUNT() and AVG() aggregate functions
BEGIN;
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(i), AVG(j) FROM mv_base_a GROUP BY i;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
 i | sum | count |         avg         
---+-----+-------+---------------------
 1 |  10 |     1 | 10.0000000000000000
 2 |  20 |     1 | 20.0000000000000000
 3 |  30 |     1 | 30.0000000000000000
 4 |  40 |     1 | 40.0000000000000000
 5 |  50 |     1 | 50.0000000000000000
(5 rows)

INSERT INTO mv_base_a VALUES(2,100);
SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
 i | sum | count |         avg         
---+-----+-------+---------------------
 1 |  10 |     1 | 10.0000000000000000
 2 | 120 |     2 | 60.0000000000000000
 3 |  30 |     1 | 30.0000000000000000
 4 |  40 |     1 | 40.0000000000000000
 5 |  50 |     1 | 50.0000000000000000
(5 rows)

UPDATE mv_base_a SET j = 200 WHERE (i,j) = (2,100);
SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
 i | sum | count |         avg          
---+-----+-------+----------------------
 1 |  10 |     1 |  10.0000000000000000
 2 | 220 |     2 | 110.0000000000000000
 3 |  30 |     1 |  30.0000000000000000
 4 |  40 |     1 |  40.0000000000000000
 5 |  50 |     1 |  50.0000000000000000
(5 rows)

DELETE FROM mv_base_a WHERE (i,j) = (2,200);
SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
 i | sum | count |         avg         
---+-----+-------+---------------------
 1 |  10 |     1 | 10.0000000000000000
 2 |  20 |     1 | 20.0000000000000000
 3 |  30 |     1 | 30.0000000000000000
 4 |  40 |     1 | 40.0000000000000000
 5 |  50 |     1 | 50.0000000000000000
(5 rows)

ROLLBACK;
-- support COUNT(*) aggregate function
BEGIN;
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT * FROM mv_ivm_agg ORDER BY 1,2,3;
 i | sum | count 
---+-----+-------
 1 |  10 |     1
 2 |  20 |     1
 3 |  30 |     1
 4 |  40 |     1
 5 |  50 |     1
(5 rows)

INSERT INTO mv_base_a VALUES(2,100);
SELECT * FROM mv_ivm_agg ORDER BY 1,2,3;
 i | sum | count 
---+-----+-------
 1 |  10 |     1
 2 | 120 |     2
 3 |  30 |     1
 4 |  40 |     1
 5 |  50 |     1
(5 rows)

ROLLBACK;
-- TRUNCATE a base table in aggregate views
BEGIN;
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
TRUNCATE mv_base_a;
SELECT sum, count FROM mv_ivm_agg;
 sum | count 
-----+-------
(0 rows)

SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i;
 i | sum | count 
---+-----+-------
(0 rows)

ROLLBACK;
-- support aggregate functions without GROUP clause
BEGIN;
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_group AS SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'sum' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT * FROM mv_ivm_group ORDER BY 1;
 sum | count |         avg         
-----+-------+---------------------
 150 |     5 | 30.0000000000000000
(1 row)

INSERT INTO mv_base_a VALUES(6,60);
SELECT * FROM mv_ivm_group ORDER BY 1;
 sum | count |         avg         
-----+-------+---------------------
 210 |     6 | 35.0000000000000000
(1 row)

DELETE FROM mv_base_a;
SELECT * FROM mv_ivm_group ORDER BY 1;
 sum | count | avg 
-----+-------+-----
     |     0 |    
(1 row)

ROLLBACK;
-- TRUNCATE a base table in aggregate views without GROUP clause
BEGIN;
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_group AS SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'sum' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
TRUNCATE mv_base_a;
SELECT sum, count, avg FROM mv_ivm_group;
 sum | count | avg 
-----+-------+-----
     |     0 |    
(1 row)

SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a;
 sum | count | avg 
-----+-------+-----
     |     0 |    
(1 row)

ROLLBACK;
-- TRUNCATE a base table without transaction block.
CREATE TABLE mv_base_simple(i int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_simple AS SELECT * FROM mv_base_simple;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
TRUNCATE mv_base_simple;
DROP TABLE mv_base_simple CASCADE;
NOTICE:  drop cascades to materialized view mv_ivm_simple
-- resolved issue: When use AVG() function and values is indivisible, result of AVG() is incorrect.
BEGIN;
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_avg_bug AS SELECT i, SUM(j), COUNT(j), AVG(j) FROM mv_base_A GROUP BY i;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;
 i | sum | count |         avg         
---+-----+-------+---------------------
 1 |  10 |     1 | 10.0000000000000000
 2 |  20 |     1 | 20.0000000000000000
 3 |  30 |     1 | 30.0000000000000000
 4 |  40 |     1 | 40.0000000000000000
 5 |  50 |     1 | 50.0000000000000000
(5 rows)

INSERT INTO mv_base_a VALUES
  (1,0),
  (1,0),
  (2,30),
  (2,30);
SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;
 i | sum | count |         avg         
---+-----+-------+---------------------
 1 |  10 |     3 |  3.3333333333333333
 2 |  80 |     3 | 26.6666666666666667
 3 |  30 |     1 | 30.0000000000000000
 4 |  40 |     1 | 40.0000000000000000
 5 |  50 |     1 | 50.0000000000000000
(5 rows)

DELETE FROM mv_base_a WHERE (i,j) = (1,0);
DELETE FROM mv_base_a WHERE (i,j) = (2,30);
SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;
 i | sum | count |         avg         
---+-----+-------+---------------------
 1 |  10 |     1 | 10.0000000000000000
 2 |  20 |     1 | 20.0000000000000000
 3 |  30 |     1 | 30.0000000000000000
 4 |  40 |     1 | 40.0000000000000000
 5 |  50 |     1 | 50.0000000000000000
(5 rows)

ROLLBACK;
-- aggregate views with column names specified
BEGIN;
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO mv_base_a VALUES (1,100), (2,200), (3,300);
UPDATE mv_base_a SET j = 2000 WHERE (i,j) = (2,20);
DELETE FROM mv_base_a WHERE (i,j) = (3,30);
SELECT * FROM mv_ivm_agg ORDER BY 1,2;
 a | sum  
---+------
 1 |  110
 2 | 2200
 3 |  300
 4 |   40
 5 |   50
(5 rows)

ROLLBACK;
BEGIN;
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a,b) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO mv_base_a VALUES (1,100), (2,200), (3,300);
UPDATE mv_base_a SET j = 2000 WHERE (i,j) = (2,20);
DELETE FROM mv_base_a WHERE (i,j) = (3,30);
SELECT * FROM mv_ivm_agg ORDER BY 1,2;
 a |  b   
---+------
 1 |  110
 2 | 2200
 3 |  300
 4 |   40
 5 |   50
(5 rows)

ROLLBACK;
BEGIN;
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a,b,c) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
ERROR:  too many column names were specified
ROLLBACK;
-- support self join view and multiple change on the same table
BEGIN;
CREATE TABLE base_t (i int, v int) DISTRIBUTED BY (i);
INSERT INTO base_t VALUES (1, 10), (2, 20), (3, 30);
CREATE INCREMENTAL MATERIALIZED VIEW mv_self(v1, v2) AS
 SELECT t1.v, t2.v FROM base_t AS t1 JOIN base_t AS t2 ON t1.i = t2.i;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'v' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT * FROM mv_self ORDER BY v1;
 v1 | v2 
----+----
 10 | 10
 20 | 20
 30 | 30
(3 rows)

INSERT INTO base_t VALUES (4,40);
DELETE FROM base_t WHERE i = 1;
UPDATE base_t SET v = v*10 WHERE i=2;
SELECT * FROM mv_self ORDER BY v1;
 v1  | v2  
-----+-----
  30 |  30
  40 |  40
 200 | 200
(3 rows)

--- with sub-transactions
SAVEPOINT p1;
INSERT INTO base_t VALUES (7,70);
RELEASE SAVEPOINT p1;
INSERT INTO base_t VALUES (7,77);
SELECT * FROM mv_self ORDER BY v1, v2;
 v1  | v2  
-----+-----
  30 |  30
  40 |  40
  70 |  70
  70 |  77
  77 |  70
  77 |  77
 200 | 200
(7 rows)

ROLLBACK;
-- views including NULL
BEGIN;
CREATE TABLE base_t (i int, v int) DISTRIBUTED BY (i);
INSERT INTO base_t VALUES (1,10),(2, NULL);
CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base_t;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT * FROM mv ORDER BY i;
 i | v  
---+----
 1 | 10
 2 |   
(2 rows)

UPDATE base_t SET v = 20 WHERE i = 2;
SELECT * FROM mv ORDER BY i;
 i | v  
---+----
 1 | 10
 2 | 20
(2 rows)

ROLLBACK;
BEGIN;
CREATE TABLE base_t (i int) DISTRIBUTED BY (i);
CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base_t;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT * FROM mv ORDER BY i;
 i 
---
(0 rows)

INSERT INTO base_t VALUES (1),(NULL);
SELECT * FROM mv ORDER BY i;
 i 
---
 1
  
(2 rows)

ROLLBACK;
BEGIN;
CREATE TABLE base_t (i int, v int) DISTRIBUTED BY (i);
INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (1, 10), (1, 20);
CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT i, sum(v) FROM base_t GROUP BY i;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT * FROM mv ORDER BY i;
 i | sum 
---+-----
 1 |  30
   |   3
(2 rows)

UPDATE base_t SET v = v * 10;
SELECT * FROM mv ORDER BY i;
 i | sum 
---+-----
 1 | 300
   |  30
(2 rows)

ROLLBACK;
-- support joins
BEGIN;
CREATE TABLE base_r(i int) DISTRIBUTED BY (i);
CREATE TABLE base_s (i int, j int) DISTRIBUTED BY (i);
CREATE TABLE base_t (j int) DISTRIBUTED BY (j);
INSERT INTO base_r VALUES (1), (2), (3), (3);
INSERT INTO base_s VALUES (2,1), (2,2), (3,1), (4,1), (4,2);
INSERT INTO base_t VALUES (2), (3), (3);
CREATE FUNCTION is_match() RETURNS text AS $$
DECLARE
x text;
BEGIN
 EXECUTE
 'SELECT CASE WHEN count(*) = 0 THEN ''OK'' ELSE ''NG'' END FROM (
	SELECT * FROM (SELECT * FROM mv EXCEPT ALL SELECT * FROM v) v1
	UNION ALL
 SELECT * FROM (SELECT * FROM v EXCEPT ALL SELECT * FROM mv) v2
 ) v' INTO x;
 RETURN x;
END;
$$ LANGUAGE plpgsql;
-- 3-way join
CREATE INCREMENTAL MATERIALIZED VIEW mv(r, si, sj, t) AS
 SELECT r.i, s.i, s.j, t.j
   FROM base_r AS r JOIN base_s AS s ON r.i=s.i JOIN base_t AS t ON s.j=t.j;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE VIEW v(r, si, sj, t) AS
 SELECT r.i, s.i, s.j, t.j
   FROM base_r AS r JOIN base_s AS s ON r.i=s.i JOIN base_t AS t ON s.j=t.j;
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
 2 |  2 |  2 | 2
(1 row)

SAVEPOINT p1;
INSERT INTO base_r VALUES (1),(2),(3);
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
 2 |  2 |  2 | 2
 2 |  2 |  2 | 2
(2 rows)

SELECT is_match();
 is_match 
----------
 OK
(1 row)

INSERT INTO base_r VALUES (4),(5);
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
 2 |  2 |  2 | 2
 2 |  2 |  2 | 2
 4 |  4 |  2 | 2
(3 rows)

SELECT is_match();
 is_match 
----------
 OK
(1 row)

ROLLBACK TO p1;
INSERT INTO base_s VALUES (1,3);
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
 1 |  1 |  3 | 3
 1 |  1 |  3 | 3
 2 |  2 |  2 | 2
(3 rows)

SELECT is_match();
 is_match 
----------
 OK
(1 row)

INSERT INTO base_s VALUES (2,3);
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
 1 |  1 |  3 | 3
 1 |  1 |  3 | 3
 2 |  2 |  2 | 2
 2 |  2 |  3 | 3
 2 |  2 |  3 | 3
(5 rows)

SELECT is_match();
 is_match 
----------
 OK
(1 row)

ROLLBACK TO p1;
INSERT INTO base_t VALUES (1),(2);
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
 2 |  2 |  1 | 1
 2 |  2 |  2 | 2
 2 |  2 |  2 | 2
 3 |  3 |  1 | 1
 3 |  3 |  1 | 1
(5 rows)

SELECT is_match();
 is_match 
----------
 OK
(1 row)

INSERT INTO base_t VALUES (3),(4);
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
 2 |  2 |  1 | 1
 2 |  2 |  2 | 2
 2 |  2 |  2 | 2
 3 |  3 |  1 | 1
 3 |  3 |  1 | 1
(5 rows)

SELECT is_match();
 is_match 
----------
 OK
(1 row)

ROLLBACK TO p1;
DELETE FROM base_r WHERE i=1;
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
 2 |  2 |  2 | 2
(1 row)

SELECT is_match();
 is_match 
----------
 OK
(1 row)

DELETE FROM base_r WHERE i=2;
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
(0 rows)

SELECT is_match();
 is_match 
----------
 OK
(1 row)

DELETE FROM base_r WHERE i=3;
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
(0 rows)

SELECT is_match();
 is_match 
----------
 OK
(1 row)

ROLLBACK TO p1;
DELETE FROM base_s WHERE i=2;
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
(0 rows)

SELECT is_match();
 is_match 
----------
 OK
(1 row)

DELETE FROM base_s WHERE i=3;
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
(0 rows)

SELECT is_match();
 is_match 
----------
 OK
(1 row)

DELETE FROM base_s WHERE i=4;
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
(0 rows)

SELECT is_match();
 is_match 
----------
 OK
(1 row)

ROLLBACK TO p1;
DELETE FROM base_t WHERE j=2;
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
(0 rows)

SELECT is_match();
 is_match 
----------
 OK
(1 row)

DELETE FROM base_t WHERE j=3;
SELECT * FROM mv ORDER BY r, si, sj, t;
 r | si | sj | t 
---+----+----+---
(0 rows)

SELECT is_match();
 is_match 
----------
 OK
(1 row)

ROLLBACK TO p1;
-- TRUNCATE a base table in views
TRUNCATE base_r;
SELECT is_match();
 is_match 
----------
 OK
(1 row)

ROLLBACK TO p1;
TRUNCATE base_s;
SELECT is_match();
 is_match 
----------
 OK
(1 row)

ROLLBACK TO p1;
TRUNCATE base_t;
SELECT is_match();
 is_match 
----------
 OK
(1 row)

ROLLBACK TO p1;
DROP MATERIALIZED VIEW mv;
DROP VIEW v;
ROLLBACK;
-- IMMV containing user defined type
BEGIN;
CREATE TYPE mytype;
CREATE FUNCTION mytype_in(cstring)
 RETURNS mytype AS 'int4in'
 LANGUAGE INTERNAL STRICT IMMUTABLE;
NOTICE:  return type mytype is only a shell
CREATE FUNCTION mytype_out(mytype)
 RETURNS cstring AS 'int4out'
 LANGUAGE INTERNAL STRICT IMMUTABLE;
NOTICE:  argument type mytype is only a shell
CREATE TYPE mytype (
 LIKE = int4,
 INPUT = mytype_in,
 OUTPUT = mytype_out
);
CREATE FUNCTION mytype_eq(mytype, mytype)
 RETURNS bool AS 'int4eq'
 LANGUAGE INTERNAL STRICT IMMUTABLE;
CREATE FUNCTION mytype_lt(mytype, mytype)
 RETURNS bool AS 'int4lt'
 LANGUAGE INTERNAL STRICT IMMUTABLE;
CREATE FUNCTION mytype_cmp(mytype, mytype)
 RETURNS integer AS 'btint4cmp'
 LANGUAGE INTERNAL STRICT IMMUTABLE;
CREATE OPERATOR = (
 leftarg = mytype, rightarg = mytype,
 procedure = mytype_eq);
CREATE OPERATOR < (
 leftarg = mytype, rightarg = mytype,
 procedure = mytype_lt);
CREATE OPERATOR CLASS mytype_ops
 DEFAULT FOR TYPE mytype USING btree AS
 OPERATOR        1       <,
 OPERATOR        3       = ,
 FUNCTION   1    mytype_cmp(mytype,mytype);
CREATE TABLE t_mytype (x mytype);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause, and no column type is suitable for a distribution key. Creating a NULL policy entry.
CREATE INCREMENTAL MATERIALIZED VIEW mv_mytype AS
 SELECT * FROM t_mytype;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named '' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO t_mytype VALUES ('1'::mytype);
SELECT * FROM mv_mytype;
 x 
---
 1
(1 row)

ROLLBACK;
-- outer join is not supported
CREATE INCREMENTAL MATERIALIZED VIEW mv(a,b) AS SELECT a.i, b.i FROM mv_base_a a LEFT JOIN mv_base_b b ON a.i=b.i;
ERROR:  OUTER JOIN is not supported on incrementally maintainable materialized view
-- CTE is not supported
CREATE INCREMENTAL MATERIALIZED VIEW mv AS
    WITH b AS ( SELECT * FROM mv_base_b) SELECT a.i,a.j FROM mv_base_a a, b WHERE a.i = b.i;
ERROR:  CTE is not supported on incrementally maintainable materialized view
-- contain system column
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm01 AS SELECT i,j,xmin FROM mv_base_a;
ERROR:  system column is not supported on incrementally maintainable materialized view
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm02 AS SELECT i,j FROM mv_base_a WHERE xmin = '610';
ERROR:  system column is not supported on incrementally maintainable materialized view
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm04 AS SELECT i,j,xmin::text AS x_min FROM mv_base_a;
ERROR:  system column is not supported on incrementally maintainable materialized view
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm06 AS SELECT i,j,xidsend(xmin) AS x_min FROM mv_base_a;
ERROR:  system column is not supported on incrementally maintainable materialized view
-- contain subquery
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm03 AS SELECT i,j FROM mv_base_a WHERE i IN (SELECT i FROM mv_base_b WHERE k < 103 );
ERROR:  subquery is not supported on incrementally maintainable materialized view
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm04 AS SELECT a.i,a.j FROM mv_base_a a, (SELECT * FROM mv_base_b) b WHERE a.i = b.i;
ERROR:  subquery is not supported on incrementally maintainable materialized view
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm05 AS SELECT i,j, (SELECT k FROM mv_base_b b WHERE a.i = b.i) FROM mv_base_a a;
ERROR:  subquery is not supported on incrementally maintainable materialized view
-- contain ORDER BY
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm07 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) ORDER BY i,j,k;
ERROR:  ORDER BY clause is not supported on incrementally maintainable materialized view
-- contain HAVING
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm08 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) GROUP BY i,j,k HAVING SUM(i) > 5;
ERROR:  HAVING clause is not supported on incrementally maintainable materialized view
-- contain view or materialized view
CREATE VIEW b_view AS SELECT i,k FROM mv_base_b;
CREATE MATERIALIZED VIEW b_mview AS SELECT i,k FROM mv_base_b;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm07 AS SELECT a.i,a.j FROM mv_base_a a,b_view b WHERE a.i = b.i;
ERROR:  VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm08 AS SELECT a.i,a.j FROM mv_base_a a,b_mview b WHERE a.i = b.i;
ERROR:  VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm09 AS SELECT a.i,a.j FROM mv_base_a a, (SELECT i, COUNT(*) FROM mv_base_b GROUP BY i) b WHERE a.i = b.i;
ERROR:  subquery is not supported on incrementally maintainable materialized view
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm10 AS SELECT a.i,a.j FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i) OR a.i > 5;
ERROR:  subquery is not supported on incrementally maintainable materialized view
-- contain mutable functions
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm12 AS SELECT i,j FROM mv_base_a WHERE i = random()::int;
ERROR:  mutable function is not supported on incrementally maintainable materialized view
HINT:  functions must be marked IMMUTABLE
-- LIMIT/OFFSET is not supported
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm13 AS SELECT i,j FROM mv_base_a LIMIT 10 OFFSET 5;
ERROR:  LIMIT/OFFSET clause is not supported on incrementally maintainable materialized view
-- DISTINCT ON is not supported
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm14 AS SELECT DISTINCT ON(i) i, j FROM mv_base_a;
ERROR:  DISTINCT ON is not supported on incrementally maintainable materialized view
-- TABLESAMPLE clause is not supported
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm15 AS SELECT i, j FROM mv_base_a TABLESAMPLE SYSTEM(50);
ERROR:  TABLESAMPLE clause is not supported on incrementally maintainable materialized view
-- window functions are not supported
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm16 AS SELECT *, cume_dist() OVER (ORDER BY i) AS rank FROM mv_base_a;
ERROR:  window functions are not supported on incrementally maintainable materialized view
-- aggregate function with some options is not supported
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm17 AS SELECT COUNT(*) FILTER(WHERE i < 3) FROM mv_base_a;
ERROR:  aggregate function with FILTER clause is not supported on incrementally maintainable materialized view
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm18 AS SELECT COUNT(DISTINCT i)  FROM mv_base_a;
ERROR:  aggregate function with DISTINCT arguments is not supported on incrementally maintainable materialized view
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm19 AS SELECT array_agg(j ORDER BY i DESC) FROM mv_base_a;
ERROR:  aggregate function with ORDER clause is not supported on incrementally maintainable materialized view
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm20 AS SELECT i,SUM(j) FROM mv_base_a GROUP BY GROUPING SETS((i),());
ERROR:  GROUPING SETS, ROLLUP, or CUBE clauses is not supported on incrementally maintainable materialized view
-- inheritance parent is not supported
BEGIN;
CREATE TABLE parent (i int, v int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE child_a(options text) INHERITS(parent);
NOTICE:  table has parent, setting distribution columns to match parent table
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm21 AS SELECT * FROM parent;
ERROR:  inheritance parent is not supported on incrementally maintainable materialized view
ROLLBACK;
-- UNION statement is not supported
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm22 AS SELECT i,j FROM mv_base_a UNION ALL SELECT i,k FROM mv_base_b;;
ERROR:  UNION/INTERSECT/EXCEPT statements are not supported on incrementally maintainable materialized view
-- empty target list is not allowed with IVM
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm25 AS SELECT FROM mv_base_a;
ERROR:  empty target list is not supported on incrementally maintainable materialized view
-- FOR UPDATE/SHARE is not supported
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm26 AS SELECT i,j FROM mv_base_a FOR UPDATE;
ERROR:  FOR UPDATE/SHARE clause is not supported on incrementally maintainable materialized view
-- tartget list cannot contain ivm column that start with '__ivm'
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm28 AS SELECT i AS "__ivm_count__" FROM mv_base_a;
ERROR:  column name __ivm_count__ is not supported on incrementally maintainable materialized view
-- expressions specified in GROUP BY must appear in the target list.
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm29 AS SELECT COUNT(i) FROM mv_base_a GROUP BY i;
ERROR:  GROUP BY expression not appearing in select list is not supported on incrementally maintainable materialized view
-- experssions containing an aggregate is not supported
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm30 AS SELECT sum(i)*0.5 FROM mv_base_a;
ERROR:  expression containing an aggregate in it is not supported on incrementally maintainable materialized view
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm31 AS SELECT sum(i)/sum(j) FROM mv_base_a;
ERROR:  expression containing an aggregate in it is not supported on incrementally maintainable materialized view
-- VALUES is not supported
CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_only_values1 AS values(1);
ERROR:  VALUES is not supported on incrementally maintainable materialized view
-- cleanup
DROP TABLE mv_base_b CASCADE;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to materialized view mv_ivm_1
drop cascades to view b_view
drop cascades to materialized view b_mview
DROP TABLE mv_base_a CASCADE;