[postgis-tickets] [PostGIS] #4973: ST_AsMVT returns broken tiles when column name and JSONB key are the same

[PostGIS]

#4973: ST_AsMVT returns broken tiles when column name and JSONB key are the same
---------------------------+---------------------------
 Reporter:  emreisikligil  |      Owner:  pramsey
     Type:  defect         |     Status:  new
 Priority:  critical       |  Milestone:  PostGIS 3.1.4
Component:  postgis        |    Version:  3.1.x
 Keywords:  ST_AsMVT       |
---------------------------+---------------------------
 ST_AsMVT can encode row columns as feature attributes as well as keys and
 values in a JSONB column in the row data. It cannot handle the case where
 JSONB object contains a key with the name of a column properly. Returned
 MVT is formatted properly but some feature attributes are missing or
 shifted (values assigned to wrong keys).

 The following query can be used to reproduce the issue. Please note that
 `field1` exists as a column and a key in the JSONB column.

 {{{
 WITH my_data AS (
     SELECT ST_AsMVTGeom(
         ST_GeomFromText('POLYGON ((0 0, 10 0, 10 5, 0 -5, 0 0))'),
         ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)),
         4096, 0, false
     ) AS geometry, 'ffffffffffffffffffff' AS id, 'value1' AS field1,
     '{"field1":"value1", "field2":"value2", "field3":"value3",
 "field4":"value4", "field5":"value5", "_field6":"value6",
 "_field7":"_value7", "_field8":"_value8"}'::JSONB
 ) SELECT ST_AsMVT(q,'my_layer',4096,'geometry') AS data
 FROM (SELECT * FROM my_data) AS q
 }}}

 This query encodes feature attributes as follows.

 {{{
 VectorTileFeature {
   properties:{
      id: 'ffffffffffffffffffff',
      field1: 'value1',
      undefined: undefined,
      field2: 'value2',
      field4: 'value4',
      field5: 'value5',
      _field6: 'value6',
      _field8: '_value8'
   },
   ...
   _keys: [
      'id',
      'field1',
      'field2',
      'field3',
      'field4',
      'field5',
      '_field6',
      '_field7',
      '_field8'
   ],
   _values: [
      'ffffffffffffffffffff',
      'value1',
      'value2',
      'value3',
      'value4',
      'value5',
      'value6',
      '_value7',
      '_value8'
   ]
 }
 }}}

 As it can be seen from the result, although all keys and values exist in
 the resulting data, attributes are not encoded properly (`_field7` and
 `field3` missing). I also came across where attributes are shifted (wrong
 values are assigned to keys) but could not reproduce the issue with this
 query.

 In my opinion, either JSONB or row columns should have the higher
 precedence and overwrite the other. And, this information should be
 documented in the related docs.

 Version:

 {{{
 POSTGIS="3.1.1 0" [EXTENSION] PGSQL="120" GEOS="3.8.1-CAPI-1.13.3"
 PROJ="7.1.1" LIBXML="2.9.10" LIBJSON="0.15" LIBPROTOBUF="1.3.3"
 WAGYU="0.5.0 (Internal)" TOPOLOGY
 }}}

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4973>
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.