Why is spatialite returning incorrect total_changes() - when I'm making NO row changes?

Question

You can read about total_changes() here: https://www.sqlite.org/lang_corefunc.html

I saw the issue while using a python interface to spatialite (pyspatialite) but can reproduce the issue with the windows spatialite.exe. Notice how spatialite.exe total_changes is not 0 as expected and increments with each select statement - weird. It should only increment for updates, inserts and deletes. Notice also that sqlite gets it right. Any suggestions appreciated?

    C:\crs\bde2>spatialite test6.db
    SpatiaLite version ..: 4.2.0    Supported Extensions:
            - 'VirtualShape'        [direct Shapefile access]
            - 'VirtualDbf'          [direct DBF access]
            - 'VirtualXL'           [direct XLS access]
            - 'VirtualText'         [direct CSV/TXT access]
            - 'VirtualNetwork'      [Dijkstra shortest path]
            - 'RTree'               [Spatial Index - R*Tree]
            - 'MbrCache'            [Spatial Index - MBR cache]
            - 'VirtualSpatialIndex' [R*Tree metahandler]
            - 'VirtualXPath'        [XML Path Language - XPath]
            - 'VirtualFDO'          [FDO-OGR interoperability]
            - 'VirtualGPKG' [OGC GeoPackage interoperability]
            - 'VirtualBBox'         [BoundingBox tables]
            - 'SpatiaLite'          [Spatial SQL - OGC]
    PROJ.4 version ......: Rel. 4.8.0, 6 March 2012
    GEOS version ........: 3.4.2-CAPI-1.8.2 r3921
    LWGEOM version ......: 2.1.3
    TARGET CPU ..........: mingw32
    SQLite version ......: 3.8.5
    Enter ".help" for instructions
    SQLite version 3.8.5 2014-06-04 14:06:34
    Enter ".help" for instructions
    Enter SQL statements terminated with a ";"
    spatialite>
    spatialite>
    spatialite> SELECT total_changes() AS 'Total changes';
    1
    spatialite> SELECT total_changes() AS 'Total changes';
    3
    spatialite> .quit

    C:\crs\bde2>sqlite3 test6.db
    SQLite version 3.8.7 2014-10-17 11:24:17
    Enter ".help" for usage hints.
    sqlite>
    sqlite> select total_changes() as 'Total changes';
    0
    sqlite> select total_changes() as 'Total changes';
    0

Answer 1

The documentation says:

The count returned by sqlite3_total_changes() includes all changes from all trigger contexts and changes made by foreign key actions.

SpatiaLite uses lots of triggers, and user-defined functions that run SQL commands.