Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
N/A
-
None
Description
There was a bug in feature MDEV-30469 for some conditions and after fix MDEV-35568 the behavior became even more strange:
Example case:
create table t1 (id int primary key, v int); |
create table t2 (id int primary key, v int); |
insert into t1 (id, v) values (2,3),(1,4); |
insert into t2 (id, v) values (5,5),(6,6); |
select * from t1; |
id v
|
2 3
|
1 4
|
select * from t2; |
id v
|
5 5
|
6 6
|
select t1.*, t2.* from t1, t2 order by t1.id, t2.id; |
id v id v
|
1 4 5 5
|
1 4 6 6
|
2 3 5 5
|
2 3 6 6
|
Limit 1
Result before fix (looks right):
select t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 1; |
id v id v
|
1 4 5 5
|
delete t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 1; |
select * from t1; |
id v
|
1 4
|
select * from t2; |
id v
|
6 6
|
Result after fix MDEV-35568(incorrect):
select t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 1; |
id v id v
|
1 4 5 5
|
delete t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 1; |
select * from t1; |
id v
|
2 3
|
1 4
|
select * from t2; |
id v
|
6 6
|
drop tables t1,t2; |
Limit 2
Result before fix (I expect that two records from table t2 will be deleted, but two records from table t1 were deleted):
select t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 2; |
id v id v
|
1 4 5 5
|
1 4 6 6
|
delete t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 2; |
select * from t1; |
id v
|
select * from t2; |
id v
|
6 6
|
Result after fix MDEV-35568(incorrect: nothing has changed compared to the previous request "limit 1"):
select t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 2; |
id v id v
|
1 4 5 5
|
1 4 6 6
|
delete t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 2; |
select * from t1; |
id v
|
2 3
|
1 4
|
select * from t2; |
id v
|
6 6
|
Limit 3
Result before fix (looks right):
select t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 3; |
id v id v
|
1 4 5 5
|
1 4 6 6
|
2 3 5 5
|
delete t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 3; |
select * from t1; |
id v
|
select * from t2; |
id v
|
Result after fix MDEV-35568(incorrect: this behavior continues for other "limit n", n>3 ):
select t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 3; |
id v id v
|
1 4 5 5
|
1 4 6 6
|
2 3 5 5
|
delete t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 3; |
select * from t1; |
id v
|
2 3
|
1 4
|
select * from t2; |
id v
|
Attachments
Issue Links
- is blocked by
-
-
- Closed
-
- is caused by
-
MDEV-30469 Support ORDER BY and LIMIT for multi-table DELETE, index hints for single-table DELETE.
-
- Closed
-
-
MDEV-35568 reintroduce delete_while_scanning to multi_delete
-
- Closed
-
OK to push