What is the bug or the crash?

The QGIS SpatiaLite data provider incorrectly caches the view schema from the underlying joined tables instead of using the actual view definition.

When loading a SpatiaLite view that JOINs multiple tables, the provider returns ALL columns from ALL joined tables, not just the columns defined in the view's
SELECT statement.

Example: A view defined with SELECT a.id, a.name, b.field_b1 FROM table_a a JOIN table_b b... (3 columns) returns all columns from both table_a AND table_b
(potentially 100+ columns).

The OGR provider does NOT have this bug - it correctly returns only the columns defined in the view.

SpatiaLite provider - BUG: returns wrong schema

uri = QgsDataSourceUri()
uri.setDatabase('/path/to/db.sqlite')
uri.setDataSource('', 'my_view', 'the_geom', '', 'ROWID')
layer = QgsVectorLayer(uri.uri(), '', 'spatialite')
print(layer.fields().count()) # Returns 100+ (WRONG - should be 25)

OGR provider - CORRECT behavior

layer = QgsVectorLayer('/path/to/db.sqlite|layername=my_view', '', 'ogr')
print(layer.fields().count()) # Returns 25 (CORRECT)

Steps to reproduce the issue

1. Create a SpatiaLite database with two tables:
   CREATE TABLE geometry_table (
   gid INTEGER PRIMARY KEY,
   the_geom GEOMETRY,
   site_name TEXT,
   area_code TEXT
   );
   SELECT AddGeometryColumn('geometry_table', 'the_geom', 4326, 'POLYGON', 'XY');

CREATE TABLE data_table (
id INTEGER PRIMARY KEY,
site TEXT,
area TEXT,
field1 TEXT,
field2 TEXT,
field3 TEXT,
-- ... many more fields
field50 TEXT
);

2. Create a view that selects only specific columns:
   CREATE VIEW test_view AS
   SELECT g.gid, g.the_geom, g.site_name, d.field1, d.field2
   FROM geometry_table g
   JOIN data_table d ON g.site_name = d.site AND g.area_code = d.area;

3. Verify the view schema directly with SQLite:
   PRAGMA table_info(test_view);
   -- Returns: gid, the_geom, site_name, field1, field2 (5 columns - CORRECT)

4. Open QGIS and go to Layer → Add Layer → Add SpatiaLite Layer

5. Connect to the database and add the view test_view

6. Open the Attribute Table or check layer.fields() in Python console

7. BUG: The layer shows ALL columns from BOTH geometry_table AND data_table (50+ columns), instead of just the 5 columns defined in the view

8. Verify with OGR: In Python console:
   layer = QgsVectorLayer('/path/to/db.sqlite|layername=test_view', 'test', 'ogr')
   print([f.name() for f in layer.fields()])

Shows only 5 fields (CORRECT)

Versions

QGIS version: 3.42.1-Münster
QGIS code revision: 1fc5283

Qt version: 5.15.2
Python version: 3.9.5
GDAL version: 3.3.2
PROJ version: 8.1.1
EPSG Registry version: v10.028 (2021-07-07)
GEOS version: 3.9.1-CAPI-1.14.2
SQLite version: 3.35.2
PDAL version: 2.3.0
PostgreSQL client version: 12.3
SpatiaLite version: 5.0.1
QWT version: 6.1.6
QScintilla2 version: 2.11.5
OS version: macOS 26.2

Supported QGIS version

• I'm running a supported QGIS version according to the roadmap.

New profile

• I tried with a new QGIS profile

Additional context

Impact:

• Empty/NULL fields may display the column name as their value (because provider maps wrong columns)
• Views with JOINs become unreliable with the SpatiaLite provider
• Performance degradation due to loading unnecessary columns
• Incorrect attribute values in feature info and attribute tables

Root cause hypothesis:
The SpatiaLite provider seems to retrieve column information from the underlying base tables (possibly via geometry_columns metadata) rather than parsing the
actual view definition or using PRAGMA table_info(view_name).

Workaround:
Use OGR provider instead of SpatiaLite provider for loading views:

Instead of spatialite provider

layer = QgsVectorLayer('/path/to/db.sqlite|layername=view_name', 'layer_name', 'ogr')

Note: This bug was discovered while developing the PyArchInit archaeological plugin, but it is NOT a plugin bug - it's a core QGIS SpatiaLite provider issue
reproducible without any plugins.