revision-id: efc4614112e8f252ac83320e0ad7035836b8a383 (mariadb-10.11.1-58-gefc4614) parent(s): 883675b8ce366166a245464e2e664c26055f9371 author: Igor Babaev committer: Igor Babaev timestamp: 2023-02-09 22:42:29 -0800 message: Adjusted test results after rebase --- mysql-test/main/delete_single_to_multi.result | 42 ++++++++----------------- mysql-test/main/update_single_to_multi.result | 44 +++++++++------------------ 2 files changed, 27 insertions(+), 59 deletions(-) diff --git a/mysql-test/main/delete_single_to_multi.result b/mysql-test/main/delete_single_to_multi.result index fe27f69..9bd5e93 100644 --- a/mysql-test/main/delete_single_to_multi.result +++ b/mysql-test/main/delete_single_to_multi.result @@ -344,11 +344,11 @@ where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 -1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey 5 dbt3_s001.supplier.s_suppkey 100 Using where +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 +1 PRIMARY orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (7%) Using where; Using rowid filter +1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 17 Using where 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where -1 PRIMARY orders eq_ref|filter PRIMARY,i_o_orderdate,i_o_custkey PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (7%) Using where; Using rowid filter -1 PRIMARY customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where +1 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where select l_orderkey, l_linenumber, l_tax from lineitem where l_orderkey in (select o_orderkey from orders where o_custkey in (select c_custkey from customer @@ -401,11 +401,11 @@ where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 -1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey 5 dbt3_s001.supplier.s_suppkey 100 Using where +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 +1 PRIMARY orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (7%) Using where; Using rowid filter +1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 17 Using where 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where -1 PRIMARY orders eq_ref|filter PRIMARY,i_o_orderdate,i_o_custkey PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (7%) Using where; Using rowid filter -1 PRIMARY customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where +1 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where delete from lineitem where l_orderkey in (select o_orderkey from orders where o_custkey in (select c_custkey from customer @@ -467,7 +467,7 @@ c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer ALL PRIMARY,i_c_nationkey NULL NULL NULL 150 Using where -1 PRIMARY nation eq_ref|filter PRIMARY,i_n_regionkey PRIMARY|i_n_regionkey 4|5 dbt3_s001.customer.c_nationkey 1 (40%) Using where; Using rowid filter +1 PRIMARY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1 Using where 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (9%) Using where; FirstMatch(nation); Using rowid filter explain format=json select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation @@ -499,14 +499,6 @@ EXPLAIN "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)" @@ -590,7 +582,7 @@ c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer ALL PRIMARY,i_c_nationkey NULL NULL NULL 150 Using where -1 PRIMARY nation eq_ref|filter PRIMARY,i_n_regionkey PRIMARY|i_n_regionkey 4|5 dbt3_s001.customer.c_nationkey 1 (40%) Using where; Using rowid filter +1 PRIMARY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1 Using where 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (9%) Using where; FirstMatch(nation); Using rowid filter explain format=json delete from customer where c_nationkey in (select n_nationkey from nation @@ -622,14 +614,6 @@ EXPLAIN "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)" @@ -2183,7 +2167,7 @@ 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 +2 DEPENDENT SUBQUERY orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 28 Using index condition; Using where delete from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08') returning c_name; c_name @@ -2296,7 +2280,7 @@ 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 +2 DEPENDENT SUBQUERY orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 28 Using index condition; Using where delete from customer where c_custkey in (select o_custkey from orders where o_orderDATE between '1992-01-09' and '1992-03-08') returning c_name; c_name @@ -2338,7 +2322,7 @@ where c_nationkey in (1,2)) order by o_totalprice limit 500; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY orders range i_o_orderdate i_o_orderdate 4 NULL 108 Using where; Using filesort -2 DEPENDENT SUBQUERY customer unique_subquery|filter PRIMARY,i_c_nationkey PRIMARY|i_c_nationkey 4|5 func 1 (10%) Using where; Using rowid filter +2 DEPENDENT SUBQUERY customer unique_subquery PRIMARY,i_c_nationkey PRIMARY 4 func 1 Using where 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 diff --git a/mysql-test/main/update_single_to_multi.result b/mysql-test/main/update_single_to_multi.result index cc28a0f..d020fd1 100644 --- a/mysql-test/main/update_single_to_multi.result +++ b/mysql-test/main/update_single_to_multi.result @@ -357,11 +357,11 @@ where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 -1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey 5 dbt3_s001.supplier.s_suppkey 100 Using where +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 +1 PRIMARY orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (7%) Using where; Using rowid filter +1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 17 Using where 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where -1 PRIMARY orders eq_ref|filter PRIMARY,i_o_orderdate,i_o_custkey PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (7%) Using where; Using rowid filter -1 PRIMARY customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where +1 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 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 @@ -377,10 +377,10 @@ 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 +4996 1 0.01 explain update lineitem set l_tax = (l_tax*100+1)/100 where l_orderkey in (select o_orderkey from orders where o_custkey in @@ -399,11 +399,11 @@ where n_name='PERU')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition 1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2 -1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey 5 dbt3_s001.supplier.s_suppkey 100 Using where +1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 11 +1 PRIMARY orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (7%) Using where; Using rowid filter +1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 17 Using where 1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where -1 PRIMARY orders eq_ref|filter PRIMARY,i_o_orderdate,i_o_custkey PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (7%) Using where; Using rowid filter -1 PRIMARY customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where +1 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 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 @@ -433,10 +433,10 @@ 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 +4996 1 0.02 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 @@ -466,10 +466,10 @@ 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 +4996 1 0.01 # FirstMatch # ========== set optimizer_switch='materialization=off'; @@ -481,7 +481,7 @@ c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer ALL PRIMARY,i_c_nationkey NULL NULL NULL 150 Using where -1 PRIMARY nation eq_ref|filter PRIMARY,i_n_regionkey PRIMARY|i_n_regionkey 4|5 dbt3_s001.customer.c_nationkey 1 (40%) Using where; Using rowid filter +1 PRIMARY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1 Using where 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (9%) Using where; FirstMatch(nation); Using rowid filter explain format=json select c_name, c_acctbal from customer where c_nationkey in (select n_nationkey from nation @@ -513,14 +513,6 @@ EXPLAIN "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)" @@ -598,7 +590,7 @@ c_custkey in (select o_custkey from orders where o_orderDATE between '1992-10-09' and '1993-06-08'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY customer ALL PRIMARY,i_c_nationkey NULL NULL NULL 150 Using where -1 PRIMARY nation eq_ref|filter PRIMARY,i_n_regionkey PRIMARY|i_n_regionkey 4|5 dbt3_s001.customer.c_nationkey 1 (40%) Using where; Using rowid filter +1 PRIMARY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1 Using where 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 11 (9%) Using where; FirstMatch(nation); Using rowid filter explain format=json update customer set c_acctbal = c_acctbal+10 where c_nationkey in (select n_nationkey from nation @@ -630,14 +622,6 @@ EXPLAIN "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)" @@ -2296,7 +2280,7 @@ where c_nationkey in (1,2)) order by o_totalprice limit 500; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY orders range i_o_orderdate i_o_orderdate 4 NULL 108 Using where; Using filesort -2 DEPENDENT SUBQUERY customer unique_subquery|filter PRIMARY,i_c_nationkey PRIMARY|i_c_nationkey 4|5 func 1 (10%) Using where; Using rowid filter +2 DEPENDENT SUBQUERY customer unique_subquery PRIMARY,i_c_nationkey PRIMARY 4 func 1 Using where update orders set o_totalprice = o_totalprice-50 where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (1,2))