maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

On Thu, Apr 01, 2021 at 10:14:04PM -0700, Igor Babaev wrote:
> Sergey,
> 
> Can you explain this:
> 
> MariaDB [test]> explain format=json select * from t1 as t2 where item_name
> in    (select item_name from t2 as s, json_table(t2.item_props,'$' columns(
> color varchar(100) path '$.color')) as t) and t2.item_name='Jeans';
> +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | EXPLAIN
> 
> 
> 
> 
> 
> 
>                                                            |
> +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | {
>   "query_block": {
>     "select_id": 1,
>     "table": {
>       "table_name": "t2",
>       "access_type": "ALL",
>       "rows": 2,
>       "filtered": 100,
>       "attached_condition": "t2.item_name = 'Jeans'"
>     },
>     "table": {
>       "table_name": "s",
>       "access_type": "ALL",
>       "rows": 2,
>       "filtered": 100,
>       "attached_condition": "s.item_name = 'Jeans'"
>     },
>     "table": {
>       "table_name": "t",
>       "access_type": "ALL",
>       "rows": 40,
>       "filtered": 100,
>       "table_function": "json_table",
>       "first_match": "t2"
>     }
>   }
> } |
> +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> How do get get s.item_name = 'Jeans' ?
> 

Check out the optimizer trace: 

First, the subquery is converted into a semi-join:

            "transformation": {
              "select_id": 2,
              "from": "IN (SELECT)",
              "to": "semijoin",
              "converted_to_semi_join": true
            }

then, there's a multiple equality:

                  "transformation": "equality_propagation",
                  "resulting_condition": "1 and multiple equal('Jeans', t2.item_name, s.item_name)"

and then, after substituion:

            "attaching_conditions_to_tables": {
              "original_condition": "t2.item_name = 'Jeans' and s.item_name = 'Jeans'",

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net