[MDEV-30538] Plans for SELECT and multi-table UPDATE/DELETE unexpectedly differ - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 11.0(EOL)
Fix Version/s: 10.4.29, 10.5.20, 10.6.13, 10.8.8, 10.9.6
Component/s: Optimizer
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;

Attachments

Activity

Ascending order - Click to sort in descending order

Igor Babaev (Inactive) added a comment - 2023-02-03 00:02

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)

Igor Babaev (Inactive) added a comment - 2023-02-03 00:02 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)

Oleksandr Byelkin added a comment - 2023-02-03 18:50

OK to push

Oleksandr Byelkin added a comment - 2023-02-03 18:50 OK to push

Igor Babaev (Inactive) added a comment - 2023-02-06 07:20

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

Igor Babaev (Inactive) added a comment - 2023-02-06 07:20 This bug was pushed into 10.4. It has to be merged upstream as it is.

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Lena Startseva

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2023-02-01 17:56

Updated:: 2023-03-16 00:35

Resolved:: 2023-02-06 07:20

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server