mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-07-04 10:28:17 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/delete_innodb.result
Dave Gosselin 5001300bd4 MDEV-30469 Support ORDER BY and LIMIT for multi-table DELETE, index hints for single-table DELETE 
We now allow multitable queries with order by and limit, such as:
  delete t1.*, t2.* from t1, t2 order by t1.id desc limit 3;
To predict what rows will be deleted, run the equivalent select:
  select t1.*, t2.* from t1, t2 order by t1.id desc limit 3;
Additionally, index hints are now supported with single table delete statements:
  delete from t2 use index(xid) order by (id) limit 2;

This approach changes the multi_delete SELECT result interceptor to use a temporary
table to collect row ids pertaining to the rows that will be deleted, rather than
directly deleting rows from the target table(s).  Row ids are collected during
send_data, then read during send_eof to delete target rows.  In the event that the
temporary table created in memory is not big enough for all matching rows, it is
converted to an aria table.

Other changes:
  - Deleting from a sequence now affects zero rows instead of emitting an error

Limitations:
  - The federated connector does not create implicit row ids, so we to use a key
  when conditionally deleting.  See the change in federated_maybe_16324629.test
2025-02-05 10:12:27 -05:00

586 lines
20 KiB
Text
Raw Permalink Blame History

# 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 2.00 100.00 100.00 Using filesort
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 1.00 100.00 50.00 Using where; FirstMatch(t1)
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 101 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": "101",
"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 101 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": "101",
"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 101 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": "101",
"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 101 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": "101",
"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
Reference in a new issue View git blame Copy permalink