Can not save data of type "array of characters (StringList / JSONSTRINGLIST) "on a layer connected to a spatialLite base : error : Type 5 of the x attribute of the entity y is unknown

[qgib]

Author Name: Mickaël CALLAC (Mickaël CALLAC) Original Redmine Issue: 21986 Affected QGIS version: 3.4.7 Redmine category:data_provider/spatialite Assignee: Alessandro Pasotti

---

Test environment: QGis 3.4.7-1 (LTR) database: spatiaLite

The error appears in the case of a layer connected to a base SpatiaLite (generated by ogr2ogr) and for an array type field characters. The field in QGis has been set as "value relational ", the option" Allow multiple selections "is checked.

The field in QGis has the following properties:

Type: QString Identity type: StringList This is a field of type "JSONSTRINGLIST" in the base spatiaLite When saving this field, the following error appears: 2019-05-03T11: 39: 48 CRITICAL Layer name layer: Type 5 of the x attribute of the entity y is unknown.

---

• base.zip (Mickaël CALLAC)

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Did it worked as expected in a previous QGIS release?

---

• status_id was changed from Open to Feedback

[qgib]

Author Name: Mickaël CALLAC (Mickaël CALLAC)

---

Yes, it worked well in QGis 2.18, since the version 3 it didn't work.

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• priority_id was changed from Normal to High
• status_id was changed from Feedback to Open

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

• assigned_to_id was configured as Alessandro Pasotti

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

Is it the attached file a direct ogr2ogr output?

The encoded jsonarrays look wierd to me:

this is not valid json:

{EC,ER,MA,PH}

it should probably be

["EC","ER","MA","PH"]

[qgib]

Author Name: Mickaël CALLAC (Mickaël CALLAC)

---

The dataBase SpatiaLite output is an export from a DB PostGis by ogr2ogr. The fields in PostGis are typed in array (character varying []) and contains value which look like {EC,ER,MA,PH}, ogr2ogr keep the same format in the spatiaLite database.

[elpaso]

I'm afraid this is an upstream bug then: if ogr2ogr automatically create a JSONARRAY field in the destination DB (spatialite) it should also convert values to a valid JSON. @rouault what do you think?

[mcallac]

Hello, I wanted to know if you had news about this bug. Thanks for your feedback

[rouault]

I just skimmed quickly through this issue, but from a quick testing, I don't think there's an issue on OGR side See

$ cat test.py
from osgeo import ogr
ds = ogr.GetDriverByName('SQLite').CreateDataSource('testjsonlist.db')
lyr = ds.CreateLayer('test')
lyr.CreateField(ogr.FieldDefn('foo', ogr.OFTStringList))
f = ogr.Feature(lyr.GetLayerDefn())
f['foo'] = ['bar', 'baz']
lyr.CreateFeature(f)
ds = None

# Create a SQLite file with a StringList column
$ python test.py

# Dump it: uses proper JSON encoding
$ echo ".dump" | sqlite3 testjsonlist.db
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE geometry_columns (     f_table_name VARCHAR,      f_geometry_column VARCHAR,      geometry_type INTEGER,      coord_dimension INTEGER,      srid INTEGER,     geometry_format VARCHAR );
INSERT INTO "geometry_columns" VALUES('test','GEOMETRY',0,2,NULL,'WKB');
CREATE TABLE spatial_ref_sys        (     srid INTEGER UNIQUE,     auth_name TEXT,     auth_srid TEXT,     srtext TEXT);
CREATE TABLE 'test' ( "ogc_fid" INTEGER PRIMARY KEY AUTOINCREMENT, 'GEOMETRY' BLOB, 'foo' JSONSTRINGLIST);
INSERT INTO "test" VALUES(1,NULL,'[ "bar", "baz" ]');
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('test',1);
COMMIT;

# Read back file with ogrinfo
$ ogrinfo testjsonlist.db -al
INFO: Open of `testjsonlist.db'
      using driver `SQLite' successful.

Layer name: test
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
(unknown)
FID Column = ogc_fid
Geometry Column = GEOMETRY
foo: StringList (0.0)
OGRFeature(test):1
  foo (StringList) = (2:bar,baz)

# Convert it into Postgres
$ ogr2ogr -update pg:dbname=autotest testjsonlist.db -overwrite

# Check with ogrinfo Postgres content
$ ogrinfo  pg:dbname=autotest test
INFO: Open of `pg:dbname=autotest'
      using driver `PostgreSQL' successful.

Layer name: test
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
(unknown)
FID Column = ogc_fid
Geometry Column = geometry
foo: StringList (0.0)
OGRFeature(test):1
  foo (StringList) = (2:bar,baz)

# Display the raw encoding of the Postgres table
$ psql -d autotest -c "select * from test"
 ogc_fid |    foo    | geometry 
---------+-----------+----------
       1 | {bar,baz} | 
(1 row)

# Convert the Postgres table to SQLite
$ ogr2ogr out2.db pg:dbname=autotest test

# Display the raw content of the generated sqlite3 file
$ echo ".dump" | sqlite3 out2.db
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE geometry_columns (     f_table_name VARCHAR,      f_geometry_column VARCHAR,      geometry_type INTEGER,      coord_dimension INTEGER,      srid INTEGER,     geometry_format VARCHAR );
INSERT INTO "geometry_columns" VALUES('test','geometry',0,2,NULL,'WKB');
CREATE TABLE spatial_ref_sys        (     srid INTEGER UNIQUE,     auth_name TEXT,     auth_srid TEXT,     srtext TEXT);
CREATE TABLE 'test' ( "ogc_fid" INTEGER PRIMARY KEY AUTOINCREMENT, 'geometry' BLOB, 'foo' JSONSTRINGLIST);
INSERT INTO "test" VALUES(1,NULL,'[ "bar", "baz" ]');
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('test',1);
COMMIT;

[elpaso]

@rouault thank you for checking. I'll check if is there anything wrong in QGIS, but I think that the root cause is a corrupted dataset: it contains invalid JSON in a JSONSTRINGLIST field.

[elpaso]

This is not a QGIS issue: I confirm https://github.com/qgis/QGIS/issues/29801#issuecomment-495907467