Igor Babaev (Inactive) added a comment - 2024-10-12 03:05 If we use table t2 similar to table t1 and with the same contents in the grouping derived table CREATE TABLE t2 ( i int , KEY (i) ) engine=innodb; INSERT INTO t2 VALUES (1), (2); INSERT INTO t1 SELECT a.i FROM ( SELECT i FROM t2 GROUP BY i) a, ( SELECT i FROM t1) c WHERE c.i = a.i; we observe the same kind of crash. If we add more rows to table t2 in the following way: INSERT INTO t2 SELECT i FROM t2; INSERT INTO t2 SELECT i+2 FROM t2; INSERT INTO t2 SELECT i+4 FROM t2; ANALYZE TABLE t2 PERSISTENT FOR ALL ; we have the same crash for both InnoDB and MyISAM engines.

Igor Babaev (Inactive) added a comment - 2024-10-12 03:36 - edited If we introduce a new flag TABLE_LIST::is_forced_to_be_materialized used for derived tables and views with is set to TRUE when a mergeable derived table / view is forced to be materialized and utilize this flag in Item_direct_view_ref::used_tables() in the the following way: diff --git a/sql/item.cc b/sql/item.cc index 9ba0094..3dd7ffe 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -10939,6 +10939,9 @@ table_map Item_direct_view_ref::used_tables() const if (get_depended_from()) return OUTER_REF_TABLE_BIT; + if (view->is_forced_to_be_materialized) + return (*ref)->used_tables(); + if (view->is_merged_derived() || view->merged || !view->table) { table_map used= (*ref)->used_tables(); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 8738f18..9eb100a 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1179,6 +1179,7 @@ TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, ("convert merged to materialization to resolve the conflict")); derived->change_refs_to_fields(); derived->set_materialized_derived(); + derived->is_forced_to_be_materialized= true; goto retry; } } diff --git a/sql/table.h b/sql/table.h index 6910ff0..79e789c 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2611,6 +2611,8 @@ struct TABLE_LIST bool merged; bool merged_for_insert; bool sequence; /* Part of NEXTVAL/CURVAL/LASTVAL */ + /* TRUE <=> materialization of derived tbale / view has been forced */ + bool is_forced_to_be_materialized; /* Items created by create_view_field and collected to change them in case then we have the following results for both InnoDB and MyISAM engines: MariaDB [test]> EXPLAIN EXTENDED INSERT INTO t1 SELECT a.i FROM (SELECT i FROM t2 GROUP BY i) a, (SELECT i FROM t1) c WHERE c.i = a.i; +------+-----------------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-----------------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | c.i | 2 | 100.00 | | | 3 | DERIVED | t1 | index | NULL | i | 5 | NULL | 2 | 100.00 | Using index | | 2 | LATERAL DERIVED | t2 | ref | i | i | 5 | c.i | 2 | 56.25 | Using index | +------+-----------------+------------+-------+---------------+------+---------+------+------+----------+-------------+ 4 rows in set, 1 warning (0.008 sec)   MariaDB [test]> INSERT INTO t1 SELECT a.i FROM (SELECT i FROM t2 GROUP BY i) a, (SELECT i FROM t1) c WHERE c.i = a.i;ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 3 Current database: test   Query OK, 2 rows affected (0.018 sec) Records: 2 Duplicates: 0 Warnings: 0   MariaDB [test]> SELECT * FROM t1; +------+ | i | +------+ | 1 | | 1 | | 2 | | 2 | +------+ 4 rows in set (0.001 sec) It can be checked that after this patch is applied all mtr tests from the main suite pass (with --ps-protocol and --view-protocol as well)

Igor Babaev (Inactive) added a comment - 2024-10-12 04:48 - edited It's easy to see that forced materialization is required not only in the function find_dup_table(), but in other functions too. To cover other cases it makes sense to set the flag TABLE_LIST::is_forced_to_be_materialized to TRUE in the function TABLE_LIST::change_refs_to_fields(): --- a/sql/item.cc +++ b/sql/item.cc @@ -10939,6 +10939,9 @@ table_map Item_direct_view_ref::used_tables() const if (get_depended_from()) return OUTER_REF_TABLE_BIT; + if (view->is_forced_to_be_materialized) + return (*ref)->used_tables(); + if (view->is_merged_derived() || view->merged || !view->table) { table_map used= (*ref)->used_tables(); diff --git a/sql/table.cc b/sql/table.cc index 74b478c..7d8eca1 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -9627,6 +9627,8 @@ bool TABLE_LIST::change_refs_to_fields() (Item*)(materialized_items + idx)); } + is_forced_to_be_materialized= true; + return FALSE; } diff --git a/sql/table.h b/sql/table.h index 6910ff0..454d40d 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2611,6 +2611,8 @@ struct TABLE_LIST bool merged; bool merged_for_insert; bool sequence; /* Part of NEXTVAL/CURVAL/LASTVAL */ + /* TRUE <=> materialization of derived tbale / view is to be forced */ + bool is_forced_to_be_materialized; /* Items created by create_view_field and collected to change them in case This patch doesn't break any mtr test from the main suite either and it fixes the bug.

Igor Babaev (Inactive) added a comment - 2024-10-12 05:04 - edited The second execution of the problematic INSERT also works as expected: MariaDB [test]> PREPARE stmt FROM " INSERT INTO t1 SELECT a.i FROM (SELECT i FROM t2 GROUP BY i) a, (SELECT i FROM t1) c WHERE c.i = a.i; "; Query OK, 0 rows affected (0.002 sec) Statement prepared   MariaDB [test]> EXECUTE stmt; Query OK, 2 rows affected (0.009 sec) Records: 2 Duplicates: 0 Warnings: 0   MariaDB [test]> SELECT * FROM t1; +------+ | i | +------+ | 1 | | 1 | | 2 | | 2 | +------+ 4 rows in set (0.001 sec)   MariaDB [test]> EXECUTE stmt; Query OK, 4 rows affected (0.010 sec) Records: 4 Duplicates: 0 Warnings: 0   MariaDB [test]> SELECT * FROM t1; +------+ | i | +------+ | 1 | | 1 | | 1 | | 1 | | 2 | | 2 | | 2 | | 2 | +------+ 8 rows in set (0.002 sec)

Igor Babaev (Inactive) added a comment - 2024-10-12 06:08 oleg.smirnov if you agree with my analysis and suggestions please construct a test case when forced materialization is used for a mergeable derived table whose specification is a join with more than 62 tables.