[MDEV-30538] Plans for SELECT and multi-table UPDATE/DELETE unexpectedly differ Created: 2023-02-01  Updated: 2023-03-16  Resolved: 2023-02-06

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 11.0
Fix Version/s: 10.4.29, 10.5.20, 10.6.13, 10.8.8, 10.9.6

Type: Bug Priority: Major
Reporter: Lena Startseva Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None


 Description   

For the following query with SELECT:

explain 
select * 
 from v1,t3
 
where
 
  v1.c2 = t3.c2 and 
  v1.c2 in (select max(c2) from t2 where c3 = v1.c3)
 
            and v1.c1 < 10 and
 
            exists (select 'X' from v2 where c1 = v1.c1);

EXPLAIN produces:

MariaDB [test]> explain 
    -> select * 
    ->  from v1,t3
 
    -> where
 
    ->   v1.c2 = t3.c2 and 
    ->   v1.c2 in (select max(c2) from t2 where c3 = v1.c3)
 
    ->             and v1.c1 < 10 and
 
    ->             exists (select 'X' from v2 where c1 = v1.c1);
 
+------+--------------------+-------------+--------+---------------+--------------+---------+-------+------+-------------------------------------------------+
 
| id   | select_type        | table       | type   | possible_keys | key          | key_len | ref   | rows | Extra                                           |
 
+------+--------------------+-------------+--------+---------------+--------------+---------+-------+------+-------------------------------------------------+
 
|    1 | PRIMARY            | t1          | ref    | t1_c2         | t1_c2        | 5       | const | 2    | Using where                                     |
 
|    1 | PRIMARY            | <subquery3> | eq_ref | distinct_key  | distinct_key | 4       | func  | 1    |                                                 |
 
|    1 | PRIMARY            | t3          | ALL    | NULL          | NULL         | NULL    | NULL  | 8    | Using where; Using join buffer (flat, BNL join) |
 
|    3 | MATERIALIZED       | t2          | ref    | t2_c2         | t2_c2        | 5       | const | 2    |                                                 |
 
|    2 | DEPENDENT SUBQUERY | t2          | ALL    | NULL          | NULL         | NULL    | NULL  | 8    | Using where                                     |
 
+------+--------------------+-------------+--------+---------------+--------------+---------+-------+------+-------------------------------------------------+

For the queries with DELETE and UPDATE based on previous SELECT:

delete
 
 from v1
 
 using v1,t3
 
where
 
  v1.c2 = t3.c2 and 
  v1.c2 in (select max(c2) from t2 where c3 = v1.c3)
 
            and v1.c1 < 10 and
 
            exists (select 'X' from v2 where c1 = v1.c1);


update v1,t3
 
set v1.c1 = 15
 
where
 
  v1.c2 = t3.c2 and 
  v1.c2 in (select max(c2) from t2 where c3 = v1.c3)
 
            and v1.c1 < 10 and
 
            exists (select 'X' from v2 where c1 = v1.c1);

EXPLAIN produces:

MariaDB [test]> explain 
    -> delete
 
    ->  from v1
 
    ->  using v1,t3
 
    -> where
 
    ->   v1.c2 = t3.c2 and 
    ->   v1.c2 in (select max(c2) from t2 where c3 = v1.c3)
 
    ->             and v1.c1 < 10 and
 
    ->             exists (select 'X' from v2 where c1 = v1.c1);
 
+------+--------------------+-------+------+---------------+-------+---------+-------+------+-------------+
 
| id   | select_type        | table | type | possible_keys | key   | key_len | ref   | rows | Extra       |
 
+------+--------------------+-------+------+---------------+-------+---------+-------+------+-------------+
 
|    1 | PRIMARY            | t1    | ref  | t1_c2         | t1_c2 | 5       | const | 2    | Using where |
 
|    1 | PRIMARY            | t3    | ALL  | NULL          | NULL  | NULL    | NULL  | 8    | Using where |
 
|    3 | DEPENDENT SUBQUERY | t2    | ref  | t2_c2         | t2_c2 | 5       | const | 2    | Using where |
 
|    2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL  | NULL    | NULL  | 8    | Using where |
 
+------+--------------------+-------+------+---------------+-------+---------+-------+------+-------------+
 
 
 
MariaDB [test]> explain 
    -> update v1,t3
 
    -> set v1.c1 = 15
 
    -> where
 
    ->   v1.c2 = t3.c2 and 
    ->   v1.c2 in (select max(c2) from t2 where c3 = v1.c3)
 
    ->             and v1.c1 < 10 and
 
    ->             exists (select 'X' from v2 where c1 = v1.c1);
 
+------+--------------------+-------+------+---------------+-------+---------+-------+------+-------------+
 
| id   | select_type        | table | type | possible_keys | key   | key_len | ref   | rows | Extra       |
 
+------+--------------------+-------+------+---------------+-------+---------+-------+------+-------------+
 
|    1 | PRIMARY            | t1    | ref  | t1_c2         | t1_c2 | 5       | const | 2    | Using where |
 
|    1 | PRIMARY            | t3    | ALL  | NULL          | NULL  | NULL    | NULL  | 8    | Using where |
 
|    3 | DEPENDENT SUBQUERY | t2    | ref  | t2_c2         | t2_c2 | 5       | const | 2    | Using where |
 
|    2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL  | NULL    | NULL  | 8    | Using where |
 
+------+--------------------+-------+------+---------------+-------+---------+-------+------+-------------+

but it is expected that the plan will be similar to the plan for SELECT

MTR test case:

 
 
create table t1 (c1 integer, c2 integer, c3 integer) ;
 
insert into t1(c1,c2,c3)
 
values (1,1,1),(1,2,2),(1,3,3),
 
(2,1,4),(2,2,5),(2,3,6),
 
(2,4,7),(2,5,8);
 
create view v1 as select * from t1 where c2=2;
 
create index t1_c2 on t1 (c2);
 
analyze table t1 persistent for all;
 
 
 
create table t2 (c1 integer, c2 integer, c3 integer) ;
 
insert into t2(c1,c2,c3)
 
values (1,1,1),(1,2,2),(1,3,3),
 
(2,1,4),(2,2,5),(2,3,6),
 
(2,4,7),(2,5,8);
 
create view v2 as select * from t2 where c2=2;
 
create index t2_c2 on t2 (c2);
 
analyze table t2 persistent for all;
 
 
 
create table t3 (c1 integer, c2 integer, c3 integer) ;
 
insert into t3(c1,c2,c3)
 
values (1,1,1),(1,2,2),(1,3,3),
 
(2,1,4),(2,2,5),(2,3,6),
 
(2,4,7),(2,5,8);
 
analyze table t3 persistent for all;
 
 
 
explain 
select * 
 from v1,t3
 
where
 
  v1.c2 = t3.c2 and 
  v1.c2 in (select max(c2) from t2 where c3 = v1.c3)
 
            and v1.c1 < 10 and
 
            exists (select 'X' from v2 where c1 = v1.c1);
 
 
explain 
delete
 
 from v1
 
 using v1,t3
 
where
 
  v1.c2 = t3.c2 and 
  v1.c2 in (select max(c2) from t2 where c3 = v1.c3)
 
            and v1.c1 < 10 and
 
            exists (select 'X' from v2 where c1 = v1.c1);
 
 
 
explain 
update v1,t3
 
set v1.c1 = 15
 
where
 
  v1.c2 = t3.c2 and 
  v1.c2 in (select max(c2) from t2 where c3 = v1.c3)
 
            and v1.c1 < 10 and
 
            exists (select 'X' from v2 where c1 = v1.c1);
 
 
 
drop view v1, v2;
 
drop table t1, t2, t3;



 Comments   
Comment by Igor Babaev [ 2023-02-03 ]

Here's a simpler test case demonstrating this problem:

create table t1 (c1 int, c2 int, c3 int, index idx(c2));
 
insert into t1 values
 
(1,1,1),(3,2,2),(1,3,3),
 
(2,1,4),(2,2,5),(4,3,6),
 
(2,4,7),(2,5,8);
 
 
 
create table t2 (c1 int, c2 int, c3 int, index idx(c2));
 
insert into t2 values
 
(1,7,1),(1,8,2),(1,3,3),
 
(2,1,4),(2,2,5),(2,3,6),
 
(2,4,7),(2,5,8);
 
 
 
create table t3 (c1 int, c2 int, c3 int, index idx(c2));
 
insert into t3 values
 
(1,1,1),(1,2,2),(1,3,3),
 
(2,1,4),(2,2,5),(2,3,6),
 
(2,4,7),(2,5,8);
 
insert into t3 select c1+1, c2+2, c3 from t3;
 
insert into t3 select c1, c2+2, c3  from t3;
 
 
 
analyze table t1,t2,t3 persistent for all;
 
 
 
create table t select * from t1;
 
 
 
explain
 
select * from t1,t3 
where
 
  t1.c2 = t3.c2 and 
  t1.c1 > 1 and 
  exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
 
 
 
explain
 
delete from t1 using t1,t3 
where
 
  t1.c2 = t3.c2 and 
  t1.c1 > 1 and 
  exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
 
 
 
explain
 
update t1,t3 set t1.c1 = t1.c1+10
 
where
 
  t1.c2 = t3.c2 and 
  t1.c1 > 1 and 
  exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);

We have the following output for the above EXPLAIN commands:

 
 
MariaDB [test]> explain
 
    -> select * from t1,t3 
    -> where
 
    ->   t1.c2 = t3.c2 and 
    ->   t1.c1 > 1 and 
    ->   exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
 
+------+--------------+-------------+--------+---------------+--------------+---------+------------+------+-------------+
 
| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref        | rows | Extra       |
 
+------+--------------+-------------+--------+---------------+--------------+---------+------------+------+-------------+
 
|    1 | PRIMARY      | t1          | ALL    | idx           | NULL         | NULL    | NULL       | 8    | Using where |
 
|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 4       | func       | 1    |             |
 
|    1 | PRIMARY      | t3          | ref    | idx           | idx          | 5       | test.t1.c2 | 3    |             |
 
|    2 | MATERIALIZED | t2          | ALL    | idx           | NULL         | NULL    | NULL       | 8    | Using where |
 
+------+--------------+-------------+--------+---------------+--------------+---------+------------+------+-------------+
 
4 rows in set (0.003 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> explain
 
    -> delete from t1 using t1,t3 
    -> where
 
    ->   t1.c2 = t3.c2 and 
    ->   t1.c1 > 1 and 
    ->   exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
 
+------+--------------------+-------+-------+---------------+------+---------+------------+------+-------------+
 
| id   | select_type        | table | type  | possible_keys | key  | key_len | ref        | rows | Extra       |
 
+------+--------------------+-------+-------+---------------+------+---------+------------+------+-------------+
 
|    1 | PRIMARY            | t1    | ALL   | idx           | NULL | NULL    | NULL       | 8    | Using where |
 
|    1 | PRIMARY            | t3    | ref   | idx           | idx  | 5       | test.t1.c2 | 3    | Using index |
 
|    2 | DEPENDENT SUBQUERY | t2    | range | idx           | idx  | 5       | NULL       | 3    | Using where |
 
+------+--------------------+-------+-------+---------------+------+---------+------------+------+-------------+
 
3 rows in set (0.001 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> explain
 
    -> update t1,t3 set t1.c1 = t1.c1+10
 
    -> where
 
    ->   t1.c2 = t3.c2 and 
    ->   t1.c1 > 1 and 
    ->   exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
 
+------+--------------------+-------+-------+---------------+------+---------+------------+------+-------------+
 
| id   | select_type        | table | type  | possible_keys | key  | key_len | ref        | rows | Extra       |
 
+------+--------------------+-------+-------+---------------+------+---------+------------+------+-------------+
 
|    1 | PRIMARY            | t1    | ALL   | idx           | NULL | NULL    | NULL       | 8    | Using where |
 
|    1 | PRIMARY            | t3    | ref   | idx           | idx  | 5       | test.t1.c2 | 3    | Using index |
 
|    2 | DEPENDENT SUBQUERY | t2    | range | idx           | idx  | 5       | NULL       | 3    | Using where |
 
+------+--------------------+-------+-------+---------------+------+---------+------------+------+-------------+
 
3 rows in set (0.001 sec)

Comment by Oleksandr Byelkin [ 2023-02-03 ]

OK to push

Comment by Igor Babaev [ 2023-02-06 ]

This bug was pushed into 10.4. It has to be merged upstream as it is.

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