MDEV-7487 Semi-join optimization for single-table update/delete statementsbb-10.10-mdev-7487

This patch allows to use semi-join optimization at the top level of
single-table update and delete statements.
The problem of supporting such optimization became easy to resolve after
processing a single-table update/delete statement started using JOIN
structure. This allowed to use JOIN::prepare() not only for multi-table
updates/deletes but for single-table ones as well. This was done in the
patch for mdev-28883:
 Re-design the upper level of handling UPDATE and DELETE statements.

Note that JOIN::prepare() detects all subqueries that can be considered
as candidates for semi-join optimization. The code added by this patch
looks for such candidates at the top level and if such candidates are found
in the processed single-table update/delete the statement is handled in
the same way as a multi-table update/delete.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

14 files changed, 6140 insertions, 34 deletions

diff --git a/mysql-test/main/delete_single_to_multi.result b/mysql-test/main/delete_single_to_multi.result
new file mode 100644
index 00000000000..c6da8612166
--- /dev/null
+++ b/mysql-test/main/delete_single_to_multi.result
@@ -0,0 +1,2355 @@
+DROP DATABASE IF EXISTS dbt3_s001;
+CREATE DATABASE dbt3_s001;
+use dbt3_s001;
+create index i_n_name on nation(n_name);
+analyze table nation;
+Table	Op	Msg_type	Msg_text
+dbt3_s001.nation	analyze	status	Engine-independent statistics collected
+dbt3_s001.nation	analyze	status	OK
+# Pullout
+# =======
+explain
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (7%)	Using where; Using rowid filter
+explain format=json
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "nation",
+          "access_type": "ref",
+          "possible_keys": ["PRIMARY", "i_n_name"],
+          "key": "i_n_name",
+          "key_length": "26",
+          "used_key_parts": ["n_name"],
+          "ref": ["const"],
+          "rows": 1,
+          "filtered": 100,
+          "index_condition": "nation.n_name = 'PERU'"
+        }
+      },
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "ref",
+          "possible_keys": ["PRIMARY", "i_c_nationkey"],
+          "key": "i_c_nationkey",
+          "key_length": "5",
+          "used_key_parts": ["c_nationkey"],
+          "ref": ["dbt3_s001.nation.n_nationkey"],
+          "rows": 11,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "orders",
+          "access_type": "ref",
+          "possible_keys": ["i_o_orderdate", "i_o_custkey"],
+          "key": "i_o_custkey",
+          "key_length": "5",
+          "used_key_parts": ["o_custkey"],
+          "ref": ["dbt3_s001.customer.c_custkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_o_orderdate",
+              "used_key_parts": ["o_orderDATE"]
+            },
+            "rows": 108,
+            "selectivity_pct": 7.2
+          },
+          "rows": 11,
+          "filtered": 7.199999809,
+          "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'"
+        }
+      }
+    ]
+  }
+}
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+1729	12137.76
+2880	145761.99
+3142	16030.15
+5095	184583.99
+5121	150334.57
+5382	138423.03
+644	201268.06
+737	12984.85
+create table t as
+select * from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+explain
+delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (7%)	Using where; Using rowid filter
+explain format=json
+delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "nation",
+          "access_type": "ref",
+          "possible_keys": ["PRIMARY", "i_n_name"],
+          "key": "i_n_name",
+          "key_length": "26",
+          "used_key_parts": ["n_name"],
+          "ref": ["const"],
+          "rows": 1,
+          "filtered": 100,
+          "index_condition": "nation.n_name = 'PERU'"
+        }
+      },
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "ref",
+          "possible_keys": ["PRIMARY", "i_c_nationkey"],
+          "key": "i_c_nationkey",
+          "key_length": "5",
+          "used_key_parts": ["c_nationkey"],
+          "ref": ["dbt3_s001.nation.n_nationkey"],
+          "rows": 11,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "orders",
+          "access_type": "ref",
+          "possible_keys": ["i_o_orderdate", "i_o_custkey"],
+          "key": "i_o_custkey",
+          "key_length": "5",
+          "used_key_parts": ["o_custkey"],
+          "ref": ["dbt3_s001.customer.c_custkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_o_orderdate",
+              "used_key_parts": ["o_orderDATE"]
+            },
+            "rows": 108,
+            "selectivity_pct": 7.2
+          },
+          "rows": 11,
+          "filtered": 7.199999809,
+          "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'"
+        }
+      }
+    ]
+  }
+}
+delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+insert into orders select * from t;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+1729	12137.76
+2880	145761.99
+3142	16030.15
+5095	184583.99
+5121	150334.57
+5382	138423.03
+644	201268.06
+737	12984.85
+drop table t;
+explain
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 910 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
+1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 910 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+ps_partkey	ps_suppkey	ps_supplycost
+1	8	357.84
+3	8	645.4
+4	1	444.37
+5	8	50.52
+6	1	642.13
+7	8	763.98
+8	1	957.34
+create table t as
+select * from partsupp where (ps_partkey, ps_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 910 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+explain
+delete from partsupp where (ps_partkey, ps_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 910 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
+1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+delete from partsupp where (ps_partkey, ps_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 910 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 910 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+ps_partkey	ps_suppkey	ps_supplycost
+insert into partsupp select * from t;
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 910 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+ps_partkey	ps_suppkey	ps_supplycost
+1	8	357.84
+3	8	645.4
+4	1	444.37
+5	8	50.52
+6	1	642.13
+7	8	763.98
+8	1	957.34
+drop table t;
+explain
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part
+where p_retailprice between 901 and 910) and
+ps_suppkey in (select s_suppkey from supplier
+where s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
+1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part
+where p_retailprice between 901 and 910) and
+ps_suppkey in (select s_suppkey from supplier
+where s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+ps_partkey	ps_suppkey	ps_supplycost
+1	8	357.84
+3	8	645.4
+4	1	444.37
+5	8	50.52
+6	1	642.13
+7	8	763.98
+8	1	957.34
+create table t as
+select * from partsupp where ps_partkey in (select p_partkey from part
+where p_retailprice between 901 and 910) and
+ps_suppkey in (select s_suppkey from supplier
+where s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+explain
+delete from partsupp where ps_partkey in (select p_partkey from part
+where p_retailprice between 901 and 910) and
+ps_suppkey in (select s_suppkey from supplier
+where s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
+1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+delete from partsupp where ps_partkey in (select p_partkey from part
+where p_retailprice between 901 and 910) and
+ps_suppkey in (select s_suppkey from supplier
+where s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part
+where p_retailprice between 901 and 910) and
+ps_suppkey in (select s_suppkey from supplier
+where s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+ps_partkey	ps_suppkey	ps_supplycost
+insert into partsupp select * from t;
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part
+where p_retailprice between 901 and 910) and
+ps_suppkey in (select s_suppkey from supplier
+where s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+ps_partkey	ps_suppkey	ps_supplycost
+1	8	357.84
+3	8	645.4
+4	1	444.37
+5	8	50.52
+6	1	642.13
+7	8	763.98
+8	1	957.34
+drop table t;
+explain
+select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders
+where o_custkey in
+(select c_custkey from customer
+where c_nationkey in
+(select n_nationkey from nation
+where n_name='PERU'))
+and
+o_orderDATE between '1992-06-30' and '1992-12-31')
+and
+(l_partkey, l_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 1000 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
+1	PRIMARY	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey	5	dbt3_s001.supplier.s_suppkey	100	Using where
+1	PRIMARY	orders	eq_ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	PRIMARY|i_o_orderdate	4|4	dbt3_s001.lineitem.l_orderkey	1 (7%)	Using where; Using rowid filter
+1	PRIMARY	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
+1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
+select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders
+where o_custkey in
+(select c_custkey from customer
+where c_nationkey in
+(select n_nationkey from nation
+where n_name='PERU'))
+and
+o_orderDATE between '1992-06-30' and '1992-12-31')
+and
+(l_partkey, l_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 1000 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+l_orderkey	l_linenumber	l_tax
+2500	2	0.02
+2500	4	0.02
+4996	1	0.01
+933	1	0.04
+create table t as
+select * from lineitem where l_orderkey in (select o_orderkey from orders
+where o_custkey in
+(select c_custkey from customer
+where c_nationkey in
+(select n_nationkey from nation
+where n_name='PERU'))
+and
+o_orderDATE between '1992-06-30' and '1992-12-31')
+and
+(l_partkey, l_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 1000 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+explain
+delete from lineitem where l_orderkey in (select o_orderkey from orders
+where o_custkey in
+(select c_custkey from customer
+where c_nationkey in
+(select n_nationkey from nation
+where n_name='PERU'))
+and
+o_orderDATE between '1992-06-30' and '1992-12-31')
+and
+(l_partkey, l_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 1000 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
+1	PRIMARY	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey	5	dbt3_s001.supplier.s_suppkey	100	Using where
+1	PRIMARY	orders	eq_ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	PRIMARY|i_o_orderdate	4|4	dbt3_s001.lineitem.l_orderkey	1 (7%)	Using where; Using rowid filter
+1	PRIMARY	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
+1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
+delete from lineitem where l_orderkey in (select o_orderkey from orders
+where o_custkey in
+(select c_custkey from customer
+where c_nationkey in
+(select n_nationkey from nation
+where n_name='PERU'))
+and
+o_orderDATE between '1992-06-30' and '1992-12-31')
+and
+(l_partkey, l_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 1000 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders
+where o_custkey in
+(select c_custkey from customer
+where c_nationkey in
+(select n_nationkey from nation
+where n_name='PERU'))
+and
+o_orderDATE between '1992-06-30' and '1992-12-31')
+and
+(l_partkey, l_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 1000 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+l_orderkey	l_linenumber	l_tax
+insert into lineitem select * from t;
+select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders
+where o_custkey in
+(select c_custkey from customer
+where c_nationkey in
+(select n_nationkey from nation
+where n_name='PERU'))
+and
+o_orderDATE between '1992-06-30' and '1992-12-31')
+and
+(l_partkey, l_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 1000 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+l_orderkey	l_linenumber	l_tax
+2500	2	0.02
+2500	4	0.02
+4996	1	0.01
+933	1	0.04
+drop table t;
+# FirstMatch
+# ==========
+explain
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	ALL	PRIMARY,i_c_nationkey	NULL	NULL	NULL	150	Using where
+1	PRIMARY	nation	eq_ref|filter	PRIMARY,i_n_regionkey	PRIMARY|i_n_regionkey	4|5	dbt3_s001.customer.c_nationkey	1 (40%)	Using where; Using rowid filter
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (6%)	Using where; FirstMatch(nation); Using rowid filter
+explain format=json
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "ALL",
+          "possible_keys": ["PRIMARY", "i_c_nationkey"],
+          "rows": 150,
+          "filtered": 100,
+          "attached_condition": "customer.c_nationkey is not null"
+        }
+      },
+      {
+        "table": {
+          "table_name": "nation",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY", "i_n_regionkey"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["n_nationkey"],
+          "ref": ["dbt3_s001.customer.c_nationkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_n_regionkey",
+              "used_key_parts": ["n_regionkey"]
+            },
+            "rows": 10,
+            "selectivity_pct": 40
+          },
+          "rows": 1,
+          "filtered": 40,
+          "attached_condition": "nation.n_regionkey in (1,2)"
+        }
+      },
+      {
+        "table": {
+          "table_name": "orders",
+          "access_type": "ref",
+          "possible_keys": ["i_o_orderdate", "i_o_custkey"],
+          "key": "i_o_custkey",
+          "key_length": "5",
+          "used_key_parts": ["o_custkey"],
+          "ref": ["dbt3_s001.customer.c_custkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_o_orderdate",
+              "used_key_parts": ["o_orderDATE"]
+            },
+            "rows": 89,
+            "selectivity_pct": 5.933333333
+          },
+          "rows": 11,
+          "filtered": 5.933333397,
+          "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-03-08'",
+          "first_match": "nation"
+        }
+      }
+    ]
+  }
+}
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000082	9468.34
+Customer#000000091	4643.14
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000115	7508.92
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+create table t as
+select * from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+explain
+delete from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	ALL	PRIMARY,i_c_nationkey	NULL	NULL	NULL	150	Using where
+1	PRIMARY	nation	eq_ref|filter	PRIMARY,i_n_regionkey	PRIMARY|i_n_regionkey	4|5	dbt3_s001.customer.c_nationkey	1 (40%)	Using where; Using rowid filter
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (6%)	Using where; FirstMatch(nation); Using rowid filter
+explain format=json
+delete from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "ALL",
+          "possible_keys": ["PRIMARY", "i_c_nationkey"],
+          "rows": 150,
+          "filtered": 100,
+          "attached_condition": "customer.c_nationkey is not null"
+        }
+      },
+      {
+        "table": {
+          "table_name": "nation",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY", "i_n_regionkey"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["n_nationkey"],
+          "ref": ["dbt3_s001.customer.c_nationkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_n_regionkey",
+              "used_key_parts": ["n_regionkey"]
+            },
+            "rows": 10,
+            "selectivity_pct": 40
+          },
+          "rows": 1,
+          "filtered": 40,
+          "attached_condition": "nation.n_regionkey in (1,2)"
+        }
+      },
+      {
+        "table": {
+          "table_name": "orders",
+          "access_type": "ref",
+          "possible_keys": ["i_o_orderdate", "i_o_custkey"],
+          "key": "i_o_custkey",
+          "key_length": "5",
+          "used_key_parts": ["o_custkey"],
+          "ref": ["dbt3_s001.customer.c_custkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_o_orderdate",
+              "used_key_parts": ["o_orderDATE"]
+            },
+            "rows": 89,
+            "selectivity_pct": 5.933333333
+          },
+          "rows": 11,
+          "filtered": 5.933333397,
+          "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-03-08'",
+          "first_match": "nation"
+        }
+      }
+    ]
+  }
+}
+delete from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+insert into customer select * from t;
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000082	9468.34
+Customer#000000091	4643.14
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000115	7508.92
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+drop table t;
+explain
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1993-01-08");
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (14%)	Using where; FirstMatch(customer); Using rowid filter
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1993-01-08");
+c_name	c_acctbal
+Customer#000000008	6819.74
+Customer#000000035	1228.24
+Customer#000000061	1536.24
+Customer#000000097	2164.48
+Customer#000000121	6428.32
+Customer#000000133	2314.67
+create table t as
+select * from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1993-01-08");
+explain
+delete from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1993-01-08");
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (14%)	Using where; FirstMatch(customer); Using rowid filter
+delete from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1993-01-08");
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1993-01-08");
+c_name	c_acctbal
+insert into customer select * from t;
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1993-01-08");
+c_name	c_acctbal
+Customer#000000008	6819.74
+Customer#000000035	1228.24
+Customer#000000061	1536.24
+Customer#000000097	2164.48
+Customer#000000121	6428.32
+Customer#000000133	2314.67
+drop table t;
+# Materialization
+# ===============
+explain
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	28	
+1	PRIMARY	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.orders.o_custkey	1	
+2	MATERIALIZED	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	28	Using index condition; Using where
+explain format=json
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "<subquery2>",
+          "access_type": "ALL",
+          "possible_keys": ["distinct_key"],
+          "rows": 28,
+          "filtered": 100,
+          "materialized": {
+            "unique": 1,
+            "query_block": {
+              "select_id": 2,
+              "nested_loop": [
+                {
+                  "table": {
+                    "table_name": "orders",
+                    "access_type": "range",
+                    "possible_keys": ["i_o_orderdate", "i_o_custkey"],
+                    "key": "i_o_orderdate",
+                    "key_length": "4",
+                    "used_key_parts": ["o_orderDATE"],
+                    "rows": 28,
+                    "filtered": 100,
+                    "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'",
+                    "attached_condition": "orders.o_custkey is not null"
+                  }
+                }
+              ]
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["c_custkey"],
+          "ref": ["dbt3_s001.orders.o_custkey"],
+          "rows": 1,
+          "filtered": 100
+        }
+      }
+    ]
+  }
+}
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000005	794.47
+Customer#000000013	3857.34
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000022	591.98
+Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000032	3471.53
+Customer#000000035	1228.24
+Customer#000000037	-917.75
+Customer#000000038	6345.11
+Customer#000000040	1335.3
+Customer#000000052	5630.28
+Customer#000000056	6530.86
+Customer#000000065	8795.16
+Customer#000000076	5745.33
+Customer#000000091	4643.14
+Customer#000000098	-551.37
+Customer#000000109	-716.1
+Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000136	-842.39
+Customer#000000140	9963.15
+create table t as
+select * from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+explain
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	28	
+1	PRIMARY	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.orders.o_custkey	1	
+2	MATERIALIZED	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	28	Using index condition; Using where
+explain format=json
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "<subquery2>",
+          "access_type": "ALL",
+          "possible_keys": ["distinct_key"],
+          "rows": 28,
+          "filtered": 100,
+          "materialized": {
+            "unique": 1,
+            "query_block": {
+              "select_id": 2,
+              "nested_loop": [
+                {
+                  "table": {
+                    "table_name": "orders",
+                    "access_type": "range",
+                    "possible_keys": ["i_o_orderdate", "i_o_custkey"],
+                    "key": "i_o_orderdate",
+                    "key_length": "4",
+                    "used_key_parts": ["o_orderDATE"],
+                    "rows": 28,
+                    "filtered": 100,
+                    "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'",
+                    "attached_condition": "orders.o_custkey is not null"
+                  }
+                }
+              ]
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["c_custkey"],
+          "ref": ["dbt3_s001.orders.o_custkey"],
+          "rows": 1,
+          "filtered": 100
+        }
+      }
+    ]
+  }
+}
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+insert into customer select * from t;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000005	794.47
+Customer#000000013	3857.34
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000022	591.98
+Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000032	3471.53
+Customer#000000035	1228.24
+Customer#000000037	-917.75
+Customer#000000038	6345.11
+Customer#000000040	1335.3
+Customer#000000052	5630.28
+Customer#000000056	6530.86
+Customer#000000065	8795.16
+Customer#000000076	5745.33
+Customer#000000091	4643.14
+Customer#000000098	-551.37
+Customer#000000109	-716.1
+Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000136	-842.39
+Customer#000000140	9963.15
+drop table t;
+explain
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-06-09' and '1993-01-08');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	114	Using index condition; Using where
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-06-09' and '1993-01-08');
+c_name	c_acctbal
+Customer#000000001	711.56
+Customer#000000002	121.65
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
+Customer#000000034	8589.7
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
+Customer#000000053	4113.64
+Customer#000000055	4572.11
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
+Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000095	5327.38
+Customer#000000097	2164.48
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
+Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000148	2135.6
+Customer#000000149	8959.65
+create table t as
+select * from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-06-09' and '1993-01-08');
+explain
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-06-09' and '1993-01-08');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	114	Using index condition; Using where
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-06-09' and '1993-01-08');
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-06-09' and '1993-01-08');
+c_name	c_acctbal
+insert into customer select * from t;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-06-09' and '1993-01-08');
+c_name	c_acctbal
+Customer#000000001	711.56
+Customer#000000002	121.65
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
+Customer#000000034	8589.7
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
+Customer#000000053	4113.64
+Customer#000000055	4572.11
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
+Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000095	5327.38
+Customer#000000097	2164.48
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
+Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000148	2135.6
+Customer#000000149	8959.65
+drop table t;
+# Materialization SJM
+# ===================
+explain
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	28	
+1	PRIMARY	customer	eq_ref	PRIMARY	PRIMARY	4	<subquery2>.o_custkey	1	
+2	MATERIALIZED	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	28	Using index condition; Using temporary
+explain format=json
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "<subquery2>",
+          "access_type": "ALL",
+          "possible_keys": ["distinct_key"],
+          "rows": 28,
+          "filtered": 100,
+          "materialized": {
+            "unique": 1,
+            "query_block": {
+              "select_id": 2,
+              "having_condition": "count(orders.o_custkey) > 1",
+              "temporary_table": {
+                "nested_loop": [
+                  {
+                    "table": {
+                      "table_name": "orders",
+                      "access_type": "range",
+                      "possible_keys": ["i_o_orderdate"],
+                      "key": "i_o_orderdate",
+                      "key_length": "4",
+                      "used_key_parts": ["o_orderDATE"],
+                      "rows": 28,
+                      "filtered": 100,
+                      "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'"
+                    }
+                  }
+                ]
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["c_custkey"],
+          "ref": ["<subquery2>.o_custkey"],
+          "rows": 1,
+          "filtered": 100
+        }
+      }
+    ]
+  }
+}
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000056	6530.86
+Customer#000000118	3582.37
+create table t as
+select * from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+explain
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	28	
+1	PRIMARY	customer	eq_ref	PRIMARY	PRIMARY	4	<subquery2>.o_custkey	1	
+2	MATERIALIZED	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	28	Using index condition; Using temporary
+explain format=json
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "<subquery2>",
+          "access_type": "ALL",
+          "possible_keys": ["distinct_key"],
+          "rows": 28,
+          "filtered": 100,
+          "materialized": {
+            "unique": 1,
+            "query_block": {
+              "select_id": 2,
+              "having_condition": "count(orders.o_custkey) > 1",
+              "temporary_table": {
+                "nested_loop": [
+                  {
+                    "table": {
+                      "table_name": "orders",
+                      "access_type": "range",
+                      "possible_keys": ["i_o_orderdate"],
+                      "key": "i_o_orderdate",
+                      "key_length": "4",
+                      "used_key_parts": ["o_orderDATE"],
+                      "rows": 28,
+                      "filtered": 100,
+                      "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'"
+                    }
+                  }
+                ]
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["c_custkey"],
+          "ref": ["<subquery2>.o_custkey"],
+          "rows": 1,
+          "filtered": 100
+        }
+      }
+    ]
+  }
+}
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+insert into customer select * from t;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000056	6530.86
+Customer#000000118	3582.37
+drop table t;
+explain
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	dbt3_s001.customer.c_custkey	1	
+2	MATERIALIZED	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	242	Using index condition; Using temporary
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000016	4681.03
+Customer#000000037	-917.75
+Customer#000000046	5744.59
+Customer#000000091	4643.14
+Customer#000000103	2757.45
+Customer#000000118	3582.37
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+create table t as
+select * from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+explain
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	dbt3_s001.customer.c_custkey	1	
+2	MATERIALIZED	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	242	Using index condition; Using temporary
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+c_name	c_acctbal
+insert into customer select * from t;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000016	4681.03
+Customer#000000037	-917.75
+Customer#000000046	5744.59
+Customer#000000091	4643.14
+Customer#000000103	2757.45
+Customer#000000118	3582.37
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+drop table t;
+# Pullout PS
+# ==========
+prepare stmt from "
+delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+";
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+1729	12137.76
+2880	145761.99
+3142	16030.15
+5095	184583.99
+5121	150334.57
+5382	138423.03
+644	201268.06
+737	12984.85
+create table t as
+select * from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+execute stmt;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+insert into orders select * from t;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+1729	12137.76
+2880	145761.99
+3142	16030.15
+5095	184583.99
+5121	150334.57
+5382	138423.03
+644	201268.06
+737	12984.85
+create table r as
+select * from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+execute stmt;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+insert into orders select * from r;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+1729	12137.76
+2880	145761.99
+3142	16030.15
+5095	184583.99
+5121	150334.57
+5382	138423.03
+644	201268.06
+737	12984.85
+drop table t,r;
+deallocate prepare stmt;
+# FirstMatch PS
+# =============
+prepare stmt from "
+delete from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+";
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000082	9468.34
+Customer#000000091	4643.14
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000115	7508.92
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+create table t as
+select * from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+execute stmt;
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+insert into customer select * from t;
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000082	9468.34
+Customer#000000091	4643.14
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000115	7508.92
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+create table r as
+select * from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+execute stmt;
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+insert into customer select * from r;
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000082	9468.34
+Customer#000000091	4643.14
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000115	7508.92
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+drop table t,r;
+deallocate prepare stmt;
+# Materialization PS
+# ==================
+prepare stmt from "
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08') and c_name like ?;
+";
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000005	794.47
+Customer#000000013	3857.34
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000022	591.98
+Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000032	3471.53
+Customer#000000035	1228.24
+Customer#000000037	-917.75
+Customer#000000038	6345.11
+Customer#000000040	1335.3
+Customer#000000052	5630.28
+Customer#000000056	6530.86
+Customer#000000065	8795.16
+Customer#000000076	5745.33
+Customer#000000091	4643.14
+Customer#000000098	-551.37
+Customer#000000109	-716.1
+Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000136	-842.39
+Customer#000000140	9963.15
+set @a1='Customer#%1_';
+create table t as
+select * from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08') and c_name like @a1;
+execute stmt using @a1;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000005	794.47
+Customer#000000022	591.98
+Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000032	3471.53
+Customer#000000035	1228.24
+Customer#000000037	-917.75
+Customer#000000038	6345.11
+Customer#000000040	1335.3
+Customer#000000052	5630.28
+Customer#000000056	6530.86
+Customer#000000065	8795.16
+Customer#000000076	5745.33
+Customer#000000091	4643.14
+Customer#000000098	-551.37
+Customer#000000109	-716.1
+Customer#000000136	-842.39
+Customer#000000140	9963.15
+insert into customer select * from t;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000005	794.47
+Customer#000000013	3857.34
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000022	591.98
+Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000032	3471.53
+Customer#000000035	1228.24
+Customer#000000037	-917.75
+Customer#000000038	6345.11
+Customer#000000040	1335.3
+Customer#000000052	5630.28
+Customer#000000056	6530.86
+Customer#000000065	8795.16
+Customer#000000076	5745.33
+Customer#000000091	4643.14
+Customer#000000098	-551.37
+Customer#000000109	-716.1
+Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000136	-842.39
+Customer#000000140	9963.15
+set @a2='Customer#%3_';
+create table r as
+select * from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08') and c_name like @a2;
+execute stmt using @a2;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000005	794.47
+Customer#000000013	3857.34
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000022	591.98
+Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000040	1335.3
+Customer#000000052	5630.28
+Customer#000000056	6530.86
+Customer#000000065	8795.16
+Customer#000000076	5745.33
+Customer#000000091	4643.14
+Customer#000000098	-551.37
+Customer#000000109	-716.1
+Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000140	9963.15
+insert into customer select * from r;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000005	794.47
+Customer#000000013	3857.34
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000022	591.98
+Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000032	3471.53
+Customer#000000035	1228.24
+Customer#000000037	-917.75
+Customer#000000038	6345.11
+Customer#000000040	1335.3
+Customer#000000052	5630.28
+Customer#000000056	6530.86
+Customer#000000065	8795.16
+Customer#000000076	5745.33
+Customer#000000091	4643.14
+Customer#000000098	-551.37
+Customer#000000109	-716.1
+Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000136	-842.39
+Customer#000000140	9963.15
+drop table t,r;
+deallocate prepare stmt;
+# Materialization SJM PS
+# ======================
+prepare stmt from "
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08') and c_acctbal between ? and ?;
+";
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000056	6530.86
+Customer#000000118	3582.37
+set @a1=3500;
+set @a2=4000;
+create table t as
+select * from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1) and c_acctbal between @a1 and @a2;
+execute stmt using @a1, @a2;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000056	6530.86
+insert into customer select * from t;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000056	6530.86
+Customer#000000118	3582.37
+set @a3=-1000;
+set @a4=3500;
+create table r as
+select * from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1) and c_acctbal between @a3 and @a4;
+execute stmt using @a3, @a4;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000056	6530.86
+Customer#000000118	3582.37
+insert into customer select * from r;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000056	6530.86
+Customer#000000118	3582.37
+drop table t,r;
+deallocate prepare stmt;
+# Pullout SP
+# ==========
+create procedure p(a1 int, a2 int)
+delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU')) and o_totalprice between a1 and a2;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+1729	12137.76
+2880	145761.99
+3142	16030.15
+5095	184583.99
+5121	150334.57
+644	201268.06
+737	12984.85
+create table t as
+select * from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU')) and o_totalprice between 150000 and 200000;
+call p(150000, 200000);
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+1729	12137.76
+2880	145761.99
+3142	16030.15
+644	201268.06
+737	12984.85
+insert into orders select * from t;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+1729	12137.76
+2880	145761.99
+3142	16030.15
+5095	184583.99
+5121	150334.57
+644	201268.06
+737	12984.85
+create table r as
+select * from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU')) and o_totalprice between 180000 and 210000;
+call p(180000, 210000);
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+1729	12137.76
+2880	145761.99
+3142	16030.15
+5121	150334.57
+737	12984.85
+insert into orders select * from r;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+1729	12137.76
+2880	145761.99
+3142	16030.15
+5095	184583.99
+5121	150334.57
+644	201268.06
+737	12984.85
+drop table t,r;
+drop procedure p;
+# FirstMatch SP
+# =============
+create procedure p(a int)
+delete from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08') and c_acctbal > a;
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000037	-917.75
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000082	9468.34
+Customer#000000091	4643.14
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000115	7508.92
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+create table t as
+select * from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08') and c_acctbal > 4000;
+call p(4000);
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000028	1007.18
+Customer#000000037	-917.75
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000097	2164.48
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000133	2314.67
+insert into customer select * from t;
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000037	-917.75
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000082	9468.34
+Customer#000000091	4643.14
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000115	7508.92
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+create table r as
+select * from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08') and c_acctbal > 2000;
+call p(2000);
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000028	1007.18
+Customer#000000037	-917.75
+Customer#000000047	274.58
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+insert into customer select * from r;
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000037	-917.75
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000082	9468.34
+Customer#000000091	4643.14
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000115	7508.92
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+drop table t,r;
+drop procedure p;
+# Materialization SP
+# ==================
+create procedure p()
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000016	4681.03
+Customer#000000025	7133.7
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000038	6345.11
+Customer#000000052	5630.28
+Customer#000000056	6530.86
+Customer#000000065	8795.16
+Customer#000000076	5745.33
+Customer#000000091	4643.14
+Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000140	9963.15
+create table t as
+select * from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+call p();
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+insert into customer select * from t;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000016	4681.03
+Customer#000000025	7133.7
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000038	6345.11
+Customer#000000052	5630.28
+Customer#000000056	6530.86
+Customer#000000065	8795.16
+Customer#000000076	5745.33
+Customer#000000091	4643.14
+Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000140	9963.15
+create table r as
+select * from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+call p();
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+insert into customer select * from r;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000016	4681.03
+Customer#000000025	7133.7
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000038	6345.11
+Customer#000000052	5630.28
+Customer#000000056	6530.86
+Customer#000000065	8795.16
+Customer#000000076	5745.33
+Customer#000000091	4643.14
+Customer#000000115	7508.92
+Customer#000000116	8403.99
+Customer#000000118	3582.37
+Customer#000000140	9963.15
+drop table t,r;
+drop procedure p;
+# Materialization SJM SP
+# ======================
+create procedure p()
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000056	6530.86
+Customer#000000118	3582.37
+create table t as
+select * from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+call p();
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+insert into customer select * from t;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000056	6530.86
+Customer#000000118	3582.37
+create table r as
+select * from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+call p();
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+insert into customer select * from r;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000056	6530.86
+Customer#000000118	3582.37
+drop table t,r;
+drop procedure p;
+# Checking limitations
+# ====================
+# Check for DELETE ... RETURNING with SJ subquery in WHERE
+select c_name from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name
+Customer#000000013
+Customer#000000016
+Customer#000000025
+Customer#000000032
+Customer#000000037
+Customer#000000038
+Customer#000000052
+Customer#000000056
+Customer#000000065
+Customer#000000076
+Customer#000000091
+Customer#000000115
+Customer#000000116
+Customer#000000118
+Customer#000000140
+create table t as
+select * from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+explain
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08') returning c_name;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	ALL	NULL	NULL	NULL	NULL	141	Using where
+2	DEPENDENT SUBQUERY	orders	index_subquery|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	func	175 (2%)	Using where; Using rowid filter
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08') returning c_name;
+c_name
+Customer#000000013
+Customer#000000016
+Customer#000000025
+Customer#000000032
+Customer#000000037
+Customer#000000038
+Customer#000000052
+Customer#000000056
+Customer#000000065
+Customer#000000076
+Customer#000000091
+Customer#000000115
+Customer#000000116
+Customer#000000118
+Customer#000000140
+select c_name from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name
+insert into customer select * from t;
+select c_name from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name
+Customer#000000013
+Customer#000000016
+Customer#000000025
+Customer#000000032
+Customer#000000037
+Customer#000000038
+Customer#000000052
+Customer#000000056
+Customer#000000065
+Customer#000000076
+Customer#000000091
+Customer#000000115
+Customer#000000116
+Customer#000000118
+Customer#000000140
+drop table t;
+select c_name from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name
+Customer#000000013
+Customer#000000032
+Customer#000000037
+Customer#000000056
+Customer#000000118
+create table t as
+select * from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+explain
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1) returning c_name;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	ALL	NULL	NULL	NULL	NULL	141	Using where
+2	DEPENDENT SUBQUERY	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	28	Using index condition; Using temporary
+delete from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1) returning c_name;
+c_name
+Customer#000000013
+Customer#000000032
+Customer#000000037
+Customer#000000056
+Customer#000000118
+select c_name from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name
+insert into customer select * from t;
+select c_name from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name
+Customer#000000013
+Customer#000000032
+Customer#000000037
+Customer#000000056
+Customer#000000118
+drop table t;
+# 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
+1856	189361.42
+324	26868.85
+4903	34363.63
+5607	24660.06
+# Should not use semi-join conversion because has ORDER BY ... LIMIT
+explain
+delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (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
+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
+4903	34363.63
+5607	24660.06
+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))
+order by o_totalprice limit 500;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (1,2));
+o_orderkey	o_totalprice
+insert into orders select * from t;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (1,2));
+o_orderkey	o_totalprice
+1221	117397.16
+1856	189361.42
+324	26868.85
+4903	34363.63
+5607	24660.06
+drop table t;
+# Should use semi-join converion
+explain
+delete from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (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
+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
+4903	34363.63
+5607	24660.06
+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));
+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
+insert into orders select * from t;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (1,2));
+o_orderkey	o_totalprice
+1221	117397.16
+1856	189361.42
+324	26868.85
+4903	34363.63
+5607	24660.06
+drop table t;
+DROP DATABASE dbt3_s001;
diff --git a/mysql-test/main/delete_single_to_multi.test b/mysql-test/main/delete_single_to_multi.test
new file mode 100644
index 00000000000..7d4811fbe5f
--- /dev/null
+++ b/mysql-test/main/delete_single_to_multi.test
@@ -0,0 +1,797 @@
+--disable_warnings
+DROP DATABASE IF EXISTS dbt3_s001;
+--enable_warnings
+
+CREATE DATABASE dbt3_s001;
+
+use dbt3_s001;
+
+--disable_query_log
+--disable_result_log
+--disable_warnings
+--source include/dbt3_s001.inc
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+create index i_n_name on nation(n_name);
+analyze table nation;
+
+
+--echo # Pullout
+--echo # =======
+
+let $c1=
+        o_orderDATE between '1992-01-01' and '1992-06-30' and
+        o_custkey in (select c_custkey from customer
+                        where c_nationkey in (select n_nationkey from nation
+                                                where n_name='PERU'));
+
+eval
+explain
+select o_orderkey, o_totalprice from orders where $c1;
+eval
+explain format=json
+select o_orderkey, o_totalprice from orders where $c1;
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+eval
+create table t as
+select * from orders where $c1;
+
+eval
+explain
+delete from orders where $c1;
+eval
+explain format=json
+delete from orders where $c1;
+eval
+delete from orders where $c1;
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+
+
+insert into orders select * from t;
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+drop table t;
+
+
+let $c2=
+       (ps_partkey, ps_suppkey) in
+         (select p_partkey, s_suppkey from part, supplier
+            where p_retailprice between 901 and 910 and
+                  s_nationkey in (select n_nationkey from nation
+                                    where n_name='PERU'));
+
+eval
+explain
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2;
+--sorted_result
+eval
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2;
+eval
+create table t as
+select * from partsupp where $c2;
+
+eval
+explain
+delete from partsupp where $c2;
+eval
+delete from partsupp where $c2;
+eval
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2;
+
+insert into partsupp select * from t;
+--sorted_result
+eval
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2;
+drop table t;
+
+
+let $c3=
+        ps_partkey in (select p_partkey from part
+                         where p_retailprice between 901 and 910) and
+        ps_suppkey in (select s_suppkey from supplier
+                         where s_nationkey in (select n_nationkey from nation
+                                                 where n_name='PERU'));
+eval
+explain
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3;
+--sorted_result
+eval
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3;
+eval
+create table t as
+select * from partsupp where $c3;
+
+eval
+explain
+delete from partsupp where $c3;
+eval
+delete from partsupp where $c3;
+eval
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3;
+
+insert into partsupp select * from t;
+--sorted_result
+eval
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3;
+drop table t;
+
+
+let $c4=
+        l_orderkey in (select o_orderkey from orders
+                         where o_custkey in
+                               (select c_custkey from customer
+                                  where c_nationkey in
+                                        (select n_nationkey from nation
+                                           where n_name='PERU'))
+                               and
+                              o_orderDATE between '1992-06-30' and '1992-12-31')
+        and
+        (l_partkey, l_suppkey) in
+        (select p_partkey, s_suppkey from part, supplier
+           where p_retailprice between 901 and 1000 and
+                 s_nationkey in (select n_nationkey from nation
+                                   where n_name='PERU'));
+
+eval
+explain
+select l_orderkey, l_linenumber, l_tax from lineitem where $c4;
+--sorted_result
+eval
+select l_orderkey, l_linenumber, l_tax from lineitem where $c4;
+eval
+create table t as
+select * from lineitem where $c4;
+
+eval
+explain
+delete from lineitem where $c4;
+eval
+delete from lineitem where $c4;
+eval
+select l_orderkey, l_linenumber, l_tax from lineitem where $c4;
+
+insert into lineitem select * from t;
+--sorted_result
+eval
+select l_orderkey, l_linenumber, l_tax from lineitem where $c4;
+drop table t;
+
+
+--echo # FirstMatch
+--echo # ==========
+
+let $c5=
+        c_nationkey in (select n_nationkey from nation
+                          where n_regionkey in (1,2))
+        and
+        c_custkey in (select o_custkey from orders
+                      where o_orderDATE between '1992-10-09' and '1993-03-08');
+
+eval
+explain
+select c_name, c_acctbal from customer where $c5;
+eval
+explain format=json
+select c_name, c_acctbal from customer where $c5;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c5;
+eval
+create table t as
+select * from customer where $c5;
+
+eval
+explain
+delete from customer where $c5;
+eval
+explain format=json
+delete from customer where $c5;
+eval
+delete from customer where $c5;
+eval
+select c_name, c_acctbal from customer where $c5;
+
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c5;
+drop table t;
+
+
+let $c6=
+        c_nationkey in (select n_nationkey from nation where n_name='PERU')
+        and
+        c_custkey in (select o_custkey from orders
+                      where o_orderDATE between "1992-01-09" and "1993-01-08");
+
+eval
+explain
+select c_name, c_acctbal from customer where $c6;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c6;
+eval
+create table t as
+select * from customer where $c6;
+
+eval
+explain
+delete from customer where $c6;
+eval
+delete from customer where $c6;
+eval
+select c_name, c_acctbal from customer where $c6;
+
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c6;
+drop table t;
+
+
+--echo # Materialization
+--echo # ===============
+
+let $c7=
+        c_custkey in (select o_custkey from orders
+                      where o_orderDATE between '1992-01-09' and '1992-03-08');
+
+eval
+explain
+select c_name, c_acctbal from customer where $c7;
+eval
+explain format=json
+select c_name, c_acctbal from customer where $c7;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c7;
+eval
+create table t as
+select * from customer where $c7;
+
+eval
+explain
+delete from customer where $c7;
+eval
+explain format=json
+delete from customer where $c7;
+eval
+delete from customer where $c7;
+eval
+select c_name, c_acctbal from customer where $c7;
+
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c7;
+drop table t;
+
+
+let $c8=
+        c_custkey in (select o_custkey from orders
+                      where o_orderDATE between '1992-06-09' and '1993-01-08');
+
+eval
+explain
+select c_name, c_acctbal from customer where $c8;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c8;
+eval
+create table t as
+select * from customer where $c8;
+
+eval
+explain
+delete from customer where $c8;
+eval
+delete from customer where $c8;
+eval
+select c_name, c_acctbal from customer where $c8;
+
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c8;
+drop table t;
+
+
+--echo # Materialization SJM
+--echo # ===================
+
+let $c9=
+        c_custkey in (select o_custkey from orders
+                        where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+
+eval
+explain
+select c_name, c_acctbal from customer where $c9;
+eval
+explain format=json
+select c_name, c_acctbal from customer where $c9;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c9;
+eval
+create table t as
+select * from customer where $c9;
+
+eval
+explain
+delete from customer where $c9;
+eval
+explain format=json
+delete from customer where $c9;
+eval
+delete from customer where $c9;
+eval
+select c_name, c_acctbal from customer where $c9;
+
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c9;
+drop table t;
+
+
+let $c10=
+        c_custkey in (select o_custkey from orders
+                        where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+
+eval
+explain
+select c_name, c_acctbal from customer where $c10;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c10;
+eval
+create table t as
+select * from customer where $c10;
+
+eval
+explain
+delete from customer where $c10;
+eval
+delete from customer where $c10;
+eval
+select c_name, c_acctbal from customer where $c10;
+
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c10;
+drop table t;
+
+
+--echo # Pullout PS
+--echo # ==========
+
+eval
+prepare stmt from "
+delete from orders where $c1;
+";
+
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+eval
+create table t as
+select * from orders where $c1;
+execute stmt;
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+insert into orders select * from t;
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+eval
+create table r as
+select * from orders where $c1;
+execute stmt;
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+insert into orders select * from r;
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+drop table t,r;
+
+deallocate prepare stmt;
+
+
+--echo # FirstMatch PS
+--echo # =============
+
+eval
+prepare stmt from "
+delete from customer where $c5;
+";
+
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c5;
+eval
+create table t as
+select * from customer where $c5;
+execute stmt;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c5;
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c5;
+eval
+create table r as
+select * from customer where $c5;
+execute stmt;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c5;
+insert into customer select * from r;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c5;
+drop table t,r;
+
+deallocate prepare stmt;
+
+
+--echo # Materialization PS
+--echo # ==================
+
+eval
+prepare stmt from "
+delete from customer where $c7 and c_name like ?;
+";
+
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c7;
+set @a1='Customer#%1_';
+eval
+create table t as
+select * from customer where $c7 and c_name like @a1;
+execute stmt using @a1;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c7;
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c7;
+set @a2='Customer#%3_';
+eval
+create table r as
+select * from customer where $c7 and c_name like @a2;
+execute stmt using @a2;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c7;
+insert into customer select * from r;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c7;
+drop table t,r;
+
+deallocate prepare stmt;
+
+
+--echo # Materialization SJM PS
+--echo # ======================
+
+eval
+prepare stmt from "
+delete from customer where $c7 and c_acctbal between ? and ?;
+";
+
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c9;
+set @a1=3500;
+set @a2=4000;
+eval
+create table t as
+select * from customer where $c9 and c_acctbal between @a1 and @a2;
+execute stmt using @a1, @a2;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c9;
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c9;
+set @a3=-1000;
+set @a4=3500;
+eval
+create table r as
+select * from customer where $c9 and c_acctbal between @a3 and @a4;
+execute stmt using @a3, @a4;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c9;
+insert into customer select * from r;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c9;
+drop table t,r;
+
+deallocate prepare stmt;
+
+
+--echo # Pullout SP
+--echo # ==========
+
+eval
+create procedure p(a1 int, a2 int)
+delete from orders where $c1 and o_totalprice between a1 and a2;
+
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+eval
+create table t as
+select * from orders where $c1 and o_totalprice between 150000 and 200000;
+call p(150000, 200000);
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+insert into orders select * from t;
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+eval
+create table r as
+select * from orders where $c1 and o_totalprice between 180000 and 210000;
+call p(180000, 210000);
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+insert into orders select * from r;
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+drop table t,r;
+
+drop procedure p;
+
+
+--echo # FirstMatch SP
+--echo # =============
+
+eval
+create procedure p(a int)
+delete from customer where $c5 and c_acctbal > a;
+
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c5;
+eval
+create table t as
+select * from customer where $c5 and c_acctbal > 4000;
+call p(4000);
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c5;
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c5;
+eval
+create table r as
+select * from customer where $c5 and c_acctbal > 2000;
+call p(2000);
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c5;
+insert into customer select * from r;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c5;
+drop table t,r;
+
+drop procedure p;
+
+
+--echo # Materialization SP
+--echo # ==================
+
+eval
+create procedure p()
+delete from customer where $c7;
+
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c7;
+eval
+create table t as
+select * from customer where $c7;
+call p();
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c7;
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c7;
+eval
+create table r as
+select * from customer where $c7;
+call p();
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c7;
+insert into customer select * from r;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c7;
+drop table t,r;
+
+drop procedure p;
+
+
+--echo # Materialization SJM SP
+--echo # ======================
+
+eval
+create procedure p()
+delete from customer where $c9;
+
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c9;
+eval
+create table t as
+select * from customer where $c9;
+call p();
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c9;
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c9;
+eval
+create table r as
+select * from customer where $c9;
+call p();
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c9;
+insert into customer select * from r;
+--sorted_result
+eval
+select c_name, c_acctbal from customer where $c9;
+drop table t,r;
+
+drop procedure p;
+
+--echo # Checking limitations
+--echo # ====================
+
+--echo # Check for DELETE ... RETURNING with SJ subquery in WHERE
+
+--sorted_result
+eval
+select c_name from customer where $c7;
+eval
+create table t as
+select * from customer where $c7;
+eval
+explain
+delete from customer where $c7 returning c_name;
+--sorted_result
+eval
+delete from customer where $c7 returning c_name;
+--sorted_result
+eval
+select c_name from customer where $c7;
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name from customer where $c7;
+drop table t;
+
+--sorted_result
+eval
+select c_name from customer where $c9;
+eval
+create table t as
+select * from customer where $c9;
+eval
+explain
+delete from customer where $c9 returning c_name;
+--sorted_result
+eval
+delete from customer where $c9 returning c_name;
+--sorted_result
+eval
+select c_name from customer where $c9;
+insert into customer select * from t;
+--sorted_result
+eval
+select c_name from customer where $c9;
+drop table t;
+
+--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;
+drop table t;
+
+--echo # Check for DELETE ... ORDER BY ...LIMIT  with SJ subquery in WHERE
+
+let $c11=
+        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));
+
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c11;
+
+--echo # Should not use semi-join conversion because has ORDER BY ... LIMIT
+eval
+explain
+delete from orders where $c11
+order by o_totalprice limit 500;
+eval
+create table t as
+select * from orders where $c11;
+select o_orderkey, o_totalprice from t;
+eval
+delete from orders where $c11
+order by o_totalprice limit 500;
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c11;
+insert into orders select * from t;
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c11;
+drop table t;
+
+--echo # Should use semi-join converion
+eval
+explain
+delete from orders where $c11;
+eval
+create table t as
+select * from orders where $c11;
+select o_orderkey, o_totalprice from t;
+eval
+delete from orders where $c11;
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c11;
+insert into orders select * from t;
+--sorted_result
+eval
+select o_orderkey, o_totalprice from orders where $c11;
+drop table t;
+
+DROP DATABASE dbt3_s001;
diff --git a/mysql-test/main/log_state.result b/mysql-test/main/log_state.result
index 5e7aac81b0c..1b1c7376f18 100644
--- a/mysql-test/main/log_state.result
+++ b/mysql-test/main/log_state.result
@@ -243,7 +243,7 @@ rows_examined	sql_text
 4	UPDATE t1 SET a=a+sleep(.02) WHERE a>2
 8	UPDATE t1 SET a=a+sleep(.02) ORDER BY a DESC
 1	UPDATE t2 set b=b+sleep(.02) limit 1
-4	UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2)
+10	UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2)
 6	DELETE FROM t1 WHERE a=a+sleep(.02) ORDER BY a LIMIT 2
 disconnect con2;
 connection default;
diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result
index 20b769b8871..5df9ced4def 100644
--- a/mysql-test/main/myisam_explain_non_select_all.result
+++ b/mysql-test/main/myisam_explain_non_select_all.result
@@ -240,14 +240,16 @@ Warnings:
 Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 EXPLAIN UPDATE        t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -271,8 +273,9 @@ Handler_read_key	5
 Handler_read_rnd_next	8
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_key	4
-Handler_read_rnd_next	5
+Handler_read_key	5
+Handler_read_rnd	3
+Handler_read_rnd_next	12
 Handler_update	3
 
 DROP TABLE t1, t2;
@@ -290,13 +293,13 @@ Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be
 EXPLAIN UPDATE        t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; FirstMatch(t1)
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; FirstMatch(t1)
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
 # Status of EXPLAIN EXTENDED query
@@ -984,14 +987,16 @@ Warnings:
 Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -1015,8 +1020,8 @@ Handler_read_key	7
 Handler_read_rnd_next	8
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_key	4
-Handler_read_rnd_next	10
+Handler_read_key	7
+Handler_read_rnd_next	8
 Handler_update	3
 
 DROP TABLE t1, t2;
@@ -1079,14 +1084,14 @@ Warnings:
 Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 EXPLAIN DELETE   FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; FirstMatch(t1)
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; FirstMatch(t1)
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -3074,14 +3079,14 @@ Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be
 EXPLAIN UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	<derived3>	index_subquery	key0	key0	5	func	2	
+1	PRIMARY	<derived3>	ref	key0	key0	5	test.t1.a	2	FirstMatch(t1)
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using filesort
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-2	DEPENDENT SUBQUERY	<derived3>	index_subquery	key0	key0	5	func	2	100.00	
+1	PRIMARY	<derived3>	ref	key0	key0	5	test.t1.a	2	100.00	FirstMatch(t1)
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 0555f7263c8..e46bd80bbf8 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -3948,6 +3948,16 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
                 }
               },
               {
+                "table": "t0",
+                "rowid_filters": [
+                  {
+                    "key": "a",
+                    "build_cost": 0.174715752,
+                    "rows": 3
+                  }
+                ]
+              },
+              {
                 "selectivity_for_indexes": [
                   {
                     "index_name": "a",
@@ -4013,6 +4023,16 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
                 }
               },
               {
+                "table": "t1",
+                "rowid_filters": [
+                  {
+                    "key": "a",
+                    "build_cost": 0.174715752,
+                    "rows": 3
+                  }
+                ]
+              },
+              {
                 "selectivity_for_indexes": [
                   {
                     "index_name": "a",
diff --git a/mysql-test/main/update_single_to_multi.result b/mysql-test/main/update_single_to_multi.result
new file mode 100644
index 00000000000..015fd06cf4c
--- /dev/null
+++ b/mysql-test/main/update_single_to_multi.result
@@ -0,0 +1,2302 @@
+DROP DATABASE IF EXISTS dbt3_s001;
+CREATE DATABASE dbt3_s001;
+use dbt3_s001;
+create index i_n_name on nation(n_name);
+analyze table nation;
+Table	Op	Msg_type	Msg_text
+dbt3_s001.nation	analyze	status	Engine-independent statistics collected
+dbt3_s001.nation	analyze	status	OK
+# Pullout
+# =======
+explain
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (7%)	Using where; Using rowid filter
+explain format=json
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "nation",
+          "access_type": "ref",
+          "possible_keys": ["PRIMARY", "i_n_name"],
+          "key": "i_n_name",
+          "key_length": "26",
+          "used_key_parts": ["n_name"],
+          "ref": ["const"],
+          "rows": 1,
+          "filtered": 100,
+          "index_condition": "nation.n_name = 'PERU'"
+        }
+      },
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "ref",
+          "possible_keys": ["PRIMARY", "i_c_nationkey"],
+          "key": "i_c_nationkey",
+          "key_length": "5",
+          "used_key_parts": ["c_nationkey"],
+          "ref": ["dbt3_s001.nation.n_nationkey"],
+          "rows": 11,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "orders",
+          "access_type": "ref",
+          "possible_keys": ["i_o_orderdate", "i_o_custkey"],
+          "key": "i_o_custkey",
+          "key_length": "5",
+          "used_key_parts": ["o_custkey"],
+          "ref": ["dbt3_s001.customer.c_custkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_o_orderdate",
+              "used_key_parts": ["o_orderDATE"]
+            },
+            "rows": 108,
+            "selectivity_pct": 7.2
+          },
+          "rows": 11,
+          "filtered": 7.199999809,
+          "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'"
+        }
+      }
+    ]
+  }
+}
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+644	201268.06
+2880	145761.99
+3142	16030.15
+5382	138423.03
+5095	184583.99
+737	12984.85
+1729	12137.76
+5121	150334.57
+explain
+update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (7%)	Using where; Using rowid filter
+explain format=json
+update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "nation",
+          "access_type": "ref",
+          "possible_keys": ["PRIMARY", "i_n_name"],
+          "key": "i_n_name",
+          "key_length": "26",
+          "used_key_parts": ["n_name"],
+          "ref": ["const"],
+          "rows": 1,
+          "filtered": 100,
+          "index_condition": "nation.n_name = 'PERU'"
+        }
+      },
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "ref",
+          "possible_keys": ["PRIMARY", "i_c_nationkey"],
+          "key": "i_c_nationkey",
+          "key_length": "5",
+          "used_key_parts": ["c_nationkey"],
+          "ref": ["dbt3_s001.nation.n_nationkey"],
+          "rows": 11,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "orders",
+          "access_type": "ref",
+          "possible_keys": ["i_o_orderdate", "i_o_custkey"],
+          "key": "i_o_custkey",
+          "key_length": "5",
+          "used_key_parts": ["o_custkey"],
+          "ref": ["dbt3_s001.customer.c_custkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_o_orderdate",
+              "used_key_parts": ["o_orderDATE"]
+            },
+            "rows": 108,
+            "selectivity_pct": 7.2
+          },
+          "rows": 11,
+          "filtered": 7.199999809,
+          "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'"
+        }
+      }
+    ]
+  }
+}
+update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+644	201218.06
+2880	145711.99
+3142	15980.15
+5382	138373.03
+5095	184533.99
+737	12934.85
+1729	12087.76
+5121	150284.57
+update orders set o_totalprice= o_totalprice+50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+644	201268.06
+2880	145761.99
+3142	16030.15
+5382	138423.03
+5095	184583.99
+737	12984.85
+1729	12137.76
+5121	150334.57
+explain
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 910 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
+1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 910 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+ps_partkey	ps_suppkey	ps_supplycost
+4	1	444.37
+6	1	642.13
+8	1	957.34
+1	8	357.84
+3	8	645.4
+5	8	50.52
+7	8	763.98
+explain
+update partsupp set ps_supplycost = ps_supplycost+2 where (ps_partkey, ps_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 910 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
+1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+update partsupp set ps_supplycost = ps_supplycost+2 where (ps_partkey, ps_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 910 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 910 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+ps_partkey	ps_suppkey	ps_supplycost
+4	1	446.37
+6	1	644.13
+8	1	959.34
+1	8	359.84
+3	8	647.4
+5	8	52.52
+7	8	765.98
+update partsupp set ps_supplycost = ps_supplycost-2 where (ps_partkey, ps_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 910 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where (ps_partkey, ps_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 910 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+ps_partkey	ps_suppkey	ps_supplycost
+4	1	444.37
+6	1	642.13
+8	1	957.34
+1	8	357.84
+3	8	645.4
+5	8	50.52
+7	8	763.98
+explain
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part
+where p_retailprice between 901 and 910) and
+ps_suppkey in (select s_suppkey from supplier
+where s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
+1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part
+where p_retailprice between 901 and 910) and
+ps_suppkey in (select s_suppkey from supplier
+where s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+ps_partkey	ps_suppkey	ps_supplycost
+4	1	444.37
+6	1	642.13
+8	1	957.34
+1	8	357.84
+3	8	645.4
+5	8	50.52
+7	8	763.98
+explain
+update partsupp set ps_supplycost = ps_supplycost+10 where ps_partkey in (select p_partkey from part
+where p_retailprice between 901 and 910) and
+ps_suppkey in (select s_suppkey from supplier
+where s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	16	
+1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+update partsupp set ps_supplycost = ps_supplycost+10 where ps_partkey in (select p_partkey from part
+where p_retailprice between 901 and 910) and
+ps_suppkey in (select s_suppkey from supplier
+where s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part
+where p_retailprice between 901 and 910) and
+ps_suppkey in (select s_suppkey from supplier
+where s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+ps_partkey	ps_suppkey	ps_supplycost
+4	1	454.37
+6	1	652.13
+8	1	967.34
+1	8	367.84
+3	8	655.4
+5	8	60.52
+7	8	773.98
+update partsupp set ps_supplycost = ps_supplycost-10 where ps_partkey in (select p_partkey from part
+where p_retailprice between 901 and 910) and
+ps_suppkey in (select s_suppkey from supplier
+where s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where ps_partkey in (select p_partkey from part
+where p_retailprice between 901 and 910) and
+ps_suppkey in (select s_suppkey from supplier
+where s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+ps_partkey	ps_suppkey	ps_supplycost
+4	1	444.37
+6	1	642.13
+8	1	957.34
+1	8	357.84
+3	8	645.4
+5	8	50.52
+7	8	763.98
+explain
+select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders
+where o_custkey in
+(select c_custkey from customer
+where c_nationkey in
+(select n_nationkey from nation
+where n_name='PERU'))
+and
+o_orderDATE between '1992-06-30' and '1992-12-31')
+and
+(l_partkey, l_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 1000 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
+1	PRIMARY	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey	5	dbt3_s001.supplier.s_suppkey	100	Using where
+1	PRIMARY	orders	eq_ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	PRIMARY|i_o_orderdate	4|4	dbt3_s001.lineitem.l_orderkey	1 (7%)	Using where; Using rowid filter
+1	PRIMARY	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
+1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
+select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders
+where o_custkey in
+(select c_custkey from customer
+where c_nationkey in
+(select n_nationkey from nation
+where n_name='PERU'))
+and
+o_orderDATE between '1992-06-30' and '1992-12-31')
+and
+(l_partkey, l_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 1000 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+l_orderkey	l_linenumber	l_tax
+4996	1	0.01
+933	1	0.04
+2500	2	0.02
+2500	4	0.02
+explain
+update lineitem set l_tax = (l_tax*100+1)/100 where l_orderkey in (select o_orderkey from orders
+where o_custkey in
+(select c_custkey from customer
+where c_nationkey in
+(select n_nationkey from nation
+where n_name='PERU'))
+and
+o_orderDATE between '1992-06-30' and '1992-12-31')
+and
+(l_partkey, l_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 1000 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	2	
+1	PRIMARY	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey	5	dbt3_s001.supplier.s_suppkey	100	Using where
+1	PRIMARY	orders	eq_ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	PRIMARY|i_o_orderdate	4|4	dbt3_s001.lineitem.l_orderkey	1 (7%)	Using where; Using rowid filter
+1	PRIMARY	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
+1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
+update lineitem set l_tax = (l_tax*100+1)/100 where l_orderkey in (select o_orderkey from orders
+where o_custkey in
+(select c_custkey from customer
+where c_nationkey in
+(select n_nationkey from nation
+where n_name='PERU'))
+and
+o_orderDATE between '1992-06-30' and '1992-12-31')
+and
+(l_partkey, l_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 1000 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders
+where o_custkey in
+(select c_custkey from customer
+where c_nationkey in
+(select n_nationkey from nation
+where n_name='PERU'))
+and
+o_orderDATE between '1992-06-30' and '1992-12-31')
+and
+(l_partkey, l_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 1000 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+l_orderkey	l_linenumber	l_tax
+4996	1	0.02
+933	1	0.05
+2500	2	0.03
+2500	4	0.03
+update lineitem set l_tax = (l_tax*100-1)/100 where l_orderkey in (select o_orderkey from orders
+where o_custkey in
+(select c_custkey from customer
+where c_nationkey in
+(select n_nationkey from nation
+where n_name='PERU'))
+and
+o_orderDATE between '1992-06-30' and '1992-12-31')
+and
+(l_partkey, l_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 1000 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders
+where o_custkey in
+(select c_custkey from customer
+where c_nationkey in
+(select n_nationkey from nation
+where n_name='PERU'))
+and
+o_orderDATE between '1992-06-30' and '1992-12-31')
+and
+(l_partkey, l_suppkey) in
+(select p_partkey, s_suppkey from part, supplier
+where p_retailprice between 901 and 1000 and
+s_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+l_orderkey	l_linenumber	l_tax
+4996	1	0.01
+933	1	0.04
+2500	2	0.02
+2500	4	0.02
+# FirstMatch
+# ==========
+explain
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	ALL	PRIMARY,i_c_nationkey	NULL	NULL	NULL	150	Using where
+1	PRIMARY	nation	eq_ref|filter	PRIMARY,i_n_regionkey	PRIMARY|i_n_regionkey	4|5	dbt3_s001.customer.c_nationkey	1 (40%)	Using where; Using rowid filter
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (6%)	Using where; FirstMatch(nation); Using rowid filter
+explain format=json
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "ALL",
+          "possible_keys": ["PRIMARY", "i_c_nationkey"],
+          "rows": 150,
+          "filtered": 100,
+          "attached_condition": "customer.c_nationkey is not null"
+        }
+      },
+      {
+        "table": {
+          "table_name": "nation",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY", "i_n_regionkey"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["n_nationkey"],
+          "ref": ["dbt3_s001.customer.c_nationkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_n_regionkey",
+              "used_key_parts": ["n_regionkey"]
+            },
+            "rows": 10,
+            "selectivity_pct": 40
+          },
+          "rows": 1,
+          "filtered": 40,
+          "attached_condition": "nation.n_regionkey in (1,2)"
+        }
+      },
+      {
+        "table": {
+          "table_name": "orders",
+          "access_type": "ref",
+          "possible_keys": ["i_o_orderdate", "i_o_custkey"],
+          "key": "i_o_custkey",
+          "key_length": "5",
+          "used_key_parts": ["o_custkey"],
+          "ref": ["dbt3_s001.customer.c_custkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_o_orderdate",
+              "used_key_parts": ["o_orderDATE"]
+            },
+            "rows": 89,
+            "selectivity_pct": 5.933333333
+          },
+          "rows": 11,
+          "filtered": 5.933333397,
+          "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-03-08'",
+          "first_match": "nation"
+        }
+      }
+    ]
+  }
+}
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000082	9468.34
+Customer#000000091	4643.14
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000115	7508.92
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+explain
+update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	ALL	PRIMARY,i_c_nationkey	NULL	NULL	NULL	150	Using where
+1	PRIMARY	nation	eq_ref|filter	PRIMARY,i_n_regionkey	PRIMARY|i_n_regionkey	4|5	dbt3_s001.customer.c_nationkey	1 (40%)	Using where; Using rowid filter
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (6%)	Using where; FirstMatch(nation); Using rowid filter
+explain format=json
+update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "ALL",
+          "possible_keys": ["PRIMARY", "i_c_nationkey"],
+          "rows": 150,
+          "filtered": 100,
+          "attached_condition": "customer.c_nationkey is not null"
+        }
+      },
+      {
+        "table": {
+          "table_name": "nation",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY", "i_n_regionkey"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["n_nationkey"],
+          "ref": ["dbt3_s001.customer.c_nationkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_n_regionkey",
+              "used_key_parts": ["n_regionkey"]
+            },
+            "rows": 10,
+            "selectivity_pct": 40
+          },
+          "rows": 1,
+          "filtered": 40,
+          "attached_condition": "nation.n_regionkey in (1,2)"
+        }
+      },
+      {
+        "table": {
+          "table_name": "orders",
+          "access_type": "ref",
+          "possible_keys": ["i_o_orderdate", "i_o_custkey"],
+          "key": "i_o_custkey",
+          "key_length": "5",
+          "used_key_parts": ["o_custkey"],
+          "ref": ["dbt3_s001.customer.c_custkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_o_orderdate",
+              "used_key_parts": ["o_orderDATE"]
+            },
+            "rows": 89,
+            "selectivity_pct": 5.933333333
+          },
+          "rows": 11,
+          "filtered": 5.933333397,
+          "attached_condition": "orders.o_orderDATE between '1992-10-09' and '1993-03-08'",
+          "first_match": "nation"
+        }
+      }
+    ]
+  }
+}
+update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9571.95
+Customer#000000019	8924.71
+Customer#000000022	601.98
+Customer#000000025	7143.7
+Customer#000000028	1017.18
+Customer#000000037	-907.75
+Customer#000000040	1345.3
+Customer#000000047	284.58
+Customer#000000059	3468.6
+Customer#000000061	1546.24
+Customer#000000064	-636.64
+Customer#000000067	8176.59
+Customer#000000082	9478.34
+Customer#000000091	4653.14
+Customer#000000094	5510.11
+Customer#000000097	2174.48
+Customer#000000101	7480.96
+Customer#000000103	2767.45
+Customer#000000106	3298.42
+Customer#000000115	7518.92
+Customer#000000121	6438.32
+Customer#000000122	7875.46
+Customer#000000127	9290.71
+Customer#000000130	5083.58
+Customer#000000133	2324.67
+Customer#000000139	7907.78
+update customer set c_acctbal = c_acctbal-10 where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000082	9468.34
+Customer#000000091	4643.14
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000115	7508.92
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+explain
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1993-01-08");
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (14%)	Using where; FirstMatch(customer); Using rowid filter
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1993-01-08");
+c_name	c_acctbal
+Customer#000000008	6819.74
+Customer#000000035	1228.24
+Customer#000000061	1536.24
+Customer#000000097	2164.48
+Customer#000000121	6428.32
+Customer#000000133	2314.67
+explain
+update customer set c_acctbal = c_acctbal+20 where c_nationkey in (select n_nationkey from nation where n_name='PERU')
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1993-01-08");
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	nation	ref	PRIMARY,i_n_name	i_n_name	26	const	1	Using index condition
+1	PRIMARY	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	11	
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (14%)	Using where; FirstMatch(customer); Using rowid filter
+update customer set c_acctbal = c_acctbal+20 where c_nationkey in (select n_nationkey from nation where n_name='PERU')
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1993-01-08");
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1993-01-08");
+c_name	c_acctbal
+Customer#000000008	6839.74
+Customer#000000035	1248.24
+Customer#000000061	1556.24
+Customer#000000097	2184.48
+Customer#000000121	6448.32
+Customer#000000133	2334.67
+update customer set c_acctbal = c_acctbal-20 where c_nationkey in (select n_nationkey from nation where n_name='PERU')
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1993-01-08");
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between "1992-01-09" and "1993-01-08");
+c_name	c_acctbal
+Customer#000000008	6819.74
+Customer#000000035	1228.24
+Customer#000000061	1536.24
+Customer#000000097	2164.48
+Customer#000000121	6428.32
+Customer#000000133	2314.67
+# Materialization
+# ===============
+explain
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	28	
+1	PRIMARY	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.orders.o_custkey	1	
+2	MATERIALIZED	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	28	Using index condition; Using where
+explain format=json
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "<subquery2>",
+          "access_type": "ALL",
+          "possible_keys": ["distinct_key"],
+          "rows": 28,
+          "filtered": 100,
+          "materialized": {
+            "unique": 1,
+            "query_block": {
+              "select_id": 2,
+              "nested_loop": [
+                {
+                  "table": {
+                    "table_name": "orders",
+                    "access_type": "range",
+                    "possible_keys": ["i_o_orderdate", "i_o_custkey"],
+                    "key": "i_o_orderdate",
+                    "key_length": "4",
+                    "used_key_parts": ["o_orderDATE"],
+                    "rows": 28,
+                    "filtered": 100,
+                    "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'",
+                    "attached_condition": "orders.o_custkey is not null"
+                  }
+                }
+              ]
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["c_custkey"],
+          "ref": ["dbt3_s001.orders.o_custkey"],
+          "rows": 1,
+          "filtered": 100
+        }
+      }
+    ]
+  }
+}
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000025	7133.7
+Customer#000000013	3857.34
+Customer#000000065	8795.16
+Customer#000000032	3471.53
+Customer#000000023	3332.02
+Customer#000000035	1228.24
+Customer#000000091	4643.14
+Customer#000000016	4681.03
+Customer#000000098	-551.37
+Customer#000000037	-917.75
+Customer#000000136	-842.39
+Customer#000000118	3582.37
+Customer#000000022	591.98
+Customer#000000005	794.47
+Customer#000000109	-716.1
+Customer#000000038	6345.11
+Customer#000000076	5745.33
+Customer#000000056	6530.86
+Customer#000000040	1335.3
+Customer#000000116	8403.99
+Customer#000000115	7508.92
+Customer#000000140	9963.15
+Customer#000000017	6.34
+Customer#000000052	5630.28
+explain
+update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	28	
+1	PRIMARY	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.orders.o_custkey	1	
+2	MATERIALIZED	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	28	Using index condition; Using where
+explain format=json
+update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "<subquery2>",
+          "access_type": "ALL",
+          "possible_keys": ["distinct_key"],
+          "rows": 28,
+          "filtered": 100,
+          "materialized": {
+            "unique": 1,
+            "query_block": {
+              "select_id": 2,
+              "nested_loop": [
+                {
+                  "table": {
+                    "table_name": "orders",
+                    "access_type": "range",
+                    "possible_keys": ["i_o_orderdate", "i_o_custkey"],
+                    "key": "i_o_orderdate",
+                    "key_length": "4",
+                    "used_key_parts": ["o_orderDATE"],
+                    "rows": 28,
+                    "filtered": 100,
+                    "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'",
+                    "attached_condition": "orders.o_custkey is not null"
+                  }
+                }
+              ]
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["c_custkey"],
+          "ref": ["dbt3_s001.orders.o_custkey"],
+          "rows": 1,
+          "filtered": 100
+        }
+      }
+    ]
+  }
+}
+update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000025	7138.7
+Customer#000000013	3862.34
+Customer#000000065	8800.16
+Customer#000000032	3476.53
+Customer#000000023	3337.02
+Customer#000000035	1233.24
+Customer#000000091	4648.14
+Customer#000000016	4686.03
+Customer#000000098	-546.37
+Customer#000000037	-912.75
+Customer#000000136	-837.39
+Customer#000000118	3587.37
+Customer#000000022	596.98
+Customer#000000005	799.47
+Customer#000000109	-711.1
+Customer#000000038	6350.11
+Customer#000000076	5750.33
+Customer#000000056	6535.86
+Customer#000000040	1340.3
+Customer#000000116	8408.99
+Customer#000000115	7513.92
+Customer#000000140	9968.15
+Customer#000000017	11.34
+Customer#000000052	5635.28
+update customer set c_acctbal = c_acctbal-5 where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000025	7133.7
+Customer#000000013	3857.34
+Customer#000000065	8795.16
+Customer#000000032	3471.53
+Customer#000000023	3332.02
+Customer#000000035	1228.24
+Customer#000000091	4643.14
+Customer#000000016	4681.03
+Customer#000000098	-551.37
+Customer#000000037	-917.75
+Customer#000000136	-842.39
+Customer#000000118	3582.37
+Customer#000000022	591.98
+Customer#000000005	794.47
+Customer#000000109	-716.1
+Customer#000000038	6345.11
+Customer#000000076	5745.33
+Customer#000000056	6530.86
+Customer#000000040	1335.3
+Customer#000000116	8403.99
+Customer#000000115	7508.92
+Customer#000000140	9963.15
+Customer#000000017	6.34
+Customer#000000052	5630.28
+explain
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-06-09' and '1993-01-08');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	114	Using index condition; Using where
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-06-09' and '1993-01-08');
+c_name	c_acctbal
+Customer#000000001	711.56
+Customer#000000002	121.65
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
+Customer#000000034	8589.7
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
+Customer#000000053	4113.64
+Customer#000000055	4572.11
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
+Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000095	5327.38
+Customer#000000097	2164.48
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
+Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000148	2135.6
+Customer#000000149	8959.65
+explain
+update customer set c_acctbal = c_acctbal+1 where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-06-09' and '1993-01-08');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	114	Using index condition; Using where
+update customer set c_acctbal = c_acctbal+1 where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-06-09' and '1993-01-08');
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-06-09' and '1993-01-08');
+c_name	c_acctbal
+Customer#000000001	712.56
+Customer#000000002	122.65
+Customer#000000007	9562.95
+Customer#000000008	6820.74
+Customer#000000010	2754.54
+Customer#000000011	-271.6
+Customer#000000016	4682.03
+Customer#000000017	7.34
+Customer#000000019	8915.71
+Customer#000000022	592.98
+Customer#000000023	3333.02
+Customer#000000025	7134.7
+Customer#000000028	1008.18
+Customer#000000029	7619.27
+Customer#000000031	5237.89
+Customer#000000034	8590.7
+Customer#000000037	-916.75
+Customer#000000040	1336.3
+Customer#000000043	9905.28
+Customer#000000044	7316.94
+Customer#000000046	5745.59
+Customer#000000047	275.58
+Customer#000000049	4574.94
+Customer#000000053	4114.64
+Customer#000000055	4573.11
+Customer#000000061	1537.24
+Customer#000000064	-645.64
+Customer#000000067	8167.59
+Customer#000000070	4868.52
+Customer#000000071	-610.19
+Customer#000000073	4289.5
+Customer#000000074	2765.43
+Customer#000000076	5746.33
+Customer#000000079	5122.28
+Customer#000000080	7384.53
+Customer#000000082	9469.34
+Customer#000000083	6464.51
+Customer#000000085	3387.64
+Customer#000000086	3307.32
+Customer#000000088	8032.44
+Customer#000000091	4644.14
+Customer#000000092	1183.91
+Customer#000000095	5328.38
+Customer#000000097	2165.48
+Customer#000000100	9890.89
+Customer#000000101	7471.96
+Customer#000000103	2758.45
+Customer#000000104	-587.38
+Customer#000000106	3289.42
+Customer#000000109	-715.1
+Customer#000000110	7463.99
+Customer#000000112	2954.35
+Customer#000000118	3583.37
+Customer#000000121	6429.32
+Customer#000000122	7866.46
+Customer#000000127	9281.71
+Customer#000000130	5074.58
+Customer#000000131	8596.53
+Customer#000000133	2315.67
+Customer#000000134	4609.9
+Customer#000000136	-841.39
+Customer#000000137	7839.3
+Customer#000000139	7898.78
+Customer#000000142	2210.81
+Customer#000000143	2187.5
+Customer#000000148	2136.6
+Customer#000000149	8960.65
+update customer set c_acctbal = c_acctbal-1 where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-06-09' and '1993-01-08');
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-06-09' and '1993-01-08');
+c_name	c_acctbal
+Customer#000000001	711.56
+Customer#000000002	121.65
+Customer#000000007	9561.95
+Customer#000000008	6819.74
+Customer#000000010	2753.54
+Customer#000000011	-272.6
+Customer#000000016	4681.03
+Customer#000000017	6.34
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000023	3332.02
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000029	7618.27
+Customer#000000031	5236.89
+Customer#000000034	8589.7
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000043	9904.28
+Customer#000000044	7315.94
+Customer#000000046	5744.59
+Customer#000000047	274.58
+Customer#000000049	4573.94
+Customer#000000053	4113.64
+Customer#000000055	4572.11
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000070	4867.52
+Customer#000000071	-611.19
+Customer#000000073	4288.5
+Customer#000000074	2764.43
+Customer#000000076	5745.33
+Customer#000000079	5121.28
+Customer#000000080	7383.53
+Customer#000000082	9468.34
+Customer#000000083	6463.51
+Customer#000000085	3386.64
+Customer#000000086	3306.32
+Customer#000000088	8031.44
+Customer#000000091	4643.14
+Customer#000000092	1182.91
+Customer#000000095	5327.38
+Customer#000000097	2164.48
+Customer#000000100	9889.89
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000104	-588.38
+Customer#000000106	3288.42
+Customer#000000109	-716.1
+Customer#000000110	7462.99
+Customer#000000112	2953.35
+Customer#000000118	3582.37
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000131	8595.53
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+Customer#000000136	-842.39
+Customer#000000137	7838.3
+Customer#000000139	7897.78
+Customer#000000142	2209.81
+Customer#000000143	2186.5
+Customer#000000148	2135.6
+Customer#000000149	8959.65
+# Materialization SJM
+# ===================
+explain
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	28	
+1	PRIMARY	customer	eq_ref	PRIMARY	PRIMARY	4	<subquery2>.o_custkey	1	
+2	MATERIALIZED	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	28	Using index condition; Using temporary
+explain format=json
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "<subquery2>",
+          "access_type": "ALL",
+          "possible_keys": ["distinct_key"],
+          "rows": 28,
+          "filtered": 100,
+          "materialized": {
+            "unique": 1,
+            "query_block": {
+              "select_id": 2,
+              "having_condition": "count(orders.o_custkey) > 1",
+              "temporary_table": {
+                "nested_loop": [
+                  {
+                    "table": {
+                      "table_name": "orders",
+                      "access_type": "range",
+                      "possible_keys": ["i_o_orderdate"],
+                      "key": "i_o_orderdate",
+                      "key_length": "4",
+                      "used_key_parts": ["o_orderDATE"],
+                      "rows": 28,
+                      "filtered": 100,
+                      "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'"
+                    }
+                  }
+                ]
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["c_custkey"],
+          "ref": ["<subquery2>.o_custkey"],
+          "rows": 1,
+          "filtered": 100
+        }
+      }
+    ]
+  }
+}
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000118	3582.37
+Customer#000000056	6530.86
+explain
+update customer set c_acctbal = c_acctbal-5 where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	28	
+1	PRIMARY	customer	eq_ref	PRIMARY	PRIMARY	4	<subquery2>.o_custkey	1	
+2	MATERIALIZED	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	28	Using index condition; Using temporary
+explain format=json
+update customer set c_acctbal = c_acctbal-5 where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "<subquery2>",
+          "access_type": "ALL",
+          "possible_keys": ["distinct_key"],
+          "rows": 28,
+          "filtered": 100,
+          "materialized": {
+            "unique": 1,
+            "query_block": {
+              "select_id": 2,
+              "having_condition": "count(orders.o_custkey) > 1",
+              "temporary_table": {
+                "nested_loop": [
+                  {
+                    "table": {
+                      "table_name": "orders",
+                      "access_type": "range",
+                      "possible_keys": ["i_o_orderdate"],
+                      "key": "i_o_orderdate",
+                      "key_length": "4",
+                      "used_key_parts": ["o_orderDATE"],
+                      "rows": 28,
+                      "filtered": 100,
+                      "index_condition": "orders.o_orderDATE between '1992-01-09' and '1992-03-08'"
+                    }
+                  }
+                ]
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "customer",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["c_custkey"],
+          "ref": ["<subquery2>.o_custkey"],
+          "rows": 1,
+          "filtered": 100
+        }
+      }
+    ]
+  }
+}
+update customer set c_acctbal = c_acctbal-5 where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3852.34
+Customer#000000032	3466.53
+Customer#000000037	-922.75
+Customer#000000118	3577.37
+Customer#000000056	6525.86
+update customer set c_acctbal = c_acctbal+5 where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000118	3582.37
+Customer#000000056	6530.86
+explain
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	dbt3_s001.customer.c_custkey	1	
+2	MATERIALIZED	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	242	Using index condition; Using temporary
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000016	4681.03
+Customer#000000037	-917.75
+Customer#000000046	5744.59
+Customer#000000091	4643.14
+Customer#000000103	2757.45
+Customer#000000118	3582.37
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+explain
+update customer set c_acctbal = c_acctbal-1 where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	ALL	PRIMARY	NULL	NULL	NULL	150	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	dbt3_s001.customer.c_custkey	1	
+2	MATERIALIZED	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	242	Using index condition; Using temporary
+update customer set c_acctbal = c_acctbal-1 where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+c_name	c_acctbal
+Customer#000000007	9560.95
+Customer#000000016	4680.03
+Customer#000000037	-918.75
+Customer#000000046	5743.59
+Customer#000000091	4642.14
+Customer#000000103	2756.45
+Customer#000000118	3581.37
+Customer#000000133	2313.67
+Customer#000000134	4607.9
+update customer set c_acctbal = c_acctbal+1 where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000016	4681.03
+Customer#000000037	-917.75
+Customer#000000046	5744.59
+Customer#000000091	4643.14
+Customer#000000103	2757.45
+Customer#000000118	3582.37
+Customer#000000133	2314.67
+Customer#000000134	4608.9
+# Pullout PS
+# ==========
+prepare stmt from "
+update orders set o_totalprice = o_totalprice+? where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+";
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+644	201268.06
+2880	145761.99
+3142	16030.15
+5382	138423.03
+5095	184583.99
+737	12984.85
+1729	12137.76
+5121	150334.57
+set @a1=-20;
+execute stmt using @a1;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+644	201248.06
+2880	145741.99
+3142	16010.15
+5382	138403.03
+5095	184563.99
+737	12964.85
+1729	12117.76
+5121	150314.57
+set @a2=-10;
+execute stmt using @a2;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+644	201238.06
+2880	145731.99
+3142	16000.15
+5382	138393.03
+5095	184553.99
+737	12954.85
+1729	12107.76
+5121	150304.57
+execute stmt using -(@a1+@a2);
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+644	201268.06
+2880	145761.99
+3142	16030.15
+5382	138423.03
+5095	184583.99
+737	12984.85
+1729	12137.76
+5121	150334.57
+deallocate prepare stmt;
+# FirstMatch PS
+# =============
+prepare stmt from "
+update customer set c_acctbal = c_acctbal+? where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+";
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000025	7133.7
+Customer#000000028	1007.18
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000082	9468.34
+Customer#000000091	4643.14
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000115	7508.92
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+set @a1=15;
+execute stmt using @a1;
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9576.95
+Customer#000000019	8929.71
+Customer#000000022	606.98
+Customer#000000025	7148.7
+Customer#000000028	1022.18
+Customer#000000037	-902.75
+Customer#000000040	1350.3
+Customer#000000047	289.58
+Customer#000000059	3473.6
+Customer#000000061	1551.24
+Customer#000000064	-631.64
+Customer#000000067	8181.59
+Customer#000000082	9483.34
+Customer#000000091	4658.14
+Customer#000000094	5515.11
+Customer#000000097	2179.48
+Customer#000000101	7485.96
+Customer#000000103	2772.45
+Customer#000000106	3303.42
+Customer#000000115	7523.92
+Customer#000000121	6443.32
+Customer#000000122	7880.46
+Customer#000000127	9295.71
+Customer#000000130	5088.58
+Customer#000000133	2329.67
+Customer#000000139	7912.78
+set @a2=5;
+execute stmt using @a2;
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9581.95
+Customer#000000019	8934.71
+Customer#000000022	611.98
+Customer#000000025	7153.7
+Customer#000000028	1027.1799999999998
+Customer#000000037	-897.75
+Customer#000000040	1355.3
+Customer#000000047	294.58
+Customer#000000059	3478.6
+Customer#000000061	1556.24
+Customer#000000064	-626.64
+Customer#000000067	8186.59
+Customer#000000082	9488.34
+Customer#000000091	4663.14
+Customer#000000094	5520.11
+Customer#000000097	2184.48
+Customer#000000101	7490.96
+Customer#000000103	2777.45
+Customer#000000106	3308.42
+Customer#000000115	7528.92
+Customer#000000121	6448.32
+Customer#000000122	7885.46
+Customer#000000127	9300.71
+Customer#000000130	5093.58
+Customer#000000133	2334.67
+Customer#000000139	7917.78
+execute stmt using -(@a1+@a2);
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000025	7133.7
+Customer#000000028	1007.1799999999998
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000082	9468.34
+Customer#000000091	4643.14
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000115	7508.92
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+deallocate prepare stmt;
+# Materialization PS
+# ==================
+prepare stmt from "
+update customer set c_acctbal = c_acctbal+? where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+";
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000025	7133.7
+Customer#000000013	3857.34
+Customer#000000065	8795.16
+Customer#000000032	3471.53
+Customer#000000023	3332.02
+Customer#000000035	1228.24
+Customer#000000091	4643.14
+Customer#000000016	4681.03
+Customer#000000098	-551.37
+Customer#000000037	-917.75
+Customer#000000136	-842.39
+Customer#000000118	3582.37
+Customer#000000022	591.98
+Customer#000000005	794.47
+Customer#000000109	-716.1
+Customer#000000038	6345.11
+Customer#000000076	5745.33
+Customer#000000056	6530.86
+Customer#000000040	1335.3
+Customer#000000116	8403.99
+Customer#000000115	7508.92
+Customer#000000140	9963.15
+Customer#000000017	6.34
+Customer#000000052	5630.28
+set @a1=7;
+execute stmt using @a1;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000025	7140.7
+Customer#000000013	3864.34
+Customer#000000065	8802.16
+Customer#000000032	3478.53
+Customer#000000023	3339.02
+Customer#000000035	1235.24
+Customer#000000091	4650.14
+Customer#000000016	4688.03
+Customer#000000098	-544.37
+Customer#000000037	-910.75
+Customer#000000136	-835.39
+Customer#000000118	3589.37
+Customer#000000022	598.98
+Customer#000000005	801.47
+Customer#000000109	-709.1
+Customer#000000038	6352.11
+Customer#000000076	5752.33
+Customer#000000056	6537.86
+Customer#000000040	1342.3
+Customer#000000116	8410.99
+Customer#000000115	7515.92
+Customer#000000140	9970.15
+Customer#000000017	13.34
+Customer#000000052	5637.28
+set @a2=3;
+execute stmt using @a2;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000025	7143.7
+Customer#000000013	3867.34
+Customer#000000065	8805.16
+Customer#000000032	3481.53
+Customer#000000023	3342.02
+Customer#000000035	1238.24
+Customer#000000091	4653.14
+Customer#000000016	4691.03
+Customer#000000098	-541.37
+Customer#000000037	-907.75
+Customer#000000136	-832.39
+Customer#000000118	3592.37
+Customer#000000022	601.98
+Customer#000000005	804.47
+Customer#000000109	-706.1
+Customer#000000038	6355.11
+Customer#000000076	5755.33
+Customer#000000056	6540.86
+Customer#000000040	1345.3
+Customer#000000116	8413.99
+Customer#000000115	7518.92
+Customer#000000140	9973.15
+Customer#000000017	16.34
+Customer#000000052	5640.28
+execute stmt using -(@a1+@a2);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000025	7133.7
+Customer#000000013	3857.34
+Customer#000000065	8795.16
+Customer#000000032	3471.53
+Customer#000000023	3332.02
+Customer#000000035	1228.24
+Customer#000000091	4643.14
+Customer#000000016	4681.03
+Customer#000000098	-551.37
+Customer#000000037	-917.75
+Customer#000000136	-842.39
+Customer#000000118	3582.37
+Customer#000000022	591.98
+Customer#000000005	794.47
+Customer#000000109	-716.1
+Customer#000000038	6345.11
+Customer#000000076	5745.33
+Customer#000000056	6530.86
+Customer#000000040	1335.3
+Customer#000000116	8403.99
+Customer#000000115	7508.92
+Customer#000000140	9963.15
+Customer#000000017	6.34
+Customer#000000052	5630.28
+deallocate prepare stmt;
+# Materialization SJM PS
+# ======================
+prepare stmt from "
+update customer set c_acctbal = c_acctbal+? where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+";
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000118	3582.37
+Customer#000000056	6530.86
+set @a1=-2;
+execute stmt using @a1;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3855.34
+Customer#000000032	3469.53
+Customer#000000037	-919.75
+Customer#000000118	3580.37
+Customer#000000056	6528.86
+set @a2=-1;
+execute stmt using @a2;
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3854.34
+Customer#000000032	3468.53
+Customer#000000037	-920.75
+Customer#000000118	3579.37
+Customer#000000056	6527.86
+execute stmt using -(@a1+@a2);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000118	3582.37
+Customer#000000056	6530.86
+deallocate prepare stmt;
+# Pullout SP
+# ==========
+create procedure p(d int)
+update orders set o_totalprice = o_totalprice+d where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+644	201268.06
+2880	145761.99
+3142	16030.15
+5382	138423.03
+5095	184583.99
+737	12984.85
+1729	12137.76
+5121	150334.57
+call p(-10);
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+644	201258.06
+2880	145751.99
+3142	16020.15
+5382	138413.03
+5095	184573.99
+737	12974.85
+1729	12127.76
+5121	150324.57
+call p(-20);
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+644	201238.06
+2880	145731.99
+3142	16000.15
+5382	138393.03
+5095	184553.99
+737	12954.85
+1729	12107.76
+5121	150304.57
+call p(10+20);
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (select n_nationkey from nation
+where n_name='PERU'));
+o_orderkey	o_totalprice
+644	201268.06
+2880	145761.99
+3142	16030.15
+5382	138423.03
+5095	184583.99
+737	12984.85
+1729	12137.76
+5121	150334.57
+drop procedure p;
+# FirstMatch SP
+# =============
+create procedure p(d int)
+update customer set c_acctbal = c_acctbal+d where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000025	7133.7
+Customer#000000028	1007.1799999999998
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000082	9468.34
+Customer#000000091	4643.14
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000115	7508.92
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+call p(5);
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9566.95
+Customer#000000019	8919.71
+Customer#000000022	596.98
+Customer#000000025	7138.7
+Customer#000000028	1012.1799999999998
+Customer#000000037	-912.75
+Customer#000000040	1340.3
+Customer#000000047	279.58
+Customer#000000059	3463.6
+Customer#000000061	1541.24
+Customer#000000064	-641.64
+Customer#000000067	8171.59
+Customer#000000082	9473.34
+Customer#000000091	4648.14
+Customer#000000094	5505.11
+Customer#000000097	2169.48
+Customer#000000101	7475.96
+Customer#000000103	2762.45
+Customer#000000106	3293.42
+Customer#000000115	7513.92
+Customer#000000121	6433.32
+Customer#000000122	7870.46
+Customer#000000127	9285.71
+Customer#000000130	5078.58
+Customer#000000133	2319.67
+Customer#000000139	7902.78
+call p(15);
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9581.95
+Customer#000000019	8934.71
+Customer#000000022	611.98
+Customer#000000025	7153.7
+Customer#000000028	1027.1799999999998
+Customer#000000037	-897.75
+Customer#000000040	1355.3
+Customer#000000047	294.58
+Customer#000000059	3478.6
+Customer#000000061	1556.24
+Customer#000000064	-626.64
+Customer#000000067	8186.59
+Customer#000000082	9488.34
+Customer#000000091	4663.14
+Customer#000000094	5520.11
+Customer#000000097	2184.48
+Customer#000000101	7490.96
+Customer#000000103	2777.45
+Customer#000000106	3308.42
+Customer#000000115	7528.92
+Customer#000000121	6448.32
+Customer#000000122	7885.46
+Customer#000000127	9300.71
+Customer#000000130	5093.58
+Customer#000000133	2334.67
+Customer#000000139	7917.78
+call p(-(5+15));
+select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation
+where n_regionkey in (1,2))
+and
+c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-10-09' and '1993-03-08');
+c_name	c_acctbal
+Customer#000000007	9561.95
+Customer#000000019	8914.71
+Customer#000000022	591.98
+Customer#000000025	7133.7
+Customer#000000028	1007.1799999999998
+Customer#000000037	-917.75
+Customer#000000040	1335.3
+Customer#000000047	274.58
+Customer#000000059	3458.6
+Customer#000000061	1536.24
+Customer#000000064	-646.64
+Customer#000000067	8166.59
+Customer#000000082	9468.34
+Customer#000000091	4643.14
+Customer#000000094	5500.11
+Customer#000000097	2164.48
+Customer#000000101	7470.96
+Customer#000000103	2757.45
+Customer#000000106	3288.42
+Customer#000000115	7508.92
+Customer#000000121	6428.32
+Customer#000000122	7865.46
+Customer#000000127	9280.71
+Customer#000000130	5073.58
+Customer#000000133	2314.67
+Customer#000000139	7897.78
+drop procedure p;
+# Materialization SP
+# ==================
+create procedure p(d int)
+update customer set c_acctbal = c_acctbal+d where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000025	7133.7
+Customer#000000013	3857.34
+Customer#000000065	8795.16
+Customer#000000032	3471.53
+Customer#000000023	3332.02
+Customer#000000035	1228.24
+Customer#000000091	4643.14
+Customer#000000016	4681.03
+Customer#000000098	-551.37
+Customer#000000037	-917.75
+Customer#000000136	-842.39
+Customer#000000118	3582.37
+Customer#000000022	591.98
+Customer#000000005	794.47
+Customer#000000109	-716.1
+Customer#000000038	6345.11
+Customer#000000076	5745.33
+Customer#000000056	6530.86
+Customer#000000040	1335.3
+Customer#000000116	8403.99
+Customer#000000115	7508.92
+Customer#000000140	9963.15
+Customer#000000017	6.34
+Customer#000000052	5630.28
+call p(3);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000025	7136.7
+Customer#000000013	3860.34
+Customer#000000065	8798.16
+Customer#000000032	3474.53
+Customer#000000023	3335.02
+Customer#000000035	1231.24
+Customer#000000091	4646.14
+Customer#000000016	4684.03
+Customer#000000098	-548.37
+Customer#000000037	-914.75
+Customer#000000136	-839.39
+Customer#000000118	3585.37
+Customer#000000022	594.98
+Customer#000000005	797.47
+Customer#000000109	-713.1
+Customer#000000038	6348.11
+Customer#000000076	5748.33
+Customer#000000056	6533.86
+Customer#000000040	1338.3
+Customer#000000116	8406.99
+Customer#000000115	7511.92
+Customer#000000140	9966.15
+Customer#000000017	9.34
+Customer#000000052	5633.28
+call p(7);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000025	7143.7
+Customer#000000013	3867.34
+Customer#000000065	8805.16
+Customer#000000032	3481.53
+Customer#000000023	3342.02
+Customer#000000035	1238.24
+Customer#000000091	4653.14
+Customer#000000016	4691.03
+Customer#000000098	-541.37
+Customer#000000037	-907.75
+Customer#000000136	-832.39
+Customer#000000118	3592.37
+Customer#000000022	601.98
+Customer#000000005	804.47
+Customer#000000109	-706.1
+Customer#000000038	6355.11
+Customer#000000076	5755.33
+Customer#000000056	6540.86
+Customer#000000040	1345.3
+Customer#000000116	8413.99
+Customer#000000115	7518.92
+Customer#000000140	9973.15
+Customer#000000017	16.34
+Customer#000000052	5640.28
+call p(-(3+7));
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08');
+c_name	c_acctbal
+Customer#000000025	7133.7
+Customer#000000013	3857.34
+Customer#000000065	8795.16
+Customer#000000032	3471.53
+Customer#000000023	3332.02
+Customer#000000035	1228.24
+Customer#000000091	4643.14
+Customer#000000016	4681.03
+Customer#000000098	-551.37
+Customer#000000037	-917.75
+Customer#000000136	-842.39
+Customer#000000118	3582.37
+Customer#000000022	591.98
+Customer#000000005	794.47
+Customer#000000109	-716.1
+Customer#000000038	6345.11
+Customer#000000076	5745.33
+Customer#000000056	6530.86
+Customer#000000040	1335.3
+Customer#000000116	8403.99
+Customer#000000115	7508.92
+Customer#000000140	9963.15
+Customer#000000017	6.34
+Customer#000000052	5630.28
+drop procedure p;
+# Materialization SJM SP
+# ======================
+create procedure p(d int)
+update customer set c_acctbal = c_acctbal+d where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000118	3582.37
+Customer#000000056	6530.86
+call p(-1);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3856.34
+Customer#000000032	3470.53
+Customer#000000037	-918.75
+Customer#000000118	3581.37
+Customer#000000056	6529.86
+call p(-2);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3854.34
+Customer#000000032	3468.53
+Customer#000000037	-920.75
+Customer#000000118	3579.37
+Customer#000000056	6527.86
+call p(1+2);
+select c_name, c_acctbal from customer where c_custkey in (select o_custkey from orders
+where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+c_name	c_acctbal
+Customer#000000013	3857.34
+Customer#000000032	3471.53
+Customer#000000037	-917.75
+Customer#000000118	3582.37
+Customer#000000056	6530.86
+drop procedure p;
+# Checking limitations
+# ====================
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (1,2));
+o_orderkey	o_totalprice
+1221	117397.16
+324	26868.85
+1856	189361.42
+1344	43809.37
+1925	146382.71
+3139	40975.96
+4903	34363.63
+5607	24660.06
+# Should not use semi-join conversion because has ORDER BY ... LIMIT
+explain
+update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (1,2))
+order by o_totalprice limit 500;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	orders	range	i_o_orderdate	i_o_orderdate	4	NULL	108	Using where; Using filesort
+2	DEPENDENT SUBQUERY	customer	unique_subquery|filter	PRIMARY,i_c_nationkey	PRIMARY|i_c_nationkey	4|5	func	1 (10%)	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))
+order by o_totalprice limit 500;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (1,2));
+o_orderkey	o_totalprice
+1221	117347.16
+324	26818.85
+1856	189311.42
+1344	43759.37
+1925	146332.71
+3139	40925.96
+4903	34313.63
+5607	24610.06
+update orders set o_totalprice = o_totalprice+50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (1,2))
+order by o_totalprice limit 500;
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (1,2));
+o_orderkey	o_totalprice
+1221	117397.16
+324	26868.85
+1856	189361.42
+1344	43809.37
+1925	146382.71
+3139	40975.96
+4903	34363.63
+5607	24660.06
+# Should use semi-join converion
+explain
+update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (1,2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	customer	range	PRIMARY,i_c_nationkey	i_c_nationkey	5	NULL	15	Using index condition
+1	PRIMARY	orders	ref|filter	i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	11 (7%)	Using where; Using rowid filter
+update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (1,2));
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (1,2));
+o_orderkey	o_totalprice
+1221	117347.16
+324	26818.85
+1856	189311.42
+1344	43759.37
+1925	146332.71
+3139	40925.96
+4903	34313.63
+5607	24610.06
+update orders set o_totalprice = o_totalprice+50 where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (1,2));
+select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and
+o_custkey in (select c_custkey from customer
+where c_nationkey in (1,2));
+o_orderkey	o_totalprice
+1221	117397.16
+324	26868.85
+1856	189361.42
+1344	43809.37
+1925	146382.71
+3139	40975.96
+4903	34363.63
+5607	24660.06
+DROP DATABASE dbt3_s001;
diff --git a/mysql-test/main/update_single_to_multi.test b/mysql-test/main/update_single_to_multi.test
new file mode 100644
index 00000000000..0fe56861992
--- /dev/null
+++ b/mysql-test/main/update_single_to_multi.test
@@ -0,0 +1,549 @@
+--disable_warnings
+DROP DATABASE IF EXISTS dbt3_s001;
+--enable_warnings
+
+CREATE DATABASE dbt3_s001;
+
+use dbt3_s001;
+
+--disable_query_log
+--disable_result_log
+--disable_warnings
+--source include/dbt3_s001.inc
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+create index i_n_name on nation(n_name);
+analyze table nation;
+
+
+--echo # Pullout
+--echo # =======
+
+let $c1=
+        o_orderDATE between '1992-01-01' and '1992-06-30' and
+        o_custkey in (select c_custkey from customer
+                        where c_nationkey in (select n_nationkey from nation
+                                                where n_name='PERU'));
+
+eval
+explain
+select o_orderkey, o_totalprice from orders where $c1;
+eval
+explain format=json
+select o_orderkey, o_totalprice from orders where $c1;
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+
+eval
+explain
+update orders set o_totalprice = o_totalprice-50 where $c1;
+eval
+explain format=json
+update orders set o_totalprice = o_totalprice-50 where $c1;
+eval
+update orders set o_totalprice = o_totalprice-50 where $c1;
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+
+eval
+update orders set o_totalprice= o_totalprice+50 where $c1;
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+
+
+let $c2=
+       (ps_partkey, ps_suppkey) in
+         (select p_partkey, s_suppkey from part, supplier
+            where p_retailprice between 901 and 910 and
+                  s_nationkey in (select n_nationkey from nation
+                                    where n_name='PERU'));
+
+eval
+explain
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2;
+eval
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2;
+
+eval
+explain
+update partsupp set ps_supplycost = ps_supplycost+2 where $c2;
+eval
+update partsupp set ps_supplycost = ps_supplycost+2 where $c2;
+eval
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2;
+
+eval
+update partsupp set ps_supplycost = ps_supplycost-2 where $c2;
+eval
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2;
+
+
+let $c3=
+        ps_partkey in (select p_partkey from part
+                         where p_retailprice between 901 and 910) and
+        ps_suppkey in (select s_suppkey from supplier
+                         where s_nationkey in (select n_nationkey from nation
+                                                 where n_name='PERU'));
+eval
+explain
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3;
+eval
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3;
+
+eval
+explain
+update partsupp set ps_supplycost = ps_supplycost+10 where $c3;
+eval
+update partsupp set ps_supplycost = ps_supplycost+10 where $c3;
+eval
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3;
+
+eval
+update partsupp set ps_supplycost = ps_supplycost-10 where $c3;
+eval
+select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3;
+
+
+let $c4=
+        l_orderkey in (select o_orderkey from orders
+                         where o_custkey in
+                               (select c_custkey from customer
+                                  where c_nationkey in
+                                        (select n_nationkey from nation
+                                           where n_name='PERU'))
+                               and
+                              o_orderDATE between '1992-06-30' and '1992-12-31')
+        and
+        (l_partkey, l_suppkey) in
+        (select p_partkey, s_suppkey from part, supplier
+           where p_retailprice between 901 and 1000 and
+                 s_nationkey in (select n_nationkey from nation
+                                   where n_name='PERU'));
+
+eval
+explain
+select l_orderkey, l_linenumber, l_tax from lineitem where $c4;
+eval
+select l_orderkey, l_linenumber, l_tax from lineitem where $c4;
+
+eval
+explain
+update lineitem set l_tax = (l_tax*100+1)/100 where $c4;
+eval
+update lineitem set l_tax = (l_tax*100+1)/100 where $c4;
+eval
+select l_orderkey, l_linenumber, l_tax from lineitem where $c4;
+
+eval
+update lineitem set l_tax = (l_tax*100-1)/100 where $c4;
+eval
+select l_orderkey, l_linenumber, l_tax from lineitem where $c4;
+
+
+--echo # FirstMatch
+--echo # ==========
+
+let $c5=
+        c_nationkey in (select n_nationkey from nation
+                          where n_regionkey in (1,2))
+        and
+        c_custkey in (select o_custkey from orders
+                        where o_orderDATE between '1992-10-09' and '1993-03-08');
+
+eval
+explain
+select c_name, c_acctbal from customer where $c5;
+eval
+explain format=json
+select c_name, c_acctbal from customer where $c5;
+eval
+select c_name, c_acctbal from customer where $c5;
+
+eval
+explain
+update customer set c_acctbal = c_acctbal+10 where $c5;
+eval
+explain format=json
+update customer set c_acctbal = c_acctbal+10 where $c5;
+eval
+update customer set c_acctbal = c_acctbal+10 where $c5;
+eval
+select c_name, c_acctbal from customer where $c5;
+
+eval
+update customer set c_acctbal = c_acctbal-10 where $c5;
+eval
+select c_name, c_acctbal from customer where $c5;
+
+
+let $c6=
+        c_nationkey in (select n_nationkey from nation where n_name='PERU')
+        and
+        c_custkey in (select o_custkey from orders
+                      where o_orderDATE between "1992-01-09" and "1993-01-08");
+
+eval
+explain
+select c_name, c_acctbal from customer where $c6;
+eval
+select c_name, c_acctbal from customer where $c6;
+
+eval
+explain
+update customer set c_acctbal = c_acctbal+20 where $c6;
+eval
+update customer set c_acctbal = c_acctbal+20 where $c6;
+eval
+select c_name, c_acctbal from customer where $c6;
+
+eval
+update customer set c_acctbal = c_acctbal-20 where $c6;
+eval
+select c_name, c_acctbal from customer where $c6;
+
+
+--echo # Materialization
+--echo # ===============
+
+let $c7=
+        c_custkey in (select o_custkey from orders
+                      where o_orderDATE between '1992-01-09' and '1992-03-08');
+
+eval
+explain
+select c_name, c_acctbal from customer where $c7;
+eval
+explain format=json
+select c_name, c_acctbal from customer where $c7;
+eval
+select c_name, c_acctbal from customer where $c7;
+
+eval
+explain
+update customer set c_acctbal = c_acctbal+5 where $c7;
+eval
+explain format=json
+update customer set c_acctbal = c_acctbal+5 where $c7;
+eval
+update customer set c_acctbal = c_acctbal+5 where $c7;
+eval
+select c_name, c_acctbal from customer where $c7;
+
+eval
+update customer set c_acctbal = c_acctbal-5 where $c7;
+eval
+select c_name, c_acctbal from customer where $c7;
+
+
+let $c8=
+        c_custkey in (select o_custkey from orders
+                      where o_orderDATE between '1992-06-09' and '1993-01-08');
+
+eval
+explain
+select c_name, c_acctbal from customer where $c8;
+eval
+select c_name, c_acctbal from customer where $c8;
+
+eval
+explain
+update customer set c_acctbal = c_acctbal+1 where $c8;
+eval
+update customer set c_acctbal = c_acctbal+1 where $c8;
+eval
+select c_name, c_acctbal from customer where $c8;
+
+eval
+update customer set c_acctbal = c_acctbal-1 where $c8;
+eval
+select c_name, c_acctbal from customer where $c8;
+
+
+--echo # Materialization SJM
+--echo # ===================
+
+let $c9=
+        c_custkey in (select o_custkey from orders
+                        where o_orderDATE between '1992-01-09' and '1992-03-08'
+                      group by o_custkey having count(o_custkey) > 1);
+
+eval
+explain
+select c_name, c_acctbal from customer where $c9;
+eval
+explain format=json
+select c_name, c_acctbal from customer where $c9;
+eval
+select c_name, c_acctbal from customer where $c9;
+
+eval
+explain
+update customer set c_acctbal = c_acctbal-5 where $c9;
+eval
+explain format=json
+update customer set c_acctbal = c_acctbal-5 where $c9;
+eval
+update customer set c_acctbal = c_acctbal-5 where $c9;
+eval
+select c_name, c_acctbal from customer where $c9;
+
+eval
+update customer set c_acctbal = c_acctbal+5 where $c9;
+eval
+select c_name, c_acctbal from customer where $c9;
+
+
+let $c10=
+        c_custkey in (select o_custkey from orders
+                        where o_orderDATE between '1992-01-09' and '1993-03-08'
+                      group by o_custkey having count(o_custkey) > 5);
+
+eval
+explain
+select c_name, c_acctbal from customer where $c10;
+eval
+select c_name, c_acctbal from customer where $c10;
+
+eval
+explain
+update customer set c_acctbal = c_acctbal-1 where $c10;
+eval
+update customer set c_acctbal = c_acctbal-1 where $c10;
+eval
+select c_name, c_acctbal from customer where $c10;
+
+eval
+update customer set c_acctbal = c_acctbal+1 where $c10;
+eval
+select c_name, c_acctbal from customer where $c10;
+
+
+--echo # Pullout PS
+--echo # ==========
+
+eval
+prepare stmt from "
+update orders set o_totalprice = o_totalprice+? where $c1;
+";
+
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+set @a1=-20;
+execute stmt using @a1;
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+set @a2=-10;
+execute stmt using @a2;
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+execute stmt using -(@a1+@a2);
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+
+deallocate prepare stmt;
+
+
+--echo # FirstMatch PS
+--echo # =============
+
+eval
+prepare stmt from "
+update customer set c_acctbal = c_acctbal+? where $c5;
+";
+
+eval
+select c_name, c_acctbal from customer where $c5;
+set @a1=15;
+execute stmt using @a1;
+eval
+select c_name, c_acctbal from customer where $c5;
+set @a2=5;
+execute stmt using @a2;
+eval
+select c_name, c_acctbal from customer where $c5;
+execute stmt using -(@a1+@a2);
+eval
+select c_name, c_acctbal from customer where $c5;
+
+deallocate prepare stmt;
+
+
+--echo # Materialization PS
+--echo # ==================
+
+eval
+prepare stmt from "
+update customer set c_acctbal = c_acctbal+? where $c7;
+";
+
+eval
+select c_name, c_acctbal from customer where $c7;
+set @a1=7;
+execute stmt using @a1;
+eval
+select c_name, c_acctbal from customer where $c7;
+set @a2=3;
+execute stmt using @a2;
+eval
+select c_name, c_acctbal from customer where $c7;
+execute stmt using -(@a1+@a2);
+eval
+select c_name, c_acctbal from customer where $c7;
+
+deallocate prepare stmt;
+
+
+--echo # Materialization SJM PS
+--echo # ======================
+
+eval
+prepare stmt from "
+update customer set c_acctbal = c_acctbal+? where $c9;
+";
+
+eval
+select c_name, c_acctbal from customer where $c9;
+set @a1=-2;
+execute stmt using @a1;
+eval
+select c_name, c_acctbal from customer where $c9;
+set @a2=-1;
+execute stmt using @a2;
+eval
+select c_name, c_acctbal from customer where $c9;
+execute stmt using -(@a1+@a2);
+eval
+select c_name, c_acctbal from customer where $c9;
+
+deallocate prepare stmt;
+
+
+--echo # Pullout SP
+--echo # ==========
+
+eval
+create procedure p(d int)
+update orders set o_totalprice = o_totalprice+d where $c1;
+
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+call p(-10);
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+call p(-20);
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+call p(10+20);
+eval
+select o_orderkey, o_totalprice from orders where $c1;
+
+drop procedure p;
+
+
+--echo # FirstMatch SP
+--echo # =============
+
+eval
+create procedure p(d int)
+update customer set c_acctbal = c_acctbal+d where $c5;
+
+eval
+select c_name, c_acctbal from customer where $c5;
+call p(5);
+eval
+select c_name, c_acctbal from customer where $c5;
+call p(15);
+eval
+select c_name, c_acctbal from customer where $c5;
+call p(-(5+15));
+eval
+select c_name, c_acctbal from customer where $c5;
+
+drop procedure p;
+
+
+--echo # Materialization SP
+--echo # ==================
+
+eval
+create procedure p(d int)
+update customer set c_acctbal = c_acctbal+d where $c7;
+
+eval
+select c_name, c_acctbal from customer where $c7;
+call p(3);
+eval
+select c_name, c_acctbal from customer where $c7;
+call p(7);
+eval
+select c_name, c_acctbal from customer where $c7;
+call p(-(3+7));
+eval
+select c_name, c_acctbal from customer where $c7;
+
+drop procedure p;
+
+
+--echo # Materialization SJM SP
+--echo # ======================
+
+eval
+create procedure p(d int)
+update customer set c_acctbal = c_acctbal+d where $c9;
+
+eval
+select c_name, c_acctbal from customer where $c9;
+call p(-1);
+eval
+select c_name, c_acctbal from customer where $c9;
+call p(-2);
+eval
+select c_name, c_acctbal from customer where $c9;
+call p(1+2);
+eval
+select c_name, c_acctbal from customer where $c9;
+
+drop procedure p;
+
+--echo # Checking limitations
+--echo # ====================
+
+let $c11=
+        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));
+
+eval
+select o_orderkey, o_totalprice from orders where $c11;
+--echo # Should not use semi-join conversion because has ORDER BY ... LIMIT
+eval
+explain
+update orders set o_totalprice = o_totalprice-50 where $c11
+order by o_totalprice limit 500;
+eval
+update orders set o_totalprice = o_totalprice-50 where $c11
+order by o_totalprice limit 500;
+eval
+select o_orderkey, o_totalprice from orders where $c11;
+eval
+update orders set o_totalprice = o_totalprice+50 where $c11
+order by o_totalprice limit 500;
+eval
+select o_orderkey, o_totalprice from orders where $c11;
+
+--echo # Should use semi-join converion
+eval
+explain
+update orders set o_totalprice = o_totalprice-50 where $c11;
+eval
+update orders set o_totalprice = o_totalprice-50 where $c11;
+eval
+select o_orderkey, o_totalprice from orders where $c11;
+eval
+update orders set o_totalprice = o_totalprice+50 where $c11;
+eval
+select o_orderkey, o_totalprice from orders where $c11;
+
+DROP DATABASE dbt3_s001;
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index fa338f0e60c..cd88e7856a2 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -533,6 +533,31 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs,
 }
 
 
+/**
+  @brief Check whether an IN subquery must be excluded from conversion to SJ
+
+  @param thd  global context the processed statement
+  @returns true if the IN subquery must be excluded from conversion to SJ
+
+  @note
+  Currently a top level IN subquery of an update/delete statement
+  is not converted to SJ if the statement contains ORDER BY ... LIMIT
+  or contains RETURNING.  
+*/
+
+bool SELECT_LEX::is_sj_conversion_prohibited(THD *thd)
+{
+  DBUG_ASSERT(master_unit()->item->substype() == Item_subselect::IN_SUBS);
+  SELECT_LEX *outer_sl= outer_select();
+  return !outer_sl->outer_select() &&
+         (thd->lex->sql_command == SQLCOM_UPDATE ||
+          thd->lex->sql_command == SQLCOM_DELETE) &&
+	 ((outer_sl->order_list.elements &&
+           outer_sl->get_limit() != HA_POS_ERROR) ||
+          (thd->lex->sql_command == SQLCOM_DELETE &&
+           thd->lex->has_returning()));
+}
+
 /*
   Check if we need JOIN::prepare()-phase subquery rewrites and if yes, do them
 
@@ -675,9 +700,9 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
         3. Subquery does not have GROUP BY or ORDER BY
         4. Subquery does not use aggregate functions or HAVING
         5. Subquery predicate is at the AND-top-level of ON/WHERE clause
-        6. We are not in a subquery of a single table UPDATE/DELETE that 
-             doesn't have a JOIN (TODO: We should handle this at some
-             point by switching to multi-table UPDATE/DELETE)
+        6. We are not in a top level subquery of a single-table UPDATE or
+	   DELETE with ORDER BY ... LIMIT and we are not in a top level
+           subquery of a single-table DELETE with returning
         7. We're not in a table-less subquery like "SELECT 1"
         8. No execution method was already chosen (by a prepared statement)
         9. Parent select is not a table-less select
@@ -692,9 +717,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
         !select_lex->group_list.elements && !join->order &&           // 3
         !join->having && !select_lex->with_sum_func &&                // 4
         in_subs->emb_on_expr_nest &&                                  // 5
-        select_lex->outer_select()->join &&                           // 6
-        (!thd->lex->m_sql_cmd ||
-	 thd->lex->m_sql_cmd->sql_command_code() == SQLCOM_UPDATE_MULTI) &&
+        !select_lex->is_sj_conversion_prohibited(thd) &&              // 6
         parent_unit->first_select()->leaf_tables.elements &&          // 7
         !in_subs->has_strategy() &&                                   // 8
         select_lex->outer_select()->table_list.first &&               // 9
@@ -754,7 +777,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
       */
       if (in_subs && !in_subs->has_strategy())
       {
-        if (is_materialization_applicable(thd, in_subs, select_lex))
+        if (!select_lex->is_sj_conversion_prohibited(thd) &&
+            is_materialization_applicable(thd, in_subs, select_lex))
         {
           in_subs->add_strategy(SUBS_MATERIALIZATION);
 
@@ -7260,3 +7284,28 @@ bool TABLE_LIST::is_sjm_scan_table()
 {
   return is_active_sjm() && sj_mat_info->is_sj_scan;
 }
+
+
+/**
+  @brief Check whether this subselect can be lifted to the top level
+  @returns true if the test is positive, false otherwise
+*/
+
+bool SELECT_LEX::is_sj_subselect_lifted_to_top()
+{
+  st_select_lex *sl= this;
+  st_select_lex *outer_sl= outer_select();
+  for ( ; outer_sl; sl= outer_sl, outer_sl= outer_sl->outer_select())
+  {
+    List_iterator_fast<Item_in_subselect> it(outer_sl->sj_subselects);
+    Item_in_subselect *in_subs;
+    while ((in_subs= it++))
+    {
+      if (in_subs->unit->first_select() == sl)
+        break;
+    }
+    if (!in_subs)
+      return false;
+  }
+  return true;
+}
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 167df5b07d9..3a2704ffe5f 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -7005,6 +7005,7 @@ public:
   // Methods used by ColumnStore
   uint get_num_of_tables() const { return num_of_tables; }
   TABLE_LIST* get_tables() const { return delete_tables; }
+  void set_delete_tables (TABLE_LIST *tbl) { delete_tables= tbl; }
 public:
   multi_delete(THD *thd_arg, TABLE_LIST *dt, uint num_of_tables);
   ~multi_delete();
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 2ff43316954..ed4b6beab34 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -1591,10 +1591,16 @@ bool Sql_cmd_delete::prepare_inner(THD *thd)
                             select_lex->order_list.first,
                             false, NULL, NULL, NULL,
                             select_lex, &lex->unit)))
-
     {
       goto err;
     }
+
+    if (!multitable &&
+        select_lex->sj_subselects.elements)
+      multitable= true;
+
+    if (!multitable)
+      ((multi_delete *)result)->set_delete_tables(0);
   }
 
   if (multitable)
@@ -1638,7 +1644,8 @@ bool Sql_cmd_delete::prepare_inner(THD *thd)
       {
         TABLE_LIST *duplicate;
         if ((duplicate= unique_table(thd, target_tbl->correspondent_table,
-                                     lex->query_tables, 0)))
+                                     lex->query_tables, 0)) &&
+             !duplicate->select_lex->is_sj_subselect_lifted_to_top())
         {
           update_non_unique_table_error(target_tbl->correspondent_table,
                                         "DELETE", duplicate);
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index e733b4fc53f..d1f0274136d 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1642,6 +1642,8 @@ public:
   void lex_start(LEX *plex);
   bool is_unit_nest() { return (nest_flags & UNIT_NEST_FL); }
   void mark_as_unit_nest() { nest_flags= UNIT_NEST_FL; }
+  bool is_sj_conversion_prohibited(THD *thd);
+  bool is_sj_subselect_lifted_to_top();
 };
 typedef class st_select_lex SELECT_LEX;
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index bf49d69023e..a7e5bc77be4 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -5834,8 +5834,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
           s->needed_reg=select->needed_reg;
           select->quick=0;
           impossible_range= records == 0 && s->table->reginfo.impossible_range;
-          if (join->thd->lex->sql_command == SQLCOM_SELECT &&
-              optimizer_flag(join->thd, OPTIMIZER_SWITCH_USE_ROWID_FILTER))
+          if (optimizer_flag(join->thd, OPTIMIZER_SWITCH_USE_ROWID_FILTER))
             s->table->init_cost_info_for_usable_range_rowid_filters(join->thd);
         }
         if (!impossible_range)
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 6b14c4f3fcc..b056b13bca3 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -2471,6 +2471,8 @@ int multi_update::do_updates()
     table = cur_table->table;
     if (table == table_to_update)
       continue;					// Already updated
+    if (table->file->pushed_rowid_filter)
+      table->file->disable_pushed_rowid_filter();
     org_updated= updated;
     tmp_table= tmp_tables[cur_table->shared];
     tmp_table->file->extra(HA_EXTRA_CACHE);	// Change to read cache
@@ -2665,7 +2667,8 @@ int multi_update::do_updates()
     check_opt_it.rewind();
     while (TABLE *tbl= check_opt_it++)
         tbl->file->ha_rnd_end();
-
+    if (table->file->save_pushed_rowid_filter)
+      table->file->enable_pushed_rowid_filter();
   }
   DBUG_RETURN(0);
 
@@ -2676,6 +2679,8 @@ err:
   }
 
 err2:
+  if (table->file->save_pushed_rowid_filter)
+    table->file->enable_pushed_rowid_filter();
   if (table->file->inited)
     (void) table->file->ha_rnd_end();
   if (tmp_table->file->inited)
@@ -2955,7 +2960,9 @@ bool Sql_cmd_update::prepare_inner(THD *thd)
     {
       goto err;
     }
-
+    if (!multitable &&
+        select_lex->sj_subselects.elements)
+      multitable= true;
   }
 
   if (table_list->has_period())
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index a587a378086..13dc602a2d6 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -13370,8 +13370,21 @@ delete_single_table:
                                            YYPS->m_lock_type,
                                            YYPS->m_mdl_type,
                                            NULL,
+                                           0)))
+              MYSQL_YYABORT;
+            Select->table_list.save_and_clear(&Lex->auxiliary_table_list);
+            Lex->table_count= 1;
+            Lex->query_tables= 0;
+            Lex->query_tables_last= &Lex->query_tables;
+            if (unlikely(!Select->
+                         add_table_to_list(thd, $2, NULL, TL_OPTION_UPDATING,
+                                           YYPS->m_lock_type,
+                                           YYPS->m_mdl_type,
+                                           NULL,
                                            $3)))
               MYSQL_YYABORT;
+            Lex->auxiliary_table_list.first->correspondent_table=
+              Lex->query_tables;
             YYPS->m_lock_type= TL_READ_DEFAULT;
             YYPS->m_mdl_type= MDL_SHARED_READ;
           }