revision-id: 6b21981eb1f61241cb4125a25e4e479164e213c0 (mariadb-10.6.1-472-g6b21981) parent(s): 596cde07f17c490c43db10ce0a8cbb7bd4dbf5ae author: Igor Babaev committer: Igor Babaev timestamp: 2022-06-18 21:44:00 -0700 message: MDEV-7487 Semi-join optimization for single-table update/delete statements This patch allows to use semi-join optimization at the top level of single-table update and delete statements. The problem of supporting such optimization became easy to resolve after processing a single-table update/delete statement started using JOIN structure. This allowed to use JOIN::prepare() not only for multi-table updates/deletes but for single-table ones as well. This was done in the patch for mdev-28883: Re-design the upper level of handling UPDATE and DELETE statements. Note that JOIN::prepare() detects all subqueries that can be considered as candidates for semi-join optimization. The code added by this patch looks for such candidates at the top level and if such candidates are found in the processed single-table update/delete the statement is handled in the same way as a multi-table update/delete. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/main/delete_single_to_multi.result | 2216 ++++++++++++++++++++ mysql-test/main/delete_single_to_multi.test | 723 +++++++ mysql-test/main/log_state.result | 2 +- .../main/myisam_explain_non_select_all.result | 45 +- mysql-test/main/opt_trace.result | 20 + mysql-test/main/update_single_to_multi.result | 2209 +++++++++++++++++++ mysql-test/main/update_single_to_multi.test | 511 +++++ sql/opt_subselect.cc | 36 +- sql/sql_class.h | 1 + sql/sql_delete.cc | 14 +- sql/sql_lex.h | 1 + sql/sql_select.cc | 3 +- sql/sql_update.cc | 12 +- sql/sql_yacc.yy | 13 + 14 files changed, 5774 insertions(+), 32 deletions(-) diff --git a/mysql-test/main/delete_single_to_multi.result b/mysql-test/main/delete_single_to_multi.result new file mode 100644 index 0000000..6cbc63f --- /dev/null +++ b/mysql-test/main/delete_single_to_multi.result @@ -0,0 +1,2216 @@ +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; +Table Op Msg_type Msg_text +dbt3_s001.nation analyze status Engine-independent statistics collected +dbt3_s001.nation analyze status OK +# Pullout +# ======= +explain +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 (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 +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 +where c_nationkey in (select n_nationkey from nation +where n_name='PERU')); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "nation", + "access_type": "ref", + "possible_keys": ["PRIMARY", "i_n_name"], + "key": "i_n_name", + "key_length": "26", + "used_key_parts": ["n_name"], + "ref": ["const"], + "rows": 1, + "filtered": 100, + "index_condition": "nation.n_name = 'PERU'" + } + }, + { + "table": { + "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"], + "rows": 11, + "filtered": 100 + } + }, + { + "table": { + "table_name": "orders", + "access_type": "ref", + "possible_keys": ["i_o_orderdate", "i_o_custkey"], + "key": "i_o_custkey", + "key_length": "5", + "used_key_parts": ["o_custkey"], + "ref": ["dbt3_s001.customer.c_custkey"], + "rowid_filter": { + "range": { + "key": "i_o_orderdate", + "used_key_parts": ["o_orderDATE"] + }, + "rows": 108, + "selectivity_pct": 7.2 + }, + "rows": 11, + "filtered": 7.199999809, + "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" + } + } + ] + } +} +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +1729 12137.76 +2880 145761.99 +3142 16030.15 +5095 184583.99 +5121 150334.57 +5382 138423.03 +644 201268.06 +737 12984.85 +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 (select n_nationkey from nation +where n_name='PERU')); +explain +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 (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 +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 +where c_nationkey in (select n_nationkey from nation +where n_name='PERU')); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "nation", + "access_type": "ref", + "possible_keys": ["PRIMARY", "i_n_name"], + "key": "i_n_name", + "key_length": "26", + "used_key_parts": ["n_name"], + "ref": ["const"], + "rows": 1, + "filtered": 100, + "index_condition": "nation.n_name = 'PERU'" + } + }, + { + "table": { + "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"], + "rows": 11, + "filtered": 100 + } + }, + { + "table": { + "table_name": "orders", + "access_type": "ref", + "possible_keys": ["i_o_orderdate", "i_o_custkey"], + "key": "i_o_custkey", + "key_length": "5", + "used_key_parts": ["o_custkey"], + "ref": ["dbt3_s001.customer.c_custkey"], + "rowid_filter": { + "range": { + "key": "i_o_orderdate", + "used_key_parts": ["o_orderDATE"] + }, + "rows": 108, + "selectivity_pct": 7.2 + }, + "rows": 11, + "filtered": 7.199999809, + "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" + } + } + ] + } +} +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 (select n_nationkey from nation +where n_name='PERU')); +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +insert into orders select * from t; +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +1729 12137.76 +2880 145761.99 +3142 16030.15 +5095 184583.99 +5121 150334.57 +5382 138423.03 +644 201268.06 +737 12984.85 +drop table t; +explain +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')); +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 +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 +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 +create table t as +select * 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')); +explain +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 +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 +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 +s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +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 +insert into partsupp select * from t; +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 +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 +drop table t; +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 +ps_suppkey in (select s_suppkey from supplier +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 +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 +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 +create table t as +select * 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')); +explain +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 +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 +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 +where s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +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 +insert into partsupp select * from t; +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 +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 +drop table t; +explain +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 +where c_nationkey in +(select n_nationkey from nation +where n_name='PERU')) +and +o_orderDATE between '1992-06-30' and '1992-12-31') +and +(l_partkey, l_suppkey) in +(select p_partkey, s_suppkey from part, supplier +where p_retailprice between 901 and 1000 and +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 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 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 +1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 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 +where c_nationkey in +(select n_nationkey from nation +where n_name='PERU')) +and +o_orderDATE between '1992-06-30' and '1992-12-31') +and +(l_partkey, l_suppkey) in +(select p_partkey, s_suppkey from part, supplier +where p_retailprice between 901 and 1000 and +s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +l_orderkey l_linenumber l_tax +2500 2 0.02 +2500 4 0.02 +4996 1 0.01 +933 1 0.04 +create table t as +select * from lineitem where l_orderkey in (select o_orderkey from orders +where o_custkey in +(select c_custkey from customer +where c_nationkey in +(select n_nationkey from nation +where n_name='PERU')) +and +o_orderDATE between '1992-06-30' and '1992-12-31') +and +(l_partkey, l_suppkey) in +(select p_partkey, s_suppkey from part, supplier +where p_retailprice between 901 and 1000 and +s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +explain +delete from lineitem where l_orderkey in (select o_orderkey from orders +where o_custkey in +(select c_custkey from customer +where c_nationkey in +(select n_nationkey from nation +where n_name='PERU')) +and +o_orderDATE between '1992-06-30' and '1992-12-31') +and +(l_partkey, l_suppkey) in +(select p_partkey, s_suppkey from part, supplier +where p_retailprice between 901 and 1000 and +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 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 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 +1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where +delete from lineitem where l_orderkey in (select o_orderkey from orders +where o_custkey in +(select c_custkey from customer +where c_nationkey in +(select n_nationkey from nation +where n_name='PERU')) +and +o_orderDATE between '1992-06-30' and '1992-12-31') +and +(l_partkey, l_suppkey) in +(select p_partkey, s_suppkey from part, supplier +where p_retailprice between 901 and 1000 and +s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +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 +where c_nationkey in +(select n_nationkey from nation +where n_name='PERU')) +and +o_orderDATE between '1992-06-30' and '1992-12-31') +and +(l_partkey, l_suppkey) in +(select p_partkey, s_suppkey from part, supplier +where p_retailprice between 901 and 1000 and +s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +l_orderkey l_linenumber l_tax +insert into lineitem select * from t; +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 +where c_nationkey in +(select n_nationkey from nation +where n_name='PERU')) +and +o_orderDATE between '1992-06-30' and '1992-12-31') +and +(l_partkey, l_suppkey) in +(select p_partkey, s_suppkey from part, supplier +where p_retailprice between 901 and 1000 and +s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +l_orderkey l_linenumber l_tax +2500 2 0.02 +2500 4 0.02 +4996 1 0.01 +933 1 0.04 +drop table t; +# FirstMatch +# ========== +explain +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-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 (6%) Using where; FirstMatch(nation); Using rowid filter +explain format=json +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "customer", + "access_type": "ALL", + "possible_keys": ["PRIMARY", "i_c_nationkey"], + "rows": 150, + "filtered": 100, + "attached_condition": "customer.c_nationkey is not null" + } + }, + { + "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": { + "table_name": "orders", + "access_type": "ref", + "possible_keys": ["i_o_orderdate", "i_o_custkey"], + "key": "i_o_custkey", + "key_length": "5", + "used_key_parts": ["o_custkey"], + "ref": ["dbt3_s001.customer.c_custkey"], + "rowid_filter": { + "range": { + "key": "i_o_orderdate", + "used_key_parts": ["o_orderDATE"] + }, + "rows": 89, + "selectivity_pct": 5.933333333 + }, + "rows": 11, + "filtered": 5.933333397, + "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-03-08'", + "first_match": "nation" + } + } + ] + } +} +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9561.95 +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 +Customer#000000064 -646.64 +Customer#000000067 8166.59 +Customer#000000082 9468.34 +Customer#000000091 4643.14 +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#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +create table t as +select * from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +explain +delete from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-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 (6%) Using where; FirstMatch(nation); Using rowid filter +explain format=json +delete from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "customer", + "access_type": "ALL", + "possible_keys": ["PRIMARY", "i_c_nationkey"], + "rows": 150, + "filtered": 100, + "attached_condition": "customer.c_nationkey is not null" + } + }, + { + "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": { + "table_name": "orders", + "access_type": "ref", + "possible_keys": ["i_o_orderdate", "i_o_custkey"], + "key": "i_o_custkey", + "key_length": "5", + "used_key_parts": ["o_custkey"], + "ref": ["dbt3_s001.customer.c_custkey"], + "rowid_filter": { + "range": { + "key": "i_o_orderdate", + "used_key_parts": ["o_orderDATE"] + }, + "rows": 89, + "selectivity_pct": 5.933333333 + }, + "rows": 11, + "filtered": 5.933333397, + "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-03-08'", + "first_match": "nation" + } + } + ] + } +} +delete from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-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_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9561.95 +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 +Customer#000000064 -646.64 +Customer#000000067 8166.59 +Customer#000000082 9468.34 +Customer#000000091 4643.14 +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#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +drop table t; +explain +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 +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 +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 +where o_orderDATE between "1992-01-09" and "1993-01-08"); +c_name c_acctbal +Customer#000000008 6819.74 +Customer#000000035 1228.24 +Customer#000000061 1536.24 +Customer#000000097 2164.48 +Customer#000000121 6428.32 +Customer#000000133 2314.67 +create table t as +select * from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU') +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1993-01-08"); +explain +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 +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 +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 +where o_orderDATE between "1992-01-09" and "1993-01-08"); +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 +where o_orderDATE between "1992-01-09" and "1993-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='PERU') +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1993-01-08"); +c_name c_acctbal +Customer#000000008 6819.74 +Customer#000000035 1228.24 +Customer#000000061 1536.24 +Customer#000000097 2164.48 +Customer#000000121 6428.32 +Customer#000000133 2314.67 +drop table t; +# Materialization +# =============== +explain +select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-01-09' and '1992-03-08'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 28 +1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 +2 MATERIALIZED orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 28 Using index condition; Using where +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'); +c_name c_acctbal +Customer#000000005 794.47 +Customer#000000013 3857.34 +Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000022 591.98 +Customer#000000023 3332.02 +Customer#000000025 7133.7 +Customer#000000032 3471.53 +Customer#000000035 1228.24 +Customer#000000037 -917.75 +Customer#000000038 6345.11 +Customer#000000040 1335.3 +Customer#000000052 5630.28 +Customer#000000056 6530.86 +Customer#000000065 8795.16 +Customer#000000076 5745.33 +Customer#000000091 4643.14 +Customer#000000098 -551.37 +Customer#000000109 -716.1 +Customer#000000115 7508.92 +Customer#000000116 8403.99 +Customer#000000118 3582.37 +Customer#000000136 -842.39 +Customer#000000140 9963.15 +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'); +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 +delete 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 + } + } + ] + } +} +delete 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_custkey in (select o_custkey from orders +where o_orderDATE between '1992-01-09' and '1992-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'); +c_name c_acctbal +Customer#000000005 794.47 +Customer#000000013 3857.34 +Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000022 591.98 +Customer#000000023 3332.02 +Customer#000000025 7133.7 +Customer#000000032 3471.53 +Customer#000000035 1228.24 +Customer#000000037 -917.75 +Customer#000000038 6345.11 +Customer#000000040 1335.3 +Customer#000000052 5630.28 +Customer#000000056 6530.86 +Customer#000000065 8795.16 +Customer#000000076 5745.33 +Customer#000000091 4643.14 +Customer#000000098 -551.37 +Customer#000000109 -716.1 +Customer#000000115 7508.92 +Customer#000000116 8403.99 +Customer#000000118 3582.37 +Customer#000000136 -842.39 +Customer#000000140 9963.15 +drop table t; +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'); +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 +create table t as +select * from customer where c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-06-09' and '1993-01-08'); +explain +delete 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 +delete from customer 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 +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-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 +drop table t; +# 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, + "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 + } + } + ] + } +} +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#000000056 6530.86 +Customer#000000118 3582.37 +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' + group by o_custkey having count(o_custkey) > 1); +explain +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); +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 +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); +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 + } + } + ] + } +} +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); +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 +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' + 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#000000056 6530.86 +Customer#000000118 3582.37 +drop table t; +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 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 +Customer#000000133 2314.67 +Customer#000000134 4608.9 +create table t as +select * 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); +explain +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); +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 +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); +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 +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 '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 +drop table t; +# Pullout PS +# ========== +prepare stmt from " +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 (select n_nationkey from nation +where n_name='PERU')); +"; +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +1729 12137.76 +2880 145761.99 +3142 16030.15 +5095 184583.99 +5121 150334.57 +5382 138423.03 +644 201268.06 +737 12984.85 +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 (select n_nationkey from nation +where n_name='PERU')); +execute stmt; +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +insert into orders select * from t; +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +1729 12137.76 +2880 145761.99 +3142 16030.15 +5095 184583.99 +5121 150334.57 +5382 138423.03 +644 201268.06 +737 12984.85 +create table r 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 (select n_nationkey from nation +where n_name='PERU')); +execute stmt; +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +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 +o_custkey in (select c_custkey from customer +where c_nationkey in (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +1729 12137.76 +2880 145761.99 +3142 16030.15 +5095 184583.99 +5121 150334.57 +5382 138423.03 +644 201268.06 +737 12984.85 +drop table t,r; +deallocate prepare stmt; +# FirstMatch PS +# ============= +prepare stmt from " +delete from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +"; +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9561.95 +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 +Customer#000000064 -646.64 +Customer#000000067 8166.59 +Customer#000000082 9468.34 +Customer#000000091 4643.14 +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#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +create table t as +select * from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +execute stmt; +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-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_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9561.95 +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 +Customer#000000064 -646.64 +Customer#000000067 8166.59 +Customer#000000082 9468.34 +Customer#000000091 4643.14 +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#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +create table r as +select * from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +execute stmt; +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +insert into customer select * from r; +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9561.95 +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 +Customer#000000064 -646.64 +Customer#000000067 8166.59 +Customer#000000082 9468.34 +Customer#000000091 4643.14 +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#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +drop table t,r; +deallocate prepare stmt; +# 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 ?; +"; +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#000000005 794.47 +Customer#000000013 3857.34 +Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000022 591.98 +Customer#000000023 3332.02 +Customer#000000025 7133.7 +Customer#000000032 3471.53 +Customer#000000035 1228.24 +Customer#000000037 -917.75 +Customer#000000038 6345.11 +Customer#000000040 1335.3 +Customer#000000052 5630.28 +Customer#000000056 6530.86 +Customer#000000065 8795.16 +Customer#000000076 5745.33 +Customer#000000091 4643.14 +Customer#000000098 -551.37 +Customer#000000109 -716.1 +Customer#000000115 7508.92 +Customer#000000116 8403.99 +Customer#000000118 3582.37 +Customer#000000136 -842.39 +Customer#000000140 9963.15 +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; +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'); +c_name c_acctbal +Customer#000000005 794.47 +Customer#000000022 591.98 +Customer#000000023 3332.02 +Customer#000000025 7133.7 +Customer#000000032 3471.53 +Customer#000000035 1228.24 +Customer#000000037 -917.75 +Customer#000000038 6345.11 +Customer#000000040 1335.3 +Customer#000000052 5630.28 +Customer#000000056 6530.86 +Customer#000000065 8795.16 +Customer#000000076 5745.33 +Customer#000000091 4643.14 +Customer#000000098 -551.37 +Customer#000000109 -716.1 +Customer#000000136 -842.39 +Customer#000000140 9963.15 +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'); +c_name c_acctbal +Customer#000000005 794.47 +Customer#000000013 3857.34 +Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000022 591.98 +Customer#000000023 3332.02 +Customer#000000025 7133.7 +Customer#000000032 3471.53 +Customer#000000035 1228.24 +Customer#000000037 -917.75 +Customer#000000038 6345.11 +Customer#000000040 1335.3 +Customer#000000052 5630.28 +Customer#000000056 6530.86 +Customer#000000065 8795.16 +Customer#000000076 5745.33 +Customer#000000091 4643.14 +Customer#000000098 -551.37 +Customer#000000109 -716.1 +Customer#000000115 7508.92 +Customer#000000116 8403.99 +Customer#000000118 3582.37 +Customer#000000136 -842.39 +Customer#000000140 9963.15 +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; +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'); +c_name c_acctbal +Customer#000000005 794.47 +Customer#000000013 3857.34 +Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000022 591.98 +Customer#000000023 3332.02 +Customer#000000025 7133.7 +Customer#000000040 1335.3 +Customer#000000052 5630.28 +Customer#000000056 6530.86 +Customer#000000065 8795.16 +Customer#000000076 5745.33 +Customer#000000091 4643.14 +Customer#000000098 -551.37 +Customer#000000109 -716.1 +Customer#000000115 7508.92 +Customer#000000116 8403.99 +Customer#000000118 3582.37 +Customer#000000140 9963.15 +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'); +c_name c_acctbal +Customer#000000005 794.47 +Customer#000000013 3857.34 +Customer#000000016 4681.03 +Customer#000000017 6.34 +Customer#000000022 591.98 +Customer#000000023 3332.02 +Customer#000000025 7133.7 +Customer#000000032 3471.53 +Customer#000000035 1228.24 +Customer#000000037 -917.75 +Customer#000000038 6345.11 +Customer#000000040 1335.3 +Customer#000000052 5630.28 +Customer#000000056 6530.86 +Customer#000000065 8795.16 +Customer#000000076 5745.33 +Customer#000000091 4643.14 +Customer#000000098 -551.37 +Customer#000000109 -716.1 +Customer#000000115 7508.92 +Customer#000000116 8403.99 +Customer#000000118 3582.37 +Customer#000000136 -842.39 +Customer#000000140 9963.15 +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 ?; +"; +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#000000056 6530.86 +Customer#000000118 3582.37 +set @a1=3500; +set @a2=4000; +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' + group by o_custkey having count(o_custkey) > 1) and c_acctbal between @a1 and @a2; +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' + group by o_custkey having count(o_custkey) > 1); +c_name c_acctbal +Customer#000000032 3471.53 +Customer#000000037 -917.75 +Customer#000000056 6530.86 +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' + 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#000000056 6530.86 +Customer#000000118 3582.37 +set @a3=-1000; +set @a4=3500; +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' + group by o_custkey having count(o_custkey) > 1) and c_acctbal between @a3 and @a4; +execute stmt using @a3, @a4; +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#000000056 6530.86 +Customer#000000118 3582.37 +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' + 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#000000056 6530.86 +Customer#000000118 3582.37 +drop table t,r; +deallocate prepare stmt; +# Pullout SP +# ========== +create procedure p(a1 int, a2 int) +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 (select n_nationkey from nation +where n_name='PERU')) and o_totalprice between a1 and a2; +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +1729 12137.76 +2880 145761.99 +3142 16030.15 +5095 184583.99 +5121 150334.57 +644 201268.06 +737 12984.85 +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 (select n_nationkey from nation +where n_name='PERU')) and o_totalprice between 150000 and 200000; +call p(150000, 200000); +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +1729 12137.76 +2880 145761.99 +3142 16030.15 +644 201268.06 +737 12984.85 +insert into orders select * from t; +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +1729 12137.76 +2880 145761.99 +3142 16030.15 +5095 184583.99 +5121 150334.57 +644 201268.06 +737 12984.85 +create table r 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 (select n_nationkey from nation +where n_name='PERU')) and o_totalprice between 180000 and 210000; +call p(180000, 210000); +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +1729 12137.76 +2880 145761.99 +3142 16030.15 +5121 150334.57 +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 +o_custkey in (select c_custkey from customer +where c_nationkey in (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +1729 12137.76 +2880 145761.99 +3142 16030.15 +5095 184583.99 +5121 150334.57 +644 201268.06 +737 12984.85 +drop table t,r; +drop procedure p; +# FirstMatch SP +# ============= +create procedure p(a int) +delete from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08') and c_acctbal > a; +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9561.95 +Customer#000000019 8914.71 +Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000037 -917.75 +Customer#000000047 274.58 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000064 -646.64 +Customer#000000067 8166.59 +Customer#000000082 9468.34 +Customer#000000091 4643.14 +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#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +create table t as +select * from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08') and c_acctbal > 4000; +call p(4000); +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000028 1007.18 +Customer#000000037 -917.75 +Customer#000000047 274.58 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000064 -646.64 +Customer#000000097 2164.48 +Customer#000000103 2757.45 +Customer#000000106 3288.42 +Customer#000000133 2314.67 +insert into customer select * from t; +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9561.95 +Customer#000000019 8914.71 +Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000037 -917.75 +Customer#000000047 274.58 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000064 -646.64 +Customer#000000067 8166.59 +Customer#000000082 9468.34 +Customer#000000091 4643.14 +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#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +create table r as +select * from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08') and c_acctbal > 2000; +call p(2000); +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000028 1007.18 +Customer#000000037 -917.75 +Customer#000000047 274.58 +Customer#000000061 1536.24 +Customer#000000064 -646.64 +insert into customer select * from r; +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9561.95 +Customer#000000019 8914.71 +Customer#000000025 7133.7 +Customer#000000028 1007.18 +Customer#000000037 -917.75 +Customer#000000047 274.58 +Customer#000000059 3458.6 +Customer#000000061 1536.24 +Customer#000000064 -646.64 +Customer#000000067 8166.59 +Customer#000000082 9468.34 +Customer#000000091 4643.14 +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#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +drop table t,r; +drop procedure p; +# 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'); +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#000000013 3857.34 +Customer#000000016 4681.03 +Customer#000000025 7133.7 +Customer#000000032 3471.53 +Customer#000000037 -917.75 +Customer#000000038 6345.11 +Customer#000000052 5630.28 +Customer#000000056 6530.86 +Customer#000000065 8795.16 +Customer#000000076 5745.33 +Customer#000000091 4643.14 +Customer#000000115 7508.92 +Customer#000000116 8403.99 +Customer#000000118 3582.37 +Customer#000000140 9963.15 +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'); +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'); +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'); +c_name c_acctbal +Customer#000000013 3857.34 +Customer#000000016 4681.03 +Customer#000000025 7133.7 +Customer#000000032 3471.53 +Customer#000000037 -917.75 +Customer#000000038 6345.11 +Customer#000000052 5630.28 +Customer#000000056 6530.86 +Customer#000000065 8795.16 +Customer#000000076 5745.33 +Customer#000000091 4643.14 +Customer#000000115 7508.92 +Customer#000000116 8403.99 +Customer#000000118 3582.37 +Customer#000000140 9963.15 +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'); +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'); +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'); +c_name c_acctbal +Customer#000000013 3857.34 +Customer#000000016 4681.03 +Customer#000000025 7133.7 +Customer#000000032 3471.53 +Customer#000000037 -917.75 +Customer#000000038 6345.11 +Customer#000000052 5630.28 +Customer#000000056 6530.86 +Customer#000000065 8795.16 +Customer#000000076 5745.33 +Customer#000000091 4643.14 +Customer#000000115 7508.92 +Customer#000000116 8403.99 +Customer#000000118 3582.37 +Customer#000000140 9963.15 +drop table t,r; +drop procedure p; +# Materialization SJM 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' + 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#000000056 6530.86 +Customer#000000118 3582.37 +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' + group by o_custkey having count(o_custkey) > 1); +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' + group by o_custkey having count(o_custkey) > 1); +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' + 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#000000056 6530.86 +Customer#000000118 3582.37 +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' + group by o_custkey having count(o_custkey) > 1); +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' + group by o_custkey having count(o_custkey) > 1); +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' + 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#000000056 6530.86 +Customer#000000118 3582.37 +drop table t,r; +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'); +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; +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 +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' + group by o_custkey having count(o_custkey) > 1); +c_name +Customer#000000013 +Customer#000000032 +Customer#000000037 +Customer#000000056 +Customer#000000118 +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' + group by o_custkey having count(o_custkey) > 1); +explain +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 +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' + group by o_custkey having count(o_custkey) > 1) returning c_name; +c_name +Customer#000000013 +Customer#000000032 +Customer#000000037 +Customer#000000056 +Customer#000000118 +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' + group by o_custkey having count(o_custkey) > 1); +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' + group by o_custkey having count(o_custkey) > 1); +c_name +Customer#000000013 +Customer#000000032 +Customer#000000037 +Customer#000000056 +Customer#000000118 +drop table t; +DROP DATABASE dbt3_s001; diff --git a/mysql-test/main/delete_single_to_multi.test b/mysql-test/main/delete_single_to_multi.test new file mode 100644 index 0000000..825da74 --- /dev/null +++ b/mysql-test/main/delete_single_to_multi.test @@ -0,0 +1,723 @@ +--disable_warnings +DROP DATABASE IF EXISTS dbt3_s001; +--enable_warnings + +CREATE DATABASE dbt3_s001; + +use dbt3_s001; + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/dbt3_s001.inc +--enable_warnings +--enable_result_log +--enable_query_log + +create index i_n_name on nation(n_name); +analyze table nation; + + +--echo # Pullout +--echo # ======= + +let $c1= + o_orderDATE between '1992-01-01' and '1992-06-30' and + o_custkey in (select c_custkey from customer + where c_nationkey in (select n_nationkey from nation + where n_name='PERU')); + +eval +explain +select o_orderkey, o_totalprice from orders where $c1; +eval +explain format=json +select o_orderkey, o_totalprice from orders where $c1; +--sorted_result +eval +select o_orderkey, o_totalprice from orders where $c1; +eval +create table t as +select * from orders where $c1; + +eval +explain +delete from orders where $c1; +eval +explain format=json +delete from orders where $c1; +eval +delete from orders where $c1; +eval +select o_orderkey, o_totalprice from orders where $c1; + + +insert into orders select * from t; +--sorted_result +eval +select o_orderkey, o_totalprice from orders where $c1; +drop table t; + + +let $c2= + (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')); + +eval +explain +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2; +--sorted_result +eval +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2; +eval +create table t as +select * from partsupp where $c2; + +eval +explain +delete from partsupp where $c2; +eval +delete from partsupp where $c2; +eval +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2; + +insert into partsupp select * from t; +--sorted_result +eval +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2; +drop table t; + + +let $c3= + 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')); +eval +explain +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3; +--sorted_result +eval +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3; +eval +create table t as +select * from partsupp where $c3; + +eval +explain +delete from partsupp where $c3; +eval +delete from partsupp where $c3; +eval +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3; + +insert into partsupp select * from t; +--sorted_result +eval +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3; +drop table t; + + +let $c4= + l_orderkey in (select o_orderkey from orders + where o_custkey in + (select c_custkey from customer + where c_nationkey in + (select n_nationkey from nation + where n_name='PERU')) + and + o_orderDATE between '1992-06-30' and '1992-12-31') + and + (l_partkey, l_suppkey) in + (select p_partkey, s_suppkey from part, supplier + where p_retailprice between 901 and 1000 and + s_nationkey in (select n_nationkey from nation + where n_name='PERU')); + +eval +explain +select l_orderkey, l_linenumber, l_tax from lineitem where $c4; +--sorted_result +eval +select l_orderkey, l_linenumber, l_tax from lineitem where $c4; +eval +create table t as +select * from lineitem where $c4; + +eval +explain +delete from lineitem where $c4; +eval +delete from lineitem where $c4; +eval +select l_orderkey, l_linenumber, l_tax from lineitem where $c4; + +insert into lineitem select * from t; +--sorted_result +eval +select l_orderkey, l_linenumber, l_tax from lineitem where $c4; +drop table t; + + +--echo # FirstMatch +--echo # ========== + +let $c5= + c_nationkey in (select n_nationkey from nation + where n_regionkey in (1,2)) + and + c_custkey in (select o_custkey from orders + where o_orderDATE between '1992-10-09' and '1993-03-08'); + +eval +explain +select c_name, c_acctbal from customer where $c5; +eval +explain format=json +select c_name, c_acctbal from customer where $c5; +--sorted_result +eval +select c_name, c_acctbal from customer where $c5; +eval +create table t as +select * from customer where $c5; + +eval +explain +delete from customer where $c5; +eval +explain format=json +delete from customer where $c5; +eval +delete from customer where $c5; +eval +select c_name, c_acctbal from customer where $c5; + +insert into customer select * from t; +--sorted_result +eval +select c_name, c_acctbal from customer where $c5; +drop table t; + + +let $c6= + c_nationkey in (select n_nationkey from nation where n_name='PERU') + and + c_custkey in (select o_custkey from orders + where o_orderDATE between "1992-01-09" and "1993-01-08"); + +eval +explain +select c_name, c_acctbal from customer where $c6; +--sorted_result +eval +select c_name, c_acctbal from customer where $c6; +eval +create table t as +select * from customer where $c6; + +eval +explain +delete from customer where $c6; +eval +delete from customer where $c6; +eval +select c_name, c_acctbal from customer where $c6; + +insert into customer select * from t; +--sorted_result +eval +select c_name, c_acctbal from customer where $c6; +drop table t; + + +--echo # Materialization +--echo # =============== + +let $c7= + c_custkey in (select o_custkey from orders + where o_orderDATE between '1992-01-09' and '1992-03-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; +eval +create table t as +select * from customer where $c7; + +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; + +insert into customer select * from t; +--sorted_result +eval +select c_name, c_acctbal from customer where $c7; +drop table t; + + +let $c8= + 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 $c8; +--sorted_result +eval +select c_name, c_acctbal from customer where $c8; +eval +create table t as +select * from customer where $c8; + +eval +explain +delete from customer where $c8; +eval +delete from customer where $c8; +eval +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 $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); + +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; +eval +create table t as +select * from customer where $c9; + +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; + +insert into customer select * from t; +--sorted_result +eval +select c_name, c_acctbal from customer where $c9; +drop table t; + + +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); + +eval +explain +select c_name, c_acctbal from customer where $c10; +--sorted_result +eval +select c_name, c_acctbal from customer where $c10; +eval +create table t as +select * from customer where $c10; + +eval +explain +delete from customer where $c10; +eval +delete from customer where $c10; +eval +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 $c10; +drop table t; + + +--echo # Pullout PS +--echo # ========== + +eval +prepare stmt from " +delete from orders where $c1; +"; + +--sorted_result +eval +select o_orderkey, o_totalprice from orders where $c1; +eval +create table t as +select * from orders where $c1; +execute stmt; +--sorted_result +eval +select o_orderkey, o_totalprice from orders where $c1; +insert into orders select * from t; +--sorted_result +eval +select o_orderkey, o_totalprice from orders where $c1; +eval +create table r as +select * from orders where $c1; +execute stmt; +--sorted_result +eval +select o_orderkey, o_totalprice from orders where $c1; +insert into orders select * from r; +--sorted_result +eval +select o_orderkey, o_totalprice from orders where $c1; +drop table t,r; + +deallocate prepare stmt; + + +--echo # FirstMatch PS +--echo # ============= + +eval +prepare stmt from " +delete from customer where $c5; +"; + +--sorted_result +eval +select c_name, c_acctbal from customer where $c5; +eval +create table t as +select * from customer where $c5; +execute stmt; +--sorted_result +eval +select c_name, c_acctbal from customer where $c5; +insert into customer select * from t; +--sorted_result +eval +select c_name, c_acctbal from customer where $c5; +eval +create table r as +select * from customer where $c5; +execute stmt; +--sorted_result +eval +select c_name, c_acctbal from customer where $c5; +insert into customer select * from r; +--sorted_result +eval +select c_name, c_acctbal from customer where $c5; +drop table t,r; + +deallocate prepare stmt; + + +--echo # Materialization PS +--echo # ================== + +eval +prepare stmt from " +delete from customer where $c7 and c_name like ?; +"; + +--sorted_result +eval +select c_name, c_acctbal from customer where $c7; +set @a1='Customer#%1_'; +eval +create table t as +select * from customer where $c7 and c_name like @a1; +execute stmt using @a1; +--sorted_result +eval +select c_name, c_acctbal from customer where $c7; +insert into customer select * from t; +--sorted_result +eval +select c_name, c_acctbal from customer where $c7; +set @a2='Customer#%3_'; +eval +create table r as +select * from customer where $c7 and c_name like @a2; +execute stmt using @a2; +--sorted_result +eval +select c_name, c_acctbal from customer where $c7; +insert into customer select * from r; +--sorted_result +eval +select c_name, c_acctbal from customer where $c7; +drop table t,r; + +deallocate prepare stmt; + + +--echo # Materialization SJM PS +--echo # ====================== + +eval +prepare stmt from " +delete from customer where $c7 and c_acctbal between ? and ?; +"; + +--sorted_result +eval +select c_name, c_acctbal from customer where $c9; +set @a1=3500; +set @a2=4000; +eval +create table t as +select * from customer where $c9 and c_acctbal between @a1 and @a2; +execute stmt using @a1, @a2; +--sorted_result +eval +select c_name, c_acctbal from customer where $c9; +insert into customer select * from t; +--sorted_result +eval +select c_name, c_acctbal from customer where $c9; +set @a3=-1000; +set @a4=3500; +eval +create table r as +select * from customer where $c9 and c_acctbal between @a3 and @a4; +execute stmt using @a3, @a4; +--sorted_result +eval +select c_name, c_acctbal from customer where $c9; +insert into customer select * from r; +--sorted_result +eval +select c_name, c_acctbal from customer where $c9; +drop table t,r; + +deallocate prepare stmt; + + +--echo # Pullout SP +--echo # ========== + +eval +create procedure p(a1 int, a2 int) +delete from orders where $c1 and o_totalprice between a1 and a2; + +--sorted_result +eval +select o_orderkey, o_totalprice from orders where $c1; +eval +create table t as +select * from orders where $c1 and o_totalprice between 150000 and 200000; +call p(150000, 200000); +--sorted_result +eval +select o_orderkey, o_totalprice from orders where $c1; +insert into orders select * from t; +--sorted_result +eval +select o_orderkey, o_totalprice from orders where $c1; +eval +create table r as +select * from orders where $c1 and o_totalprice between 180000 and 210000; +call p(180000, 210000); +--sorted_result +eval +select o_orderkey, o_totalprice from orders where $c1; +insert into orders select * from r; +--sorted_result +eval +select o_orderkey, o_totalprice from orders where $c1; +drop table t,r; + +drop procedure p; + + +--echo # FirstMatch SP +--echo # ============= + +eval +create procedure p(a int) +delete from customer where $c5 and c_acctbal > a; + +--sorted_result +eval +select c_name, c_acctbal from customer where $c5; +eval +create table t as +select * from customer where $c5 and c_acctbal > 4000; +call p(4000); +--sorted_result +eval +select c_name, c_acctbal from customer where $c5; +insert into customer select * from t; +--sorted_result +eval +select c_name, c_acctbal from customer where $c5; +eval +create table r as +select * from customer where $c5 and c_acctbal > 2000; +call p(2000); +--sorted_result +eval +select c_name, c_acctbal from customer where $c5; +insert into customer select * from r; +--sorted_result +eval +select c_name, c_acctbal from customer where $c5; +drop table t,r; + +drop procedure p; + + +--echo # Materialization SP +--echo # ================== + +eval +create procedure p() +delete from customer where $c7; + +--sorted_result +eval +select c_name, c_acctbal from customer where $c7; +eval +create table t as +select * from customer where $c7; +call p(); +--sorted_result +eval +select c_name, c_acctbal from customer where $c7; +insert into customer select * from t; +--sorted_result +eval +select c_name, c_acctbal from customer where $c7; +eval +create table r as +select * from customer where $c7; +call p(); +--sorted_result +eval +select c_name, c_acctbal from customer where $c7; +insert into customer select * from r; +--sorted_result +eval +select c_name, c_acctbal from customer where $c7; +drop table t,r; + +drop procedure p; + + +--echo # Materialization SJM SP +--echo # ====================== + +eval +create procedure p() +delete from customer where $c9; + +--sorted_result +eval +select c_name, c_acctbal from customer where $c9; +eval +create table t as +select * from customer where $c9; +call p(); +--sorted_result +eval +select c_name, c_acctbal from customer where $c9; +insert into customer select * from t; +--sorted_result +eval +select c_name, c_acctbal from customer where $c9; +eval +create table r as +select * from customer where $c9; +call p(); +--sorted_result +eval +select c_name, c_acctbal from customer where $c9; +insert into customer select * from r; +--sorted_result +eval +select c_name, c_acctbal from customer where $c9; +drop table t,r; + +drop procedure p; + +--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; +--sorted_result +eval +select c_name from customer where $c7; +drop table t; + +--sorted_result +eval +select c_name from customer where $c9; +eval +create table t as +select * from customer where $c9; +eval +explain +delete from customer where $c9 returning c_name; +--sorted_result +eval +delete from customer where $c9 returning c_name; +--sorted_result +eval +select c_name from customer where $c9; +insert into customer select * from t; +--sorted_result +eval +select c_name from customer where $c9; +drop table t; + +DROP DATABASE dbt3_s001; diff --git a/mysql-test/main/log_state.result b/mysql-test/main/log_state.result index 5e7aac8..1b1c737 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 -4 UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2) +10 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/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index 20b769b..5df9ced 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -240,14 +240,16 @@ Warnings: Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 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 t1 ALL NULL NULL NULL NULL 3 Using where -2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +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 t1 ALL NULL NULL NULL NULL 3 100.00 Using where -2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +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 @@ -271,8 +273,9 @@ Handler_read_key 5 Handler_read_rnd_next 8 # Status of testing query execution: Variable_name Value -Handler_read_key 4 -Handler_read_rnd_next 5 +Handler_read_key 5 +Handler_read_rnd 3 +Handler_read_rnd_next 12 Handler_update 3 DROP TABLE t1, t2; @@ -290,13 +293,13 @@ Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t1) FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3); 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 -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; FirstMatch(t1) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 # Status of EXPLAIN EXTENDED query @@ -984,14 +987,16 @@ Warnings: Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 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 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +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 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 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +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 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -1015,8 +1020,8 @@ Handler_read_key 7 Handler_read_rnd_next 8 # Status of testing query execution: Variable_name Value -Handler_read_key 4 -Handler_read_rnd_next 10 +Handler_read_key 7 +Handler_read_rnd_next 8 Handler_update 3 DROP TABLE t1, t2; @@ -1079,14 +1084,14 @@ Warnings: Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead EXPLAIN DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t1) FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1) # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -3074,14 +3079,14 @@ Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be 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 -2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 5 func 2 +1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 FirstMatch(t1) 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 -2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 5 func 2 100.00 +1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 100.00 FirstMatch(t1) 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 0555f72..e46bd80 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -3948,6 +3948,16 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { } }, { + "table": "t0", + "rowid_filters": [ + { + "key": "a", + "build_cost": 0.174715752, + "rows": 3 + } + ] + }, + { "selectivity_for_indexes": [ { "index_name": "a", @@ -4013,6 +4023,16 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { } }, { + "table": "t1", + "rowid_filters": [ + { + "key": "a", + "build_cost": 0.174715752, + "rows": 3 + } + ] + }, + { "selectivity_for_indexes": [ { "index_name": "a", diff --git a/mysql-test/main/update_single_to_multi.result b/mysql-test/main/update_single_to_multi.result new file mode 100644 index 0000000..687132f --- /dev/null +++ b/mysql-test/main/update_single_to_multi.result @@ -0,0 +1,2209 @@ +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; +Table Op Msg_type Msg_text +dbt3_s001.nation analyze status Engine-independent statistics collected +dbt3_s001.nation analyze status OK +# Pullout +# ======= +explain +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 (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 +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 +where c_nationkey in (select n_nationkey from nation +where n_name='PERU')); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "nation", + "access_type": "ref", + "possible_keys": ["PRIMARY", "i_n_name"], + "key": "i_n_name", + "key_length": "26", + "used_key_parts": ["n_name"], + "ref": ["const"], + "rows": 1, + "filtered": 100, + "index_condition": "nation.n_name = 'PERU'" + } + }, + { + "table": { + "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"], + "rows": 11, + "filtered": 100 + } + }, + { + "table": { + "table_name": "orders", + "access_type": "ref", + "possible_keys": ["i_o_orderdate", "i_o_custkey"], + "key": "i_o_custkey", + "key_length": "5", + "used_key_parts": ["o_custkey"], + "ref": ["dbt3_s001.customer.c_custkey"], + "rowid_filter": { + "range": { + "key": "i_o_orderdate", + "used_key_parts": ["o_orderDATE"] + }, + "rows": 108, + "selectivity_pct": 7.2 + }, + "rows": 11, + "filtered": 7.199999809, + "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" + } + } + ] + } +} +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +644 201268.06 +2880 145761.99 +3142 16030.15 +5382 138423.03 +5095 184583.99 +737 12984.85 +1729 12137.76 +5121 150334.57 +explain +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 (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 +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 +where c_nationkey in (select n_nationkey from nation +where n_name='PERU')); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "nation", + "access_type": "ref", + "possible_keys": ["PRIMARY", "i_n_name"], + "key": "i_n_name", + "key_length": "26", + "used_key_parts": ["n_name"], + "ref": ["const"], + "rows": 1, + "filtered": 100, + "index_condition": "nation.n_name = 'PERU'" + } + }, + { + "table": { + "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"], + "rows": 11, + "filtered": 100 + } + }, + { + "table": { + "table_name": "orders", + "access_type": "ref", + "possible_keys": ["i_o_orderdate", "i_o_custkey"], + "key": "i_o_custkey", + "key_length": "5", + "used_key_parts": ["o_custkey"], + "ref": ["dbt3_s001.customer.c_custkey"], + "rowid_filter": { + "range": { + "key": "i_o_orderdate", + "used_key_parts": ["o_orderDATE"] + }, + "rows": 108, + "selectivity_pct": 7.2 + }, + "rows": 11, + "filtered": 7.199999809, + "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" + } + } + ] + } +} +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 (select n_nationkey from nation +where n_name='PERU')); +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +644 201218.06 +2880 145711.99 +3142 15980.15 +5382 138373.03 +5095 184533.99 +737 12934.85 +1729 12087.76 +5121 150284.57 +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 (select n_nationkey from nation +where n_name='PERU')); +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +644 201268.06 +2880 145761.99 +3142 16030.15 +5382 138423.03 +5095 184583.99 +737 12984.85 +1729 12137.76 +5121 150334.57 +explain +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')); +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 +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 +5 8 50.52 +7 8 763.98 +explain +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 +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 +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 +s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +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 446.37 +6 1 644.13 +8 1 959.34 +1 8 359.84 +3 8 647.4 +5 8 52.52 +7 8 765.98 +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 +s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +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 +5 8 50.52 +7 8 763.98 +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 +ps_suppkey in (select s_suppkey from supplier +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 +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 +5 8 50.52 +7 8 763.98 +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 +ps_suppkey in (select s_suppkey from supplier +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 +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 +where s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +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 454.37 +6 1 652.13 +8 1 967.34 +1 8 367.84 +3 8 655.4 +5 8 60.52 +7 8 773.98 +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 +where s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +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 +5 8 50.52 +7 8 763.98 +explain +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 +where c_nationkey in +(select n_nationkey from nation +where n_name='PERU')) +and +o_orderDATE between '1992-06-30' and '1992-12-31') +and +(l_partkey, l_suppkey) in +(select p_partkey, s_suppkey from part, supplier +where p_retailprice between 901 and 1000 and +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 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 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 +1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 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 +where c_nationkey in +(select n_nationkey from nation +where n_name='PERU')) +and +o_orderDATE between '1992-06-30' and '1992-12-31') +and +(l_partkey, l_suppkey) in +(select p_partkey, s_suppkey from part, supplier +where p_retailprice between 901 and 1000 and +s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +l_orderkey l_linenumber l_tax +4996 1 0.01 +933 1 0.04 +2500 2 0.02 +2500 4 0.02 +explain +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 +where c_nationkey in +(select n_nationkey from nation +where n_name='PERU')) +and +o_orderDATE between '1992-06-30' and '1992-12-31') +and +(l_partkey, l_suppkey) in +(select p_partkey, s_suppkey from part, supplier +where p_retailprice between 901 and 1000 and +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 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 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 +1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 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 +where c_nationkey in +(select n_nationkey from nation +where n_name='PERU')) +and +o_orderDATE between '1992-06-30' and '1992-12-31') +and +(l_partkey, l_suppkey) in +(select p_partkey, s_suppkey from part, supplier +where p_retailprice between 901 and 1000 and +s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +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 +where c_nationkey in +(select n_nationkey from nation +where n_name='PERU')) +and +o_orderDATE between '1992-06-30' and '1992-12-31') +and +(l_partkey, l_suppkey) in +(select p_partkey, s_suppkey from part, supplier +where p_retailprice between 901 and 1000 and +s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +l_orderkey l_linenumber l_tax +4996 1 0.02 +933 1 0.05 +2500 2 0.03 +2500 4 0.03 +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 +where c_nationkey in +(select n_nationkey from nation +where n_name='PERU')) +and +o_orderDATE between '1992-06-30' and '1992-12-31') +and +(l_partkey, l_suppkey) in +(select p_partkey, s_suppkey from part, supplier +where p_retailprice between 901 and 1000 and +s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +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 +where c_nationkey in +(select n_nationkey from nation +where n_name='PERU')) +and +o_orderDATE between '1992-06-30' and '1992-12-31') +and +(l_partkey, l_suppkey) in +(select p_partkey, s_suppkey from part, supplier +where p_retailprice between 901 and 1000 and +s_nationkey in (select n_nationkey from nation +where n_name='PERU')); +l_orderkey l_linenumber l_tax +4996 1 0.01 +933 1 0.04 +2500 2 0.02 +2500 4 0.02 +# FirstMatch +# ========== +explain +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-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 (6%) Using where; FirstMatch(nation); Using rowid filter +explain format=json +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "customer", + "access_type": "ALL", + "possible_keys": ["PRIMARY", "i_c_nationkey"], + "rows": 150, + "filtered": 100, + "attached_condition": "customer.c_nationkey is not null" + } + }, + { + "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": { + "table_name": "orders", + "access_type": "ref", + "possible_keys": ["i_o_orderdate", "i_o_custkey"], + "key": "i_o_custkey", + "key_length": "5", + "used_key_parts": ["o_custkey"], + "ref": ["dbt3_s001.customer.c_custkey"], + "rowid_filter": { + "range": { + "key": "i_o_orderdate", + "used_key_parts": ["o_orderDATE"] + }, + "rows": 89, + "selectivity_pct": 5.933333333 + }, + "rows": 11, + "filtered": 5.933333397, + "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-03-08'", + "first_match": "nation" + } + } + ] + } +} +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9561.95 +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 +Customer#000000064 -646.64 +Customer#000000067 8166.59 +Customer#000000082 9468.34 +Customer#000000091 4643.14 +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#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +explain +update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-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 (6%) Using where; FirstMatch(nation); Using rowid filter +explain format=json +update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "customer", + "access_type": "ALL", + "possible_keys": ["PRIMARY", "i_c_nationkey"], + "rows": 150, + "filtered": 100, + "attached_condition": "customer.c_nationkey is not null" + } + }, + { + "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": { + "table_name": "orders", + "access_type": "ref", + "possible_keys": ["i_o_orderdate", "i_o_custkey"], + "key": "i_o_custkey", + "key_length": "5", + "used_key_parts": ["o_custkey"], + "ref": ["dbt3_s001.customer.c_custkey"], + "rowid_filter": { + "range": { + "key": "i_o_orderdate", + "used_key_parts": ["o_orderDATE"] + }, + "rows": 89, + "selectivity_pct": 5.933333333 + }, + "rows": 11, + "filtered": 5.933333397, + "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-03-08'", + "first_match": "nation" + } + } + ] + } +} +update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9571.95 +Customer#000000019 8924.71 +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#000000082 9478.34 +Customer#000000091 4653.14 +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#000000127 9290.71 +Customer#000000130 5083.58 +Customer#000000133 2324.67 +Customer#000000139 7907.78 +update customer set c_acctbal = c_acctbal-10 where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9561.95 +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 +Customer#000000064 -646.64 +Customer#000000067 8166.59 +Customer#000000082 9468.34 +Customer#000000091 4643.14 +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#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +explain +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 +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 +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 +where o_orderDATE between "1992-01-09" and "1993-01-08"); +c_name c_acctbal +Customer#000000008 6819.74 +Customer#000000035 1228.24 +Customer#000000061 1536.24 +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='PERU') +and +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 +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 +where o_orderDATE between "1992-01-09" and "1993-01-08"); +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 +where o_orderDATE between "1992-01-09" and "1993-01-08"); +c_name c_acctbal +Customer#000000008 6839.74 +Customer#000000035 1248.24 +Customer#000000061 1556.24 +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='PERU') +and +c_custkey in (select o_custkey from orders +where o_orderDATE between "1992-01-09" and "1993-01-08"); +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 +where o_orderDATE between "1992-01-09" and "1993-01-08"); +c_name c_acctbal +Customer#000000008 6819.74 +Customer#000000035 1228.24 +Customer#000000061 1536.24 +Customer#000000097 2164.48 +Customer#000000121 6428.32 +Customer#000000133 2314.67 +# Materialization +# =============== +explain +select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-01-09' and '1992-03-08'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 28 +1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 +2 MATERIALIZED orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 28 Using index condition; Using where +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'); +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 +update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-01-09' and '1992-03-08'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 28 +1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 +2 MATERIALIZED orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 28 Using index condition; Using where +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'); +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 + } + } + ] + } +} +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'); +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 +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, + "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 + } + } + ] + } +} +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 +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": { + "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 + } + } + ] + } +} +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 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 +Customer#000000133 2314.67 +Customer#000000134 4608.9 +explain +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); +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 +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); +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 9560.95 +Customer#000000016 4680.03 +Customer#000000037 -918.75 +Customer#000000046 5743.59 +Customer#000000091 4642.14 +Customer#000000103 2756.45 +Customer#000000118 3581.37 +Customer#000000133 2313.67 +Customer#000000134 4607.9 +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); +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 +# Pullout PS +# ========== +prepare stmt from " +update orders set o_totalprice = o_totalprice+? where o_orderDATE between '1992-01-01' and '1992-06-30' and +o_custkey in (select c_custkey from customer +where c_nationkey in (select n_nationkey from nation +where n_name='PERU')); +"; +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +644 201268.06 +2880 145761.99 +3142 16030.15 +5382 138423.03 +5095 184583.99 +737 12984.85 +1729 12137.76 +5121 150334.57 +set @a1=-20; +execute stmt using @a1; +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +644 201248.06 +2880 145741.99 +3142 16010.15 +5382 138403.03 +5095 184563.99 +737 12964.85 +1729 12117.76 +5121 150314.57 +set @a2=-10; +execute stmt using @a2; +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +644 201238.06 +2880 145731.99 +3142 16000.15 +5382 138393.03 +5095 184553.99 +737 12954.85 +1729 12107.76 +5121 150304.57 +execute stmt using -(@a1+@a2); +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +644 201268.06 +2880 145761.99 +3142 16030.15 +5382 138423.03 +5095 184583.99 +737 12984.85 +1729 12137.76 +5121 150334.57 +deallocate prepare stmt; +# FirstMatch PS +# ============= +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)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +"; +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9561.95 +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 +Customer#000000064 -646.64 +Customer#000000067 8166.59 +Customer#000000082 9468.34 +Customer#000000091 4643.14 +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#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +set @a1=15; +execute stmt using @a1; +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9576.95 +Customer#000000019 8929.71 +Customer#000000022 606.98 +Customer#000000025 7148.7 +Customer#000000028 1022.18 +Customer#000000037 -902.75 +Customer#000000040 1350.3 +Customer#000000047 289.58 +Customer#000000059 3473.6 +Customer#000000061 1551.24 +Customer#000000064 -631.64 +Customer#000000067 8181.59 +Customer#000000082 9483.34 +Customer#000000091 4658.14 +Customer#000000094 5515.11 +Customer#000000097 2179.48 +Customer#000000101 7485.96 +Customer#000000103 2772.45 +Customer#000000106 3303.42 +Customer#000000115 7523.92 +Customer#000000121 6443.32 +Customer#000000122 7880.46 +Customer#000000127 9295.71 +Customer#000000130 5088.58 +Customer#000000133 2329.67 +Customer#000000139 7912.78 +set @a2=5; +execute stmt using @a2; +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9581.95 +Customer#000000019 8934.71 +Customer#000000022 611.98 +Customer#000000025 7153.7 +Customer#000000028 1027.1799999999998 +Customer#000000037 -897.75 +Customer#000000040 1355.3 +Customer#000000047 294.58 +Customer#000000059 3478.6 +Customer#000000061 1556.24 +Customer#000000064 -626.64 +Customer#000000067 8186.59 +Customer#000000082 9488.34 +Customer#000000091 4663.14 +Customer#000000094 5520.11 +Customer#000000097 2184.48 +Customer#000000101 7490.96 +Customer#000000103 2777.45 +Customer#000000106 3308.42 +Customer#000000115 7528.92 +Customer#000000121 6448.32 +Customer#000000122 7885.46 +Customer#000000127 9300.71 +Customer#000000130 5093.58 +Customer#000000133 2334.67 +Customer#000000139 7917.78 +execute stmt using -(@a1+@a2); +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9561.95 +Customer#000000019 8914.71 +Customer#000000022 591.98 +Customer#000000025 7133.7 +Customer#000000028 1007.1799999999998 +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#000000082 9468.34 +Customer#000000091 4643.14 +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#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +deallocate prepare stmt; +# 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'); +"; +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 +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'); +c_name c_acctbal +Customer#000000025 7140.7 +Customer#000000013 3864.34 +Customer#000000065 8802.16 +Customer#000000032 3478.53 +Customer#000000023 3339.02 +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#000000115 7515.92 +Customer#000000140 9970.15 +Customer#000000017 13.34 +Customer#000000052 5637.28 +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'); +c_name c_acctbal +Customer#000000025 7143.7 +Customer#000000013 3867.34 +Customer#000000065 8805.16 +Customer#000000032 3481.53 +Customer#000000023 3342.02 +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#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#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 +deallocate prepare stmt; +# Materialization SJM 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' + 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 +set @a1=-2; +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' + group by o_custkey having count(o_custkey) > 1); +c_name c_acctbal +Customer#000000013 3855.34 +Customer#000000032 3469.53 +Customer#000000037 -919.75 +Customer#000000118 3580.37 +Customer#000000056 6528.86 +set @a2=-1; +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' + group by o_custkey having count(o_custkey) > 1); +c_name c_acctbal +Customer#000000013 3854.34 +Customer#000000032 3468.53 +Customer#000000037 -920.75 +Customer#000000118 3579.37 +Customer#000000056 6527.86 +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' + 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 +deallocate prepare stmt; +# Pullout SP +# ========== +create procedure p(d int) +update orders set o_totalprice = o_totalprice+d where o_orderDATE between '1992-01-01' and '1992-06-30' and +o_custkey in (select c_custkey from customer +where c_nationkey in (select n_nationkey from nation +where n_name='PERU')); +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +644 201268.06 +2880 145761.99 +3142 16030.15 +5382 138423.03 +5095 184583.99 +737 12984.85 +1729 12137.76 +5121 150334.57 +call p(-10); +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +644 201258.06 +2880 145751.99 +3142 16020.15 +5382 138413.03 +5095 184573.99 +737 12974.85 +1729 12127.76 +5121 150324.57 +call p(-20); +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +644 201238.06 +2880 145731.99 +3142 16000.15 +5382 138393.03 +5095 184553.99 +737 12954.85 +1729 12107.76 +5121 150304.57 +call p(10+20); +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 (select n_nationkey from nation +where n_name='PERU')); +o_orderkey o_totalprice +644 201268.06 +2880 145761.99 +3142 16030.15 +5382 138423.03 +5095 184583.99 +737 12984.85 +1729 12137.76 +5121 150334.57 +drop procedure p; +# FirstMatch SP +# ============= +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)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9561.95 +Customer#000000019 8914.71 +Customer#000000022 591.98 +Customer#000000025 7133.7 +Customer#000000028 1007.1799999999998 +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#000000082 9468.34 +Customer#000000091 4643.14 +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#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +call p(5); +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9566.95 +Customer#000000019 8919.71 +Customer#000000022 596.98 +Customer#000000025 7138.7 +Customer#000000028 1012.1799999999998 +Customer#000000037 -912.75 +Customer#000000040 1340.3 +Customer#000000047 279.58 +Customer#000000059 3463.6 +Customer#000000061 1541.24 +Customer#000000064 -641.64 +Customer#000000067 8171.59 +Customer#000000082 9473.34 +Customer#000000091 4648.14 +Customer#000000094 5505.11 +Customer#000000097 2169.48 +Customer#000000101 7475.96 +Customer#000000103 2762.45 +Customer#000000106 3293.42 +Customer#000000115 7513.92 +Customer#000000121 6433.32 +Customer#000000122 7870.46 +Customer#000000127 9285.71 +Customer#000000130 5078.58 +Customer#000000133 2319.67 +Customer#000000139 7902.78 +call p(15); +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9581.95 +Customer#000000019 8934.71 +Customer#000000022 611.98 +Customer#000000025 7153.7 +Customer#000000028 1027.1799999999998 +Customer#000000037 -897.75 +Customer#000000040 1355.3 +Customer#000000047 294.58 +Customer#000000059 3478.6 +Customer#000000061 1556.24 +Customer#000000064 -626.64 +Customer#000000067 8186.59 +Customer#000000082 9488.34 +Customer#000000091 4663.14 +Customer#000000094 5520.11 +Customer#000000097 2184.48 +Customer#000000101 7490.96 +Customer#000000103 2777.45 +Customer#000000106 3308.42 +Customer#000000115 7528.92 +Customer#000000121 6448.32 +Customer#000000122 7885.46 +Customer#000000127 9300.71 +Customer#000000130 5093.58 +Customer#000000133 2334.67 +Customer#000000139 7917.78 +call p(-(5+15)); +select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation +where n_regionkey in (1,2)) +and +c_custkey in (select o_custkey from orders +where o_orderDATE between '1992-10-09' and '1993-03-08'); +c_name c_acctbal +Customer#000000007 9561.95 +Customer#000000019 8914.71 +Customer#000000022 591.98 +Customer#000000025 7133.7 +Customer#000000028 1007.1799999999998 +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#000000082 9468.34 +Customer#000000091 4643.14 +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#000000127 9280.71 +Customer#000000130 5073.58 +Customer#000000133 2314.67 +Customer#000000139 7897.78 +drop procedure p; +# 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'); +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 +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'); +c_name c_acctbal +Customer#000000025 7136.7 +Customer#000000013 3860.34 +Customer#000000065 8798.16 +Customer#000000032 3474.53 +Customer#000000023 3335.02 +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#000000115 7511.92 +Customer#000000140 9966.15 +Customer#000000017 9.34 +Customer#000000052 5633.28 +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'); +c_name c_acctbal +Customer#000000025 7143.7 +Customer#000000013 3867.34 +Customer#000000065 8805.16 +Customer#000000032 3481.53 +Customer#000000023 3342.02 +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#000000052 5640.28 +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'); +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 +drop procedure p; +# Materialization SJM 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' + 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 +call p(-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 3856.34 +Customer#000000032 3470.53 +Customer#000000037 -918.75 +Customer#000000118 3581.37 +Customer#000000056 6529.86 +call p(-2); +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 3854.34 +Customer#000000032 3468.53 +Customer#000000037 -920.75 +Customer#000000118 3579.37 +Customer#000000056 6527.86 +call p(1+2); +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 +drop procedure p; +DROP DATABASE dbt3_s001; diff --git a/mysql-test/main/update_single_to_multi.test b/mysql-test/main/update_single_to_multi.test new file mode 100644 index 0000000..f018365 --- /dev/null +++ b/mysql-test/main/update_single_to_multi.test @@ -0,0 +1,511 @@ +--disable_warnings +DROP DATABASE IF EXISTS dbt3_s001; +--enable_warnings + +CREATE DATABASE dbt3_s001; + +use dbt3_s001; + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/dbt3_s001.inc +--enable_warnings +--enable_result_log +--enable_query_log + +create index i_n_name on nation(n_name); +analyze table nation; + + +--echo # Pullout +--echo # ======= + +let $c1= + o_orderDATE between '1992-01-01' and '1992-06-30' and + o_custkey in (select c_custkey from customer + where c_nationkey in (select n_nationkey from nation + where n_name='PERU')); + +eval +explain +select o_orderkey, o_totalprice from orders where $c1; +eval +explain format=json +select o_orderkey, o_totalprice from orders where $c1; +eval +select o_orderkey, o_totalprice from orders where $c1; + +eval +explain +update orders set o_totalprice = o_totalprice-50 where $c1; +eval +explain format=json +update orders set o_totalprice = o_totalprice-50 where $c1; +eval +update orders set o_totalprice = o_totalprice-50 where $c1; +eval +select o_orderkey, o_totalprice from orders where $c1; + +eval +update orders set o_totalprice= o_totalprice+50 where $c1; +eval +select o_orderkey, o_totalprice from orders where $c1; + + +let $c2= + (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')); + +eval +explain +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2; +eval +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2; + +eval +explain +update partsupp set ps_supplycost = ps_supplycost+2 where $c2; +eval +update partsupp set ps_supplycost = ps_supplycost+2 where $c2; +eval +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2; + +eval +update partsupp set ps_supplycost = ps_supplycost-2 where $c2; +eval +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2; + + +let $c3= + 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')); +eval +explain +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3; +eval +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3; + +eval +explain +update partsupp set ps_supplycost = ps_supplycost+10 where $c3; +eval +update partsupp set ps_supplycost = ps_supplycost+10 where $c3; +eval +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3; + +eval +update partsupp set ps_supplycost = ps_supplycost-10 where $c3; +eval +select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3; + + +let $c4= + l_orderkey in (select o_orderkey from orders + where o_custkey in + (select c_custkey from customer + where c_nationkey in + (select n_nationkey from nation + where n_name='PERU')) + and + o_orderDATE between '1992-06-30' and '1992-12-31') + and + (l_partkey, l_suppkey) in + (select p_partkey, s_suppkey from part, supplier + where p_retailprice between 901 and 1000 and + s_nationkey in (select n_nationkey from nation + where n_name='PERU')); + +eval +explain +select l_orderkey, l_linenumber, l_tax from lineitem where $c4; +eval +select l_orderkey, l_linenumber, l_tax from lineitem where $c4; + +eval +explain +update lineitem set l_tax = (l_tax*100+1)/100 where $c4; +eval +update lineitem set l_tax = (l_tax*100+1)/100 where $c4; +eval +select l_orderkey, l_linenumber, l_tax from lineitem where $c4; + +eval +update lineitem set l_tax = (l_tax*100-1)/100 where $c4; +eval +select l_orderkey, l_linenumber, l_tax from lineitem where $c4; + + +--echo # FirstMatch +--echo # ========== + +let $c5= + c_nationkey in (select n_nationkey from nation + where n_regionkey in (1,2)) + and + c_custkey in (select o_custkey from orders + where o_orderDATE between '1992-10-09' and '1993-03-08'); + +eval +explain +select c_name, c_acctbal from customer where $c5; +eval +explain format=json +select c_name, c_acctbal from customer where $c5; +eval +select c_name, c_acctbal from customer where $c5; + +eval +explain +update customer set c_acctbal = c_acctbal+10 where $c5; +eval +explain format=json +update customer set c_acctbal = c_acctbal+10 where $c5; +eval +update customer set c_acctbal = c_acctbal+10 where $c5; +eval +select c_name, c_acctbal from customer where $c5; + +eval +update customer set c_acctbal = c_acctbal-10 where $c5; +eval +select c_name, c_acctbal from customer where $c5; + + +let $c6= + c_nationkey in (select n_nationkey from nation where n_name='PERU') + and + c_custkey in (select o_custkey from orders + where o_orderDATE between "1992-01-09" and "1993-01-08"); + +eval +explain +select c_name, c_acctbal from customer where $c6; +eval +select c_name, c_acctbal from customer where $c6; + +eval +explain +update customer set c_acctbal = c_acctbal+20 where $c6; +eval +update customer set c_acctbal = c_acctbal+20 where $c6; +eval +select c_name, c_acctbal from customer where $c6; + +eval +update customer set c_acctbal = c_acctbal-20 where $c6; +eval +select c_name, c_acctbal from customer where $c6; + + +--echo # Materialization +--echo # =============== + +let $c7= + c_custkey in (select o_custkey from orders + where o_orderDATE between '1992-01-09' and '1992-03-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; +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; +eval +select c_name, c_acctbal from customer where $c7; + +eval +update customer set c_acctbal = c_acctbal-5 where $c7; +eval +select c_name, c_acctbal from customer where $c7; + + +let $c8= + 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 $c8; +eval +select c_name, c_acctbal from customer where $c8; + +eval +explain +update customer set c_acctbal = c_acctbal+1 where $c8; +eval +update customer set c_acctbal = c_acctbal+1 where $c8; +eval +select c_name, c_acctbal from customer where $c8; + +eval +update customer set c_acctbal = c_acctbal-1 where $c8; +eval +select c_name, c_acctbal from customer where $c8; + + +--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); + +eval +explain +select c_name, c_acctbal from customer where $c9; +eval +explain format=json +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-5 where $c9; +eval +explain format=json +update customer set c_acctbal = c_acctbal-5 where $c9; +eval +update customer set c_acctbal = c_acctbal-5 where $c9; +eval +select c_name, c_acctbal from customer where $c9; + +eval +update customer set c_acctbal = c_acctbal+5 where $c9; +eval +select c_name, c_acctbal from customer where $c9; + + +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); + +eval +explain +select c_name, c_acctbal from customer where $c10; +eval +select c_name, c_acctbal from customer where $c10; + +eval +explain +update customer set c_acctbal = c_acctbal-1 where $c10; +eval +update customer set c_acctbal = c_acctbal-1 where $c10; +eval +select c_name, c_acctbal from customer where $c10; + +eval +update customer set c_acctbal = c_acctbal+1 where $c10; +eval +select c_name, c_acctbal from customer where $c10; + + +--echo # Pullout PS +--echo # ========== + +eval +prepare stmt from " +update orders set o_totalprice = o_totalprice+? where $c1; +"; + +eval +select o_orderkey, o_totalprice from orders where $c1; +set @a1=-20; +execute stmt using @a1; +eval +select o_orderkey, o_totalprice from orders where $c1; +set @a2=-10; +execute stmt using @a2; +eval +select o_orderkey, o_totalprice from orders where $c1; +execute stmt using -(@a1+@a2); +eval +select o_orderkey, o_totalprice from orders where $c1; + +deallocate prepare stmt; + + +--echo # FirstMatch PS +--echo # ============= + +eval +prepare stmt from " +update customer set c_acctbal = c_acctbal+? where $c5; +"; + +eval +select c_name, c_acctbal from customer where $c5; +set @a1=15; +execute stmt using @a1; +eval +select c_name, c_acctbal from customer where $c5; +set @a2=5; +execute stmt using @a2; +eval +select c_name, c_acctbal from customer where $c5; +execute stmt using -(@a1+@a2); +eval +select c_name, c_acctbal from customer where $c5; + +deallocate prepare stmt; + + +--echo # Materialization PS +--echo # ================== + +eval +prepare stmt from " +update customer set c_acctbal = c_acctbal+? where $c7; +"; + +eval +select c_name, c_acctbal from customer where $c7; +set @a1=7; +execute stmt using @a1; +eval +select c_name, c_acctbal from customer where $c7; +set @a2=3; +execute stmt using @a2; +eval +select c_name, c_acctbal from customer where $c7; +execute stmt using -(@a1+@a2); +eval +select c_name, c_acctbal from customer where $c7; + +deallocate prepare stmt; + + +--echo # Materialization SJM PS +--echo # ====================== + +eval +prepare stmt from " +update customer set c_acctbal = c_acctbal+? where $c9; +"; + +eval +select c_name, c_acctbal from customer where $c9; +set @a1=-2; +execute stmt using @a1; +eval +select c_name, c_acctbal from customer where $c9; +set @a2=-1; +execute stmt using @a2; +eval +select c_name, c_acctbal from customer where $c9; +execute stmt using -(@a1+@a2); +eval +select c_name, c_acctbal from customer where $c9; + +deallocate prepare stmt; + + +--echo # Pullout SP +--echo # ========== + +eval +create procedure p(d int) +update orders set o_totalprice = o_totalprice+d where $c1; + +eval +select o_orderkey, o_totalprice from orders where $c1; +call p(-10); +eval +select o_orderkey, o_totalprice from orders where $c1; +call p(-20); +eval +select o_orderkey, o_totalprice from orders where $c1; +call p(10+20); +eval +select o_orderkey, o_totalprice from orders where $c1; + +drop procedure p; + + +--echo # FirstMatch SP +--echo # ============= + +eval +create procedure p(d int) +update customer set c_acctbal = c_acctbal+d where $c5; + +eval +select c_name, c_acctbal from customer where $c5; +call p(5); +eval +select c_name, c_acctbal from customer where $c5; +call p(15); +eval +select c_name, c_acctbal from customer where $c5; +call p(-(5+15)); +eval +select c_name, c_acctbal from customer where $c5; + +drop procedure p; + + +--echo # Materialization SP +--echo # ================== + +eval +create procedure p(d int) +update customer set c_acctbal = c_acctbal+d where $c7; + +eval +select c_name, c_acctbal from customer where $c7; +call p(3); +eval +select c_name, c_acctbal from customer where $c7; +call p(7); +eval +select c_name, c_acctbal from customer where $c7; +call p(-(3+7)); +eval +select c_name, c_acctbal from customer where $c7; + +drop procedure p; + + +--echo # Materialization SJM SP +--echo # ====================== + +eval +create procedure p(d int) +update customer set c_acctbal = c_acctbal+d where $c9; + +eval +select c_name, c_acctbal from customer where $c9; +call p(-1); +eval +select c_name, c_acctbal from customer where $c9; +call p(-2); +eval +select c_name, c_acctbal from customer where $c9; +call p(1+2); +eval +select c_name, c_acctbal from customer where $c9; + +drop procedure p; + + +DROP DATABASE dbt3_s001; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index fa338f0..4379378 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -675,9 +675,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join) 3. Subquery does not have GROUP BY or ORDER BY 4. Subquery does not use aggregate functions or HAVING 5. Subquery predicate is at the AND-top-level of ON/WHERE clause - 6. We are not in a subquery of a single table UPDATE/DELETE that - doesn't have a JOIN (TODO: We should handle this at some - point by switching to multi-table UPDATE/DELETE) + 6. We are not in a top level subquery of a single-table DELETE + with RETURNING 7. We're not in a table-less subquery like "SELECT 1" 8. No execution method was already chosen (by a prepared statement) 9. Parent select is not a table-less select @@ -692,9 +691,9 @@ int check_and_do_in_subquery_rewrites(JOIN *join) !select_lex->group_list.elements && !join->order && // 3 !join->having && !select_lex->with_sum_func && // 4 in_subs->emb_on_expr_nest && // 5 - select_lex->outer_select()->join && // 6 - (!thd->lex->m_sql_cmd || - thd->lex->m_sql_cmd->sql_command_code() == SQLCOM_UPDATE_MULTI) && + !(thd->lex->sql_command == SQLCOM_DELETE && // 6 + thd->lex->has_returning() && // 6 + !select_lex->outer_select()->outer_select()) && // 6 parent_unit->first_select()->leaf_tables.elements && // 7 !in_subs->has_strategy() && // 8 select_lex->outer_select()->table_list.first && // 9 @@ -7260,3 +7259,28 @@ bool TABLE_LIST::is_sjm_scan_table() { return is_active_sjm() && sj_mat_info->is_sj_scan; } + + +/** + @brief Check whether this subselect can be lifted to the top level + @returns true if the test is positive, false otherwise +*/ + +bool SELECT_LEX::is_sj_subselect_lifted_to_top() +{ + st_select_lex *sl= this; + st_select_lex *outer_sl= outer_select(); + for ( ; outer_sl; sl= outer_sl, outer_sl= outer_sl->outer_select()) + { + List_iterator_fast<Item_in_subselect> it(outer_sl->sj_subselects); + Item_in_subselect *in_subs; + while ((in_subs= it++)) + { + if (in_subs->unit->first_select() == sl) + break; + } + if (!in_subs) + return false; + } + return true; +} diff --git a/sql/sql_class.h b/sql/sql_class.h index 167df5b..3a2704f 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -7005,6 +7005,7 @@ class multi_delete :public select_result_interceptor // Methods used by ColumnStore uint get_num_of_tables() const { return num_of_tables; } TABLE_LIST* get_tables() const { return delete_tables; } + void set_delete_tables (TABLE_LIST *tbl) { delete_tables= tbl; } public: multi_delete(THD *thd_arg, TABLE_LIST *dt, uint num_of_tables); ~multi_delete(); diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 2ff4331..52efab3 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -1591,10 +1591,19 @@ bool Sql_cmd_delete::prepare_inner(THD *thd) select_lex->order_list.first, false, NULL, NULL, NULL, select_lex, &lex->unit))) - { goto err; } + + if (!multitable && + select_lex->sj_subselects.elements && + !select_lex->order_list.elements && + select_lex->master_unit()->lim.get_select_limit() == HA_POS_ERROR && + !thd->lex->has_returning()) + multitable= true; + + if (!multitable) + ((multi_delete *)result)->set_delete_tables(0); } if (multitable) @@ -1638,7 +1647,8 @@ bool Sql_cmd_delete::prepare_inner(THD *thd) { TABLE_LIST *duplicate; if ((duplicate= unique_table(thd, target_tbl->correspondent_table, - lex->query_tables, 0))) + lex->query_tables, 0)) && + !duplicate->select_lex->is_sj_subselect_lifted_to_top()) { update_non_unique_table_error(target_tbl->correspondent_table, "DELETE", duplicate); diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 0badc32..582ca53 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1642,6 +1642,7 @@ class st_select_lex: public st_select_lex_node void lex_start(LEX *plex); bool is_unit_nest() { return (nest_flags & UNIT_NEST_FL); } void mark_as_unit_nest() { nest_flags= UNIT_NEST_FL; } + bool is_sj_subselect_lifted_to_top(); }; typedef class st_select_lex SELECT_LEX; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index bf49d69..a7e5bc7 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5834,8 +5834,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, s->needed_reg=select->needed_reg; select->quick=0; impossible_range= records == 0 && s->table->reginfo.impossible_range; - if (join->thd->lex->sql_command == SQLCOM_SELECT && - optimizer_flag(join->thd, OPTIMIZER_SWITCH_USE_ROWID_FILTER)) + if (optimizer_flag(join->thd, OPTIMIZER_SWITCH_USE_ROWID_FILTER)) s->table->init_cost_info_for_usable_range_rowid_filters(join->thd); } if (!impossible_range) diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 6b14c4f..b614291 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -2471,6 +2471,8 @@ int multi_update::do_updates() table = cur_table->table; if (table == table_to_update) continue; // Already updated + if (table->file->pushed_rowid_filter) + table->file->disable_pushed_rowid_filter(); org_updated= updated; tmp_table= tmp_tables[cur_table->shared]; tmp_table->file->extra(HA_EXTRA_CACHE); // Change to read cache @@ -2665,7 +2667,8 @@ int multi_update::do_updates() check_opt_it.rewind(); while (TABLE *tbl= check_opt_it++) tbl->file->ha_rnd_end(); - + if (table->file->save_pushed_rowid_filter) + table->file->enable_pushed_rowid_filter(); } DBUG_RETURN(0); @@ -2676,6 +2679,8 @@ int multi_update::do_updates() } err2: + if (table->file->save_pushed_rowid_filter) + table->file->enable_pushed_rowid_filter(); if (table->file->inited) (void) table->file->ha_rnd_end(); if (tmp_table->file->inited) @@ -2956,6 +2961,11 @@ bool Sql_cmd_update::prepare_inner(THD *thd) goto err; } + if (!multitable && + select_lex->sj_subselects.elements && + !select_lex->order_list.elements && + select_lex->master_unit()->lim.get_select_limit() == HA_POS_ERROR) + multitable= true; } if (table_list->has_period()) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index a587a37..13dc602 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -13370,8 +13370,21 @@ delete_single_table: YYPS->m_lock_type, YYPS->m_mdl_type, NULL, + 0))) + MYSQL_YYABORT; + Select->table_list.save_and_clear(&Lex->auxiliary_table_list); + Lex->table_count= 1; + Lex->query_tables= 0; + Lex->query_tables_last= &Lex->query_tables; + if (unlikely(!Select-> + add_table_to_list(thd, $2, NULL, TL_OPTION_UPDATING, + YYPS->m_lock_type, + YYPS->m_mdl_type, + NULL, $3))) MYSQL_YYABORT; + Lex->auxiliary_table_list.first->correspondent_table= + Lex->query_tables; YYPS->m_lock_type= TL_READ_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_READ; }