[Commits] 40938e0: An attempt to rebase MDEV-7487 against 11.0.1
revision-id: 40938e077f130e7d6139d296fd8dfbf29ce096ca (mariadb-10.11.1-109-g40938e0) parent(s): 7fca91e5e2267a995896e162a13b8ba7d1b08a22 author: Igor Babaev committer: Igor Babaev timestamp: 2023-03-07 17:18:45 -0800 message: An attempt to rebase MDEV-7487 against 11.0.1 --- mysql-test/main/delete.result | 6 +- mysql-test/main/delete_single_to_multi.result | 286 +++++++------- mysql-test/main/derived.test | 10 - mysql-test/main/derived_1.result | 19 + mysql-test/main/derived_cond_pushdown.result | 3 + mysql-test/main/log_state.result | 2 +- mysql-test/main/multi_update.result | 18 - .../main/myisam_explain_non_select_all.result | 28 +- mysql-test/main/opt_trace.result | 4 +- mysql-test/main/subselect.result | 12 + mysql-test/main/subselect.test | 6 + mysql-test/main/subselect_no_exists_to_in.result | 12 + mysql-test/main/subselect_no_mat.result | 12 + mysql-test/main/subselect_no_opts.result | 12 + mysql-test/main/subselect_no_scache.result | 12 + mysql-test/main/subselect_no_semijoin.result | 12 + mysql-test/main/update_single_to_multi.result | 418 +++++++++++---------- mysql-test/main/update_use_source.result | 17 +- sql/sql_delete.cc | 4 - sql/sql_update.cc | 4 - 20 files changed, 495 insertions(+), 402 deletions(-) diff --git a/mysql-test/main/delete.result b/mysql-test/main/delete.result index dd62e09..c4bf335 100644 --- a/mysql-test/main/delete.result +++ b/mysql-test/main/delete.result @@ -583,8 +583,7 @@ explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED a ALL NULL NULL NULL NULL 8 +1 PRIMARY a ALL NULL NULL NULL NULL 8 Using where; FirstMatch(t1) delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; select *from t1; @@ -600,8 +599,7 @@ where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED a ALL NULL NULL NULL NULL 8 +1 PRIMARY a ALL NULL NULL NULL NULL 8 Using where; FirstMatch(t1) delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; diff --git a/mysql-test/main/delete_single_to_multi.result b/mysql-test/main/delete_single_to_multi.result index 9bd5e93..6c13edc 100644 --- a/mysql-test/main/delete_single_to_multi.result +++ b/mysql-test/main/delete_single_to_multi.result @@ -26,6 +26,7 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.072146724, "nested_loop": [ { "table": { @@ -36,7 +37,9 @@ EXPLAIN "key_length": "26", "used_key_parts": ["n_name"], "ref": ["const"], + "loops": 1, "rows": 1, + "cost": 0.002024411, "filtered": 100, "index_condition": "nation.n_name = 'PERU'" } @@ -50,7 +53,9 @@ EXPLAIN "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], + "loops": 1, "rows": 11, + "cost": 0.014384434, "filtered": 100 } }, @@ -71,7 +76,9 @@ EXPLAIN "rows": 108, "selectivity_pct": 7.2 }, + "loops": 11.66666667, "rows": 11, + "cost": 0.055737879, "filtered": 7.199999809, "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" } @@ -115,6 +122,7 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.072146724, "nested_loop": [ { "table": { @@ -125,7 +133,9 @@ EXPLAIN "key_length": "26", "used_key_parts": ["n_name"], "ref": ["const"], + "loops": 1, "rows": 1, + "cost": 0.002024411, "filtered": 100, "index_condition": "nation.n_name = 'PERU'" } @@ -139,7 +149,9 @@ EXPLAIN "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], + "loops": 1, "rows": 11, + "cost": 0.014384434, "filtered": 100 } }, @@ -160,7 +172,9 @@ EXPLAIN "rows": 108, "selectivity_pct": 7.2 }, + "loops": 11.66666667, "rows": 11, + "cost": 0.055737879, "filtered": 7.199999809, "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" } @@ -200,7 +214,7 @@ s_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_suppkey 4 dbt3_s001.supplier.s_suppkey 16 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.partsupp.ps_partkey 1 Using where select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in @@ -230,7 +244,7 @@ s_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_suppkey 4 dbt3_s001.supplier.s_suppkey 16 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.partsupp.ps_partkey 1 Using where delete from partsupp where (ps_partkey, ps_suppkey) in @@ -267,7 +281,7 @@ where s_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_suppkey 4 dbt3_s001.supplier.s_suppkey 16 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.partsupp.ps_partkey 1 Using where select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part @@ -297,7 +311,7 @@ where s_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_suppkey 4 dbt3_s001.supplier.s_suppkey 16 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.partsupp.ps_partkey 1 Using where delete from partsupp where ps_partkey in (select p_partkey from part @@ -344,11 +358,11 @@ where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 -1 PRIMARY orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (7%) Using where; Using rowid filter -1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 17 Using where +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey 5 dbt3_s001.supplier.s_suppkey 100 Using where 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where -1 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where +1 PRIMARY orders eq_ref|filter PRIMARY,i_o_orderdate,i_o_custkey PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (7%) Using where; Using rowid filter +1 PRIMARY customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders where o_custkey in (select c_custkey from customer @@ -401,11 +415,11 @@ where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 -1 PRIMARY orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (7%) Using where; Using rowid filter -1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 17 Using where +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey 5 dbt3_s001.supplier.s_suppkey 100 Using where 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where -1 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where +1 PRIMARY orders eq_ref|filter PRIMARY,i_o_orderdate,i_o_custkey PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (7%) Using where; Using rowid filter +1 PRIMARY customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where delete from lineitem where l_orderkey in (select o_orderkey from orders where o_custkey in (select c_custkey from customer @@ -466,8 +480,8 @@ and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY customer ALL PRIMARY,i_c_nationkey NULL NULL NULL 150 Using where -1 PRIMARY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1 Using where +1 PRIMARY customer ALL PRIMARY,i_c_nationkey NULL NULL NULL 150 +1 PRIMARY nation ALL PRIMARY,i_n_regionkey NULL NULL NULL 25 Using where; Using join buffer (flat, BNL join) 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (9%) Using where; FirstMatch(nation); Using rowid filter explain format=json select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation @@ -479,29 +493,35 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.439864068, "nested_loop": [ { "table": { "table_name": "customer", "access_type": "ALL", "possible_keys": ["PRIMARY", "i_c_nationkey"], + "loops": 1, "rows": 150, - "filtered": 100, - "attached_condition": "customer.c_nationkey is not null" + "cost": 0.03493875, + "filtered": 100 } }, { - "table": { - "table_name": "nation", - "access_type": "eq_ref", - "possible_keys": ["PRIMARY", "i_n_regionkey"], - "key": "PRIMARY", - "key_length": "4", - "used_key_parts": ["n_nationkey"], - "ref": ["dbt3_s001.customer.c_nationkey"], - "rows": 1, - "filtered": 40, - "attached_condition": "nation.n_regionkey in (1,2)" + "block-nl-join": { + "table": { + "table_name": "nation", + "access_type": "ALL", + "possible_keys": ["PRIMARY", "i_n_regionkey"], + "loops": 150, + "rows": 25, + "cost": 0.162374625, + "filtered": 1.600000024, + "attached_condition": "nation.n_regionkey in (1,2)" + }, + "buffer_type": "flat", + "buffer_size": "6Kb", + "join_type": "BNL", + "attached_condition": "nation.n_nationkey = customer.c_nationkey" } }, { @@ -521,8 +541,10 @@ EXPLAIN "rows": 140, "selectivity_pct": 9.333333333 }, + "loops": 60, "rows": 11, - "filtered": 9.333333015, + "cost": 0.242550693, + "filtered": 8.571428299, "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'", "first_match": "nation" } @@ -581,8 +603,8 @@ and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY customer ALL PRIMARY,i_c_nationkey NULL NULL NULL 150 Using where -1 PRIMARY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1 Using where +1 PRIMARY customer ALL PRIMARY,i_c_nationkey NULL NULL NULL 150 +1 PRIMARY nation ALL PRIMARY,i_n_regionkey NULL NULL NULL 25 Using where 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (9%) Using where; FirstMatch(nation); Using rowid filter explain format=json delete from customer where c_nationkey in (select n_nationkey from nation @@ -594,29 +616,29 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.439864068, "nested_loop": [ { "table": { "table_name": "customer", "access_type": "ALL", "possible_keys": ["PRIMARY", "i_c_nationkey"], + "loops": 1, "rows": 150, - "filtered": 100, - "attached_condition": "customer.c_nationkey is not null" + "cost": 0.03493875, + "filtered": 100 } }, { "table": { "table_name": "nation", - "access_type": "eq_ref", + "access_type": "ALL", "possible_keys": ["PRIMARY", "i_n_regionkey"], - "key": "PRIMARY", - "key_length": "4", - "used_key_parts": ["n_nationkey"], - "ref": ["dbt3_s001.customer.c_nationkey"], - "rows": 1, - "filtered": 40, - "attached_condition": "nation.n_regionkey in (1,2)" + "loops": 150, + "rows": 25, + "cost": 0.162374625, + "filtered": 1.600000024, + "attached_condition": "nation.n_nationkey = customer.c_nationkey and nation.n_regionkey in (1,2)" } }, { @@ -636,8 +658,10 @@ EXPLAIN "rows": 140, "selectivity_pct": 9.333333333 }, + "loops": 60, "rows": 11, - "filtered": 9.333333015, + "cost": 0.242550693, + "filtered": 8.571428299, "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'", "first_match": "nation" } @@ -759,9 +783,8 @@ explain select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 28 -1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 -2 MATERIALIZED orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 28 Using index condition; Using where +1 PRIMARY orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 28 Using index condition; Using where; Start temporary +1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 End temporary explain format=json select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08'); @@ -769,50 +792,42 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.114174674, "nested_loop": [ { - "table": { - "table_name": "<subquery2>", - "access_type": "ALL", - "possible_keys": ["distinct_key"], - "rows": 28, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "nested_loop": [ - { - "table": { - "table_name": "orders", - "access_type": "range", - "possible_keys": ["i_o_orderdate", "i_o_custkey"], - "key": "i_o_orderdate", - "key_length": "4", - "used_key_parts": ["o_orderDATE"], - "rows": 28, - "filtered": 100, - "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'", - "attached_condition": "orders.o_custkey is not null" - } - } - ] + "duplicates_removal": [ + { + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["i_o_orderdate", "i_o_custkey"], + "key": "i_o_orderdate", + "key_length": "4", + "used_key_parts": ["o_orderDATE"], + "loops": 1, + "rows": 28, + "cost": 0.035889016, + "filtered": 100, + "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'", + "attached_condition": "orders.o_custkey is not null" + } + }, + { + "table": { + "table_name": "customer", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["c_custkey"], + "ref": ["dbt3_s001.orders.o_custkey"], + "loops": 28, + "rows": 1, + "cost": 0.048617528, + "filtered": 3.571428537 } } - } - }, - { - "table": { - "table_name": "customer", - "access_type": "eq_ref", - "possible_keys": ["PRIMARY"], - "key": "PRIMARY", - "key_length": "4", - "used_key_parts": ["c_custkey"], - "ref": ["dbt3_s001.orders.o_custkey"], - "rows": 1, - "filtered": 100 - } + ] } ] } @@ -851,9 +866,8 @@ explain delete from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 28 -1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 -2 MATERIALIZED orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 28 Using index condition; Using where +1 PRIMARY orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 28 Using index condition; Using where; Start temporary +1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 End temporary explain format=json delete from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08'); @@ -861,50 +875,42 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.083497902, "nested_loop": [ { - "table": { - "table_name": "<subquery2>", - "access_type": "ALL", - "possible_keys": ["distinct_key"], - "rows": 28, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "nested_loop": [ - { - "table": { - "table_name": "orders", - "access_type": "range", - "possible_keys": ["i_o_orderdate", "i_o_custkey"], - "key": "i_o_orderdate", - "key_length": "4", - "used_key_parts": ["o_orderDATE"], - "rows": 28, - "filtered": 100, - "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'", - "attached_condition": "orders.o_custkey is not null" - } - } - ] + "duplicates_removal": [ + { + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["i_o_orderdate", "i_o_custkey"], + "key": "i_o_orderdate", + "key_length": "4", + "used_key_parts": ["o_orderDATE"], + "loops": 1, + "rows": 28, + "cost": 0.035889016, + "filtered": 100, + "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'", + "attached_condition": "orders.o_custkey is not null" + } + }, + { + "table": { + "table_name": "customer", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["c_custkey"], + "ref": ["dbt3_s001.orders.o_custkey"], + "loops": 28, + "rows": 1, + "cost": 0.017940756, + "filtered": 3.571428537 } } - } - }, - { - "table": { - "table_name": "customer", - "access_type": "eq_ref", - "possible_keys": ["PRIMARY"], - "key": "PRIMARY", - "key_length": "4", - "used_key_parts": ["c_custkey"], - "ref": ["dbt3_s001.orders.o_custkey"], - "rows": 1, - "filtered": 100 - } + ] } ] } @@ -1125,18 +1131,22 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.085533248, "nested_loop": [ { "table": { "table_name": "<subquery2>", "access_type": "ALL", "possible_keys": ["distinct_key"], + "loops": 1, "rows": 28, + "cost": 0.03691572, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": 0.053826401, "having_condition": "count(orders.o_custkey) > 1", "temporary_table": { "nested_loop": [ @@ -1148,7 +1158,9 @@ EXPLAIN "key": "i_o_orderdate", "key_length": "4", "used_key_parts": ["o_orderDATE"], + "loops": 1, "rows": 28, + "cost": 0.035889016, "filtered": 100, "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'" } @@ -1168,7 +1180,9 @@ EXPLAIN "key_length": "4", "used_key_parts": ["c_custkey"], "ref": ["<subquery2>.o_custkey"], + "loops": 28, "rows": 1, + "cost": 0.048617528, "filtered": 100 } } @@ -1204,18 +1218,22 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.054856476, "nested_loop": [ { "table": { "table_name": "<subquery2>", "access_type": "ALL", "possible_keys": ["distinct_key"], + "loops": 1, "rows": 28, + "cost": 0.03691572, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": 0.053826401, "having_condition": "count(orders.o_custkey) > 1", "temporary_table": { "nested_loop": [ @@ -1227,7 +1245,9 @@ EXPLAIN "key": "i_o_orderdate", "key_length": "4", "used_key_parts": ["o_orderDATE"], + "loops": 1, "rows": 28, + "cost": 0.035889016, "filtered": 100, "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'" } @@ -1247,7 +1267,9 @@ EXPLAIN "key_length": "4", "used_key_parts": ["c_custkey"], "ref": ["<subquery2>.o_custkey"], + "loops": 28, "rows": 1, + "cost": 0.017940756, "filtered": 100 } } @@ -1279,7 +1301,7 @@ where o_orderDATE between '1992-01-09' and '1993-03-08' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer ALL PRIMARY NULL NULL NULL 150 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 dbt3_s001.customer.c_custkey 1 -2 MATERIALIZED orders range i_o_orderdate i_o_orderdate 4 NULL 242 Using index condition; Using temporary +2 MATERIALIZED orders ALL i_o_orderdate NULL NULL NULL 1500 Using where; Using temporary select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08' group by o_custkey having count(o_custkey) > 5); @@ -1304,7 +1326,7 @@ where o_orderDATE between '1992-01-09' and '1993-03-08' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer ALL PRIMARY NULL NULL NULL 150 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 dbt3_s001.customer.c_custkey 1 -2 MATERIALIZED orders range i_o_orderdate i_o_orderdate 4 NULL 242 Using index condition; Using temporary +2 MATERIALIZED orders ALL i_o_orderdate NULL NULL NULL 1500 Using where; Using temporary delete from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08' group by o_custkey having count(o_custkey) > 5); @@ -2329,9 +2351,9 @@ o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); select o_orderkey, o_totalprice from t; o_orderkey o_totalprice +1221 117397.16 324 26868.85 1856 189361.42 -1221 117397.16 4903 34363.63 5607 24660.06 delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and @@ -2367,9 +2389,9 @@ o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); select o_orderkey, o_totalprice from t; o_orderkey o_totalprice -1856 189361.42 -324 26868.85 1221 117397.16 +324 26868.85 +1856 189361.42 4903 34363.63 5607 24660.06 delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index 586d0d5..a7ecfa0 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -1132,13 +1132,10 @@ select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a fr analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except ALL (select t3.a from t3 order by b))q where t1.a=q.a; -<<<<<<< 21505331eaaf9b4e9ab6b393da821ecd87975be0 select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except ALL (select t3.a from t3 order by b))q where t1.a=q.a; drop table t1,t2,t3; -======= ->>>>>>> MDEV-7487 Semi-join optimization for single-table update/delete statements --echo # --echo # MDEV-16549: Server crashes in Item_field::fix_fields on query with --echo # view and subquery, Assertion `context' failed, Assertion `field' failed @@ -1157,7 +1154,6 @@ DROP TABLE t1; --echo # --echo # End of 10.3 tests --echo # -<<<<<<< 21505331eaaf9b4e9ab6b393da821ecd87975be0 --echo # --echo # Test of "Derived tables and union can now create distinct keys" @@ -1249,8 +1245,6 @@ drop table t1; --echo # --echo # End of 11.0 tests --echo # -======= ->>>>>>> MDEV-7487 Semi-join optimization for single-table update/delete statements --echo # --echo # MDEV-28883: single/multi-table UPDATE/DELETE whose WHERE condition @@ -1458,8 +1452,4 @@ deallocate prepare stmt; drop table t1,t2,t3; -<<<<<<< 21505331eaaf9b4e9ab6b393da821ecd87975be0 --echo # End of MariaDB 11.1 tests -======= ---echo # End of MariaDB 11.0 tests ->>>>>>> MDEV-7487 Semi-join optimization for single-table update/delete statements diff --git a/mysql-test/main/derived_1.result b/mysql-test/main/derived_1.result new file mode 100644 index 0000000..a61f5d5 --- /dev/null +++ b/mysql-test/main/derived_1.result @@ -0,0 +1,19 @@ +# +# MDEV-28883: single/multi-table UPDATE/DELETE whose WHERE condition +# contains subquery from mergeable derived table +# that uses the updated/deleted table +# +create table t1 (pk int, a int); +insert into t1 values (1,3), (2, 7), (3,1), (4,9); +create table t2 (pk int, a int); +insert into t2 values (1,3), (2, 7), (3,1), (4,9); +create table t3 (a int); +insert into t3 VALUES (0),(1); +explain delete from t1 using t1,t3 +where t1.a=t3.a and +t1.a = ( select * from (select a from t2) dt where dt.a > 7); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using where +drop table t1,t2,t3; diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 6c9501d..fd3530b 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -13134,6 +13134,7 @@ EXPLAIN "materialized": { "query_block": { "select_id": 3, + "cost": "COST_REPLACED", "filesort": { "sort_key": "t1.f2", "temporary_table": { @@ -13142,7 +13143,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 2, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.f2 < 2" } diff --git a/mysql-test/main/log_state.result b/mysql-test/main/log_state.result index 1b1c737..18c8da7 100644 --- a/mysql-test/main/log_state.result +++ b/mysql-test/main/log_state.result @@ -243,7 +243,7 @@ rows_examined sql_text 4 UPDATE t1 SET a=a+sleep(.02) WHERE a>2 8 UPDATE t1 SET a=a+sleep(.02) ORDER BY a DESC 1 UPDATE t2 set b=b+sleep(.02) limit 1 -10 UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2) +6 UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2) 6 DELETE FROM t1 WHERE a=a+sleep(.02) ORDER BY a LIMIT 2 disconnect con2; connection default; diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result index af254be..2364ee7 100644 --- a/mysql-test/main/multi_update.result +++ b/mysql-test/main/multi_update.result @@ -1302,42 +1302,24 @@ t1.c1 > 1 and exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where -<<<<<<< 3f0764f42cf801a4410f32a4ee7b2b4f4ce7e3fb 1 PRIMARY t2 range idx idx 5 NULL 3 Using index condition; Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 -======= -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 -2 MATERIALIZED t2 range idx idx 5 NULL 3 Using index condition; Using where ->>>>>>> Fixes of MDEV-30538 and MDEV-30586 for 10.4 adjusted for 11.0. explain delete from t1 using t1,t3 where t1.c2 = t3.c2 and t1.c1 > 1 and exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where -<<<<<<< 3f0764f42cf801a4410f32a4ee7b2b4f4ce7e3fb 1 PRIMARY t2 range idx idx 5 NULL 3 Using where; FirstMatch(t1) 1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index -======= -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index -2 MATERIALIZED t2 range idx idx 5 NULL 3 Using where ->>>>>>> Fixes of MDEV-30538 and MDEV-30586 for 10.4 adjusted for 11.0. explain update t1,t3 set t1.c1 = t1.c1+10 where t1.c2 = t3.c2 and t1.c1 > 1 and exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where -<<<<<<< 3f0764f42cf801a4410f32a4ee7b2b4f4ce7e3fb 1 PRIMARY t2 range idx idx 5 NULL 3 Using where; FirstMatch(t1) 1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index -======= -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index -2 MATERIALIZED t2 range idx idx 5 NULL 3 Using where ->>>>>>> Fixes of MDEV-30538 and MDEV-30586 for 10.4 adjusted for 11.0. create table t as select * from t1; select * from t1,t3 where t1.c2 = t3.c2 and diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index c8462bd..2f56847 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -218,16 +218,14 @@ INSERT INTO t2 VALUES (1), (2), (3); # EXPLAIN UPDATE t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 33.33 Using where; FirstMatch 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -248,9 +246,9 @@ Handler_read_key 4 Handler_read_rnd_next 5 # Status of testing query execution: Variable_name Value -Handler_read_key 5 +Handler_read_key 4 Handler_read_rnd 3 -Handler_read_rnd_next 12 +Handler_read_rnd_next 9 Handler_update 3 DROP TABLE t1, t2; @@ -904,17 +902,13 @@ INSERT INTO t2 VALUES (1), (2), (3), (1000); EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 -s +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t1) FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 -s +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 Using where; FirstMatch(t1) # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -935,8 +929,8 @@ Handler_read_key 4 Handler_read_rnd_next 9 # Status of testing query execution: Variable_name Value -Handler_read_key 7 -Handler_read_rnd_next 8 +Handler_read_key 4 +Handler_read_rnd_next 10 Handler_update 3 DROP TABLE t1, t2; @@ -2830,16 +2824,14 @@ INSERT INTO t2 VALUES (1), (2), (3); EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 FirstMatch(t1) -s +1 PRIMARY <derived3> eq_ref distinct_key distinct_key 5 test.t1.a 1 3 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 100.00 FirstMatch(t1) -s +1 PRIMARY <derived3> eq_ref distinct_key distinct_key 5 test.t1.a 1 100.00 3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort # Status of EXPLAIN EXTENDED query Variable_name Value diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 1e32802..d5213d4 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -4495,7 +4495,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "rowid_filters": [ { "key": "a", - "build_cost": 0.174715752, + "build_cost": 0.001129926, "rows": 3 } ] @@ -4570,7 +4570,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "rowid_filters": [ { "key": "a", - "build_cost": 0.174715752, + "build_cost": 0.001129926, "rows": 3 } ] diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index ce156f1..3a246e2 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -634,6 +634,18 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); +select * from t11; +a b +0 10 +1 11 +select * from t12; +a b +33 10 +22 11 +delete from t11; +delete from t12; +insert into t11 values (0, 10),(1, 11),(2, 12); +insert into t12 values (33, 10),(22, 11),(2, 12); delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index 8fefa39..578dbe8 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -387,6 +387,12 @@ insert into t2 values (1, 21),(2, 12),(3, 23); select * from t11; select * from t12; delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); +select * from t11; +select * from t12; +delete from t11; +delete from t12; +insert into t11 values (0, 10),(1, 11),(2, 12); +insert into t12 values (33, 10),(22, 11),(2, 12); -- error ER_SUBQUERY_NO_1_ROW delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index 37a503d..3be4f07 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -638,6 +638,18 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); +select * from t11; +a b +0 10 +1 11 +select * from t12; +a b +33 10 +22 11 +delete from t11; +delete from t12; +insert into t11 values (0, 10),(1, 11),(2, 12); +insert into t12 values (33, 10),(22, 11),(2, 12); delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 32a6358..58124a9 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -641,6 +641,18 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); +select * from t11; +a b +0 10 +1 11 +select * from t12; +a b +33 10 +22 11 +delete from t11; +delete from t12; +insert into t11 values (0, 10),(1, 11),(2, 12); +insert into t12 values (33, 10),(22, 11),(2, 12); delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index d75c421..10153b0 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -637,6 +637,18 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); +select * from t11; +a b +0 10 +1 11 +select * from t12; +a b +33 10 +22 11 +delete from t11; +delete from t12; +insert into t11 values (0, 10),(1, 11),(2, 12); +insert into t12 values (33, 10),(22, 11),(2, 12); delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 336936d..cd31b9b 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -640,6 +640,18 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); +select * from t11; +a b +0 10 +1 11 +select * from t12; +a b +33 10 +22 11 +delete from t11; +delete from t12; +insert into t11 values (0, 10),(1, 11),(2, 12); +insert into t12 values (33, 10),(22, 11),(2, 12); delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index c34dfe6..eba0f55 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -637,6 +637,18 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); +select * from t11; +a b +0 10 +1 11 +select * from t12; +a b +33 10 +22 11 +delete from t11; +delete from t12; +insert into t11 values (0, 10),(1, 11),(2, 12); +insert into t12 values (33, 10),(22, 11),(2, 12); delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); diff --git a/mysql-test/main/update_single_to_multi.result b/mysql-test/main/update_single_to_multi.result index d020fd1..be97047 100644 --- a/mysql-test/main/update_single_to_multi.result +++ b/mysql-test/main/update_single_to_multi.result @@ -26,6 +26,7 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.072146724, "nested_loop": [ { "table": { @@ -36,7 +37,9 @@ EXPLAIN "key_length": "26", "used_key_parts": ["n_name"], "ref": ["const"], + "loops": 1, "rows": 1, + "cost": 0.002024411, "filtered": 100, "index_condition": "nation.n_name = 'PERU'" } @@ -50,7 +53,9 @@ EXPLAIN "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], + "loops": 1, "rows": 11, + "cost": 0.014384434, "filtered": 100 } }, @@ -71,7 +76,9 @@ EXPLAIN "rows": 108, "selectivity_pct": 7.2 }, + "loops": 11.66666667, "rows": 11, + "cost": 0.055737879, "filtered": 7.199999809, "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" } @@ -110,6 +117,7 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.072146724, "nested_loop": [ { "table": { @@ -120,7 +128,9 @@ EXPLAIN "key_length": "26", "used_key_parts": ["n_name"], "ref": ["const"], + "loops": 1, "rows": 1, + "cost": 0.002024411, "filtered": 100, "index_condition": "nation.n_name = 'PERU'" } @@ -134,7 +144,9 @@ EXPLAIN "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], + "loops": 1, "rows": 11, + "cost": 0.014384434, "filtered": 100 } }, @@ -155,7 +167,9 @@ EXPLAIN "rows": 108, "selectivity_pct": 7.2 }, + "loops": 11.66666667, "rows": 11, + "cost": 0.055737879, "filtered": 7.199999809, "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" } @@ -205,7 +219,7 @@ s_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_suppkey 4 dbt3_s001.supplier.s_suppkey 16 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.partsupp.ps_partkey 1 Using where select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in @@ -229,7 +243,7 @@ s_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_suppkey 4 dbt3_s001.supplier.s_suppkey 16 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.partsupp.ps_partkey 1 Using where update partsupp set ps_supplycost = ps_supplycost+2 where (ps_partkey, ps_suppkey) in @@ -276,7 +290,7 @@ where s_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_suppkey 4 dbt3_s001.supplier.s_suppkey 16 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.partsupp.ps_partkey 1 Using where select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part @@ -300,7 +314,7 @@ where s_nationkey in (select n_nationkey from nation where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_suppkey 4 dbt3_s001.supplier.s_suppkey 16 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.partsupp.ps_partkey 1 Using where update partsupp set ps_supplycost = ps_supplycost+10 where ps_partkey in (select p_partkey from part @@ -357,11 +371,11 @@ where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 -1 PRIMARY orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (7%) Using where; Using rowid filter -1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 17 Using where +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey 5 dbt3_s001.supplier.s_suppkey 100 Using where 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where -1 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where +1 PRIMARY orders eq_ref|filter PRIMARY,i_o_orderdate,i_o_custkey PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (7%) Using where; Using rowid filter +1 PRIMARY customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders where o_custkey in (select c_custkey from customer @@ -377,10 +391,10 @@ where p_retailprice between 901 and 1000 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); l_orderkey l_linenumber l_tax +4996 1 0.01 933 1 0.04 2500 2 0.02 2500 4 0.02 -4996 1 0.01 explain update lineitem set l_tax = (l_tax*100+1)/100 where l_orderkey in (select o_orderkey from orders where o_custkey in @@ -399,11 +413,11 @@ where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 -1 PRIMARY orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (7%) Using where; Using rowid filter -1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 17 Using where +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey 5 dbt3_s001.supplier.s_suppkey 100 Using where 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where -1 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where +1 PRIMARY orders eq_ref|filter PRIMARY,i_o_orderdate,i_o_custkey PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (7%) Using where; Using rowid filter +1 PRIMARY customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where update lineitem set l_tax = (l_tax*100+1)/100 where l_orderkey in (select o_orderkey from orders where o_custkey in (select c_custkey from customer @@ -433,10 +447,10 @@ where p_retailprice between 901 and 1000 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); l_orderkey l_linenumber l_tax +4996 1 0.02 933 1 0.05 2500 2 0.03 2500 4 0.03 -4996 1 0.02 update lineitem set l_tax = (l_tax*100-1)/100 where l_orderkey in (select o_orderkey from orders where o_custkey in (select c_custkey from customer @@ -466,10 +480,10 @@ where p_retailprice between 901 and 1000 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); l_orderkey l_linenumber l_tax +4996 1 0.01 933 1 0.04 2500 2 0.02 2500 4 0.02 -4996 1 0.01 # FirstMatch # ========== set optimizer_switch='materialization=off'; @@ -480,8 +494,8 @@ and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY customer ALL PRIMARY,i_c_nationkey NULL NULL NULL 150 Using where -1 PRIMARY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1 Using where +1 PRIMARY customer ALL PRIMARY,i_c_nationkey NULL NULL NULL 150 +1 PRIMARY nation ALL PRIMARY,i_n_regionkey NULL NULL NULL 25 Using where; Using join buffer (flat, BNL join) 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (9%) Using where; FirstMatch(nation); Using rowid filter explain format=json select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation @@ -493,29 +507,35 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.439864068, "nested_loop": [ { "table": { "table_name": "customer", "access_type": "ALL", "possible_keys": ["PRIMARY", "i_c_nationkey"], + "loops": 1, "rows": 150, - "filtered": 100, - "attached_condition": "customer.c_nationkey is not null" + "cost": 0.03493875, + "filtered": 100 } }, { - "table": { - "table_name": "nation", - "access_type": "eq_ref", - "possible_keys": ["PRIMARY", "i_n_regionkey"], - "key": "PRIMARY", - "key_length": "4", - "used_key_parts": ["n_nationkey"], - "ref": ["dbt3_s001.customer.c_nationkey"], - "rows": 1, - "filtered": 40, - "attached_condition": "nation.n_regionkey in (1,2)" + "block-nl-join": { + "table": { + "table_name": "nation", + "access_type": "ALL", + "possible_keys": ["PRIMARY", "i_n_regionkey"], + "loops": 150, + "rows": 25, + "cost": 0.162374625, + "filtered": 1.600000024, + "attached_condition": "nation.n_regionkey in (1,2)" + }, + "buffer_type": "flat", + "buffer_size": "6Kb", + "join_type": "BNL", + "attached_condition": "nation.n_nationkey = customer.c_nationkey" } }, { @@ -535,8 +555,10 @@ EXPLAIN "rows": 140, "selectivity_pct": 9.333333333 }, + "loops": 60, "rows": 11, - "filtered": 9.333333015, + "cost": 0.242550693, + "filtered": 8.571428299, "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'", "first_match": "nation" } @@ -550,38 +572,38 @@ and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal -Customer#000000007 9561.95 -Customer#000000008 6819.74 +Customer#000000059 3458.6 +Customer#000000106 3288.42 Customer#000000017 6.34 -Customer#000000019 8914.71 +Customer#000000047 274.58 +Customer#000000092 1182.91 +Customer#000000101 7470.96 Customer#000000022 591.98 -Customer#000000025 7133.7 -Customer#000000028 1007.18 -Customer#000000037 -917.75 Customer#000000040 1335.3 -Customer#000000047 274.58 -Customer#000000059 3458.6 -Customer#000000061 1536.24 Customer#000000064 -646.64 -Customer#000000067 8166.59 -Customer#000000077 1738.87 -Customer#000000082 9468.34 +Customer#000000122 7865.46 +Customer#000000028 1007.18 +Customer#000000037 -917.75 Customer#000000091 4643.14 -Customer#000000092 1182.91 +Customer#000000115 7508.92 +Customer#000000067 8166.59 Customer#000000094 5500.11 -Customer#000000097 2164.48 -Customer#000000101 7470.96 Customer#000000103 2757.45 -Customer#000000106 3288.42 -Customer#000000115 7508.92 -Customer#000000121 6428.32 -Customer#000000122 7865.46 -Customer#000000124 1842.49 -Customer#000000127 9280.71 Customer#000000130 5073.58 -Customer#000000133 2314.67 Customer#000000139 7897.78 Customer#000000142 2209.81 +Customer#000000025 7133.7 +Customer#000000008 6819.74 +Customer#000000061 1536.24 +Customer#000000077 1738.87 +Customer#000000097 2164.48 +Customer#000000121 6428.32 +Customer#000000133 2314.67 +Customer#000000007 9561.95 +Customer#000000019 8914.71 +Customer#000000082 9468.34 +Customer#000000124 1842.49 +Customer#000000127 9280.71 explain update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) @@ -589,8 +611,8 @@ and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY customer ALL PRIMARY,i_c_nationkey NULL NULL NULL 150 Using where -1 PRIMARY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1 Using where +1 PRIMARY customer ALL PRIMARY,i_c_nationkey NULL NULL NULL 150 +1 PRIMARY nation ALL PRIMARY,i_n_regionkey NULL NULL NULL 25 Using where 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (9%) Using where; FirstMatch(nation); Using rowid filter explain format=json update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation @@ -602,29 +624,29 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.439864068, "nested_loop": [ { "table": { "table_name": "customer", "access_type": "ALL", "possible_keys": ["PRIMARY", "i_c_nationkey"], + "loops": 1, "rows": 150, - "filtered": 100, - "attached_condition": "customer.c_nationkey is not null" + "cost": 0.03493875, + "filtered": 100 } }, { "table": { "table_name": "nation", - "access_type": "eq_ref", + "access_type": "ALL", "possible_keys": ["PRIMARY", "i_n_regionkey"], - "key": "PRIMARY", - "key_length": "4", - "used_key_parts": ["n_nationkey"], - "ref": ["dbt3_s001.customer.c_nationkey"], - "rows": 1, - "filtered": 40, - "attached_condition": "nation.n_regionkey in (1,2)" + "loops": 150, + "rows": 25, + "cost": 0.162374625, + "filtered": 1.600000024, + "attached_condition": "nation.n_nationkey = customer.c_nationkey and nation.n_regionkey in (1,2)" } }, { @@ -644,8 +666,10 @@ EXPLAIN "rows": 140, "selectivity_pct": 9.333333333 }, + "loops": 60, "rows": 11, - "filtered": 9.333333015, + "cost": 0.242550693, + "filtered": 8.571428299, "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'", "first_match": "nation" } @@ -664,38 +688,38 @@ and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal -Customer#000000007 9571.95 -Customer#000000008 6829.74 +Customer#000000059 3468.6 +Customer#000000106 3298.42 Customer#000000017 16.34 -Customer#000000019 8924.71 +Customer#000000047 284.58 +Customer#000000092 1192.91 +Customer#000000101 7480.96 Customer#000000022 601.98 -Customer#000000025 7143.7 -Customer#000000028 1017.18 -Customer#000000037 -907.75 Customer#000000040 1345.3 -Customer#000000047 284.58 -Customer#000000059 3468.6 -Customer#000000061 1546.24 Customer#000000064 -636.64 -Customer#000000067 8176.59 -Customer#000000077 1748.87 -Customer#000000082 9478.34 +Customer#000000122 7875.46 +Customer#000000028 1017.18 +Customer#000000037 -907.75 Customer#000000091 4653.14 -Customer#000000092 1192.91 +Customer#000000115 7518.92 +Customer#000000067 8176.59 Customer#000000094 5510.11 -Customer#000000097 2174.48 -Customer#000000101 7480.96 Customer#000000103 2767.45 -Customer#000000106 3298.42 -Customer#000000115 7518.92 -Customer#000000121 6438.32 -Customer#000000122 7875.46 -Customer#000000124 1852.49 -Customer#000000127 9290.71 Customer#000000130 5083.58 -Customer#000000133 2324.67 Customer#000000139 7907.78 Customer#000000142 2219.81 +Customer#000000025 7143.7 +Customer#000000008 6829.74 +Customer#000000061 1546.24 +Customer#000000077 1748.87 +Customer#000000097 2174.48 +Customer#000000121 6438.32 +Customer#000000133 2324.67 +Customer#000000007 9571.95 +Customer#000000019 8924.71 +Customer#000000082 9478.34 +Customer#000000124 1852.49 +Customer#000000127 9290.71 update customer set c_acctbal = c_acctbal-10 where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) and @@ -707,38 +731,38 @@ and c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal -Customer#000000007 9561.95 -Customer#000000008 6819.74 +Customer#000000059 3458.6 +Customer#000000106 3288.42 Customer#000000017 6.34 -Customer#000000019 8914.71 +Customer#000000047 274.58 +Customer#000000092 1182.91 +Customer#000000101 7470.96 Customer#000000022 591.98 -Customer#000000025 7133.7 -Customer#000000028 1007.18 -Customer#000000037 -917.75 Customer#000000040 1335.3 -Customer#000000047 274.58 -Customer#000000059 3458.6 -Customer#000000061 1536.24 Customer#000000064 -646.64 -Customer#000000067 8166.59 -Customer#000000077 1738.87 -Customer#000000082 9468.34 +Customer#000000122 7865.46 +Customer#000000028 1007.18 +Customer#000000037 -917.75 Customer#000000091 4643.14 -Customer#000000092 1182.91 +Customer#000000115 7508.92 +Customer#000000067 8166.59 Customer#000000094 5500.11 -Customer#000000097 2164.48 -Customer#000000101 7470.96 Customer#000000103 2757.45 -Customer#000000106 3288.42 -Customer#000000115 7508.92 -Customer#000000121 6428.32 -Customer#000000122 7865.46 -Customer#000000124 1842.49 -Customer#000000127 9280.71 Customer#000000130 5073.58 -Customer#000000133 2314.67 Customer#000000139 7897.78 Customer#000000142 2209.81 +Customer#000000025 7133.7 +Customer#000000008 6819.74 +Customer#000000061 1536.24 +Customer#000000077 1738.87 +Customer#000000097 2164.48 +Customer#000000121 6428.32 +Customer#000000133 2314.67 +Customer#000000007 9561.95 +Customer#000000019 8914.71 +Customer#000000082 9468.34 +Customer#000000124 1842.49 +Customer#000000127 9280.71 set optimizer_switch='materialization=default'; explain select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU') @@ -805,9 +829,8 @@ explain select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 28 -1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 -2 MATERIALIZED orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 28 Using index condition; Using where +1 PRIMARY orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 28 Using index condition; Using where; Start temporary +1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 End temporary explain format=json select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08'); @@ -815,50 +838,42 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.114174674, "nested_loop": [ { - "table": { - "table_name": "<subquery2>", - "access_type": "ALL", - "possible_keys": ["distinct_key"], - "rows": 28, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "nested_loop": [ - { - "table": { - "table_name": "orders", - "access_type": "range", - "possible_keys": ["i_o_orderdate", "i_o_custkey"], - "key": "i_o_orderdate", - "key_length": "4", - "used_key_parts": ["o_orderDATE"], - "rows": 28, - "filtered": 100, - "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'", - "attached_condition": "orders.o_custkey is not null" - } - } - ] + "duplicates_removal": [ + { + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["i_o_orderdate", "i_o_custkey"], + "key": "i_o_orderdate", + "key_length": "4", + "used_key_parts": ["o_orderDATE"], + "loops": 1, + "rows": 28, + "cost": 0.035889016, + "filtered": 100, + "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'", + "attached_condition": "orders.o_custkey is not null" + } + }, + { + "table": { + "table_name": "customer", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["c_custkey"], + "ref": ["dbt3_s001.orders.o_custkey"], + "loops": 28, + "rows": 1, + "cost": 0.048617528, + "filtered": 3.571428537 } } - } - }, - { - "table": { - "table_name": "customer", - "access_type": "eq_ref", - "possible_keys": ["PRIMARY"], - "key": "PRIMARY", - "key_length": "4", - "used_key_parts": ["c_custkey"], - "ref": ["dbt3_s001.orders.o_custkey"], - "rows": 1, - "filtered": 100 - } + ] } ] } @@ -894,9 +909,8 @@ explain update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 28 -1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 -2 MATERIALIZED orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 28 Using index condition; Using where +1 PRIMARY orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 28 Using index condition; Using where; Start temporary +1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 End temporary explain format=json update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08'); @@ -904,50 +918,42 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.114174674, "nested_loop": [ { - "table": { - "table_name": "<subquery2>", - "access_type": "ALL", - "possible_keys": ["distinct_key"], - "rows": 28, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "nested_loop": [ - { - "table": { - "table_name": "orders", - "access_type": "range", - "possible_keys": ["i_o_orderdate", "i_o_custkey"], - "key": "i_o_orderdate", - "key_length": "4", - "used_key_parts": ["o_orderDATE"], - "rows": 28, - "filtered": 100, - "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'", - "attached_condition": "orders.o_custkey is not null" - } - } - ] + "duplicates_removal": [ + { + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["i_o_orderdate", "i_o_custkey"], + "key": "i_o_orderdate", + "key_length": "4", + "used_key_parts": ["o_orderDATE"], + "loops": 1, + "rows": 28, + "cost": 0.035889016, + "filtered": 100, + "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'", + "attached_condition": "orders.o_custkey is not null" + } + }, + { + "table": { + "table_name": "customer", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["c_custkey"], + "ref": ["dbt3_s001.orders.o_custkey"], + "loops": 28, + "rows": 1, + "cost": 0.048617528, + "filtered": 3.571428537 } } - } - }, - { - "table": { - "table_name": "customer", - "access_type": "eq_ref", - "possible_keys": ["PRIMARY"], - "key": "PRIMARY", - "key_length": "4", - "used_key_parts": ["c_custkey"], - "ref": ["dbt3_s001.orders.o_custkey"], - "rows": 1, - "filtered": 100 - } + ] } ] } @@ -1256,18 +1262,22 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.085533248, "nested_loop": [ { "table": { "table_name": "<subquery2>", "access_type": "ALL", "possible_keys": ["distinct_key"], + "loops": 1, "rows": 28, + "cost": 0.03691572, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": 0.053826401, "having_condition": "count(orders.o_custkey) > 1", "temporary_table": { "nested_loop": [ @@ -1279,7 +1289,9 @@ EXPLAIN "key": "i_o_orderdate", "key_length": "4", "used_key_parts": ["o_orderDATE"], + "loops": 1, "rows": 28, + "cost": 0.035889016, "filtered": 100, "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'" } @@ -1299,7 +1311,9 @@ EXPLAIN "key_length": "4", "used_key_parts": ["c_custkey"], "ref": ["<subquery2>.o_custkey"], + "loops": 28, "rows": 1, + "cost": 0.048617528, "filtered": 100 } } @@ -1331,18 +1345,22 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.085533248, "nested_loop": [ { "table": { "table_name": "<subquery2>", "access_type": "ALL", "possible_keys": ["distinct_key"], + "loops": 1, "rows": 28, + "cost": 0.03691572, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": 0.053826401, "having_condition": "count(orders.o_custkey) > 1", "temporary_table": { "nested_loop": [ @@ -1354,7 +1372,9 @@ EXPLAIN "key": "i_o_orderdate", "key_length": "4", "used_key_parts": ["o_orderDATE"], + "loops": 1, "rows": 28, + "cost": 0.035889016, "filtered": 100, "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'" } @@ -1374,7 +1394,9 @@ EXPLAIN "key_length": "4", "used_key_parts": ["c_custkey"], "ref": ["<subquery2>.o_custkey"], + "loops": 28, "rows": 1, + "cost": 0.048617528, "filtered": 100 } } @@ -1412,7 +1434,7 @@ where o_orderDATE between '1992-01-09' and '1993-03-08' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer ALL PRIMARY NULL NULL NULL 150 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 dbt3_s001.customer.c_custkey 1 -2 MATERIALIZED orders range i_o_orderdate i_o_orderdate 4 NULL 242 Using index condition; Using temporary +2 MATERIALIZED orders ALL i_o_orderdate NULL NULL NULL 1500 Using where; Using temporary select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08' group by o_custkey having count(o_custkey) > 5); @@ -1433,7 +1455,7 @@ where o_orderDATE between '1992-01-09' and '1993-03-08' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer ALL PRIMARY NULL NULL NULL 150 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 dbt3_s001.customer.c_custkey 1 -2 MATERIALIZED orders range i_o_orderdate i_o_orderdate 4 NULL 242 Using index condition; Using temporary +2 MATERIALIZED orders ALL i_o_orderdate NULL NULL NULL 1500 Using where; Using temporary update customer set c_acctbal = c_acctbal-1 where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1993-03-08' group by o_custkey having count(o_custkey) > 5); diff --git a/mysql-test/main/update_use_source.result b/mysql-test/main/update_use_source.result index 994b38a..99d7242 100644 --- a/mysql-test/main/update_use_source.result +++ b/mysql-test/main/update_use_source.result @@ -76,8 +76,7 @@ rollback; explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED a ALL NULL NULL NULL NULL 8 +1 PRIMARY a ALL NULL NULL NULL NULL 8 Using where; FirstMatch(t1) start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; affected rows: 4 @@ -318,8 +317,7 @@ rollback; explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED a range t1_c2 t1_c2 5 NULL 2 Using where; Using index +1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 8 Using index; FirstMatch(t1) start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; affected rows: 4 @@ -559,9 +557,8 @@ rollback; # explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED a range t1_c2 t1_c2 5 NULL 2 Using where; Using index +1 PRIMARY a index t1_c2 t1_c2 10 NULL 8 Using where; Using index; LooseScan +1 PRIMARY t1 ref t1_c2 t1_c2 5 test.a.c2 1 start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; affected rows: 4 @@ -802,9 +799,8 @@ rollback; # explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED a range t1_c2 t1_c2 5 NULL 2 Using where; Using index +1 PRIMARY a index t1_c2 t1_c2 10 NULL 8 Using where; Using index; LooseScan +1 PRIMARY t1 ref t1_c2 t1_c2 5 test.a.c2 1 start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; affected rows: 4 @@ -1199,7 +1195,6 @@ create table t1 (c1 integer) engine=InnoDb; create table t2 (c1 integer) engine=InnoDb; create view v1 as select t1.c1 as "t1c1" ,t2.c1 as "t2c1" from t1,t2 where t1.c1=t2.c1; update v1 set t1c1=2 order by 1; -ERROR 42S22: Unknown column '1' in 'order clause' update v1 set t1c1=2 limit 1; drop table t1; drop table t2; diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 609f5f0..6339d9d 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -351,10 +351,6 @@ bool Sql_cmd_delete::delete_from_single_table(THD *thd) query_plan.using_filesort= FALSE; THD_STAGE_INFO(thd, stage_init_update); -<<<<<<< 21505331eaaf9b4e9ab6b393da821ecd87975be0 - // create_explain_query(thd->lex, thd->mem_root); -======= ->>>>>>> MDEV-7487 Semi-join optimization for single-table update/delete statements const bool delete_history= table_list->vers_conditions.delete_history; DBUG_ASSERT(!(delete_history && table_list->period_conditions.is_set())); diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 3838f27..b1a5875 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -385,10 +385,6 @@ bool Sql_cmd_update::update_single_table(THD *thd) DBUG_ENTER("Sql_cmd_update::update_single_table"); THD_STAGE_INFO(thd, stage_init_update); -<<<<<<< 21505331eaaf9b4e9ab6b393da821ecd87975be0 - // create_explain_query(thd->lex, thd->mem_root); -======= ->>>>>>> MDEV-7487 Semi-join optimization for single-table update/delete statements thd->table_map_for_update= 0;
participants (1)
-
IgorBabaev