Intersects error on filtered and casted geometry type

[Regina Obe]

> Hello,
> 
> Bellow my case and the steps to reproduce :
> 
> /* About my database */
> SELECT VERSION();
> -- PostgreSQL 16.4, compiled by Visual C++ build 1941, 64-bit SELECT
> POSTGIS_VERSION();
> -- 3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
> 
> /* Create a table with a generic geometry column with french CC48 3948
SRID */
> CREATE TEMP TABLE temp_geometry_tab (
>     id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
>     geom GEOMETRY(GEOMETRY, 3948)
> );
> -- CREATE TABLE
> -- Query returned successfully in 117 msec.
> 
> /* Add two rows, one with a point geometry and another with multipolygon
*/
> INSERT INTO temp_geometry_tab (geom) VALUES ('POINT (2049346
> 7276066)'), ('MULTIPOLYGON (((2049302 7274650,2050712
> 7276342,2051981 7276013,2050865 7275954,2049795 7274791,2049948
> 7274050,2049302 7274650)))') ;
> -- INSERT 0 2
> -- Query returned successfully in 71 msec.
> 
> /* Create a view to filter the point geometries and cast them into points
*/
> CREATE TEMP VIEW temp_geometry_view AS SELECT
>     id,
>     geom::GEOMETRY(POINT, 3948)
> FROM temp_geometry_tab
> WHERE ST_GeometryType(geom) IN ('ST_Point') ;
> -- CREATE VIEW
> -- Query returned successfully in 83 msec.
> 
> /* When I try to intersect the view geometry, an error occurs as if I
query the
> base table */ SELECT id FROM temp_geometry_view WHERE geom &&
> ST_MakeEnvelope(2041534,7271489,2059551,7281479,3948)
> ;
> -- ERROR:  Geometry type (MultiPolygon) does not match column type (Point)
> -- SQL state: 22023
> 
> /* When I try to query the first view with and additional filter it works
*/ SELECT
>     id,
>     geom::GEOMETRY(POINT, 3948)
> FROM temp_geometry_tab
> WHERE
>     ST_GeometryType(geom) IN ('ST_Point')
>     AND geom &&
> ST_MakeEnvelope(2041534,7271489,2059551,7281479,3948)
> ;
> -- id: 1 | geom:
> 01010000206C0F00000000000042453F410000008088C15B41
> 
> 
> Is-it normal that the filter consider the whole base table with
multipolygon
> geometries while only point type geometries are filtered in the queried
view?
> 
> Regards,
> Julien Monticolo
> 
I think in the past it might not have been possible but I don't think there
is a guarantee that the WHERE clause always gets checked before the SELECT
part is applied.

I suppose you could get around it, not nice by doing this

 CREATE TEMP VIEW temp_geometry_view AS SELECT
     id,
    CASE WHEN  ST_GeometryType(geom) IN ('ST_Point') THEN geom ELSE NULL
END::GEOMETRY(POINT, 3948)
 FROM temp_geometry_tab
 WHERE ST_GeometryType(geom) IN ('ST_Point') ;

I think adding in a pointless OFFSET also forces WHERE clause to be
processed first, though that is a very oldish hack too

CREATE TEMP VIEW temp_geometry_view AS SELECT
     id,
    geom::GEOMETRY(POINT, 3948)
 FROM temp_geometry_tab
 WHERE ST_GeometryType(geom) IN ('ST_Point')
OFFSET 0;

I think also using a CHECK option on the view might work though that
designed to prevent updates that would prevent a record from being filtered
out of the view by updating the condition that makes it a member and not for
this situation:

Something like


 CREATE TEMP VIEW temp_geometry_view AS SELECT
     id,
   geom::GEOMETRY(POINT, 3948)
 FROM temp_geometry_tab
 WHERE ST_GeometryType(geom) IN ('ST_Point')
WITH CHECK OPTION ;