[MDEV-14551] Can't find record in table on multi-table update with ORDER BY Created: 2017-11-30  Updated: 2020-12-09  Resolved: 2018-05-17

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 10.3
Fix Version/s: 10.3.7

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: 10.3-ga, affects-tests, regression

Issue Links:
PartOf
includes MDEV-21112 Can't find record when using LIMIT 1 ... Closed
Relates
relates to MDEV-14847 Assertion `(buff[7] & 7) == HEAD_PAGE... Closed
relates to MDEV-13911 Support ORDER BY and LIMIT in multi-t... Closed
relates to MDEV-17573 Assertion in federatedx on multi-update Closed
relates to MDEV-20515 multi-update tries to position update... Closed
Sprint: 10.3.6-1

 Description    

--source include/have_partition.inc
 
 
 
CREATE TABLE t1 (i INT) ENGINE=MyISAM;
 
INSERT IGNORE INTO t1 VALUES (1),(2);
 
 
 
CREATE TABLE t2 (f INT) ENGINE=MyISAM 
PARTITION BY RANGE(f) (
 
  PARTITION p1 VALUES LESS THAN (128),
 
  PARTITION p2 VALUES LESS THAN MAXVALUE
 
);
 
REPLACE INTO t2 VALUES (1),(2);
 
 
 
UPDATE t1, t2 SET f = 126 ORDER BY f LIMIT 2;
 
 
 
# Cleanup
 
DROP TABLE t1, t2;


MariaDB [test]> UPDATE t1, t2 SET f = 126 ORDER BY f LIMIT 2;
 
ERROR 1032 (HY000): Can't find record in 't2'



 Comments   
Comment by Elena Stepanova [ 2017-11-30 ]

Also, while the test case from the description is not applicable to previous versions (because of the ORDER BY in UPDATE), this one with views instead of a direct multi-update is, and it appears to show a regression:

--source include/have_partition.inc
 
 
 
CREATE TABLE t1 (i INT);
 
INSERT IGNORE INTO t1 VALUES (1),(2);
 
 
 
CREATE TABLE t2 (f INT) 
PARTITION BY RANGE(f) (
 
  PARTITION p1 VALUES LESS THAN (128),
 
  PARTITION p2 VALUES LESS THAN MAXVALUE
 
);
 
 
 
CREATE VIEW v AS SELECT f FROM t1, t2;
 
REPLACE INTO t2 VALUES (1),(2);
 
 
 
UPDATE v SET f = 126 ORDER BY f LIMIT 2;
 
 
 
# Cleanup
 
DROP VIEW v;
 
DROP TABLE t1, t2;

It passes on 10.2, but causes the same ER_KEY_NOT_FOUND on 10.3.

Comment by Sergei Petrunia [ 2018-04-13 ]

I assume the ball is on Serg's side until the failing example is fixed.

Generated at Thu Feb 08 08:14:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.