[postgis-tickets] r17512 - AddRasterConstraints: Ignore NULLs when generating constraints

[Raul]

Author: algunenano
Date: 2019-06-12 06:29:53 -0700 (Wed, 12 Jun 2019)
New Revision: 17512

Modified:
   branches/2.5/NEWS
   branches/2.5/raster/rt_pg/rtpostgis.sql.in
   branches/2.5/raster/test/regress/tickets.sql
   branches/2.5/raster/test/regress/tickets_expected
Log:
AddRasterConstraints: Ignore NULLs when generating constraints

References #4388



Modified: branches/2.5/NEWS
===================================================================
--- branches/2.5/NEWS	2019-06-12 13:08:50 UTC (rev 17511)
+++ branches/2.5/NEWS	2019-06-12 13:29:53 UTC (rev 17512)
@@ -7,6 +7,7 @@
   - #4361, Fix postgis_type_name with (GEOMETRYM,3) (Matt Bretl)
   - #4326, Fix circular arc distance calculation (Paul Ramsey)
   - #4380, Simple TIN support to allow viz in QGIS (Paul Ramsey)
+  - #4388, AddRasterConstraints: Ignore NULLs when generating constraints (Raúl Marín)
 
 
 PostGIS 2.5.2

Modified: branches/2.5/raster/rt_pg/rtpostgis.sql.in
===================================================================
--- branches/2.5/raster/rt_pg/rtpostgis.sql.in	2019-06-12 13:08:50 UTC (rev 17511)
+++ branches/2.5/raster/rt_pg/rtpostgis.sql.in	2019-06-12 13:29:53 UTC (rev 17512)
@@ -7243,7 +7243,9 @@
 		sql := 'SELECT @extschema at .st_srid('
 			|| quote_ident($3)
 			|| ') FROM ' || fqtn
-			|| ' LIMIT 1';
+			|| ' WHERE '
+			|| quote_ident($3)
+			|| ' IS NOT NULL LIMIT 1;';
 		BEGIN
 			EXECUTE sql INTO attr;
 		EXCEPTION WHEN OTHERS THEN
@@ -7326,7 +7328,9 @@
 			|| quote_ident($3)
 			|| ') FROM '
 			|| fqtn
-			|| ' LIMIT 1';
+			|| ' WHERE '
+			|| quote_ident($3)
+			|| ' IS NOT NULL LIMIT 1;';
 		BEGIN
 			EXECUTE sql INTO attr;
 		EXCEPTION WHEN OTHERS THEN
@@ -7477,11 +7481,13 @@
 		fqtn := fqtn || quote_ident($2);
 
 		sql := 'SELECT @extschema at .ST_SRID('
-            || quote_ident($3)
-      || ') FROM '
-            || fqtn
-            || ' LIMIT 1;';
-    EXECUTE sql INTO srid;
+			|| quote_ident($3)
+			|| ') FROM '
+			|| fqtn
+			|| ' WHERE '
+			|| quote_ident($3)
+			|| ' IS NOT NULL LIMIT 1;';
+                EXECUTE sql INTO srid;
 
 		cn := 'enforce_max_extent_' || $3;
 
@@ -7544,7 +7550,11 @@
 
 		sql := 'SELECT @extschema at .st_makeemptyraster(1, 1, upperleftx, upperlefty, scalex, scaley, skewx, skewy, srid) FROM @extschema at .st_metadata((SELECT '
 			|| quote_ident($3)
-			|| ' FROM ' || fqtn || ' LIMIT 1))';
+			|| ' FROM '
+			|| fqtn
+			|| ' WHERE '
+			|| quote_ident($3)
+			|| ' IS NOT NULL LIMIT 1))';
 		BEGIN
 			EXECUTE sql INTO attr;
 		EXCEPTION WHEN OTHERS THEN
@@ -7792,8 +7802,11 @@
 		cn := 'enforce_num_bands_' || $3;
 
 		sql := 'SELECT @extschema at .st_numbands(' || quote_ident($3)
-			|| ') FROM ' || fqtn
-			|| ' LIMIT 1';
+			|| ') FROM '
+			|| fqtn
+			|| ' WHERE '
+			|| quote_ident($3)
+			|| ' IS NOT NULL LIMIT 1;';
 		BEGIN
 			EXECUTE sql INTO attr;
 		EXCEPTION WHEN OTHERS THEN
@@ -7868,7 +7881,9 @@
 
 		sql := 'SELECT @extschema at ._raster_constraint_pixel_types(' || quote_ident($3)
 			|| ') FROM ' || fqtn
-			|| ' LIMIT 1';
+			|| ' WHERE '
+			|| quote_ident($3)
+			|| ' IS NOT NULL LIMIT 1;';
 		BEGIN
 			EXECUTE sql INTO attr;
 		EXCEPTION WHEN OTHERS THEN
@@ -7954,7 +7969,9 @@
 
 		sql := 'SELECT @extschema at ._raster_constraint_nodata_values(' || quote_ident($3)
 			|| ') FROM ' || fqtn
-			|| ' LIMIT 1';
+			|| ' WHERE '
+			|| quote_ident($3)
+			|| ' IS NOT NULL LIMIT 1;';
 		BEGIN
 			EXECUTE sql INTO attr;
 		EXCEPTION WHEN OTHERS THEN
@@ -8043,7 +8060,9 @@
 
 		sql := 'SELECT @extschema at ._raster_constraint_out_db(' || quote_ident($3)
 			|| ') FROM ' || fqtn
-			|| ' LIMIT 1';
+			|| ' WHERE '
+			|| quote_ident($3)
+			|| ' IS NOT NULL LIMIT 1;';
 		BEGIN
 			EXECUTE sql INTO attr;
 		EXCEPTION WHEN OTHERS THEN

Modified: branches/2.5/raster/test/regress/tickets.sql
===================================================================
--- branches/2.5/raster/test/regress/tickets.sql	2019-06-12 13:08:50 UTC (rev 17511)
+++ branches/2.5/raster/test/regress/tickets.sql	2019-06-12 13:29:53 UTC (rev 17512)
@@ -123,3 +123,13 @@
 SELECT '#4102.2', ST_BandNoDataValue(ST_AddBand(ST_MakeEmptyRaster(2, 2, 0, 0, 1, -1, 0, 0, 0), 1, '32BSI', 0, -10), 1) AS rast;
 
 select '#3457', ST_Area((ST_DumpAsPolygons(ST_Clip(ST_ASRaster(ST_GeomFromText('POLYGON((0 0,100 0,100 100,0 100,0 0))',4326),ST_Addband(ST_MakeEmptyRaster(1,1,0,0,1,-1,0,0,4326),'32BF'::text,0,-1),'32BF'::text,1,-1), ST_GeomFromText('POLYGON((0 0,100 100,100 0,0 0))',4326)))).geom);
+
+
+/**
+#4308,
+*/
+CREATE TABLE table_4308 (r raster);
+INSERT INTO table_4308(r) values (NULL);
+INSERT INTO table_4308(r) SELECT ST_AddBand(ST_MakeEmptyRaster(10, 10, 1, 1, 2, 2, 0, 0,4326), 1, '8BSI'::text, -129, NULL);;
+SELECT AddRasterConstraints('table_4308', 'r');
+DROP TABLE table_4308;
\ No newline at end of file

Modified: branches/2.5/raster/test/regress/tickets_expected
===================================================================
--- branches/2.5/raster/test/regress/tickets_expected	2019-06-12 13:08:50 UTC (rev 17511)
+++ branches/2.5/raster/test/regress/tickets_expected	2019-06-12 13:29:53 UTC (rev 17512)
@@ -17,3 +17,15 @@
 #4102.1|-10
 #4102.2|-10
 #3457|4950
+NOTICE:  Adding SRID constraint
+NOTICE:  Adding scale-X constraint
+NOTICE:  Adding scale-Y constraint
+NOTICE:  Adding blocksize-X constraint
+NOTICE:  Adding blocksize-Y constraint
+NOTICE:  Adding alignment constraint
+NOTICE:  Adding number of bands constraint
+NOTICE:  Adding pixel type constraint
+NOTICE:  Adding nodata value constraint
+NOTICE:  Adding out-of-database constraint
+NOTICE:  Adding maximum extent constraint
+t