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