Adjusted test results after rebase against 11.0.1

19 files changed, 2853 insertions, 839 deletions

diff --git a/mysql-test/main/delete.result b/mysql-test/main/delete.result
index dd62e09b632..c4bf335091a 100644
--- a/mysql-test/main/delete.result
+++ b/mysql-test/main/delete.result
@@ -583,8 +583,7 @@ explain delete from t1
 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	8	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(t1)
 delete from t1
 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
 select *from t1;
@@ -600,8 +599,7 @@ where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3
 order by c2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	8	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(t1)
 delete from t1
 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3
 order by c2;
diff --git a/mysql-test/main/delete_single_to_multi.result b/mysql-test/main/delete_single_to_multi.result
index fe27f69bb78..eba4ca87167 100644
--- a/mysql-test/main/delete_single_to_multi.result
+++ b/mysql-test/main/delete_single_to_multi.result
@@ -2,10 +2,26 @@ DROP DATABASE IF EXISTS dbt3_s001;
 CREATE DATABASE dbt3_s001;
 use dbt3_s001;
 create index i_n_name on nation(n_name);
-analyze table nation;
+analyze table
+nation, lineitem, customer, orders, part, supplier, partsupp, region
+persistent for all;
 Table	Op	Msg_type	Msg_text
 dbt3_s001.nation	analyze	status	Engine-independent statistics collected
 dbt3_s001.nation	analyze	status	OK
+dbt3_s001.lineitem	analyze	status	Engine-independent statistics collected
+dbt3_s001.lineitem	analyze	status	OK
+dbt3_s001.customer	analyze	status	Engine-independent statistics collected
+dbt3_s001.customer	analyze	status	OK
+dbt3_s001.orders	analyze	status	Engine-independent statistics collected
+dbt3_s001.orders	analyze	status	OK
+dbt3_s001.part	analyze	status	Engine-independent statistics collected
+dbt3_s001.part	analyze	status	OK
+dbt3_s001.supplier	analyze	status	Engine-independent statistics collected
+dbt3_s001.supplier	analyze	status	OK
+dbt3_s001.partsupp	analyze	status	Engine-independent statistics collected
+dbt3_s001.partsupp	analyze	status	OK
+dbt3_s001.region	analyze	status	Engine-independent statistics collected
+dbt3_s001.region	analyze	status	OK
 # Pullout
 # =======
 explain
@@ -15,8 +31,8 @@ where c_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
-1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (7%)	Using where; Using rowid filter
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
 explain format=json
 select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
 o_custkey in (select c_custkey from customer
@@ -26,6 +42,7 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.052271677,
     "nested_loop": [
       {
         "table": {
@@ -36,7 +53,9 @@ EXPLAIN
           "key_length": "26",
           "used_key_parts": ["n_name"],
           "ref": ["const"],
+          "loops": 1,
           "rows": 1,
+          "cost": 0.002024411,
           "filtered": 100,
           "index_condition": "nation.n_name = 'PERU'"
         }
@@ -50,7 +69,9 @@ EXPLAIN
           "key_length": "5",
           "used_key_parts": ["c_nationkey"],
           "ref": ["dbt3_s001.nation.n_nationkey"],
-          "rows": 11,
+          "loops": 1,
+          "rows": 6,
+          "cost": 0.008193756,
           "filtered": 100
         }
       },
@@ -71,7 +92,9 @@ EXPLAIN
             "rows": 108,
             "selectivity_pct": 7.2
           },
-          "rows": 11,
+          "loops": 6,
+          "rows": 15,
+          "cost": 0.04205351,
           "filtered": 7.199999809,
           "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'"
         }
@@ -104,8 +127,8 @@ where c_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
-1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (7%)	Using where; Using rowid filter
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
 explain format=json
 delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
 o_custkey in (select c_custkey from customer
@@ -115,6 +138,7 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.052271677,
     "nested_loop": [
       {
         "table": {
@@ -125,7 +149,9 @@ EXPLAIN
           "key_length": "26",
           "used_key_parts": ["n_name"],
           "ref": ["const"],
+          "loops": 1,
           "rows": 1,
+          "cost": 0.002024411,
           "filtered": 100,
           "index_condition": "nation.n_name = 'PERU'"
         }
@@ -139,7 +165,9 @@ EXPLAIN
           "key_length": "5",
           "used_key_parts": ["c_nationkey"],
           "ref": ["dbt3_s001.nation.n_nationkey"],
-          "rows": 11,
+          "loops": 1,
+          "rows": 6,
+          "cost": 0.008193756,
           "filtered": 100
         }
       },
@@ -160,7 +188,9 @@ EXPLAIN
             "rows": 108,
             "selectivity_pct": 7.2
           },
-          "rows": 11,
+          "loops": 6,
+          "rows": 15,
+          "cost": 0.04205351,
           "filtered": 7.199999809,
           "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'"
         }
@@ -200,9 +230,9 @@ s_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
-1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
-1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	
 select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in
 (select p_partkey, s_suppkey from part, supplier
 where p_retailprice between 901 and 910 and
@@ -230,9 +260,9 @@ s_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
-1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
-1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where
+1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	
 delete from partsupp where (ps_partkey, ps_suppkey) in
 (select p_partkey, s_suppkey from part, supplier
 where p_retailprice between 901 and 910 and
@@ -267,9 +297,9 @@ where s_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
-1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
-1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	
 select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part
 where p_retailprice between 901 and 910) and
 ps_suppkey in (select s_suppkey from supplier
@@ -297,9 +327,9 @@ where s_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
-1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
-1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where
+1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	
 delete from partsupp where ps_partkey in (select p_partkey from part
 where p_retailprice between 901 and 910) and
 ps_suppkey in (select s_suppkey from supplier
@@ -344,11 +374,11 @@ where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
-1	PRIMARY	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey	5	dbt3_s001.supplier.s_suppkey	100	Using where
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
+1	PRIMARY	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
 1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
-1	PRIMARY	orders	eq_ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	PRIMARY|i_o_orderdate	4|4	dbt3_s001.lineitem.l_orderkey	1 (7%)	Using where; Using rowid filter
-1	PRIMARY	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
 select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders
 where o_custkey in
 (select c_custkey from customer
@@ -401,11 +431,11 @@ where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
-1	PRIMARY	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey	5	dbt3_s001.supplier.s_suppkey	100	Using where
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
+1	PRIMARY	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
 1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
-1	PRIMARY	orders	eq_ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	PRIMARY|i_o_orderdate	4|4	dbt3_s001.lineitem.l_orderkey	1 (7%)	Using where; Using rowid filter
-1	PRIMARY	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
 delete from lineitem where l_orderkey in (select o_orderkey from orders
 where o_custkey in
 (select c_custkey from customer
@@ -466,9 +496,9 @@ and
 c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-10-09' and '1993-06-08');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	customer	ALL	PRIMARY,i_c_nationkey	NULL	NULL	NULL	150	Using where
-1	PRIMARY	nation	eq_ref|filter	PRIMARY,i_n_regionkey	PRIMARY|i_n_regionkey	4|5	dbt3_s001.customer.c_nationkey	1 (40%)	Using where; Using rowid filter
-1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (9%)	Using where; FirstMatch(nation); Using rowid filter
+1	PRIMARY	nation	ALL	PRIMARY,i_n_regionkey	NULL	NULL	NULL	25	Using where
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (9%)	Using where; FirstMatch(customer); Using rowid filter
 explain format=json
 select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
 where n_regionkey in (1,2))
@@ -479,37 +509,33 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.40015207,
     "nested_loop": [
       {
         "table": {
-          "table_name": "customer",
+          "table_name": "nation",
           "access_type": "ALL",
-          "possible_keys": ["PRIMARY", "i_c_nationkey"],
-          "rows": 150,
-          "filtered": 100,
-          "attached_condition": "customer.c_nationkey is not null"
+          "possible_keys": ["PRIMARY", "i_n_regionkey"],
+          "loops": 1,
+          "rows": 25,
+          "cost": 0.013945725,
+          "filtered": 40,
+          "attached_condition": "nation.n_regionkey in (1,2)"
         }
       },
       {
         "table": {
-          "table_name": "nation",
-          "access_type": "eq_ref",
-          "possible_keys": ["PRIMARY", "i_n_regionkey"],
-          "key": "PRIMARY",
-          "key_length": "4",
-          "used_key_parts": ["n_nationkey"],
-          "ref": ["dbt3_s001.customer.c_nationkey"],
-          "rowid_filter": {
-            "range": {
-              "key": "i_n_regionkey",
-              "used_key_parts": ["n_regionkey"]
-            },
-            "rows": 10,
-            "selectivity_pct": 40
-          },
-          "rows": 1,
-          "filtered": 40,
-          "attached_condition": "nation.n_regionkey in (1,2)"
+          "table_name": "customer",
+          "access_type": "ref",
+          "possible_keys": ["PRIMARY", "i_c_nationkey"],
+          "key": "i_c_nationkey",
+          "key_length": "5",
+          "used_key_parts": ["c_nationkey"],
+          "ref": ["dbt3_s001.nation.n_nationkey"],
+          "loops": 10,
+          "rows": 6,
+          "cost": 0.08009436,
+          "filtered": 100
         }
       },
       {
@@ -529,10 +555,12 @@ EXPLAIN
             "rows": 140,
             "selectivity_pct": 9.333333333
           },
-          "rows": 11,
-          "filtered": 9.333333015,
+          "loops": 60,
+          "rows": 15,
+          "cost": 0.306111985,
+          "filtered": 6.666666508,
           "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'",
-          "first_match": "nation"
+          "first_match": "customer"
         }
       }
     ]
@@ -589,9 +617,9 @@ and
 c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-10-09' and '1993-06-08');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	customer	ALL	PRIMARY,i_c_nationkey	NULL	NULL	NULL	150	Using where
-1	PRIMARY	nation	eq_ref|filter	PRIMARY,i_n_regionkey	PRIMARY|i_n_regionkey	4|5	dbt3_s001.customer.c_nationkey	1 (40%)	Using where; Using rowid filter
-1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (9%)	Using where; FirstMatch(nation); Using rowid filter
+1	PRIMARY	nation	ALL	PRIMARY,i_n_regionkey	NULL	NULL	NULL	25	Using where
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (9%)	Using where; FirstMatch(customer); Using rowid filter
 explain format=json
 delete from customer where c_nationkey in (select n_nationkey from nation
 where n_regionkey in (1,2))
@@ -602,37 +630,33 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.40015207,
     "nested_loop": [
       {
         "table": {
-          "table_name": "customer",
+          "table_name": "nation",
           "access_type": "ALL",
-          "possible_keys": ["PRIMARY", "i_c_nationkey"],
-          "rows": 150,
-          "filtered": 100,
-          "attached_condition": "customer.c_nationkey is not null"
+          "possible_keys": ["PRIMARY", "i_n_regionkey"],
+          "loops": 1,
+          "rows": 25,
+          "cost": 0.013945725,
+          "filtered": 40,
+          "attached_condition": "nation.n_regionkey in (1,2)"
         }
       },
       {
         "table": {
-          "table_name": "nation",
-          "access_type": "eq_ref",
-          "possible_keys": ["PRIMARY", "i_n_regionkey"],
-          "key": "PRIMARY",
-          "key_length": "4",
-          "used_key_parts": ["n_nationkey"],
-          "ref": ["dbt3_s001.customer.c_nationkey"],
-          "rowid_filter": {
-            "range": {
-              "key": "i_n_regionkey",
-              "used_key_parts": ["n_regionkey"]
-            },
-            "rows": 10,
-            "selectivity_pct": 40
-          },
-          "rows": 1,
-          "filtered": 40,
-          "attached_condition": "nation.n_regionkey in (1,2)"
+          "table_name": "customer",
+          "access_type": "ref",
+          "possible_keys": ["PRIMARY", "i_c_nationkey"],
+          "key": "i_c_nationkey",
+          "key_length": "5",
+          "used_key_parts": ["c_nationkey"],
+          "ref": ["dbt3_s001.nation.n_nationkey"],
+          "loops": 10,
+          "rows": 6,
+          "cost": 0.08009436,
+          "filtered": 100
         }
       },
       {
@@ -652,10 +676,12 @@ EXPLAIN
             "rows": 140,
             "selectivity_pct": 9.333333333
           },
-          "rows": 11,
-          "filtered": 9.333333015,
+          "loops": 60,
+          "rows": 15,
+          "cost": 0.306111985,
+          "filtered": 6.666666508,
           "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'",
-          "first_match": "nation"
+          "first_match": "customer"
         }
       }
     ]
@@ -720,8 +746,8 @@ c_custkey in (select o_custkey from orders
 where o_orderDATE between "1992-01-09" and "1993-01-08");
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
-1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (14%)	Using where; FirstMatch(customer); Using rowid filter
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (14%)	Using where; FirstMatch(customer); Using rowid filter
 select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
 and
 c_custkey in (select o_custkey from orders
@@ -745,8 +771,8 @@ c_custkey in (select o_custkey from orders
 where o_orderDATE between "1992-01-09" and "1993-01-08");
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
-1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (14%)	Using where; FirstMatch(customer); Using rowid filter
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (14%)	Using where; FirstMatch(customer); Using rowid filter
 delete from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
 and
 c_custkey in (select o_custkey from orders
@@ -771,27 +797,138 @@ Customer#000000133	2314.67
 drop table t;
 # Materialization
 # ===============
+set optimizer_switch='firstmatch=off';
+explain
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where
+n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1995-01-08");
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	range	PRIMARY,i_n_name	i_n_name	26	NULL	4	Using index condition
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
+3	MATERIALIZED	orders	ALL	i_o_orderdate,i_o_custkey	NULL	NULL	NULL	1500	Using where
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where
+n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1995-01-08");
+c_name	c_acctbal
+Customer#000000008	6819.74
+Customer#000000014	5266.3
+Customer#000000025	7133.7
+Customer#000000035	1228.24
+Customer#000000038	6345.11
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000067	8166.59
+Customer#000000077	1738.87
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000098	-551.37
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000113	2912
+Customer#000000121	6428.32
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+Customer#000000142	2209.81
+create table t as
+select * from customer where c_nationkey in (select n_nationkey from nation where
+n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1995-01-08");
+explain
+delete from customer where c_nationkey in (select n_nationkey from nation where
+n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1995-01-08");
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	range	PRIMARY,i_n_name	i_n_name	26	NULL	4	Using index condition
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
+3	MATERIALIZED	orders	ALL	i_o_orderdate,i_o_custkey	NULL	NULL	NULL	1500	Using where
+delete from customer where c_nationkey in (select n_nationkey from nation where
+n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1995-01-08");
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where
+n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1995-01-08");
+c_name	c_acctbal
+insert into customer select * from t;
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where
+n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1995-01-08");
+c_name	c_acctbal
+Customer#000000008	6819.74
+Customer#000000014	5266.3
+Customer#000000025	7133.7
+Customer#000000035	1228.24
+Customer#000000038	6345.11
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000067	8166.59
+Customer#000000077	1738.87
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000098	-551.37
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000113	2912
+Customer#000000121	6428.32
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+Customer#000000142	2209.81
+drop table t;
+set optimizer_switch='firstmatch=default';
 explain
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	28	
-1	PRIMARY	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.orders.o_custkey	1	
-2	MATERIALIZED	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	28	Using index condition; Using where
+1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	orders	ALL	i_o_orderdate,i_o_custkey	NULL	NULL	NULL	1500	Using where
 explain format=json
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.382051418,
     "nested_loop": [
       {
         "table": {
-          "table_name": "<subquery2>",
+          "table_name": "customer",
           "access_type": "ALL",
+          "possible_keys": ["PRIMARY"],
+          "loops": 1,
+          "rows": 150,
+          "cost": 0.03493875,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "<subquery2>",
+          "access_type": "eq_ref",
           "possible_keys": ["distinct_key"],
-          "rows": 28,
+          "key": "distinct_key",
+          "key_length": "4",
+          "used_key_parts": ["o_custkey"],
+          "ref": ["func"],
+          "rows": 1,
           "filtered": 100,
           "materialized": {
             "unique": 1,
@@ -801,89 +938,155 @@ EXPLAIN
                 {
                   "table": {
                     "table_name": "orders",
-                    "access_type": "range",
+                    "access_type": "ALL",
                     "possible_keys": ["i_o_orderdate", "i_o_custkey"],
-                    "key": "i_o_orderdate",
-                    "key_length": "4",
-                    "used_key_parts": ["o_orderDATE"],
-                    "rows": 28,
-                    "filtered": 100,
-                    "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'",
-                    "attached_condition": "orders.o_custkey is not null"
+                    "loops": 1,
+                    "rows": 1500,
+                    "cost": 0.2532975,
+                    "filtered": 16.13333321,
+                    "attached_condition": "orders.o_orderDATE between '1992-01-09' and '1993-03-08'"
                   }
                 }
               ]
             }
           }
         }
-      },
-      {
-        "table": {
-          "table_name": "customer",
-          "access_type": "eq_ref",
-          "possible_keys": ["PRIMARY"],
-          "key": "PRIMARY",
-          "key_length": "4",
-          "used_key_parts": ["c_custkey"],
-          "ref": ["dbt3_s001.orders.o_custkey"],
-          "rows": 1,
-          "filtered": 100
-        }
       }
     ]
   }
 }
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
+Customer#000000001	711.56
+Customer#000000002	121.65
 Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
+Customer#000000014	5266.3
 Customer#000000016	4681.03
 Customer#000000017	6.34
+Customer#000000019	8914.71
 Customer#000000022	591.98
 Customer#000000023	3332.02
 Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
 Customer#000000032	3471.53
+Customer#000000034	8589.7
 Customer#000000035	1228.24
 Customer#000000037	-917.75
 Customer#000000038	6345.11
 Customer#000000040	1335.3
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
 Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
 Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
 Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
 Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
 Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
 Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
 Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
 Customer#000000116	8403.99
 Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
 Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 create table t as
 select * from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 explain
 delete from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	28	
-1	PRIMARY	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.orders.o_custkey	1	
-2	MATERIALIZED	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	28	Using index condition; Using where
+1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	orders	ALL	i_o_orderdate,i_o_custkey	NULL	NULL	NULL	1500	Using where
 explain format=json
 delete from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.37364371,
     "nested_loop": [
       {
         "table": {
-          "table_name": "<subquery2>",
+          "table_name": "customer",
           "access_type": "ALL",
+          "possible_keys": ["PRIMARY"],
+          "loops": 1,
+          "rows": 150,
+          "cost": 0.026531042,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "<subquery2>",
+          "access_type": "eq_ref",
           "possible_keys": ["distinct_key"],
-          "rows": 28,
+          "key": "distinct_key",
+          "key_length": "4",
+          "used_key_parts": ["o_custkey"],
+          "ref": ["func"],
+          "rows": 1,
           "filtered": 100,
           "materialized": {
             "unique": 1,
@@ -893,71 +1096,121 @@ EXPLAIN
                 {
                   "table": {
                     "table_name": "orders",
-                    "access_type": "range",
+                    "access_type": "ALL",
                     "possible_keys": ["i_o_orderdate", "i_o_custkey"],
-                    "key": "i_o_orderdate",
-                    "key_length": "4",
-                    "used_key_parts": ["o_orderDATE"],
-                    "rows": 28,
-                    "filtered": 100,
-                    "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'",
-                    "attached_condition": "orders.o_custkey is not null"
+                    "loops": 1,
+                    "rows": 1500,
+                    "cost": 0.2532975,
+                    "filtered": 16.13333321,
+                    "attached_condition": "orders.o_orderDATE between '1992-01-09' and '1993-03-08'"
                   }
                 }
               ]
             }
           }
         }
-      },
-      {
-        "table": {
-          "table_name": "customer",
-          "access_type": "eq_ref",
-          "possible_keys": ["PRIMARY"],
-          "key": "PRIMARY",
-          "key_length": "4",
-          "used_key_parts": ["c_custkey"],
-          "ref": ["dbt3_s001.orders.o_custkey"],
-          "rows": 1,
-          "filtered": 100
-        }
       }
     ]
   }
 }
 delete from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
 insert into customer select * from t;
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
+Customer#000000001	711.56
+Customer#000000002	121.65
 Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
+Customer#000000014	5266.3
 Customer#000000016	4681.03
 Customer#000000017	6.34
+Customer#000000019	8914.71
 Customer#000000022	591.98
 Customer#000000023	3332.02
 Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
 Customer#000000032	3471.53
+Customer#000000034	8589.7
 Customer#000000035	1228.24
 Customer#000000037	-917.75
 Customer#000000038	6345.11
 Customer#000000040	1335.3
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
 Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
 Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
 Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
 Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
 Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
 Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
 Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
 Customer#000000116	8403.99
 Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
 Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 drop table t;
 explain
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
@@ -1141,18 +1394,22 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.085533248,
     "nested_loop": [
       {
         "table": {
           "table_name": "<subquery2>",
           "access_type": "ALL",
           "possible_keys": ["distinct_key"],
+          "loops": 1,
           "rows": 28,
+          "cost": 0.03691572,
           "filtered": 100,
           "materialized": {
             "unique": 1,
             "query_block": {
               "select_id": 2,
+              "cost": 0.053826401,
               "having_condition": "count(orders.o_custkey) > 1",
               "temporary_table": {
                 "nested_loop": [
@@ -1164,7 +1421,9 @@ EXPLAIN
                       "key": "i_o_orderdate",
                       "key_length": "4",
                       "used_key_parts": ["o_orderDATE"],
+                      "loops": 1,
                       "rows": 28,
+                      "cost": 0.035889016,
                       "filtered": 100,
                       "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'"
                     }
@@ -1184,7 +1443,9 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["c_custkey"],
           "ref": ["<subquery2>.o_custkey"],
+          "loops": 28,
           "rows": 1,
+          "cost": 0.048617528,
           "filtered": 100
         }
       }
@@ -1220,18 +1481,22 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.054856476,
     "nested_loop": [
       {
         "table": {
           "table_name": "<subquery2>",
           "access_type": "ALL",
           "possible_keys": ["distinct_key"],
+          "loops": 1,
           "rows": 28,
+          "cost": 0.03691572,
           "filtered": 100,
           "materialized": {
             "unique": 1,
             "query_block": {
               "select_id": 2,
+              "cost": 0.053826401,
               "having_condition": "count(orders.o_custkey) > 1",
               "temporary_table": {
                 "nested_loop": [
@@ -1243,7 +1508,9 @@ EXPLAIN
                       "key": "i_o_orderdate",
                       "key_length": "4",
                       "used_key_parts": ["o_orderDATE"],
+                      "loops": 1,
                       "rows": 28,
+                      "cost": 0.035889016,
                       "filtered": 100,
                       "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'"
                     }
@@ -1263,7 +1530,9 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["c_custkey"],
           "ref": ["<subquery2>.o_custkey"],
+          "loops": 28,
           "rows": 1,
+          "cost": 0.017940756,
           "filtered": 100
         }
       }
@@ -1295,7 +1564,7 @@ where o_orderDATE between '1992-01-09' and '1993-03-08'
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	dbt3_s001.customer.c_custkey	1	
-2	MATERIALIZED	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	242	Using index condition; Using temporary
+2	MATERIALIZED	orders	ALL	i_o_orderdate	NULL	NULL	NULL	1500	Using where; Using temporary
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-01-09' and '1993-03-08'
                       group by o_custkey having count(o_custkey) > 5);
@@ -1320,7 +1589,7 @@ where o_orderDATE between '1992-01-09' and '1993-03-08'
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	dbt3_s001.customer.c_custkey	1	
-2	MATERIALIZED	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	242	Using index condition; Using temporary
+2	MATERIALIZED	orders	ALL	i_o_orderdate	NULL	NULL	NULL	1500	Using where; Using temporary
 delete from customer where c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-01-09' and '1993-03-08'
                       group by o_custkey having count(o_custkey) > 5);
@@ -1418,6 +1687,7 @@ drop table t,r;
 deallocate prepare stmt;
 # FirstMatch PS
 # =============
+set optimizer_switch='materialization=off';
 prepare stmt from "
 delete from customer where c_nationkey in (select n_nationkey from nation
 where n_regionkey in (1,2))
@@ -1569,155 +1839,468 @@ Customer#000000139	7897.78
 Customer#000000142	2209.81
 drop table t,r;
 deallocate prepare stmt;
+set optimizer_switch='materialization=default';
 # Materialization PS
 # ==================
 prepare stmt from "
 delete from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08') and c_name like ?;
+where o_orderDATE between '1992-01-09' and '1993-03-08') and c_name like ?;
 ";
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
+Customer#000000001	711.56
+Customer#000000002	121.65
 Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
+Customer#000000014	5266.3
 Customer#000000016	4681.03
 Customer#000000017	6.34
+Customer#000000019	8914.71
 Customer#000000022	591.98
 Customer#000000023	3332.02
 Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
 Customer#000000032	3471.53
+Customer#000000034	8589.7
 Customer#000000035	1228.24
 Customer#000000037	-917.75
 Customer#000000038	6345.11
 Customer#000000040	1335.3
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
 Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
 Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
 Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
 Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
 Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
 Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
 Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
 Customer#000000116	8403.99
 Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
 Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 set @a1='Customer#%1_';
 create table t as
 select * from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08') and c_name like @a1;
+where o_orderDATE between '1992-01-09' and '1993-03-08') and c_name like @a1;
 execute stmt using @a1;
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
+Customer#000000001	711.56
+Customer#000000002	121.65
 Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
 Customer#000000022	591.98
 Customer#000000023	3332.02
 Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
 Customer#000000032	3471.53
+Customer#000000034	8589.7
 Customer#000000035	1228.24
 Customer#000000037	-917.75
 Customer#000000038	6345.11
 Customer#000000040	1335.3
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
 Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
 Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
 Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
 Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
 Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
 Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
 Customer#000000109	-716.1
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
 Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 insert into customer select * from t;
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
+Customer#000000001	711.56
+Customer#000000002	121.65
 Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
+Customer#000000014	5266.3
 Customer#000000016	4681.03
 Customer#000000017	6.34
+Customer#000000019	8914.71
 Customer#000000022	591.98
 Customer#000000023	3332.02
 Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
 Customer#000000032	3471.53
+Customer#000000034	8589.7
 Customer#000000035	1228.24
 Customer#000000037	-917.75
 Customer#000000038	6345.11
 Customer#000000040	1335.3
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
 Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
 Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
 Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
 Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
 Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
 Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
 Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
 Customer#000000116	8403.99
 Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
 Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 set @a2='Customer#%3_';
 create table r as
 select * from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08') and c_name like @a2;
+where o_orderDATE between '1992-01-09' and '1993-03-08') and c_name like @a2;
 execute stmt using @a2;
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
+Customer#000000001	711.56
+Customer#000000002	121.65
 Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
+Customer#000000014	5266.3
 Customer#000000016	4681.03
 Customer#000000017	6.34
+Customer#000000019	8914.71
 Customer#000000022	591.98
 Customer#000000023	3332.02
 Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
 Customer#000000040	1335.3
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
 Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
 Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
 Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
 Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
 Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
 Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
 Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
 Customer#000000116	8403.99
 Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
 Customer#000000140	9963.15
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 insert into customer select * from r;
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
+Customer#000000001	711.56
+Customer#000000002	121.65
 Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
+Customer#000000014	5266.3
 Customer#000000016	4681.03
 Customer#000000017	6.34
+Customer#000000019	8914.71
 Customer#000000022	591.98
 Customer#000000023	3332.02
 Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
 Customer#000000032	3471.53
+Customer#000000034	8589.7
 Customer#000000035	1228.24
 Customer#000000037	-917.75
 Customer#000000038	6345.11
 Customer#000000040	1335.3
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
 Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
 Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
 Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
 Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
 Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
 Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
 Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
 Customer#000000116	8403.99
 Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
 Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 drop table t,r;
 deallocate prepare stmt;
 # Materialization SJM PS
 # ======================
 prepare stmt from "
 delete from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08') and c_acctbal between ? and ?;
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1) and c_acctbal between ? and ?;
 ";
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-01-09' and '1992-03-08'
@@ -1795,6 +2378,7 @@ o_orderkey	o_totalprice
 3142	16030.15
 5095	184583.99
 5121	150334.57
+5382	138423.03
 644	201268.06
 737	12984.85
 create table t as
@@ -1811,6 +2395,7 @@ o_orderkey	o_totalprice
 1729	12137.76
 2880	145761.99
 3142	16030.15
+5382	138423.03
 644	201268.06
 737	12984.85
 insert into orders select * from t;
@@ -1824,6 +2409,7 @@ o_orderkey	o_totalprice
 3142	16030.15
 5095	184583.99
 5121	150334.57
+5382	138423.03
 644	201268.06
 737	12984.85
 create table r as
@@ -1841,6 +2427,7 @@ o_orderkey	o_totalprice
 2880	145761.99
 3142	16030.15
 5121	150334.57
+5382	138423.03
 737	12984.85
 insert into orders select * from r;
 select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
@@ -1853,12 +2440,14 @@ o_orderkey	o_totalprice
 3142	16030.15
 5095	184583.99
 5121	150334.57
+5382	138423.03
 644	201268.06
 737	12984.85
 drop table t,r;
 drop procedure p;
 # FirstMatch SP
 # =============
+set optimizer_switch='materialization=off';
 create procedure p(a int)
 delete from customer where c_nationkey in (select n_nationkey from nation
 where n_regionkey in (1,2))
@@ -1873,10 +2462,13 @@ where o_orderDATE between '1992-10-09' and '1993-06-08');
 c_name	c_acctbal
 Customer#000000007	9561.95
 Customer#000000008	6819.74
+Customer#000000017	6.34
 Customer#000000019	8914.71
+Customer#000000022	591.98
 Customer#000000025	7133.7
 Customer#000000028	1007.18
 Customer#000000037	-917.75
+Customer#000000040	1335.3
 Customer#000000047	274.58
 Customer#000000059	3458.6
 Customer#000000061	1536.24
@@ -1913,8 +2505,11 @@ and
 c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-10-09' and '1993-06-08');
 c_name	c_acctbal
+Customer#000000017	6.34
+Customer#000000022	591.98
 Customer#000000028	1007.18
 Customer#000000037	-917.75
+Customer#000000040	1335.3
 Customer#000000047	274.58
 Customer#000000059	3458.6
 Customer#000000061	1536.24
@@ -1936,10 +2531,13 @@ where o_orderDATE between '1992-10-09' and '1993-06-08');
 c_name	c_acctbal
 Customer#000000007	9561.95
 Customer#000000008	6819.74
+Customer#000000017	6.34
 Customer#000000019	8914.71
+Customer#000000022	591.98
 Customer#000000025	7133.7
 Customer#000000028	1007.18
 Customer#000000037	-917.75
+Customer#000000040	1335.3
 Customer#000000047	274.58
 Customer#000000059	3458.6
 Customer#000000061	1536.24
@@ -1976,8 +2574,11 @@ and
 c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-10-09' and '1993-06-08');
 c_name	c_acctbal
+Customer#000000017	6.34
+Customer#000000022	591.98
 Customer#000000028	1007.18
 Customer#000000037	-917.75
+Customer#000000040	1335.3
 Customer#000000047	274.58
 Customer#000000061	1536.24
 Customer#000000064	-646.64
@@ -1993,10 +2594,13 @@ where o_orderDATE between '1992-10-09' and '1993-06-08');
 c_name	c_acctbal
 Customer#000000007	9561.95
 Customer#000000008	6819.74
+Customer#000000017	6.34
 Customer#000000019	8914.71
+Customer#000000022	591.98
 Customer#000000025	7133.7
 Customer#000000028	1007.18
 Customer#000000037	-917.75
+Customer#000000040	1335.3
 Customer#000000047	274.58
 Customer#000000059	3458.6
 Customer#000000061	1536.24
@@ -2022,81 +2626,304 @@ Customer#000000139	7897.78
 Customer#000000142	2209.81
 drop table t,r;
 drop procedure p;
+set optimizer_switch='materialization=default';
 # Materialization SP
 # ==================
 create procedure p()
 delete from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
+Customer#000000001	711.56
+Customer#000000002	121.65
+Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
+Customer#000000014	5266.3
 Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000023	3332.02
 Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
 Customer#000000032	3471.53
+Customer#000000034	8589.7
+Customer#000000035	1228.24
 Customer#000000037	-917.75
 Customer#000000038	6345.11
+Customer#000000040	1335.3
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
 Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
 Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
 Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
 Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
 Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
+Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
+Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
 Customer#000000116	8403.99
 Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 create table t as
 select * from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 call p();
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
 insert into customer select * from t;
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
+Customer#000000001	711.56
+Customer#000000002	121.65
+Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
+Customer#000000014	5266.3
 Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000023	3332.02
 Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
 Customer#000000032	3471.53
+Customer#000000034	8589.7
+Customer#000000035	1228.24
 Customer#000000037	-917.75
 Customer#000000038	6345.11
+Customer#000000040	1335.3
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
 Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
 Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
 Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
 Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
 Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
+Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
+Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
 Customer#000000116	8403.99
 Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 create table r as
 select * from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 call p();
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
 insert into customer select * from r;
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
+Customer#000000001	711.56
+Customer#000000002	121.65
+Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
+Customer#000000014	5266.3
 Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000023	3332.02
 Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
 Customer#000000032	3471.53
+Customer#000000034	8589.7
+Customer#000000035	1228.24
 Customer#000000037	-917.75
 Customer#000000038	6345.11
+Customer#000000040	1335.3
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
 Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
 Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
 Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
 Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
 Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
+Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
+Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
 Customer#000000116	8403.99
 Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 drop table t,r;
 drop procedure p;
 # Materialization SJM SP
@@ -2158,72 +2985,294 @@ drop procedure p;
 # ====================
 # Check for DELETE ... RETURNING with SJ subquery in WHERE
 select c_name from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name
+Customer#000000001
+Customer#000000002
+Customer#000000005
+Customer#000000007
+Customer#000000008
+Customer#000000010
+Customer#000000011
 Customer#000000013
+Customer#000000014
 Customer#000000016
+Customer#000000017
+Customer#000000019
+Customer#000000022
+Customer#000000023
 Customer#000000025
+Customer#000000028
+Customer#000000029
+Customer#000000031
 Customer#000000032
+Customer#000000034
+Customer#000000035
 Customer#000000037
 Customer#000000038
+Customer#000000040
+Customer#000000041
+Customer#000000043
+Customer#000000044
+Customer#000000046
+Customer#000000047
+Customer#000000049
 Customer#000000052
+Customer#000000053
+Customer#000000055
 Customer#000000056
+Customer#000000058
+Customer#000000059
+Customer#000000061
+Customer#000000062
+Customer#000000064
 Customer#000000065
+Customer#000000067
+Customer#000000070
+Customer#000000071
+Customer#000000073
+Customer#000000074
 Customer#000000076
+Customer#000000079
+Customer#000000080
+Customer#000000082
+Customer#000000083
+Customer#000000085
+Customer#000000086
+Customer#000000088
+Customer#000000089
 Customer#000000091
+Customer#000000092
+Customer#000000094
+Customer#000000095
+Customer#000000097
+Customer#000000098
+Customer#000000100
+Customer#000000101
+Customer#000000103
+Customer#000000104
+Customer#000000106
+Customer#000000107
+Customer#000000109
+Customer#000000110
+Customer#000000112
 Customer#000000115
 Customer#000000116
 Customer#000000118
+Customer#000000121
+Customer#000000122
+Customer#000000127
+Customer#000000128
+Customer#000000130
+Customer#000000131
+Customer#000000133
+Customer#000000134
+Customer#000000136
+Customer#000000137
+Customer#000000139
 Customer#000000140
+Customer#000000142
+Customer#000000143
+Customer#000000145
+Customer#000000148
+Customer#000000149
 create table t as
 select * from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 explain
 delete from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08') returning c_name;
+where o_orderDATE between '1992-01-09' and '1993-03-08') returning c_name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	customer	ALL	NULL	NULL	NULL	NULL	141	Using where
-2	DEPENDENT SUBQUERY	orders	index_subquery|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	func	175 (2%)	Using where; Using rowid filter
+1	PRIMARY	customer	ALL	NULL	NULL	NULL	NULL	150	Using where
+2	DEPENDENT SUBQUERY	orders	index_subquery	i_o_orderdate,i_o_custkey	i_o_custkey	5	func	15	Using where
 delete from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08') returning c_name;
+where o_orderDATE between '1992-01-09' and '1993-03-08') returning c_name;
 c_name
+Customer#000000001
+Customer#000000002
+Customer#000000005
+Customer#000000007
+Customer#000000008
+Customer#000000010
+Customer#000000011
 Customer#000000013
+Customer#000000014
 Customer#000000016
+Customer#000000017
+Customer#000000019
+Customer#000000022
+Customer#000000023
 Customer#000000025
+Customer#000000028
+Customer#000000029
+Customer#000000031
 Customer#000000032
+Customer#000000034
+Customer#000000035
 Customer#000000037
 Customer#000000038
+Customer#000000040
+Customer#000000041
+Customer#000000043
+Customer#000000044
+Customer#000000046
+Customer#000000047
+Customer#000000049
 Customer#000000052
+Customer#000000053
+Customer#000000055
 Customer#000000056
+Customer#000000058
+Customer#000000059
+Customer#000000061
+Customer#000000062
+Customer#000000064
 Customer#000000065
+Customer#000000067
+Customer#000000070
+Customer#000000071
+Customer#000000073
+Customer#000000074
 Customer#000000076
+Customer#000000079
+Customer#000000080
+Customer#000000082
+Customer#000000083
+Customer#000000085
+Customer#000000086
+Customer#000000088
+Customer#000000089
 Customer#000000091
+Customer#000000092
+Customer#000000094
+Customer#000000095
+Customer#000000097
+Customer#000000098
+Customer#000000100
+Customer#000000101
+Customer#000000103
+Customer#000000104
+Customer#000000106
+Customer#000000107
+Customer#000000109
+Customer#000000110
+Customer#000000112
 Customer#000000115
 Customer#000000116
 Customer#000000118
+Customer#000000121
+Customer#000000122
+Customer#000000127
+Customer#000000128
+Customer#000000130
+Customer#000000131
+Customer#000000133
+Customer#000000134
+Customer#000000136
+Customer#000000137
+Customer#000000139
 Customer#000000140
+Customer#000000142
+Customer#000000143
+Customer#000000145
+Customer#000000148
+Customer#000000149
 select c_name from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name
 insert into customer select * from t;
 select c_name from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name
+Customer#000000001
+Customer#000000002
+Customer#000000005
+Customer#000000007
+Customer#000000008
+Customer#000000010
+Customer#000000011
 Customer#000000013
+Customer#000000014
 Customer#000000016
+Customer#000000017
+Customer#000000019
+Customer#000000022
+Customer#000000023
 Customer#000000025
+Customer#000000028
+Customer#000000029
+Customer#000000031
 Customer#000000032
+Customer#000000034
+Customer#000000035
 Customer#000000037
 Customer#000000038
+Customer#000000040
+Customer#000000041
+Customer#000000043
+Customer#000000044
+Customer#000000046
+Customer#000000047
+Customer#000000049
 Customer#000000052
+Customer#000000053
+Customer#000000055
 Customer#000000056
+Customer#000000058
+Customer#000000059
+Customer#000000061
+Customer#000000062
+Customer#000000064
 Customer#000000065
+Customer#000000067
+Customer#000000070
+Customer#000000071
+Customer#000000073
+Customer#000000074
 Customer#000000076
+Customer#000000079
+Customer#000000080
+Customer#000000082
+Customer#000000083
+Customer#000000085
+Customer#000000086
+Customer#000000088
+Customer#000000089
 Customer#000000091
+Customer#000000092
+Customer#000000094
+Customer#000000095
+Customer#000000097
+Customer#000000098
+Customer#000000100
+Customer#000000101
+Customer#000000103
+Customer#000000104
+Customer#000000106
+Customer#000000107
+Customer#000000109
+Customer#000000110
+Customer#000000112
 Customer#000000115
 Customer#000000116
 Customer#000000118
+Customer#000000121
+Customer#000000122
+Customer#000000127
+Customer#000000128
+Customer#000000130
+Customer#000000131
+Customer#000000133
+Customer#000000134
+Customer#000000136
+Customer#000000137
+Customer#000000139
 Customer#000000140
+Customer#000000142
+Customer#000000143
+Customer#000000145
+Customer#000000148
+Customer#000000149
 drop table t;
 select c_name from customer where c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-01-09' and '1992-03-08'
@@ -2243,7 +3292,7 @@ delete from customer where c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-01-09' and '1992-03-08'
                       group by o_custkey having count(o_custkey) > 1) returning c_name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	customer	ALL	NULL	NULL	NULL	NULL	141	Using where
+1	PRIMARY	customer	ALL	NULL	NULL	NULL	NULL	150	Using where
 2	DEPENDENT SUBQUERY	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	28	Using index condition; Using temporary
 delete from customer where c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-01-09' and '1992-03-08'
@@ -2269,64 +3318,16 @@ Customer#000000037
 Customer#000000056
 Customer#000000118
 drop table t;
-# Check for DELETE ... RETURNING with SJ subquery in WHERE
-select c_name from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
-c_name
-Customer#000000013
-Customer#000000016
-Customer#000000025
-Customer#000000032
-Customer#000000037
-Customer#000000038
-Customer#000000052
-Customer#000000056
-Customer#000000065
-Customer#000000076
-Customer#000000091
-Customer#000000115
-Customer#000000116
-Customer#000000118
-Customer#000000140
-create table t as
-select * from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
-explain
-delete from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08') returning c_name;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	customer	ALL	NULL	NULL	NULL	NULL	141	Using where
-2	DEPENDENT SUBQUERY	orders	index_subquery|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	func	175 (2%)	Using where; Using rowid filter
-delete from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08') returning c_name;
-c_name
-Customer#000000013
-Customer#000000016
-Customer#000000025
-Customer#000000032
-Customer#000000037
-Customer#000000038
-Customer#000000052
-Customer#000000056
-Customer#000000065
-Customer#000000076
-Customer#000000091
-Customer#000000115
-Customer#000000116
-Customer#000000118
-Customer#000000140
-select c_name from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
-c_name
-insert into customer select * from t;
-drop table t;
 # Check for DELETE ... ORDER BY ...LIMIT  with SJ subquery in WHERE
 select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
 o_custkey in (select c_custkey from customer
 where c_nationkey in (1,2));
 o_orderkey	o_totalprice
 1221	117397.16
+1344	43809.37
 1856	189361.42
+1925	146382.71
+3139	40975.96
 324	26868.85
 4903	34363.63
 5607	24660.06
@@ -2338,18 +3339,21 @@ where c_nationkey in (1,2))
 order by o_totalprice limit 500;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	108	Using where; Using filesort
-2	DEPENDENT SUBQUERY	customer	unique_subquery|filter	PRIMARY,i_c_nationkey	PRIMARY|i_c_nationkey	4|5	func	1 (10%)	Using where; Using rowid filter
+2	DEPENDENT SUBQUERY	customer	unique_subquery	PRIMARY,i_c_nationkey	PRIMARY	4	func	1	Using where
 create table t as
 select * from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
 o_custkey in (select c_custkey from customer
 where c_nationkey in (1,2));
 select o_orderkey, o_totalprice from t;
 o_orderkey	o_totalprice
+1221	117397.16
 324	26868.85
 1856	189361.42
-1221	117397.16
 4903	34363.63
 5607	24660.06
+1344	43809.37
+1925	146382.71
+3139	40975.96
 delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
 o_custkey in (select c_custkey from customer
 where c_nationkey in (1,2))
@@ -2364,7 +3368,10 @@ o_custkey in (select c_custkey from customer
 where c_nationkey in (1,2));
 o_orderkey	o_totalprice
 1221	117397.16
+1344	43809.37
 1856	189361.42
+1925	146382.71
+3139	40975.96
 324	26868.85
 4903	34363.63
 5607	24660.06
@@ -2375,19 +3382,22 @@ delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
 o_custkey in (select c_custkey from customer
 where c_nationkey in (1,2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	customer	range	PRIMARY,i_c_nationkey	i_c_nationkey	5	NULL	14	Using index condition
-1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	12 (7%)	Using where; Using rowid filter
+1	PRIMARY	customer	range	PRIMARY,i_c_nationkey	i_c_nationkey	5	NULL	13	Using index condition
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
 create table t as
 select * from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
 o_custkey in (select c_custkey from customer
 where c_nationkey in (1,2));
 select o_orderkey, o_totalprice from t;
 o_orderkey	o_totalprice
-1856	189361.42
-324	26868.85
 1221	117397.16
+324	26868.85
+1856	189361.42
 4903	34363.63
 5607	24660.06
+1344	43809.37
+1925	146382.71
+3139	40975.96
 delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
 o_custkey in (select c_custkey from customer
 where c_nationkey in (1,2));
@@ -2401,7 +3411,10 @@ o_custkey in (select c_custkey from customer
 where c_nationkey in (1,2));
 o_orderkey	o_totalprice
 1221	117397.16
+1344	43809.37
 1856	189361.42
+1925	146382.71
+3139	40975.96
 324	26868.85
 4903	34363.63
 5607	24660.06
diff --git a/mysql-test/main/delete_single_to_multi.test b/mysql-test/main/delete_single_to_multi.test
index 2c49128c67b..2829ae125c2 100644
--- a/mysql-test/main/delete_single_to_multi.test
+++ b/mysql-test/main/delete_single_to_multi.test
@@ -15,7 +15,9 @@ use dbt3_s001;
 --enable_query_log
 
 create index i_n_name on nation(n_name);
-analyze table nation;
+analyze table
+  nation, lineitem, customer, orders, part, supplier, partsupp, region
+persistent for all;
 
 
 --echo # Pullout
@@ -242,16 +244,18 @@ drop table t;
 --echo # Materialization
 --echo # ===============
 
+set optimizer_switch='firstmatch=off';
+
 let $c7=
+        c_nationkey in (select n_nationkey from nation where
+           n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
+        and
         c_custkey in (select o_custkey from orders
-                      where o_orderDATE between '1992-01-09' and '1992-03-08');
+                      where o_orderDATE between "1992-01-09" and "1995-01-08");
 
 eval
 explain
 select c_name, c_acctbal from customer where $c7;
-eval
-explain format=json
-select c_name, c_acctbal from customer where $c7;
 --sorted_result
 eval
 select c_name, c_acctbal from customer where $c7;
@@ -263,9 +267,6 @@ eval
 explain
 delete from customer where $c7;
 eval
-explain format=json
-delete from customer where $c7;
-eval
 delete from customer where $c7;
 eval
 select c_name, c_acctbal from customer where $c7;
@@ -276,14 +277,18 @@ eval
 select c_name, c_acctbal from customer where $c7;
 drop table t;
 
+set optimizer_switch='firstmatch=default';
 
 let $c8=
         c_custkey in (select o_custkey from orders
-                      where o_orderDATE between '1992-06-09' and '1993-01-08');
+                      where o_orderDATE between '1992-01-09' and '1993-03-08');
 
 eval
 explain
 select c_name, c_acctbal from customer where $c8;
+eval
+explain format=json
+select c_name, c_acctbal from customer where $c8;
 --sorted_result
 eval
 select c_name, c_acctbal from customer where $c8;
@@ -295,6 +300,9 @@ eval
 explain
 delete from customer where $c8;
 eval
+explain format=json
+delete from customer where $c8;
+eval
 delete from customer where $c8;
 eval
 select c_name, c_acctbal from customer where $c8;
@@ -306,20 +314,13 @@ select c_name, c_acctbal from customer where $c8;
 drop table t;
 
 
---echo # Materialization SJM
---echo # ===================
-
 let $c9=
         c_custkey in (select o_custkey from orders
-                        where o_orderDATE between '1992-01-09' and '1992-03-08'
-                      group by o_custkey having count(o_custkey) > 1);
+                      where o_orderDATE between '1992-06-09' and '1993-01-08');
 
 eval
 explain
 select c_name, c_acctbal from customer where $c9;
-eval
-explain format=json
-select c_name, c_acctbal from customer where $c9;
 --sorted_result
 eval
 select c_name, c_acctbal from customer where $c9;
@@ -331,9 +332,6 @@ eval
 explain
 delete from customer where $c9;
 eval
-explain format=json
-delete from customer where $c9;
-eval
 delete from customer where $c9;
 eval
 select c_name, c_acctbal from customer where $c9;
@@ -345,14 +343,20 @@ select c_name, c_acctbal from customer where $c9;
 drop table t;
 
 
+--echo # Materialization SJM
+--echo # ===================
+
 let $c10=
         c_custkey in (select o_custkey from orders
-                        where o_orderDATE between '1992-01-09' and '1993-03-08'
-                      group by o_custkey having count(o_custkey) > 5);
+                        where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
 
 eval
 explain
 select c_name, c_acctbal from customer where $c10;
+eval
+explain format=json
+select c_name, c_acctbal from customer where $c10;
 --sorted_result
 eval
 select c_name, c_acctbal from customer where $c10;
@@ -364,6 +368,9 @@ eval
 explain
 delete from customer where $c10;
 eval
+explain format=json
+delete from customer where $c10;
+eval
 delete from customer where $c10;
 eval
 select c_name, c_acctbal from customer where $c10;
@@ -375,6 +382,36 @@ select c_name, c_acctbal from customer where $c10;
 drop table t;
 
 
+let $c11=
+        c_custkey in (select o_custkey from orders
+                        where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+
+eval
+explain
+select c_name, c_acctbal from customer where $c11;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c11;
+eval
+create table t as
+select * from customer where $c11;
+
+eval
+explain
+delete from customer where $c11;
+eval
+delete from customer where $c11;
+eval
+select c_name, c_acctbal from customer where $c11;
+
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c11;
+drop table t;
+
+
 --echo # Pullout PS
 --echo # ==========
 
@@ -416,6 +453,8 @@ deallocate prepare stmt;
 --echo # FirstMatch PS
 --echo # =============
 
+set optimizer_switch='materialization=off';
+
 eval
 prepare stmt from "
 delete from customer where $c5;
@@ -450,42 +489,43 @@ drop table t,r;
 
 deallocate prepare stmt;
 
+set optimizer_switch='materialization=default';
 
 --echo # Materialization PS
 --echo # ==================
 
 eval
 prepare stmt from "
-delete from customer where $c7 and c_name like ?;
+delete from customer where $c8 and c_name like ?;
 ";
 
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 set @a1='Customer#%1_';
 eval
 create table t as
-select * from customer where $c7 and c_name like @a1;
+select * from customer where $c8 and c_name like @a1;
 execute stmt using @a1;
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 insert into customer select * from t;
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 set @a2='Customer#%3_';
 eval
 create table r as
-select * from customer where $c7 and c_name like @a2;
+select * from customer where $c8 and c_name like @a2;
 execute stmt using @a2;
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 insert into customer select * from r;
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 drop table t,r;
 
 deallocate prepare stmt;
@@ -496,38 +536,38 @@ deallocate prepare stmt;
 
 eval
 prepare stmt from "
-delete from customer where $c7 and c_acctbal between ? and ?;
+delete from customer where $c10 and c_acctbal between ? and ?;
 ";
 
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 set @a1=3500;
 set @a2=4000;
 eval
 create table t as
-select * from customer where $c9 and c_acctbal between @a1 and @a2;
+select * from customer where $c10 and c_acctbal between @a1 and @a2;
 execute stmt using @a1, @a2;
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 insert into customer select * from t;
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 set @a3=-1000;
 set @a4=3500;
 eval
 create table r as
-select * from customer where $c9 and c_acctbal between @a3 and @a4;
+select * from customer where $c10 and c_acctbal between @a3 and @a4;
 execute stmt using @a3, @a4;
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 insert into customer select * from r;
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 drop table t,r;
 
 deallocate prepare stmt;
@@ -573,6 +613,8 @@ drop procedure p;
 --echo # FirstMatch SP
 --echo # =============
 
+set optimizer_switch='materialization=off';
+
 eval
 create procedure p(a int)
 delete from customer where $c5 and c_acctbal > a;
@@ -606,39 +648,41 @@ drop table t,r;
 
 drop procedure p;
 
+set optimizer_switch='materialization=default';
+
 
 --echo # Materialization SP
 --echo # ==================
 
 eval
 create procedure p()
-delete from customer where $c7;
+delete from customer where $c8;
 
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 eval
 create table t as
-select * from customer where $c7;
+select * from customer where $c8;
 call p();
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 insert into customer select * from t;
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 eval
 create table r as
-select * from customer where $c7;
+select * from customer where $c8;
 call p();
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 insert into customer select * from r;
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 drop table t,r;
 
 drop procedure p;
@@ -649,33 +693,33 @@ drop procedure p;
 
 eval
 create procedure p()
-delete from customer where $c9;
+delete from customer where $c10;
 
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 eval
 create table t as
-select * from customer where $c9;
+select * from customer where $c10;
 call p();
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 insert into customer select * from t;
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 eval
 create table r as
-select * from customer where $c9;
+select * from customer where $c10;
 call p();
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 insert into customer select * from r;
 --sorted_result
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 drop table t,r;
 
 drop procedure p;
@@ -687,64 +731,44 @@ drop procedure p;
 
 --sorted_result
 eval
-select c_name from customer where $c7;
+select c_name from customer where $c8;
 eval
 create table t as
-select * from customer where $c7;
+select * from customer where $c8;
 eval
 explain
-delete from customer where $c7 returning c_name;
+delete from customer where $c8 returning c_name;
 --sorted_result
 eval
-delete from customer where $c7 returning c_name;
+delete from customer where $c8 returning c_name;
 --sorted_result
 eval
-select c_name from customer where $c7;
+select c_name from customer where $c8;
 insert into customer select * from t;
 --sorted_result
 eval
-select c_name from customer where $c7;
+select c_name from customer where $c8;
 drop table t;
 
 --sorted_result
 eval
-select c_name from customer where $c9;
+select c_name from customer where $c10;
 eval
 create table t as
-select * from customer where $c9;
+select * from customer where $c10;
 eval
 explain
-delete from customer where $c9 returning c_name;
+delete from customer where $c10 returning c_name;
 --sorted_result
 eval
-delete from customer where $c9 returning c_name;
+delete from customer where $c10 returning c_name;
 --sorted_result
 eval
-select c_name from customer where $c9;
+select c_name from customer where $c10;
 insert into customer select * from t;
 --sorted_result
 eval
-select c_name from customer where $c9;
-drop table t;
-
---echo # Check for DELETE ... RETURNING with SJ subquery in WHERE
-
---sorted_result
-eval
-select c_name from customer where $c7;
-eval
-create table t as
-select * from customer where $c7;
-eval
-explain
-delete from customer where $c7 returning c_name;
---sorted_result
-eval
-delete from customer where $c7 returning c_name;
---sorted_result
-eval
-select c_name from customer where $c7;
-insert into customer select * from t;
+select c_name from customer where $c10;
 drop table t;
 
 --echo # Check for DELETE ... ORDER BY ...LIMIT  with SJ subquery in WHERE
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 7523e2883f7..fd3530b1688 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -13134,20 +13134,7 @@ EXPLAIN
           "materialized": {
             "query_block": {
               "select_id": 3,
-<<<<<<< 2ad65c4dbcb291867725d50d1f53c8da8549afb3
               "cost": "COST_REPLACED",
-              "nested_loop": [
-                {
-                  "table": {
-                    "table_name": "t1",
-                    "access_type": "ALL",
-                    "loops": 1,
-                    "rows": 2,
-                    "cost": "COST_REPLACED",
-                    "filtered": 100,
-                    "attached_condition": "t1.f2 < 2"
-                  }
-=======
               "filesort": {
                 "sort_key": "t1.f2",
                 "temporary_table": {
@@ -13156,7 +13143,9 @@ EXPLAIN
                       "table": {
                         "table_name": "t1",
                         "access_type": "ALL",
+                        "loops": 1,
                         "rows": 2,
+                        "cost": "COST_REPLACED",
                         "filtered": 100,
                         "attached_condition": "t1.f2 < 2"
                       }
diff --git a/mysql-test/main/log_state.result b/mysql-test/main/log_state.result
index 1b1c7376f18..18c8da708a9 100644
--- a/mysql-test/main/log_state.result
+++ b/mysql-test/main/log_state.result
@@ -243,7 +243,7 @@ rows_examined	sql_text
 4	UPDATE t1 SET a=a+sleep(.02) WHERE a>2
 8	UPDATE t1 SET a=a+sleep(.02) ORDER BY a DESC
 1	UPDATE t2 set b=b+sleep(.02) limit 1
-10	UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2)
+6	UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2)
 6	DELETE FROM t1 WHERE a=a+sleep(.02) ORDER BY a LIMIT 2
 disconnect con2;
 connection default;
diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result
index e5ed031c6fb..2364ee732ce 100644
--- a/mysql-test/main/multi_update.result
+++ b/mysql-test/main/multi_update.result
@@ -1270,6 +1270,7 @@ DROP TABLES t1, t2;
 # End of 10.3 tests
 #
 # MDEV-30538: multi-table UPDATE/DELETE with possible exists-to-in
+#
 create table t1 (c1 int, c2 int, c3 int, index idx(c2));
 insert into t1 values
 (1,1,1),(3,2,2),(1,3,3),
diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result
index e91c4fffde2..8b2979b5154 100644
--- a/mysql-test/main/myisam_explain_non_select_all.result
+++ b/mysql-test/main/myisam_explain_non_select_all.result
@@ -218,16 +218,14 @@ INSERT INTO t2 VALUES (1), (2), (3);
 #
 EXPLAIN UPDATE        t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; FirstMatch
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	33.33	Using where; FirstMatch
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -248,9 +246,9 @@ Handler_read_key	4
 Handler_read_rnd_next	5
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_key	5
+Handler_read_key	4
 Handler_read_rnd	3
-Handler_read_rnd_next	12
+Handler_read_rnd_next	9
 Handler_update	3
 
 DROP TABLE t1, t2;
@@ -904,15 +902,13 @@ INSERT INTO t2 VALUES (1), (2), (3), (1000);
 EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where; FirstMatch(t1)
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; FirstMatch(t1)
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -933,8 +929,8 @@ Handler_read_key	4
 Handler_read_rnd_next	9
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_key	7
-Handler_read_rnd_next	8
+Handler_read_key	4
+Handler_read_rnd_next	10
 Handler_update	3
 
 DROP TABLE t1, t2;
@@ -2828,14 +2824,14 @@ INSERT INTO t2 VALUES (1), (2), (3);
 EXPLAIN UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-1	PRIMARY	<derived3>	ref	key0	key0	5	test.t1.a	2	FirstMatch(t1)
+1	PRIMARY	<derived3>	eq_ref	distinct_key	distinct_key	5	test.t1.a	1	
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using filesort
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<derived3>	ref	key0	key0	5	test.t1.a	2	100.00	FirstMatch(t1)
+1	PRIMARY	<derived3>	eq_ref	distinct_key	distinct_key	5	test.t1.a	1	100.00	
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 0750d240aaf..634ef3343a4 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -4471,7 +4471,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
                 "rowid_filters": [
                   {
                     "key": "a",
-                    "build_cost": 0.174715752,
+                    "build_cost": 0.001129926,
                     "rows": 3
                   }
                 ]
@@ -4546,7 +4546,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
                 "rowid_filters": [
                   {
                     "key": "a",
-                    "build_cost": 0.174715752,
+                    "build_cost": 0.001129926,
                     "rows": 3
                   }
                 ]
diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result
index 9e486179719..e0cf1e61d95 100644
--- a/mysql-test/main/subselect.result
+++ b/mysql-test/main/subselect.result
@@ -634,6 +634,18 @@ a	b
 22	11
 2	12
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
+select * from t11;
+a	b
+0	10
+1	11
+select * from t12;
+a	b
+33	10
+22	11
+delete from t11;
+delete from t12;
+insert into t11 values (0, 10),(1, 11),(2, 12);
+insert into t12 values (33, 10),(22, 11),(2, 12);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test
index 8fefa39fb3b..578dbe86406 100644
--- a/mysql-test/main/subselect.test
+++ b/mysql-test/main/subselect.test
@@ -387,6 +387,12 @@ insert into t2 values (1, 21),(2, 12),(3, 23);
 select * from t11;
 select * from t12;
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
+select * from t11;
+select * from t12;
+delete from t11;
+delete from t12;
+insert into t11 values (0, 10),(1, 11),(2, 12);
+insert into t12 values (33, 10),(22, 11),(2, 12);
 -- error ER_SUBQUERY_NO_1_ROW
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result
index 37a503dff6b..3be4f07b8ca 100644
--- a/mysql-test/main/subselect_no_exists_to_in.result
+++ b/mysql-test/main/subselect_no_exists_to_in.result
@@ -638,6 +638,18 @@ a	b
 22	11
 2	12
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
+select * from t11;
+a	b
+0	10
+1	11
+select * from t12;
+a	b
+33	10
+22	11
+delete from t11;
+delete from t12;
+insert into t11 values (0, 10),(1, 11),(2, 12);
+insert into t12 values (33, 10),(22, 11),(2, 12);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result
index 3832365ed86..a47e2977774 100644
--- a/mysql-test/main/subselect_no_mat.result
+++ b/mysql-test/main/subselect_no_mat.result
@@ -641,6 +641,18 @@ a	b
 22	11
 2	12
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
+select * from t11;
+a	b
+0	10
+1	11
+select * from t12;
+a	b
+33	10
+22	11
+delete from t11;
+delete from t12;
+insert into t11 values (0, 10),(1, 11),(2, 12);
+insert into t12 values (33, 10),(22, 11),(2, 12);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result
index d75c421e3aa..10153b0f60c 100644
--- a/mysql-test/main/subselect_no_opts.result
+++ b/mysql-test/main/subselect_no_opts.result
@@ -637,6 +637,18 @@ a	b
 22	11
 2	12
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
+select * from t11;
+a	b
+0	10
+1	11
+select * from t12;
+a	b
+33	10
+22	11
+delete from t11;
+delete from t12;
+insert into t11 values (0, 10),(1, 11),(2, 12);
+insert into t12 values (33, 10),(22, 11),(2, 12);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result
index 3bce10e803c..456d1dec846 100644
--- a/mysql-test/main/subselect_no_scache.result
+++ b/mysql-test/main/subselect_no_scache.result
@@ -640,6 +640,18 @@ a	b
 22	11
 2	12
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
+select * from t11;
+a	b
+0	10
+1	11
+select * from t12;
+a	b
+33	10
+22	11
+delete from t11;
+delete from t12;
+insert into t11 values (0, 10),(1, 11),(2, 12);
+insert into t12 values (33, 10),(22, 11),(2, 12);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result
index c34dfe69ea9..eba0f55a072 100644
--- a/mysql-test/main/subselect_no_semijoin.result
+++ b/mysql-test/main/subselect_no_semijoin.result
@@ -637,6 +637,18 @@ a	b
 22	11
 2	12
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
+select * from t11;
+a	b
+0	10
+1	11
+select * from t12;
+a	b
+33	10
+22	11
+delete from t11;
+delete from t12;
+insert into t11 values (0, 10),(1, 11),(2, 12);
+insert into t12 values (33, 10),(22, 11),(2, 12);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
diff --git a/mysql-test/main/update_single_to_multi.result b/mysql-test/main/update_single_to_multi.result
index cc28a0fab26..11969f7e072 100644
--- a/mysql-test/main/update_single_to_multi.result
+++ b/mysql-test/main/update_single_to_multi.result
@@ -2,10 +2,26 @@ DROP DATABASE IF EXISTS dbt3_s001;
 CREATE DATABASE dbt3_s001;
 use dbt3_s001;
 create index i_n_name on nation(n_name);
-analyze table nation;
+analyze table
+nation, lineitem, customer, orders, part, supplier, partsupp, region
+persistent for all;
 Table	Op	Msg_type	Msg_text
 dbt3_s001.nation	analyze	status	Engine-independent statistics collected
 dbt3_s001.nation	analyze	status	OK
+dbt3_s001.lineitem	analyze	status	Engine-independent statistics collected
+dbt3_s001.lineitem	analyze	status	OK
+dbt3_s001.customer	analyze	status	Engine-independent statistics collected
+dbt3_s001.customer	analyze	status	OK
+dbt3_s001.orders	analyze	status	Engine-independent statistics collected
+dbt3_s001.orders	analyze	status	OK
+dbt3_s001.part	analyze	status	Engine-independent statistics collected
+dbt3_s001.part	analyze	status	OK
+dbt3_s001.supplier	analyze	status	Engine-independent statistics collected
+dbt3_s001.supplier	analyze	status	OK
+dbt3_s001.partsupp	analyze	status	Engine-independent statistics collected
+dbt3_s001.partsupp	analyze	status	OK
+dbt3_s001.region	analyze	status	Engine-independent statistics collected
+dbt3_s001.region	analyze	status	OK
 # Pullout
 # =======
 explain
@@ -15,8 +31,8 @@ where c_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
-1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (7%)	Using where; Using rowid filter
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
 explain format=json
 select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
 o_custkey in (select c_custkey from customer
@@ -26,6 +42,7 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.052271677,
     "nested_loop": [
       {
         "table": {
@@ -36,7 +53,9 @@ EXPLAIN
           "key_length": "26",
           "used_key_parts": ["n_name"],
           "ref": ["const"],
+          "loops": 1,
           "rows": 1,
+          "cost": 0.002024411,
           "filtered": 100,
           "index_condition": "nation.n_name = 'PERU'"
         }
@@ -50,7 +69,9 @@ EXPLAIN
           "key_length": "5",
           "used_key_parts": ["c_nationkey"],
           "ref": ["dbt3_s001.nation.n_nationkey"],
-          "rows": 11,
+          "loops": 1,
+          "rows": 6,
+          "cost": 0.008193756,
           "filtered": 100
         }
       },
@@ -71,7 +92,9 @@ EXPLAIN
             "rows": 108,
             "selectivity_pct": 7.2
           },
-          "rows": 11,
+          "loops": 6,
+          "rows": 15,
+          "cost": 0.04205351,
           "filtered": 7.199999809,
           "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'"
         }
@@ -99,8 +122,8 @@ where c_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
-1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (7%)	Using where; Using rowid filter
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
 explain format=json
 update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
 o_custkey in (select c_custkey from customer
@@ -110,6 +133,7 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.052271677,
     "nested_loop": [
       {
         "table": {
@@ -120,7 +144,9 @@ EXPLAIN
           "key_length": "26",
           "used_key_parts": ["n_name"],
           "ref": ["const"],
+          "loops": 1,
           "rows": 1,
+          "cost": 0.002024411,
           "filtered": 100,
           "index_condition": "nation.n_name = 'PERU'"
         }
@@ -134,7 +160,9 @@ EXPLAIN
           "key_length": "5",
           "used_key_parts": ["c_nationkey"],
           "ref": ["dbt3_s001.nation.n_nationkey"],
-          "rows": 11,
+          "loops": 1,
+          "rows": 6,
+          "cost": 0.008193756,
           "filtered": 100
         }
       },
@@ -155,7 +183,9 @@ EXPLAIN
             "rows": 108,
             "selectivity_pct": 7.2
           },
-          "rows": 11,
+          "loops": 6,
+          "rows": 15,
+          "cost": 0.04205351,
           "filtered": 7.199999809,
           "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'"
         }
@@ -205,22 +235,22 @@ s_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
-1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
-1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	
 select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in
 (select p_partkey, s_suppkey from part, supplier
 where p_retailprice between 901 and 910 and
 s_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 ps_partkey	ps_suppkey	ps_supplycost
-4	1	444.37
-6	1	642.13
-8	1	957.34
 1	8	357.84
 3	8	645.4
+4	1	444.37
 5	8	50.52
+6	1	642.13
 7	8	763.98
+8	1	957.34
 explain
 update partsupp set ps_supplycost = ps_supplycost+2 where (ps_partkey, ps_suppkey) in
 (select p_partkey, s_suppkey from part, supplier
@@ -229,9 +259,9 @@ s_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
-1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
-1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where
+1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	
 update partsupp set ps_supplycost = ps_supplycost+2 where (ps_partkey, ps_suppkey) in
 (select p_partkey, s_suppkey from part, supplier
 where p_retailprice between 901 and 910 and
@@ -243,13 +273,13 @@ where p_retailprice between 901 and 910 and
 s_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 ps_partkey	ps_suppkey	ps_supplycost
-4	1	446.37
-6	1	644.13
-8	1	959.34
 1	8	359.84
 3	8	647.4
+4	1	446.37
 5	8	52.52
+6	1	644.13
 7	8	765.98
+8	1	959.34
 update partsupp set ps_supplycost = ps_supplycost-2 where (ps_partkey, ps_suppkey) in
 (select p_partkey, s_suppkey from part, supplier
 where p_retailprice between 901 and 910 and
@@ -261,13 +291,13 @@ where p_retailprice between 901 and 910 and
 s_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 ps_partkey	ps_suppkey	ps_supplycost
-4	1	444.37
-6	1	642.13
-8	1	957.34
 1	8	357.84
 3	8	645.4
+4	1	444.37
 5	8	50.52
+6	1	642.13
 7	8	763.98
+8	1	957.34
 explain
 select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part
 where p_retailprice between 901 and 910) and
@@ -276,22 +306,22 @@ where s_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
-1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
-1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	
 select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part
 where p_retailprice between 901 and 910) and
 ps_suppkey in (select s_suppkey from supplier
 where s_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 ps_partkey	ps_suppkey	ps_supplycost
-4	1	444.37
-6	1	642.13
-8	1	957.34
 1	8	357.84
 3	8	645.4
+4	1	444.37
 5	8	50.52
+6	1	642.13
 7	8	763.98
+8	1	957.34
 explain
 update partsupp set ps_supplycost = ps_supplycost+10 where ps_partkey in (select p_partkey from part
 where p_retailprice between 901 and 910) and
@@ -300,9 +330,9 @@ where s_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
-1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
-1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where
+1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	
 update partsupp set ps_supplycost = ps_supplycost+10 where ps_partkey in (select p_partkey from part
 where p_retailprice between 901 and 910) and
 ps_suppkey in (select s_suppkey from supplier
@@ -314,13 +344,13 @@ ps_suppkey in (select s_suppkey from supplier
 where s_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 ps_partkey	ps_suppkey	ps_supplycost
-4	1	454.37
-6	1	652.13
-8	1	967.34
 1	8	367.84
 3	8	655.4
+4	1	454.37
 5	8	60.52
+6	1	652.13
 7	8	773.98
+8	1	967.34
 update partsupp set ps_supplycost = ps_supplycost-10 where ps_partkey in (select p_partkey from part
 where p_retailprice between 901 and 910) and
 ps_suppkey in (select s_suppkey from supplier
@@ -332,13 +362,13 @@ ps_suppkey in (select s_suppkey from supplier
 where s_nationkey in (select n_nationkey from nation
 where n_name='PERU'));
 ps_partkey	ps_suppkey	ps_supplycost
-4	1	444.37
-6	1	642.13
-8	1	957.34
 1	8	357.84
 3	8	645.4
+4	1	444.37
 5	8	50.52
+6	1	642.13
 7	8	763.98
+8	1	957.34
 explain
 select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders
 where o_custkey in
@@ -357,11 +387,11 @@ where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
-1	PRIMARY	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey	5	dbt3_s001.supplier.s_suppkey	100	Using where
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
+1	PRIMARY	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
 1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
-1	PRIMARY	orders	eq_ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	PRIMARY|i_o_orderdate	4|4	dbt3_s001.lineitem.l_orderkey	1 (7%)	Using where; Using rowid filter
-1	PRIMARY	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
 select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders
 where o_custkey in
 (select c_custkey from customer
@@ -399,11 +429,11 @@ where n_name='PERU'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
-1	PRIMARY	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey	5	dbt3_s001.supplier.s_suppkey	100	Using where
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
+1	PRIMARY	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
 1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
-1	PRIMARY	orders	eq_ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	PRIMARY|i_o_orderdate	4|4	dbt3_s001.lineitem.l_orderkey	1 (7%)	Using where; Using rowid filter
-1	PRIMARY	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
 update lineitem set l_tax = (l_tax*100+1)/100 where l_orderkey in (select o_orderkey from orders
 where o_custkey in
 (select c_custkey from customer
@@ -480,9 +510,9 @@ and
 c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-10-09' and '1993-06-08');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	customer	ALL	PRIMARY,i_c_nationkey	NULL	NULL	NULL	150	Using where
-1	PRIMARY	nation	eq_ref|filter	PRIMARY,i_n_regionkey	PRIMARY|i_n_regionkey	4|5	dbt3_s001.customer.c_nationkey	1 (40%)	Using where; Using rowid filter
-1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (9%)	Using where; FirstMatch(nation); Using rowid filter
+1	PRIMARY	nation	ALL	PRIMARY,i_n_regionkey	NULL	NULL	NULL	25	Using where
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (9%)	Using where; FirstMatch(customer); Using rowid filter
 explain format=json
 select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
 where n_regionkey in (1,2))
@@ -493,37 +523,33 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.40015207,
     "nested_loop": [
       {
         "table": {
-          "table_name": "customer",
+          "table_name": "nation",
           "access_type": "ALL",
-          "possible_keys": ["PRIMARY", "i_c_nationkey"],
-          "rows": 150,
-          "filtered": 100,
-          "attached_condition": "customer.c_nationkey is not null"
+          "possible_keys": ["PRIMARY", "i_n_regionkey"],
+          "loops": 1,
+          "rows": 25,
+          "cost": 0.013945725,
+          "filtered": 40,
+          "attached_condition": "nation.n_regionkey in (1,2)"
         }
       },
       {
         "table": {
-          "table_name": "nation",
-          "access_type": "eq_ref",
-          "possible_keys": ["PRIMARY", "i_n_regionkey"],
-          "key": "PRIMARY",
-          "key_length": "4",
-          "used_key_parts": ["n_nationkey"],
-          "ref": ["dbt3_s001.customer.c_nationkey"],
-          "rowid_filter": {
-            "range": {
-              "key": "i_n_regionkey",
-              "used_key_parts": ["n_regionkey"]
-            },
-            "rows": 10,
-            "selectivity_pct": 40
-          },
-          "rows": 1,
-          "filtered": 40,
-          "attached_condition": "nation.n_regionkey in (1,2)"
+          "table_name": "customer",
+          "access_type": "ref",
+          "possible_keys": ["PRIMARY", "i_c_nationkey"],
+          "key": "i_c_nationkey",
+          "key_length": "5",
+          "used_key_parts": ["c_nationkey"],
+          "ref": ["dbt3_s001.nation.n_nationkey"],
+          "loops": 10,
+          "rows": 6,
+          "cost": 0.08009436,
+          "filtered": 100
         }
       },
       {
@@ -543,10 +569,12 @@ EXPLAIN
             "rows": 140,
             "selectivity_pct": 9.333333333
           },
-          "rows": 11,
-          "filtered": 9.333333015,
+          "loops": 60,
+          "rows": 15,
+          "cost": 0.306111985,
+          "filtered": 6.666666508,
           "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'",
-          "first_match": "nation"
+          "first_match": "customer"
         }
       }
     ]
@@ -558,38 +586,38 @@ and
 c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-10-09' and '1993-06-08');
 c_name	c_acctbal
-Customer#000000007	9561.95
-Customer#000000008	6819.74
+Customer#000000059	3458.6
+Customer#000000106	3288.42
 Customer#000000017	6.34
-Customer#000000019	8914.71
+Customer#000000047	274.58
+Customer#000000092	1182.91
+Customer#000000101	7470.96
 Customer#000000022	591.98
-Customer#000000025	7133.7
-Customer#000000028	1007.18
-Customer#000000037	-917.75
 Customer#000000040	1335.3
-Customer#000000047	274.58
-Customer#000000059	3458.6
-Customer#000000061	1536.24
 Customer#000000064	-646.64
-Customer#000000067	8166.59
-Customer#000000077	1738.87
-Customer#000000082	9468.34
+Customer#000000122	7865.46
+Customer#000000028	1007.18
+Customer#000000037	-917.75
 Customer#000000091	4643.14
-Customer#000000092	1182.91
+Customer#000000115	7508.92
+Customer#000000067	8166.59
 Customer#000000094	5500.11
-Customer#000000097	2164.48
-Customer#000000101	7470.96
 Customer#000000103	2757.45
-Customer#000000106	3288.42
-Customer#000000115	7508.92
-Customer#000000121	6428.32
-Customer#000000122	7865.46
-Customer#000000124	1842.49
-Customer#000000127	9280.71
 Customer#000000130	5073.58
-Customer#000000133	2314.67
 Customer#000000139	7897.78
 Customer#000000142	2209.81
+Customer#000000025	7133.7
+Customer#000000008	6819.74
+Customer#000000061	1536.24
+Customer#000000077	1738.87
+Customer#000000097	2164.48
+Customer#000000121	6428.32
+Customer#000000133	2314.67
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000082	9468.34
+Customer#000000124	1842.49
+Customer#000000127	9280.71
 explain
 update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation
 where n_regionkey in (1,2))
@@ -597,9 +625,9 @@ and
 c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-10-09' and '1993-06-08');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	customer	ALL	PRIMARY,i_c_nationkey	NULL	NULL	NULL	150	Using where
-1	PRIMARY	nation	eq_ref|filter	PRIMARY,i_n_regionkey	PRIMARY|i_n_regionkey	4|5	dbt3_s001.customer.c_nationkey	1 (40%)	Using where; Using rowid filter
-1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (9%)	Using where; FirstMatch(nation); Using rowid filter
+1	PRIMARY	nation	ALL	PRIMARY,i_n_regionkey	NULL	NULL	NULL	25	Using where
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (9%)	Using where; FirstMatch(customer); Using rowid filter
 explain format=json
 update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation
 where n_regionkey in (1,2))
@@ -610,37 +638,33 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.40015207,
     "nested_loop": [
       {
         "table": {
-          "table_name": "customer",
+          "table_name": "nation",
           "access_type": "ALL",
-          "possible_keys": ["PRIMARY", "i_c_nationkey"],
-          "rows": 150,
-          "filtered": 100,
-          "attached_condition": "customer.c_nationkey is not null"
+          "possible_keys": ["PRIMARY", "i_n_regionkey"],
+          "loops": 1,
+          "rows": 25,
+          "cost": 0.013945725,
+          "filtered": 40,
+          "attached_condition": "nation.n_regionkey in (1,2)"
         }
       },
       {
         "table": {
-          "table_name": "nation",
-          "access_type": "eq_ref",
-          "possible_keys": ["PRIMARY", "i_n_regionkey"],
-          "key": "PRIMARY",
-          "key_length": "4",
-          "used_key_parts": ["n_nationkey"],
-          "ref": ["dbt3_s001.customer.c_nationkey"],
-          "rowid_filter": {
-            "range": {
-              "key": "i_n_regionkey",
-              "used_key_parts": ["n_regionkey"]
-            },
-            "rows": 10,
-            "selectivity_pct": 40
-          },
-          "rows": 1,
-          "filtered": 40,
-          "attached_condition": "nation.n_regionkey in (1,2)"
+          "table_name": "customer",
+          "access_type": "ref",
+          "possible_keys": ["PRIMARY", "i_c_nationkey"],
+          "key": "i_c_nationkey",
+          "key_length": "5",
+          "used_key_parts": ["c_nationkey"],
+          "ref": ["dbt3_s001.nation.n_nationkey"],
+          "loops": 10,
+          "rows": 6,
+          "cost": 0.08009436,
+          "filtered": 100
         }
       },
       {
@@ -660,10 +684,12 @@ EXPLAIN
             "rows": 140,
             "selectivity_pct": 9.333333333
           },
-          "rows": 11,
-          "filtered": 9.333333015,
+          "loops": 60,
+          "rows": 15,
+          "cost": 0.306111985,
+          "filtered": 6.666666508,
           "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-06-08'",
-          "first_match": "nation"
+          "first_match": "customer"
         }
       }
     ]
@@ -680,38 +706,38 @@ and
 c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-10-09' and '1993-06-08');
 c_name	c_acctbal
-Customer#000000007	9571.95
-Customer#000000008	6829.74
+Customer#000000059	3468.6
+Customer#000000106	3298.42
 Customer#000000017	16.34
-Customer#000000019	8924.71
+Customer#000000047	284.58
+Customer#000000092	1192.91
+Customer#000000101	7480.96
 Customer#000000022	601.98
-Customer#000000025	7143.7
-Customer#000000028	1017.18
-Customer#000000037	-907.75
 Customer#000000040	1345.3
-Customer#000000047	284.58
-Customer#000000059	3468.6
-Customer#000000061	1546.24
 Customer#000000064	-636.64
-Customer#000000067	8176.59
-Customer#000000077	1748.87
-Customer#000000082	9478.34
+Customer#000000122	7875.46
+Customer#000000028	1017.18
+Customer#000000037	-907.75
 Customer#000000091	4653.14
-Customer#000000092	1192.91
+Customer#000000115	7518.92
+Customer#000000067	8176.59
 Customer#000000094	5510.11
-Customer#000000097	2174.48
-Customer#000000101	7480.96
 Customer#000000103	2767.45
-Customer#000000106	3298.42
-Customer#000000115	7518.92
-Customer#000000121	6438.32
-Customer#000000122	7875.46
-Customer#000000124	1852.49
-Customer#000000127	9290.71
 Customer#000000130	5083.58
-Customer#000000133	2324.67
 Customer#000000139	7907.78
 Customer#000000142	2219.81
+Customer#000000025	7143.7
+Customer#000000008	6829.74
+Customer#000000061	1546.24
+Customer#000000077	1748.87
+Customer#000000097	2174.48
+Customer#000000121	6438.32
+Customer#000000133	2324.67
+Customer#000000007	9571.95
+Customer#000000019	8924.71
+Customer#000000082	9478.34
+Customer#000000124	1852.49
+Customer#000000127	9290.71
 update customer set c_acctbal = c_acctbal-10 where c_nationkey in (select n_nationkey from nation
 where n_regionkey in (1,2))
 and
@@ -723,38 +749,38 @@ and
 c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-10-09' and '1993-06-08');
 c_name	c_acctbal
-Customer#000000007	9561.95
-Customer#000000008	6819.74
+Customer#000000059	3458.6
+Customer#000000106	3288.42
 Customer#000000017	6.34
-Customer#000000019	8914.71
+Customer#000000047	274.58
+Customer#000000092	1182.91
+Customer#000000101	7470.96
 Customer#000000022	591.98
-Customer#000000025	7133.7
-Customer#000000028	1007.18
-Customer#000000037	-917.75
 Customer#000000040	1335.3
-Customer#000000047	274.58
-Customer#000000059	3458.6
-Customer#000000061	1536.24
 Customer#000000064	-646.64
-Customer#000000067	8166.59
-Customer#000000077	1738.87
-Customer#000000082	9468.34
+Customer#000000122	7865.46
+Customer#000000028	1007.18
+Customer#000000037	-917.75
 Customer#000000091	4643.14
-Customer#000000092	1182.91
+Customer#000000115	7508.92
+Customer#000000067	8166.59
 Customer#000000094	5500.11
-Customer#000000097	2164.48
-Customer#000000101	7470.96
 Customer#000000103	2757.45
-Customer#000000106	3288.42
-Customer#000000115	7508.92
-Customer#000000121	6428.32
-Customer#000000122	7865.46
-Customer#000000124	1842.49
-Customer#000000127	9280.71
 Customer#000000130	5073.58
-Customer#000000133	2314.67
 Customer#000000139	7897.78
 Customer#000000142	2209.81
+Customer#000000025	7133.7
+Customer#000000008	6819.74
+Customer#000000061	1536.24
+Customer#000000077	1738.87
+Customer#000000097	2164.48
+Customer#000000121	6428.32
+Customer#000000133	2314.67
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000082	9468.34
+Customer#000000124	1842.49
+Customer#000000127	9280.71
 set optimizer_switch='materialization=default';
 explain
 select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
@@ -763,8 +789,8 @@ c_custkey in (select o_custkey from orders
 where o_orderDATE between "1992-01-09" and "1993-01-08");
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
-1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (14%)	Using where; FirstMatch(customer); Using rowid filter
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (14%)	Using where; FirstMatch(customer); Using rowid filter
 select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
 and
 c_custkey in (select o_custkey from orders
@@ -783,8 +809,8 @@ c_custkey in (select o_custkey from orders
 where o_orderDATE between "1992-01-09" and "1993-01-08");
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
-1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
-1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (14%)	Using where; FirstMatch(customer); Using rowid filter
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (14%)	Using where; FirstMatch(customer); Using rowid filter
 update customer set c_acctbal = c_acctbal+20 where c_nationkey in (select n_nationkey from nation where n_name='PERU')
 and
 c_custkey in (select o_custkey from orders
@@ -817,27 +843,155 @@ Customer#000000121	6428.32
 Customer#000000133	2314.67
 # Materialization
 # ===============
+set optimizer_switch='firstmatch=off';
+explain
+select c_name, c_acctbal from customer where c_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 '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	28	
-1	PRIMARY	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.orders.o_custkey	1	
-2	MATERIALIZED	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	28	Using index condition; Using where
+1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	orders	ALL	i_o_orderdate,i_o_custkey	NULL	NULL	NULL	1500	Using where
 explain format=json
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.382051418,
     "nested_loop": [
       {
         "table": {
-          "table_name": "<subquery2>",
+          "table_name": "customer",
           "access_type": "ALL",
+          "possible_keys": ["PRIMARY"],
+          "loops": 1,
+          "rows": 150,
+          "cost": 0.03493875,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "<subquery2>",
+          "access_type": "eq_ref",
           "possible_keys": ["distinct_key"],
-          "rows": 28,
+          "key": "distinct_key",
+          "key_length": "4",
+          "used_key_parts": ["o_custkey"],
+          "ref": ["func"],
+          "rows": 1,
           "filtered": 100,
           "materialized": {
             "unique": 1,
@@ -847,86 +1001,152 @@ EXPLAIN
                 {
                   "table": {
                     "table_name": "orders",
-                    "access_type": "range",
+                    "access_type": "ALL",
                     "possible_keys": ["i_o_orderdate", "i_o_custkey"],
-                    "key": "i_o_orderdate",
-                    "key_length": "4",
-                    "used_key_parts": ["o_orderDATE"],
-                    "rows": 28,
-                    "filtered": 100,
-                    "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'",
-                    "attached_condition": "orders.o_custkey is not null"
+                    "loops": 1,
+                    "rows": 1500,
+                    "cost": 0.2532975,
+                    "filtered": 16.13333321,
+                    "attached_condition": "orders.o_orderDATE between '1992-01-09' and '1993-03-08'"
                   }
                 }
               ]
             }
           }
         }
-      },
-      {
-        "table": {
-          "table_name": "customer",
-          "access_type": "eq_ref",
-          "possible_keys": ["PRIMARY"],
-          "key": "PRIMARY",
-          "key_length": "4",
-          "used_key_parts": ["c_custkey"],
-          "ref": ["dbt3_s001.orders.o_custkey"],
-          "rows": 1,
-          "filtered": 100
-        }
       }
     ]
   }
 }
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
-Customer#000000025	7133.7
+Customer#000000001	711.56
+Customer#000000002	121.65
+Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
-Customer#000000065	8795.16
-Customer#000000032	3471.53
+Customer#000000014	5266.3
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000019	8914.71
+Customer#000000022	591.98
 Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
+Customer#000000032	3471.53
+Customer#000000034	8589.7
 Customer#000000035	1228.24
-Customer#000000091	4643.14
-Customer#000000016	4681.03
-Customer#000000098	-551.37
 Customer#000000037	-917.75
-Customer#000000136	-842.39
-Customer#000000118	3582.37
-Customer#000000022	591.98
-Customer#000000005	794.47
-Customer#000000109	-716.1
 Customer#000000038	6345.11
-Customer#000000076	5745.33
-Customer#000000056	6530.86
 Customer#000000040	1335.3
-Customer#000000116	8403.99
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
+Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
+Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
+Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
+Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
+Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
+Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
+Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
-Customer#000000017	6.34
-Customer#000000052	5630.28
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 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');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	28	
-1	PRIMARY	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.orders.o_custkey	1	
-2	MATERIALIZED	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	28	Using index condition; Using where
+1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	orders	ALL	i_o_orderdate,i_o_custkey	NULL	NULL	NULL	1500	Using where
 explain format=json
 update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.382051418,
     "nested_loop": [
       {
         "table": {
-          "table_name": "<subquery2>",
+          "table_name": "customer",
           "access_type": "ALL",
+          "possible_keys": ["PRIMARY"],
+          "loops": 1,
+          "rows": 150,
+          "cost": 0.03493875,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "<subquery2>",
+          "access_type": "eq_ref",
           "possible_keys": ["distinct_key"],
-          "rows": 28,
+          "key": "distinct_key",
+          "key_length": "4",
+          "used_key_parts": ["o_custkey"],
+          "ref": ["func"],
+          "rows": 1,
           "filtered": 100,
           "materialized": {
             "unique": 1,
@@ -936,96 +1156,211 @@ EXPLAIN
                 {
                   "table": {
                     "table_name": "orders",
-                    "access_type": "range",
+                    "access_type": "ALL",
                     "possible_keys": ["i_o_orderdate", "i_o_custkey"],
-                    "key": "i_o_orderdate",
-                    "key_length": "4",
-                    "used_key_parts": ["o_orderDATE"],
-                    "rows": 28,
-                    "filtered": 100,
-                    "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'",
-                    "attached_condition": "orders.o_custkey is not null"
+                    "loops": 1,
+                    "rows": 1500,
+                    "cost": 0.2532975,
+                    "filtered": 16.13333321,
+                    "attached_condition": "orders.o_orderDATE between '1992-01-09' and '1993-03-08'"
                   }
                 }
               ]
             }
           }
         }
-      },
-      {
-        "table": {
-          "table_name": "customer",
-          "access_type": "eq_ref",
-          "possible_keys": ["PRIMARY"],
-          "key": "PRIMARY",
-          "key_length": "4",
-          "used_key_parts": ["c_custkey"],
-          "ref": ["dbt3_s001.orders.o_custkey"],
-          "rows": 1,
-          "filtered": 100
-        }
       }
     ]
   }
 }
 update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
-Customer#000000025	7138.7
+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#000000065	8800.16
-Customer#000000032	3476.53
+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#000000091	4648.14
-Customer#000000016	4686.03
-Customer#000000098	-546.37
 Customer#000000037	-912.75
-Customer#000000136	-837.39
-Customer#000000118	3587.37
-Customer#000000022	596.98
-Customer#000000005	799.47
-Customer#000000109	-711.1
 Customer#000000038	6350.11
-Customer#000000076	5750.33
-Customer#000000056	6535.86
 Customer#000000040	1340.3
-Customer#000000116	8408.99
+Customer#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#000000017	11.34
-Customer#000000052	5635.28
+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 '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
-Customer#000000025	7133.7
+Customer#000000001	711.56
+Customer#000000002	121.65
+Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
-Customer#000000065	8795.16
-Customer#000000032	3471.53
+Customer#000000014	5266.3
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000019	8914.71
+Customer#000000022	591.98
 Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
+Customer#000000032	3471.53
+Customer#000000034	8589.7
 Customer#000000035	1228.24
-Customer#000000091	4643.14
-Customer#000000016	4681.03
-Customer#000000098	-551.37
 Customer#000000037	-917.75
-Customer#000000136	-842.39
-Customer#000000118	3582.37
-Customer#000000022	591.98
-Customer#000000005	794.47
-Customer#000000109	-716.1
 Customer#000000038	6345.11
-Customer#000000076	5745.33
-Customer#000000056	6530.86
 Customer#000000040	1335.3
-Customer#000000116	8403.99
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
+Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
+Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
+Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
+Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
+Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
+Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
+Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
-Customer#000000017	6.34
-Customer#000000052	5630.28
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 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');
@@ -1272,18 +1607,22 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.085533248,
     "nested_loop": [
       {
         "table": {
           "table_name": "<subquery2>",
           "access_type": "ALL",
           "possible_keys": ["distinct_key"],
+          "loops": 1,
           "rows": 28,
+          "cost": 0.03691572,
           "filtered": 100,
           "materialized": {
             "unique": 1,
             "query_block": {
               "select_id": 2,
+              "cost": 0.053826401,
               "having_condition": "count(orders.o_custkey) > 1",
               "temporary_table": {
                 "nested_loop": [
@@ -1295,7 +1634,9 @@ EXPLAIN
                       "key": "i_o_orderdate",
                       "key_length": "4",
                       "used_key_parts": ["o_orderDATE"],
+                      "loops": 1,
                       "rows": 28,
+                      "cost": 0.035889016,
                       "filtered": 100,
                       "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'"
                     }
@@ -1315,7 +1656,9 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["c_custkey"],
           "ref": ["<subquery2>.o_custkey"],
+          "loops": 28,
           "rows": 1,
+          "cost": 0.048617528,
           "filtered": 100
         }
       }
@@ -1347,18 +1690,22 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": 0.085533248,
     "nested_loop": [
       {
         "table": {
           "table_name": "<subquery2>",
           "access_type": "ALL",
           "possible_keys": ["distinct_key"],
+          "loops": 1,
           "rows": 28,
+          "cost": 0.03691572,
           "filtered": 100,
           "materialized": {
             "unique": 1,
             "query_block": {
               "select_id": 2,
+              "cost": 0.053826401,
               "having_condition": "count(orders.o_custkey) > 1",
               "temporary_table": {
                 "nested_loop": [
@@ -1370,7 +1717,9 @@ EXPLAIN
                       "key": "i_o_orderdate",
                       "key_length": "4",
                       "used_key_parts": ["o_orderDATE"],
+                      "loops": 1,
                       "rows": 28,
+                      "cost": 0.035889016,
                       "filtered": 100,
                       "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'"
                     }
@@ -1390,7 +1739,9 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["c_custkey"],
           "ref": ["<subquery2>.o_custkey"],
+          "loops": 28,
           "rows": 1,
+          "cost": 0.048617528,
           "filtered": 100
         }
       }
@@ -1428,7 +1779,7 @@ where o_orderDATE between '1992-01-09' and '1993-03-08'
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	dbt3_s001.customer.c_custkey	1	
-2	MATERIALIZED	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	242	Using index condition; Using temporary
+2	MATERIALIZED	orders	ALL	i_o_orderdate	NULL	NULL	NULL	1500	Using where; Using temporary
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-01-09' and '1993-03-08'
                       group by o_custkey having count(o_custkey) > 5);
@@ -1449,7 +1800,7 @@ where o_orderDATE between '1992-01-09' and '1993-03-08'
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	dbt3_s001.customer.c_custkey	1	
-2	MATERIALIZED	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	242	Using index condition; Using temporary
+2	MATERIALIZED	orders	ALL	i_o_orderdate	NULL	NULL	NULL	1500	Using where; Using temporary
 update customer set c_acctbal = c_acctbal-1 where c_custkey in (select o_custkey from orders
 where o_orderDATE between '1992-01-09' and '1993-03-08'
                       group by o_custkey having count(o_custkey) > 5);
@@ -1550,6 +1901,7 @@ o_orderkey	o_totalprice
 deallocate prepare stmt;
 # FirstMatch PS
 # =============
+set optimizer_switch='materialization=off';
 prepare stmt from "
 update customer set c_acctbal = c_acctbal+? where c_nationkey in (select n_nationkey from nation
 where n_regionkey in (1,2))
@@ -1715,125 +2067,386 @@ Customer#000000082	9468.34
 Customer#000000124	1842.49
 Customer#000000127	9280.71
 deallocate prepare stmt;
+set optimizer_switch='materialization=default';
 # Materialization PS
 # ==================
 prepare stmt from "
 update customer set c_acctbal = c_acctbal+? where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 ";
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
-Customer#000000025	7133.7
+Customer#000000001	711.56
+Customer#000000002	121.65
+Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
-Customer#000000065	8795.16
-Customer#000000032	3471.53
+Customer#000000014	5266.3
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000019	8914.71
+Customer#000000022	591.98
 Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000028	1007.1799999999998
+Customer#000000029	7618.27
+Customer#000000031	5236.89
+Customer#000000032	3471.53
+Customer#000000034	8589.7
 Customer#000000035	1228.24
-Customer#000000091	4643.14
-Customer#000000016	4681.03
-Customer#000000098	-551.37
 Customer#000000037	-917.75
-Customer#000000136	-842.39
-Customer#000000118	3582.37
-Customer#000000022	591.98
-Customer#000000005	794.47
-Customer#000000109	-716.1
 Customer#000000038	6345.11
-Customer#000000076	5745.33
-Customer#000000056	6530.86
 Customer#000000040	1335.3
-Customer#000000116	8403.99
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
+Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
+Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
+Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
+Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
+Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
+Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
+Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
-Customer#000000017	6.34
-Customer#000000052	5630.28
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 set @a1=7;
 execute stmt using @a1;
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
-Customer#000000025	7140.7
+Customer#000000001	718.56
+Customer#000000002	128.65
+Customer#000000005	801.47
+Customer#000000007	9568.95
+Customer#000000008	6826.74
+Customer#000000010	2760.54
+Customer#000000011	-265.6
 Customer#000000013	3864.34
-Customer#000000065	8802.16
-Customer#000000032	3478.53
+Customer#000000014	5273.3
+Customer#000000016	4688.03
+Customer#000000017	13.34
+Customer#000000019	8921.71
+Customer#000000022	598.98
 Customer#000000023	3339.02
+Customer#000000025	7140.7
+Customer#000000028	1014.1799999999998
+Customer#000000029	7625.27
+Customer#000000031	5243.89
+Customer#000000032	3478.53
+Customer#000000034	8596.7
 Customer#000000035	1235.24
-Customer#000000091	4650.14
-Customer#000000016	4688.03
-Customer#000000098	-544.37
 Customer#000000037	-910.75
-Customer#000000136	-835.39
-Customer#000000118	3589.37
-Customer#000000022	598.98
-Customer#000000005	801.47
-Customer#000000109	-709.1
 Customer#000000038	6352.11
-Customer#000000076	5752.33
-Customer#000000056	6537.86
 Customer#000000040	1342.3
-Customer#000000116	8410.99
+Customer#000000041	277.95
+Customer#000000043	9911.28
+Customer#000000044	7322.94
+Customer#000000046	5751.59
+Customer#000000047	281.58
+Customer#000000049	4580.94
+Customer#000000052	5637.28
+Customer#000000053	4120.64
+Customer#000000055	4579.11
+Customer#000000056	6537.86
+Customer#000000058	6485.46
+Customer#000000059	3465.6
+Customer#000000061	1543.24
+Customer#000000062	602.61
+Customer#000000064	-639.64
+Customer#000000065	8802.16
+Customer#000000067	8173.59
+Customer#000000070	4874.52
+Customer#000000071	-604.19
+Customer#000000073	4295.5
+Customer#000000074	2771.43
+Customer#000000076	5752.33
+Customer#000000079	5128.28
+Customer#000000080	7390.53
+Customer#000000082	9475.34
+Customer#000000083	6470.51
+Customer#000000085	3393.64
+Customer#000000086	3313.32
+Customer#000000088	8038.44
+Customer#000000089	1537.76
+Customer#000000091	4650.14
+Customer#000000092	1189.91
+Customer#000000094	5507.11
+Customer#000000095	5334.38
+Customer#000000097	2171.48
+Customer#000000098	-544.37
+Customer#000000100	9896.89
+Customer#000000101	7477.96
+Customer#000000103	2764.45
+Customer#000000104	-581.38
+Customer#000000106	3295.42
+Customer#000000107	2521.15
+Customer#000000109	-709.1
+Customer#000000110	7469.99
+Customer#000000112	2960.35
 Customer#000000115	7515.92
+Customer#000000116	8410.99
+Customer#000000118	3589.37
+Customer#000000121	6435.32
+Customer#000000122	7872.46
+Customer#000000127	9287.71
+Customer#000000128	-979.96
+Customer#000000130	5080.58
+Customer#000000131	8602.53
+Customer#000000133	2321.67
+Customer#000000134	4615.9
+Customer#000000136	-835.39
+Customer#000000137	7845.3
+Customer#000000139	7904.78
 Customer#000000140	9970.15
-Customer#000000017	13.34
-Customer#000000052	5637.28
+Customer#000000142	2216.81
+Customer#000000143	2193.5
+Customer#000000145	9755.93
+Customer#000000148	2142.6
+Customer#000000149	8966.65
 set @a2=3;
 execute stmt using @a2;
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
-Customer#000000025	7143.7
+Customer#000000001	721.56
+Customer#000000002	131.65
+Customer#000000005	804.47
+Customer#000000007	9571.95
+Customer#000000008	6829.74
+Customer#000000010	2763.54
+Customer#000000011	-262.6
 Customer#000000013	3867.34
-Customer#000000065	8805.16
-Customer#000000032	3481.53
+Customer#000000014	5276.3
+Customer#000000016	4691.03
+Customer#000000017	16.34
+Customer#000000019	8924.71
+Customer#000000022	601.98
 Customer#000000023	3342.02
+Customer#000000025	7143.7
+Customer#000000028	1017.1799999999998
+Customer#000000029	7628.27
+Customer#000000031	5246.89
+Customer#000000032	3481.53
+Customer#000000034	8599.7
 Customer#000000035	1238.24
-Customer#000000091	4653.14
-Customer#000000016	4691.03
-Customer#000000098	-541.37
 Customer#000000037	-907.75
-Customer#000000136	-832.39
-Customer#000000118	3592.37
-Customer#000000022	601.98
-Customer#000000005	804.47
-Customer#000000109	-706.1
 Customer#000000038	6355.11
-Customer#000000076	5755.33
-Customer#000000056	6540.86
 Customer#000000040	1345.3
-Customer#000000116	8413.99
+Customer#000000041	280.95
+Customer#000000043	9914.28
+Customer#000000044	7325.94
+Customer#000000046	5754.59
+Customer#000000047	284.58
+Customer#000000049	4583.94
+Customer#000000052	5640.28
+Customer#000000053	4123.64
+Customer#000000055	4582.11
+Customer#000000056	6540.86
+Customer#000000058	6488.46
+Customer#000000059	3468.6
+Customer#000000061	1546.24
+Customer#000000062	605.61
+Customer#000000064	-636.64
+Customer#000000065	8805.16
+Customer#000000067	8176.59
+Customer#000000070	4877.52
+Customer#000000071	-601.19
+Customer#000000073	4298.5
+Customer#000000074	2774.43
+Customer#000000076	5755.33
+Customer#000000079	5131.28
+Customer#000000080	7393.53
+Customer#000000082	9478.34
+Customer#000000083	6473.51
+Customer#000000085	3396.64
+Customer#000000086	3316.32
+Customer#000000088	8041.44
+Customer#000000089	1540.76
+Customer#000000091	4653.14
+Customer#000000092	1192.91
+Customer#000000094	5510.11
+Customer#000000095	5337.38
+Customer#000000097	2174.48
+Customer#000000098	-541.37
+Customer#000000100	9899.89
+Customer#000000101	7480.96
+Customer#000000103	2767.45
+Customer#000000104	-578.38
+Customer#000000106	3298.42
+Customer#000000107	2524.15
+Customer#000000109	-706.1
+Customer#000000110	7472.99
+Customer#000000112	2963.35
 Customer#000000115	7518.92
+Customer#000000116	8413.99
+Customer#000000118	3592.37
+Customer#000000121	6438.32
+Customer#000000122	7875.46
+Customer#000000127	9290.71
+Customer#000000128	-976.96
+Customer#000000130	5083.58
+Customer#000000131	8605.53
+Customer#000000133	2324.67
+Customer#000000134	4618.9
+Customer#000000136	-832.39
+Customer#000000137	7848.3
+Customer#000000139	7907.78
 Customer#000000140	9973.15
-Customer#000000017	16.34
-Customer#000000052	5640.28
+Customer#000000142	2219.81
+Customer#000000143	2196.5
+Customer#000000145	9758.93
+Customer#000000148	2145.6
+Customer#000000149	8969.65
 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');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
-Customer#000000025	7133.7
+Customer#000000001	711.56
+Customer#000000002	121.65
+Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
-Customer#000000065	8795.16
-Customer#000000032	3471.53
+Customer#000000014	5266.3
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000019	8914.71
+Customer#000000022	591.98
 Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000028	1007.1799999999998
+Customer#000000029	7618.27
+Customer#000000031	5236.89
+Customer#000000032	3471.53
+Customer#000000034	8589.7
 Customer#000000035	1228.24
-Customer#000000091	4643.14
-Customer#000000016	4681.03
-Customer#000000098	-551.37
 Customer#000000037	-917.75
-Customer#000000136	-842.39
-Customer#000000118	3582.37
-Customer#000000022	591.98
-Customer#000000005	794.47
-Customer#000000109	-716.1
 Customer#000000038	6345.11
-Customer#000000076	5745.33
-Customer#000000056	6530.86
 Customer#000000040	1335.3
-Customer#000000116	8403.99
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
+Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
+Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
+Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
+Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
+Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
+Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
+Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
-Customer#000000017	6.34
-Customer#000000052	5630.28
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 deallocate prepare stmt;
 # Materialization SJM PS
 # ======================
@@ -1949,6 +2562,7 @@ o_orderkey	o_totalprice
 drop procedure p;
 # FirstMatch SP
 # =============
+set optimizer_switch='materialization=off';
 create procedure p(d int)
 update customer set c_acctbal = c_acctbal+d where c_nationkey in (select n_nationkey from nation
 where n_regionkey in (1,2))
@@ -2111,122 +2725,383 @@ Customer#000000082	9468.34
 Customer#000000124	1842.49
 Customer#000000127	9280.71
 drop procedure p;
+set optimizer_switch='materialization=default';
 # Materialization SP
 # ==================
 create procedure p(d int)
 update customer set c_acctbal = c_acctbal+d where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
-Customer#000000025	7133.7
+Customer#000000001	711.56
+Customer#000000002	121.65
+Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
-Customer#000000065	8795.16
-Customer#000000032	3471.53
+Customer#000000014	5266.3
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000019	8914.71
+Customer#000000022	591.98
 Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000028	1007.1799999999998
+Customer#000000029	7618.27
+Customer#000000031	5236.89
+Customer#000000032	3471.53
+Customer#000000034	8589.7
 Customer#000000035	1228.24
-Customer#000000091	4643.14
-Customer#000000016	4681.03
-Customer#000000098	-551.37
 Customer#000000037	-917.75
-Customer#000000136	-842.39
-Customer#000000118	3582.37
-Customer#000000022	591.98
-Customer#000000005	794.47
-Customer#000000109	-716.1
 Customer#000000038	6345.11
-Customer#000000076	5745.33
-Customer#000000056	6530.86
 Customer#000000040	1335.3
-Customer#000000116	8403.99
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
+Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
+Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
+Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
+Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
+Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
+Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
+Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
-Customer#000000017	6.34
-Customer#000000052	5630.28
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 call p(3);
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
-Customer#000000025	7136.7
+Customer#000000001	714.56
+Customer#000000002	124.65
+Customer#000000005	797.47
+Customer#000000007	9564.95
+Customer#000000008	6822.74
+Customer#000000010	2756.54
+Customer#000000011	-269.6
 Customer#000000013	3860.34
-Customer#000000065	8798.16
-Customer#000000032	3474.53
+Customer#000000014	5269.3
+Customer#000000016	4684.03
+Customer#000000017	9.34
+Customer#000000019	8917.71
+Customer#000000022	594.98
 Customer#000000023	3335.02
+Customer#000000025	7136.7
+Customer#000000028	1010.1799999999998
+Customer#000000029	7621.27
+Customer#000000031	5239.89
+Customer#000000032	3474.53
+Customer#000000034	8592.7
 Customer#000000035	1231.24
-Customer#000000091	4646.14
-Customer#000000016	4684.03
-Customer#000000098	-548.37
 Customer#000000037	-914.75
-Customer#000000136	-839.39
-Customer#000000118	3585.37
-Customer#000000022	594.98
-Customer#000000005	797.47
-Customer#000000109	-713.1
 Customer#000000038	6348.11
-Customer#000000076	5748.33
-Customer#000000056	6533.86
 Customer#000000040	1338.3
-Customer#000000116	8406.99
+Customer#000000041	273.95
+Customer#000000043	9907.28
+Customer#000000044	7318.94
+Customer#000000046	5747.59
+Customer#000000047	277.58
+Customer#000000049	4576.94
+Customer#000000052	5633.28
+Customer#000000053	4116.64
+Customer#000000055	4575.11
+Customer#000000056	6533.86
+Customer#000000058	6481.46
+Customer#000000059	3461.6
+Customer#000000061	1539.24
+Customer#000000062	598.61
+Customer#000000064	-643.64
+Customer#000000065	8798.16
+Customer#000000067	8169.59
+Customer#000000070	4870.52
+Customer#000000071	-608.19
+Customer#000000073	4291.5
+Customer#000000074	2767.43
+Customer#000000076	5748.33
+Customer#000000079	5124.28
+Customer#000000080	7386.53
+Customer#000000082	9471.34
+Customer#000000083	6466.51
+Customer#000000085	3389.64
+Customer#000000086	3309.32
+Customer#000000088	8034.44
+Customer#000000089	1533.76
+Customer#000000091	4646.14
+Customer#000000092	1185.91
+Customer#000000094	5503.11
+Customer#000000095	5330.38
+Customer#000000097	2167.48
+Customer#000000098	-548.37
+Customer#000000100	9892.89
+Customer#000000101	7473.96
+Customer#000000103	2760.45
+Customer#000000104	-585.38
+Customer#000000106	3291.42
+Customer#000000107	2517.15
+Customer#000000109	-713.1
+Customer#000000110	7465.99
+Customer#000000112	2956.35
 Customer#000000115	7511.92
+Customer#000000116	8406.99
+Customer#000000118	3585.37
+Customer#000000121	6431.32
+Customer#000000122	7868.46
+Customer#000000127	9283.71
+Customer#000000128	-983.96
+Customer#000000130	5076.58
+Customer#000000131	8598.53
+Customer#000000133	2317.67
+Customer#000000134	4611.9
+Customer#000000136	-839.39
+Customer#000000137	7841.3
+Customer#000000139	7900.78
 Customer#000000140	9966.15
-Customer#000000017	9.34
-Customer#000000052	5633.28
+Customer#000000142	2212.81
+Customer#000000143	2189.5
+Customer#000000145	9751.93
+Customer#000000148	2138.6
+Customer#000000149	8962.65
 call p(7);
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
-Customer#000000025	7143.7
+Customer#000000001	721.56
+Customer#000000002	131.65
+Customer#000000005	804.47
+Customer#000000007	9571.95
+Customer#000000008	6829.74
+Customer#000000010	2763.54
+Customer#000000011	-262.6
 Customer#000000013	3867.34
-Customer#000000065	8805.16
-Customer#000000032	3481.53
+Customer#000000014	5276.3
+Customer#000000016	4691.03
+Customer#000000017	16.34
+Customer#000000019	8924.71
+Customer#000000022	601.98
 Customer#000000023	3342.02
+Customer#000000025	7143.7
+Customer#000000028	1017.1799999999998
+Customer#000000029	7628.27
+Customer#000000031	5246.89
+Customer#000000032	3481.53
+Customer#000000034	8599.7
 Customer#000000035	1238.24
-Customer#000000091	4653.14
-Customer#000000016	4691.03
-Customer#000000098	-541.37
 Customer#000000037	-907.75
-Customer#000000136	-832.39
-Customer#000000118	3592.37
-Customer#000000022	601.98
-Customer#000000005	804.47
-Customer#000000109	-706.1
 Customer#000000038	6355.11
-Customer#000000076	5755.33
-Customer#000000056	6540.86
 Customer#000000040	1345.3
-Customer#000000116	8413.99
+Customer#000000041	280.95
+Customer#000000043	9914.28
+Customer#000000044	7325.94
+Customer#000000046	5754.59
+Customer#000000047	284.58
+Customer#000000049	4583.94
+Customer#000000052	5640.28
+Customer#000000053	4123.64
+Customer#000000055	4582.11
+Customer#000000056	6540.86
+Customer#000000058	6488.46
+Customer#000000059	3468.6
+Customer#000000061	1546.24
+Customer#000000062	605.61
+Customer#000000064	-636.64
+Customer#000000065	8805.16
+Customer#000000067	8176.59
+Customer#000000070	4877.52
+Customer#000000071	-601.19
+Customer#000000073	4298.5
+Customer#000000074	2774.43
+Customer#000000076	5755.33
+Customer#000000079	5131.28
+Customer#000000080	7393.53
+Customer#000000082	9478.34
+Customer#000000083	6473.51
+Customer#000000085	3396.64
+Customer#000000086	3316.32
+Customer#000000088	8041.44
+Customer#000000089	1540.76
+Customer#000000091	4653.14
+Customer#000000092	1192.91
+Customer#000000094	5510.11
+Customer#000000095	5337.38
+Customer#000000097	2174.48
+Customer#000000098	-541.37
+Customer#000000100	9899.89
+Customer#000000101	7480.96
+Customer#000000103	2767.45
+Customer#000000104	-578.38
+Customer#000000106	3298.42
+Customer#000000107	2524.15
+Customer#000000109	-706.1
+Customer#000000110	7472.99
+Customer#000000112	2963.35
 Customer#000000115	7518.92
+Customer#000000116	8413.99
+Customer#000000118	3592.37
+Customer#000000121	6438.32
+Customer#000000122	7875.46
+Customer#000000127	9290.71
+Customer#000000128	-976.96
+Customer#000000130	5083.58
+Customer#000000131	8605.53
+Customer#000000133	2324.67
+Customer#000000134	4618.9
+Customer#000000136	-832.39
+Customer#000000137	7848.3
+Customer#000000139	7907.78
 Customer#000000140	9973.15
-Customer#000000017	16.34
-Customer#000000052	5640.28
+Customer#000000142	2219.81
+Customer#000000143	2196.5
+Customer#000000145	9758.93
+Customer#000000148	2145.6
+Customer#000000149	8969.65
 call p(-(3+7));
 select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
-where o_orderDATE between '1992-01-09' and '1992-03-08');
+where o_orderDATE between '1992-01-09' and '1993-03-08');
 c_name	c_acctbal
-Customer#000000025	7133.7
+Customer#000000001	711.56
+Customer#000000002	121.65
+Customer#000000005	794.47
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
 Customer#000000013	3857.34
-Customer#000000065	8795.16
-Customer#000000032	3471.53
+Customer#000000014	5266.3
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000019	8914.71
+Customer#000000022	591.98
 Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000028	1007.1799999999998
+Customer#000000029	7618.27
+Customer#000000031	5236.89
+Customer#000000032	3471.53
+Customer#000000034	8589.7
 Customer#000000035	1228.24
-Customer#000000091	4643.14
-Customer#000000016	4681.03
-Customer#000000098	-551.37
 Customer#000000037	-917.75
-Customer#000000136	-842.39
-Customer#000000118	3582.37
-Customer#000000022	591.98
-Customer#000000005	794.47
-Customer#000000109	-716.1
 Customer#000000038	6345.11
-Customer#000000076	5745.33
-Customer#000000056	6530.86
 Customer#000000040	1335.3
-Customer#000000116	8403.99
+Customer#000000041	270.95
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
+Customer#000000052	5630.28
+Customer#000000053	4113.64
+Customer#000000055	4572.11
+Customer#000000056	6530.86
+Customer#000000058	6478.46
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000062	595.61
+Customer#000000064	-646.64
+Customer#000000065	8795.16
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
+Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000089	1530.76
+Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000094	5500.11
+Customer#000000095	5327.38
+Customer#000000097	2164.48
+Customer#000000098	-551.37
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000107	2514.15
+Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
 Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000128	-986.96
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
 Customer#000000140	9963.15
-Customer#000000017	6.34
-Customer#000000052	5630.28
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000145	9748.93
+Customer#000000148	2135.6
+Customer#000000149	8959.65
 drop procedure p;
 # Materialization SJM SP
 # ======================
@@ -2296,7 +3171,7 @@ where c_nationkey in (1,2))
 order by o_totalprice limit 500;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	108	Using where; Using filesort
-2	DEPENDENT SUBQUERY	customer	unique_subquery|filter	PRIMARY,i_c_nationkey	PRIMARY|i_c_nationkey	4|5	func	1 (10%)	Using where; Using rowid filter
+2	DEPENDENT SUBQUERY	customer	unique_subquery	PRIMARY,i_c_nationkey	PRIMARY	4	func	1	Using where
 update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
 o_custkey in (select c_custkey from customer
 where c_nationkey in (1,2))
@@ -2336,7 +3211,7 @@ o_custkey in (select c_custkey from customer
 where c_nationkey in (1,2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	customer	range	PRIMARY,i_c_nationkey	i_c_nationkey	5	NULL	15	Using index condition
-1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (7%)	Using where; Using rowid filter
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (7%)	Using where; Using rowid filter
 update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
 o_custkey in (select c_custkey from customer
 where c_nationkey in (1,2));
diff --git a/mysql-test/main/update_single_to_multi.test b/mysql-test/main/update_single_to_multi.test
index bf89a6ceba5..a215c5acc67 100644
--- a/mysql-test/main/update_single_to_multi.test
+++ b/mysql-test/main/update_single_to_multi.test
@@ -15,8 +15,9 @@ use dbt3_s001;
 --enable_query_log
 
 create index i_n_name on nation(n_name);
-analyze table nation;
-
+analyze table
+  nation, lineitem, customer, orders, part, supplier, partsupp, region
+persistent for all;
 
 --echo # Pullout
 --echo # =======
@@ -209,117 +210,148 @@ select c_name, c_acctbal from customer where $c6;
 --echo # Materialization
 --echo # ===============
 
+set optimizer_switch='firstmatch=off';
+
 let $c7=
+        c_nationkey in (select n_nationkey from nation where
+           n_name in ('JAPAN', 'INDONESIA', 'PERU', 'ARGENTINA'))
+        and
         c_custkey in (select o_custkey from orders
-                      where o_orderDATE between '1992-01-09' and '1992-03-08');
+                      where o_orderDATE between "1992-01-09" and "1995-01-08");
 
 eval
 explain
 select c_name, c_acctbal from customer where $c7;
 eval
-explain format=json
-select c_name, c_acctbal from customer where $c7;
-eval
 select c_name, c_acctbal from customer where $c7;
 
 eval
 explain
-update customer set c_acctbal = c_acctbal+5 where $c7;
+update customer set c_acctbal = c_acctbal+20 where $c7;
 eval
-explain format=json
-update customer set c_acctbal = c_acctbal+5 where $c7;
-eval
-update customer set c_acctbal = c_acctbal+5 where $c7;
+update customer set c_acctbal = c_acctbal+20 where $c7;
 eval
 select c_name, c_acctbal from customer where $c7;
 
 eval
-update customer set c_acctbal = c_acctbal-5 where $c7;
+update customer set c_acctbal = c_acctbal-20 where $c7;
 eval
 select c_name, c_acctbal from customer where $c7;
 
+set optimizer_switch='firstmatch=default';
 
 let $c8=
         c_custkey in (select o_custkey from orders
-                      where o_orderDATE between '1992-06-09' and '1993-01-08');
+                      where o_orderDATE between '1992-01-09' and '1993-03-08');
 
 eval
 explain
 select c_name, c_acctbal from customer where $c8;
 eval
+explain format=json
+select c_name, c_acctbal from customer where $c8;
+eval
 select c_name, c_acctbal from customer where $c8;
 
 eval
 explain
-update customer set c_acctbal = c_acctbal+1 where $c8;
+update customer set c_acctbal = c_acctbal+5 where $c8;
 eval
-update customer set c_acctbal = c_acctbal+1 where $c8;
+explain format=json
+update customer set c_acctbal = c_acctbal+5 where $c8;
+eval
+update customer set c_acctbal = c_acctbal+5 where $c8;
 eval
 select c_name, c_acctbal from customer where $c8;
 
 eval
-update customer set c_acctbal = c_acctbal-1 where $c8;
+update customer set c_acctbal = c_acctbal-5 where $c8;
 eval
 select c_name, c_acctbal from customer where $c8;
 
 
+let $c9=
+        c_custkey in (select o_custkey from orders
+                      where o_orderDATE between '1992-06-09' and '1993-01-08');
+
+eval
+explain
+select c_name, c_acctbal from customer where $c9;
+eval
+select c_name, c_acctbal from customer where $c9;
+
+eval
+explain
+update customer set c_acctbal = c_acctbal+1 where $c9;
+eval
+update customer set c_acctbal = c_acctbal+1 where $c9;
+eval
+select c_name, c_acctbal from customer where $c9;
+
+eval
+update customer set c_acctbal = c_acctbal-1 where $c9;
+eval
+select c_name, c_acctbal from customer where $c9;
+
+
+
 --echo # Materialization SJM
 --echo # ===================
 
-let $c9=
+let $c10=
         c_custkey in (select o_custkey from orders
                         where o_orderDATE between '1992-01-09' and '1992-03-08'
                       group by o_custkey having count(o_custkey) > 1);
 
 eval
 explain
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 eval
 explain format=json
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 
 eval
 explain
-update customer set c_acctbal = c_acctbal-5 where $c9;
+update customer set c_acctbal = c_acctbal-5 where $c10;
 eval
 explain format=json
-update customer set c_acctbal = c_acctbal-5 where $c9;
+update customer set c_acctbal = c_acctbal-5 where $c10;
 eval
-update customer set c_acctbal = c_acctbal-5 where $c9;
+update customer set c_acctbal = c_acctbal-5 where $c10;
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 
 eval
-update customer set c_acctbal = c_acctbal+5 where $c9;
+update customer set c_acctbal = c_acctbal+5 where $c10;
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 
 
-let $c10=
+let $c11=
         c_custkey in (select o_custkey from orders
                         where o_orderDATE between '1992-01-09' and '1993-03-08'
                       group by o_custkey having count(o_custkey) > 5);
 
 eval
 explain
-select c_name, c_acctbal from customer where $c10;
+select c_name, c_acctbal from customer where $c11;
 eval
-select c_name, c_acctbal from customer where $c10;
+select c_name, c_acctbal from customer where $c11;
 
 eval
 explain
-update customer set c_acctbal = c_acctbal-1 where $c10;
+update customer set c_acctbal = c_acctbal-1 where $c11;
 eval
-update customer set c_acctbal = c_acctbal-1 where $c10;
+update customer set c_acctbal = c_acctbal-1 where $c11;
 eval
-select c_name, c_acctbal from customer where $c10;
+select c_name, c_acctbal from customer where $c11;
 
 eval
-update customer set c_acctbal = c_acctbal+1 where $c10;
+update customer set c_acctbal = c_acctbal+1 where $c11;
 eval
-select c_name, c_acctbal from customer where $c10;
+select c_name, c_acctbal from customer where $c11;
 
 
 --echo # Pullout PS
@@ -350,6 +382,8 @@ deallocate prepare stmt;
 --echo # FirstMatch PS
 --echo # =============
 
+set optimizer_switch='materialization=off';
+
 eval
 prepare stmt from "
 update customer set c_acctbal = c_acctbal+? where $c5;
@@ -371,28 +405,29 @@ select c_name, c_acctbal from customer where $c5;
 
 deallocate prepare stmt;
 
+set optimizer_switch='materialization=default';
 
 --echo # Materialization PS
 --echo # ==================
 
 eval
 prepare stmt from "
-update customer set c_acctbal = c_acctbal+? where $c7;
+update customer set c_acctbal = c_acctbal+? where $c8;
 ";
 
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 set @a1=7;
 execute stmt using @a1;
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 set @a2=3;
 execute stmt using @a2;
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 execute stmt using -(@a1+@a2);
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 
 deallocate prepare stmt;
 
@@ -402,22 +437,22 @@ deallocate prepare stmt;
 
 eval
 prepare stmt from "
-update customer set c_acctbal = c_acctbal+? where $c9;
+update customer set c_acctbal = c_acctbal+? where $c10;
 ";
 
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 set @a1=-2;
 execute stmt using @a1;
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 set @a2=-1;
 execute stmt using @a2;
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 execute stmt using -(@a1+@a2);
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 
 deallocate prepare stmt;
 
@@ -447,6 +482,8 @@ drop procedure p;
 --echo # FirstMatch SP
 --echo # =============
 
+set optimizer_switch='materialization=off';
+
 eval
 create procedure p(d int)
 update customer set c_acctbal = c_acctbal+d where $c5;
@@ -465,25 +502,27 @@ select c_name, c_acctbal from customer where $c5;
 
 drop procedure p;
 
+set optimizer_switch='materialization=default';
+
 
 --echo # Materialization SP
 --echo # ==================
 
 eval
 create procedure p(d int)
-update customer set c_acctbal = c_acctbal+d where $c7;
+update customer set c_acctbal = c_acctbal+d where $c8;
 
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 call p(3);
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 call p(7);
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 call p(-(3+7));
 eval
-select c_name, c_acctbal from customer where $c7;
+select c_name, c_acctbal from customer where $c8;
 
 drop procedure p;
 
@@ -493,19 +532,19 @@ drop procedure p;
 
 eval
 create procedure p(d int)
-update customer set c_acctbal = c_acctbal+d where $c9;
+update customer set c_acctbal = c_acctbal+d where $c10;
 
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 call p(-1);
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 call p(-2);
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 call p(1+2);
 eval
-select c_name, c_acctbal from customer where $c9;
+select c_name, c_acctbal from customer where $c10;
 
 drop procedure p;
 
diff --git a/mysql-test/main/update_use_source.result b/mysql-test/main/update_use_source.result
index 3f5274185ab..99d72420ded 100644
--- a/mysql-test/main/update_use_source.result
+++ b/mysql-test/main/update_use_source.result
@@ -316,7 +316,7 @@ rollback;
 #
 explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	t1_c2	t1_c2	5	NULL	2	Using where
+1	PRIMARY	t1	range	t1_c2	t1_c2	5	NULL	2	Using index condition
 1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c2	8	Using index; FirstMatch(t1)
 start transaction;
 update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 6eda8ef2e88..4b01a96a132 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -32596,7 +32596,8 @@ bool Sql_cmd_dml::execute_inner(THD *thd)
   if (unlikely(thd->is_error()))
     goto err;
 
-  join->exec();
+  if (join->exec())
+    goto err;
 
   if (thd->lex->describe & DESCRIBE_EXTENDED)
   {