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

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

            "transformation": {

              "select_id": 2,

              "from": "IN (SELECT)",

              "to": "materialization",

              "sjm_scan_allowed": true,

              "possible": true

},

  "transformation": {

    "select_id": N,

    "from": "EXISTS (SELECT)",

    "to": "IN (SELECT)"

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') );

@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":

[MDEV-21092] EXISTS to IN is not reflected in the optimizer trace Created: 2019-11-20 Updated: 2023-02-03 Resolved: 2022-12-26
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.4
Fix Version/s:	11.0.0

[MDEV-21092] EXISTS to IN is not reflected in the optimizer trace Created: 2019-11-20 Updated: 2023-02-03 Resolved: 2022-12-26