From da27ffe625e06797d8c76b9456915b96beb2ce59 Mon Sep 17 00:00:00 2001 From: Bborie Park Date: Sat, 2 May 2015 20:27:49 +0000 Subject: [PATCH] add unit tests for numeric overflow. ticket #3100 git-svn-id: http://svn.osgeo.org/postgis/trunk@13468 b70326c6-7e19-0410-871a-916f4a2858ee --- raster/rt_pg/rtpostgis.sql.in | 2 +- raster/test/regress/tickets.sql | 102 ++++++++++++++++++++++++++- raster/test/regress/tickets_expected | 9 +++ 3 files changed, 109 insertions(+), 4 deletions(-) diff --git a/raster/rt_pg/rtpostgis.sql.in b/raster/rt_pg/rtpostgis.sql.in index 1286c94b9..bf3d57421 100644 --- a/raster/rt_pg/rtpostgis.sql.in +++ b/raster/rt_pg/rtpostgis.sql.in @@ -7011,7 +7011,7 @@ CREATE OR REPLACE FUNCTION _add_raster_constraint_scale(rastschema name, rasttab || ' ADD CONSTRAINT ' || quote_ident(cn) || ' CHECK (round(st_scale' || $4 || '(' || quote_ident($3) - || ')::numeric, 10) = round(' || attr || '::numeric, 10))'; + || ')::numeric, 10) = round(' || text(attr) || '::numeric, 10))'; RETURN _add_raster_constraint(cn, sql); END; $$ LANGUAGE 'plpgsql' VOLATILE STRICT diff --git a/raster/test/regress/tickets.sql b/raster/test/regress/tickets.sql index 398c61ce0..cba9b72aa 100644 --- a/raster/test/regress/tickets.sql +++ b/raster/test/regress/tickets.sql @@ -1,12 +1,21 @@ --- #1485 +/****************************************************************************** + #1485 +******************************************************************************/ + SELECT '#1485', count(*) FROM geometry_columns WHERE f_table_name = 'raster_columns'; --- #2532 +/****************************************************************************** + #2532 +******************************************************************************/ + SELECT '#2532.1', NULL::raster @ null::geometry; SELECT '#2532.2', NULL::geometry @ null::raster; --- #2911 +/****************************************************************************** + #2911 +******************************************************************************/ + WITH data AS ( SELECT '#2911' l, ST_Metadata(ST_Rescale( ST_AddBand( ST_MakeEmptyRaster(10, 10, 0, 0, 1, -1, 0, 0, 0), @@ -16,3 +25,90 @@ WITH data AS ( SELECT '#2911' l, ST_Metadata(ST_Rescale( -2.0 )) m ) SELECT l, (m).* FROM data; + +/****************************************************************************** + #3006 +******************************************************************************/ + +SET client_min_messages TO ERROR; + +DROP TABLE IF EXISTS test_raster_scale_regular; +DROP TABLE IF EXISTS test_raster_scale_big; +DROP TABLE IF EXISTS test_raster_scale_small; + +CREATE TABLE test_raster_scale_regular ( + rid integer, + rast raster +); + +CREATE TABLE test_raster_scale_big ( + rid integer, + rast raster +); + +CREATE TABLE test_raster_scale_small ( + rid integer, + rast raster +); + +CREATE OR REPLACE FUNCTION make_test_raster( + table_suffix text, + rid integer, + scale_x double precision, + scale_y double precision DEFAULT 1.0 +) +RETURNS void +AS $$ +DECLARE + rast raster; + width integer := 2; + height integer := 2; + ul_x double precision := 0; + ul_y double precision := 0; + skew_x double precision := 0; + skew_y double precision := 0; + initvalue double precision := 1; + nodataval double precision := 0; +BEGIN + rast := ST_MakeEmptyRaster(width, height, ul_x, ul_y, scale_x, scale_y, skew_x, skew_y, 0); + rast := ST_AddBand(rast, 1, '8BUI', initvalue, nodataval); + + EXECUTE format('INSERT INTO test_raster_scale_%s VALUES (%L, %L)', table_suffix, rid, rast); + RETURN; +END; +$$ LANGUAGE 'plpgsql'; + +SELECT make_test_raster('regular', 0, 1); +SELECT make_test_raster('regular', 1, 1.0000001); +SELECT make_test_raster('regular', 2, 0.9999999); +SELECT AddRasterConstraints('test_raster_scale_regular'::name, 'rast'::name, 'scale_x', 'scale_y'); +SELECT r_table_name, r_raster_column, scale_x, scale_y FROM raster_columns + WHERE r_raster_column = 'rast' AND r_table_name = 'test_raster_scale_regular'; + +-- Issues enforce_scaley_rast constraint violation +SELECT make_test_raster('regular', 3, 1.001, 0.9999999); + +SELECT make_test_raster('big', 0, -1234567890123456789.0); +SELECT AddRasterConstraints('test_raster_scale_big'::name, 'rast'::name, 'scale_x', 'scale_y'); +SELECT r_table_name, r_raster_column, scale_x, scale_y FROM raster_columns + WHERE r_raster_column = 'rast' AND r_table_name = 'test_raster_scale_big'; + +-- Issues enforce_scalex_rast constraint violation +SELECT make_test_raster('big', 1, -12345678901234567890.0); + +SELECT make_test_raster('small', 0, 0.00001); +SELECT make_test_raster('small', 1, 0.000011); +SELECT make_test_raster('small', 2, 0.00000999); +SELECT AddRasterConstraints('test_raster_scale_small'::name, 'rast'::name, 'scale_x', 'scale_y'); +SELECT r_table_name, r_raster_column, scale_x, scale_y FROM raster_columns + WHERE r_raster_column = 'rast' AND r_table_name = 'test_raster_scale_small'; + +-- Issues enforce_scaley_rast constraint violation +SELECT make_test_raster('small', 3, 0.00001, 1.00001); + +DROP FUNCTION make_test_raster(text, integer, double precision, double precision); +DROP TABLE IF EXISTS test_raster_scale_regular; +DROP TABLE IF EXISTS test_raster_scale_big; +DROP TABLE IF EXISTS test_raster_scale_small; + +SET client_min_messages TO DEFAULT; diff --git a/raster/test/regress/tickets_expected b/raster/test/regress/tickets_expected index e00841ebf..f758838d7 100644 --- a/raster/test/regress/tickets_expected +++ b/raster/test/regress/tickets_expected @@ -3,3 +3,12 @@ #2532.2| NOTICE: Raster has default geotransform. Adjusting metadata for use of GDAL Warp API #2911|0|0|5|5|2|-2|0|0|0|1 +t +test_raster_scale_regular|rast||1 +ERROR: new row for relation "test_raster_scale_regular" violates check constraint "enforce_scaley_rast" +t +test_raster_scale_big|rast|-1.23456789012346e+18|1 +ERROR: new row for relation "test_raster_scale_big" violates check constraint "enforce_scalex_rast" +t +test_raster_scale_small|rast||1 +ERROR: new row for relation "test_raster_scale_small" violates check constraint "enforce_scaley_rast" -- 2.40.0