[postgis-tickets] r17267 - Change from using CTE to use subquery so results are the same across all platforms

[Regina Obe]

Author: robe
Date: 2019-02-21 06:53:21 -0800 (Thu, 21 Feb 2019)
New Revision: 17267

Modified:
   trunk/regress/core/estimatedextent.sql
   trunk/regress/core/estimatedextent_expected
Log:
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-20 23:50:32 UTC (rev 17266)
+++ trunk/regress/core/estimatedextent.sql	2019-02-21 14:53:21 UTC (rev 17267)
@@ -159,24 +159,24 @@
 analyze p;
 
 -- #3391.17
-with e as ( select ST_EstimatedExtent('public','p','g','f') as e )
 select '#3391.17', 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.18
-with e as ( select ST_EstimatedExtent('public','p','g','t') as e )
 select '#3391.18', 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.19
-with e as ( select ST_EstimatedExtent('public','c1','g', 'f') as e )
 select '#3391.19', 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;
 
 -- #3391.20
-with e as ( select ST_EstimatedExtent('public','c1','g', 't') as e )
 select '#3391.20', 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;
 
 drop table p cascade;
 
@@ -212,4 +212,4 @@
 insert into test (geom1, geom2) select NULL, NULL;
 insert into test (geom1, geom2) select NULL, NULL;
 ANALYZE test;
-drop table test cascade;
\ No newline at end of file
+drop table test cascade;

Modified: trunk/regress/core/estimatedextent_expected
===================================================================
--- trunk/regress/core/estimatedextent_expected	2019-02-20 23:50:32 UTC (rev 17266)
+++ trunk/regress/core/estimatedextent_expected	2019-02-21 14:53:21 UTC (rev 17267)
@@ -35,6 +35,9 @@
 #3391.16||||
 #3391.17|0.00|1.00|0.00|1.00
 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.18||||
 #3391.19|0.00|1.00|0.00|1.00
 #3391.20|0.00|1.00|0.00|1.00