[PostGIS] #6144: ST_CreateOverview function errors

[PostGIS]

#6144: ST_CreateOverview function errors
-------------------------+---------------------------
 Reporter:  amorevosgeo  |      Owner:  pramsey
     Type:  defect       |     Status:  new
 Priority:  medium       |  Milestone:  PostGIS 3.6.5
Component:  postgis      |    Version:  3.5.x
 Keywords:               |
-------------------------+---------------------------
 PostgreSQL and POSTGIS version:

 PostgreSQL 16.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0
 20240719 (Red Hat 11.5.0-5), 64-bit
 POSTGIS="3.5.0 d2c3ca4" [EXTENSION] PGSQL="160" GEOS="3.12.2-CAPI-1.18.2"
 SFCGAL="SFCGAL 2.2.0, CGAL 5.6.2, BOOST 1.75.0" PROJ="9.6.2
 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
 USER_WRITABLE_DIRECTORY=/var/lib/pgsql/.local/share/proj
 DATABASE_PATH=/usr/proj96/share/proj/proj.db" (compiled against PROJ
 9.12.2) GDAL="GDAL 3.8.5, released 2024/04/02" LIBXML="2.9.13"
 LIBJSON="0.14" RASTER


 1. IF block:
 {{{
 IF sinfo.tw IS NULL or sinfo.tw IS NULL THEN
     RAISE EXCEPTION 'cannot create overview without tilesize constraint,
 try select AddRasterConstraints(''%'', ''%'');', tab::text, col;
   END IF;
 }}}
 Here, "sinfo.**tw**" is compared with NULL twice. In the second case, it
 should be "sinfo.**th**".


 2. sql block:
 {{{
 sql := 'CREATE TABLE ' || quote_ident(sinfo.sch)
       || '.' || quote_ident(ttab)
       || ' AS SELECT ST_Retile($1, $2, $3, $4, $5, $6, $7) '
       || quote_ident(col);
   EXECUTE sql USING tab, col, sinfo.ext,
                     sinfo.sfx * factor, sinfo.sfy * factor,
                     sinfo.tw, sinfo.th, algo;
 }}}

 Here, ST_Retile() has 7 args but USING has 8 args:

 $1 => tab,[[BR]]
 $2 => col,[[BR]]
 $3 => sinfo.ext,[[BR]]
 $4 => sinfo.sfx * factor,[[BR]]
 $5 => sinfo.sfy * factor,[[BR]]
 $6 => sinfo.tw,[[BR]]
 $7 => sinfo.th,[[BR]]
 $8 => algo

 Without the eighth argument ("algo"), ST_Retile() will always use the
 default value (text algo='NearestNeighbor').


 These errors are also found in earlier versions (PostGIS 3.2.0 and
 newest).

 These errors are also found in current PostGIS repository:
 https://gitea.osgeo.org/postgis/postgis/src/branch/master/raster/rt_pg/rtpostgis.sql.in

 1. Row 8858.
 2. Row 8869.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/6144>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.