Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
N/A
-
None
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 )
|
|
Attachments
Issue Links
- is caused by
-
MDEV-28883
Re-design the upper level of handling UPDATE and DELETE statements
-
- Closed
-
Activity
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)
|
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.
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.
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 |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|
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.
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 awayAt 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.