[MDEV-30539] EXPLAIN EXTENDED: no message with queries for DML statements - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL), 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.11.3, 11.0.2, 10.4.29, 10.5.20, 10.6.13, 10.8.8, 10.9.6, 10.10.4
Component/s: Optimizer
Labels:
None

Description

For the following query with SELECT:

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 EXTENDED produces messages:

Warnings:

Note	1276	Field or reference 'v1.c3' of SELECT #2 was resolved in SELECT #1

Note	1003	/* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2`,`test`.`t3`.`c3` AS `c3` from `test`.`t1` semi join (`test`.`t2`) join `test`.`t3` where `test`.`t1`.`c2` = 2 and `test`.`t3`.`c2` = 2 and `test`.`t2`.`c2` = 2 and <expr_cache><2,`test`.`t1`.`c3`>(<in_optimizer>(2,<exists>(/* select#2 */ select max(`test`.`t2`.`c2`) from `test`.`t2` where `test`.`t2`.`c3` = `test`.`t1`.`c3` having <cache>(`test`.`t1`.`c2`) = <ref_null_helper>(max(`test`.`t2`.`c2`))))) and `test`.`t1`.`c1` < 10

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 EXTENDED does not produce message with query after optimization :

Warnings:

Note	1276	Field or reference 'v1.c3' of SELECT #2 was resolved in SELECT #1

Note	1276	Field or reference 'v1.c1' of SELECT #3 was resolved in SELECT #1

Problem with plan in this case will be fixed in ~~MDEV-30538~~

MTR test case:

--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  EXTENDED

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 EXTENDED

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 EXTENDED

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

Issue Links

causes

MDEV-31224 Crash with EXPLAIN EXTENDED for multi-table update of system table

Closed

Activity

Ascending order - Click to sort in descending order

Lena Startseva created issue - 2023-02-01 18:22

Lena Startseva made changes - 2023-02-01 18:23

Field	Original Value	New Value
Assignee		Sergei Petrunia [ psergey ]

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

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 EXTENDED produces messages:
{code:sql}
Warnings:
Note 1276 Field or reference 'v1.c3' of SELECT #2 was resolved in SELECT #1
Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2`,`test`.`t3`.`c3` AS `c3` from `test`.`t1` semi join (`test`.`t2`) join `test`.`t3` where `test`.`t1`.`c2` = 2 and `test`.`t3`.`c2` = 2 and `test`.`t2`.`c2` = 2 and <expr_cache><2,`test`.`t1`.`c3`>(<in_optimizer>(2,<exists>(/* select#2 */ select max(`test`.`t2`.`c2`) from `test`.`t2` where `test`.`t2`.`c3` = `test`.`t1`.`c3` having <cache>(`test`.`t1`.`c2`) = <ref_null_helper>(max(`test`.`t2`.`c2`))))) and `test`.`t1`.`c1` < 10
{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 EXTENDED does not produce message with query after optimization :
{code:sql}
Warnings:
Note 1276 Field or reference 'v1.c3' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'v1.c1' of SELECT #3 was resolved in SELECT #1
{code}

Problem with plan in this case will be fixed in ~~MDEV-30538~~

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 EXTENDED
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 EXTENDED
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 EXTENDED
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 EXTENDED produces messages:
{code:sql}
Warnings:
Note 1276 Field or reference 'v1.c3' of SELECT #2 was resolved in SELECT #1
Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2`,`test`.`t3`.`c3` AS `c3` from `test`.`t1` semi join (`test`.`t2`) join `test`.`t3` where `test`.`t1`.`c2` = 2 and `test`.`t3`.`c2` = 2 and `test`.`t2`.`c2` = 2 and <expr_cache><2,`test`.`t1`.`c3`>(<in_optimizer>(2,<exists>(/* select#2 */ select max(`test`.`t2`.`c2`) from `test`.`t2` where `test`.`t2`.`c3` = `test`.`t1`.`c3` having <cache>(`test`.`t1`.`c2`) = <ref_null_helper>(max(`test`.`t2`.`c2`))))) and `test`.`t1`.`c1` < 10
{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 EXTENDED does not produce message with query after optimization :
{code:sql}
Warnings:
Note 1276 Field or reference 'v1.c3' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'v1.c1' of SELECT #3 was resolved in SELECT #1
{code}

Problem with plan in this case will be fixed in ~~MDEV-30538~~

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 EXTENDED
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 EXTENDED
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 EXTENDED
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) made changes - 2023-02-03 07:01

Summary

EXPLAIN EXTENDED: does not show message with query after optimization for UPDATE/DELETE

EXPLAIN EXTENDED: does not show message with query for UPDATE/DELETE

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

Assignee

Sergei Petrunia [ psergey ]

Igor Babaev [ igor ]

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

Fix Version/s

10.4 [ 22408 ]

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

Status

Open [ 1 ]

In Progress [ 3 ]

Igor Babaev (Inactive) made changes - 2023-02-27 01:00

Summary

EXPLAIN EXTENDED: does not show message with query for UPDATE/DELETE

EXPLAIN EXTENDED: no message with query for DML statements

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

Summary

EXPLAIN EXTENDED: no message with query for DML statements

EXPLAIN EXTENDED: no message with queries for DML statements

Igor Babaev (Inactive) made changes - 2023-03-23 05:40

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

Oleksandr Byelkin made changes - 2023-03-25 09:24

Assignee

Oleksandr Byelkin [ sanja ]

Igor Babaev [ igor ]

Oleksandr Byelkin made changes - 2023-03-25 09:25

Assignee

Igor Babaev [ igor ]

Oleksandr Byelkin [ sanja ]

Oleksandr Byelkin made changes - 2023-03-25 09:25

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

Igor Babaev (Inactive) added a comment - 2023-03-28 03:29

A fix for this bug was pushed into 10.4. It should be merged upstream as it is, but not not into 11.1 (unreleased). 11.1 has different code for UPDATE/DELETE. So their might be some problems with the merge into 11.1.

Igor Babaev (Inactive) added a comment - 2023-03-28 03:29 A fix for this bug was pushed into 10.4. It should be merged upstream as it is, but not not into 11.1 (unreleased). 11.1 has different code for UPDATE/DELETE. So their might be some problems with the merge into 11.1.

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

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.8.8 [ 28518 ]
Fix Version/s		10.9.6 [ 28520 ]
Fix Version/s		10.10.4 [ 28522 ]
Fix Version/s		10.11.3 [ 28524 ]
Fix Version/s		11.0.2 [ 28706 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2023-05-31 14:40

Link

This issue causes ~~MDEV-31224~~ [ ~~MDEV-31224~~ ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Lena Startseva

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2023-02-01 18:22

Updated:: 2023-05-31 14:40

Resolved:: 2023-03-28 03:29

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration