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

Wrong result for UPDATE ... ORDER BY LIMIT which uses tmp.table

Details

    Description

      Discovered while debugging MDEV-35848.

      Preamble:

      drop table if exists t1, t2;
      		 
      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);

      Test case:

      UPDATE t1, t2 SET t1.v=-1, t2.v=-1 ORDER BY t1.id, t2.id LIMIT 2;

      Observed result:

      MariaDB [test]> select * from t1;
      		 
      +----+------+
      		 
      | id | v    |
      		 
      +----+------+
      		 
      |  1 |   -1 |
      		 
      |  2 |   -1 |
      		 
      +----+------+
      		 
      2 rows in set (0.002 sec)
      		 
       
      		 
      MariaDB [test]> select * from t2;
      		 
      +----+------+
      		 
      | id | v    |
      		 
      +----+------+
      		 
      |  5 |   -1 |
      		 
      |  6 |    6 |
      		 
      +----+------+
      		 
      2 rows in set (0.001 sec)

      Expected result:

      MariaDB [test]> select * from t1;
      		 
      +----+------+
      		 
      | id | v    |
      		 
      +----+------+
      		 
      |  1 |   -1 |
      		 
      |  2 |    3 |
      		 
      +----+------+
      		 
      2 rows in set (0.002 sec)
      		 
       
      		 
      MariaDB [test]> select * from t2;
      		 
      +----+------+
      		 
      | id | v    |
      		 
      +----+------+
      		 
      |  5 |   -1 |
      		 
      |  6 |   -1 |
      		 
      +----+------+
      		 
      2 rows in set (0.001 sec)

      From debugging, we see that between the calls to items1= ref_ptr_array_slice(slice_num: 2); and set_items_ref_array(src_arr: items1); (both from JOIN::make_aggr_tables_info()) the ORDER BY clause gets reversed so that t2 is before t1.

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35848 Multi-table DELETE with order by...limit works incorrect

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

          Activity

            Ascending order - Click to sort in descending order
            Gosselin Dave Gosselin added a comment -

            For UPDATE and during change_refs_to_tmp_fields, the select_lex->ref_pointer_array is mutated, then replaced by set_items_ref_array which causes the ORDER BY to reference different fields. Are the ORDER BY objects (ORDER*) also updated to point to the correct Items in the new ref array? No, they are not, and we can see that the ORDER* object's item pointers point someplace in the ref array and that's not updated after change_refs_to_tmp_fields. If we note the ORDER* addresses in setup_order (for example, at the call to find_order_in_list), we can see that they correctly reference their respective Item objects before the call to change_refs_to_tmp_fields in make_aggr_tables_info() and then, when the new ref array is installed, the same Item** pointers now, when dereferenced, point to different ORDER BY columns (that is, different Item instances, namely the ORDER* for t1.id now references t2.id and vice-versa).

            Gosselin Dave Gosselin added a comment - For UPDATE and during change_refs_to_tmp_fields, the select_lex->ref_pointer_array is mutated, then replaced by set_items_ref_array which causes the ORDER BY to reference different fields. Are the ORDER BY objects (ORDER*) also updated to point to the correct Items in the new ref array? No, they are not, and we can see that the ORDER* object's item pointers point someplace in the ref array and that's not updated after change_refs_to_tmp_fields. If we note the ORDER* addresses in setup_order (for example, at the call to find_order_in_list), we can see that they correctly reference their respective Item objects before the call to change_refs_to_tmp_fields in make_aggr_tables_info() and then, when the new ref array is installed, the same Item** pointers now, when dereferenced, point to different ORDER BY columns (that is, different Item instances, namely the ORDER* for t1.id now references t2.id and vice-versa).
            psergei Sergei Petrunia added a comment -

            (note the commit hash, there were several similar commits force-pushed):

            commit ee5bacdfda63cf356bdab92201aa130ca80961f1 (HEAD -> bb-10.11-MDEV-35955-variant2, origin/bb-10.11-MDEV-35955-variant2)
            		 
            Author: Sergei Petrunia <sergey@mariadb.com>
            		 
            Date:   Thu Jan 30 16:30:56 2025 +0200
            		 
             
            		 
                MDEV-35955 Wrong result for UPDATE ... ORDER BY LIMIT which uses tmp.table
            		 
                
                (Variant 2)
            		 
                Multi-table UPDATE ... ORDER BY ... LIMIT could update the wrong rows when
            		 
                ORDER BY was resolved by Using temporary + Using filesort.
            		 
            ...

            psergei Sergei Petrunia added a comment - (note the commit hash, there were several similar commits force-pushed): commit ee5bacdfda63cf356bdab92201aa130ca80961f1 (HEAD -> bb-10.11-MDEV-35955-variant2, origin/bb-10.11-MDEV-35955-variant2) Author: Sergei Petrunia <sergey@mariadb.com> Date: Thu Jan 30 16:30:56 2025 +0200   MDEV-35955 Wrong result for UPDATE ... ORDER BY LIMIT which uses tmp.table (Variant 2) Multi-table UPDATE ... ORDER BY ... LIMIT could update the wrong rows when ORDER BY was resolved by Using temporary + Using filesort. ...
            Gosselin Dave Gosselin added a comment - - edited

            psergei's fix is the second approach, my original approach is https://github.com/MariaDB/server/pull/3804 and both approaches fix the problem.

            Gosselin Dave Gosselin added a comment - - edited psergei 's fix is the second approach, my original approach is https://github.com/MariaDB/server/pull/3804 and both approaches fix the problem.
            sanja Oleksandr Byelkin added a comment -

            ee5bacdfda63cf356bdab92201aa130ca80961f1 is a good catch, thank you!

            sanja Oleksandr Byelkin added a comment - ee5bacdfda63cf356bdab92201aa130ca80961f1 is a good catch, thank you!

            People

              psergei Sergei Petrunia
              Gosselin Dave Gosselin
              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.