The rowid is always available as an undeclared column named ROWID, OID, or _ROWID_Īs long as those names are not also used by explicitly declared columns. Now we'll read yet again the above paragraph taken from SQLite's own documentation, this time paying the due attention to any single world: The recently discovered issue is even worst (if possible) than the previous one: it will not simply cause a severe Spatial Index corruption, it could eventually transform any SQL query attempting to access the Spatial Index into a deadly slow query. If you missed this critical point, reading this old technical note is warmly suggested. You'll probably be well aware that not declaring an INTEGER PRIMARY KEY supporting any Geometry table could directly cause many severe Spatial Index releted headaches, e.g. If (for any reason) the above assumption is no longer satisfied then the Spatial Index will simply return unreliable and useless results. The SpatialLite's own implementation of the Spatial Index widely depends on ROWIDs in order to JOIN both the main table containing Geometries and the corresponding R*Tree table.Īnyway there is a dangerous hidden trap in all this: it works well only since ROWID values returned by the main table do exactly correspond to ROWID values stored into the R*Tree. Using ROWID values as relational keys whenever is possible is really useful in many SQL queries, because relational JOIN ops based on ROWIDs always are very efficient and fast to be resolved. In other words: a ROWID represents a Unique Identifier allowing to retrieve every individual row from a table in a very quick time. If the table has a column of type INTEGER PRIMARY KEY then that column is another Long as those names are not also used by explicitly declared columns. The rowid is always available as an undeclared column named ROWID, OID, or _ROWID_ as So you are kindly invited to carefully read this technical note.Įach entry in an SQLite table has a unique 64-bit signed integer key called the 'rowid'.

The specific problem causing this issue seems to have a very low probability to happen under normal conditions anyway, once this problem will actually arise the consequences could easily have a really strong negative impact on the overall performance of SpatiaLite. If you assume that your INTEGER PRIMARY KEY maps to an array, then how do you deal with deleting 'element 0'? You will have to renumber all your rows (INTEGER PRIMARY KEY) to maintain the mapping.Broken R*Tree caused by shadowed ROWID issuesĪ new issue affecting the R*Tree Spatial Index has been recently reported. It would be prudent to avoid assigning artificial overloads of meaning unless the can be held invariant for all eternity. This holds notwithstanding the initial value.Īny other meaning is merely an artificial overload of meaning assigned by the designer. Given how these number are auto-generated if the INTEGER PRIMARY KEY is entirely and always computer generated and has not been futzed with (updated/changed/assigned) by an external source, and the number of insertions has not overflowed a signed 64-bit integer, that a numerically greater INTEGER PRIMARY KEY indicates that particular 'row' (tuple) was inserted into the relation subsequently to the insertion of any 'row' (tuple) in that relation with a lesser INTEGER PRIMARY KEY. The only 'meaning' that can be attributed to an INTEGER PRIMARY KEY column is that it uniquely identifies a particular 'row' (tuple) in the 'table' (relation).

Sqlite rowid