diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/test/regress/expected/matview.out | 35 | ||||
| -rw-r--r-- | src/test/regress/sql/matview.sql | 8 |
2 files changed, 24 insertions, 19 deletions
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index f03b9196b02..5a53f84c5b8 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -8,12 +8,12 @@ INSERT INTO t VALUES (5, 'z', 11); -- we want a view based on the table, too, since views present additional challenges CREATE VIEW tv AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type; -SELECT * FROM tv; +SELECT * FROM tv ORDER BY type; type | totamt ------+-------- + x | 5 y | 12 z | 11 - x | 5 (3 rows) -- create a materialized view with no data, and confirm correct behavior @@ -53,20 +53,22 @@ SELECT * FROM tm; -- create various views EXPLAIN (costs off) - CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv; - QUERY PLAN ---------------------- - HashAggregate - -> Seq Scan on t -(2 rows) + CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv ORDER BY type; + QUERY PLAN +--------------------------- + Sort + Sort Key: t.type + -> HashAggregate + -> Seq Scan on t +(4 rows) -CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv; +CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv ORDER BY type; SELECT * FROM tvm; type | totamt ------+-------- + x | 5 y | 12 z | 11 - x | 5 (3 rows) CREATE MATERIALIZED VIEW tmm AS SELECT sum(totamt) AS grandtot FROM tm; @@ -95,7 +97,8 @@ CREATE INDEX aa ON bb (grandtot); View definition: SELECT tv.type, tv.totamt - FROM tv; + FROM tv + ORDER BY tv.type; \d+ tvm Materialized view "public.tvm" @@ -106,7 +109,8 @@ View definition: View definition: SELECT tv.type, tv.totamt - FROM tv; + FROM tv + ORDER BY tv.type; \d+ tvvm Materialized view "public.tvvm" @@ -151,7 +155,8 @@ SET search_path = mvschema, public; View definition: SELECT tv.type, tv.totamt - FROM tv; + FROM tv + ORDER BY tv.type; -- modify the underlying table data INSERT INTO t VALUES (6, 'z', 13); @@ -328,12 +333,12 @@ SELECT * FROM tum; (3 rows) -- test join of mv and view -SELECT type, m.totamt AS mtot, v.totamt AS vtot FROM tm m LEFT JOIN tv v USING (type); +SELECT type, m.totamt AS mtot, v.totamt AS vtot FROM tm m LEFT JOIN tv v USING (type) ORDER BY type; type | mtot | vtot ------+------+------ + x | 5 | 5 y | 12 | 12 z | 24 | 24 - x | 5 | 5 (3 rows) -- test diemv when the mv does exist diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index a01d266ee56..08b48188c3c 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -9,7 +9,7 @@ INSERT INTO t VALUES -- we want a view based on the table, too, since views present additional challenges CREATE VIEW tv AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type; -SELECT * FROM tv; +SELECT * FROM tv ORDER BY type; -- create a materialized view with no data, and confirm correct behavior EXPLAIN (costs off) @@ -24,8 +24,8 @@ SELECT * FROM tm; -- create various views EXPLAIN (costs off) - CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv; -CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv; + CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv ORDER BY type; +CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv ORDER BY type; SELECT * FROM tvm; CREATE MATERIALIZED VIEW tmm AS SELECT sum(totamt) AS grandtot FROM tm; CREATE MATERIALIZED VIEW tvmm AS SELECT sum(totamt) AS grandtot FROM tvm; @@ -104,7 +104,7 @@ SELECT pg_relation_is_scannable('tum'::regclass); SELECT * FROM tum; -- test join of mv and view -SELECT type, m.totamt AS mtot, v.totamt AS vtot FROM tm m LEFT JOIN tv v USING (type); +SELECT type, m.totamt AS mtot, v.totamt AS vtot FROM tm m LEFT JOIN tv v USING (type) ORDER BY type; -- test diemv when the mv does exist DROP MATERIALIZED VIEW IF EXISTS tum; |