Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-35848

Multi-table DELETE with order by...limit works incorrect

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • N/A
    • 11.8.1
    • Optimizer
    • 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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35955 Wrong result for UPDATE ... ORDER BY LIMIT which uses tmp.table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is caused by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-30469 Support ORDER BY and LIMIT for multi-table DELETE, index hints for single-table DELETE.

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-35568 reintroduce delete_while_scanning to multi_delete

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          People

            Gosselin Dave Gosselin
            lstartseva Lena Startseva
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.