Virtual layer not loaded correctly after re-opening a project

[koenigherford]

What is the bug or the crash?

A virtual table (with attributes as expected) is not loaded correcly after closing and new opening of project again, when the virtual table is created from a second virtuaI table which has a join to another layer in layer-properties.

(In my original project I made the join by layer properties and not by sql-query because the joined layer contains a virtual field with array-functions (which can not be made with sqlite-sql-query) - in this example the join might be made in another way before / in the sql-query but this nonsens-example was made to show the issue with simpler code).

Before closing the project the virtual layer has a correct, as-expected attribute-table. After reopening the project the "Handling unavailable layers"-dialog occurs for this virtual layer. If you keep the unavailable layer and go to Layer->Create layer->New Virtual Layer -> Add "unavailable layer"-definition as stored in project you will be able to recreate the attribute table as expected.

Because of convinience and issue #46831 it is not a good way to recreate the unavailable virtual layer every time after reopening the project.

(In my original-project I saw in some cases that
1)
the whole layer was unavailable (as described before)

or
2)
the virtual layer (which contained "group"-functions) could be loaded - but contained only a reduced number of fields (just them which were used to group the rows). All "min(my_field) my_field"-fields were not in the table again after reopening the project. This could not be reproduced - but may be related to 1)
)

I attached a small example-project.
test_project.zip

Steps to reproduce the issue

Use test-project
test_project.zip

or

New project

2.1.) Insert data table (table "test_data_vl")
2.2.) Insert table for join to virtual layer (table "types") in step 4)

create a virtual layer "test_virtual_layer_1" based on table 2.1 and add it to the project

create a join between "test_virtual_layer_1" and table 2.2 ("types") in layer-properties

create a virtual layer "test_virtual_layer_2" based on table 2.2 and add it to the project

Check the attribute table of "test_virtual_layer_2"

Save, close and reopen project

Keep unavailable layer

Select unavailable layer and Layer->Create Layer->New virtual layer ->add "test_virtual_layer_2"(unavailable layer) (with code as stored in project)

Check attribute table of "test_virtual_layer_2" again

Versions

<style type="text/css"> p, li { white-space: pre-wrap; } </style>

QGIS version	3.22.2-Białowieża	QGIS code revision	1601ec4
Qt version	5.15.2
Python version	3.9.5
GDAL/OGR version	3.4.0
PROJ version	8.2.0
EPSG Registry database version	v10.038 (2021-10-21)
GEOS version	3.10.0-CAPI-1.16.0
SQLite version	3.35.2
PDAL version	2.3.0
PostgreSQL client version	13.0
SpatiaLite version	5.0.1
QWT version	6.1.3
QScintilla2 version	2.11.5
OS version	Windows 10 Version 2009
Active Python plugins
db_manager	0.1.20
grassprovider	2.12.99
MetaSearch	0.3.5
processing	2.12.99
sagaprovider	2.12.99
qfieldsync	v3.4.4

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

No response

[elpaso]

It's the joined field that when the layer is loaded is not yet resolved.