This issue was discovered on 8.0.32, is still present on 8.0.36, and was probably present before 8.0.32 as well.

The issue is reproducible in one of the production environments with large tables. I wasn't able to reproduce it locally, but I investigated the problematic environment, so please see my conclusions and the proposed fix. I hope it will be convincing enough for someone proficient in the optimizer code area :)

How to repeat:
Having a large dataset, execute the problematic CTE query (attached) twice.
It crashes for the 2nd time.

I'm not very familiar with optimizer code, but this is what I've figured out:

For CTE queries, the optimizer can request the creation of a temp table for a particular CTE. If such a temp result is used in table joins, indexes are added to such a table, based on join columns. All possible indexes are added at the 1st stage and then the optimizer decides which of them will be used, removing unused ones. If the CTE temp result is used in joins in other CTEs of the same query, the process of finding indexes is repeated several times.

Indexes are created in TABLE::add_tmp_key() as <auto_keyARRAY_INDEX>, where ARRAY_INDEX is the index in TABLE_SHARE::key_names array. Let's say <auto_key0>, <auto_key1>, <auto_key2> are created. Then the query optimizer decides that only <auto_key2> will be used, so <auto_key0> and <auto_key1> are removed. <auto_key2> is shifted to the 1st unused position (TABLE::move_tmp_key()),
so TABLE_SHARE::key_names contains <auto_key2> on position 0.

Then the query optimizer makes a plan for another join, repeating the above steps. It adds new keys to TABLE_SHARE::key_names and it results with <auto_key2>, <auto_key1>, <auto_key2>. So we've got two <auto_key2> keys, having different definitions.

Then the temp table is created in the InnoDB world (it is big enough, so it doesn't fit TempTable SE)

Then the query is executed. We get to ha_innobase::open() which calls dict_table_get_index_on_name() requesting <auto_key2> (the 2nd one). But the function returns the 1st index.
innobase_match_index_columns() is called to check the consistency between MySql and InnoDB index definition and here we get the message:

The problem is in the optimizer's way of naming auto keys. The current algorithm does not guarantee the uniqueness of key names, causing name clashes in the InnoDB world.

I've attached the anonymized:

1. Query

2. explain for the 1st and 2nd run

3. optimizer traces for the 1st and 2nd run

2nd run was performed with the proposed fix - without this, it just crashes, so there is no way to collect.

What we see is that for the 1st time, the optimizer decided to use <auto_key0> and for the 2nd time it decided to use <auto_key2> (part inside "-> Index lookup on cte2 using" in explain). As it was then shifted to position 0, and later, a new <auto_key2> was added, we got a crash.

Suggested fix:

I assume that the optimizer is OK with making decisions about which key to use, so it is fine that for the 2nd time, it used another key than for the 1st time. The only problem is that the optimizer can generate a table without unique index names, which causes a name clash in InnoDB.

The suggested fix is to guarantee auto-generate key names to be unique.