[MDEV-22534] Trivial correlation detection/removal for IN subqueries - Jira

Details

Type: Task
Status: In Review (View Workflow)
Priority: Major
Resolution: Unresolved
Fix Version/s: 12.1
Component/s: Optimizer
Labels:

Description

EXISTS-to-IN optimization performs trivial correlation detection and removal.

MySQL 8 has got it too, but in addition to EXISTS subqueries, they perform de-correlation also for IN subqueries. This allows them to use Materialization strategy for trivially-correlated IN- subqueries:

create table ten(a int primary key);

insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1 (a int, b int, c int);

insert into t1 select a,a,a from ten;

create table t2 select * from t1;

explain select * from t1 where a in (select a from t2 where t1.b=t2.b);

id     select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra

1      SIMPLE  t1      NULL    ALL     NULL    NULL    NULL    NULL    10      100.00  Using where

1      SIMPLE  <subquery2>     NULL    eq_ref  <auto_distinct_key>     <auto_distinct_key>     10      test.t1.a,test.t1.b     1       100.00  NULL

2      MATERIALIZED    t2      NULL    ALL     NULL    NULL    NULL    NULL    10      100.00  NULL

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

mdev22534-full-scan-on-null-key.sql
2 kB
2023-06-13 11:13

Issue Links

blocks

MDEV-31510 Allow exists2in and decorrelate-in to work for IN/EXISTS subqueries in arbitrary context

Open

is blocked by

MDEV-30073 Wrong result on 2nd execution of PS for query with NOT EXISTS

In Progress

relates to

MDEV-3881 Endless loop for query with EXISTS predicate using outer reference to view

Closed

MDEV-31229 Extend check_equality_for_exist2in() to cover inner table expressions

Open

MDEV-31408 Second SELECT from VIEW based on information_schema.optimizer_trace gives NULL result

Closed

MDEV-31647 Stack looping and SIGSEGV in Item_args::walk_args on UPDATE

Open

(1 relates to)

Activity

Ascending order - Click to sort in descending order

Sergei Petrunia created issue - 2020-05-12 12:58

Sergei Petrunia made changes - 2020-05-12 13:00

Field	Original Value	New Value
Labels		subquery

Sergei Golubchik made changes - 2020-08-16 20:11

Assignee

Sergei Petrunia [ psergey ]

Sergei Golubchik made changes - 2020-08-17 17:30

Rank

Ranked lower

Sergei Golubchik made changes - 2021-06-22 17:31

Fix Version/s		10.7 [ 24805 ]
Fix Version/s	10.6 [ 24028 ]

Ralf Gebhardt made changes - 2021-10-12 21:04

Fix Version/s		10.8 [ 26121 ]
Fix Version/s	10.7 [ 24805 ]

Sergei Golubchik made changes - 2021-12-06 21:21

Workflow

MariaDB v3 [ 108567 ]

MariaDB v4 [ 131288 ]

Sergei Petrunia made changes - 2021-12-08 14:32

Labels

subquery

optimizer subquery

Sergei Petrunia made changes - 2021-12-21 11:48

Labels

optimizer subquery

optimizer optimizer-easy subquery

Sergei Golubchik made changes - 2022-02-01 11:33

Fix Version/s		10.9 [ 26905 ]
Fix Version/s	10.8 [ 26121 ]

Sergei Golubchik made changes - 2022-04-06 10:48

Fix Version/s		10.10 [ 27530 ]
Fix Version/s	10.9 [ 26905 ]

Sergei Golubchik made changes - 2022-06-15 13:15

Fix Version/s		10.11 [ 27614 ]
Fix Version/s	10.10 [ 27530 ]

Sergei Golubchik made changes - 2022-08-02 19:58

Fix Version/s		10.12 [ 28320 ]
Fix Version/s	10.11 [ 27614 ]

Sergei Petrunia made changes - 2023-03-24 16:24

Fix Version/s		11.2 [ 28603 ]
Fix Version/s	11.0 [ 28320 ]

Yuchen Pei made changes - 2023-05-01 12:17

Assignee

Sergei Petrunia [ psergey ]

Yuchen Pei [ JIRAUSER52627 ]

Yuchen Pei made changes - 2023-05-03 11:45

Status

Open [ 1 ]

In Progress [ 3 ]

Yuchen Pei made changes - 2023-05-09 08:12

Priority

Major [ 3 ]

Critical [ 2 ]

Yuchen Pei made changes - 2023-05-10 07:45

Link

This issue relates to MDEV-31229 [ MDEV-31229 ]

Yuchen Pei made changes - 2023-05-16 06:04

Link

This issue relates to MDEV-31269 [ MDEV-31269 ]

Yuchen Pei made changes - 2023-05-19 10:16

Assignee	Yuchen Pei [ JIRAUSER52627 ]	Sergei Petrunia [ psergey ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Yuchen Pei made changes - 2023-05-23 07:42

Link

This issue is blocked by MDEV-31269 [ MDEV-31269 ]

Sergei Petrunia made changes - 2023-05-25 19:21

Assignee	Sergei Petrunia [ psergey ]	Yuchen Pei [ JIRAUSER52627 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Yuchen Pei made changes - 2023-06-02 00:52

Assignee	Yuchen Pei [ JIRAUSER52627 ]	Sergei Petrunia [ psergey ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Petrunia made changes - 2023-06-13 11:13

Attachment

mdev22534-full-scan-on-null-key.sql [ 70511 ]

Yuchen Pei made changes - 2023-06-21 00:35

Link

This issue relates to ~~MDEV-31408~~ [ ~~MDEV-31408~~ ]

Yuchen Pei made changes - 2023-06-21 00:35

Link

This issue relates to MDEV-31269 [ MDEV-31269 ]

Yuchen Pei made changes - 2023-06-21 00:46

Link

This issue blocks MDEV-31510 [ MDEV-31510 ]

Yuchen Pei made changes - 2023-06-21 01:04

Link

This issue relates to ~~MDEV-3881~~ [ ~~MDEV-3881~~ ]

Yuchen Pei made changes - 2023-07-26 04:14

Fix Version/s		11.3 [ 28565 ]
Fix Version/s	11.2 [ 28603 ]

Sergei Golubchik made changes - 2023-08-18 12:24

Link

This issue is blocked by MDEV-31269 [ MDEV-31269 ]

Sergei Golubchik made changes - 2023-09-12 20:38

Link

This issue blocks TODO-4253 [ TODO-4253 ]

Sergei Golubchik made changes - 2023-09-14 20:34

Fix Version/s		11.4 [ 29301 ]
Fix Version/s	11.3 [ 28565 ]

Sergei Golubchik made changes - 2023-09-14 20:34

Link

This issue blocks TODO-4253 [ TODO-4253 ]

Sergei Petrunia made changes - 2023-10-24 13:51

Link

This issue relates to MDEV-31647 [ MDEV-31647 ]

Ralf Gebhardt made changes - 2023-10-30 14:12

Fix Version/s		11.5 [ 29506 ]
Fix Version/s	11.4 [ 29301 ]

Sergei Golubchik made changes - 2023-10-30 14:27

Fix Version/s		11.4 [ 29301 ]
Fix Version/s	11.5 [ 29506 ]

Sergei Golubchik made changes - 2023-12-22 17:45

Fix Version/s		11.5 [ 29506 ]
Fix Version/s	11.4 [ 29301 ]

Julien Fritsch made changes - 2024-01-25 09:44

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2024-01-25 10:30

Assignee

Sergei Petrunia [ psergey ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2024-01-25 10:30

Assignee	Sergei Golubchik [ serg ]	Sergei Petrunia [ psergey ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Yuchen Pei made changes - 2024-02-05 08:34

Link

This issue is blocked by MDEV-30073 [ MDEV-30073 ]

Sergei Golubchik made changes - 2024-03-19 18:31

Fix Version/s		11.6 [ 29515 ]
Fix Version/s	11.5 [ 29506 ]

Sergei Golubchik made changes - 2024-06-04 15:39

Fix Version/s		11.7 [ 29815 ]
Fix Version/s	11.6 [ 29515 ]

Sergei Golubchik made changes - 2024-09-24 13:53

Fix Version/s		11.8 [ 29921 ]
Fix Version/s	11.7 [ 29815 ]

Sergei Golubchik made changes - 2024-12-10 15:11

Fix Version/s		11.9 [ 29945 ]
Fix Version/s	11.8 [ 29921 ]

Sergei Petrunia made changes - 2025-02-21 09:46

Priority

Critical [ 2 ]

Major [ 3 ]

Sergei Petrunia made changes - 2025-02-21 09:46

Labels

optimizer optimizer-easy subquery

optimizer optimizer-easy patch-pending subquery

Sergei Petrunia made changes - 2025-02-26 08:42

Fix Version/s		12.1 [ 29992 ]
Fix Version/s	12.0 [ 29945 ]

People

Assignee:: Sergei Petrunia

Reporter:: Sergei Petrunia

Votes:: 0 Vote for this issue

Watchers:: 9 Start watching this issue

Dates

Created:: 2020-05-12 12:58

Updated:: 2025-02-27 09:01

Git Integration

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

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration