DROP DATABASE IF EXISTS dbt3_s001;
CREATE DATABASE dbt3_s001 CHARACTER SET latin1;
use dbt3_s001;
create index i_n_name on nation(n_name);
analyze table
nation, lineitem, customer, orders, part, supplier, partsupp, region
persistent for all;
Table	Op	Msg_type	Msg_text
dbt3_s001.nation	analyze	status	Engine-independent statistics collected
dbt3_s001.nation	analyze	status	OK
dbt3_s001.lineitem	analyze	status	Engine-independent statistics collected
dbt3_s001.lineitem	analyze	status	OK
dbt3_s001.customer	analyze	status	Engine-independent statistics collected
dbt3_s001.customer	analyze	status	OK
dbt3_s001.orders	analyze	status	Engine-independent statistics collected
dbt3_s001.orders	analyze	status	OK
dbt3_s001.part	analyze	status	Engine-independent statistics collected
dbt3_s001.part	analyze	status	OK
dbt3_s001.supplier	analyze	status	Engine-independent statistics collected
dbt3_s001.supplier	analyze	status	OK
dbt3_s001.partsupp	analyze	status	Engine-independent statistics collected
dbt3_s001.partsupp	analyze	status	OK
dbt3_s001.region	analyze	status	Engine-independent statistics collected
dbt3_s001.region	analyze	status	OK
# Pullout
# =======
explain
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	6	
1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
explain format=json
select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
o_custkey in (select c_custkey from customer
where c_nationkey in (select n_nationkey from nation
where n_name='PERU'));
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.052271677,
    "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"],
          "loops": 1,
          "rows": 1,
          "cost": 0.002024411,
          "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"],
          "loops": 1,
          "rows": 6,
          "cost": 0.008193756,
          "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
          },
          "loops": 6,
          "rows": 15,
          "cost": 0.04205351,
          "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	6	
1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
explain format=json
update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
o_custkey in (select c_custkey from customer
where c_nationkey in (select n_nationkey from nation
where n_name='PERU'));
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "COST_REPLACED",
    "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"],
          "loops": 1,
          "rows": 1,
          "cost": "COST_REPLACED",
          "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"],
          "loops": 1,
          "rows": 6,
          "cost": "COST_REPLACED",
          "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
          },
          "loops": 6,
          "rows": 15,
          "cost": "COST_REPLACED",
          "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	1	
1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where; Using join buffer (flat, BNL join)
1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	
select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in
(select p_partkey, s_suppkey from part, supplier
where p_retailprice between 901 and 910 and
s_nationkey in (select n_nationkey from nation
where n_name='PERU'));
ps_partkey	ps_suppkey	ps_supplycost
1	8	357.84
3	8	645.4
4	1	444.37
5	8	50.52
6	1	642.13
7	8	763.98
8	1	957.34
explain
update partsupp set ps_supplycost = ps_supplycost+2 where (ps_partkey, ps_suppkey) in
(select p_partkey, s_suppkey from part, supplier
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	1	
1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where
1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	
update partsupp set ps_supplycost = ps_supplycost+2 where (ps_partkey, ps_suppkey) in
(select p_partkey, s_suppkey from part, supplier
where p_retailprice between 901 and 910 and
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
1	8	359.84
3	8	647.4
4	1	446.37
5	8	52.52
6	1	644.13
7	8	765.98
8	1	959.34
update partsupp set ps_supplycost = ps_supplycost-2 where (ps_partkey, ps_suppkey) in
(select p_partkey, s_suppkey from part, supplier
where p_retailprice between 901 and 910 and
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
1	8	357.84
3	8	645.4
4	1	444.37
5	8	50.52
6	1	642.13
7	8	763.98
8	1	957.34
explain
select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part
where p_retailprice between 901 and 910) and
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	1	
1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where; Using join buffer (flat, BNL join)
1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	
select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part
where p_retailprice between 901 and 910) and
ps_suppkey in (select s_suppkey from supplier
where s_nationkey in (select n_nationkey from nation
where n_name='PERU'));
ps_partkey	ps_suppkey	ps_supplycost
1	8	357.84
3	8	645.4
4	1	444.37
5	8	50.52
6	1	642.13
7	8	763.98
8	1	957.34
explain
update partsupp set ps_supplycost = ps_supplycost+10 where ps_partkey in (select p_partkey from part
where p_retailprice between 901 and 910) and
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	1	
1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where
1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	
update partsupp set ps_supplycost = ps_supplycost+10 where ps_partkey in (select p_partkey from part
where p_retailprice between 901 and 910) and
ps_suppkey in (select s_suppkey from supplier
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
1	8	367.84
3	8	655.4
4	1	454.37
5	8	60.52
6	1	652.13
7	8	773.98
8	1	967.34
update partsupp set ps_supplycost = ps_supplycost-10 where ps_partkey in (select p_partkey from part
where p_retailprice between 901 and 910) and
ps_suppkey in (select s_suppkey from supplier
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
1	8	357.84
3	8	645.4
4	1	444.37
5	8	50.52
6	1	642.13
7	8	763.98
8	1	957.34
explain
select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders
where o_custkey in
(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	1	
1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
1	PRIMARY	orders	ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
1	PRIMARY	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
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	1	
1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
1	PRIMARY	orders	ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
1	PRIMARY	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
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
# ==========
set optimizer_switch='materialization=off';
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-06-08');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	nation	ALL	PRIMARY,i_n_regionkey	NULL	NULL	NULL	25	Using where
1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (9%)	Using where; FirstMatch(customer); Using rowid filter
explain format=json
select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
where n_regionkey in (1,2))
and
c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-10-09' and '1993-06-08');
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "nation",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY", "i_n_regionkey"],
          "loops": 1,
          "rows": 25,
          "cost": "COST_REPLACED",
          "filtered": 40,
          "attached_condition": "nation.n_regionkey in (1,2)"
        }
      },
      {
        "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"],
          "loops": 10,
          "rows": 6,
          "cost": "COST_REPLACED",
          "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": 140,
            "selectivity_pct": 9.333333333
          },
          "loops": 60,
          "rows": 15,
          "cost": "COST_REPLACED",
          "filtered": 6.666666508,
          "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'",
          "first_match": "customer"
        }
      }
    ]
  }
}
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-06-08');
c_name	c_acctbal
Customer#000000059	3458.6
Customer#000000106	3288.42
Customer#000000017	6.34
Customer#000000047	274.58
Customer#000000092	1182.91
Customer#000000101	7470.96
Customer#000000022	591.98
Customer#000000040	1335.3
Customer#000000064	-646.64
Customer#000000122	7865.46
Customer#000000028	1007.18
Customer#000000037	-917.75
Customer#000000091	4643.14
Customer#000000115	7508.92
Customer#000000067	8166.59
Customer#000000094	5500.11
Customer#000000103	2757.45
Customer#000000130	5073.58
Customer#000000139	7897.78
Customer#000000142	2209.81
Customer#000000025	7133.7
Customer#000000008	6819.74
Customer#000000061	1536.24
Customer#000000077	1738.87
Customer#000000097	2164.48
Customer#000000121	6428.32
Customer#000000133	2314.67
Customer#000000007	9561.95
Customer#000000019	8914.71
Customer#000000082	9468.34
Customer#000000124	1842.49
Customer#000000127	9280.71
explain
update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation
where n_regionkey in (1,2))
and
c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-10-09' and '1993-06-08');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	nation	ALL	PRIMARY,i_n_regionkey	NULL	NULL	NULL	25	Using where
1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (9%)	Using where; FirstMatch(customer); Using rowid filter
explain format=json
update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation
where n_regionkey in (1,2))
and
c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-10-09' and '1993-06-08');
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "nation",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY", "i_n_regionkey"],
          "loops": 1,
          "rows": 25,
          "cost": "COST_REPLACED",
          "filtered": 40,
          "attached_condition": "nation.n_regionkey in (1,2)"
        }
      },
      {
        "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"],
          "loops": 10,
          "rows": 6,
          "cost": "COST_REPLACED",
          "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": 140,
            "selectivity_pct": 9.333333333
          },
          "loops": 60,
          "rows": 15,
          "cost": "COST_REPLACED",
          "filtered": 6.666666508,
          "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'",
          "first_match": "customer"
        }
      }
    ]
  }
}
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-06-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-06-08');
c_name	c_acctbal
Customer#000000059	3468.6
Customer#000000106	3298.42
Customer#000000017	16.34
Customer#000000047	284.58
Customer#000000092	1192.91
Customer#000000101	7480.96
Customer#000000022	601.98
Customer#000000040	1345.3
Customer#000000064	-636.64
Customer#000000122	7875.46
Customer#000000028	1017.18
Customer#000000037	-907.75
Customer#000000091	4653.14
Customer#000000115	7518.92
Customer#000000067	8176.59
Customer#000000094	5510.11
Customer#000000103	2767.45
Customer#000000130	5083.58
Customer#000000139	7907.78
Customer#000000142	2219.81
Customer#000000025	7143.7
Customer#000000008	6829.74
Customer#000000061	1546.24
Customer#000000077	1748.87
Customer#000000097	2174.48
Customer#000000121	6438.32
Customer#000000133	2324.67
Customer#000000007	9571.95
Customer#000000019	8924.71
Customer#000000082	9478.34
Customer#000000124	1852.49
Customer#000000127	9290.71
update customer set c_acctbal = c_acctbal-10 where c_nationkey in (select n_nationkey from nation
where n_regionkey in (1,2))
and
c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-10-09' and '1993-06-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-06-08');
c_name	c_acctbal
Customer#000000059	3458.6
Customer#000000106	3288.42
Customer#000000017	6.34
Customer#000000047	274.58
Customer#000000092	1182.91
Customer#000000101	7470.96
Customer#000000022	591.98
Customer#000000040	1335.3
Customer#000000064	-646.64
Customer#000000122	7865.46
Customer#000000028	1007.18
Customer#000000037	-917.75
Customer#000000091	4643.14
Customer#000000115	7508.92
Customer#000000067	8166.59
Customer#000000094	5500.11
Customer#000000103	2757.45
Customer#000000130	5073.58
Customer#000000139	7897.78
Customer#000000142	2209.81
Customer#000000025	7133.7
Customer#000000008	6819.74
Customer#000000061	1536.24
Customer#000000077	1738.87
Customer#000000097	2164.48
Customer#000000121	6428.32
Customer#000000133	2314.67
Customer#000000007	9561.95
Customer#000000019	8914.71
Customer#000000082	9468.34
Customer#000000124	1842.49
Customer#000000127	9280.71
set optimizer_switch='materialization=default';
explain
select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
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	6	
1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (14%)	Using where; FirstMatch(customer); Using rowid filter
select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
and
c_custkey in (select o_custkey from orders
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	6	
1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (14%)	Using where; FirstMatch(customer); Using rowid filter
update customer set c_acctbal = c_acctbal+20 where c_nationkey in (select n_nationkey from nation where n_name='PERU')
and
c_custkey in (select o_custkey from orders
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
# ===============
set optimizer_switch='firstmatch=off';
explain
select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where
n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
and
c_custkey in (select o_custkey from orders
where o_orderDATE between "1992-01-09" and "1995-01-08");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	nation	range	PRIMARY,i_n_name	i_n_name	26	NULL	4	Using index condition
1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
3	MATERIALIZED	orders	ALL	i_o_orderdate,i_o_custkey	NULL	NULL	NULL	1500	Using where
select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where
n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
and
c_custkey in (select o_custkey from orders
where o_orderDATE between "1992-01-09" and "1995-01-08");
c_name	c_acctbal
Customer#000000014	5266.3
Customer#000000059	3458.6
Customer#000000106	3288.42
Customer#000000067	8166.59
Customer#000000094	5500.11
Customer#000000103	2757.45
Customer#000000130	5073.58
Customer#000000139	7897.78
Customer#000000142	2209.81
Customer#000000025	7133.7
Customer#000000038	6345.11
Customer#000000098	-551.37
Customer#000000113	2912
Customer#000000008	6819.74
Customer#000000035	1228.24
Customer#000000061	1536.24
Customer#000000077	1738.87
Customer#000000097	2164.48
Customer#000000121	6428.32
Customer#000000133	2314.67
explain
update customer set c_acctbal = c_acctbal+20 where c_nationkey in (select n_nationkey from nation where
n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
and
c_custkey in (select o_custkey from orders
where o_orderDATE between "1992-01-09" and "1995-01-08");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	nation	range	PRIMARY,i_n_name	i_n_name	26	NULL	4	Using index condition
1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
3	MATERIALIZED	orders	ALL	i_o_orderdate,i_o_custkey	NULL	NULL	NULL	1500	Using where
update customer set c_acctbal = c_acctbal+20 where c_nationkey in (select n_nationkey from nation where
n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
and
c_custkey in (select o_custkey from orders
where o_orderDATE between "1992-01-09" and "1995-01-08");
select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where
n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
and
c_custkey in (select o_custkey from orders
where o_orderDATE between "1992-01-09" and "1995-01-08");
c_name	c_acctbal
Customer#000000014	5286.3
Customer#000000059	3478.6
Customer#000000106	3308.42
Customer#000000067	8186.59
Customer#000000094	5520.11
Customer#000000103	2777.45
Customer#000000130	5093.58
Customer#000000139	7917.78
Customer#000000142	2229.81
Customer#000000025	7153.7
Customer#000000038	6365.11
Customer#000000098	-531.37
Customer#000000113	2932
Customer#000000008	6839.74
Customer#000000035	1248.24
Customer#000000061	1556.24
Customer#000000077	1758.87
Customer#000000097	2184.48
Customer#000000121	6448.32
Customer#000000133	2334.67
update customer set c_acctbal = c_acctbal-20 where c_nationkey in (select n_nationkey from nation where
n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
and
c_custkey in (select o_custkey from orders
where o_orderDATE between "1992-01-09" and "1995-01-08");
select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where
n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
and
c_custkey in (select o_custkey from orders
where o_orderDATE between "1992-01-09" and "1995-01-08");
c_name	c_acctbal
Customer#000000014	5266.3
Customer#000000059	3458.6
Customer#000000106	3288.42
Customer#000000067	8166.59
Customer#000000094	5500.11
Customer#000000103	2757.45
Customer#000000130	5073.58
Customer#000000139	7897.78
Customer#000000142	2209.81
Customer#000000025	7133.7
Customer#000000038	6345.11
Customer#000000098	-551.37
Customer#000000113	2912
Customer#000000008	6819.74
Customer#000000035	1228.24
Customer#000000061	1536.24
Customer#000000077	1738.87
Customer#000000097	2164.48
Customer#000000121	6428.32
Customer#000000133	2314.67
set optimizer_switch='firstmatch=default';
explain
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
2	MATERIALIZED	orders	ALL	i_o_orderdate,i_o_custkey	NULL	NULL	NULL	1500	Using where
explain format=json
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.382051418,
    "nested_loop": [
      {
        "table": {
          "table_name": "customer",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY"],
          "loops": 1,
          "rows": 150,
          "cost": 0.03493875,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "<subquery2>",
          "access_type": "eq_ref",
          "possible_keys": ["distinct_key"],
          "key": "distinct_key",
          "key_length": "4",
          "used_key_parts": ["o_custkey"],
          "ref": ["func"],
          "rows": 1,
          "filtered": 100,
          "materialized": {
            "unique": 1,
            "query_block": {
              "select_id": 2,
              "nested_loop": [
                {
                  "table": {
                    "table_name": "orders",
                    "access_type": "ALL",
                    "possible_keys": ["i_o_orderdate", "i_o_custkey"],
                    "loops": 1,
                    "rows": 1500,
                    "cost": 0.2532975,
                    "filtered": 16.13333321,
                    "attached_condition": "orders.o_orderDATE between '1992-01-09' and '1993-03-08'"
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
c_name	c_acctbal
Customer#000000001	711.56
Customer#000000002	121.65
Customer#000000005	794.47
Customer#000000007	9561.95
Customer#000000008	6819.74
Customer#000000010	2753.54
Customer#000000011	-272.6
Customer#000000013	3857.34
Customer#000000014	5266.3
Customer#000000016	4681.03
Customer#000000017	6.34
Customer#000000019	8914.71
Customer#000000022	591.98
Customer#000000023	3332.02
Customer#000000025	7133.7
Customer#000000028	1007.18
Customer#000000029	7618.27
Customer#000000031	5236.89
Customer#000000032	3471.53
Customer#000000034	8589.7
Customer#000000035	1228.24
Customer#000000037	-917.75
Customer#000000038	6345.11
Customer#000000040	1335.3
Customer#000000041	270.95
Customer#000000043	9904.28
Customer#000000044	7315.94
Customer#000000046	5744.59
Customer#000000047	274.58
Customer#000000049	4573.94
Customer#000000052	5630.28
Customer#000000053	4113.64
Customer#000000055	4572.11
Customer#000000056	6530.86
Customer#000000058	6478.46
Customer#000000059	3458.6
Customer#000000061	1536.24
Customer#000000062	595.61
Customer#000000064	-646.64
Customer#000000065	8795.16
Customer#000000067	8166.59
Customer#000000070	4867.52
Customer#000000071	-611.19
Customer#000000073	4288.5
Customer#000000074	2764.43
Customer#000000076	5745.33
Customer#000000079	5121.28
Customer#000000080	7383.53
Customer#000000082	9468.34
Customer#000000083	6463.51
Customer#000000085	3386.64
Customer#000000086	3306.32
Customer#000000088	8031.44
Customer#000000089	1530.76
Customer#000000091	4643.14
Customer#000000092	1182.91
Customer#000000094	5500.11
Customer#000000095	5327.38
Customer#000000097	2164.48
Customer#000000098	-551.37
Customer#000000100	9889.89
Customer#000000101	7470.96
Customer#000000103	2757.45
Customer#000000104	-588.38
Customer#000000106	3288.42
Customer#000000107	2514.15
Customer#000000109	-716.1
Customer#000000110	7462.99
Customer#000000112	2953.35
Customer#000000115	7508.92
Customer#000000116	8403.99
Customer#000000118	3582.37
Customer#000000121	6428.32
Customer#000000122	7865.46
Customer#000000127	9280.71
Customer#000000128	-986.96
Customer#000000130	5073.58
Customer#000000131	8595.53
Customer#000000133	2314.67
Customer#000000134	4608.9
Customer#000000136	-842.39
Customer#000000137	7838.3
Customer#000000139	7897.78
Customer#000000140	9963.15
Customer#000000142	2209.81
Customer#000000143	2186.5
Customer#000000145	9748.93
Customer#000000148	2135.6
Customer#000000149	8959.65
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 '1993-03-08');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
2	MATERIALIZED	orders	ALL	i_o_orderdate,i_o_custkey	NULL	NULL	NULL	1500	Using where
explain format=json
update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "customer",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY"],
          "loops": 1,
          "rows": 150,
          "cost": "COST_REPLACED",
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "<subquery2>",
          "access_type": "eq_ref",
          "possible_keys": ["distinct_key"],
          "key": "distinct_key",
          "key_length": "4",
          "used_key_parts": ["o_custkey"],
          "ref": ["func"],
          "rows": 1,
          "filtered": 100,
          "materialized": {
            "unique": 1,
            "query_block": {
              "select_id": 2,
              "nested_loop": [
                {
                  "table": {
                    "table_name": "orders",
                    "access_type": "ALL",
                    "possible_keys": ["i_o_orderdate", "i_o_custkey"],
                    "loops": 1,
                    "rows": 1500,
                    "cost": "COST_REPLACED",
                    "filtered": 16.13333321,
                    "attached_condition": "orders.o_orderDATE between '1992-01-09' and '1993-03-08'"
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}
update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
c_name	c_acctbal
Customer#000000001	716.56
Customer#000000002	126.65
Customer#000000005	799.47
Customer#000000007	9566.95
Customer#000000008	6824.74
Customer#000000010	2758.54
Customer#000000011	-267.6
Customer#000000013	3862.34
Customer#000000014	5271.3
Customer#000000016	4686.03
Customer#000000017	11.34
Customer#000000019	8919.71
Customer#000000022	596.98
Customer#000000023	3337.02
Customer#000000025	7138.7
Customer#000000028	1012.18
Customer#000000029	7623.27
Customer#000000031	5241.89
Customer#000000032	3476.53
Customer#000000034	8594.7
Customer#000000035	1233.24
Customer#000000037	-912.75
Customer#000000038	6350.11
Customer#000000040	1340.3
Customer#000000041	275.95
Customer#000000043	9909.28
Customer#000000044	7320.94
Customer#000000046	5749.59
Customer#000000047	279.58
Customer#000000049	4578.94
Customer#000000052	5635.28
Customer#000000053	4118.64
Customer#000000055	4577.11
Customer#000000056	6535.86
Customer#000000058	6483.46
Customer#000000059	3463.6
Customer#000000061	1541.24
Customer#000000062	600.61
Customer#000000064	-641.64
Customer#000000065	8800.16
Customer#000000067	8171.59
Customer#000000070	4872.52
Customer#000000071	-606.19
Customer#000000073	4293.5
Customer#000000074	2769.43
Customer#000000076	5750.33
Customer#000000079	5126.28
Customer#000000080	7388.53
Customer#000000082	9473.34
Customer#000000083	6468.51
Customer#000000085	3391.64
Customer#000000086	3311.32
Customer#000000088	8036.44
Customer#000000089	1535.76
Customer#000000091	4648.14
Customer#000000092	1187.91
Customer#000000094	5505.11
Customer#000000095	5332.38
Customer#000000097	2169.48
Customer#000000098	-546.37
Customer#000000100	9894.89
Customer#000000101	7475.96
Customer#000000103	2762.45
Customer#000000104	-583.38
Customer#000000106	3293.42
Customer#000000107	2519.15
Customer#000000109	-711.1
Customer#000000110	7467.99
Customer#000000112	2958.35
Customer#000000115	7513.92
Customer#000000116	8408.99
Customer#000000118	3587.37
Customer#000000121	6433.32
Customer#000000122	7870.46
Customer#000000127	9285.71
Customer#000000128	-981.96
Customer#000000130	5078.58
Customer#000000131	8600.53
Customer#000000133	2319.67
Customer#000000134	4613.9
Customer#000000136	-837.39
Customer#000000137	7843.3
Customer#000000139	7902.78
Customer#000000140	9968.15
Customer#000000142	2214.81
Customer#000000143	2191.5
Customer#000000145	9753.93
Customer#000000148	2140.6
Customer#000000149	8964.65
update customer set c_acctbal = c_acctbal-5 where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
c_name	c_acctbal
Customer#000000001	711.56
Customer#000000002	121.65
Customer#000000005	794.47
Customer#000000007	9561.95
Customer#000000008	6819.74
Customer#000000010	2753.54
Customer#000000011	-272.6
Customer#000000013	3857.34
Customer#000000014	5266.3
Customer#000000016	4681.03
Customer#000000017	6.34
Customer#000000019	8914.71
Customer#000000022	591.98
Customer#000000023	3332.02
Customer#000000025	7133.7
Customer#000000028	1007.18
Customer#000000029	7618.27
Customer#000000031	5236.89
Customer#000000032	3471.53
Customer#000000034	8589.7
Customer#000000035	1228.24
Customer#000000037	-917.75
Customer#000000038	6345.11
Customer#000000040	1335.3
Customer#000000041	270.95
Customer#000000043	9904.28
Customer#000000044	7315.94
Customer#000000046	5744.59
Customer#000000047	274.58
Customer#000000049	4573.94
Customer#000000052	5630.28
Customer#000000053	4113.64
Customer#000000055	4572.11
Customer#000000056	6530.86
Customer#000000058	6478.46
Customer#000000059	3458.6
Customer#000000061	1536.24
Customer#000000062	595.61
Customer#000000064	-646.64
Customer#000000065	8795.16
Customer#000000067	8166.59
Customer#000000070	4867.52
Customer#000000071	-611.19
Customer#000000073	4288.5
Customer#000000074	2764.43
Customer#000000076	5745.33
Customer#000000079	5121.28
Customer#000000080	7383.53
Customer#000000082	9468.34
Customer#000000083	6463.51
Customer#000000085	3386.64
Customer#000000086	3306.32
Customer#000000088	8031.44
Customer#000000089	1530.76
Customer#000000091	4643.14
Customer#000000092	1182.91
Customer#000000094	5500.11
Customer#000000095	5327.38
Customer#000000097	2164.48
Customer#000000098	-551.37
Customer#000000100	9889.89
Customer#000000101	7470.96
Customer#000000103	2757.45
Customer#000000104	-588.38
Customer#000000106	3288.42
Customer#000000107	2514.15
Customer#000000109	-716.1
Customer#000000110	7462.99
Customer#000000112	2953.35
Customer#000000115	7508.92
Customer#000000116	8403.99
Customer#000000118	3582.37
Customer#000000121	6428.32
Customer#000000122	7865.46
Customer#000000127	9280.71
Customer#000000128	-986.96
Customer#000000130	5073.58
Customer#000000131	8595.53
Customer#000000133	2314.67
Customer#000000134	4608.9
Customer#000000136	-842.39
Customer#000000137	7838.3
Customer#000000139	7897.78
Customer#000000140	9963.15
Customer#000000142	2209.81
Customer#000000143	2186.5
Customer#000000145	9748.93
Customer#000000148	2135.6
Customer#000000149	8959.65
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,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "<subquery2>",
          "access_type": "ALL",
          "possible_keys": ["distinct_key"],
          "loops": 1,
          "rows": 28,
          "cost": "COST_REPLACED",
          "filtered": 100,
          "materialized": {
            "unique": 1,
            "materialization": {
              "query_block": {
                "select_id": 2,
                "cost": "COST_REPLACED",
                "having_condition": "count(orders.o_custkey) > 1",
                "temporary_table": {
                  "nested_loop": [
                    {
                      "table": {
                        "table_name": "orders",
                        "access_type": "range",
                        "possible_keys": ["i_o_orderdate"],
                        "key": "i_o_orderdate",
                        "key_length": "4",
                        "used_key_parts": ["o_orderDATE"],
                        "loops": 1,
                        "rows": 28,
                        "cost": "COST_REPLACED",
                        "filtered": 100,
                        "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'"
                      }
                    }
                  ]
                }
              }
            }
          }
        }
      },
      {
        "table": {
          "table_name": "customer",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["c_custkey"],
          "ref": ["<subquery2>.o_custkey"],
          "loops": 28,
          "rows": 1,
          "cost": "COST_REPLACED",
          "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,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "<subquery2>",
          "access_type": "ALL",
          "possible_keys": ["distinct_key"],
          "loops": 1,
          "rows": 28,
          "cost": "COST_REPLACED",
          "filtered": 100,
          "materialized": {
            "unique": 1,
            "materialization": {
              "query_block": {
                "select_id": 2,
                "cost": "COST_REPLACED",
                "having_condition": "count(orders.o_custkey) > 1",
                "temporary_table": {
                  "nested_loop": [
                    {
                      "table": {
                        "table_name": "orders",
                        "access_type": "range",
                        "possible_keys": ["i_o_orderdate"],
                        "key": "i_o_orderdate",
                        "key_length": "4",
                        "used_key_parts": ["o_orderDATE"],
                        "loops": 1,
                        "rows": 28,
                        "cost": "COST_REPLACED",
                        "filtered": 100,
                        "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'"
                      }
                    }
                  ]
                }
              }
            }
          }
        }
      },
      {
        "table": {
          "table_name": "customer",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["c_custkey"],
          "ref": ["<subquery2>.o_custkey"],
          "loops": 28,
          "rows": 1,
          "cost": "COST_REPLACED",
          "filtered": 100
        }
      }
    ]
  }
}
update customer set c_acctbal = c_acctbal-5 where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1992-03-08'
                      group by o_custkey having count(o_custkey) > 1);
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1992-03-08'
                      group by o_custkey having count(o_custkey) > 1);
c_name	c_acctbal
Customer#000000013	3852.34
Customer#000000032	3466.53
Customer#000000037	-922.75
Customer#000000118	3577.37
Customer#000000056	6525.86
update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1992-03-08'
                      group by o_custkey having count(o_custkey) > 1);
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1992-03-08'
                      group by o_custkey having count(o_custkey) > 1);
c_name	c_acctbal
Customer#000000013	3857.34
Customer#000000032	3471.53
Customer#000000037	-917.75
Customer#000000118	3582.37
Customer#000000056	6530.86
explain
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08'
                      group by o_custkey having count(o_custkey) > 5);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	dbt3_s001.customer.c_custkey	1	
2	MATERIALIZED	orders	ALL	i_o_orderdate	NULL	NULL	NULL	1500	Using where; Using temporary
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08'
                      group by o_custkey having count(o_custkey) > 5);
c_name	c_acctbal
Customer#000000007	9561.95
Customer#000000016	4681.03
Customer#000000037	-917.75
Customer#000000046	5744.59
Customer#000000091	4643.14
Customer#000000103	2757.45
Customer#000000118	3582.37
Customer#000000133	2314.67
Customer#000000134	4608.9
explain
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	ALL	i_o_orderdate	NULL	NULL	NULL	1500	Using where; Using temporary
update customer set c_acctbal = c_acctbal-1 where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08'
                      group by o_custkey having count(o_custkey) > 5);
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
# =============
set optimizer_switch='materialization=off';
prepare stmt from "
update customer set c_acctbal = c_acctbal+? where c_nationkey in (select n_nationkey from nation
where n_regionkey in (1,2))
and
c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-10-09' and '1993-06-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-06-08');
c_name	c_acctbal
Customer#000000059	3458.6
Customer#000000106	3288.42
Customer#000000017	6.34
Customer#000000047	274.58
Customer#000000092	1182.91
Customer#000000101	7470.96
Customer#000000022	591.98
Customer#000000040	1335.3
Customer#000000064	-646.64
Customer#000000122	7865.46
Customer#000000028	1007.18
Customer#000000037	-917.75
Customer#000000091	4643.14
Customer#000000115	7508.92
Customer#000000067	8166.59
Customer#000000094	5500.11
Customer#000000103	2757.45
Customer#000000130	5073.58
Customer#000000139	7897.78
Customer#000000142	2209.81
Customer#000000025	7133.7
Customer#000000008	6819.74
Customer#000000061	1536.24
Customer#000000077	1738.87
Customer#000000097	2164.48
Customer#000000121	6428.32
Customer#000000133	2314.67
Customer#000000007	9561.95
Customer#000000019	8914.71
Customer#000000082	9468.34
Customer#000000124	1842.49
Customer#000000127	9280.71
set @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-06-08');
c_name	c_acctbal
Customer#000000059	3473.6
Customer#000000106	3303.42
Customer#000000017	21.34
Customer#000000047	289.58
Customer#000000092	1197.91
Customer#000000101	7485.96
Customer#000000022	606.98
Customer#000000040	1350.3
Customer#000000064	-631.64
Customer#000000122	7880.46
Customer#000000028	1022.18
Customer#000000037	-902.75
Customer#000000091	4658.14
Customer#000000115	7523.92
Customer#000000067	8181.59
Customer#000000094	5515.11
Customer#000000103	2772.45
Customer#000000130	5088.58
Customer#000000139	7912.78
Customer#000000142	2224.81
Customer#000000025	7148.7
Customer#000000008	6834.74
Customer#000000061	1551.24
Customer#000000077	1753.87
Customer#000000097	2179.48
Customer#000000121	6443.32
Customer#000000133	2329.67
Customer#000000007	9576.95
Customer#000000019	8929.71
Customer#000000082	9483.34
Customer#000000124	1857.49
Customer#000000127	9295.71
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-06-08');
c_name	c_acctbal
Customer#000000059	3478.6
Customer#000000106	3308.42
Customer#000000017	26.34
Customer#000000047	294.58
Customer#000000092	1202.91
Customer#000000101	7490.96
Customer#000000022	611.98
Customer#000000040	1355.3
Customer#000000064	-626.64
Customer#000000122	7885.46
Customer#000000028	1027.1799999999998
Customer#000000037	-897.75
Customer#000000091	4663.14
Customer#000000115	7528.92
Customer#000000067	8186.59
Customer#000000094	5520.11
Customer#000000103	2777.45
Customer#000000130	5093.58
Customer#000000139	7917.78
Customer#000000142	2229.81
Customer#000000025	7153.7
Customer#000000008	6839.74
Customer#000000061	1556.24
Customer#000000077	1758.87
Customer#000000097	2184.48
Customer#000000121	6448.32
Customer#000000133	2334.67
Customer#000000007	9581.95
Customer#000000019	8934.71
Customer#000000082	9488.34
Customer#000000124	1862.49
Customer#000000127	9300.71
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-06-08');
c_name	c_acctbal
Customer#000000059	3458.6
Customer#000000106	3288.42
Customer#000000017	6.34
Customer#000000047	274.58
Customer#000000092	1182.91
Customer#000000101	7470.96
Customer#000000022	591.98
Customer#000000040	1335.3
Customer#000000064	-646.64
Customer#000000122	7865.46
Customer#000000028	1007.1799999999998
Customer#000000037	-917.75
Customer#000000091	4643.14
Customer#000000115	7508.92
Customer#000000067	8166.59
Customer#000000094	5500.11
Customer#000000103	2757.45
Customer#000000130	5073.58
Customer#000000139	7897.78
Customer#000000142	2209.81
Customer#000000025	7133.7
Customer#000000008	6819.74
Customer#000000061	1536.24
Customer#000000077	1738.87
Customer#000000097	2164.48
Customer#000000121	6428.32
Customer#000000133	2314.67
Customer#000000007	9561.95
Customer#000000019	8914.71
Customer#000000082	9468.34
Customer#000000124	1842.49
Customer#000000127	9280.71
deallocate prepare stmt;
set optimizer_switch='materialization=default';
# Materialization PS
# ==================
prepare stmt from "
update customer set c_acctbal = c_acctbal+? where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
";
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
c_name	c_acctbal
Customer#000000001	711.56
Customer#000000002	121.65
Customer#000000005	794.47
Customer#000000007	9561.95
Customer#000000008	6819.74
Customer#000000010	2753.54
Customer#000000011	-272.6
Customer#000000013	3857.34
Customer#000000014	5266.3
Customer#000000016	4681.03
Customer#000000017	6.34
Customer#000000019	8914.71
Customer#000000022	591.98
Customer#000000023	3332.02
Customer#000000025	7133.7
Customer#000000028	1007.1799999999998
Customer#000000029	7618.27
Customer#000000031	5236.89
Customer#000000032	3471.53
Customer#000000034	8589.7
Customer#000000035	1228.24
Customer#000000037	-917.75
Customer#000000038	6345.11
Customer#000000040	1335.3
Customer#000000041	270.95
Customer#000000043	9904.28
Customer#000000044	7315.94
Customer#000000046	5744.59
Customer#000000047	274.58
Customer#000000049	4573.94
Customer#000000052	5630.28
Customer#000000053	4113.64
Customer#000000055	4572.11
Customer#000000056	6530.86
Customer#000000058	6478.46
Customer#000000059	3458.6
Customer#000000061	1536.24
Customer#000000062	595.61
Customer#000000064	-646.64
Customer#000000065	8795.16
Customer#000000067	8166.59
Customer#000000070	4867.52
Customer#000000071	-611.19
Customer#000000073	4288.5
Customer#000000074	2764.43
Customer#000000076	5745.33
Customer#000000079	5121.28
Customer#000000080	7383.53
Customer#000000082	9468.34
Customer#000000083	6463.51
Customer#000000085	3386.64
Customer#000000086	3306.32
Customer#000000088	8031.44
Customer#000000089	1530.76
Customer#000000091	4643.14
Customer#000000092	1182.91
Customer#000000094	5500.11
Customer#000000095	5327.38
Customer#000000097	2164.48
Customer#000000098	-551.37
Customer#000000100	9889.89
Customer#000000101	7470.96
Customer#000000103	2757.45
Customer#000000104	-588.38
Customer#000000106	3288.42
Customer#000000107	2514.15
Customer#000000109	-716.1
Customer#000000110	7462.99
Customer#000000112	2953.35
Customer#000000115	7508.92
Customer#000000116	8403.99
Customer#000000118	3582.37
Customer#000000121	6428.32
Customer#000000122	7865.46
Customer#000000127	9280.71
Customer#000000128	-986.96
Customer#000000130	5073.58
Customer#000000131	8595.53
Customer#000000133	2314.67
Customer#000000134	4608.9
Customer#000000136	-842.39
Customer#000000137	7838.3
Customer#000000139	7897.78
Customer#000000140	9963.15
Customer#000000142	2209.81
Customer#000000143	2186.5
Customer#000000145	9748.93
Customer#000000148	2135.6
Customer#000000149	8959.65
set @a1=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 '1993-03-08');
c_name	c_acctbal
Customer#000000001	718.56
Customer#000000002	128.65
Customer#000000005	801.47
Customer#000000007	9568.95
Customer#000000008	6826.74
Customer#000000010	2760.54
Customer#000000011	-265.6
Customer#000000013	3864.34
Customer#000000014	5273.3
Customer#000000016	4688.03
Customer#000000017	13.34
Customer#000000019	8921.71
Customer#000000022	598.98
Customer#000000023	3339.02
Customer#000000025	7140.7
Customer#000000028	1014.1799999999998
Customer#000000029	7625.27
Customer#000000031	5243.89
Customer#000000032	3478.53
Customer#000000034	8596.7
Customer#000000035	1235.24
Customer#000000037	-910.75
Customer#000000038	6352.11
Customer#000000040	1342.3
Customer#000000041	277.95
Customer#000000043	9911.28
Customer#000000044	7322.94
Customer#000000046	5751.59
Customer#000000047	281.58
Customer#000000049	4580.94
Customer#000000052	5637.28
Customer#000000053	4120.64
Customer#000000055	4579.11
Customer#000000056	6537.86
Customer#000000058	6485.46
Customer#000000059	3465.6
Customer#000000061	1543.24
Customer#000000062	602.61
Customer#000000064	-639.64
Customer#000000065	8802.16
Customer#000000067	8173.59
Customer#000000070	4874.52
Customer#000000071	-604.19
Customer#000000073	4295.5
Customer#000000074	2771.43
Customer#000000076	5752.33
Customer#000000079	5128.28
Customer#000000080	7390.53
Customer#000000082	9475.34
Customer#000000083	6470.51
Customer#000000085	3393.64
Customer#000000086	3313.32
Customer#000000088	8038.44
Customer#000000089	1537.76
Customer#000000091	4650.14
Customer#000000092	1189.91
Customer#000000094	5507.11
Customer#000000095	5334.38
Customer#000000097	2171.48
Customer#000000098	-544.37
Customer#000000100	9896.89
Customer#000000101	7477.96
Customer#000000103	2764.45
Customer#000000104	-581.38
Customer#000000106	3295.42
Customer#000000107	2521.15
Customer#000000109	-709.1
Customer#000000110	7469.99
Customer#000000112	2960.35
Customer#000000115	7515.92
Customer#000000116	8410.99
Customer#000000118	3589.37
Customer#000000121	6435.32
Customer#000000122	7872.46
Customer#000000127	9287.71
Customer#000000128	-979.96
Customer#000000130	5080.58
Customer#000000131	8602.53
Customer#000000133	2321.67
Customer#000000134	4615.9
Customer#000000136	-835.39
Customer#000000137	7845.3
Customer#000000139	7904.78
Customer#000000140	9970.15
Customer#000000142	2216.81
Customer#000000143	2193.5
Customer#000000145	9755.93
Customer#000000148	2142.6
Customer#000000149	8966.65
set @a2=3;
execute stmt using @a2;
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
c_name	c_acctbal
Customer#000000001	721.56
Customer#000000002	131.65
Customer#000000005	804.47
Customer#000000007	9571.95
Customer#000000008	6829.74
Customer#000000010	2763.54
Customer#000000011	-262.6
Customer#000000013	3867.34
Customer#000000014	5276.3
Customer#000000016	4691.03
Customer#000000017	16.34
Customer#000000019	8924.71
Customer#000000022	601.98
Customer#000000023	3342.02
Customer#000000025	7143.7
Customer#000000028	1017.1799999999998
Customer#000000029	7628.27
Customer#000000031	5246.89
Customer#000000032	3481.53
Customer#000000034	8599.7
Customer#000000035	1238.24
Customer#000000037	-907.75
Customer#000000038	6355.11
Customer#000000040	1345.3
Customer#000000041	280.95
Customer#000000043	9914.28
Customer#000000044	7325.94
Customer#000000046	5754.59
Customer#000000047	284.58
Customer#000000049	4583.94
Customer#000000052	5640.28
Customer#000000053	4123.64
Customer#000000055	4582.11
Customer#000000056	6540.86
Customer#000000058	6488.46
Customer#000000059	3468.6
Customer#000000061	1546.24
Customer#000000062	605.61
Customer#000000064	-636.64
Customer#000000065	8805.16
Customer#000000067	8176.59
Customer#000000070	4877.52
Customer#000000071	-601.19
Customer#000000073	4298.5
Customer#000000074	2774.43
Customer#000000076	5755.33
Customer#000000079	5131.28
Customer#000000080	7393.53
Customer#000000082	9478.34
Customer#000000083	6473.51
Customer#000000085	3396.64
Customer#000000086	3316.32
Customer#000000088	8041.44
Customer#000000089	1540.76
Customer#000000091	4653.14
Customer#000000092	1192.91
Customer#000000094	5510.11
Customer#000000095	5337.38
Customer#000000097	2174.48
Customer#000000098	-541.37
Customer#000000100	9899.89
Customer#000000101	7480.96
Customer#000000103	2767.45
Customer#000000104	-578.38
Customer#000000106	3298.42
Customer#000000107	2524.15
Customer#000000109	-706.1
Customer#000000110	7472.99
Customer#000000112	2963.35
Customer#000000115	7518.92
Customer#000000116	8413.99
Customer#000000118	3592.37
Customer#000000121	6438.32
Customer#000000122	7875.46
Customer#000000127	9290.71
Customer#000000128	-976.96
Customer#000000130	5083.58
Customer#000000131	8605.53
Customer#000000133	2324.67
Customer#000000134	4618.9
Customer#000000136	-832.39
Customer#000000137	7848.3
Customer#000000139	7907.78
Customer#000000140	9973.15
Customer#000000142	2219.81
Customer#000000143	2196.5
Customer#000000145	9758.93
Customer#000000148	2145.6
Customer#000000149	8969.65
execute stmt using -(@a1+@a2);
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
c_name	c_acctbal
Customer#000000001	711.56
Customer#000000002	121.65
Customer#000000005	794.47
Customer#000000007	9561.95
Customer#000000008	6819.74
Customer#000000010	2753.54
Customer#000000011	-272.6
Customer#000000013	3857.34
Customer#000000014	5266.3
Customer#000000016	4681.03
Customer#000000017	6.34
Customer#000000019	8914.71
Customer#000000022	591.98
Customer#000000023	3332.02
Customer#000000025	7133.7
Customer#000000028	1007.1799999999998
Customer#000000029	7618.27
Customer#000000031	5236.89
Customer#000000032	3471.53
Customer#000000034	8589.7
Customer#000000035	1228.24
Customer#000000037	-917.75
Customer#000000038	6345.11
Customer#000000040	1335.3
Customer#000000041	270.95
Customer#000000043	9904.28
Customer#000000044	7315.94
Customer#000000046	5744.59
Customer#000000047	274.58
Customer#000000049	4573.94
Customer#000000052	5630.28
Customer#000000053	4113.64
Customer#000000055	4572.11
Customer#000000056	6530.86
Customer#000000058	6478.46
Customer#000000059	3458.6
Customer#000000061	1536.24
Customer#000000062	595.61
Customer#000000064	-646.64
Customer#000000065	8795.16
Customer#000000067	8166.59
Customer#000000070	4867.52
Customer#000000071	-611.19
Customer#000000073	4288.5
Customer#000000074	2764.43
Customer#000000076	5745.33
Customer#000000079	5121.28
Customer#000000080	7383.53
Customer#000000082	9468.34
Customer#000000083	6463.51
Customer#000000085	3386.64
Customer#000000086	3306.32
Customer#000000088	8031.44
Customer#000000089	1530.76
Customer#000000091	4643.14
Customer#000000092	1182.91
Customer#000000094	5500.11
Customer#000000095	5327.38
Customer#000000097	2164.48
Customer#000000098	-551.37
Customer#000000100	9889.89
Customer#000000101	7470.96
Customer#000000103	2757.45
Customer#000000104	-588.38
Customer#000000106	3288.42
Customer#000000107	2514.15
Customer#000000109	-716.1
Customer#000000110	7462.99
Customer#000000112	2953.35
Customer#000000115	7508.92
Customer#000000116	8403.99
Customer#000000118	3582.37
Customer#000000121	6428.32
Customer#000000122	7865.46
Customer#000000127	9280.71
Customer#000000128	-986.96
Customer#000000130	5073.58
Customer#000000131	8595.53
Customer#000000133	2314.67
Customer#000000134	4608.9
Customer#000000136	-842.39
Customer#000000137	7838.3
Customer#000000139	7897.78
Customer#000000140	9963.15
Customer#000000142	2209.81
Customer#000000143	2186.5
Customer#000000145	9748.93
Customer#000000148	2135.6
Customer#000000149	8959.65
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
# =============
set optimizer_switch='materialization=off';
create procedure p(d int)
update customer set c_acctbal = c_acctbal+d where c_nationkey in (select n_nationkey from nation
where n_regionkey in (1,2))
and
c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-10-09' and '1993-06-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-06-08');
c_name	c_acctbal
Customer#000000059	3458.6
Customer#000000106	3288.42
Customer#000000017	6.34
Customer#000000047	274.58
Customer#000000092	1182.91
Customer#000000101	7470.96
Customer#000000022	591.98
Customer#000000040	1335.3
Customer#000000064	-646.64
Customer#000000122	7865.46
Customer#000000028	1007.1799999999998
Customer#000000037	-917.75
Customer#000000091	4643.14
Customer#000000115	7508.92
Customer#000000067	8166.59
Customer#000000094	5500.11
Customer#000000103	2757.45
Customer#000000130	5073.58
Customer#000000139	7897.78
Customer#000000142	2209.81
Customer#000000025	7133.7
Customer#000000008	6819.74
Customer#000000061	1536.24
Customer#000000077	1738.87
Customer#000000097	2164.48
Customer#000000121	6428.32
Customer#000000133	2314.67
Customer#000000007	9561.95
Customer#000000019	8914.71
Customer#000000082	9468.34
Customer#000000124	1842.49
Customer#000000127	9280.71
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-06-08');
c_name	c_acctbal
Customer#000000059	3463.6
Customer#000000106	3293.42
Customer#000000017	11.34
Customer#000000047	279.58
Customer#000000092	1187.91
Customer#000000101	7475.96
Customer#000000022	596.98
Customer#000000040	1340.3
Customer#000000064	-641.64
Customer#000000122	7870.46
Customer#000000028	1012.1799999999998
Customer#000000037	-912.75
Customer#000000091	4648.14
Customer#000000115	7513.92
Customer#000000067	8171.59
Customer#000000094	5505.11
Customer#000000103	2762.45
Customer#000000130	5078.58
Customer#000000139	7902.78
Customer#000000142	2214.81
Customer#000000025	7138.7
Customer#000000008	6824.74
Customer#000000061	1541.24
Customer#000000077	1743.87
Customer#000000097	2169.48
Customer#000000121	6433.32
Customer#000000133	2319.67
Customer#000000007	9566.95
Customer#000000019	8919.71
Customer#000000082	9473.34
Customer#000000124	1847.49
Customer#000000127	9285.71
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-06-08');
c_name	c_acctbal
Customer#000000059	3478.6
Customer#000000106	3308.42
Customer#000000017	26.34
Customer#000000047	294.58
Customer#000000092	1202.91
Customer#000000101	7490.96
Customer#000000022	611.98
Customer#000000040	1355.3
Customer#000000064	-626.64
Customer#000000122	7885.46
Customer#000000028	1027.1799999999998
Customer#000000037	-897.75
Customer#000000091	4663.14
Customer#000000115	7528.92
Customer#000000067	8186.59
Customer#000000094	5520.11
Customer#000000103	2777.45
Customer#000000130	5093.58
Customer#000000139	7917.78
Customer#000000142	2229.81
Customer#000000025	7153.7
Customer#000000008	6839.74
Customer#000000061	1556.24
Customer#000000077	1758.87
Customer#000000097	2184.48
Customer#000000121	6448.32
Customer#000000133	2334.67
Customer#000000007	9581.95
Customer#000000019	8934.71
Customer#000000082	9488.34
Customer#000000124	1862.49
Customer#000000127	9300.71
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-06-08');
c_name	c_acctbal
Customer#000000059	3458.6
Customer#000000106	3288.42
Customer#000000017	6.34
Customer#000000047	274.58
Customer#000000092	1182.91
Customer#000000101	7470.96
Customer#000000022	591.98
Customer#000000040	1335.3
Customer#000000064	-646.64
Customer#000000122	7865.46
Customer#000000028	1007.1799999999998
Customer#000000037	-917.75
Customer#000000091	4643.14
Customer#000000115	7508.92
Customer#000000067	8166.59
Customer#000000094	5500.11
Customer#000000103	2757.45
Customer#000000130	5073.58
Customer#000000139	7897.78
Customer#000000142	2209.81
Customer#000000025	7133.7
Customer#000000008	6819.74
Customer#000000061	1536.24
Customer#000000077	1738.87
Customer#000000097	2164.48
Customer#000000121	6428.32
Customer#000000133	2314.67
Customer#000000007	9561.95
Customer#000000019	8914.71
Customer#000000082	9468.34
Customer#000000124	1842.49
Customer#000000127	9280.71
drop procedure p;
set optimizer_switch='materialization=default';
# Materialization SP
# ==================
create procedure p(d int)
update customer set c_acctbal = c_acctbal+d where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
c_name	c_acctbal
Customer#000000001	711.56
Customer#000000002	121.65
Customer#000000005	794.47
Customer#000000007	9561.95
Customer#000000008	6819.74
Customer#000000010	2753.54
Customer#000000011	-272.6
Customer#000000013	3857.34
Customer#000000014	5266.3
Customer#000000016	4681.03
Customer#000000017	6.34
Customer#000000019	8914.71
Customer#000000022	591.98
Customer#000000023	3332.02
Customer#000000025	7133.7
Customer#000000028	1007.1799999999998
Customer#000000029	7618.27
Customer#000000031	5236.89
Customer#000000032	3471.53
Customer#000000034	8589.7
Customer#000000035	1228.24
Customer#000000037	-917.75
Customer#000000038	6345.11
Customer#000000040	1335.3
Customer#000000041	270.95
Customer#000000043	9904.28
Customer#000000044	7315.94
Customer#000000046	5744.59
Customer#000000047	274.58
Customer#000000049	4573.94
Customer#000000052	5630.28
Customer#000000053	4113.64
Customer#000000055	4572.11
Customer#000000056	6530.86
Customer#000000058	6478.46
Customer#000000059	3458.6
Customer#000000061	1536.24
Customer#000000062	595.61
Customer#000000064	-646.64
Customer#000000065	8795.16
Customer#000000067	8166.59
Customer#000000070	4867.52
Customer#000000071	-611.19
Customer#000000073	4288.5
Customer#000000074	2764.43
Customer#000000076	5745.33
Customer#000000079	5121.28
Customer#000000080	7383.53
Customer#000000082	9468.34
Customer#000000083	6463.51
Customer#000000085	3386.64
Customer#000000086	3306.32
Customer#000000088	8031.44
Customer#000000089	1530.76
Customer#000000091	4643.14
Customer#000000092	1182.91
Customer#000000094	5500.11
Customer#000000095	5327.38
Customer#000000097	2164.48
Customer#000000098	-551.37
Customer#000000100	9889.89
Customer#000000101	7470.96
Customer#000000103	2757.45
Customer#000000104	-588.38
Customer#000000106	3288.42
Customer#000000107	2514.15
Customer#000000109	-716.1
Customer#000000110	7462.99
Customer#000000112	2953.35
Customer#000000115	7508.92
Customer#000000116	8403.99
Customer#000000118	3582.37
Customer#000000121	6428.32
Customer#000000122	7865.46
Customer#000000127	9280.71
Customer#000000128	-986.96
Customer#000000130	5073.58
Customer#000000131	8595.53
Customer#000000133	2314.67
Customer#000000134	4608.9
Customer#000000136	-842.39
Customer#000000137	7838.3
Customer#000000139	7897.78
Customer#000000140	9963.15
Customer#000000142	2209.81
Customer#000000143	2186.5
Customer#000000145	9748.93
Customer#000000148	2135.6
Customer#000000149	8959.65
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 '1993-03-08');
c_name	c_acctbal
Customer#000000001	714.56
Customer#000000002	124.65
Customer#000000005	797.47
Customer#000000007	9564.95
Customer#000000008	6822.74
Customer#000000010	2756.54
Customer#000000011	-269.6
Customer#000000013	3860.34
Customer#000000014	5269.3
Customer#000000016	4684.03
Customer#000000017	9.34
Customer#000000019	8917.71
Customer#000000022	594.98
Customer#000000023	3335.02
Customer#000000025	7136.7
Customer#000000028	1010.1799999999998
Customer#000000029	7621.27
Customer#000000031	5239.89
Customer#000000032	3474.53
Customer#000000034	8592.7
Customer#000000035	1231.24
Customer#000000037	-914.75
Customer#000000038	6348.11
Customer#000000040	1338.3
Customer#000000041	273.95
Customer#000000043	9907.28
Customer#000000044	7318.94
Customer#000000046	5747.59
Customer#000000047	277.58
Customer#000000049	4576.94
Customer#000000052	5633.28
Customer#000000053	4116.64
Customer#000000055	4575.11
Customer#000000056	6533.86
Customer#000000058	6481.46
Customer#000000059	3461.6
Customer#000000061	1539.24
Customer#000000062	598.61
Customer#000000064	-643.64
Customer#000000065	8798.16
Customer#000000067	8169.59
Customer#000000070	4870.52
Customer#000000071	-608.19
Customer#000000073	4291.5
Customer#000000074	2767.43
Customer#000000076	5748.33
Customer#000000079	5124.28
Customer#000000080	7386.53
Customer#000000082	9471.34
Customer#000000083	6466.51
Customer#000000085	3389.64
Customer#000000086	3309.32
Customer#000000088	8034.44
Customer#000000089	1533.76
Customer#000000091	4646.14
Customer#000000092	1185.91
Customer#000000094	5503.11
Customer#000000095	5330.38
Customer#000000097	2167.48
Customer#000000098	-548.37
Customer#000000100	9892.89
Customer#000000101	7473.96
Customer#000000103	2760.45
Customer#000000104	-585.38
Customer#000000106	3291.42
Customer#000000107	2517.15
Customer#000000109	-713.1
Customer#000000110	7465.99
Customer#000000112	2956.35
Customer#000000115	7511.92
Customer#000000116	8406.99
Customer#000000118	3585.37
Customer#000000121	6431.32
Customer#000000122	7868.46
Customer#000000127	9283.71
Customer#000000128	-983.96
Customer#000000130	5076.58
Customer#000000131	8598.53
Customer#000000133	2317.67
Customer#000000134	4611.9
Customer#000000136	-839.39
Customer#000000137	7841.3
Customer#000000139	7900.78
Customer#000000140	9966.15
Customer#000000142	2212.81
Customer#000000143	2189.5
Customer#000000145	9751.93
Customer#000000148	2138.6
Customer#000000149	8962.65
call p(7);
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
c_name	c_acctbal
Customer#000000001	721.56
Customer#000000002	131.65
Customer#000000005	804.47
Customer#000000007	9571.95
Customer#000000008	6829.74
Customer#000000010	2763.54
Customer#000000011	-262.6
Customer#000000013	3867.34
Customer#000000014	5276.3
Customer#000000016	4691.03
Customer#000000017	16.34
Customer#000000019	8924.71
Customer#000000022	601.98
Customer#000000023	3342.02
Customer#000000025	7143.7
Customer#000000028	1017.1799999999998
Customer#000000029	7628.27
Customer#000000031	5246.89
Customer#000000032	3481.53
Customer#000000034	8599.7
Customer#000000035	1238.24
Customer#000000037	-907.75
Customer#000000038	6355.11
Customer#000000040	1345.3
Customer#000000041	280.95
Customer#000000043	9914.28
Customer#000000044	7325.94
Customer#000000046	5754.59
Customer#000000047	284.58
Customer#000000049	4583.94
Customer#000000052	5640.28
Customer#000000053	4123.64
Customer#000000055	4582.11
Customer#000000056	6540.86
Customer#000000058	6488.46
Customer#000000059	3468.6
Customer#000000061	1546.24
Customer#000000062	605.61
Customer#000000064	-636.64
Customer#000000065	8805.16
Customer#000000067	8176.59
Customer#000000070	4877.52
Customer#000000071	-601.19
Customer#000000073	4298.5
Customer#000000074	2774.43
Customer#000000076	5755.33
Customer#000000079	5131.28
Customer#000000080	7393.53
Customer#000000082	9478.34
Customer#000000083	6473.51
Customer#000000085	3396.64
Customer#000000086	3316.32
Customer#000000088	8041.44
Customer#000000089	1540.76
Customer#000000091	4653.14
Customer#000000092	1192.91
Customer#000000094	5510.11
Customer#000000095	5337.38
Customer#000000097	2174.48
Customer#000000098	-541.37
Customer#000000100	9899.89
Customer#000000101	7480.96
Customer#000000103	2767.45
Customer#000000104	-578.38
Customer#000000106	3298.42
Customer#000000107	2524.15
Customer#000000109	-706.1
Customer#000000110	7472.99
Customer#000000112	2963.35
Customer#000000115	7518.92
Customer#000000116	8413.99
Customer#000000118	3592.37
Customer#000000121	6438.32
Customer#000000122	7875.46
Customer#000000127	9290.71
Customer#000000128	-976.96
Customer#000000130	5083.58
Customer#000000131	8605.53
Customer#000000133	2324.67
Customer#000000134	4618.9
Customer#000000136	-832.39
Customer#000000137	7848.3
Customer#000000139	7907.78
Customer#000000140	9973.15
Customer#000000142	2219.81
Customer#000000143	2196.5
Customer#000000145	9758.93
Customer#000000148	2145.6
Customer#000000149	8969.65
call p(-(3+7));
select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
where o_orderDATE between '1992-01-09' and '1993-03-08');
c_name	c_acctbal
Customer#000000001	711.56
Customer#000000002	121.65
Customer#000000005	794.47
Customer#000000007	9561.95
Customer#000000008	6819.74
Customer#000000010	2753.54
Customer#000000011	-272.6
Customer#000000013	3857.34
Customer#000000014	5266.3
Customer#000000016	4681.03
Customer#000000017	6.34
Customer#000000019	8914.71
Customer#000000022	591.98
Customer#000000023	3332.02
Customer#000000025	7133.7
Customer#000000028	1007.1799999999998
Customer#000000029	7618.27
Customer#000000031	5236.89
Customer#000000032	3471.53
Customer#000000034	8589.7
Customer#000000035	1228.24
Customer#000000037	-917.75
Customer#000000038	6345.11
Customer#000000040	1335.3
Customer#000000041	270.95
Customer#000000043	9904.28
Customer#000000044	7315.94
Customer#000000046	5744.59
Customer#000000047	274.58
Customer#000000049	4573.94
Customer#000000052	5630.28
Customer#000000053	4113.64
Customer#000000055	4572.11
Customer#000000056	6530.86
Customer#000000058	6478.46
Customer#000000059	3458.6
Customer#000000061	1536.24
Customer#000000062	595.61
Customer#000000064	-646.64
Customer#000000065	8795.16
Customer#000000067	8166.59
Customer#000000070	4867.52
Customer#000000071	-611.19
Customer#000000073	4288.5
Customer#000000074	2764.43
Customer#000000076	5745.33
Customer#000000079	5121.28
Customer#000000080	7383.53
Customer#000000082	9468.34
Customer#000000083	6463.51
Customer#000000085	3386.64
Customer#000000086	3306.32
Customer#000000088	8031.44
Customer#000000089	1530.76
Customer#000000091	4643.14
Customer#000000092	1182.91
Customer#000000094	5500.11
Customer#000000095	5327.38
Customer#000000097	2164.48
Customer#000000098	-551.37
Customer#000000100	9889.89
Customer#000000101	7470.96
Customer#000000103	2757.45
Customer#000000104	-588.38
Customer#000000106	3288.42
Customer#000000107	2514.15
Customer#000000109	-716.1
Customer#000000110	7462.99
Customer#000000112	2953.35
Customer#000000115	7508.92
Customer#000000116	8403.99
Customer#000000118	3582.37
Customer#000000121	6428.32
Customer#000000122	7865.46
Customer#000000127	9280.71
Customer#000000128	-986.96
Customer#000000130	5073.58
Customer#000000131	8595.53
Customer#000000133	2314.67
Customer#000000134	4608.9
Customer#000000136	-842.39
Customer#000000137	7838.3
Customer#000000139	7897.78
Customer#000000140	9963.15
Customer#000000142	2209.81
Customer#000000143	2186.5
Customer#000000145	9748.93
Customer#000000148	2135.6
Customer#000000149	8959.65
drop 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;
# Checking limitations
# ====================
select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
o_custkey in (select c_custkey from customer
where c_nationkey in (1,2));
o_orderkey	o_totalprice
1221	117397.16
324	26868.85
1856	189361.42
1344	43809.37
1925	146382.71
3139	40975.96
4903	34363.63
5607	24660.06
# Should not use semi-join conversion because has ORDER BY ... LIMIT
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 (1,2))
order by o_totalprice limit 500;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	108	Using where; Using filesort
2	DEPENDENT SUBQUERY	customer	unique_subquery	PRIMARY,i_c_nationkey	PRIMARY	4	func	1	Using where
update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
o_custkey in (select c_custkey from customer
where c_nationkey in (1,2))
order by o_totalprice limit 500;
select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
o_custkey in (select c_custkey from customer
where c_nationkey in (1,2));
o_orderkey	o_totalprice
1221	117347.16
324	26818.85
1856	189311.42
1344	43759.37
1925	146332.71
3139	40925.96
4903	34313.63
5607	24610.06
update orders set o_totalprice = o_totalprice+50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
o_custkey in (select c_custkey from customer
where c_nationkey in (1,2))
order by o_totalprice limit 500;
select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
o_custkey in (select c_custkey from customer
where c_nationkey in (1,2));
o_orderkey	o_totalprice
1221	117397.16
324	26868.85
1856	189361.42
1344	43809.37
1925	146382.71
3139	40975.96
4903	34363.63
5607	24660.06
# Should use semi-join converion
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 (1,2));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	customer	range	PRIMARY,i_c_nationkey	i_c_nationkey	5	NULL	15	Using index condition
1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
o_custkey in (select c_custkey from customer
where c_nationkey in (1,2));
select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
o_custkey in (select c_custkey from customer
where c_nationkey in (1,2));
o_orderkey	o_totalprice
1221	117347.16
324	26818.85
1856	189311.42
1344	43759.37
1925	146332.71
3139	40925.96
4903	34313.63
5607	24610.06
update orders set o_totalprice = o_totalprice+50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
o_custkey in (select c_custkey from customer
where c_nationkey in (1,2));
select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
o_custkey in (select c_custkey from customer
where c_nationkey in (1,2));
o_orderkey	o_totalprice
1221	117397.16
324	26868.85
1856	189361.42
1344	43809.37
1925	146382.71
3139	40975.96
4903	34363.63
5607	24660.06
CREATE TABLE partsupp_small (
ps_partkey int(11) NOT NULL DEFAULT '0',
ps_suppkey int(11) NOT NULL DEFAULT '0',
ps_availqty int(11) DEFAULT NULL,
ps_supplycost double DEFAULT NULL,
ps_comment varchar(199) DEFAULT NULL,
PRIMARY KEY (ps_partkey,ps_suppkey),
KEY i_ps_partkey (ps_partkey),
KEY i_ps_suppkey (ps_suppkey)
);
create index i_ps_sup_part on partsupp_small(ps_suppkey, ps_partkey);
insert into partsupp_small select * from partsupp where ps_partkey <50;
analyze table partsupp_small  persistent for all;
Table	Op	Msg_type	Msg_text
dbt3_s001.partsupp_small	analyze	status	Engine-independent statistics collected
dbt3_s001.partsupp_small	analyze	status	OK
# LooseScan
# =========
explain
select count(*) from lineitem where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	partsupp_small	index	PRIMARY,i_ps_partkey,i_ps_suppkey,i_ps_sup_part	i_ps_sup_part	8	NULL	176	Using where; Using index; LooseScan
1	PRIMARY	lineitem	ref	i_l_suppkey	i_l_suppkey	5	dbt3_s001.partsupp_small.ps_suppkey	600	Using index
explain format=json
select count(*) from lineitem where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "partsupp_small",
          "access_type": "index",
          "possible_keys": [
            "PRIMARY",
            "i_ps_partkey",
            "i_ps_suppkey",
            "i_ps_sup_part"
          ],
          "key": "i_ps_sup_part",
          "key_length": "8",
          "used_key_parts": ["ps_suppkey", "ps_partkey"],
          "loops": 1,
          "rows": 176,
          "cost": "COST_REPLACED",
          "filtered": 5.113636494,
          "attached_condition": "partsupp_small.ps_partkey in (1,2,3)",
          "using_index": true,
          "loose_scan": true
        }
      },
      {
        "table": {
          "table_name": "lineitem",
          "access_type": "ref",
          "possible_keys": ["i_l_suppkey"],
          "key": "i_l_suppkey",
          "key_length": "5",
          "used_key_parts": ["l_suppkey"],
          "ref": ["dbt3_s001.partsupp_small.ps_suppkey"],
          "loops": 9,
          "rows": 600,
          "cost": "COST_REPLACED",
          "filtered": 11.11111069,
          "using_index": true
        }
      }
    ]
  }
}
select count(*) from lineitem where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
count(*)
5373
select  5373 as count, 136458704.22 as old_sum;
count	old_sum
5373	136458704.22
explain
update  lineitem set l_extendedprice=l_extendedprice+10 where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	lineitem	ALL	i_l_suppkey	NULL	NULL	NULL	6005	
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
2	MATERIALIZED	partsupp_small	range	PRIMARY,i_ps_partkey,i_ps_suppkey,i_ps_sup_part	PRIMARY	4	NULL	9	Using where; Using index
explain format=json
update  lineitem set l_extendedprice=l_extendedprice+10 where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "lineitem",
          "access_type": "ALL",
          "possible_keys": ["i_l_suppkey"],
          "loops": 1,
          "rows": 6005,
          "cost": "COST_REPLACED",
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "<subquery2>",
          "access_type": "eq_ref",
          "possible_keys": ["distinct_key"],
          "key": "distinct_key",
          "key_length": "4",
          "used_key_parts": ["ps_suppkey"],
          "ref": ["func"],
          "rows": 1,
          "filtered": 100,
          "materialized": {
            "unique": 1,
            "query_block": {
              "select_id": 2,
              "nested_loop": [
                {
                  "table": {
                    "table_name": "partsupp_small",
                    "access_type": "range",
                    "possible_keys": [
                      "PRIMARY",
                      "i_ps_partkey",
                      "i_ps_suppkey",
                      "i_ps_sup_part"
                    ],
                    "key": "PRIMARY",
                    "key_length": "4",
                    "used_key_parts": ["ps_partkey"],
                    "loops": 1,
                    "rows": 9,
                    "cost": "COST_REPLACED",
                    "filtered": 100,
                    "attached_condition": "partsupp_small.ps_partkey in (1,2,3)",
                    "using_index": true
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}
update  lineitem set l_extendedprice=l_extendedprice+10 where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
select ROUND(sum(l_extendedprice),2), 5373 as count,
(136458704.22+10*5373) as 'old_sum+10*count'
        from lineitem where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
ROUND(sum(l_extendedprice),2)	count	old_sum+10*count
136512434.22	5373	136512434.22
update  lineitem set l_extendedprice=l_extendedprice-10 where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
select  ROUND(sum(l_extendedprice),2), 5373 as count,
136458704.22 as old_sum from lineitem where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
ROUND(sum(l_extendedprice),2)	count	old_sum
136458704.22	5373	136458704.22
# LooseScan PS
# ============
prepare stmt from "
update  lineitem set l_extendedprice=l_extendedprice+? where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
";
select  5373 as count, 136458704.22 as old_sum;
count	old_sum
5373	136458704.22
set @a1=20;
execute stmt using @a1;
select ROUND(sum(l_extendedprice),2), 5373 as count,
(136458704.22+20*5373) as 'old_sum+20*count'
        from lineitem where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
ROUND(sum(l_extendedprice),2)	count	old_sum+20*count
136566164.22	5373	136566164.22
set @a2=10;
execute stmt using @a2;
select ROUND(sum(l_extendedprice),2), 5373 as count,
(136458704.22+30*5373) as 'old_sum+30*count'
        from lineitem where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
ROUND(sum(l_extendedprice),2)	count	old_sum+30*count
136619894.22	5373	136619894.22
execute stmt using -(@a1+@a2);
select  ROUND(sum(l_extendedprice),2), 5373 as count,
136458704.22 as old_sum from lineitem where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
ROUND(sum(l_extendedprice),2)	count	old_sum
136458704.22	5373	136458704.22
deallocate prepare stmt;
# LooseScan SP
# ============
create procedure p(d int)
update  lineitem set l_extendedprice=l_extendedprice+d where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
select  5373 as count, 136458704.22 as old_sum;
count	old_sum
5373	136458704.22
call p(10);
select ROUND(sum(l_extendedprice),2), 5373 as count,
(136458704.22+10*5373) as 'old_sum+10*count'
        from lineitem where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
ROUND(sum(l_extendedprice),2)	count	old_sum+10*count
136512434.22	5373	136512434.22
call p(20);
select ROUND(sum(l_extendedprice),2), 5373 as count,
(136458704.22+30*5373) as 'old_sum+30*count'
        from lineitem where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
ROUND(sum(l_extendedprice),2)	count	old_sum+30*count
136619894.22	5373	136619894.22
call p(-(10+20));
select  ROUND(sum(l_extendedprice),2), 5373 as count,
136458704.22 as old_sum from lineitem where l_suppkey in
(select ps_suppkey from partsupp_small
where ps_partkey in (1,2,3));
ROUND(sum(l_extendedprice),2)	count	old_sum
136458704.22	5373	136458704.22
drop procedure p;
# DuplicateWeedout
# ================
set @tmp_optimizer_switch= @@optimizer_switch;
set optimizer_switch='materialization=off';
analyze table lineitem;
Table	Op	Msg_type	Msg_text
dbt3_s001.lineitem	analyze	status	Engine-independent statistics collected
dbt3_s001.lineitem	analyze	status	OK
analyze table orders;
Table	Op	Msg_type	Msg_text
dbt3_s001.orders	analyze	status	Engine-independent statistics collected
dbt3_s001.orders	analyze	status	OK
explain
select count(*) from lineitem where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	supplier	range	PRIMARY	PRIMARY	4	NULL	1	Using where; Using index; Start temporary
1	PRIMARY	partsupp_small	ref	PRIMARY,i_ps_partkey,i_ps_suppkey,i_ps_sup_part	i_ps_sup_part	4	dbt3_s001.supplier.s_suppkey	17	Using where; Using index
1	PRIMARY	lineitem	ref	i_l_suppkey_partkey,i_l_partkey	i_l_suppkey_partkey	5	dbt3_s001.partsupp_small.ps_partkey	30	Using index
1	PRIMARY	lineitem	ref	i_l_suppkey_partkey,i_l_partkey	i_l_partkey	5	dbt3_s001.partsupp_small.ps_partkey	30	Using index; End temporary
explain format=json
select count(*) from lineitem where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "duplicates_removal": [
          {
            "table": {
              "table_name": "supplier",
              "access_type": "range",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["s_suppkey"],
              "loops": 1,
              "rows": 1,
              "cost": "COST_REPLACED",
              "filtered": 100,
              "attached_condition": "supplier.s_suppkey < 2",
              "using_index": true
            }
          },
          {
            "table": {
              "table_name": "partsupp_small",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "i_ps_partkey",
                "i_ps_suppkey",
                "i_ps_sup_part"
              ],
              "key": "i_ps_sup_part",
              "key_length": "4",
              "used_key_parts": ["ps_suppkey"],
              "ref": ["dbt3_s001.supplier.s_suppkey"],
              "loops": 1,
              "rows": 17,
              "cost": "COST_REPLACED",
              "filtered": 79.54545593,
              "attached_condition": "partsupp_small.ps_partkey is not null",
              "using_index": true
            }
          },
          {
            "table": {
              "table_name": "lineitem",
              "access_type": "ref",
              "possible_keys": ["i_l_suppkey_partkey", "i_l_partkey"],
              "key": "i_l_suppkey_partkey",
              "key_length": "5",
              "used_key_parts": ["l_partkey"],
              "ref": ["dbt3_s001.partsupp_small.ps_partkey"],
              "loops": 14,
              "rows": 30,
              "cost": "COST_REPLACED",
              "filtered": 100,
              "using_index": true
            }
          },
          {
            "table": {
              "table_name": "lineitem",
              "access_type": "ref",
              "possible_keys": ["i_l_suppkey_partkey", "i_l_partkey"],
              "key": "i_l_partkey",
              "key_length": "5",
              "used_key_parts": ["l_partkey"],
              "ref": ["dbt3_s001.partsupp_small.ps_partkey"],
              "loops": 420.35,
              "rows": 30,
              "cost": "COST_REPLACED",
              "filtered": 0.237896994,
              "using_index": true
            }
          }
        ]
      }
    ]
  }
}
select count(*) from lineitem where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
count(*)
471
select  471 as count, 10751458.66 as old_sum;
count	old_sum
471	10751458.66
explain
update  lineitem set l_extendedprice=l_extendedprice+10 where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	supplier	range	PRIMARY	PRIMARY	4	NULL	1	Using where; Using index; Start temporary
1	PRIMARY	partsupp_small	ref	PRIMARY,i_ps_partkey,i_ps_suppkey,i_ps_sup_part	i_ps_sup_part	4	dbt3_s001.supplier.s_suppkey	17	Using where; Using index
1	PRIMARY	lineitem	ref	i_l_suppkey_partkey,i_l_partkey	i_l_suppkey_partkey	5	dbt3_s001.partsupp_small.ps_partkey	30	
1	PRIMARY	lineitem	ref	i_l_suppkey_partkey,i_l_partkey	i_l_partkey	5	dbt3_s001.partsupp_small.ps_partkey	30	Using index; End temporary
explain format=json
update  lineitem set l_extendedprice=l_extendedprice+10 where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "duplicates_removal": [
          {
            "table": {
              "table_name": "supplier",
              "access_type": "range",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["s_suppkey"],
              "loops": 1,
              "rows": 1,
              "cost": "COST_REPLACED",
              "filtered": 100,
              "attached_condition": "supplier.s_suppkey < 2",
              "using_index": true
            }
          },
          {
            "table": {
              "table_name": "partsupp_small",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "i_ps_partkey",
                "i_ps_suppkey",
                "i_ps_sup_part"
              ],
              "key": "i_ps_sup_part",
              "key_length": "4",
              "used_key_parts": ["ps_suppkey"],
              "ref": ["dbt3_s001.supplier.s_suppkey"],
              "loops": 1,
              "rows": 17,
              "cost": "COST_REPLACED",
              "filtered": 79.54545593,
              "attached_condition": "partsupp_small.ps_partkey is not null",
              "using_index": true
            }
          },
          {
            "table": {
              "table_name": "lineitem",
              "access_type": "ref",
              "possible_keys": ["i_l_suppkey_partkey", "i_l_partkey"],
              "key": "i_l_suppkey_partkey",
              "key_length": "5",
              "used_key_parts": ["l_partkey"],
              "ref": ["dbt3_s001.partsupp_small.ps_partkey"],
              "loops": 14,
              "rows": 30,
              "cost": "COST_REPLACED",
              "filtered": 100
            }
          },
          {
            "table": {
              "table_name": "lineitem",
              "access_type": "ref",
              "possible_keys": ["i_l_suppkey_partkey", "i_l_partkey"],
              "key": "i_l_partkey",
              "key_length": "5",
              "used_key_parts": ["l_partkey"],
              "ref": ["dbt3_s001.partsupp_small.ps_partkey"],
              "loops": 420.35,
              "rows": 30,
              "cost": "COST_REPLACED",
              "filtered": 0.237896994,
              "using_index": true
            }
          }
        ]
      }
    ]
  }
}
update  lineitem set l_extendedprice=l_extendedprice+10 where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
select ROUND(sum(l_extendedprice),2), 471 as count,
(10751458.66+10*471) as 'old_sum+10*count'
        from lineitem where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
ROUND(sum(l_extendedprice),2)	count	old_sum+10*count
10756168.66	471	10756168.66
update  lineitem set l_extendedprice=l_extendedprice-10 where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
select  ROUND(sum(l_extendedprice),2), 471 as count,
10751458.66 as old_sum from lineitem where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
ROUND(sum(l_extendedprice),2)	count	old_sum
10751458.66	471	10751458.66
# DuplicateWeedout PS
# ===================
prepare stmt from "
update  lineitem set l_extendedprice=l_extendedprice+? where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
";
select  471 as count, 10751458.66 as old_sum;
count	old_sum
471	10751458.66
set @a1=20;
execute stmt using @a1;
select ROUND(sum(l_extendedprice),2), 471 as count,
(10751458.66+20*471) as 'old_sum+20*count'
        from lineitem where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
ROUND(sum(l_extendedprice),2)	count	old_sum+20*count
10760878.66	471	10760878.66
set @a2=10;
execute stmt using @a2;
select ROUND(sum(l_extendedprice),2), 471 as count,
(10751458.66+30*471) as 'old_sum+30*count'
        from lineitem where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
ROUND(sum(l_extendedprice),2)	count	old_sum+30*count
10765588.66	471	10765588.66
execute stmt using -(@a1+@a2);
select  ROUND(sum(l_extendedprice),2), 471 as count,
10751458.66 as old_sum from lineitem where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
ROUND(sum(l_extendedprice),2)	count	old_sum
10751458.66	471	10751458.66
deallocate prepare stmt;
# DuplicateWeedout SP
# ===================
create procedure p(d int)
update  lineitem set l_extendedprice=l_extendedprice+d where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
select  471 as count, 10751458.66 as old_sum;
count	old_sum
471	10751458.66
call p(10);
select ROUND(sum(l_extendedprice),2), 471 as count,
(10751458.66+10*471) as 'old_sum+10*count'
        from lineitem where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
ROUND(sum(l_extendedprice),2)	count	old_sum+10*count
10756168.66	471	10756168.66
call p(20);
select ROUND(sum(l_extendedprice),2), 471 as count,
(10751458.66+30*471) as 'old_sum+30*count'
        from lineitem where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
ROUND(sum(l_extendedprice),2)	count	old_sum+30*count
10765588.66	471	10765588.66
call p(-(10+20));
select  ROUND(sum(l_extendedprice),2), 471 as count,
10751458.66 as old_sum from lineitem where l_partkey in (
select ps_partkey
from partsupp_small join lineitem on ps_partkey=l_partkey
where ps_suppkey in (
select s_suppkey from supplier  where  s_suppkey < 2
)
);
ROUND(sum(l_extendedprice),2)	count	old_sum
10751458.66	471	10751458.66
drop procedure p;
set @@optimizer_switch=@tmp_optimizer_switch;
drop table partsupp_small;
DROP DATABASE dbt3_s001;