[postgis-tickets] r17658 - Deal with differing behaviour of subquery planning between Pg versions.

[Paul Ramsey]

Author: pramsey
Date: 2019-08-01 13:15:14 -0700 (Thu, 01 Aug 2019)
New Revision: 17658

Modified:
   branches/2.5/regress/estimatedextent.sql
   branches/2.5/regress/estimatedextent_expected
   branches/2.5/regress/sfcgal/tickets.sql
   branches/2.5/regress/sfcgal/tickets_expected
Log:
Deal with differing behaviour of subquery planning between Pg versions.
References #4464


Modified: branches/2.5/regress/estimatedextent.sql
===================================================================
--- branches/2.5/regress/estimatedextent.sql	2019-08-01 17:48:07 UTC (rev 17657)
+++ branches/2.5/regress/estimatedextent.sql	2019-08-01 20:15:14 UTC (rev 17658)
@@ -11,7 +11,7 @@
 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 ( select ST_EstimatedExtent('t','g') as e ) e;
+from ( select ST_EstimatedExtent('t','g') as e limit 1) e;
 
 -- #877.4
 analyze t;
@@ -18,7 +18,7 @@
 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 ( select ST_EstimatedExtent('t','g') as e ) e;
+from ( select ST_EstimatedExtent('t','g') as e limit 1 ) e;
 
 -- #877.5
 truncate t;
@@ -25,7 +25,7 @@
 select '#877.5',
   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 ( select ST_EstimatedExtent('t','g') as e ) e;
+from ( select ST_EstimatedExtent('t','g') as e limit 1 ) e;
 drop table t;
 
 -- #3391
@@ -43,19 +43,19 @@
 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 ( select ST_EstimatedExtent('c1','g') as e ) e;
+from ( select ST_EstimatedExtent('c1','g') as e limit 1 ) e;
 
 -- #3391.2
 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 ( select ST_EstimatedExtent('c2','g') as e ) e;
+from ( select ST_EstimatedExtent('c2','g') as e limit 1 ) e;
 
 -- #3391.3
 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 ( select ST_EstimatedExtent('p','g') as e ) e;
+from ( select ST_EstimatedExtent('p','g') as e limit 1 ) e;
 
 insert into c1 values ('Point(0 0)'::geometry);
 insert into c1 values ('Point(1 1)'::geometry);
@@ -68,19 +68,19 @@
 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 ( select ST_EstimatedExtent('c1','g') as e ) e;
+from ( select ST_EstimatedExtent('c1','g') as e limit 1 ) e;
 
 -- #3391.5
 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 ( select ST_EstimatedExtent('c2','g') as e ) e;
+from ( select ST_EstimatedExtent('c2','g') as e limit 1 ) e;
 
 -- #3391.6
 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 ( select ST_EstimatedExtent('p','g') as e ) e;
+from ( select ST_EstimatedExtent('p','g') as e limit 1 ) e;
 
 insert into c2 values ('Point(0 0)'::geometry);
 insert into c2 values ('Point(-1 -1)'::geometry);
@@ -93,19 +93,19 @@
 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 ( select ST_EstimatedExtent('c1','g') as e ) e;
+from ( select ST_EstimatedExtent('c1','g') as e limit 1 ) e;
 
 -- #3391.8
 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 ( select ST_EstimatedExtent('c2','g') as e ) e;
+from ( select ST_EstimatedExtent('c2','g') as e limit 1 ) e;
 
 -- #3391.9
 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 ( select ST_EstimatedExtent('p','g') as e )e;
+from ( select ST_EstimatedExtent('p','g') as e limit 1 )e;
 
 insert into p values ('Point(1 1)'::geometry);
 insert into p values ('Point(2 2)'::geometry);
@@ -118,19 +118,19 @@
 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 ( select ST_EstimatedExtent('c1','g') as e ) e;
+from ( select ST_EstimatedExtent('c1','g') as e limit 1 ) e;
 
 -- #3391.11
 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 ( select ST_EstimatedExtent('c2','g') as e ) e;
+from ( select ST_EstimatedExtent('c2','g') as e limit 1 ) e;
 
 -- #3391.12
 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 ( select ST_EstimatedExtent('p','g') as e ) e;
+from ( select ST_EstimatedExtent('p','g') as e limit 1 ) e;
 
 -- test calls with 3th parameter
 
@@ -150,25 +150,25 @@
 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 ( select ST_EstimatedExtent('public','p','g','t') as e ) e;
+from ( select ST_EstimatedExtent('public','p','g','t') as e limit 1 ) e;
 
 -- #3391.14
 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 ( select ST_EstimatedExtent('public','p','g','f') as e ) e;
+from ( select ST_EstimatedExtent('public','p','g','f') as e limit 1 ) e;
 
 -- #3391.15
 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 ( select ST_EstimatedExtent('public','c1','g', 't') as e ) e;
+from ( select ST_EstimatedExtent('public','c1','g', 't') as e limit 1 ) e;
 
 -- #3391.16
 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 ( select ST_EstimatedExtent('public','c1','g', 'f') as e ) e;
+from ( select ST_EstimatedExtent('public','c1','g', 'f') as e limit 1 ) e;
 
 insert into c1 values ('Point(0 0)'::geometry);
 insert into c1 values ('Point(1 1)'::geometry);
@@ -181,25 +181,25 @@
 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 ( select ST_EstimatedExtent('public','p','g','f') as e ) e;
+from ( select ST_EstimatedExtent('public','p','g','f') as e limit 1 ) e;
 
 -- #3391.18
 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 ( select ST_EstimatedExtent('public','p','g','t') as e ) e;
+from ( select ST_EstimatedExtent('public','p','g','t') as e limit 1 ) e;
 
 -- #3391.19
 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 ( select ST_EstimatedExtent('public','c1','g', 'f') as e ) e;
+from ( select ST_EstimatedExtent('public','c1','g', 'f') as e limit 1 ) e;
 
 -- #3391.20
 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 ( select ST_EstimatedExtent('public','c1','g', 't') as e ) e;
+from ( select ST_EstimatedExtent('public','c1','g', 't') as e limit 1 ) e;
 
 drop table p cascade;
 

Modified: branches/2.5/regress/estimatedextent_expected
===================================================================
--- branches/2.5/regress/estimatedextent_expected	2019-08-01 17:48:07 UTC (rev 17657)
+++ branches/2.5/regress/estimatedextent_expected	2019-08-01 20:15:14 UTC (rev 17658)
@@ -6,32 +6,17 @@
 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
@@ -41,30 +26,15 @@
 #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
-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

Modified: branches/2.5/regress/sfcgal/tickets.sql
===================================================================
--- branches/2.5/regress/sfcgal/tickets.sql	2019-08-01 17:48:07 UTC (rev 17657)
+++ branches/2.5/regress/sfcgal/tickets.sql	2019-08-01 20:15:14 UTC (rev 17658)
@@ -465,7 +465,7 @@
 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 ( select ST_EstimatedExtent('t','g') as e ) e;
+from ( select ST_EstimatedExtent('t','g') as e limit 1 ) e;
 
 -- #877.4
 analyze t;
@@ -472,7 +472,7 @@
 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 ( select ST_EstimatedExtent('t','g') as e ) e;
+from ( select ST_EstimatedExtent('t','g') as e limit 1 ) e;
 
 -- #877.5
 truncate t;
@@ -479,7 +479,7 @@
 select '#877.5',
   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 ( select ST_EstimatedExtent('t','g') as e ) e;
+from ( select ST_EstimatedExtent('t','g') as e limit 1 ) e;
 drop table t;
 
 -- #1292

Modified: branches/2.5/regress/sfcgal/tickets_expected
===================================================================
--- branches/2.5/regress/sfcgal/tickets_expected	2019-08-01 17:48:07 UTC (rev 17657)
+++ branches/2.5/regress/sfcgal/tickets_expected	2019-08-01 20:15:14 UTC (rev 17658)
@@ -95,7 +95,7 @@
 #835.11|MULTILINESTRING EMPTY
 #835.12|MULTIPOLYGON EMPTY
 #650|MULTIPOINT(0 0,1 1,2 2)
-#667|SRID=4326;CURVEPOLYGON(CIRCULARSTRING(30 40,-50 39.9999999999999,30 40))
+ERROR:  function postgis_sfcgal_version() does not exist at character 118
 #677|1121395
 #680|01d107000000000000000024c000000000000049400000000000000040
 #681a|
@@ -151,9 +151,6 @@
 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