[MDEV-24961] Multi-table UPDATE does not consider ORDER BY clause - Jira

Oskar Schneider created issue - 2021-02-24 06:50

Oskar Schneider made changes - 2021-02-24 06:50

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-13911~~ [ ~~MDEV-13911~~ ]

Oskar Schneider made changes - 2021-02-24 06:52

Description

{noformat}
CREATE TABLE AP (
prid int not null,
prio int not null
);

CREATE TABLE APD (
prid int not null,
info varchar(64)
);

INSERT INTO AP VALUES (1,3), (2,3), (3,2), (4,2), (5,1), (6,1);
INSERT INTO APD (prid) VALUES (1), (2), (3), (4), (5), (6);

SELECT * FROM AP;
SELECT * FROM APD;

# The SELECT returns the correct values for prid (prid 3,5,6)
SELECT APD.prid, AP.prio FROM AP INNER JOIN APD ON AP.prid = APD.prid
ORDER BY AP.prio, AP.prid
LIMIT 3;

# The UPDATE with identical ORDER updates the columns with prid 1,2,3
UPDATE AP INNER JOIN APD ON AP.prid = APD.prid
SET info = 'UPDATED'
ORDER BY AP.prio, AP.prid
LIMIT 3;

SELECT * FROM APD;
{noformat}

The script returns this output:

{noformat}
+------+------+
| prid | prio |
+------+------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 2 |
| 5 | 1 |
| 6 | 1 |
+------+------+
6 rows in set (0.001 sec)

+------+------+
| prid | info |
+------+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+------+------+
6 rows in set (0.000 sec)

+------+------+
| prid | prio |
+------+------+
| 5 | 1 |
| 6 | 1 |
| 3 | 2 |
+------+------+
3 rows in set (0.000 sec)

Query OK, 3 rows affected (0.001 sec)
Rows matched: 3 Changed: 3 Warnings: 0

+------+---------+
| prid | info |
+------+---------+
| 1 | UPDATED |
| 2 | UPDATED |
| 3 | UPDATED |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+------+---------+

{noformat}

In this script, the ORDER BY AP.prio is not consider:

{noformat}
CREATE TABLE AP (
prid int not null,
prio int not null
);

CREATE TABLE APD (
prid int not null,
info varchar(64)
);

INSERT INTO AP VALUES (1,3), (2,3), (3,2), (4,2), (5,1), (6,1);
INSERT INTO APD (prid) VALUES (1), (2), (3), (4), (5), (6);

SELECT * FROM AP;
SELECT * FROM APD;

# The SELECT returns the correct values for prid (prid 3,5,6)
SELECT APD.prid, AP.prio FROM AP INNER JOIN APD ON AP.prid = APD.prid
ORDER BY AP.prio, AP.prid
LIMIT 3;

# The UPDATE with identical ORDER updates the columns with prid 1,2,3
UPDATE AP INNER JOIN APD ON AP.prid = APD.prid
SET info = 'UPDATED'
ORDER BY AP.prio, AP.prid
LIMIT 3;

SELECT * FROM APD;
{noformat}

The script returns this output:

{noformat}
+------+------+
| prid | prio |
+------+------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 2 |
| 5 | 1 |
| 6 | 1 |
+------+------+
6 rows in set (0.001 sec)

+------+------+
| prid | info |
+------+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+------+------+
6 rows in set (0.000 sec)

+------+------+
| prid | prio |
+------+------+
| 5 | 1 |
| 6 | 1 |
| 3 | 2 |
+------+------+
3 rows in set (0.000 sec)

Query OK, 3 rows affected (0.001 sec)
Rows matched: 3 Changed: 3 Warnings: 0

+------+---------+
| prid | info |
+------+---------+
| 1 | UPDATED |
| 2 | UPDATED |
| 3 | UPDATED |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+------+---------+

{noformat}

Oskar Schneider made changes - 2021-02-24 06:52

Description

In this script, the ORDER BY AP.prio is not consider:

{noformat}
CREATE TABLE AP (
prid int not null,
prio int not null
);

CREATE TABLE APD (
prid int not null,
info varchar(64)
);

INSERT INTO AP VALUES (1,3), (2,3), (3,2), (4,2), (5,1), (6,1);
INSERT INTO APD (prid) VALUES (1), (2), (3), (4), (5), (6);

SELECT * FROM AP;
SELECT * FROM APD;

# The SELECT returns the correct values for prid (prid 3,5,6)
SELECT APD.prid, AP.prio FROM AP INNER JOIN APD ON AP.prid = APD.prid
ORDER BY AP.prio, AP.prid
LIMIT 3;

# The UPDATE with identical ORDER updates the columns with prid 1,2,3
UPDATE AP INNER JOIN APD ON AP.prid = APD.prid
SET info = 'UPDATED'
ORDER BY AP.prio, AP.prid
LIMIT 3;

SELECT * FROM APD;
{noformat}

The script returns this output:

{noformat}
+------+------+
| prid | prio |
+------+------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 2 |
| 5 | 1 |
| 6 | 1 |
+------+------+
6 rows in set (0.001 sec)

+------+------+
| prid | info |
+------+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+------+------+
6 rows in set (0.000 sec)

+------+------+
| prid | prio |
+------+------+
| 5 | 1 |
| 6 | 1 |
| 3 | 2 |
+------+------+
3 rows in set (0.000 sec)

Query OK, 3 rows affected (0.001 sec)
Rows matched: 3 Changed: 3 Warnings: 0

+------+---------+
| prid | info |
+------+---------+
| 1 | UPDATED |
| 2 | UPDATED |
| 3 | UPDATED |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+------+---------+

{noformat}

In this script, the ORDER BY AP.prio is not considered:

{noformat}
CREATE TABLE AP (
prid int not null,
prio int not null
);

CREATE TABLE APD (
prid int not null,
info varchar(64)
);

INSERT INTO AP VALUES (1,3), (2,3), (3,2), (4,2), (5,1), (6,1);
INSERT INTO APD (prid) VALUES (1), (2), (3), (4), (5), (6);

SELECT * FROM AP;
SELECT * FROM APD;

# The SELECT returns the correct values for prid (prid 3,5,6)
SELECT APD.prid, AP.prio FROM AP INNER JOIN APD ON AP.prid = APD.prid
ORDER BY AP.prio, AP.prid
LIMIT 3;

# The UPDATE with identical ORDER updates the columns with prid 1,2,3
UPDATE AP INNER JOIN APD ON AP.prid = APD.prid
SET info = 'UPDATED'
ORDER BY AP.prio, AP.prid
LIMIT 3;

SELECT * FROM APD;
{noformat}

The script returns this output:

{noformat}
+------+------+
| prid | prio |
+------+------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 2 |
| 5 | 1 |
| 6 | 1 |
+------+------+
6 rows in set (0.001 sec)

+------+------+
| prid | info |
+------+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+------+------+
6 rows in set (0.000 sec)

+------+------+
| prid | prio |
+------+------+
| 5 | 1 |
| 6 | 1 |
| 3 | 2 |
+------+------+
3 rows in set (0.000 sec)

Query OK, 3 rows affected (0.001 sec)
Rows matched: 3 Changed: 3 Warnings: 0

+------+---------+
| prid | info |
+------+---------+
| 1 | UPDATED |
| 2 | UPDATED |
| 3 | UPDATED |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+------+---------+

{noformat}

Alice Sherepa made changes - 2021-02-24 08:37

Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]

Alice Sherepa made changes - 2021-02-24 08:37

Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]

Alice Sherepa made changes - 2021-02-24 08:39

Assignee

Sergei Golubchik [ serg ]

Alice Sherepa added a comment - 2021-02-24 08:43

Thanks for the report!
as a temporary workaround:

MariaDB [test]> UPDATE AP INNER JOIN APD ON AP.prid = APD.prid

    ->  SET info = 'UPDATED', AP.prio=AP.prio

    ->  ORDER BY AP.prio, AP.prid

    ->  LIMIT 3;

Query OK, 3 rows affected (0.003 sec)

Rows matched: 6  Changed: 3  Warnings: 0

MariaDB [test]> SELECT * FROM APD;

+------+---------+

| prid | info    |

+------+---------+

|    1 | NULL    |

|    2 | NULL    |

|    3 | UPDATED |

|    4 | NULL    |

|    5 | UPDATED |

|    6 | UPDATED |

+------+---------+

6 rows in set (0.001 sec)

Alice Sherepa added a comment - 2021-02-24 08:43 Thanks for the report! as a temporary workaround: MariaDB [test]> UPDATE AP INNER JOIN APD ON AP.prid = APD.prid -> SET info = 'UPDATED', AP.prio=AP.prio -> ORDER BY AP.prio, AP.prid -> LIMIT 3; Query OK, 3 rows affected (0.003 sec) Rows matched: 6 Changed: 3 Warnings: 0 MariaDB [test]> SELECT * FROM APD; +------+---------+ | prid | info | +------+---------+ | 1 | NULL | | 2 | NULL | | 3 | UPDATED | | 4 | NULL | | 5 | UPDATED | | 6 | UPDATED | +------+---------+ 6 rows in set (0.001 sec)

Oskar Schneider added a comment - 2021-03-16 13:54 - edited

The workaround unfortunately does not work reliably. Sometimes it updates the data as expected, sometimes in the wrong order.
When the UPDATE didn't work it seems that the workaround changed the value of AP.prio to a different value although the assignment is AP.prio = AP.prio!

Oskar Schneider added a comment - 2021-03-16 13:54 - edited The workaround unfortunately does not work reliably. Sometimes it updates the data as expected, sometimes in the wrong order. When the UPDATE didn't work it seems that the workaround changed the value of AP.prio to a different value although the assignment is AP.prio = AP.prio!

Sergei Golubchik made changes - 2021-12-06 21:34

Workflow

MariaDB v3 [ 119452 ]

MariaDB v4 [ 142619 ]

David Townes added a comment - 2023-03-19 21:20 - edited

Encountered this bug today in MariaDB 10.6.12, and it appears it might be related to internal use of temporary tables.

In all the instances I have tested where "Using temporary" shows up in the query explanation and ordering is not only requested by also required on any table other than the first in a join sequence (the above example outputs correctly if the values in AP.prio are instead ascending and sequential), the ORDER BY clause seems to result in updates being applied to randomly sorted rows on subsequent tables.

See ~~MDEV-20081~~, where it looks like this issue was observed in Daniel Black's comments but dismissed as irrelevant to that particular case. It also looks like related problems were addressed in ~~MDEV-14551~~ and ~~MDEV-20515~~, with commits related to multi_update::prepare2, but this issue somehow remains, and my C++ debugging skills are not sophisticated enough to localize to problem in sql_update.cc (I tried).

The workaround above likely works sometimes but not others because internal temporary table use depends on a wide variety of factors, and convincing the server to avoid use of internal temporary tables is highly context dependent. In my testing, moving the table referenced by columns in the ORDER BY clause to first position and then swapping STRAIGHT_JOIN for INNER JOIN stopped the server from using internal temporary tables for larger queries and cause the update to perform correctly, but the same tactic does not appear to help with the simple text example above, and while the workaround above does work with the simple text example it does not with larger queries.

For the queries in the original report, compare:

EXPLAIN UPDATE AP INNER JOIN APD ON AP.prid = APD.prid SET info = 'UPDATED' ORDER BY AP.prio, AP.prid LIMIT 3;

+------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |

+------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

|    1 | SIMPLE      | AP    | ALL  | NULL          | NULL | NULL    | NULL | 6    | Using temporary; Using filesort |

|    1 | SIMPLE      | APD   | ALL  | NULL          | NULL | NULL    | NULL | 6    | Using where                     |

+------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

EXPLAIN UPDATE AP INNER JOIN APD ON AP.prid = APD.prid SET info = 'UPDATED', AP.prio=AP.prio ORDER BY AP.prio, AP.prid LIMIT 3;

+------+-------------+-------+------+---------------+------+---------+------+------+----------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |

+------+-------------+-------+------+---------------+------+---------+------+------+----------------+

|    1 | SIMPLE      | AP    | ALL  | NULL          | NULL | NULL    | NULL | 6    | Using filesort |

|    1 | SIMPLE      | APD   | ALL  | NULL          | NULL | NULL    | NULL | 6    | Using where    |

+------+-------------+-------+------+---------------+------+---------+------+------+----------------+

David Townes added a comment - 2023-03-19 21:20 - edited Encountered this bug today in MariaDB 10.6.12, and it appears it might be related to internal use of temporary tables. In all the instances I have tested where "Using temporary" shows up in the query explanation and ordering is not only requested by also required on any table other than the first in a join sequence (the above example outputs correctly if the values in AP.prio are instead ascending and sequential), the ORDER BY clause seems to result in updates being applied to randomly sorted rows on subsequent tables. See MDEV-20081 , where it looks like this issue was observed in Daniel Black's comments but dismissed as irrelevant to that particular case. It also looks like related problems were addressed in MDEV-14551 and MDEV-20515 , with commits related to multi_update::prepare2, but this issue somehow remains, and my C++ debugging skills are not sophisticated enough to localize to problem in sql_update.cc (I tried). The workaround above likely works sometimes but not others because internal temporary table use depends on a wide variety of factors, and convincing the server to avoid use of internal temporary tables is highly context dependent. In my testing, moving the table referenced by columns in the ORDER BY clause to first position and then swapping STRAIGHT_JOIN for INNER JOIN stopped the server from using internal temporary tables for larger queries and cause the update to perform correctly, but the same tactic does not appear to help with the simple text example above, and while the workaround above does work with the simple text example it does not with larger queries. For the queries in the original report, compare: EXPLAIN UPDATE AP INNER JOIN APD ON AP.prid = APD.prid SET info = 'UPDATED' ORDER BY AP.prio, AP.prid LIMIT 3; +------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+ | 1 | SIMPLE | AP | ALL | NULL | NULL | NULL | NULL | 6 | Using temporary; Using filesort | | 1 | SIMPLE | APD | ALL | NULL | NULL | NULL | NULL | 6 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+ EXPLAIN UPDATE AP INNER JOIN APD ON AP.prid = APD.prid SET info = 'UPDATED', AP.prio=AP.prio ORDER BY AP.prio, AP.prid LIMIT 3; +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | AP | ALL | NULL | NULL | NULL | NULL | 6 | Using filesort | | 1 | SIMPLE | APD | ALL | NULL | NULL | NULL | NULL | 6 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+----------------+

Alice Sherepa made changes - 2023-03-20 11:53

Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.7 [ 24805 ]
Affects Version/s		10.8 [ 26121 ]
Affects Version/s		10.9 [ 26905 ]
Affects Version/s		10.10 [ 27530 ]
Affects Version/s		10.11 [ 27614 ]

Alice Sherepa made changes - 2023-03-20 11:53

Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.8 [ 26121 ]
Fix Version/s		10.9 [ 26905 ]
Fix Version/s		10.10 [ 27530 ]
Fix Version/s		10.11 [ 27614 ]
Fix Version/s	10.3 [ 22126 ]

Alice Sherepa made changes - 2023-03-20 11:55

Status

Open [ 1 ]

Confirmed [ 10101 ]

Sergei Golubchik made changes - 2023-03-20 12:40

Assignee

Sergei Golubchik [ serg ]

Rex Johnston [ JIRAUSER52533 ]

Rex Johnston made changes - 2023-03-20 17:12

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Julien Fritsch made changes - 2023-04-27 14:54

Fix Version/s

10.8 [ 26121 ]

Rex Johnston added a comment - 2023-08-01 20:39 - edited

A slightly clearer test.

CREATE TABLE t (

 incr int,

 decr int

);

CREATE TABLE target (

 incr int,

 label varchar(10)

);

INSERT INTO t VALUES (1,6), (2,5), (3,4), (4,3), (5,2), (6,1);

INSERT INTO target (incr, label) VALUES (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five'), (6, 'six');

SELECT * FROM t;

SELECT * FROM target;

# The SELECT returns the correct values for incr {4,5,6}

SELECT target.incr, t.decr, target.label FROM t INNER JOIN target ON t.incr = target.incr

 ORDER BY t.decr

 LIMIT 3;

# The UPDATE with identical ORDER we expect the labels shown above {4,5,6} to be replaced with "SELECTED", {1,2,3} left unchanged

UPDATE t INNER JOIN target ON t.incr = target.incr

 SET label = 'SELECTED'

 ORDER BY t.decr

 LIMIT 3;

SELECT * FROM target;

DROP table t, target;

Just tested on 11.2, which fails in a slightly different way to 10.6.

In testing, the temporary table created for iterating over (representing the equivalent select), has "rowid" pushed onto the front of the order by list, but the fix requires more than just correcting this.

Rex Johnston added a comment - 2023-08-01 20:39 - edited A slightly clearer test. CREATE TABLE t ( incr int , decr int ); CREATE TABLE target ( incr int , label varchar (10) ); INSERT INTO t VALUES (1,6), (2,5), (3,4), (4,3), (5,2), (6,1); INSERT INTO target (incr, label) VALUES (1, 'one' ), (2, 'two' ), (3, 'three' ), (4, 'four' ), (5, 'five' ), (6, 'six' ); SELECT * FROM t; SELECT * FROM target; # The SELECT returns the correct values for incr {4,5,6} SELECT target.incr, t.decr, target.label FROM t INNER JOIN target ON t.incr = target.incr ORDER BY t.decr LIMIT 3; # The UPDATE with identical ORDER we expect the labels shown above {4,5,6} to be replaced with "SELECTED" , {1,2,3} left unchanged UPDATE t INNER JOIN target ON t.incr = target.incr SET label = 'SELECTED' ORDER BY t.decr LIMIT 3; SELECT * FROM target; DROP table t, target; Just tested on 11.2, which fails in a slightly different way to 10.6. In testing, the temporary table created for iterating over (representing the equivalent select), has "rowid" pushed onto the front of the order by list, but the fix requires more than just correcting this.

Rex Johnston made changes - 2023-08-01 20:44

Affects Version/s		11.0 [ 28320 ]
Affects Version/s		11.1 [ 28549 ]
Affects Version/s		11.2 [ 28603 ]

Alice Sherepa made changes - 2023-08-02 16:05

Link

This issue relates to MDEV-31828 [ MDEV-31828 ]

Rex Johnston made changes - 2023-11-07 04:12

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Rex Johnston made changes - 2023-11-26 17:22

Link

This issue relates to ~~MDEV-30469~~ [ ~~MDEV-30469~~ ]

Rex Johnston made changes - 2023-11-26 17:22

Link

This issue relates to ~~MDEV-30469~~ [ ~~MDEV-30469~~ ]

Julien Fritsch made changes - 2023-11-28 10:24

Fix Version/s

10.9 [ 26905 ]

Julien Fritsch made changes - 2023-11-28 10:29

Fix Version/s

10.10 [ 27530 ]

Julien Fritsch made changes - 2024-09-10 15:05

Fix Version/s

10.4 [ 22408 ]

MariaDB Server

Multi-table UPDATE does not consider ORDER BY clause

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration