# Tests for delete with INNODB # # MDEV-22187: SIGSEGV in ha_innobase::cmp_ref on DELETE # SET @save_sort_buffer_size= @@sort_buffer_size; SET sort_buffer_size=1024; CREATE TABLE t1(c1 CHAR(255) PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 VALUES (0), ('a'), ('b'); ANALYZE TABLE t1 PERSISTENT FOR ALL; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT * FROM t1; c1 0 a b EXPLAIN DELETE b FROM t1 AS a JOIN t1 AS b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE a ALL NULL NULL NULL NULL 3 1 SIMPLE b ALL NULL NULL NULL NULL 3 DELETE b FROM t1 AS a JOIN t1 AS b; SELECT * FROM t1; c1 SET sort_buffer_size=@save_sort_buffer_size; DROP TABLE t1; # # MDEV-32212 DELETE with ORDER BY and semijoin optimization causing crash # CREATE TABLE t1 (c1 INT) ENGINE=InnoDB; CREATE TABLE t2 (c2 INT) ENGINE=InnoDB; INSERT INTO t1 values (1),(2),(3),(4),(5),(6); INSERT INTO t2 values (2); DELETE FROM t1 WHERE c1 IN (select c2 from t2); select * from t1; c1 1 3 4 5 6 truncate t1; truncate t2; INSERT INTO t1 values (1),(2),(3),(4),(5),(6); INSERT INTO t2 values (2); check sj optimization with order-by analyze DELETE FROM t1 WHERE c1 IN (select c2 from t2) ORDER BY c1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using filesort 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 1.00 100.00 16.67 Using where; FirstMatch(t1) select * from t1; c1 1 3 4 5 6 truncate t2; INSERT INTO t2 values (3); disallows sj optimization analyze DELETE FROM t1 WHERE c1 IN (select c2 from t2) ORDER BY c1 limit 1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 1.00 100.00 100.00 Using where; Using filesort 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 1.00 100.00 20.00 Using where select * from t1; c1 1 4 5 6 DROP TABLE t1, t2; # # MDEV-33533: multi-delete using rowid filter # set @save_default_storage_engine=@@default_storage_engine; set default_storage_engine=InnoDB; CREATE DATABASE dbt3_s001; use dbt3_s001; create index i_n_name on nation(n_name); analyze table nation, lineitem, customer, orders, part, supplier, partsupp, region persistent for all; Table Op Msg_type Msg_text dbt3_s001.nation analyze status Engine-independent statistics collected dbt3_s001.nation analyze status OK dbt3_s001.lineitem analyze status Engine-independent statistics collected dbt3_s001.lineitem analyze status OK dbt3_s001.customer analyze status Engine-independent statistics collected dbt3_s001.customer analyze status OK dbt3_s001.orders analyze status Engine-independent statistics collected dbt3_s001.orders analyze status OK dbt3_s001.part analyze status Engine-independent statistics collected dbt3_s001.part analyze status OK dbt3_s001.supplier analyze status Engine-independent statistics collected dbt3_s001.supplier analyze status OK dbt3_s001.partsupp analyze status Engine-independent statistics collected dbt3_s001.partsupp analyze status OK dbt3_s001.region analyze status Engine-independent statistics collected dbt3_s001.region analyze status OK explain select o_orderkey, o_totalprice from orders, customer, nation where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey = c_custkey and c_nationkey = n_nationkey and n_name='PERU'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using where; Using index 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 Using index 1 SIMPLE orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (8%) Using where; Using rowid filter explain format=json select o_orderkey, o_totalprice from orders, customer, nation where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey = c_custkey and c_nationkey = n_nationkey and n_name='PERU'; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "nation", "access_type": "ref", "possible_keys": ["PRIMARY", "i_n_name"], "key": "i_n_name", "key_length": "26", "used_key_parts": ["n_name"], "ref": ["const"], "loops": 1, "rows": 1, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "nation.n_name = 'PERU'", "using_index": true } }, { "table": { "table_name": "customer", "access_type": "ref", "possible_keys": ["PRIMARY", "i_c_nationkey"], "key": "i_c_nationkey", "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], "loops": 1, "rows": 6, "cost": "COST_REPLACED", "filtered": 100, "using_index": true } }, { "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": 119, "selectivity_pct": 7.933333333 }, "loops": 6, "rows": 15, "cost": "COST_REPLACED", "filtered": 7.933333397, "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" } } ] } } select o_orderkey, o_totalprice from orders, customer, nation where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey = c_custkey and c_nationkey = n_nationkey and 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 orders.* from orders, customer, nation where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey = c_custkey and c_nationkey = n_nationkey and n_name='PERU'; explain delete from orders using orders, customer, nation where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey = c_custkey and c_nationkey = n_nationkey and n_name='PERU'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using where; Using index 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 Using index 1 SIMPLE orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (8%) Using where; Using rowid filter explain format=json delete from orders using orders, customer, nation where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey = c_custkey and c_nationkey = n_nationkey and n_name='PERU'; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "nation", "access_type": "ref", "possible_keys": ["PRIMARY", "i_n_name"], "key": "i_n_name", "key_length": "26", "used_key_parts": ["n_name"], "ref": ["const"], "loops": 1, "rows": 1, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "nation.n_name = 'PERU'", "using_index": true } }, { "table": { "table_name": "customer", "access_type": "ref", "possible_keys": ["PRIMARY", "i_c_nationkey"], "key": "i_c_nationkey", "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], "loops": 1, "rows": 6, "cost": "COST_REPLACED", "filtered": 100, "using_index": true } }, { "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": 119, "selectivity_pct": 7.933333333 }, "loops": 6, "rows": 15, "cost": "COST_REPLACED", "filtered": 7.933333397, "attached_condition": "orders.o_orderDATE between '1992-01-01' and '1992-06-30'" } } ] } } delete from orders using orders, customer, nation where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey = c_custkey and c_nationkey = n_nationkey and n_name='PERU'; select o_orderkey, o_totalprice from orders, customer, nation where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey = c_custkey and c_nationkey = n_nationkey and n_name='PERU'; o_orderkey o_totalprice insert into orders select * from t; select o_orderkey, o_totalprice from orders, customer, nation where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey = c_custkey and c_nationkey = n_nationkey and 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 prepare stmt from " delete from orders using orders, customer, nation where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey = c_custkey and c_nationkey = n_nationkey and n_name='PERU'; "; execute stmt; select o_orderkey, o_totalprice from orders, customer, nation where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey = c_custkey and c_nationkey = n_nationkey and n_name='PERU'; o_orderkey o_totalprice insert into orders select * from t; select o_orderkey, o_totalprice from orders, customer, nation where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey = c_custkey and c_nationkey = n_nationkey and 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 execute stmt; select o_orderkey, o_totalprice from orders, customer, nation where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey = c_custkey and c_nationkey = n_nationkey and n_name='PERU'; o_orderkey o_totalprice insert into orders select * from t; select o_orderkey, o_totalprice from orders, customer, nation where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey = c_custkey and c_nationkey = n_nationkey and 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 deallocate prepare stmt; drop table t; 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 where; Using index 1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 Using index 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (8%) Using where; Using rowid filter explain format=json select o_orderkey, o_totalprice from orders where o_orderDATE between '1992-01-01' and '1992-06-30' and o_custkey in (select c_custkey from customer where c_nationkey in (select n_nationkey from nation where n_name='PERU')); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "nation", "access_type": "ref", "possible_keys": ["PRIMARY", "i_n_name"], "key": "i_n_name", "key_length": "26", "used_key_parts": ["n_name"], "ref": ["const"], "loops": 1, "rows": 1, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "nation.n_name = 'PERU'", "using_index": true } }, { "table": { "table_name": "customer", "access_type": "ref", "possible_keys": ["PRIMARY", "i_c_nationkey"], "key": "i_c_nationkey", "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], "loops": 1, "rows": 6, "cost": "COST_REPLACED", "filtered": 100, "using_index": true } }, { "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": 119, "selectivity_pct": 7.933333333 }, "loops": 6, "rows": 15, "cost": "COST_REPLACED", "filtered": 7.933333397, "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 where; Using index 1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 Using index 1 PRIMARY orders ref|filter i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (8%) 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, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "nation", "access_type": "ref", "possible_keys": ["PRIMARY", "i_n_name"], "key": "i_n_name", "key_length": "26", "used_key_parts": ["n_name"], "ref": ["const"], "loops": 1, "rows": 1, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "nation.n_name = 'PERU'", "using_index": true } }, { "table": { "table_name": "customer", "access_type": "ref", "possible_keys": ["PRIMARY", "i_c_nationkey"], "key": "i_c_nationkey", "key_length": "5", "used_key_parts": ["c_nationkey"], "ref": ["dbt3_s001.nation.n_nationkey"], "loops": 1, "rows": 6, "cost": "COST_REPLACED", "filtered": 100, "using_index": true } }, { "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": 119, "selectivity_pct": 7.933333333 }, "loops": 6, "rows": 15, "cost": "COST_REPLACED", "filtered": 7.933333397, "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; DROP DATABASE dbt3_s001; set default_storage_engine=@save_default_storage_engine; # # Additional tests of first table and rowid filter # CREATE DATABASE dbt3_s001; use dbt3_s001; set @save_default_storage_engine=@@default_storage_engine; set default_storage_engine=InnoDB; CREATE INDEX i_l_quantity ON lineitem(l_quantity); CREATE INDEX i_o_totalprice ON orders(o_totalprice); ANALYZE TABLE lineitem, orders; Table Op Msg_type Msg_text dbt3_s001.lineitem analyze status Engine-independent statistics collected dbt3_s001.lineitem analyze status OK dbt3_s001.orders analyze status Engine-independent statistics collected dbt3_s001.orders analyze status OK set optimizer_use_condition_selectivity=2; create table second(s_receiptDATE date, filler char(100), key(s_receiptDATE)) ; insert into second select date_add(l_receiptDATE, interval 1 day), 'helllo' from lineitem ; select count(*) from lineitem, second WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 47 and second.s_receiptDATE = date_add(l_receiptDATE, interval 1 day); count(*) 114 # lineitem should be first and with "Using rowid filter" explain delete lineitem FROM lineitem, second WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 47 and second.s_receiptDATE = date_add(l_receiptDATE, interval 1 day); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE lineitem range|filter i_l_shipdate,i_l_quantity i_l_quantity|i_l_shipdate 9|4 NULL 349 (8%) Using where; Using rowid filter 1 SIMPLE second ref s_receiptDATE s_receiptDATE 4 func 1 Using where; Using index delete lineitem FROM lineitem, second WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 47 and second.s_receiptDATE = date_add(l_receiptDATE, interval 1 day); # Should be 0 select count(*) from lineitem, second WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 47 and second.s_receiptDATE = date_add(l_receiptDATE, interval 1 day); count(*) 0 drop database dbt3_s001; set default_storage_engine=@save_default_storage_engine; End of 11.1 tests