QGIS PostGIS Raster Driver BUG (example) with suggested fix (ALL VERSIONS OF QGIS)

[qgib]

Author Name: al piszcz (al piszcz)
Original Redmine Issue: 19583
Affected QGIS version: 3.3(master)
Redmine category:data_provider/postgis

---

Attempting to load a raster from a postgis database using the Database Manager plugin with a schema creates an error when the table name uses mixed case or special characters.

The reported error is "Cannot get GDAL Raster band:" in the GUI dialog.

PostGIS/GRES recommends double quotes around "database"."SchemaName"."TableName_1" or "SchemaName"."TableName_1" if already connected to the database.

It appears the code in
https://github.com/varunsaraf/postgis_raster/blob/master/postgisrasterdataset.cpp
(and perhaps elsewhere does not double quote schema and table name.
https://www.postgresql.org/docs/10/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS.

SCHEMA=rasterschema
TABLE=test (WORKS)
Table=Test_1 (FAILS), the name gets interpreted as test_1 and fails, if "test"."Test_1" raster load completes.

WORKING EXAMPLE
2018-08-09 14:00:03.724 EDT [608908] LOG: statement: select st_bandmetadata(rast, band) from (select rast, generate_series(1, 1) band from (select rast from rasterschema.test where (true) AND st_numbands(rast)=1 limit 1) bar) foo

FAILING EXAMPLE
2018-08-09 14:03:12.835 EDT [608908] LOG: statement: select st_bandmetadata(rast, band) from (select rast, generate_series(1, 1) band from (select rast from rasterschema.Test_1 where (true) AND st_numbands(rast)=1 limit 1) bar) foo
2018-08-09 14:03:12.835 EDT [608908] ERROR: relation "rasterchema.test_1" does not exist at character 105

Using the failing example from the command line with recommended quoting:
db=# select st_bandmetadata(rast, band) from (select rast, generate_series(1, 1) band from (select rast from "rasterschema"."Test_1" where (true) AND st_numbands(rast)=1 limit 1) bar) foo;
st_bandmetadata

(8BUI,0,f,)
(1 row)

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

This is a GDAL issue.

---

• resolution was changed from to up/downstream

[qgib]

Author Name: Nyall Dawson (@nyalldawson)

---

• status_id was changed from Open to Closed

[qgib]

Author Name: al piszcz (al piszcz)

---

Hello, thank you for the review, do I need to submit this issue elsewhere?

The query from https://github.com/varunsaraf/postgis_raster/blob/master/postgisrasterdataset.cpp
is not enclosing the schema and table name in "".

    if (pszWhere == NULL) {
        osCommand.Printf("select st_bandmetadata(%s, band) from "
        "(select %s, generate_series(1, st_numbands(%s)) band from "
        "(select %s from %s.%s limit 1) bar) foo", pszColumn, pszColumn,
        pszColumn, pszColumn, pszSchema, pszTable);
    } 

    else {
        osCommand.Printf("select st_bandmetadata(%s, band) from "
        "(select %s, generate_series(1, st_numbands(%s)) band from "
        "(select %s from %s.%s where %s limit 1) bar) foo", pszColumn, 
        pszColumn, pszColumn, pszColumn, pszSchema, pszTable, pszWhere);
    }

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

al piszcz wrote:

Hello, thank you for the review, do I need to submit this issue elsewhere?

The query from https://github.com/varunsaraf/postgis_raster/blob/master/postgisrasterdataset.cpp
is not enclosing the schema and table name in "".

Take a look at OSGeo/gdal#837