Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5, 11.8
-
None
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
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue causes |
| Fix Version/s | 10.5 [ 23123 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Fix Version/s | 11.8 [ 29921 ] |
| Summary | UPDATE wrong result | Wrong result for UPDATE ... ORDER BY LIMIT which uses tmp.table |
| Component/s | Optimizer [ 10200 ] |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 114 [ 29980 ] | |
| Fix Version/s | 10.5 [ 23123 ] |
| Link |
This issue blocks |
| Link |
This issue causes |
| Assignee | Dave Gosselin [ JIRAUSER52216 ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
(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's fix is the second approach, my original approach is https://github.com/MariaDB/server/pull/3804 and both approaches fix the problem.
ee5bacdfda63cf356bdab92201aa130ca80961f1 is a good catch, thank you!
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.11.11 [ 29954 ] | |
| Fix Version/s | 11.4.5 [ 29956 ] | |
| Fix Version/s | 11.7.2 [ 29914 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 11.8 [ 29921 ] | |
| Fix Version/s | 114 [ 29980 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Fix Version/s | 10.11.12 [ 29998 ] | |
| Fix Version/s | 11.4.6 [ 29999 ] | |
| Fix Version/s | 11.7.2 [ 29914 ] | |
| Fix Version/s | 10.11.11 [ 29954 ] | |
| Fix Version/s | 11.4.5 [ 29956 ] |
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).