[MDEV-26844] DELETE returns ROW_NUMBER=1 for every row upon ER_TRUNCATED_WRONG_VALUE Created: 2021-10-15  Updated: 2021-10-26  Resolved: 2021-10-26

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Delete
Affects Version/s: N/A
Fix Version/s: 10.7.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Rucha Deodhar
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-10075 Provide index of error causing error ... Closed
Relates
relates to MDEV-26909 ROW_NUMBER and DELETE/UPDATE with ORD... Open

 Description    

create or replace table t (a varchar(8));
 
insert into t values ('val1'),('val2'),('100'),('val4');
 
delete from t where a = 100;
 
 
 
show warnings;
 
get diagnostics condition 3 @n = row_number;
 
select @n;

bb-10.7-row_number cb9002bee36

 
+---------+------+------------------------------------------+
 
| Level   | Code | Message                                  |
 
+---------+------+------------------------------------------+
 
| Warning | 1292 | Truncated incorrect DOUBLE value: 'val1' |
 
| Warning | 1292 | Truncated incorrect DOUBLE value: 'val2' |
 
| Warning | 1292 | Truncated incorrect DOUBLE value: 'val4' |
 
+---------+------+------------------------------------------+
 
3 rows in set (0.000 sec)
 
 
 
MariaDB [test]> get diagnostics condition 3 @n = row_number;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> select @n;
 
+------+
 
| @n   |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.000 sec)

Same for every condition.
It should be either 0 or different for each row.

If I add ORDER BY to DELETE, it starts returning ROW_NUMBER=0 instead:

MariaDB [test]> delete from t where a = 100 order by a;
 
Query OK, 0 rows affected, 3 warnings (0.001 sec)
 
 
 
MariaDB [test]> get diagnostics condition 3 @n = row_number;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> select @n;
 
+------+
 
| @n   |
 
+------+
 
|    0 |
 
+------+
 
1 row in set (0.001 sec)

This is also counter-intuitive. If there supposed to be any difference in behavior, I would expect DELETE with ORDER BY to be supported and without ORDER BY unsupported, but not vice versa as it appears now.

 Comments   
Comment by Rucha Deodhar [ 2021-10-19 ]

Patch: https://github.com/MariaDB/server/commit/76af9e1d56053e0fc5519104ab86e100ffb980be

Comment by Sergei Golubchik [ 2021-10-24 ]

let's split this MDEV is two. The first is without ORDER BY and without any filesort changes, only DELETE part:

CREATE TABLE t (a VARCHAR(8));
 
INSERT INTO t VALUES ('val1'),('val2'),('100'),('val4');
 
DELETE FROM t WHERE a = 100;
 
GET DIAGNOSTICS CONDITION 3 @n = ROW_NUMBER;
 
SELECT @n;
 
DROP TABLE t;

This test and fix are ok to push, please, push them.

Comment by Rucha Deodhar [ 2021-10-24 ]

Fixed as two separate commits and pushed to bb-10.7-row_number

Comment by Sergei Golubchik [ 2021-10-24 ]

sorry, I wasn't clear enough. Only the first part — without ORDER BY — was ok to push.

but np problem, the fix isn't in the main tree yet, so nothing irreversible has happened yet, let's continue.

Please, try this test case:

CREATE TABLE t (a VARCHAR(8), b tinyint);
 
INSERT INTO t(a) VALUES ('val1'),('val2'),('100'),('val4'),('100');
 
SELECT * FROM t;
 
set sql_mode='';
 
UPDATE t SET b=1234 WHERE a = 100 ORDER BY a;
 
GET DIAGNOSTICS CONDITION 2 @n = ROW_NUMBER;
 
SELECT @n;
 
GET DIAGNOSTICS CONDITION 5 @n = ROW_NUMBER;
 
SELECT @n;

Comment by Rucha Deodhar [ 2021-10-25 ]

Patch (fix for UPDATE and DELETE with ORDER BY) : https://github.com/MariaDB/server/commit/0b01c7cb7bc594061f689f13fec8c40449996d62

Comment by Sergei Golubchik [ 2021-10-26 ]

the ORDER BY issue is moved to MDEV-26909

Generated at Thu Feb 08 09:48:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.