[Commits] 2ae8dc2: Adjusted test results after rebase against 11.0.1

revision-id: 2ae8dc24351b830fd15970758b65f58caf504678 (mariadb-10.11.1-108-g2ae8dc2) parent(s): 12840543e531bf5809f662ec6e70c117d0c16de9 author: Igor Babaev committer: Igor Babaev timestamp: 2023-03-09 21:48:58 -0800 message: Adjusted test results after rebase against 11.0.1 --- mysql-test/main/delete.result | 6 +- mysql-test/main/delete_single_to_multi.result | 1491 +++++++++++-- mysql-test/main/delete_single_to_multi.test | 196 +- mysql-test/main/derived_cond_pushdown.result | 15 +- mysql-test/main/log_state.result | 2 +- mysql-test/main/multi_update.result | 1 + .../main/myisam_explain_non_select_all.result | 24 +- 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 | 2273 ++++++++++++++------ mysql-test/main/update_single_to_multi.test | 147 +- mysql-test/main/update_use_source.result | 2 +- sql/sql_select.cc | 3 +- 19 files changed, 3128 insertions(+), 1114 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 fe27f69..eba4ca8 100644 --- a/mysql-test/main/delete_single_to_multi.result +++ b/mysql-test/main/delete_single_to_multi.result @@ -2,10 +2,26 @@ DROP DATABASE IF EXISTS dbt3_s001; CREATE DATABASE dbt3_s001; use dbt3_s001; create index i_n_name on nation(n_name); -analyze table nation; +analyze table +nation, lineitem, customer, orders, part, supplier, partsupp, region +persistent for all; Table Op Msg_type Msg_text dbt3_s001.nation analyze status Engine-independent statistics collected dbt3_s001.nation analyze status OK +dbt3_s001.lineitem analyze status Engine-independent statistics collected +dbt3_s001.lineitem analyze status OK +dbt3_s001.customer analyze status Engine-independent statistics collected +dbt3_s001.customer analyze status OK +dbt3_s001.orders analyze status Engine-independent statistics collected +dbt3_s001.orders analyze status OK +dbt3_s001.part analyze status Engine-independent statistics collected +dbt3_s001.part analyze status OK +dbt3_s001.supplier analyze status Engine-independent statistics collected +dbt3_s001.supplier analyze status OK +dbt3_s001.partsupp analyze status Engine-independent statistics collected +dbt3_s001.partsupp analyze status OK +dbt3_s001.region analyze status Engine-independent statistics collected +dbt3_s001.region analyze status OK # Pullout # ======= explain @@ -15,8 +31,8 @@ where c_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 customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 -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 (7%) Using where; Using rowid filter +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (7%) Using where; Using rowid filter explain format=json select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer @@ -26,6 +42,7 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.052271677, "nested_loop": [ { "table": { @@ -36,7 +53,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 +69,9 @@ EXPLAIN "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], - "rows": 11, + "loops": 1, + "rows": 6, + "cost": 0.008193756, "filtered": 100 } }, @@ -71,7 +92,9 @@ EXPLAIN "rows": 108, "selectivity_pct": 7.2 }, - "rows": 11, + "loops": 6, + "rows": 15, + "cost": 0.04205351, "filtered": 7.199999809, "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" } @@ -104,8 +127,8 @@ where c_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 customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 -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 (7%) Using where; Using rowid filter +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (7%) Using where; Using rowid filter explain format=json delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer @@ -115,6 +138,7 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.052271677, "nested_loop": [ { "table": { @@ -125,7 +149,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 +165,9 @@ EXPLAIN "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], - "rows": 11, + "loops": 1, + "rows": 6, + "cost": 0.008193756, "filtered": 100 } }, @@ -160,7 +188,9 @@ EXPLAIN "rows": 108, "selectivity_pct": 7.2 }, - "rows": 11, + "loops": 6, + "rows": 15, + "cost": 0.04205351, "filtered": 7.199999809, "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" } @@ -200,9 +230,9 @@ 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 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 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 Using where; Using join buffer (flat, BNL join) +1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 910 and @@ -230,9 +260,9 @@ 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 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 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 Using where +1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 delete from partsupp where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 910 and @@ -267,9 +297,9 @@ 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 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 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 Using where; Using join buffer (flat, BNL join) +1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part where p_retailprice between 901 and 910) and ps_suppkey in (select s_suppkey from supplier @@ -297,9 +327,9 @@ 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 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 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 Using where +1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 delete from partsupp where ps_partkey in (select p_partkey from part where p_retailprice between 901 and 910) and ps_suppkey in (select s_suppkey from supplier @@ -344,11 +374,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 supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 -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 supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +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 15 (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 PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 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 +431,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 supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 -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 supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +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 15 (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 PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 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,9 +496,9 @@ 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|filter PRIMARY,i_n_regionkey PRIMARY|i_n_regionkey 4|5 dbt3_s001.customer.c_nationkey 1 (40%) Using where; Using rowid filter -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 +1 PRIMARY nation ALL PRIMARY,i_n_regionkey NULL NULL NULL 25 Using where +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (9%) Using where; FirstMatch(customer); Using rowid filter explain format=json select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) @@ -479,37 +509,33 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.40015207, "nested_loop": [ { "table": { - "table_name": "customer", + "table_name": "nation", "access_type": "ALL", - "possible_keys": ["PRIMARY", "i_c_nationkey"], - "rows": 150, - "filtered": 100, - "attached_condition": "customer.c_nationkey is not null" + "possible_keys": ["PRIMARY", "i_n_regionkey"], + "loops": 1, + "rows": 25, + "cost": 0.013945725, + "filtered": 40, + "attached_condition": "nation.n_regionkey in (1,2)" } }, { "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"], - "rowid_filter": { - "range": { - "key": "i_n_regionkey", - "used_key_parts": ["n_regionkey"] - }, - "rows": 10, - "selectivity_pct": 40 - }, - "rows": 1, - "filtered": 40, - "attached_condition": "nation.n_regionkey in (1,2)" + "table_name": "customer", + "access_type": "ref", + "possible_keys": ["PRIMARY", "i_c_nationkey"], + "key": "i_c_nationkey", + "key_length": "5", + "used_key_parts": ["c_nationkey"], + "ref": ["dbt3_s001.nation.n_nationkey"], + "loops": 10, + "rows": 6, + "cost": 0.08009436, + "filtered": 100 } }, { @@ -529,10 +555,12 @@ EXPLAIN "rows": 140, "selectivity_pct": 9.333333333 }, - "rows": 11, - "filtered": 9.333333015, + "loops": 60, + "rows": 15, + "cost": 0.306111985, + "filtered": 6.666666508, "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'", - "first_match": "nation" + "first_match": "customer" } } ] @@ -589,9 +617,9 @@ 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|filter PRIMARY,i_n_regionkey PRIMARY|i_n_regionkey 4|5 dbt3_s001.customer.c_nationkey 1 (40%) Using where; Using rowid filter -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 +1 PRIMARY nation ALL PRIMARY,i_n_regionkey NULL NULL NULL 25 Using where +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (9%) Using where; FirstMatch(customer); Using rowid filter explain format=json delete from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) @@ -602,37 +630,33 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.40015207, "nested_loop": [ { "table": { - "table_name": "customer", + "table_name": "nation", "access_type": "ALL", - "possible_keys": ["PRIMARY", "i_c_nationkey"], - "rows": 150, - "filtered": 100, - "attached_condition": "customer.c_nationkey is not null" + "possible_keys": ["PRIMARY", "i_n_regionkey"], + "loops": 1, + "rows": 25, + "cost": 0.013945725, + "filtered": 40, + "attached_condition": "nation.n_regionkey in (1,2)" } }, { "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"], - "rowid_filter": { - "range": { - "key": "i_n_regionkey", - "used_key_parts": ["n_regionkey"] - }, - "rows": 10, - "selectivity_pct": 40 - }, - "rows": 1, - "filtered": 40, - "attached_condition": "nation.n_regionkey in (1,2)" + "table_name": "customer", + "access_type": "ref", + "possible_keys": ["PRIMARY", "i_c_nationkey"], + "key": "i_c_nationkey", + "key_length": "5", + "used_key_parts": ["c_nationkey"], + "ref": ["dbt3_s001.nation.n_nationkey"], + "loops": 10, + "rows": 6, + "cost": 0.08009436, + "filtered": 100 } }, { @@ -652,10 +676,12 @@ EXPLAIN "rows": 140, "selectivity_pct": 9.333333333 }, - "rows": 11, - "filtered": 9.333333015, + "loops": 60, + "rows": 15, + "cost": 0.306111985, + "filtered": 6.666666508, "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'", - "first_match": "nation" + "first_match": "customer" } } ] @@ -720,8 +746,8 @@ c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1993-01-08"); 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 customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 -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 (14%) Using where; FirstMatch(customer); Using rowid filter +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (14%) Using where; FirstMatch(customer); Using rowid filter select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU') and c_custkey in (select o_custkey from orders @@ -745,8 +771,8 @@ c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1993-01-08"); 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 customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 -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 (14%) Using where; FirstMatch(customer); Using rowid filter +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (14%) Using where; FirstMatch(customer); Using rowid filter delete from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU') and c_custkey in (select o_custkey from orders @@ -771,27 +797,138 @@ Customer#000000133 2314.67 drop table t; # Materialization # =============== +set optimizer_switch='firstmatch=off'; +explain +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where +n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1995-01-08"); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY nation range PRIMARY,i_n_name i_n_name 26 NULL 4 Using index condition +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +3 MATERIALIZED orders ALL i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where +n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1995-01-08"); +c_name c_acctbal +Customer#000000008 6819.74 +Customer#000000014 5266.3 +Customer#000000025 7133.7 +Customer#000000035 1228.24 +Customer#000000038 6345.11 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000067 8166.59 +Customer#000000077 1738.87 +Customer#000000094 5500.11 +Customer#000000097 2164.48 +Customer#000000098 -551.37 +Customer#000000103 2757.45 +Customer#000000106 3288.42 +Customer#000000113 2912 +Customer#000000121 6428.32 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +Customer#000000142 2209.81 +create table t as +select * from customer where c_nationkey in (select n_nationkey from nation where +n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1995-01-08"); +explain +delete from customer where c_nationkey in (select n_nationkey from nation where +n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1995-01-08"); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY nation range PRIMARY,i_n_name i_n_name 26 NULL 4 Using index condition +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +3 MATERIALIZED orders ALL i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where +delete from customer where c_nationkey in (select n_nationkey from nation where +n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1995-01-08"); +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where +n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1995-01-08"); +c_name c_acctbal +insert into customer select * from t; +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where +n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1995-01-08"); +c_name c_acctbal +Customer#000000008 6819.74 +Customer#000000014 5266.3 +Customer#000000025 7133.7 +Customer#000000035 1228.24 +Customer#000000038 6345.11 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000067 8166.59 +Customer#000000077 1738.87 +Customer#000000094 5500.11 +Customer#000000097 2164.48 +Customer#000000098 -551.37 +Customer#000000103 2757.45 +Customer#000000106 3288.42 +Customer#000000113 2912 +Customer#000000121 6428.32 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +Customer#000000142 2209.81 +drop table t; +set optimizer_switch='firstmatch=default'; 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'); +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 <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 customer ALL PRIMARY NULL NULL NULL 150 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED orders ALL i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.382051418, "nested_loop": [ { "table": { - "table_name": "<subquery2>", + "table_name": "customer", "access_type": "ALL", + "possible_keys": ["PRIMARY"], + "loops": 1, + "rows": 150, + "cost": 0.03493875, + "filtered": 100 + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", "possible_keys": ["distinct_key"], - "rows": 28, + "key": "distinct_key", + "key_length": "4", + "used_key_parts": ["o_custkey"], + "ref": ["func"], + "rows": 1, "filtered": 100, "materialized": { "unique": 1, @@ -801,89 +938,155 @@ EXPLAIN { "table": { "table_name": "orders", - "access_type": "range", + "access_type": "ALL", "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" + "loops": 1, + "rows": 1500, + "cost": 0.2532975, + "filtered": 16.13333321, + "attached_condition": "orders.o_orderDATE between '1992-01-09' and '1993-03-08'" } } ] } } } - }, - { - "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 - } } ] } } 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal +Customer#000000001 711.56 +Customer#000000002 121.65 Customer#000000005 794.47 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 Customer#000000013 3857.34 +Customer#000000014 5266.3 Customer#000000016 4681.03 Customer#000000017 6.34 +Customer#000000019 8914.71 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000029 7618.27 +Customer#000000031 5236.89 Customer#000000032 3471.53 +Customer#000000034 8589.7 Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 Customer#000000040 1335.3 +Customer#000000041 270.95 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 Customer#000000052 5630.28 +Customer#000000053 4113.64 +Customer#000000055 4572.11 Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000062 595.61 +Customer#000000064 -646.64 Customer#000000065 8795.16 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000089 1530.76 Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000094 5500.11 +Customer#000000095 5327.38 +Customer#000000097 2164.48 Customer#000000098 -551.37 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000107 2514.15 Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000128 -986.96 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 Customer#000000140 9963.15 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000145 9748.93 +Customer#000000148 2135.6 +Customer#000000149 8959.65 create table t as select * from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); explain delete from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); +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 <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 customer ALL PRIMARY NULL NULL NULL 150 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED orders ALL i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.37364371, "nested_loop": [ { "table": { - "table_name": "<subquery2>", + "table_name": "customer", "access_type": "ALL", + "possible_keys": ["PRIMARY"], + "loops": 1, + "rows": 150, + "cost": 0.026531042, + "filtered": 100 + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", "possible_keys": ["distinct_key"], - "rows": 28, + "key": "distinct_key", + "key_length": "4", + "used_key_parts": ["o_custkey"], + "ref": ["func"], + "rows": 1, "filtered": 100, "materialized": { "unique": 1, @@ -893,71 +1096,121 @@ EXPLAIN { "table": { "table_name": "orders", - "access_type": "range", + "access_type": "ALL", "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" + "loops": 1, + "rows": 1500, + "cost": 0.2532975, + "filtered": 16.13333321, + "attached_condition": "orders.o_orderDATE between '1992-01-09' and '1993-03-08'" } } ] } } } - }, - { - "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 - } } ] } } delete from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal insert into customer select * from t; 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal +Customer#000000001 711.56 +Customer#000000002 121.65 Customer#000000005 794.47 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 Customer#000000013 3857.34 +Customer#000000014 5266.3 Customer#000000016 4681.03 Customer#000000017 6.34 +Customer#000000019 8914.71 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000029 7618.27 +Customer#000000031 5236.89 Customer#000000032 3471.53 +Customer#000000034 8589.7 Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 Customer#000000040 1335.3 +Customer#000000041 270.95 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 Customer#000000052 5630.28 +Customer#000000053 4113.64 +Customer#000000055 4572.11 Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000062 595.61 +Customer#000000064 -646.64 Customer#000000065 8795.16 -Customer#000000076 5745.33 -Customer#000000091 4643.14 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 +Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000089 1530.76 +Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000094 5500.11 +Customer#000000095 5327.38 +Customer#000000097 2164.48 Customer#000000098 -551.37 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000107 2514.15 Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000128 -986.96 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 Customer#000000140 9963.15 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000145 9748.93 +Customer#000000148 2135.6 +Customer#000000149 8959.65 drop table t; explain select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders @@ -1141,18 +1394,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": [ @@ -1164,7 +1421,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'" } @@ -1184,7 +1443,9 @@ EXPLAIN "key_length": "4", "used_key_parts": ["c_custkey"], "ref": ["<subquery2>.o_custkey"], + "loops": 28, "rows": 1, + "cost": 0.048617528, "filtered": 100 } } @@ -1220,18 +1481,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": [ @@ -1243,7 +1508,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'" } @@ -1263,7 +1530,9 @@ EXPLAIN "key_length": "4", "used_key_parts": ["c_custkey"], "ref": ["<subquery2>.o_custkey"], + "loops": 28, "rows": 1, + "cost": 0.017940756, "filtered": 100 } } @@ -1295,7 +1564,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); @@ -1320,7 +1589,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); @@ -1418,6 +1687,7 @@ drop table t,r; deallocate prepare stmt; # FirstMatch PS # ============= +set optimizer_switch='materialization=off'; prepare stmt from " delete from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) @@ -1569,155 +1839,468 @@ Customer#000000139 7897.78 Customer#000000142 2209.81 drop table t,r; deallocate prepare stmt; +set optimizer_switch='materialization=default'; # Materialization PS # ================== prepare stmt from " delete from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08') and c_name like ?; +where o_orderDATE between '1992-01-09' and '1993-03-08') and c_name like ?; "; 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal +Customer#000000001 711.56 +Customer#000000002 121.65 Customer#000000005 794.47 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 Customer#000000013 3857.34 +Customer#000000014 5266.3 Customer#000000016 4681.03 Customer#000000017 6.34 +Customer#000000019 8914.71 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000029 7618.27 +Customer#000000031 5236.89 Customer#000000032 3471.53 +Customer#000000034 8589.7 Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 Customer#000000040 1335.3 +Customer#000000041 270.95 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 Customer#000000052 5630.28 +Customer#000000053 4113.64 +Customer#000000055 4572.11 Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000062 595.61 +Customer#000000064 -646.64 Customer#000000065 8795.16 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000089 1530.76 Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000094 5500.11 +Customer#000000095 5327.38 +Customer#000000097 2164.48 Customer#000000098 -551.37 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000107 2514.15 Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000128 -986.96 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 Customer#000000140 9963.15 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000145 9748.93 +Customer#000000148 2135.6 +Customer#000000149 8959.65 set @a1='Customer#%1_'; create table t as select * from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08') and c_name like @a1; +where o_orderDATE between '1992-01-09' and '1993-03-08') and c_name like @a1; execute stmt using @a1; 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal +Customer#000000001 711.56 +Customer#000000002 121.65 Customer#000000005 794.47 +Customer#000000007 9561.95 +Customer#000000008 6819.74 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000029 7618.27 +Customer#000000031 5236.89 Customer#000000032 3471.53 +Customer#000000034 8589.7 Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 Customer#000000040 1335.3 +Customer#000000041 270.95 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 Customer#000000052 5630.28 +Customer#000000053 4113.64 +Customer#000000055 4572.11 Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000062 595.61 +Customer#000000064 -646.64 Customer#000000065 8795.16 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000089 1530.76 Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000094 5500.11 +Customer#000000095 5327.38 +Customer#000000097 2164.48 Customer#000000098 -551.37 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000107 2514.15 Customer#000000109 -716.1 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000128 -986.96 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 Customer#000000140 9963.15 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000145 9748.93 +Customer#000000148 2135.6 +Customer#000000149 8959.65 insert into customer select * from t; 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal +Customer#000000001 711.56 +Customer#000000002 121.65 Customer#000000005 794.47 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 Customer#000000013 3857.34 +Customer#000000014 5266.3 Customer#000000016 4681.03 Customer#000000017 6.34 +Customer#000000019 8914.71 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000029 7618.27 +Customer#000000031 5236.89 Customer#000000032 3471.53 +Customer#000000034 8589.7 Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 Customer#000000040 1335.3 +Customer#000000041 270.95 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 Customer#000000052 5630.28 +Customer#000000053 4113.64 +Customer#000000055 4572.11 Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000062 595.61 +Customer#000000064 -646.64 Customer#000000065 8795.16 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000089 1530.76 Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000094 5500.11 +Customer#000000095 5327.38 +Customer#000000097 2164.48 Customer#000000098 -551.37 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000107 2514.15 Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000128 -986.96 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 Customer#000000140 9963.15 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000145 9748.93 +Customer#000000148 2135.6 +Customer#000000149 8959.65 set @a2='Customer#%3_'; create table r as select * from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08') and c_name like @a2; +where o_orderDATE between '1992-01-09' and '1993-03-08') and c_name like @a2; execute stmt using @a2; 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal +Customer#000000001 711.56 +Customer#000000002 121.65 Customer#000000005 794.47 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 Customer#000000013 3857.34 +Customer#000000014 5266.3 Customer#000000016 4681.03 Customer#000000017 6.34 +Customer#000000019 8914.71 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000029 7618.27 Customer#000000040 1335.3 +Customer#000000041 270.95 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 Customer#000000052 5630.28 +Customer#000000053 4113.64 +Customer#000000055 4572.11 Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000062 595.61 +Customer#000000064 -646.64 Customer#000000065 8795.16 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000089 1530.76 Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000094 5500.11 +Customer#000000095 5327.38 +Customer#000000097 2164.48 Customer#000000098 -551.37 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000107 2514.15 Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000128 -986.96 Customer#000000140 9963.15 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000145 9748.93 +Customer#000000148 2135.6 +Customer#000000149 8959.65 insert into customer select * from r; 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal +Customer#000000001 711.56 +Customer#000000002 121.65 Customer#000000005 794.47 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 Customer#000000013 3857.34 +Customer#000000014 5266.3 Customer#000000016 4681.03 Customer#000000017 6.34 +Customer#000000019 8914.71 Customer#000000022 591.98 Customer#000000023 3332.02 Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000029 7618.27 +Customer#000000031 5236.89 Customer#000000032 3471.53 +Customer#000000034 8589.7 Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 Customer#000000040 1335.3 +Customer#000000041 270.95 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 Customer#000000052 5630.28 +Customer#000000053 4113.64 +Customer#000000055 4572.11 Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000062 595.61 +Customer#000000064 -646.64 Customer#000000065 8795.16 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000089 1530.76 Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000094 5500.11 +Customer#000000095 5327.38 +Customer#000000097 2164.48 Customer#000000098 -551.37 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000107 2514.15 Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000128 -986.96 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 Customer#000000140 9963.15 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000145 9748.93 +Customer#000000148 2135.6 +Customer#000000149 8959.65 drop table t,r; deallocate prepare stmt; # Materialization SJM PS # ====================== prepare stmt from " delete from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08') and c_acctbal between ? and ?; +where o_orderDATE between '1992-01-09' and '1992-03-08' + group by o_custkey having count(o_custkey) > 1) and c_acctbal between ? and ?; "; 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' @@ -1795,6 +2378,7 @@ o_orderkey o_totalprice 3142 16030.15 5095 184583.99 5121 150334.57 +5382 138423.03 644 201268.06 737 12984.85 create table t as @@ -1811,6 +2395,7 @@ o_orderkey o_totalprice 1729 12137.76 2880 145761.99 3142 16030.15 +5382 138423.03 644 201268.06 737 12984.85 insert into orders select * from t; @@ -1824,6 +2409,7 @@ o_orderkey o_totalprice 3142 16030.15 5095 184583.99 5121 150334.57 +5382 138423.03 644 201268.06 737 12984.85 create table r as @@ -1841,6 +2427,7 @@ o_orderkey o_totalprice 2880 145761.99 3142 16030.15 5121 150334.57 +5382 138423.03 737 12984.85 insert into orders select * from r; select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and @@ -1853,12 +2440,14 @@ o_orderkey o_totalprice 3142 16030.15 5095 184583.99 5121 150334.57 +5382 138423.03 644 201268.06 737 12984.85 drop table t,r; drop procedure p; # FirstMatch SP # ============= +set optimizer_switch='materialization=off'; create procedure p(a int) delete from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) @@ -1873,10 +2462,13 @@ where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal Customer#000000007 9561.95 Customer#000000008 6819.74 +Customer#000000017 6.34 Customer#000000019 8914.71 +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 @@ -1913,8 +2505,11 @@ 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#000000017 6.34 +Customer#000000022 591.98 Customer#000000028 1007.18 Customer#000000037 -917.75 +Customer#000000040 1335.3 Customer#000000047 274.58 Customer#000000059 3458.6 Customer#000000061 1536.24 @@ -1936,10 +2531,13 @@ where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal Customer#000000007 9561.95 Customer#000000008 6819.74 +Customer#000000017 6.34 Customer#000000019 8914.71 +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 @@ -1976,8 +2574,11 @@ 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#000000017 6.34 +Customer#000000022 591.98 Customer#000000028 1007.18 Customer#000000037 -917.75 +Customer#000000040 1335.3 Customer#000000047 274.58 Customer#000000061 1536.24 Customer#000000064 -646.64 @@ -1993,10 +2594,13 @@ where o_orderDATE between '1992-10-09' and '1993-06-08'); c_name c_acctbal Customer#000000007 9561.95 Customer#000000008 6819.74 +Customer#000000017 6.34 Customer#000000019 8914.71 +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 @@ -2022,81 +2626,304 @@ Customer#000000139 7897.78 Customer#000000142 2209.81 drop table t,r; drop procedure p; +set optimizer_switch='materialization=default'; # Materialization SP # ================== create procedure p() delete from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal +Customer#000000001 711.56 +Customer#000000002 121.65 +Customer#000000005 794.47 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 Customer#000000013 3857.34 +Customer#000000014 5266.3 Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000019 8914.71 +Customer#000000022 591.98 +Customer#000000023 3332.02 Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000029 7618.27 +Customer#000000031 5236.89 Customer#000000032 3471.53 +Customer#000000034 8589.7 +Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 +Customer#000000040 1335.3 +Customer#000000041 270.95 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 Customer#000000052 5630.28 +Customer#000000053 4113.64 +Customer#000000055 4572.11 Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000062 595.61 +Customer#000000064 -646.64 Customer#000000065 8795.16 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000089 1530.76 Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000094 5500.11 +Customer#000000095 5327.38 +Customer#000000097 2164.48 +Customer#000000098 -551.37 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000107 2514.15 +Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000128 -986.96 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 +Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 Customer#000000140 9963.15 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000145 9748.93 +Customer#000000148 2135.6 +Customer#000000149 8959.65 create table t as select * from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); call p(); 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal insert into customer select * from t; 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal +Customer#000000001 711.56 +Customer#000000002 121.65 +Customer#000000005 794.47 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 Customer#000000013 3857.34 +Customer#000000014 5266.3 Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000019 8914.71 +Customer#000000022 591.98 +Customer#000000023 3332.02 Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000029 7618.27 +Customer#000000031 5236.89 Customer#000000032 3471.53 +Customer#000000034 8589.7 +Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 +Customer#000000040 1335.3 +Customer#000000041 270.95 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 Customer#000000052 5630.28 +Customer#000000053 4113.64 +Customer#000000055 4572.11 Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000062 595.61 +Customer#000000064 -646.64 Customer#000000065 8795.16 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000089 1530.76 Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000094 5500.11 +Customer#000000095 5327.38 +Customer#000000097 2164.48 +Customer#000000098 -551.37 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000107 2514.15 +Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000128 -986.96 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 +Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 Customer#000000140 9963.15 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000145 9748.93 +Customer#000000148 2135.6 +Customer#000000149 8959.65 create table r as select * from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); call p(); 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal insert into customer select * from r; 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal +Customer#000000001 711.56 +Customer#000000002 121.65 +Customer#000000005 794.47 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 Customer#000000013 3857.34 +Customer#000000014 5266.3 Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000019 8914.71 +Customer#000000022 591.98 +Customer#000000023 3332.02 Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000029 7618.27 +Customer#000000031 5236.89 Customer#000000032 3471.53 +Customer#000000034 8589.7 +Customer#000000035 1228.24 Customer#000000037 -917.75 Customer#000000038 6345.11 +Customer#000000040 1335.3 +Customer#000000041 270.95 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 Customer#000000052 5630.28 +Customer#000000053 4113.64 +Customer#000000055 4572.11 Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000062 595.61 +Customer#000000064 -646.64 Customer#000000065 8795.16 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000089 1530.76 Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000094 5500.11 +Customer#000000095 5327.38 +Customer#000000097 2164.48 +Customer#000000098 -551.37 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000107 2514.15 +Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 Customer#000000115 7508.92 Customer#000000116 8403.99 Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000128 -986.96 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 +Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 Customer#000000140 9963.15 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000145 9748.93 +Customer#000000148 2135.6 +Customer#000000149 8959.65 drop table t,r; drop procedure p; # Materialization SJM SP @@ -2158,72 +2985,294 @@ drop procedure p; # ==================== # Check for DELETE ... RETURNING with SJ subquery in WHERE select c_name from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name +Customer#000000001 +Customer#000000002 +Customer#000000005 +Customer#000000007 +Customer#000000008 +Customer#000000010 +Customer#000000011 Customer#000000013 +Customer#000000014 Customer#000000016 +Customer#000000017 +Customer#000000019 +Customer#000000022 +Customer#000000023 Customer#000000025 +Customer#000000028 +Customer#000000029 +Customer#000000031 Customer#000000032 +Customer#000000034 +Customer#000000035 Customer#000000037 Customer#000000038 +Customer#000000040 +Customer#000000041 +Customer#000000043 +Customer#000000044 +Customer#000000046 +Customer#000000047 +Customer#000000049 Customer#000000052 +Customer#000000053 +Customer#000000055 Customer#000000056 +Customer#000000058 +Customer#000000059 +Customer#000000061 +Customer#000000062 +Customer#000000064 Customer#000000065 +Customer#000000067 +Customer#000000070 +Customer#000000071 +Customer#000000073 +Customer#000000074 Customer#000000076 +Customer#000000079 +Customer#000000080 +Customer#000000082 +Customer#000000083 +Customer#000000085 +Customer#000000086 +Customer#000000088 +Customer#000000089 Customer#000000091 +Customer#000000092 +Customer#000000094 +Customer#000000095 +Customer#000000097 +Customer#000000098 +Customer#000000100 +Customer#000000101 +Customer#000000103 +Customer#000000104 +Customer#000000106 +Customer#000000107 +Customer#000000109 +Customer#000000110 +Customer#000000112 Customer#000000115 Customer#000000116 Customer#000000118 +Customer#000000121 +Customer#000000122 +Customer#000000127 +Customer#000000128 +Customer#000000130 +Customer#000000131 +Customer#000000133 +Customer#000000134 +Customer#000000136 +Customer#000000137 +Customer#000000139 Customer#000000140 +Customer#000000142 +Customer#000000143 +Customer#000000145 +Customer#000000148 +Customer#000000149 create table t as select * from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); explain delete from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08') returning c_name; +where o_orderDATE between '1992-01-09' and '1993-03-08') returning c_name; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY customer ALL NULL NULL NULL NULL 141 Using where -2 DEPENDENT SUBQUERY orders index_subquery|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 func 175 (2%) Using where; Using rowid filter +1 PRIMARY customer ALL NULL NULL NULL NULL 150 Using where +2 DEPENDENT SUBQUERY orders index_subquery i_o_orderdate,i_o_custkey i_o_custkey 5 func 15 Using where delete from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08') returning c_name; +where o_orderDATE between '1992-01-09' and '1993-03-08') returning c_name; c_name +Customer#000000001 +Customer#000000002 +Customer#000000005 +Customer#000000007 +Customer#000000008 +Customer#000000010 +Customer#000000011 Customer#000000013 +Customer#000000014 Customer#000000016 +Customer#000000017 +Customer#000000019 +Customer#000000022 +Customer#000000023 Customer#000000025 +Customer#000000028 +Customer#000000029 +Customer#000000031 Customer#000000032 +Customer#000000034 +Customer#000000035 Customer#000000037 Customer#000000038 +Customer#000000040 +Customer#000000041 +Customer#000000043 +Customer#000000044 +Customer#000000046 +Customer#000000047 +Customer#000000049 Customer#000000052 +Customer#000000053 +Customer#000000055 Customer#000000056 +Customer#000000058 +Customer#000000059 +Customer#000000061 +Customer#000000062 +Customer#000000064 Customer#000000065 +Customer#000000067 +Customer#000000070 +Customer#000000071 +Customer#000000073 +Customer#000000074 Customer#000000076 +Customer#000000079 +Customer#000000080 +Customer#000000082 +Customer#000000083 +Customer#000000085 +Customer#000000086 +Customer#000000088 +Customer#000000089 Customer#000000091 +Customer#000000092 +Customer#000000094 +Customer#000000095 +Customer#000000097 +Customer#000000098 +Customer#000000100 +Customer#000000101 +Customer#000000103 +Customer#000000104 +Customer#000000106 +Customer#000000107 +Customer#000000109 +Customer#000000110 +Customer#000000112 Customer#000000115 Customer#000000116 Customer#000000118 +Customer#000000121 +Customer#000000122 +Customer#000000127 +Customer#000000128 +Customer#000000130 +Customer#000000131 +Customer#000000133 +Customer#000000134 +Customer#000000136 +Customer#000000137 +Customer#000000139 Customer#000000140 +Customer#000000142 +Customer#000000143 +Customer#000000145 +Customer#000000148 +Customer#000000149 select c_name from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name insert into customer select * from t; select c_name from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name +Customer#000000001 +Customer#000000002 +Customer#000000005 +Customer#000000007 +Customer#000000008 +Customer#000000010 +Customer#000000011 Customer#000000013 +Customer#000000014 Customer#000000016 +Customer#000000017 +Customer#000000019 +Customer#000000022 +Customer#000000023 Customer#000000025 +Customer#000000028 +Customer#000000029 +Customer#000000031 Customer#000000032 +Customer#000000034 +Customer#000000035 Customer#000000037 Customer#000000038 +Customer#000000040 +Customer#000000041 +Customer#000000043 +Customer#000000044 +Customer#000000046 +Customer#000000047 +Customer#000000049 Customer#000000052 +Customer#000000053 +Customer#000000055 Customer#000000056 +Customer#000000058 +Customer#000000059 +Customer#000000061 +Customer#000000062 +Customer#000000064 Customer#000000065 +Customer#000000067 +Customer#000000070 +Customer#000000071 +Customer#000000073 +Customer#000000074 Customer#000000076 +Customer#000000079 +Customer#000000080 +Customer#000000082 +Customer#000000083 +Customer#000000085 +Customer#000000086 +Customer#000000088 +Customer#000000089 Customer#000000091 +Customer#000000092 +Customer#000000094 +Customer#000000095 +Customer#000000097 +Customer#000000098 +Customer#000000100 +Customer#000000101 +Customer#000000103 +Customer#000000104 +Customer#000000106 +Customer#000000107 +Customer#000000109 +Customer#000000110 +Customer#000000112 Customer#000000115 Customer#000000116 Customer#000000118 +Customer#000000121 +Customer#000000122 +Customer#000000127 +Customer#000000128 +Customer#000000130 +Customer#000000131 +Customer#000000133 +Customer#000000134 +Customer#000000136 +Customer#000000137 +Customer#000000139 Customer#000000140 +Customer#000000142 +Customer#000000143 +Customer#000000145 +Customer#000000148 +Customer#000000149 drop table t; select c_name from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' @@ -2243,7 +3292,7 @@ delete from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1) returning c_name; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY customer ALL NULL NULL NULL NULL 141 Using where +1 PRIMARY customer ALL NULL NULL NULL NULL 150 Using where 2 DEPENDENT SUBQUERY orders range i_o_orderdate i_o_orderdate 4 NULL 28 Using index condition; Using temporary delete from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' @@ -2269,64 +3318,16 @@ Customer#000000037 Customer#000000056 Customer#000000118 drop table t; -# Check for DELETE ... RETURNING with SJ subquery in WHERE -select c_name from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); -c_name -Customer#000000013 -Customer#000000016 -Customer#000000025 -Customer#000000032 -Customer#000000037 -Customer#000000038 -Customer#000000052 -Customer#000000056 -Customer#000000065 -Customer#000000076 -Customer#000000091 -Customer#000000115 -Customer#000000116 -Customer#000000118 -Customer#000000140 -create table t as -select * from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); -explain -delete from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08') returning c_name; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY customer ALL NULL NULL NULL NULL 141 Using where -2 DEPENDENT SUBQUERY orders index_subquery|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 func 175 (2%) Using where; Using rowid filter -delete from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08') returning c_name; -c_name -Customer#000000013 -Customer#000000016 -Customer#000000025 -Customer#000000032 -Customer#000000037 -Customer#000000038 -Customer#000000052 -Customer#000000056 -Customer#000000065 -Customer#000000076 -Customer#000000091 -Customer#000000115 -Customer#000000116 -Customer#000000118 -Customer#000000140 -select c_name from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); -c_name -insert into customer select * from t; -drop table t; # Check for DELETE ... ORDER BY ...LIMIT with SJ subquery in WHERE select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); o_orderkey o_totalprice 1221 117397.16 +1344 43809.37 1856 189361.42 +1925 146382.71 +3139 40975.96 324 26868.85 4903 34363.63 5607 24660.06 @@ -2338,18 +3339,21 @@ where c_nationkey in (1,2)) order by o_totalprice limit 500; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY orders range i_o_orderdate i_o_orderdate 4 NULL 108 Using where; Using filesort -2 DEPENDENT SUBQUERY customer unique_subquery|filter PRIMARY,i_c_nationkey PRIMARY|i_c_nationkey 4|5 func 1 (10%) Using where; Using rowid filter +2 DEPENDENT SUBQUERY customer unique_subquery PRIMARY,i_c_nationkey PRIMARY 4 func 1 Using where create table t as select * from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and 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 +1344 43809.37 +1925 146382.71 +3139 40975.96 delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)) @@ -2364,7 +3368,10 @@ o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); o_orderkey o_totalprice 1221 117397.16 +1344 43809.37 1856 189361.42 +1925 146382.71 +3139 40975.96 324 26868.85 4903 34363.63 5607 24660.06 @@ -2375,19 +3382,22 @@ delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY customer range PRIMARY,i_c_nationkey i_c_nationkey 5 NULL 14 Using index condition -1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 12 (7%) Using where; Using rowid filter +1 PRIMARY customer range PRIMARY,i_c_nationkey i_c_nationkey 5 NULL 13 Using index condition +1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (7%) Using where; Using rowid filter create table t as select * from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and 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 +1344 43809.37 +1925 146382.71 +3139 40975.96 delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); @@ -2401,7 +3411,10 @@ o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); o_orderkey o_totalprice 1221 117397.16 +1344 43809.37 1856 189361.42 +1925 146382.71 +3139 40975.96 324 26868.85 4903 34363.63 5607 24660.06 diff --git a/mysql-test/main/delete_single_to_multi.test b/mysql-test/main/delete_single_to_multi.test index 2c49128..2829ae1 100644 --- a/mysql-test/main/delete_single_to_multi.test +++ b/mysql-test/main/delete_single_to_multi.test @@ -15,7 +15,9 @@ use dbt3_s001; --enable_query_log create index i_n_name on nation(n_name); -analyze table nation; +analyze table + nation, lineitem, customer, orders, part, supplier, partsupp, region +persistent for all; --echo # Pullout @@ -242,16 +244,18 @@ drop table t; --echo # Materialization --echo # =============== +set optimizer_switch='firstmatch=off'; + let $c7= + c_nationkey in (select n_nationkey from nation where + n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) + and c_custkey in (select o_custkey from orders - where o_orderDATE between '1992-01-09' and '1992-03-08'); + where o_orderDATE between "1992-01-09" and "1995-01-08"); eval explain select c_name, c_acctbal from customer where $c7; -eval -explain format=json -select c_name, c_acctbal from customer where $c7; --sorted_result eval select c_name, c_acctbal from customer where $c7; @@ -263,9 +267,6 @@ eval explain delete from customer where $c7; eval -explain format=json -delete from customer where $c7; -eval delete from customer where $c7; eval select c_name, c_acctbal from customer where $c7; @@ -276,14 +277,18 @@ eval select c_name, c_acctbal from customer where $c7; drop table t; +set optimizer_switch='firstmatch=default'; let $c8= c_custkey in (select o_custkey from orders - where o_orderDATE between '1992-06-09' and '1993-01-08'); + where o_orderDATE between '1992-01-09' and '1993-03-08'); eval explain select c_name, c_acctbal from customer where $c8; +eval +explain format=json +select c_name, c_acctbal from customer where $c8; --sorted_result eval select c_name, c_acctbal from customer where $c8; @@ -295,6 +300,9 @@ eval explain delete from customer where $c8; eval +explain format=json +delete from customer where $c8; +eval delete from customer where $c8; eval select c_name, c_acctbal from customer where $c8; @@ -306,20 +314,13 @@ select c_name, c_acctbal from customer where $c8; drop table t; ---echo # Materialization SJM ---echo # =================== - let $c9= c_custkey in (select o_custkey from orders - where o_orderDATE between '1992-01-09' and '1992-03-08' - group by o_custkey having count(o_custkey) > 1); + where o_orderDATE between '1992-06-09' and '1993-01-08'); eval explain select c_name, c_acctbal from customer where $c9; -eval -explain format=json -select c_name, c_acctbal from customer where $c9; --sorted_result eval select c_name, c_acctbal from customer where $c9; @@ -331,9 +332,6 @@ eval explain delete from customer where $c9; eval -explain format=json -delete from customer where $c9; -eval delete from customer where $c9; eval select c_name, c_acctbal from customer where $c9; @@ -345,14 +343,20 @@ select c_name, c_acctbal from customer where $c9; drop table t; +--echo # Materialization SJM +--echo # =================== + let $c10= 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); + where o_orderDATE between '1992-01-09' and '1992-03-08' + group by o_custkey having count(o_custkey) > 1); eval explain select c_name, c_acctbal from customer where $c10; +eval +explain format=json +select c_name, c_acctbal from customer where $c10; --sorted_result eval select c_name, c_acctbal from customer where $c10; @@ -364,6 +368,9 @@ eval explain delete from customer where $c10; eval +explain format=json +delete from customer where $c10; +eval delete from customer where $c10; eval select c_name, c_acctbal from customer where $c10; @@ -375,6 +382,36 @@ select c_name, c_acctbal from customer where $c10; drop table t; +let $c11= + 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); + +eval +explain +select c_name, c_acctbal from customer where $c11; +--sorted_result +eval +select c_name, c_acctbal from customer where $c11; +eval +create table t as +select * from customer where $c11; + +eval +explain +delete from customer where $c11; +eval +delete from customer where $c11; +eval +select c_name, c_acctbal from customer where $c11; + +insert into customer select * from t; +--sorted_result +eval +select c_name, c_acctbal from customer where $c11; +drop table t; + + --echo # Pullout PS --echo # ========== @@ -416,6 +453,8 @@ deallocate prepare stmt; --echo # FirstMatch PS --echo # ============= +set optimizer_switch='materialization=off'; + eval prepare stmt from " delete from customer where $c5; @@ -450,42 +489,43 @@ drop table t,r; deallocate prepare stmt; +set optimizer_switch='materialization=default'; --echo # Materialization PS --echo # ================== eval prepare stmt from " -delete from customer where $c7 and c_name like ?; +delete from customer where $c8 and c_name like ?; "; --sorted_result eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; set @a1='Customer#%1_'; eval create table t as -select * from customer where $c7 and c_name like @a1; +select * from customer where $c8 and c_name like @a1; execute stmt using @a1; --sorted_result eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; insert into customer select * from t; --sorted_result eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; set @a2='Customer#%3_'; eval create table r as -select * from customer where $c7 and c_name like @a2; +select * from customer where $c8 and c_name like @a2; execute stmt using @a2; --sorted_result eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; insert into customer select * from r; --sorted_result eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; drop table t,r; deallocate prepare stmt; @@ -496,38 +536,38 @@ deallocate prepare stmt; eval prepare stmt from " -delete from customer where $c7 and c_acctbal between ? and ?; +delete from customer where $c10 and c_acctbal between ? and ?; "; --sorted_result eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; set @a1=3500; set @a2=4000; eval create table t as -select * from customer where $c9 and c_acctbal between @a1 and @a2; +select * from customer where $c10 and c_acctbal between @a1 and @a2; execute stmt using @a1, @a2; --sorted_result eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; insert into customer select * from t; --sorted_result eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; set @a3=-1000; set @a4=3500; eval create table r as -select * from customer where $c9 and c_acctbal between @a3 and @a4; +select * from customer where $c10 and c_acctbal between @a3 and @a4; execute stmt using @a3, @a4; --sorted_result eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; insert into customer select * from r; --sorted_result eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; drop table t,r; deallocate prepare stmt; @@ -573,6 +613,8 @@ drop procedure p; --echo # FirstMatch SP --echo # ============= +set optimizer_switch='materialization=off'; + eval create procedure p(a int) delete from customer where $c5 and c_acctbal > a; @@ -606,39 +648,41 @@ drop table t,r; drop procedure p; +set optimizer_switch='materialization=default'; + --echo # Materialization SP --echo # ================== eval create procedure p() -delete from customer where $c7; +delete from customer where $c8; --sorted_result eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; eval create table t as -select * from customer where $c7; +select * from customer where $c8; call p(); --sorted_result eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; insert into customer select * from t; --sorted_result eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; eval create table r as -select * from customer where $c7; +select * from customer where $c8; call p(); --sorted_result eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; insert into customer select * from r; --sorted_result eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; drop table t,r; drop procedure p; @@ -649,33 +693,33 @@ drop procedure p; eval create procedure p() -delete from customer where $c9; +delete from customer where $c10; --sorted_result eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; eval create table t as -select * from customer where $c9; +select * from customer where $c10; call p(); --sorted_result eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; insert into customer select * from t; --sorted_result eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; eval create table r as -select * from customer where $c9; +select * from customer where $c10; call p(); --sorted_result eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; insert into customer select * from r; --sorted_result eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; drop table t,r; drop procedure p; @@ -687,64 +731,44 @@ drop procedure p; --sorted_result eval -select c_name from customer where $c7; +select c_name from customer where $c8; eval create table t as -select * from customer where $c7; +select * from customer where $c8; eval explain -delete from customer where $c7 returning c_name; +delete from customer where $c8 returning c_name; --sorted_result eval -delete from customer where $c7 returning c_name; +delete from customer where $c8 returning c_name; --sorted_result eval -select c_name from customer where $c7; +select c_name from customer where $c8; insert into customer select * from t; --sorted_result eval -select c_name from customer where $c7; +select c_name from customer where $c8; drop table t; --sorted_result eval -select c_name from customer where $c9; +select c_name from customer where $c10; eval create table t as -select * from customer where $c9; +select * from customer where $c10; eval explain -delete from customer where $c9 returning c_name; +delete from customer where $c10 returning c_name; --sorted_result eval -delete from customer where $c9 returning c_name; +delete from customer where $c10 returning c_name; --sorted_result eval -select c_name from customer where $c9; +select c_name from customer where $c10; insert into customer select * from t; --sorted_result eval -select c_name from customer where $c9; -drop table t; - ---echo # Check for DELETE ... RETURNING with SJ subquery in WHERE - ---sorted_result -eval -select c_name from customer where $c7; -eval -create table t as -select * from customer where $c7; -eval -explain -delete from customer where $c7 returning c_name; ---sorted_result -eval -delete from customer where $c7 returning c_name; ---sorted_result -eval -select c_name from customer where $c7; -insert into customer select * from t; +select c_name from customer where $c10; drop table t; --echo # Check for DELETE ... ORDER BY ...LIMIT with SJ subquery in WHERE diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 7523e28..fd3530b 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -13134,20 +13134,7 @@ EXPLAIN "materialized": { "query_block": { "select_id": 3, -<<<<<<< 2ad65c4dbcb291867725d50d1f53c8da8549afb3 "cost": "COST_REPLACED", - "nested_loop": [ - { - "table": { - "table_name": "t1", - "access_type": "ALL", - "loops": 1, - "rows": 2, - "cost": "COST_REPLACED", - "filtered": 100, - "attached_condition": "t1.f2 < 2" - } -======= "filesort": { "sort_key": "t1.f2", "temporary_table": { @@ -13156,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 e5ed031..2364ee7 100644 --- a/mysql-test/main/multi_update.result +++ b/mysql-test/main/multi_update.result @@ -1270,6 +1270,7 @@ DROP TABLES t1, t2; # End of 10.3 tests # # MDEV-30538: multi-table UPDATE/DELETE with possible exists-to-in +# create table t1 (c1 int, c2 int, c3 int, index idx(c2)); insert into t1 values (1,1,1),(3,2,2),(1,3,3), diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index 3edbf29..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,15 +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 +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 +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 @@ -933,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; @@ -2828,14 +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) +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) +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 cc28a0f..11969f7 100644 --- a/mysql-test/main/update_single_to_multi.result +++ b/mysql-test/main/update_single_to_multi.result @@ -2,10 +2,26 @@ DROP DATABASE IF EXISTS dbt3_s001; CREATE DATABASE dbt3_s001; use dbt3_s001; create index i_n_name on nation(n_name); -analyze table nation; +analyze table +nation, lineitem, customer, orders, part, supplier, partsupp, region +persistent for all; Table Op Msg_type Msg_text dbt3_s001.nation analyze status Engine-independent statistics collected dbt3_s001.nation analyze status OK +dbt3_s001.lineitem analyze status Engine-independent statistics collected +dbt3_s001.lineitem analyze status OK +dbt3_s001.customer analyze status Engine-independent statistics collected +dbt3_s001.customer analyze status OK +dbt3_s001.orders analyze status Engine-independent statistics collected +dbt3_s001.orders analyze status OK +dbt3_s001.part analyze status Engine-independent statistics collected +dbt3_s001.part analyze status OK +dbt3_s001.supplier analyze status Engine-independent statistics collected +dbt3_s001.supplier analyze status OK +dbt3_s001.partsupp analyze status Engine-independent statistics collected +dbt3_s001.partsupp analyze status OK +dbt3_s001.region analyze status Engine-independent statistics collected +dbt3_s001.region analyze status OK # Pullout # ======= explain @@ -15,8 +31,8 @@ where c_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 customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 -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 (7%) Using where; Using rowid filter +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (7%) Using where; Using rowid filter explain format=json select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer @@ -26,6 +42,7 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.052271677, "nested_loop": [ { "table": { @@ -36,7 +53,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 +69,9 @@ EXPLAIN "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], - "rows": 11, + "loops": 1, + "rows": 6, + "cost": 0.008193756, "filtered": 100 } }, @@ -71,7 +92,9 @@ EXPLAIN "rows": 108, "selectivity_pct": 7.2 }, - "rows": 11, + "loops": 6, + "rows": 15, + "cost": 0.04205351, "filtered": 7.199999809, "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" } @@ -99,8 +122,8 @@ where c_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 customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 -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 (7%) Using where; Using rowid filter +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (7%) Using where; Using rowid filter explain format=json update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer @@ -110,6 +133,7 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.052271677, "nested_loop": [ { "table": { @@ -120,7 +144,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 +160,9 @@ EXPLAIN "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], - "rows": 11, + "loops": 1, + "rows": 6, + "cost": 0.008193756, "filtered": 100 } }, @@ -155,7 +183,9 @@ EXPLAIN "rows": 108, "selectivity_pct": 7.2 }, - "rows": 11, + "loops": 6, + "rows": 15, + "cost": 0.04205351, "filtered": 7.199999809, "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" } @@ -205,22 +235,22 @@ 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 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 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 Using where; Using join buffer (flat, BNL join) +1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 910 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); ps_partkey ps_suppkey ps_supplycost -4 1 444.37 -6 1 642.13 -8 1 957.34 1 8 357.84 3 8 645.4 +4 1 444.37 5 8 50.52 +6 1 642.13 7 8 763.98 +8 1 957.34 explain update partsupp set ps_supplycost = ps_supplycost+2 where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier @@ -229,9 +259,9 @@ 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 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 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 Using where +1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 update partsupp set ps_supplycost = ps_supplycost+2 where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 910 and @@ -243,13 +273,13 @@ where p_retailprice between 901 and 910 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); ps_partkey ps_suppkey ps_supplycost -4 1 446.37 -6 1 644.13 -8 1 959.34 1 8 359.84 3 8 647.4 +4 1 446.37 5 8 52.52 +6 1 644.13 7 8 765.98 +8 1 959.34 update partsupp set ps_supplycost = ps_supplycost-2 where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 910 and @@ -261,13 +291,13 @@ where p_retailprice between 901 and 910 and s_nationkey in (select n_nationkey from nation where n_name='PERU')); ps_partkey ps_suppkey ps_supplycost -4 1 444.37 -6 1 642.13 -8 1 957.34 1 8 357.84 3 8 645.4 +4 1 444.37 5 8 50.52 +6 1 642.13 7 8 763.98 +8 1 957.34 explain select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part where p_retailprice between 901 and 910) and @@ -276,22 +306,22 @@ 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 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 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 Using where; Using join buffer (flat, BNL join) +1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part where p_retailprice between 901 and 910) and ps_suppkey in (select s_suppkey from supplier where s_nationkey in (select n_nationkey from nation where n_name='PERU')); ps_partkey ps_suppkey ps_supplycost -4 1 444.37 -6 1 642.13 -8 1 957.34 1 8 357.84 3 8 645.4 +4 1 444.37 5 8 50.52 +6 1 642.13 7 8 763.98 +8 1 957.34 explain update partsupp set ps_supplycost = ps_supplycost+10 where ps_partkey in (select p_partkey from part where p_retailprice between 901 and 910) and @@ -300,9 +330,9 @@ 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 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 +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 Using where +1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 update partsupp set ps_supplycost = ps_supplycost+10 where ps_partkey in (select p_partkey from part where p_retailprice between 901 and 910) and ps_suppkey in (select s_suppkey from supplier @@ -314,13 +344,13 @@ ps_suppkey in (select s_suppkey from supplier where s_nationkey in (select n_nationkey from nation where n_name='PERU')); ps_partkey ps_suppkey ps_supplycost -4 1 454.37 -6 1 652.13 -8 1 967.34 1 8 367.84 3 8 655.4 +4 1 454.37 5 8 60.52 +6 1 652.13 7 8 773.98 +8 1 967.34 update partsupp set ps_supplycost = ps_supplycost-10 where ps_partkey in (select p_partkey from part where p_retailprice between 901 and 910) and ps_suppkey in (select s_suppkey from supplier @@ -332,13 +362,13 @@ ps_suppkey in (select s_suppkey from supplier where s_nationkey in (select n_nationkey from nation where n_name='PERU')); ps_partkey ps_suppkey ps_supplycost -4 1 444.37 -6 1 642.13 -8 1 957.34 1 8 357.84 3 8 645.4 +4 1 444.37 5 8 50.52 +6 1 642.13 7 8 763.98 +8 1 957.34 explain select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders where o_custkey in @@ -357,11 +387,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 supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 -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 supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +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 15 (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 PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 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 @@ -399,11 +429,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 supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 -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 supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +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 15 (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 PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 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 @@ -480,9 +510,9 @@ 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|filter PRIMARY,i_n_regionkey PRIMARY|i_n_regionkey 4|5 dbt3_s001.customer.c_nationkey 1 (40%) Using where; Using rowid filter -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 +1 PRIMARY nation ALL PRIMARY,i_n_regionkey NULL NULL NULL 25 Using where +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (9%) Using where; FirstMatch(customer); Using rowid filter explain format=json select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) @@ -493,37 +523,33 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.40015207, "nested_loop": [ { "table": { - "table_name": "customer", + "table_name": "nation", "access_type": "ALL", - "possible_keys": ["PRIMARY", "i_c_nationkey"], - "rows": 150, - "filtered": 100, - "attached_condition": "customer.c_nationkey is not null" + "possible_keys": ["PRIMARY", "i_n_regionkey"], + "loops": 1, + "rows": 25, + "cost": 0.013945725, + "filtered": 40, + "attached_condition": "nation.n_regionkey in (1,2)" } }, { "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"], - "rowid_filter": { - "range": { - "key": "i_n_regionkey", - "used_key_parts": ["n_regionkey"] - }, - "rows": 10, - "selectivity_pct": 40 - }, - "rows": 1, - "filtered": 40, - "attached_condition": "nation.n_regionkey in (1,2)" + "table_name": "customer", + "access_type": "ref", + "possible_keys": ["PRIMARY", "i_c_nationkey"], + "key": "i_c_nationkey", + "key_length": "5", + "used_key_parts": ["c_nationkey"], + "ref": ["dbt3_s001.nation.n_nationkey"], + "loops": 10, + "rows": 6, + "cost": 0.08009436, + "filtered": 100 } }, { @@ -543,10 +569,12 @@ EXPLAIN "rows": 140, "selectivity_pct": 9.333333333 }, - "rows": 11, - "filtered": 9.333333015, + "loops": 60, + "rows": 15, + "cost": 0.306111985, + "filtered": 6.666666508, "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'", - "first_match": "nation" + "first_match": "customer" } } ] @@ -558,38 +586,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)) @@ -597,9 +625,9 @@ 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|filter PRIMARY,i_n_regionkey PRIMARY|i_n_regionkey 4|5 dbt3_s001.customer.c_nationkey 1 (40%) Using where; Using rowid filter -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 +1 PRIMARY nation ALL PRIMARY,i_n_regionkey NULL NULL NULL 25 Using where +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (9%) Using where; FirstMatch(customer); Using rowid filter explain format=json update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) @@ -610,37 +638,33 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.40015207, "nested_loop": [ { "table": { - "table_name": "customer", + "table_name": "nation", "access_type": "ALL", - "possible_keys": ["PRIMARY", "i_c_nationkey"], - "rows": 150, - "filtered": 100, - "attached_condition": "customer.c_nationkey is not null" + "possible_keys": ["PRIMARY", "i_n_regionkey"], + "loops": 1, + "rows": 25, + "cost": 0.013945725, + "filtered": 40, + "attached_condition": "nation.n_regionkey in (1,2)" } }, { "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"], - "rowid_filter": { - "range": { - "key": "i_n_regionkey", - "used_key_parts": ["n_regionkey"] - }, - "rows": 10, - "selectivity_pct": 40 - }, - "rows": 1, - "filtered": 40, - "attached_condition": "nation.n_regionkey in (1,2)" + "table_name": "customer", + "access_type": "ref", + "possible_keys": ["PRIMARY", "i_c_nationkey"], + "key": "i_c_nationkey", + "key_length": "5", + "used_key_parts": ["c_nationkey"], + "ref": ["dbt3_s001.nation.n_nationkey"], + "loops": 10, + "rows": 6, + "cost": 0.08009436, + "filtered": 100 } }, { @@ -660,10 +684,12 @@ EXPLAIN "rows": 140, "selectivity_pct": 9.333333333 }, - "rows": 11, - "filtered": 9.333333015, + "loops": 60, + "rows": 15, + "cost": 0.306111985, + "filtered": 6.666666508, "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'", - "first_match": "nation" + "first_match": "customer" } } ] @@ -680,38 +706,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 @@ -723,38 +749,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') @@ -763,8 +789,8 @@ c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1993-01-08"); 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 customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 -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 (14%) Using where; FirstMatch(customer); Using rowid filter +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (14%) Using where; FirstMatch(customer); Using rowid filter select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU') and c_custkey in (select o_custkey from orders @@ -783,8 +809,8 @@ c_custkey in (select o_custkey from orders where o_orderDATE between "1992-01-09" and "1993-01-08"); 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 customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 -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 (14%) Using where; FirstMatch(customer); Using rowid filter +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (14%) Using where; FirstMatch(customer); Using rowid filter update customer set c_acctbal = c_acctbal+20 where c_nationkey in (select n_nationkey from nation where n_name='PERU') and c_custkey in (select o_custkey from orders @@ -817,116 +843,155 @@ Customer#000000121 6428.32 Customer#000000133 2314.67 # Materialization # =============== +set optimizer_switch='firstmatch=off'; 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'); +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where +n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1995-01-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 -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'); -EXPLAIN -{ - "query_block": { - "select_id": 1, - "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" - } - } - ] - } - } - } - }, - { - "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 - } - } - ] - } -} -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'); +1 PRIMARY nation range PRIMARY,i_n_name i_n_name 26 NULL 4 Using index condition +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +3 MATERIALIZED orders ALL i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where +n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1995-01-08"); c_name c_acctbal +Customer#000000014 5266.3 +Customer#000000059 3458.6 +Customer#000000106 3288.42 +Customer#000000067 8166.59 +Customer#000000094 5500.11 +Customer#000000103 2757.45 +Customer#000000130 5073.58 +Customer#000000139 7897.78 +Customer#000000142 2209.81 Customer#000000025 7133.7 -Customer#000000013 3857.34 -Customer#000000065 8795.16 -Customer#000000032 3471.53 -Customer#000000023 3332.02 -Customer#000000035 1228.24 -Customer#000000091 4643.14 -Customer#000000016 4681.03 +Customer#000000038 6345.11 Customer#000000098 -551.37 -Customer#000000037 -917.75 -Customer#000000136 -842.39 -Customer#000000118 3582.37 -Customer#000000022 591.98 -Customer#000000005 794.47 -Customer#000000109 -716.1 +Customer#000000113 2912 +Customer#000000008 6819.74 +Customer#000000035 1228.24 +Customer#000000061 1536.24 +Customer#000000077 1738.87 +Customer#000000097 2164.48 +Customer#000000121 6428.32 +Customer#000000133 2314.67 +explain +update customer set c_acctbal = c_acctbal+20 where c_nationkey in (select n_nationkey from nation where +n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1995-01-08"); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY nation range PRIMARY,i_n_name i_n_name 26 NULL 4 Using index condition +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +3 MATERIALIZED orders ALL i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where +update customer set c_acctbal = c_acctbal+20 where c_nationkey in (select n_nationkey from nation where +n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1995-01-08"); +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where +n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1995-01-08"); +c_name c_acctbal +Customer#000000014 5286.3 +Customer#000000059 3478.6 +Customer#000000106 3308.42 +Customer#000000067 8186.59 +Customer#000000094 5520.11 +Customer#000000103 2777.45 +Customer#000000130 5093.58 +Customer#000000139 7917.78 +Customer#000000142 2229.81 +Customer#000000025 7153.7 +Customer#000000038 6365.11 +Customer#000000098 -531.37 +Customer#000000113 2932 +Customer#000000008 6839.74 +Customer#000000035 1248.24 +Customer#000000061 1556.24 +Customer#000000077 1758.87 +Customer#000000097 2184.48 +Customer#000000121 6448.32 +Customer#000000133 2334.67 +update customer set c_acctbal = c_acctbal-20 where c_nationkey in (select n_nationkey from nation where +n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1995-01-08"); +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where +n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1995-01-08"); +c_name c_acctbal +Customer#000000014 5266.3 +Customer#000000059 3458.6 +Customer#000000106 3288.42 +Customer#000000067 8166.59 +Customer#000000094 5500.11 +Customer#000000103 2757.45 +Customer#000000130 5073.58 +Customer#000000139 7897.78 +Customer#000000142 2209.81 +Customer#000000025 7133.7 Customer#000000038 6345.11 -Customer#000000076 5745.33 -Customer#000000056 6530.86 -Customer#000000040 1335.3 -Customer#000000116 8403.99 -Customer#000000115 7508.92 -Customer#000000140 9963.15 -Customer#000000017 6.34 -Customer#000000052 5630.28 +Customer#000000098 -551.37 +Customer#000000113 2912 +Customer#000000008 6819.74 +Customer#000000035 1228.24 +Customer#000000061 1536.24 +Customer#000000077 1738.87 +Customer#000000097 2164.48 +Customer#000000121 6428.32 +Customer#000000133 2314.67 +set optimizer_switch='firstmatch=default'; 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'); +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'); 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 customer ALL PRIMARY NULL NULL NULL 150 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED orders ALL i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where 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'); +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'); EXPLAIN { "query_block": { "select_id": 1, + "cost": 0.382051418, "nested_loop": [ { "table": { - "table_name": "<subquery2>", + "table_name": "customer", "access_type": "ALL", + "possible_keys": ["PRIMARY"], + "loops": 1, + "rows": 150, + "cost": 0.03493875, + "filtered": 100 + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", "possible_keys": ["distinct_key"], - "rows": 28, + "key": "distinct_key", + "key_length": "4", + "used_key_parts": ["o_custkey"], + "ref": ["func"], + "rows": 1, "filtered": 100, "materialized": { "unique": 1, @@ -936,112 +1001,35 @@ EXPLAIN { "table": { "table_name": "orders", - "access_type": "range", + "access_type": "ALL", "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" + "loops": 1, + "rows": 1500, + "cost": 0.2532975, + "filtered": 16.13333321, + "attached_condition": "orders.o_orderDATE between '1992-01-09' and '1993-03-08'" } } ] } } } - }, - { - "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 - } } ] } } -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'); -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'); -c_name c_acctbal -Customer#000000025 7138.7 -Customer#000000013 3862.34 -Customer#000000065 8800.16 -Customer#000000032 3476.53 -Customer#000000023 3337.02 -Customer#000000035 1233.24 -Customer#000000091 4648.14 -Customer#000000016 4686.03 -Customer#000000098 -546.37 -Customer#000000037 -912.75 -Customer#000000136 -837.39 -Customer#000000118 3587.37 -Customer#000000022 596.98 -Customer#000000005 799.47 -Customer#000000109 -711.1 -Customer#000000038 6350.11 -Customer#000000076 5750.33 -Customer#000000056 6535.86 -Customer#000000040 1340.3 -Customer#000000116 8408.99 -Customer#000000115 7513.92 -Customer#000000140 9968.15 -Customer#000000017 11.34 -Customer#000000052 5635.28 -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'); -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'); -c_name c_acctbal -Customer#000000025 7133.7 -Customer#000000013 3857.34 -Customer#000000065 8795.16 -Customer#000000032 3471.53 -Customer#000000023 3332.02 -Customer#000000035 1228.24 -Customer#000000091 4643.14 -Customer#000000016 4681.03 -Customer#000000098 -551.37 -Customer#000000037 -917.75 -Customer#000000136 -842.39 -Customer#000000118 3582.37 -Customer#000000022 591.98 -Customer#000000005 794.47 -Customer#000000109 -716.1 -Customer#000000038 6345.11 -Customer#000000076 5745.33 -Customer#000000056 6530.86 -Customer#000000040 1335.3 -Customer#000000116 8403.99 -Customer#000000115 7508.92 -Customer#000000140 9963.15 -Customer#000000017 6.34 -Customer#000000052 5630.28 -explain -select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-06-09' and '1993-01-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 func 1 -2 MATERIALIZED orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 114 Using index condition; Using where select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-06-09' and '1993-01-08'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal Customer#000000001 711.56 Customer#000000002 121.65 +Customer#000000005 794.47 Customer#000000007 9561.95 Customer#000000008 6819.74 Customer#000000010 2753.54 Customer#000000011 -272.6 +Customer#000000013 3857.34 +Customer#000000014 5266.3 Customer#000000016 4681.03 Customer#000000017 6.34 Customer#000000019 8914.71 @@ -1051,18 +1039,28 @@ Customer#000000025 7133.7 Customer#000000028 1007.18 Customer#000000029 7618.27 Customer#000000031 5236.89 +Customer#000000032 3471.53 Customer#000000034 8589.7 +Customer#000000035 1228.24 Customer#000000037 -917.75 +Customer#000000038 6345.11 Customer#000000040 1335.3 +Customer#000000041 270.95 Customer#000000043 9904.28 Customer#000000044 7315.94 Customer#000000046 5744.59 Customer#000000047 274.58 Customer#000000049 4573.94 +Customer#000000052 5630.28 Customer#000000053 4113.64 Customer#000000055 4572.11 +Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 Customer#000000061 1536.24 +Customer#000000062 595.61 Customer#000000064 -646.64 +Customer#000000065 8795.16 Customer#000000067 8166.59 Customer#000000070 4867.52 Customer#000000071 -611.19 @@ -1076,22 +1074,29 @@ Customer#000000083 6463.51 Customer#000000085 3386.64 Customer#000000086 3306.32 Customer#000000088 8031.44 +Customer#000000089 1530.76 Customer#000000091 4643.14 Customer#000000092 1182.91 +Customer#000000094 5500.11 Customer#000000095 5327.38 Customer#000000097 2164.48 +Customer#000000098 -551.37 Customer#000000100 9889.89 Customer#000000101 7470.96 Customer#000000103 2757.45 Customer#000000104 -588.38 Customer#000000106 3288.42 +Customer#000000107 2514.15 Customer#000000109 -716.1 Customer#000000110 7462.99 Customer#000000112 2953.35 +Customer#000000115 7508.92 +Customer#000000116 8403.99 Customer#000000118 3582.37 Customer#000000121 6428.32 Customer#000000122 7865.46 Customer#000000127 9280.71 +Customer#000000128 -986.96 Customer#000000130 5073.58 Customer#000000131 8595.53 Customer#000000133 2314.67 @@ -1099,100 +1104,183 @@ Customer#000000134 4608.9 Customer#000000136 -842.39 Customer#000000137 7838.3 Customer#000000139 7897.78 +Customer#000000140 9963.15 Customer#000000142 2209.81 Customer#000000143 2186.5 +Customer#000000145 9748.93 Customer#000000148 2135.6 Customer#000000149 8959.65 explain -update customer set c_acctbal = c_acctbal+1 where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-06-09' and '1993-01-08'); +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 '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 func 1 -2 MATERIALIZED orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 114 Using index condition; Using where -update customer set c_acctbal = c_acctbal+1 where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-06-09' and '1993-01-08'); -select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-06-09' and '1993-01-08'); -c_name c_acctbal -Customer#000000001 712.56 -Customer#000000002 122.65 -Customer#000000007 9562.95 -Customer#000000008 6820.74 -Customer#000000010 2754.54 -Customer#000000011 -271.6 -Customer#000000016 4682.03 -Customer#000000017 7.34 -Customer#000000019 8915.71 -Customer#000000022 592.98 -Customer#000000023 3333.02 -Customer#000000025 7134.7 -Customer#000000028 1008.18 -Customer#000000029 7619.27 -Customer#000000031 5237.89 -Customer#000000034 8590.7 -Customer#000000037 -916.75 -Customer#000000040 1336.3 -Customer#000000043 9905.28 -Customer#000000044 7316.94 -Customer#000000046 5745.59 -Customer#000000047 275.58 -Customer#000000049 4574.94 -Customer#000000053 4114.64 -Customer#000000055 4573.11 -Customer#000000061 1537.24 -Customer#000000064 -645.64 -Customer#000000067 8167.59 -Customer#000000070 4868.52 -Customer#000000071 -610.19 -Customer#000000073 4289.5 -Customer#000000074 2765.43 -Customer#000000076 5746.33 -Customer#000000079 5122.28 -Customer#000000080 7384.53 -Customer#000000082 9469.34 -Customer#000000083 6464.51 -Customer#000000085 3387.64 -Customer#000000086 3307.32 -Customer#000000088 8032.44 -Customer#000000091 4644.14 -Customer#000000092 1183.91 -Customer#000000095 5328.38 -Customer#000000097 2165.48 -Customer#000000100 9890.89 -Customer#000000101 7471.96 -Customer#000000103 2758.45 -Customer#000000104 -587.38 -Customer#000000106 3289.42 -Customer#000000109 -715.1 -Customer#000000110 7463.99 -Customer#000000112 2954.35 -Customer#000000118 3583.37 -Customer#000000121 6429.32 -Customer#000000122 7866.46 -Customer#000000127 9281.71 -Customer#000000130 5074.58 -Customer#000000131 8596.53 -Customer#000000133 2315.67 -Customer#000000134 4609.9 -Customer#000000136 -841.39 -Customer#000000137 7839.3 -Customer#000000139 7898.78 -Customer#000000142 2210.81 -Customer#000000143 2187.5 -Customer#000000148 2136.6 -Customer#000000149 8960.65 -update customer set c_acctbal = c_acctbal-1 where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-06-09' and '1993-01-08'); +2 MATERIALIZED orders ALL i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where +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 '1993-03-08'); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "cost": 0.382051418, + "nested_loop": [ + { + "table": { + "table_name": "customer", + "access_type": "ALL", + "possible_keys": ["PRIMARY"], + "loops": 1, + "rows": 150, + "cost": 0.03493875, + "filtered": 100 + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "4", + "used_key_parts": ["o_custkey"], + "ref": ["func"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "nested_loop": [ + { + "table": { + "table_name": "orders", + "access_type": "ALL", + "possible_keys": ["i_o_orderdate", "i_o_custkey"], + "loops": 1, + "rows": 1500, + "cost": 0.2532975, + "filtered": 16.13333321, + "attached_condition": "orders.o_orderDATE between '1992-01-09' and '1993-03-08'" + } + } + ] + } + } + } + } + ] + } +} +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 '1993-03-08'); select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-06-09' and '1993-01-08'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000001 716.56 +Customer#000000002 126.65 +Customer#000000005 799.47 +Customer#000000007 9566.95 +Customer#000000008 6824.74 +Customer#000000010 2758.54 +Customer#000000011 -267.6 +Customer#000000013 3862.34 +Customer#000000014 5271.3 +Customer#000000016 4686.03 +Customer#000000017 11.34 +Customer#000000019 8919.71 +Customer#000000022 596.98 +Customer#000000023 3337.02 +Customer#000000025 7138.7 +Customer#000000028 1012.18 +Customer#000000029 7623.27 +Customer#000000031 5241.89 +Customer#000000032 3476.53 +Customer#000000034 8594.7 +Customer#000000035 1233.24 +Customer#000000037 -912.75 +Customer#000000038 6350.11 +Customer#000000040 1340.3 +Customer#000000041 275.95 +Customer#000000043 9909.28 +Customer#000000044 7320.94 +Customer#000000046 5749.59 +Customer#000000047 279.58 +Customer#000000049 4578.94 +Customer#000000052 5635.28 +Customer#000000053 4118.64 +Customer#000000055 4577.11 +Customer#000000056 6535.86 +Customer#000000058 6483.46 +Customer#000000059 3463.6 +Customer#000000061 1541.24 +Customer#000000062 600.61 +Customer#000000064 -641.64 +Customer#000000065 8800.16 +Customer#000000067 8171.59 +Customer#000000070 4872.52 +Customer#000000071 -606.19 +Customer#000000073 4293.5 +Customer#000000074 2769.43 +Customer#000000076 5750.33 +Customer#000000079 5126.28 +Customer#000000080 7388.53 +Customer#000000082 9473.34 +Customer#000000083 6468.51 +Customer#000000085 3391.64 +Customer#000000086 3311.32 +Customer#000000088 8036.44 +Customer#000000089 1535.76 +Customer#000000091 4648.14 +Customer#000000092 1187.91 +Customer#000000094 5505.11 +Customer#000000095 5332.38 +Customer#000000097 2169.48 +Customer#000000098 -546.37 +Customer#000000100 9894.89 +Customer#000000101 7475.96 +Customer#000000103 2762.45 +Customer#000000104 -583.38 +Customer#000000106 3293.42 +Customer#000000107 2519.15 +Customer#000000109 -711.1 +Customer#000000110 7467.99 +Customer#000000112 2958.35 +Customer#000000115 7513.92 +Customer#000000116 8408.99 +Customer#000000118 3587.37 +Customer#000000121 6433.32 +Customer#000000122 7870.46 +Customer#000000127 9285.71 +Customer#000000128 -981.96 +Customer#000000130 5078.58 +Customer#000000131 8600.53 +Customer#000000133 2319.67 +Customer#000000134 4613.9 +Customer#000000136 -837.39 +Customer#000000137 7843.3 +Customer#000000139 7902.78 +Customer#000000140 9968.15 +Customer#000000142 2214.81 +Customer#000000143 2191.5 +Customer#000000145 9753.93 +Customer#000000148 2140.6 +Customer#000000149 8964.65 +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 '1993-03-08'); +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'); c_name c_acctbal Customer#000000001 711.56 Customer#000000002 121.65 +Customer#000000005 794.47 Customer#000000007 9561.95 Customer#000000008 6819.74 Customer#000000010 2753.54 Customer#000000011 -272.6 +Customer#000000013 3857.34 +Customer#000000014 5266.3 Customer#000000016 4681.03 Customer#000000017 6.34 Customer#000000019 8914.71 @@ -1202,18 +1290,28 @@ Customer#000000025 7133.7 Customer#000000028 1007.18 Customer#000000029 7618.27 Customer#000000031 5236.89 +Customer#000000032 3471.53 Customer#000000034 8589.7 +Customer#000000035 1228.24 Customer#000000037 -917.75 +Customer#000000038 6345.11 Customer#000000040 1335.3 +Customer#000000041 270.95 Customer#000000043 9904.28 Customer#000000044 7315.94 Customer#000000046 5744.59 Customer#000000047 274.58 Customer#000000049 4573.94 +Customer#000000052 5630.28 Customer#000000053 4113.64 Customer#000000055 4572.11 +Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 Customer#000000061 1536.24 +Customer#000000062 595.61 Customer#000000064 -646.64 +Customer#000000065 8795.16 Customer#000000067 8166.59 Customer#000000070 4867.52 Customer#000000071 -611.19 @@ -1227,22 +1325,29 @@ Customer#000000083 6463.51 Customer#000000085 3386.64 Customer#000000086 3306.32 Customer#000000088 8031.44 +Customer#000000089 1530.76 Customer#000000091 4643.14 Customer#000000092 1182.91 +Customer#000000094 5500.11 Customer#000000095 5327.38 Customer#000000097 2164.48 +Customer#000000098 -551.37 Customer#000000100 9889.89 Customer#000000101 7470.96 Customer#000000103 2757.45 Customer#000000104 -588.38 Customer#000000106 3288.42 +Customer#000000107 2514.15 Customer#000000109 -716.1 Customer#000000110 7462.99 Customer#000000112 2953.35 +Customer#000000115 7508.92 +Customer#000000116 8403.99 Customer#000000118 3582.37 Customer#000000121 6428.32 Customer#000000122 7865.46 Customer#000000127 9280.71 +Customer#000000128 -986.96 Customer#000000130 5073.58 Customer#000000131 8595.53 Customer#000000133 2314.67 @@ -1250,115 +1355,274 @@ Customer#000000134 4608.9 Customer#000000136 -842.39 Customer#000000137 7838.3 Customer#000000139 7897.78 +Customer#000000140 9963.15 Customer#000000142 2209.81 Customer#000000143 2186.5 +Customer#000000145 9748.93 Customer#000000148 2135.6 Customer#000000149 8959.65 -# Materialization SJM -# =================== 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' - group by o_custkey having count(o_custkey) > 1); +where o_orderDATE between '1992-06-09' and '1993-01-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 <subquery2>.o_custkey 1 -2 MATERIALIZED orders range i_o_orderdate i_o_orderdate 4 NULL 28 Using index condition; Using 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' - group by o_custkey having count(o_custkey) > 1); -EXPLAIN -{ - "query_block": { - "select_id": 1, - "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, - "having_condition": "count(orders.o_custkey) > 1", - "temporary_table": { - "nested_loop": [ - { - "table": { - "table_name": "orders", - "access_type": "range", - "possible_keys": ["i_o_orderdate"], - "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'" - } - } - ] - } - } - } - } - }, - { - "table": { - "table_name": "customer", - "access_type": "eq_ref", - "possible_keys": ["PRIMARY"], - "key": "PRIMARY", - "key_length": "4", - "used_key_parts": ["c_custkey"], - "ref": ["<subquery2>.o_custkey"], - "rows": 1, - "filtered": 100 - } - } - ] - } -} +1 PRIMARY customer ALL PRIMARY NULL NULL NULL 150 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 114 Using index condition; Using where 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' - group by o_custkey having count(o_custkey) > 1); +where o_orderDATE between '1992-06-09' and '1993-01-08'); c_name c_acctbal -Customer#000000013 3857.34 -Customer#000000032 3471.53 +Customer#000000001 711.56 +Customer#000000002 121.65 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 +Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000019 8914.71 +Customer#000000022 591.98 +Customer#000000023 3332.02 +Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000029 7618.27 +Customer#000000031 5236.89 +Customer#000000034 8589.7 Customer#000000037 -917.75 -Customer#000000118 3582.37 -Customer#000000056 6530.86 -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' - group by o_custkey having count(o_custkey) > 1); -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 <subquery2>.o_custkey 1 -2 MATERIALIZED orders range i_o_orderdate i_o_orderdate 4 NULL 28 Using index condition; Using 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' - group by o_custkey having count(o_custkey) > 1); -EXPLAIN -{ - "query_block": { - "select_id": 1, - "nested_loop": [ - { - "table": { +Customer#000000040 1335.3 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 +Customer#000000053 4113.64 +Customer#000000055 4572.11 +Customer#000000061 1536.24 +Customer#000000064 -646.64 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 +Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000095 5327.38 +Customer#000000097 2164.48 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 +Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 +Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000148 2135.6 +Customer#000000149 8959.65 +explain +update customer set c_acctbal = c_acctbal+1 where c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-06-09' and '1993-01-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 func 1 +2 MATERIALIZED orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 114 Using index condition; Using where +update customer set c_acctbal = c_acctbal+1 where c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-06-09' and '1993-01-08'); +select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-06-09' and '1993-01-08'); +c_name c_acctbal +Customer#000000001 712.56 +Customer#000000002 122.65 +Customer#000000007 9562.95 +Customer#000000008 6820.74 +Customer#000000010 2754.54 +Customer#000000011 -271.6 +Customer#000000016 4682.03 +Customer#000000017 7.34 +Customer#000000019 8915.71 +Customer#000000022 592.98 +Customer#000000023 3333.02 +Customer#000000025 7134.7 +Customer#000000028 1008.18 +Customer#000000029 7619.27 +Customer#000000031 5237.89 +Customer#000000034 8590.7 +Customer#000000037 -916.75 +Customer#000000040 1336.3 +Customer#000000043 9905.28 +Customer#000000044 7316.94 +Customer#000000046 5745.59 +Customer#000000047 275.58 +Customer#000000049 4574.94 +Customer#000000053 4114.64 +Customer#000000055 4573.11 +Customer#000000061 1537.24 +Customer#000000064 -645.64 +Customer#000000067 8167.59 +Customer#000000070 4868.52 +Customer#000000071 -610.19 +Customer#000000073 4289.5 +Customer#000000074 2765.43 +Customer#000000076 5746.33 +Customer#000000079 5122.28 +Customer#000000080 7384.53 +Customer#000000082 9469.34 +Customer#000000083 6464.51 +Customer#000000085 3387.64 +Customer#000000086 3307.32 +Customer#000000088 8032.44 +Customer#000000091 4644.14 +Customer#000000092 1183.91 +Customer#000000095 5328.38 +Customer#000000097 2165.48 +Customer#000000100 9890.89 +Customer#000000101 7471.96 +Customer#000000103 2758.45 +Customer#000000104 -587.38 +Customer#000000106 3289.42 +Customer#000000109 -715.1 +Customer#000000110 7463.99 +Customer#000000112 2954.35 +Customer#000000118 3583.37 +Customer#000000121 6429.32 +Customer#000000122 7866.46 +Customer#000000127 9281.71 +Customer#000000130 5074.58 +Customer#000000131 8596.53 +Customer#000000133 2315.67 +Customer#000000134 4609.9 +Customer#000000136 -841.39 +Customer#000000137 7839.3 +Customer#000000139 7898.78 +Customer#000000142 2210.81 +Customer#000000143 2187.5 +Customer#000000148 2136.6 +Customer#000000149 8960.65 +update customer set c_acctbal = c_acctbal-1 where c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-06-09' and '1993-01-08'); +select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-06-09' and '1993-01-08'); +c_name c_acctbal +Customer#000000001 711.56 +Customer#000000002 121.65 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 +Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000019 8914.71 +Customer#000000022 591.98 +Customer#000000023 3332.02 +Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000029 7618.27 +Customer#000000031 5236.89 +Customer#000000034 8589.7 +Customer#000000037 -917.75 +Customer#000000040 1335.3 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 +Customer#000000053 4113.64 +Customer#000000055 4572.11 +Customer#000000061 1536.24 +Customer#000000064 -646.64 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 +Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000095 5327.38 +Customer#000000097 2164.48 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 +Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 +Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000148 2135.6 +Customer#000000149 8959.65 +# Materialization SJM +# =================== +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' + group by o_custkey having count(o_custkey) > 1); +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 <subquery2>.o_custkey 1 +2 MATERIALIZED orders range i_o_orderdate i_o_orderdate 4 NULL 28 Using index condition; Using 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' + group by o_custkey having count(o_custkey) > 1); +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": [ @@ -1370,7 +1634,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'" } @@ -1390,28 +1656,15 @@ EXPLAIN "key_length": "4", "used_key_parts": ["c_custkey"], "ref": ["<subquery2>.o_custkey"], + "loops": 28, "rows": 1, + "cost": 0.048617528, "filtered": 100 } } ] } } -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' - group by o_custkey having count(o_custkey) > 1); -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' - group by o_custkey having count(o_custkey) > 1); -c_name c_acctbal -Customer#000000013 3852.34 -Customer#000000032 3466.53 -Customer#000000037 -922.75 -Customer#000000118 3577.37 -Customer#000000056 6525.86 -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' - group by o_custkey having count(o_custkey) > 1); 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' group by o_custkey having count(o_custkey) > 1); @@ -1422,24 +1675,122 @@ Customer#000000037 -917.75 Customer#000000118 3582.37 Customer#000000056 6530.86 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 '1993-03-08' - group by o_custkey having count(o_custkey) > 5); +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' + group by o_custkey having count(o_custkey) > 1); 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 -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); -c_name c_acctbal -Customer#000000007 9561.95 -Customer#000000016 4681.03 -Customer#000000037 -917.75 -Customer#000000046 5744.59 -Customer#000000091 4643.14 -Customer#000000103 2757.45 -Customer#000000118 3582.37 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 28 +1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 <subquery2>.o_custkey 1 +2 MATERIALIZED orders range i_o_orderdate i_o_orderdate 4 NULL 28 Using index condition; Using 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' + group by o_custkey having count(o_custkey) > 1); +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": [ + { + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["i_o_orderdate"], + "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'" + } + } + ] + } + } + } + } + }, + { + "table": { + "table_name": "customer", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["c_custkey"], + "ref": ["<subquery2>.o_custkey"], + "loops": 28, + "rows": 1, + "cost": 0.048617528, + "filtered": 100 + } + } + ] + } +} +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' + group by o_custkey having count(o_custkey) > 1); +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' + group by o_custkey having count(o_custkey) > 1); +c_name c_acctbal +Customer#000000013 3852.34 +Customer#000000032 3466.53 +Customer#000000037 -922.75 +Customer#000000118 3577.37 +Customer#000000056 6525.86 +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' + group by o_custkey having count(o_custkey) > 1); +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' + group by o_custkey having count(o_custkey) > 1); +c_name c_acctbal +Customer#000000013 3857.34 +Customer#000000032 3471.53 +Customer#000000037 -917.75 +Customer#000000118 3582.37 +Customer#000000056 6530.86 +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 '1993-03-08' + group by o_custkey having count(o_custkey) > 5); +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 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); +c_name c_acctbal +Customer#000000007 9561.95 +Customer#000000016 4681.03 +Customer#000000037 -917.75 +Customer#000000046 5744.59 +Customer#000000091 4643.14 +Customer#000000103 2757.45 +Customer#000000118 3582.37 Customer#000000133 2314.67 Customer#000000134 4608.9 explain @@ -1449,7 +1800,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); @@ -1550,6 +1901,7 @@ o_orderkey o_totalprice deallocate prepare stmt; # FirstMatch PS # ============= +set optimizer_switch='materialization=off'; prepare stmt from " update customer set c_acctbal = c_acctbal+? where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) @@ -1715,125 +2067,386 @@ Customer#000000082 9468.34 Customer#000000124 1842.49 Customer#000000127 9280.71 deallocate prepare stmt; +set optimizer_switch='materialization=default'; # Materialization PS # ================== prepare stmt from " update customer set c_acctbal = c_acctbal+? where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); "; 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal -Customer#000000025 7133.7 +Customer#000000001 711.56 +Customer#000000002 121.65 +Customer#000000005 794.47 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 Customer#000000013 3857.34 -Customer#000000065 8795.16 -Customer#000000032 3471.53 +Customer#000000014 5266.3 +Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000019 8914.71 +Customer#000000022 591.98 Customer#000000023 3332.02 +Customer#000000025 7133.7 +Customer#000000028 1007.1799999999998 +Customer#000000029 7618.27 +Customer#000000031 5236.89 +Customer#000000032 3471.53 +Customer#000000034 8589.7 Customer#000000035 1228.24 -Customer#000000091 4643.14 -Customer#000000016 4681.03 -Customer#000000098 -551.37 Customer#000000037 -917.75 -Customer#000000136 -842.39 -Customer#000000118 3582.37 -Customer#000000022 591.98 -Customer#000000005 794.47 -Customer#000000109 -716.1 Customer#000000038 6345.11 -Customer#000000076 5745.33 -Customer#000000056 6530.86 Customer#000000040 1335.3 -Customer#000000116 8403.99 +Customer#000000041 270.95 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 +Customer#000000052 5630.28 +Customer#000000053 4113.64 +Customer#000000055 4572.11 +Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000062 595.61 +Customer#000000064 -646.64 +Customer#000000065 8795.16 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 +Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000089 1530.76 +Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000094 5500.11 +Customer#000000095 5327.38 +Customer#000000097 2164.48 +Customer#000000098 -551.37 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000107 2514.15 +Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 Customer#000000115 7508.92 +Customer#000000116 8403.99 +Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000128 -986.96 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 +Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 Customer#000000140 9963.15 -Customer#000000017 6.34 -Customer#000000052 5630.28 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000145 9748.93 +Customer#000000148 2135.6 +Customer#000000149 8959.65 set @a1=7; execute stmt using @a1; 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal -Customer#000000025 7140.7 +Customer#000000001 718.56 +Customer#000000002 128.65 +Customer#000000005 801.47 +Customer#000000007 9568.95 +Customer#000000008 6826.74 +Customer#000000010 2760.54 +Customer#000000011 -265.6 Customer#000000013 3864.34 -Customer#000000065 8802.16 -Customer#000000032 3478.53 +Customer#000000014 5273.3 +Customer#000000016 4688.03 +Customer#000000017 13.34 +Customer#000000019 8921.71 +Customer#000000022 598.98 Customer#000000023 3339.02 +Customer#000000025 7140.7 +Customer#000000028 1014.1799999999998 +Customer#000000029 7625.27 +Customer#000000031 5243.89 +Customer#000000032 3478.53 +Customer#000000034 8596.7 Customer#000000035 1235.24 -Customer#000000091 4650.14 -Customer#000000016 4688.03 -Customer#000000098 -544.37 Customer#000000037 -910.75 -Customer#000000136 -835.39 -Customer#000000118 3589.37 -Customer#000000022 598.98 -Customer#000000005 801.47 -Customer#000000109 -709.1 Customer#000000038 6352.11 -Customer#000000076 5752.33 -Customer#000000056 6537.86 Customer#000000040 1342.3 -Customer#000000116 8410.99 +Customer#000000041 277.95 +Customer#000000043 9911.28 +Customer#000000044 7322.94 +Customer#000000046 5751.59 +Customer#000000047 281.58 +Customer#000000049 4580.94 +Customer#000000052 5637.28 +Customer#000000053 4120.64 +Customer#000000055 4579.11 +Customer#000000056 6537.86 +Customer#000000058 6485.46 +Customer#000000059 3465.6 +Customer#000000061 1543.24 +Customer#000000062 602.61 +Customer#000000064 -639.64 +Customer#000000065 8802.16 +Customer#000000067 8173.59 +Customer#000000070 4874.52 +Customer#000000071 -604.19 +Customer#000000073 4295.5 +Customer#000000074 2771.43 +Customer#000000076 5752.33 +Customer#000000079 5128.28 +Customer#000000080 7390.53 +Customer#000000082 9475.34 +Customer#000000083 6470.51 +Customer#000000085 3393.64 +Customer#000000086 3313.32 +Customer#000000088 8038.44 +Customer#000000089 1537.76 +Customer#000000091 4650.14 +Customer#000000092 1189.91 +Customer#000000094 5507.11 +Customer#000000095 5334.38 +Customer#000000097 2171.48 +Customer#000000098 -544.37 +Customer#000000100 9896.89 +Customer#000000101 7477.96 +Customer#000000103 2764.45 +Customer#000000104 -581.38 +Customer#000000106 3295.42 +Customer#000000107 2521.15 +Customer#000000109 -709.1 +Customer#000000110 7469.99 +Customer#000000112 2960.35 Customer#000000115 7515.92 +Customer#000000116 8410.99 +Customer#000000118 3589.37 +Customer#000000121 6435.32 +Customer#000000122 7872.46 +Customer#000000127 9287.71 +Customer#000000128 -979.96 +Customer#000000130 5080.58 +Customer#000000131 8602.53 +Customer#000000133 2321.67 +Customer#000000134 4615.9 +Customer#000000136 -835.39 +Customer#000000137 7845.3 +Customer#000000139 7904.78 Customer#000000140 9970.15 -Customer#000000017 13.34 -Customer#000000052 5637.28 +Customer#000000142 2216.81 +Customer#000000143 2193.5 +Customer#000000145 9755.93 +Customer#000000148 2142.6 +Customer#000000149 8966.65 set @a2=3; execute stmt using @a2; 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal -Customer#000000025 7143.7 +Customer#000000001 721.56 +Customer#000000002 131.65 +Customer#000000005 804.47 +Customer#000000007 9571.95 +Customer#000000008 6829.74 +Customer#000000010 2763.54 +Customer#000000011 -262.6 Customer#000000013 3867.34 -Customer#000000065 8805.16 -Customer#000000032 3481.53 +Customer#000000014 5276.3 +Customer#000000016 4691.03 +Customer#000000017 16.34 +Customer#000000019 8924.71 +Customer#000000022 601.98 Customer#000000023 3342.02 +Customer#000000025 7143.7 +Customer#000000028 1017.1799999999998 +Customer#000000029 7628.27 +Customer#000000031 5246.89 +Customer#000000032 3481.53 +Customer#000000034 8599.7 Customer#000000035 1238.24 -Customer#000000091 4653.14 -Customer#000000016 4691.03 -Customer#000000098 -541.37 Customer#000000037 -907.75 -Customer#000000136 -832.39 -Customer#000000118 3592.37 -Customer#000000022 601.98 -Customer#000000005 804.47 -Customer#000000109 -706.1 Customer#000000038 6355.11 -Customer#000000076 5755.33 -Customer#000000056 6540.86 Customer#000000040 1345.3 -Customer#000000116 8413.99 -Customer#000000115 7518.92 -Customer#000000140 9973.15 -Customer#000000017 16.34 +Customer#000000041 280.95 +Customer#000000043 9914.28 +Customer#000000044 7325.94 +Customer#000000046 5754.59 +Customer#000000047 284.58 +Customer#000000049 4583.94 Customer#000000052 5640.28 -execute stmt using -(@a1+@a2); -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'); -c_name c_acctbal -Customer#000000025 7133.7 +Customer#000000053 4123.64 +Customer#000000055 4582.11 +Customer#000000056 6540.86 +Customer#000000058 6488.46 +Customer#000000059 3468.6 +Customer#000000061 1546.24 +Customer#000000062 605.61 +Customer#000000064 -636.64 +Customer#000000065 8805.16 +Customer#000000067 8176.59 +Customer#000000070 4877.52 +Customer#000000071 -601.19 +Customer#000000073 4298.5 +Customer#000000074 2774.43 +Customer#000000076 5755.33 +Customer#000000079 5131.28 +Customer#000000080 7393.53 +Customer#000000082 9478.34 +Customer#000000083 6473.51 +Customer#000000085 3396.64 +Customer#000000086 3316.32 +Customer#000000088 8041.44 +Customer#000000089 1540.76 +Customer#000000091 4653.14 +Customer#000000092 1192.91 +Customer#000000094 5510.11 +Customer#000000095 5337.38 +Customer#000000097 2174.48 +Customer#000000098 -541.37 +Customer#000000100 9899.89 +Customer#000000101 7480.96 +Customer#000000103 2767.45 +Customer#000000104 -578.38 +Customer#000000106 3298.42 +Customer#000000107 2524.15 +Customer#000000109 -706.1 +Customer#000000110 7472.99 +Customer#000000112 2963.35 +Customer#000000115 7518.92 +Customer#000000116 8413.99 +Customer#000000118 3592.37 +Customer#000000121 6438.32 +Customer#000000122 7875.46 +Customer#000000127 9290.71 +Customer#000000128 -976.96 +Customer#000000130 5083.58 +Customer#000000131 8605.53 +Customer#000000133 2324.67 +Customer#000000134 4618.9 +Customer#000000136 -832.39 +Customer#000000137 7848.3 +Customer#000000139 7907.78 +Customer#000000140 9973.15 +Customer#000000142 2219.81 +Customer#000000143 2196.5 +Customer#000000145 9758.93 +Customer#000000148 2145.6 +Customer#000000149 8969.65 +execute stmt using -(@a1+@a2); +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'); +c_name c_acctbal +Customer#000000001 711.56 +Customer#000000002 121.65 +Customer#000000005 794.47 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 Customer#000000013 3857.34 -Customer#000000065 8795.16 -Customer#000000032 3471.53 +Customer#000000014 5266.3 +Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000019 8914.71 +Customer#000000022 591.98 Customer#000000023 3332.02 +Customer#000000025 7133.7 +Customer#000000028 1007.1799999999998 +Customer#000000029 7618.27 +Customer#000000031 5236.89 +Customer#000000032 3471.53 +Customer#000000034 8589.7 Customer#000000035 1228.24 -Customer#000000091 4643.14 -Customer#000000016 4681.03 -Customer#000000098 -551.37 Customer#000000037 -917.75 -Customer#000000136 -842.39 -Customer#000000118 3582.37 -Customer#000000022 591.98 -Customer#000000005 794.47 -Customer#000000109 -716.1 Customer#000000038 6345.11 -Customer#000000076 5745.33 -Customer#000000056 6530.86 Customer#000000040 1335.3 -Customer#000000116 8403.99 +Customer#000000041 270.95 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 +Customer#000000052 5630.28 +Customer#000000053 4113.64 +Customer#000000055 4572.11 +Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000062 595.61 +Customer#000000064 -646.64 +Customer#000000065 8795.16 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 +Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000089 1530.76 +Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000094 5500.11 +Customer#000000095 5327.38 +Customer#000000097 2164.48 +Customer#000000098 -551.37 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000107 2514.15 +Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 Customer#000000115 7508.92 +Customer#000000116 8403.99 +Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000128 -986.96 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 +Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 Customer#000000140 9963.15 -Customer#000000017 6.34 -Customer#000000052 5630.28 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000145 9748.93 +Customer#000000148 2135.6 +Customer#000000149 8959.65 deallocate prepare stmt; # Materialization SJM PS # ====================== @@ -1949,6 +2562,7 @@ o_orderkey o_totalprice drop procedure p; # FirstMatch SP # ============= +set optimizer_switch='materialization=off'; create procedure p(d int) update customer set c_acctbal = c_acctbal+d where c_nationkey in (select n_nationkey from nation where n_regionkey in (1,2)) @@ -2111,122 +2725,383 @@ Customer#000000082 9468.34 Customer#000000124 1842.49 Customer#000000127 9280.71 drop procedure p; +set optimizer_switch='materialization=default'; # Materialization SP # ================== create procedure p(d int) update customer set c_acctbal = c_acctbal+d where c_custkey in (select o_custkey from orders -where o_orderDATE between '1992-01-09' and '1992-03-08'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal -Customer#000000025 7133.7 +Customer#000000001 711.56 +Customer#000000002 121.65 +Customer#000000005 794.47 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 Customer#000000013 3857.34 -Customer#000000065 8795.16 -Customer#000000032 3471.53 +Customer#000000014 5266.3 +Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000019 8914.71 +Customer#000000022 591.98 Customer#000000023 3332.02 +Customer#000000025 7133.7 +Customer#000000028 1007.1799999999998 +Customer#000000029 7618.27 +Customer#000000031 5236.89 +Customer#000000032 3471.53 +Customer#000000034 8589.7 Customer#000000035 1228.24 -Customer#000000091 4643.14 -Customer#000000016 4681.03 -Customer#000000098 -551.37 Customer#000000037 -917.75 -Customer#000000136 -842.39 -Customer#000000118 3582.37 -Customer#000000022 591.98 -Customer#000000005 794.47 -Customer#000000109 -716.1 Customer#000000038 6345.11 -Customer#000000076 5745.33 -Customer#000000056 6530.86 Customer#000000040 1335.3 -Customer#000000116 8403.99 +Customer#000000041 270.95 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 +Customer#000000052 5630.28 +Customer#000000053 4113.64 +Customer#000000055 4572.11 +Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000062 595.61 +Customer#000000064 -646.64 +Customer#000000065 8795.16 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 +Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000089 1530.76 +Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000094 5500.11 +Customer#000000095 5327.38 +Customer#000000097 2164.48 +Customer#000000098 -551.37 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000107 2514.15 +Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 Customer#000000115 7508.92 +Customer#000000116 8403.99 +Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000128 -986.96 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 +Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 Customer#000000140 9963.15 -Customer#000000017 6.34 -Customer#000000052 5630.28 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000145 9748.93 +Customer#000000148 2135.6 +Customer#000000149 8959.65 call p(3); 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal -Customer#000000025 7136.7 +Customer#000000001 714.56 +Customer#000000002 124.65 +Customer#000000005 797.47 +Customer#000000007 9564.95 +Customer#000000008 6822.74 +Customer#000000010 2756.54 +Customer#000000011 -269.6 Customer#000000013 3860.34 -Customer#000000065 8798.16 -Customer#000000032 3474.53 +Customer#000000014 5269.3 +Customer#000000016 4684.03 +Customer#000000017 9.34 +Customer#000000019 8917.71 +Customer#000000022 594.98 Customer#000000023 3335.02 +Customer#000000025 7136.7 +Customer#000000028 1010.1799999999998 +Customer#000000029 7621.27 +Customer#000000031 5239.89 +Customer#000000032 3474.53 +Customer#000000034 8592.7 Customer#000000035 1231.24 -Customer#000000091 4646.14 -Customer#000000016 4684.03 -Customer#000000098 -548.37 Customer#000000037 -914.75 -Customer#000000136 -839.39 -Customer#000000118 3585.37 -Customer#000000022 594.98 -Customer#000000005 797.47 -Customer#000000109 -713.1 Customer#000000038 6348.11 -Customer#000000076 5748.33 -Customer#000000056 6533.86 Customer#000000040 1338.3 -Customer#000000116 8406.99 +Customer#000000041 273.95 +Customer#000000043 9907.28 +Customer#000000044 7318.94 +Customer#000000046 5747.59 +Customer#000000047 277.58 +Customer#000000049 4576.94 +Customer#000000052 5633.28 +Customer#000000053 4116.64 +Customer#000000055 4575.11 +Customer#000000056 6533.86 +Customer#000000058 6481.46 +Customer#000000059 3461.6 +Customer#000000061 1539.24 +Customer#000000062 598.61 +Customer#000000064 -643.64 +Customer#000000065 8798.16 +Customer#000000067 8169.59 +Customer#000000070 4870.52 +Customer#000000071 -608.19 +Customer#000000073 4291.5 +Customer#000000074 2767.43 +Customer#000000076 5748.33 +Customer#000000079 5124.28 +Customer#000000080 7386.53 +Customer#000000082 9471.34 +Customer#000000083 6466.51 +Customer#000000085 3389.64 +Customer#000000086 3309.32 +Customer#000000088 8034.44 +Customer#000000089 1533.76 +Customer#000000091 4646.14 +Customer#000000092 1185.91 +Customer#000000094 5503.11 +Customer#000000095 5330.38 +Customer#000000097 2167.48 +Customer#000000098 -548.37 +Customer#000000100 9892.89 +Customer#000000101 7473.96 +Customer#000000103 2760.45 +Customer#000000104 -585.38 +Customer#000000106 3291.42 +Customer#000000107 2517.15 +Customer#000000109 -713.1 +Customer#000000110 7465.99 +Customer#000000112 2956.35 Customer#000000115 7511.92 +Customer#000000116 8406.99 +Customer#000000118 3585.37 +Customer#000000121 6431.32 +Customer#000000122 7868.46 +Customer#000000127 9283.71 +Customer#000000128 -983.96 +Customer#000000130 5076.58 +Customer#000000131 8598.53 +Customer#000000133 2317.67 +Customer#000000134 4611.9 +Customer#000000136 -839.39 +Customer#000000137 7841.3 +Customer#000000139 7900.78 Customer#000000140 9966.15 -Customer#000000017 9.34 -Customer#000000052 5633.28 +Customer#000000142 2212.81 +Customer#000000143 2189.5 +Customer#000000145 9751.93 +Customer#000000148 2138.6 +Customer#000000149 8962.65 call p(7); 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal -Customer#000000025 7143.7 +Customer#000000001 721.56 +Customer#000000002 131.65 +Customer#000000005 804.47 +Customer#000000007 9571.95 +Customer#000000008 6829.74 +Customer#000000010 2763.54 +Customer#000000011 -262.6 Customer#000000013 3867.34 -Customer#000000065 8805.16 -Customer#000000032 3481.53 +Customer#000000014 5276.3 +Customer#000000016 4691.03 +Customer#000000017 16.34 +Customer#000000019 8924.71 +Customer#000000022 601.98 Customer#000000023 3342.02 +Customer#000000025 7143.7 +Customer#000000028 1017.1799999999998 +Customer#000000029 7628.27 +Customer#000000031 5246.89 +Customer#000000032 3481.53 +Customer#000000034 8599.7 Customer#000000035 1238.24 -Customer#000000091 4653.14 -Customer#000000016 4691.03 -Customer#000000098 -541.37 Customer#000000037 -907.75 -Customer#000000136 -832.39 -Customer#000000118 3592.37 -Customer#000000022 601.98 -Customer#000000005 804.47 -Customer#000000109 -706.1 Customer#000000038 6355.11 -Customer#000000076 5755.33 -Customer#000000056 6540.86 Customer#000000040 1345.3 -Customer#000000116 8413.99 +Customer#000000041 280.95 +Customer#000000043 9914.28 +Customer#000000044 7325.94 +Customer#000000046 5754.59 +Customer#000000047 284.58 +Customer#000000049 4583.94 +Customer#000000052 5640.28 +Customer#000000053 4123.64 +Customer#000000055 4582.11 +Customer#000000056 6540.86 +Customer#000000058 6488.46 +Customer#000000059 3468.6 +Customer#000000061 1546.24 +Customer#000000062 605.61 +Customer#000000064 -636.64 +Customer#000000065 8805.16 +Customer#000000067 8176.59 +Customer#000000070 4877.52 +Customer#000000071 -601.19 +Customer#000000073 4298.5 +Customer#000000074 2774.43 +Customer#000000076 5755.33 +Customer#000000079 5131.28 +Customer#000000080 7393.53 +Customer#000000082 9478.34 +Customer#000000083 6473.51 +Customer#000000085 3396.64 +Customer#000000086 3316.32 +Customer#000000088 8041.44 +Customer#000000089 1540.76 +Customer#000000091 4653.14 +Customer#000000092 1192.91 +Customer#000000094 5510.11 +Customer#000000095 5337.38 +Customer#000000097 2174.48 +Customer#000000098 -541.37 +Customer#000000100 9899.89 +Customer#000000101 7480.96 +Customer#000000103 2767.45 +Customer#000000104 -578.38 +Customer#000000106 3298.42 +Customer#000000107 2524.15 +Customer#000000109 -706.1 +Customer#000000110 7472.99 +Customer#000000112 2963.35 Customer#000000115 7518.92 +Customer#000000116 8413.99 +Customer#000000118 3592.37 +Customer#000000121 6438.32 +Customer#000000122 7875.46 +Customer#000000127 9290.71 +Customer#000000128 -976.96 +Customer#000000130 5083.58 +Customer#000000131 8605.53 +Customer#000000133 2324.67 +Customer#000000134 4618.9 +Customer#000000136 -832.39 +Customer#000000137 7848.3 +Customer#000000139 7907.78 Customer#000000140 9973.15 -Customer#000000017 16.34 -Customer#000000052 5640.28 +Customer#000000142 2219.81 +Customer#000000143 2196.5 +Customer#000000145 9758.93 +Customer#000000148 2145.6 +Customer#000000149 8969.65 call p(-(3+7)); 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'); +where o_orderDATE between '1992-01-09' and '1993-03-08'); c_name c_acctbal -Customer#000000025 7133.7 +Customer#000000001 711.56 +Customer#000000002 121.65 +Customer#000000005 794.47 +Customer#000000007 9561.95 +Customer#000000008 6819.74 +Customer#000000010 2753.54 +Customer#000000011 -272.6 Customer#000000013 3857.34 -Customer#000000065 8795.16 -Customer#000000032 3471.53 +Customer#000000014 5266.3 +Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000019 8914.71 +Customer#000000022 591.98 Customer#000000023 3332.02 +Customer#000000025 7133.7 +Customer#000000028 1007.1799999999998 +Customer#000000029 7618.27 +Customer#000000031 5236.89 +Customer#000000032 3471.53 +Customer#000000034 8589.7 Customer#000000035 1228.24 -Customer#000000091 4643.14 -Customer#000000016 4681.03 -Customer#000000098 -551.37 Customer#000000037 -917.75 -Customer#000000136 -842.39 -Customer#000000118 3582.37 -Customer#000000022 591.98 -Customer#000000005 794.47 -Customer#000000109 -716.1 Customer#000000038 6345.11 -Customer#000000076 5745.33 -Customer#000000056 6530.86 Customer#000000040 1335.3 -Customer#000000116 8403.99 +Customer#000000041 270.95 +Customer#000000043 9904.28 +Customer#000000044 7315.94 +Customer#000000046 5744.59 +Customer#000000047 274.58 +Customer#000000049 4573.94 +Customer#000000052 5630.28 +Customer#000000053 4113.64 +Customer#000000055 4572.11 +Customer#000000056 6530.86 +Customer#000000058 6478.46 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000062 595.61 +Customer#000000064 -646.64 +Customer#000000065 8795.16 +Customer#000000067 8166.59 +Customer#000000070 4867.52 +Customer#000000071 -611.19 +Customer#000000073 4288.5 +Customer#000000074 2764.43 +Customer#000000076 5745.33 +Customer#000000079 5121.28 +Customer#000000080 7383.53 +Customer#000000082 9468.34 +Customer#000000083 6463.51 +Customer#000000085 3386.64 +Customer#000000086 3306.32 +Customer#000000088 8031.44 +Customer#000000089 1530.76 +Customer#000000091 4643.14 +Customer#000000092 1182.91 +Customer#000000094 5500.11 +Customer#000000095 5327.38 +Customer#000000097 2164.48 +Customer#000000098 -551.37 +Customer#000000100 9889.89 +Customer#000000101 7470.96 +Customer#000000103 2757.45 +Customer#000000104 -588.38 +Customer#000000106 3288.42 +Customer#000000107 2514.15 +Customer#000000109 -716.1 +Customer#000000110 7462.99 +Customer#000000112 2953.35 Customer#000000115 7508.92 +Customer#000000116 8403.99 +Customer#000000118 3582.37 +Customer#000000121 6428.32 +Customer#000000122 7865.46 +Customer#000000127 9280.71 +Customer#000000128 -986.96 +Customer#000000130 5073.58 +Customer#000000131 8595.53 +Customer#000000133 2314.67 +Customer#000000134 4608.9 +Customer#000000136 -842.39 +Customer#000000137 7838.3 +Customer#000000139 7897.78 Customer#000000140 9963.15 -Customer#000000017 6.34 -Customer#000000052 5630.28 +Customer#000000142 2209.81 +Customer#000000143 2186.5 +Customer#000000145 9748.93 +Customer#000000148 2135.6 +Customer#000000149 8959.65 drop procedure p; # Materialization SJM SP # ====================== @@ -2296,7 +3171,7 @@ where c_nationkey in (1,2)) order by o_totalprice limit 500; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY orders range i_o_orderdate i_o_orderdate 4 NULL 108 Using where; Using filesort -2 DEPENDENT SUBQUERY customer unique_subquery|filter PRIMARY,i_c_nationkey PRIMARY|i_c_nationkey 4|5 func 1 (10%) Using where; Using rowid filter +2 DEPENDENT SUBQUERY customer unique_subquery PRIMARY,i_c_nationkey PRIMARY 4 func 1 Using where update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)) @@ -2336,7 +3211,7 @@ o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer range PRIMARY,i_c_nationkey i_c_nationkey 5 NULL 15 Using index condition -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 (7%) Using where; Using rowid filter +1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (7%) Using where; Using rowid filter update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2)); diff --git a/mysql-test/main/update_single_to_multi.test b/mysql-test/main/update_single_to_multi.test index bf89a6c..a215c5a 100644 --- a/mysql-test/main/update_single_to_multi.test +++ b/mysql-test/main/update_single_to_multi.test @@ -15,8 +15,9 @@ use dbt3_s001; --enable_query_log create index i_n_name on nation(n_name); -analyze table nation; - +analyze table + nation, lineitem, customer, orders, part, supplier, partsupp, region +persistent for all; --echo # Pullout --echo # ======= @@ -209,117 +210,148 @@ select c_name, c_acctbal from customer where $c6; --echo # Materialization --echo # =============== +set optimizer_switch='firstmatch=off'; + let $c7= + c_nationkey in (select n_nationkey from nation where + n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA')) + and c_custkey in (select o_custkey from orders - where o_orderDATE between '1992-01-09' and '1992-03-08'); + where o_orderDATE between "1992-01-09" and "1995-01-08"); eval explain select c_name, c_acctbal from customer where $c7; eval -explain format=json -select c_name, c_acctbal from customer where $c7; -eval select c_name, c_acctbal from customer where $c7; eval explain -update customer set c_acctbal = c_acctbal+5 where $c7; +update customer set c_acctbal = c_acctbal+20 where $c7; eval -explain format=json -update customer set c_acctbal = c_acctbal+5 where $c7; -eval -update customer set c_acctbal = c_acctbal+5 where $c7; +update customer set c_acctbal = c_acctbal+20 where $c7; eval select c_name, c_acctbal from customer where $c7; eval -update customer set c_acctbal = c_acctbal-5 where $c7; +update customer set c_acctbal = c_acctbal-20 where $c7; eval select c_name, c_acctbal from customer where $c7; +set optimizer_switch='firstmatch=default'; let $c8= c_custkey in (select o_custkey from orders - where o_orderDATE between '1992-06-09' and '1993-01-08'); + where o_orderDATE between '1992-01-09' and '1993-03-08'); eval explain select c_name, c_acctbal from customer where $c8; eval +explain format=json +select c_name, c_acctbal from customer where $c8; +eval select c_name, c_acctbal from customer where $c8; eval explain -update customer set c_acctbal = c_acctbal+1 where $c8; +update customer set c_acctbal = c_acctbal+5 where $c8; eval -update customer set c_acctbal = c_acctbal+1 where $c8; +explain format=json +update customer set c_acctbal = c_acctbal+5 where $c8; +eval +update customer set c_acctbal = c_acctbal+5 where $c8; eval select c_name, c_acctbal from customer where $c8; eval -update customer set c_acctbal = c_acctbal-1 where $c8; +update customer set c_acctbal = c_acctbal-5 where $c8; eval select c_name, c_acctbal from customer where $c8; +let $c9= + c_custkey in (select o_custkey from orders + where o_orderDATE between '1992-06-09' and '1993-01-08'); + +eval +explain +select c_name, c_acctbal from customer where $c9; +eval +select c_name, c_acctbal from customer where $c9; + +eval +explain +update customer set c_acctbal = c_acctbal+1 where $c9; +eval +update customer set c_acctbal = c_acctbal+1 where $c9; +eval +select c_name, c_acctbal from customer where $c9; + +eval +update customer set c_acctbal = c_acctbal-1 where $c9; +eval +select c_name, c_acctbal from customer where $c9; + + + --echo # Materialization SJM --echo # =================== -let $c9= +let $c10= c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08' group by o_custkey having count(o_custkey) > 1); eval explain -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; eval explain format=json -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; eval explain -update customer set c_acctbal = c_acctbal-5 where $c9; +update customer set c_acctbal = c_acctbal-5 where $c10; eval explain format=json -update customer set c_acctbal = c_acctbal-5 where $c9; +update customer set c_acctbal = c_acctbal-5 where $c10; eval -update customer set c_acctbal = c_acctbal-5 where $c9; +update customer set c_acctbal = c_acctbal-5 where $c10; eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; eval -update customer set c_acctbal = c_acctbal+5 where $c9; +update customer set c_acctbal = c_acctbal+5 where $c10; eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; -let $c10= +let $c11= 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); eval explain -select c_name, c_acctbal from customer where $c10; +select c_name, c_acctbal from customer where $c11; eval -select c_name, c_acctbal from customer where $c10; +select c_name, c_acctbal from customer where $c11; eval explain -update customer set c_acctbal = c_acctbal-1 where $c10; +update customer set c_acctbal = c_acctbal-1 where $c11; eval -update customer set c_acctbal = c_acctbal-1 where $c10; +update customer set c_acctbal = c_acctbal-1 where $c11; eval -select c_name, c_acctbal from customer where $c10; +select c_name, c_acctbal from customer where $c11; eval -update customer set c_acctbal = c_acctbal+1 where $c10; +update customer set c_acctbal = c_acctbal+1 where $c11; eval -select c_name, c_acctbal from customer where $c10; +select c_name, c_acctbal from customer where $c11; --echo # Pullout PS @@ -350,6 +382,8 @@ deallocate prepare stmt; --echo # FirstMatch PS --echo # ============= +set optimizer_switch='materialization=off'; + eval prepare stmt from " update customer set c_acctbal = c_acctbal+? where $c5; @@ -371,28 +405,29 @@ select c_name, c_acctbal from customer where $c5; deallocate prepare stmt; +set optimizer_switch='materialization=default'; --echo # Materialization PS --echo # ================== eval prepare stmt from " -update customer set c_acctbal = c_acctbal+? where $c7; +update customer set c_acctbal = c_acctbal+? where $c8; "; eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; set @a1=7; execute stmt using @a1; eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; set @a2=3; execute stmt using @a2; eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; execute stmt using -(@a1+@a2); eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; deallocate prepare stmt; @@ -402,22 +437,22 @@ deallocate prepare stmt; eval prepare stmt from " -update customer set c_acctbal = c_acctbal+? where $c9; +update customer set c_acctbal = c_acctbal+? where $c10; "; eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; set @a1=-2; execute stmt using @a1; eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; set @a2=-1; execute stmt using @a2; eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; execute stmt using -(@a1+@a2); eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; deallocate prepare stmt; @@ -447,6 +482,8 @@ drop procedure p; --echo # FirstMatch SP --echo # ============= +set optimizer_switch='materialization=off'; + eval create procedure p(d int) update customer set c_acctbal = c_acctbal+d where $c5; @@ -465,25 +502,27 @@ select c_name, c_acctbal from customer where $c5; drop procedure p; +set optimizer_switch='materialization=default'; + --echo # Materialization SP --echo # ================== eval create procedure p(d int) -update customer set c_acctbal = c_acctbal+d where $c7; +update customer set c_acctbal = c_acctbal+d where $c8; eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; call p(3); eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; call p(7); eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; call p(-(3+7)); eval -select c_name, c_acctbal from customer where $c7; +select c_name, c_acctbal from customer where $c8; drop procedure p; @@ -493,19 +532,19 @@ drop procedure p; eval create procedure p(d int) -update customer set c_acctbal = c_acctbal+d where $c9; +update customer set c_acctbal = c_acctbal+d where $c10; eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; call p(-1); eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; call p(-2); eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; call p(1+2); eval -select c_name, c_acctbal from customer where $c9; +select c_name, c_acctbal from customer where $c10; drop procedure p; diff --git a/mysql-test/main/update_use_source.result b/mysql-test/main/update_use_source.result index 3f52741..99d7242 100644 --- a/mysql-test/main/update_use_source.result +++ b/mysql-test/main/update_use_source.result @@ -316,7 +316,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 where +1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition 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; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ad55ddb..ba64c00 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -32592,7 +32592,8 @@ bool Sql_cmd_dml::execute_inner(THD *thd) if (unlikely(thd->is_error())) goto err; - join->exec(); + if (join->exec()) + goto err; if (thd->lex->describe & DESCRIBE_EXTENDED) {