Bug report #14999

Spatialite provider does not support alphanumeric primary keys

Added by Frank Donnelly almost 8 years ago. Updated almost 8 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/SpatiaLite
Affected QGIS version:2.14.3 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:duplicate
Crashes QGIS or corrupts data:No Copied to github as #:22946

Description

I'm using QGIS 2.14 Essen on a Windows 7 machine (specifically 2.14.3). If I connect to a Spatialite database, add a layer to my layers panel, and open the attribute table of the layer, QGIS populates all the cell values with the word ERROR. If I sort the table, QGIS populates all the cells with the values of one record.

However:

1. If I use the Identify Feature tool, I can click on a feature and successfully view its attributes
2. If I use the Database Manager, I can view the entire table and it renders perfectly
3. If I use another program (Spatialite GUI, ArcGIS) I can see the attributes just fine
4. If I use an older version of QGIS - 2.8 Wien - the error doesn't occur

I've noticed that this only happens when a layer's unique ID is a text value that contains alpha characters. If the layer has an integer ID, or has a text ID where all the characters are numeric, the error doesn't occur. But this shouldn't be happening - all of the layers have a unique rowid integer column that Spatialite/SQLite creates by default, and if the attribute table viewer needs a unique numeric id to function it should look at that rowid column.

Sample database file is attached. Train station layer fails (text id, mix of alpha and numeric). Subway station layer partially fails (text id, ids that are numeric work, alpha and numeric fails). Borough layer works fine (text id, all numeric).

qgis_attribute_error.zip (565 KB) Frank Donnelly, 2016-06-10 01:22 PM


Related issues

Duplicates QGIS Application - Bug report #14575: attribute table errors when spatialite primary key is text Closed 2016-03-27
Duplicates QGIS Application - Bug report #14626: SpatiaLite layer not displaying in QGIS 2.14, works ok in... Closed 2016-04-07

Associated revisions

Revision 69e3c6f7
Added by Even Rouault almost 8 years ago

[Spatialite provider] prefer rowid as primary key where available

Adapted from 1050174532627ae44c4467e9911c1fca41e138e3 without the cleanups.

fixes #14575, fixes #14626, fixes #14999

History

#1 Updated by Matthias Kuhn almost 8 years ago

  • Category changed from Attribute table to Data Provider/SpatiaLite
  • Subject changed from Open Attribute Table for Spatialite Layers Yields Errors to Spatialite provider does not support alphanumeric primary keys

#2 Updated by Even Rouault almost 8 years ago

  • Status changed from Open to Closed
  • Target version set to Version 2.16
  • Resolution set to duplicate

This is a duplicate of #14575 and #14626.

#3 Updated by Frank Donnelly almost 8 years ago

Sorry for the duplication. The target version for this fix is 2.16 - that means this won't be back-ported to a future point release of 2.14? For many who only adopt the LTS releases, this is a make or break issue.

#4 Updated by Even Rouault almost 8 years ago

  • Target version changed from Version 2.16 to Version 2.14

Backported to 2.14

Also available in: Atom PDF