[MDEV-26854] Wrong row number reported upon multi-table update Created: 2021-10-18  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Update, Server
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None


 Description    

create or replace table t (a tinyint);
 
insert into t values (1),(2),(127);
 
update t t1 join t t2 set t1.a = t1.a + 100 order by t1.a;
 
 
 
# Cleanup
 
drop table t;

10.3 b378ddb3

 
MariaDB [test]> update t t1 join t t2 set t1.a = t1.a + 100 order by t1.a;
 
ERROR 1264 (22003): Out of range value for column 'a' at row 1

ROW_NUMBER from MDEV-10075 returns the same value.

It is set to 1 even when the update cannot be performed due to safe mode. In this case it is not reported in the error message, but ROW_NUMBER demonstrates it:

bb-10.7-row_number c27f04ed

 
MariaDB [test]> update t t1 join t t2 set t1.a = t1.a + 100 order by t1.a;
 
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
 
MariaDB [test]> get diagnostics condition 1 @n = row_number; select @n;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
+------+
 
| @n   |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.000 sec)

(For single-table update it would be 0).
Generated at Thu Feb 08 09:48:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.