Use ORDER BY on matview definitions were needed for stable plans.

kgrittn · kgrittn · commit 241139ae4baf · 2013-03-19T10:33:37.000-05:00
Per report from Hadi Moshayedi of matview regression test failure
with optimization of aggregates.  A few ORDER BY clauses improve
code coverage for matviews while solving that problem.
diff --git 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
@@ -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;
