[MDEV-21092] EXISTS to IN is not reflected in the optimizer trace - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.4(EOL)
Fix Version/s: 11.0.0
Component/s: Optimizer
Labels:
- optimizer_trace

Description

--- a/mysql-test/main/subselect_exists2in_costmat.test

+++ b/mysql-test/main/subselect_exists2in_costmat.test

@@ -65,19 +65,24 @@ set @@optimizer_switch = 'exists_to_in=on,in_to_exists=on,semijoin=on,materializ

 -- echo Q1.1m:

 -- echo MATERIALIZATION: there are too many rows in the outer query

 -- echo to be looked up in the inner table.

-EXPLAIN

+EXPLAIN EXTENDED

 SELECT Name FROM Country

 WHERE (EXISTS (select 1 from City where City.Population > 100000 and

 Code = Country) OR

        Name LIKE 'L%') AND

       surfacearea > 1000000;

+set optimizer_trace=1;

 SELECT Name FROM Country

 WHERE (EXISTS (select 1 from City where City.Population > 100000 and

 Code = Country) OR

        Name LIKE 'L%') AND

       surfacearea > 1000000;

+select * from information_schema.optimizer_trace;

 drop database world;

 set optimizer_switch=@subselect_mat_cost;

Attachments

Issue Links

relates to

MDEV-21095 Index condition push down is not reflected in optimizer trace

Closed

MDEV-29997 Partition Pruning not included in optimizer tracing

Closed

Activity

Ascending order - Click to sort in descending order

Sergei Petrunia added a comment - 2022-11-06 14:01

An example of how in-to-exists is traced:

            "transformation": {

              "select_id": 2,

              "from": "IN (SELECT)",

              "to": "materialization",

              "sjm_scan_allowed": true,

              "possible": true

},

Sergei Petrunia added a comment - 2022-11-06 14:01 An example of how in-to-exists is traced: { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "materialization", "sjm_scan_allowed": true, "possible": true } },

Sergei Petrunia added a comment - 2022-11-06 14:02

Suggested way of tracing exists-to-in:

  "transformation": {

    "select_id": N,

    "from": "EXISTS (SELECT)",

    "to": "IN (SELECT)"

Sergei Petrunia added a comment - 2022-11-06 14:02 Suggested way of tracing exists-to-in: { "transformation" : { "select_id" : N, "from" : "EXISTS (SELECT)" , "to" : "IN (SELECT)" } }

Sergei Petrunia added a comment - 2022-11-06 14:03

Function doing the conversion: Item_exists_subselect::exists2in_processor

Sergei Petrunia added a comment - 2022-11-06 14:03 Function doing the conversion: Item_exists_subselect::exists2in_processor

Rex Johnston added a comment - 2022-11-10 03:15 - edited

above test case, world database....

set @@optimizer_switch = 'exists_to_in=on,in_to_exists=on,semijoin=on,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on';

-- echo

-- echo 1. Subquery in a disjunctive WHERE clause of the outer query.

-- echo

-- echo

-- echo Q1.1m:

-- echo MATERIALIZATION: there are too many rows in the outer query

-- echo to be looked up in the inner table.

EXPLAIN EXTENDED

SELECT Name FROM Country

WHERE ( EXISTS (select 1 from City where City.Population > 100000 and Country.Code = City.Country)

	        OR (Country.Name LIKE 'L%') )

	AND surfacearea > 1000000;

set optimizer_trace=1;

reset query cache;

SELECT Name FROM Country

WHERE (EXISTS (select 1 from City where City.Population > 100000 and Country.Code = City.Country)

			OR (Country.Name LIKE 'L%') )

	AND surfacearea > 1000000;

select trace from information_schema.optimizer_trace into @trace;

--select @trace as out1;

--select json_extract(@trace, '$.steps[*].join_optimization') as out2;

select json_detailed(json_extract(@trace, '$.steps[*].join_optimization')) into @trace_join_optimization;

select @trace_join_optimization;

--select json_detailed(json_extract(@trace_join_optimization, '$**.transformation') );

produces

@trace_join_optimization

        "select_id": 1,

        "steps":

                "transformation":

                    "select_id": 2,

                    "from": "EXISTS (SELECT)",

                    "to": "IN (SELECT)",

                    "upper_not": false

},

                "transformation":

                    "select_id": 2,

                    "from": "IN (SELECT)",

                    "to": "materialization",

                    "sjm_scan_allowed": true,

                    "possible": true

},

                "condition_processing":

Rex Johnston added a comment - 2022-11-10 03:15 - edited above test case, world database.... set @@optimizer_switch = 'exists_to_in=on,in_to_exists=on,semijoin=on,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on'; -- echo -- echo 1. Subquery in a disjunctive WHERE clause of the outer query. -- echo -- echo -- echo Q1.1m: -- echo MATERIALIZATION: there are too many rows in the outer query -- echo to be looked up in the inner table. EXPLAIN EXTENDED SELECT Name FROM Country WHERE ( EXISTS (select 1 from City where City.Population > 100000 and Country.Code = City.Country) OR (Country.Name LIKE 'L%') ) AND surfacearea > 1000000; set optimizer_trace=1; reset query cache; SELECT Name FROM Country WHERE (EXISTS (select 1 from City where City.Population > 100000 and Country.Code = City.Country) OR (Country.Name LIKE 'L%') ) AND surfacearea > 1000000; select trace from information_schema.optimizer_trace into @trace; --select @trace as out1; --select json_extract(@trace, '$.steps[*].join_optimization') as out2; select json_detailed(json_extract(@trace, '$.steps[*].join_optimization')) into @trace_join_optimization; select @trace_join_optimization; --select json_detailed(json_extract(@trace_join_optimization, '$**.transformation') ); produces @trace_join_optimization [ { "select_id": 1, "steps": [ { "transformation": { "select_id": 2, "from": "EXISTS (SELECT)", "to": "IN (SELECT)", "upper_not": false } }, { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "condition_processing":

Sergei Petrunia added a comment - 2022-12-26 07:28

The patch is in preview-11.0-preview tree

Sergei Petrunia added a comment - 2022-12-26 07:28 The patch is in preview-11.0-preview tree

People

Assignee:: Sergei Petrunia

Reporter:: Oleksandr Byelkin

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2019-11-20 04:59

Updated:: 2023-02-03 11:28

Resolved:: 2022-12-26 07:28

Git Integration

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

MariaDB Server