[MDEV-28965] Assertion failure when preparing UPDATE with derived table in WHERE Created: 2022-06-27  Updated: 2023-03-16  Resolved: 2022-07-12

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Delete, Data Manipulation - Update
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Alice Sherepa Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-28883 Re-design the upper level of handling... Closed

 Description    

CREATE TABLE t1 ( a int);
 
INSERT into t1 values (1),(2),(3);
 
 
 
UPDATE t1 SET a = 1 WHERE a = ( SELECT * FROM (SELECT a FROM t1) dt ) ;

preview-10.10-misc f7d2374083184261897e86bced158

 
mariadbd: /10.10/src/sql/table.cc:7064: void Field_iterator_table_ref::set_field_iterator(): Assertion `table_ref->is_merged_derived()' failed.
 
220627 17:58:30 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.10.0-MariaDB-debug-log
 
 
 
sql/table.cc:7065(Field_iterator_table_ref::set_field_iterator())[0x5560e72a4fce]
 
sql/table.cc:7090(Field_iterator_table_ref::set(TABLE_LIST*))[0x5560e72a52e1]
 
sql/sql_base.cc:8453(insert_fields(THD*, Name_resolution_context*, char const*, char const*, List_iterator<Item>*, bool, unsigned int*, bool))[0x5560e707fd7b]
 
sql/sql_base.cc:7877(setup_wild(THD*, TABLE_LIST*, List<Item>&, List<Item>*, st_select_lex*, bool))[0x5560e707df6e]
 
sql/sql_select.cc:1412(JOIN::prepare(TABLE_LIST*, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5560e718caeb]
 
sql/item_subselect.cc:3929(subselect_single_select_engine::prepare(THD*))[0x5560e760eecd]
 
sql/item_subselect.cc:295(Item_subselect::fix_fields(THD*, Item**))[0x5560e7600c0e]
 
sql/item.h:1144(Item::fix_fields_if_needed(THD*, Item**))[0x5560e6ff9588]
 
sql/item_func.cc:347(Item_func::fix_fields(THD*, Item**))[0x5560e75802c8]
 
sql/item.h:1144(Item::fix_fields_if_needed(THD*, Item**))[0x5560e6ff9588]
 
sql/item.h:1148(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5560e6ff95c1]
 
sql/item.h:1153(Item::fix_fields_if_needed_for_bool(THD*, Item**))[0x5560e708469d]
 
sql/sql_base.cc:8737(setup_conds(THD*, TABLE_LIST*, List<TABLE_LIST>&, Item**))[0x5560e7080df5]
 
sql/sql_select.cc:867(setup_without_group(THD*, Bounds_checked_array<Item*>, TABLE_LIST*, List<TABLE_LIST>&, List<Item>&, List<Item>&, Item**, st_order*, st_order*, List<Window_spec>&, List<Item_window_func>&, bool*, unsigned int*))[0x5560e7189e3f]
 
sql/sql_select.cc:1439(JOIN::prepare(TABLE_LIST*, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5560e718ce45]
 
sql/sql_update.cc:2950(Sql_cmd_update::prepare_inner(THD*))[0x5560e7284ff5]
 
sql/sql_select.cc:30572(Sql_cmd_dml::prepare(THD*))[0x5560e71e0778]
 
sql/sql_select.cc:30625(Sql_cmd_dml::execute(THD*))[0x5560e71e0909]
 
sql/sql_parse.cc:4386(mysql_execute_command(THD*, bool))[0x5560e712ab5d]
 
sql/sql_parse.cc:7797(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x5560e7135ff9]
 
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x5560e71234b9]
 
sql/sql_parse.cc:1405(do_command(THD*, bool))[0x5560e7121e9a]
 
sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x5560e72fc6bb]
 
sql/sql_connect.cc:1314(handle_one_connection)[0x5560e72fc34b]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5560e781306b]
 
nptl/pthread_create.c:478(start_thread)[0x7f19ba846609]
 
 
 
Query (0x7f1988015470): UPDATE t1 SET a = 1 WHERE a = ( SELECT * FROM (SELECT a FROM t1) dt )



 Comments   
Comment by Igor Babaev [ 2022-06-28 ]

The same problem we see for single-table DELETE statements with mergeable derived tables used in their WHERE clauses.

MariaDB [test]> DELETE FROM t1 WHERE t1.a = ( SELECT * FROM (SELECT a FROM t1) dt ) ;
 
ERROR 2006 (HY000): Server has gone away

At the server side we have:

bb-10.10-MDEV-28883/sql/table.cc:7064: void Field_iterator_table_ref::set_field_iterator(): Assertion `table_ref->is_merged_derived()' failed.
 
"mariadbd" received signal SIGABRT, Aborted.

Comment by Igor Babaev [ 2022-07-02 ]

The following patch fixes this problem:

diff --git a/sql/table.cc b/sql/table.cc
 
index f0d5149..e95d36e 100644
 
--- a/sql/table.cc
 
+++ b/sql/table.cc
 
@@ -7059,7 +7059,8 @@ void Field_iterator_table_ref::set_field_iterator()
 
                        table_ref->alias.str));
 
   }
 
   /* This is a merge view, so use field_translation. */
 
-  else if (table_ref->field_translation)
 
+  else if (table_ref->field_translation &&
 
+           !table_ref->is_materialized_derived())
 
   {
 
     DBUG_ASSERT(table_ref->is_merged_derived());
 
     field_it= &view_field_it;

With this patch we have:

MariaDB [test]> UPDATE t1 SET a = 1 WHERE a = ( SELECT * FROM (SELECT a FROM t1) dt );
 
ERROR 1242 (21000): Subquery returns more than 1 row
 
MariaDB [test]> DELETE FROM t1 WHERE t1.a = ( SELECT * FROM (SELECT a FROM t1) dt );
 
ERROR 1242 (21000): Subquery returns more than 1 row

Or, if we force the subquery to return 1 row we have: 

MariaDB [test]> UPDATE t1 SET a = 1 WHERE a = ( SELECT * FROM (SELECT a FROM t1) dt LIMIT 2,1);
 
Query OK, 1 row affected (0.005 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
MariaDB [test]> SELECT * FROM t1;
 
+------+
 
| a    |
 
+------+
 
|    1 |
 
|    2 |
 
|    1 |
 
+------+
 
3 rows in set (0.001 sec)
 
MariaDB [test]> DELETE FROM t1 WHERE t1.a = ( SELECT * FROM (SELECT a FROM t1) dt LIMIT 1);
 
Query OK, 2 rows affected (0.006 sec)
 
MariaDB [test]> SELECT * FROM t1;
 
+------+
 
| a    |
 
+------+
 
|    2 |
 
+------+
 
1 row in set (0.001 sec)

Comment by Igor Babaev [ 2022-07-02 ]

However, if we look at the output of EXPLAIN commands for the queries we see

MariaDB [test]> EXPLAIN UPDATE t1 SET a = 1 WHERE a = ( SELECT * FROM (SELECT a FROM t1) dt LIMIT 2,1);
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
|    1 | PRIMARY     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using where |
 
|    2 | SUBQUERY    | <derived3> | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
 
|    3 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
3 rows in set (0.002 sec)
 
 
 
MariaDB [test]> EXPLAIN DELETE FROM t1 WHERE t1.a = ( SELECT * FROM (SELECT a FROM t1) dt LIMIT 1);
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
|    1 | PRIMARY     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using where |
 
|    2 | SUBQUERY    | <derived3> | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
 
|    3 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+

This is not good because there are no reasons why the mergeable derived tables is not merged here into the embedding subquery.
If we use a replica of table t1 in the derived table then we have a plan without materialization of the derived table:

MariaDB [test]> CREATE TABLE t2 ( a int);                                                       
Query OK, 0 rows affected (0.018 sec)
 
MariaDB [test]> INSERT into t2 values (1),(2),(3);                                                      
Query OK, 3 rows affected (0.006 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> EXPLAIN UPDATE t1 SET a = 1 WHERE a = ( SELECT * FROM (SELECT a FROM t2) dt LIMIT 2,1); 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
|    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using where |
 
|    2 | SUBQUERY    | t2    | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
2 rows in set (0.002 sec)
 
 
 
MariaDB [test]> DELETE FROM t1 WHERE t1.a = ( SELECT * FROM (SELECT a FROM t2) dt LIMIT 1);                 
Query OK, 1 row affected (0.005 sec)
 
 
 
MariaDB [test]> EXPLAIN DELETE FROM t1 WHERE t1.a = ( SELECT * FROM (SELECT a FROM t2) dt LIMIT 1);
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
|    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL | 2    | Using where |
 
|    2 | SUBQUERY    | t2    | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
2 rows in set (0.002 sec)

The same behavior we see before the patch for MDEV-28883.

Comment by Igor Babaev [ 2022-07-02 ]

Interesting that any mergeable derived table in the FROM list of a subquery used in the WHERE condition of multi-table UPDATE/DELETE is always materialized , no matter whether the tables used in the derived table are to be updated or not.

MariaDB [test]> CREATE TABLE t3 (a int);      
Query OK, 0 rows affected (0.020 sec)
 
 
 
MariaDB [test]> INSERT INTO t3 VALUES (0),(1);
 
Query OK, 2 rows affected (0.007 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> EXPLAIN UPDATE t1,t3 SET t1.a = 1 WHERE t1.a=t3.a AND t1.a = ( SELECT * FROM (SELECT a FROM t2) dt LIMIT 2,1);
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
|    1 | PRIMARY     | t3         | ALL  | NULL          | NULL | NULL    | NULL | 2    | Using where |
 
|    1 | PRIMARY     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using where |
 
|    2 | SUBQUERY    | <derived3> | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
 
|    3 | DERIVED     | t2         | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
4 rows in set (0.003 sec)
 
 
 
MariaDB [test]> EXPLAIN UPDATE t1,t3 SET t1.a = 1 WHERE t1.a=t3.a AND t1.a = ( SELECT * FROM (SELECT a FROM t1) dt LIMIT 2,1);
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
|    1 | PRIMARY     | t3         | ALL  | NULL          | NULL | NULL    | NULL | 2    | Using where |
 
|    1 | PRIMARY     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using where |
 
|    2 | SUBQUERY    | <derived3> | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
 
|    3 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
4 rows in set (0.003 sec)
 
 
 
MariaDB [test]> EXPLAIN DELETE FROM t1 USING t1,t3 WHERE t1.a=t3.a AND t1.a = ( SELECT * FROM (SELECT a FROM t2) dt LIMIT 1);
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
|    1 | PRIMARY     | t3         | ALL  | NULL          | NULL | NULL    | NULL | 2    | Using where |
 
|    1 | PRIMARY     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using where |
 
|    2 | SUBQUERY    | <derived3> | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
 
|    3 | DERIVED     | t2         | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
4 rows in set (0.002 sec)
 
 
 
MariaDB [test]> EXPLAIN DELETE FROM t1 USING t1,t3 WHERE t1.a=t3.a AND t1.a = ( SELECT * FROM (SELECT a FROM t1) dt LIMIT 1);
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
|    1 | PRIMARY     | t3         | ALL  | NULL          | NULL | NULL    | NULL | 2    | Using where |
 
|    1 | PRIMARY     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using where |
 
|    2 | SUBQUERY    | <derived3> | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
 
|    3 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
 
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
 
4 rows in set (0.002 sec)

The same behavior we see before the patch for MDEV-28883.

Comment by Igor Babaev [ 2022-07-03 ]

I've noticed that when processing the following query from update_use_source.test

update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1)

the single-table update is processed as a multi-table update where the conversion of IN predicands to semi-joins is supported. The EXPLAIN for this query demonstrates this:

MariaDB [test]> explain update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);    +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
 
|    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL | 8    |                             |
 
|    1 | PRIMARY     | a     | ALL  | NULL          | NULL | NULL    | NULL | 8    | Using where; FirstMatch(t1) |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------

This processing of such queries appeared after the patch for MDEV-12874 : "UPDATE statements with the same source and target".
Unfortunately conversion to semi-join happens only when IN subquery contains the updated table:

MariaDB [test]> create table t2 (old_c1 integer, old_c2 integer,c1 integer, c2 integer, c3 integer);
 
Query OK, 0 rows affected (0.021 sec)
 
 
 
MariaDB [test]> insert into t2 select * from t1;
 
Query OK, 8 rows affected (0.010 sec)
 
Records: 8  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> explain update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t2 a where t1.c1=a.c1);
 
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
 
|    1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL | 7    | Using where |
 
|    2 | DEPENDENT SUBQUERY | a     | ALL  | NULL          | NULL | NULL    | NULL | 8    | Using where |
 
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+

Comment by Igor Babaev [ 2022-07-04 ]

For the delete statement with the same where condition as for the update statement

 
delete from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);

we don't see any conversion of the IN predicate into semi-join either after the commit for
MDEV-28883 or before it. We have

MariaDB [test]> explain delete from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
 
|    1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL | 8    | Using where |
 
|    2 | DEPENDENT SUBQUERY | a     | ALL  | NULL          | NULL | NULL    | NULL | 8    | Using where |
 
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+

I fact the patch for MDEV-12137 : "DELETE statement with the same source and target" never promotes single-table DELETE to MULTI-DELETE as opposed to the patch for MDEV-12874 that does it some cases.

Comment by Igor Babaev [ 2022-07-12 ]

A fix for this bug was pushed into bb-10.10-MDEV-28883

Comment by Roel Van de Paar [ 2022-07-21 ]

The bug is confirmed fixed in bb-10.10-MDEV-28883

Generated at Thu Feb 08 10:04:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.