Oleg Smirnov added a comment - 2024-01-10 13:35 The problem seems to be related to the unsuccessful merging of view v2 . After the parsing stage dbug_print(select_lex) prints the following statement: "select dt.b AS b from (select v2.b + 200 AS b from (((((((v1 s1 left join v1 s2 on(s2.a = s1.a)) left join v1 s3 on(s3.a = s2.a)) left join v1 s4 on(s4.a = s3.a)) left join v1 s5 on(s5.a = s4.a)) left join v1 s6 on(s6.a = s5.a)) left join v1 s7 on(s7.a = s6.a)) left join v2 on(v2.a = s7.a)) where s1.a > 1) dt" At 1st execution mysql_derived_prepare() sets flag "mergeable" for v2 and creates the field translation table. After names resolution select_lex looks like "select t9.b + 100 + 200 AS b from (select t9.b + 100 + 200 AS b from (((((((v1 s1 left join v1 s2 on(t8.a = t8.a)) left join v1 s3 on(t8.a = t8.a)) left join v1 s4 on(t8.a = t8.a)) left join v1 s5 on(t8.a = t8.a)) left join v1 s6 on(t8.a = t8.a)) left join v1 s7 on(t8.a = t8.a)) left join v2 on(t8.a" = t8.a)) where t8.a > 1) dt" Note the t9.b in the select lists. It's the reference that was translated from v2.b as the view v2 is expected to be merged. At the optimization stage we hit the following limitation: mysql_derived_merge() /* Check whether there is enough free bits in table map to merge subquery. If not - materialize it. This check isn't cached so when there is a big and small subqueries, and the bigger one can't be merged it wouldn't block the smaller one. */ if (parent_lex->get_free_table_map(&map, &tablenr) || dt_select->leaf_tables.elements + tablenr > MAX_TABLES) { /* There is no enough table bits, fall back to materialization. */ cause= "Not enough table bits to merge subquery"; goto unconditional_materialization; } Then the next code block works which rolls back the preparation for merging and sets the view v2 to "materialized": mysql_derived_merge() unconditional_materialization: derived->change_refs_to_fields(); derived->set_materialized_derived(); if (!derived->table || !derived->table->is_created()) res= mysql_derived_create(thd, lex, derived); goto exit_merge; After derived->change_refs_to_fields() select_lex looks as follows (only first 300 characters shown): "select v2.b + 200 AS b from (select v2.b + 200 AS b from (((((((((((((((t1 left join t2 on(t2.a = t1.a)) left join t3 on(t3.a = t2.a)) left join t4 on(t4.a = t3.a)) left join t5 on(t5.a = t4.a)) left join t6 on(t6.a = t5.a)) left join t7 on(t7.a = t6.a)) left join t8 on(t8.a = t7.a))) left join ((((" There is again v2.b in the select lists, which is correct. But the merging of derived tables and views is performed only during the first execution: JOIN::optimize_inner() if (select_lex->first_cond_optimization) { //Do it only for the first execution /* Merge all mergeable derived tables/views in this SELECT. */ if (select_lex->handle_derived(thd->lex, DT_MERGE)) DBUG_RETURN(TRUE); } So at the 2nd execution we lack the call of derived->change_refs_to_fields() and end up have incorrect references to t9.b in the select lists: "select t9.b + 100 + 200 AS b from (select t9.b + 100 + 200 AS b from (((((((v1 s1 left join v1 s2 on(t8.a = t8.a)) left join v1 s3 on(t8.a = t8.a)) left join v1 s4 on(t8.a = t8.a)) left join v1 s5 on(t8.a = t8.a)) left join v1 s6 on(t8.a = t8.a)) left join v1 s7 on(t8.a = t8.a)) left join v2 on(t8.a" = t8.a)) where t8.a > 1) dt" As a result, there are incorrect table_maps and a crash at table elimination (which is not related to the elimination itself, however)

Oleg Smirnov added a comment - 2024-01-13 15:07 - edited igor , can you please review the proposed solution in branch bb-10.4-mdev-33081 ? commit 69bd5437c45490a53f075f8ac17744a3d9b31ccd (HEAD -> bb-10.4-MDEV-33081) Author: Oleg Smirnov <olernov@gmail.com> Date: Sat Jan 13 17:32:16 2024 +0700   MDEV-33081 Crash on second execution of PS for query over many views On the preparation phase a view or derived table can be marked as mergeable, and the appropriate field_translation table will be set up for it. But the merging itself may fail due to lack of free bits in table map (see mysql_derived_merge()). In such a case the merging is cancelled, and the view/DT falls back to materialization. But the field_translation table persists, and at 2nd execution of a prepared statement the name resolution procedure resolves names incorrectly using the outdated field_translation table. The solution is to reset field_translation to NULL if the merging of a DT/view fails. However, there are two exceptions when field_translation is used later at the execution phase: - pushdown of a DT to an external storage engine - statements looking like INSERT INTO t1 (SELECT tmp.a FROM (select * FROM t1) as tmp) In such cases field_translation must not be reset

Igor Babaev (Inactive) added a comment - 2024-01-14 02:24 An explanation of the proper second execution of the reported PS with set optimizer_switch='table_elimination=off' is requested.

Oleg Smirnov added a comment - 2024-01-15 10:04 The code outside the table elimination turns out to be tolerant to such inconsistency, at least for the given test case. But I've managed to reproduce the error with the table elimination turned off. Look at the modification of your original test case : # <... this part is unchanged ...>   CREATE VIEW v2 AS SELECT t8.a as b, t9.b as a FROM t1 LEFT JOIN t2 ON t2.a = t1.a LEFT JOIN t3 ON t3.a = t2.a LEFT JOIN t8 ON t8.a = t3.a LEFT JOIN t9 ON t9.a = t8.a;   SET optimizer_switch = 'table_elimination=off';   let $q= SELECT dt.b FROM ( SELECT v2.b + 200 as b FROM v1 AS s1 LEFT JOIN v1 AS s2 ON s2.a = s1.a LEFT JOIN v1 AS s3 ON s3.a = s2.a LEFT JOIN v1 AS s4 ON s4.a = s3.a LEFT JOIN v1 AS s5 ON s5.a = s4.a LEFT JOIN v1 AS s6 ON s6.a = s5.a LEFT JOIN v1 AS s7 ON s7.a = s6.a LEFT JOIN v2 ON v2.a = s7.a WHERE s1.a > 1 ) dt;   --echo # Sample result (regular statement) eval $q;   eval PREPARE stmt FROM "$q"; EXECUTE stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; The correct result is NULL, but the non-patched 10.4 outputs 202 at the second execution of the PS. At the same time, the patched version produces NULL at both executions. This test case can also be added to the commit if the patch is accepted.

Igor Babaev (Inactive) added a comment - 2024-01-18 19:55 This bug will be fixed when a fix for MDEV-30073 is pushed into 10.4