SpatiaLite views in QGIS are not being mapped correctly

Question

I've created a view in SpatiaLite, the unique id is made with

cast(row_number() over() as integer) as id

the geometry is made with

CastToMultiPolygon(st_union(geom)) as geom (it's a merging operation)

and the geometry is registered with

INSERT INTO "main"."geometry_columns" ("f_table_name", "f_geometry_column", "geometry_type", "coord_dimension", "srid", "spatial_index_enabled") VALUES ('table1', 'geom', '6', '2', '27700', '0');

The layer shows in the QGIS canvas and all the features show in the attribute table, but selecting features is messed up. there appears to be no relationship between the canvas location and the feature selected - I click on a feature on the canvas and a completely different feature somewhere else is selected.

On trying to zoom to the feature from the attribute table I get

WARNING Zoom to feature id failed : Feature not found

and selection yields the spatialite error

WARNING SQLite error: no such column: ROWID SQL: SELECT 0,"description" FROM "table1" WHERE ROWID=7

As a test, I changed the query to declare the id column as "ROWID"

cast(row_number() over() as integer) as ROWID

then when I try to load the layer it won't load at all, complaining...

2024-07-26T13:10:36 WARNING SQLite error: no such column: id SQL: PRAGMA table_info("table1")

It seems the I'm not successfully telling QGIS what the unique id column is, but I cannot find any way to do so.