[postgis-tickets] r17268 - Missed some spots in last commit

[Regina Obe]

Author: robe
Date: 2019-02-21 10:26:47 -0800 (Thu, 21 Feb 2019)
New Revision: 17268

Modified:
   trunk/regress/core/estimatedextent.sql
   trunk/regress/core/estimatedextent_expected
Log:
Missed some spots in last commit
Change from using CTE to use subquery so results are the same across all platforms
reason necessary in PostgreSQL 12, CTE changed from being always materialized to being often inlined.  
This means PG12, no longer behaves like old CTE and behaves more like subquery.
As a result 12 CTE is outputting 4 warnings instead of 1 like older versions. 
Changing to just subquery should force all to output 4 warnings.
references #4329 for PostGIS 3.0

Modified: trunk/regress/core/estimatedextent.sql
===================================================================
--- trunk/regress/core/estimatedextent.sql	2019-02-21 14:53:21 UTC (rev 17267)
+++ trunk/regress/core/estimatedextent.sql	2019-02-21 18:26:47 UTC (rev 17268)
@@ -8,15 +8,15 @@
 insert into t(g) values ('LINESTRING(-10 -50, 20 30)');
 
 -- #877.3
-with e as ( select ST_EstimatedExtent('t','g') as e )
 select '#877.3', round(st_xmin(e.e)::numeric, 5), round(st_xmax(e.e)::numeric, 5),
-round(st_ymin(e.e)::numeric, 5), round(st_ymax(e.e)::numeric, 5) from e;
+round(st_ymin(e.e)::numeric, 5), round(st_ymax(e.e)::numeric, 5)
+from ( select ST_EstimatedExtent('t','g') as e ) AS e;
 
 -- #877.4
 analyze t;
-with e as ( select ST_EstimatedExtent('t','g') as e )
 select '#877.4', round(st_xmin(e.e)::numeric, 5), round(st_xmax(e.e)::numeric, 5),
-round(st_ymin(e.e)::numeric, 5), round(st_ymax(e.e)::numeric, 5) from e;
+round(st_ymin(e.e)::numeric, 5), round(st_ymax(e.e)::numeric, 5)
+from ( select ST_EstimatedExtent('t','g') as e ) AS e;
 
 -- #877.5
 truncate t;
@@ -37,19 +37,19 @@
 analyze p;
 
 -- #3391.1
-with e as ( select ST_EstimatedExtent('c1','g') as e )
 select '#3391.1', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from ( select ST_EstimatedExtent('c1','g') as e ) AS e;
 
 -- #3391.2
-with e as ( select ST_EstimatedExtent('c2','g') as e )
 select '#3391.2', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from ( select ST_EstimatedExtent('c2','g') as e ) AS e;
 
 -- #3391.3
-with e as ( select ST_EstimatedExtent('p','g') as e )
 select '#3391.3', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from ( select ST_EstimatedExtent('p','g') as e ) AS e;
 
 insert into c1 values ('Point(0 0)'::geometry);
 insert into c1 values ('Point(1 1)'::geometry);
@@ -59,19 +59,19 @@
 analyze p;
 
 -- #3391.4
-with e as ( select ST_EstimatedExtent('c1','g') as e )
 select '#3391.4', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from ( select ST_EstimatedExtent('c1','g') as e ) AS e;
 
 -- #3391.5
-with e as ( select ST_EstimatedExtent('c2','g') as e )
 select '#3391.5', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from ( select ST_EstimatedExtent('c2','g') as e ) AS e;
 
 -- #3391.6
-with e as ( select ST_EstimatedExtent('p','g') as e )
 select '#3391.6', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from ( select ST_EstimatedExtent('p','g') as e ) AS e;
 
 insert into c2 values ('Point(0 0)'::geometry);
 insert into c2 values ('Point(-1 -1)'::geometry);
@@ -81,19 +81,19 @@
 analyze p;
 
 -- #3391.7
-with e as ( select ST_EstimatedExtent('c1','g') as e )
 select '#3391.7', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from ( select ST_EstimatedExtent('c1','g') as e ) AS e;
 
 -- #3391.8
-with e as ( select ST_EstimatedExtent('c2','g') as e )
 select '#3391.8', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from ( select ST_EstimatedExtent('c2','g') as e ) AS e;
 
 -- #3391.9
-with e as ( select ST_EstimatedExtent('p','g') as e )
 select '#3391.9', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from ( select ST_EstimatedExtent('p','g') as e ) AS e;
 
 insert into p values ('Point(1 1)'::geometry);
 insert into p values ('Point(2 2)'::geometry);
@@ -103,19 +103,19 @@
 analyze p;
 
 -- #3391.10
-with e as ( select ST_EstimatedExtent('c1','g') as e )
 select '#3391.10', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from ( select ST_EstimatedExtent('c1','g') as e ) AS e;
 
 -- #3391.11
-with e as ( select ST_EstimatedExtent('c2','g') as e )
 select '#3391.11', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from ( select ST_EstimatedExtent('c2','g') as e ) AS e;
 
 -- #3391.12
-with e as ( select ST_EstimatedExtent('p','g') as e )
 select '#3391.12', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from ( select ST_EstimatedExtent('p','g') as e ) AS e;
 
 -- test calls with 3th parameter
 
@@ -132,24 +132,24 @@
 analyze p;
 
 -- #3391.13
-with e as ( select ST_EstimatedExtent('public','p','g','t') as e )
 select '#3391.13', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from ( select ST_EstimatedExtent('public','p','g','t') as e ) AS e;
 
 -- #3391.14
-with e as ( select ST_EstimatedExtent('public','p','g','f') as e )
 select '#3391.14', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from ( select ST_EstimatedExtent('public','p','g','f') as e ) AS e;
 
 -- #3391.15
-with e as ( select ST_EstimatedExtent('public','c1','g', 't') as e )
 select '#3391.15', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from (select ST_EstimatedExtent('public','c1','g', 't') as e ) AS e;
 
 -- #3391.16
-with e as ( select ST_EstimatedExtent('public','c1','g', 'f') as e )
 select '#3391.16', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
-round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
+round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2)
+from  ( select ST_EstimatedExtent('public','c1','g', 'f') as e ) AS e;
 
 insert into c1 values ('Point(0 0)'::geometry);
 insert into c1 values ('Point(1 1)'::geometry);

Modified: trunk/regress/core/estimatedextent_expected
===================================================================
--- trunk/regress/core/estimatedextent_expected	2019-02-21 14:53:21 UTC (rev 17267)
+++ trunk/regress/core/estimatedextent_expected	2019-02-21 18:26:47 UTC (rev 17268)
@@ -6,17 +6,32 @@
 WARNING:  stats for "t.g" do not exist
 #877.2.deprecated|
 WARNING:  stats for "t.g" do not exist
+WARNING:  stats for "t.g" do not exist
+WARNING:  stats for "t.g" do not exist
+WARNING:  stats for "t.g" do not exist
 #877.3||||
 #877.4|-10.15000|20.15000|-50.40000|30.40000
 #877.5|-10.15000|20.15000|-50.40000|30.40000
 WARNING:  stats for "c1.g" do not exist
+WARNING:  stats for "c1.g" do not exist
+WARNING:  stats for "c1.g" do not exist
+WARNING:  stats for "c1.g" do not exist
 #3391.1||||
 WARNING:  stats for "c2.g" do not exist
+WARNING:  stats for "c2.g" do not exist
+WARNING:  stats for "c2.g" do not exist
+WARNING:  stats for "c2.g" do not exist
 #3391.2||||
 WARNING:  stats for "p.g" do not exist
+WARNING:  stats for "p.g" do not exist
+WARNING:  stats for "p.g" do not exist
+WARNING:  stats for "p.g" do not exist
 #3391.3||||
 #3391.4|0.00|1.00|0.00|1.00
 WARNING:  stats for "c2.g" do not exist
+WARNING:  stats for "c2.g" do not exist
+WARNING:  stats for "c2.g" do not exist
+WARNING:  stats for "c2.g" do not exist
 #3391.5||||
 #3391.6|0.00|1.00|0.00|1.00
 #3391.7|0.00|1.00|0.00|1.00
@@ -26,12 +41,24 @@
 #3391.11|-1.00|0.00|-1.00|0.00
 #3391.12|-1.01|2.02|-1.01|2.02
 WARNING:  stats for "p.g" do not exist
+WARNING:  stats for "p.g" do not exist
+WARNING:  stats for "p.g" do not exist
+WARNING:  stats for "p.g" do not exist
 #3391.13||||
 WARNING:  stats for "p.g" do not exist
+WARNING:  stats for "p.g" do not exist
+WARNING:  stats for "p.g" do not exist
+WARNING:  stats for "p.g" do not exist
 #3391.14||||
 WARNING:  stats for "c1.g" do not exist
+WARNING:  stats for "c1.g" do not exist
+WARNING:  stats for "c1.g" do not exist
+WARNING:  stats for "c1.g" do not exist
 #3391.15||||
 WARNING:  stats for "c1.g" do not exist
+WARNING:  stats for "c1.g" do not exist
+WARNING:  stats for "c1.g" do not exist
+WARNING:  stats for "c1.g" do not exist
 #3391.16||||
 #3391.17|0.00|1.00|0.00|1.00
 WARNING:  stats for "p.g" do not exist