[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

Lena Startseva created issue - 2023-02-01 17:56

Lena Startseva made changes - 2023-02-01 17:57

Field	Original Value	New Value
Description	For the following query with SELECT: {code:sql} 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 v1.c1 in (select c1 from v2); {code} EXPLAIN produces: {code:sql} 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 {code} For the queries with DELETE and UPDATE based on previous SELECT: {code:sql} 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); {code} {code:sql} update v1,t3 set v1.c1 = 15, t3.c1=21 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); {code} EXPLAIN produces: {code:sql} 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 {code} but it is expected that the plan will be the same as plan for select. MTR test case: {code:sql} --source include/have_innodb.inc create table t1 (c1 integer, c2 integer, c3 integer) engine=innodb; 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) engine=innodb; 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) engine=innodb; 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 v1.c1 in (select c1 from v2); 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, t3.c1=21 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; {code}	For the following query with SELECT: {code:sql} 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 v1.c1 in (select c1 from v2); {code} EXPLAIN produces: {code:sql} 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 {code} For the queries with DELETE and UPDATE based on previous SELECT: {code:sql} 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); {code} {code:sql} update v1,t3 set v1.c1 = 15, t3.c1=21 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); {code} EXPLAIN produces: {code:sql} 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 {code} but it is expected that the plan will be the same as plan for SELECT MTR test case: {code:sql} --source include/have_innodb.inc create table t1 (c1 integer, c2 integer, c3 integer) engine=innodb; 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) engine=innodb; 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) engine=innodb; 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 v1.c1 in (select c1 from v2); 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, t3.c1=21 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; {code}

Lena Startseva made changes - 2023-02-01 17:59

Assignee

Igor Babaev [ igor ]

Lena Startseva made changes - 2023-02-01 17:59

Component/s

Optimizer [ 10200 ]

Igor Babaev (Inactive) made changes - 2023-02-01 23:04

Summary

EXPLAIN: different plan for SELECT and DELETE/UPDATE

EXPLAIN: different plan for SELECT and multi-table DELETE/UPDATE

Igor Babaev (Inactive) made changes - 2023-02-01 23:04

Summary

EXPLAIN: different plan for SELECT and multi-table DELETE/UPDATE

EXPLAIN: different plans for SELECT and multi-table DELETE/UPDATE

Igor Babaev (Inactive) made changes - 2023-02-01 23:05

Fix Version/s

10.4 [ 22408 ]

Igor Babaev (Inactive) made changes - 2023-02-01 23:05

Status

Open [ 1 ]

In Progress [ 3 ]

Lena Startseva made changes - 2023-02-02 10:02

Description

For the following query with SELECT:
{code:sql}
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
            v1.c1 in (select c1 from v2);
{code}

EXPLAIN produces:
{code:sql}
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
{code}

For the queries with DELETE and UPDATE based on previous SELECT:
{code:sql}
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);
{code}

{code:sql}
update v1,t3
set v1.c1 = 15, t3.c1=21
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);
{code}

EXPLAIN produces:
{code:sql}
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
{code}

*but it is expected that the plan will be the same as plan for SELECT*

MTR test case:
{code:sql}
--source include/have_innodb.inc

create table t1 (c1 integer, c2 integer, c3 integer) engine=innodb;
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) engine=innodb;
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) engine=innodb;
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
            v1.c1 in (select c1 from v2);

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, t3.c1=21
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;
{code}

For the following query with SELECT:
{code:sql}
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
            v1.c1 in (select c1 from v2);
{code}

EXPLAIN produces:
{code:sql}
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
{code}

For the queries with DELETE and UPDATE based on previous SELECT:
{code:sql}
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);
{code}

{code:sql}
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);
{code}

EXPLAIN produces:
{code:sql}
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
{code}

*but it is expected that the plan will be the same as plan for SELECT*

MTR test case:
{code:sql}
--source include/have_innodb.inc

create table t1 (c1 integer, c2 integer, c3 integer) engine=innodb;
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) engine=innodb;
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) engine=innodb;
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
            v1.c1 in (select c1 from v2);

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;
{code}

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)

Igor Babaev (Inactive) made changes - 2023-02-03 06:27

Summary

EXPLAIN: different plans for SELECT and multi-table DELETE/UPDATE

Unexpectedly different plans for SELECT and multi-table DELETE/UPDATE

Igor Babaev (Inactive) made changes - 2023-02-03 06:29

Summary

Unexpectedly different plans for SELECT and multi-table DELETE/UPDATE

Plans for SELECT and multi-table UPDATE/DELETE unexpectedly differ

Igor Babaev (Inactive) made changes - 2023-02-03 06:40

Assignee	Igor Babaev [ igor ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Igor Babaev (Inactive) made changes - 2023-02-03 06:54

Description

For the following query with SELECT:
{code:sql}
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
            v1.c1 in (select c1 from v2);
{code}

EXPLAIN produces:
{code:sql}
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
{code}

For the queries with DELETE and UPDATE based on previous SELECT:
{code:sql}
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);
{code}

{code:sql}
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);
{code}

EXPLAIN produces:
{code:sql}
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
{code}

*but it is expected that the plan will be the same as plan for SELECT*

MTR test case:
{code:sql}
--source include/have_innodb.inc

create table t1 (c1 integer, c2 integer, c3 integer) engine=innodb;
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) engine=innodb;
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) engine=innodb;
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
            v1.c1 in (select c1 from v2);

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;
{code}

For the following query with SELECT:
{code:sql}
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);
{code}

EXPLAIN produces:
{noformat}
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 |
+------+--------------------+-------------+--------+---------------+--------------+---------+-------+------+-------------------------------------------------+
{noformat}

For the queries with DELETE and UPDATE based on previous SELECT:
{code:sql}
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);
{code}

{code:sql}
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);
{code}

EXPLAIN produces:
{noformat}
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 | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | |
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
| 3 | MATERIALIZED | t2 | ref | t2_c2 | t2_c2 | 5 | const | 2 | |
| 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
+------+--------------------+-------------+--------+---------------+--------------+---------+-------+------+-------------+
{noformat}

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

MTR test case:
{code:sql}

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;
{code}

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

Oleksandr Byelkin made changes - 2023-02-03 18:50

Assignee	Oleksandr Byelkin [ sanja ]	Igor Babaev [ igor ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Igor Babaev (Inactive) made changes - 2023-02-06 07:16

Status

Stalled [ 10000 ]

In Progress [ 3 ]

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.

Igor Babaev (Inactive) made changes - 2023-02-06 07:20

Fix Version/s		10.4.29 [ 28510 ]
Fix Version/s		10.5.20 [ 28512 ]
Fix Version/s		10.6.13 [ 28514 ]
Fix Version/s		10.7.9 [ 28516 ]
Fix Version/s		10.8.8 [ 28518 ]
Fix Version/s		10.9.6 [ 28520 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Igor Babaev (Inactive) made changes - 2023-02-08 04:18

Description

For the following query with SELECT:
{code:sql}
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);
{code}

EXPLAIN produces:
{noformat}
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 |
+------+--------------------+-------------+--------+---------------+--------------+---------+-------+------+-------------------------------------------------+
{noformat}

For the queries with DELETE and UPDATE based on previous SELECT:
{code:sql}
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);
{code}

{code:sql}
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);
{code}

EXPLAIN produces:
{noformat}
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 |
+------+--------------------+-------+------+---------------+-------+---------+-------+------+-------------+

{noformat}

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

MTR test case:
{code:sql}

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;
{code}

Sergei Golubchik made changes - 2023-02-09 19:25

Fix Version/s

10.7.9 [ 28516 ]

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

Details

Description

Attachments

Activity

People

Dates

Git Integration