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

[Raul]

Author: algunenano
Date: 2019-06-12 06:31:25 -0700 (Wed, 12 Jun 2019)
New Revision: 17514

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

Closes #4388


Modified: branches/2.3/NEWS
===================================================================
--- branches/2.3/NEWS	2019-06-12 13:30:48 UTC (rev 17513)
+++ branches/2.3/NEWS	2019-06-12 13:31:25 UTC (rev 17514)
@@ -5,6 +5,7 @@
 
   - #4361, Fix postgis_type_name with (GEOMETRYM,3) (Matt Bretl)
   - #4326, Fix circular arc distance calculation (Paul Ramsey)
+  - #4388, AddRasterConstraints: Ignore NULLs when generating constraints (Raúl Marín)
 
 
 PostGIS 2.3.9

Modified: branches/2.3/raster/rt_pg/rtpostgis.sql.in
===================================================================
--- branches/2.3/raster/rt_pg/rtpostgis.sql.in	2019-06-12 13:30:48 UTC (rev 17513)
+++ branches/2.3/raster/rt_pg/rtpostgis.sql.in	2019-06-12 13:31:25 UTC (rev 17514)
@@ -6919,7 +6919,9 @@
 		sql := 'SELECT 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
@@ -7000,7 +7002,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
@@ -7147,11 +7151,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;
 
@@ -7212,7 +7218,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
@@ -7450,8 +7460,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
@@ -7524,7 +7537,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
@@ -7608,7 +7623,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
@@ -7695,7 +7712,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.3/raster/test/regress/tickets.sql
===================================================================
--- branches/2.3/raster/test/regress/tickets.sql	2019-06-12 13:30:48 UTC (rev 17513)
+++ branches/2.3/raster/test/regress/tickets.sql	2019-06-12 13:31:25 UTC (rev 17514)
@@ -117,3 +117,12 @@
  #3055 ST_Clip() on a raster without band crashes the server
 ******************************************************************************/
 SELECT ST_SummaryStats(ST_Clip(ST_MakeEmptyRaster(42, 42, 0, 0, 1.0, 1.0, 0, 0, 4269), ST_MakeEnvelope(0, 0, 20, 20, 4269)));
+
+/**
+#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.3/raster/test/regress/tickets_expected
===================================================================
--- branches/2.3/raster/test/regress/tickets_expected	2019-06-12 13:30:48 UTC (rev 17513)
+++ branches/2.3/raster/test/regress/tickets_expected	2019-06-12 13:31:25 UTC (rev 17514)
@@ -14,3 +14,15 @@
 ERROR:  new row for relation "test_raster_scale_small" violates check constraint "enforce_scaley_rast"
 NOTICE:  Input raster is empty or has no bands. Returning empty raster
 NOTICE:  Invalid band index (must use 1-based). Returning NULL
+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