[MDEV-17716] multi-UPDATE with and without prepared satements accept different syntax Created: 2018-11-14  Updated: 2022-11-07  Resolved: 2022-11-07

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.0
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Oleksandr Byelkin Assignee: Oleksandr Byelkin
Resolution: Won't Fix Votes: 0
Labels: None


 Description    

CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 
CREATE TABLE t2 (b INT, c INT, KEY(b)) ENGINE=InnoDB PARTITION BY HASH(c) PARTITIONS 2;
 
CREATE ALGORITHM = MERGE VIEW v AS SELECT a, b FROM t1 STRAIGHT_JOIN t2 WHERE b = 'foo' WITH CHECK OPTION;
 
 
INSERT INTO t1 VALUES (1),(2);
 
INSERT IGNORE INTO t2 VALUES (2,2),('three',3),(4,4);
 
UPDATE v SET a = NULL ORDER BY a, b;

above will be error with prepared statements due to check at the end of multi_update_precheck()



 Comments   
Comment by Oleksandr Byelkin [ 2018-11-14 ]

5.5, 10.1 and up have to be checked

Comment by Oleksandr Byelkin [ 2018-11-14 ]

9aa459f0df7e000224e4ac54c5009eac42ef365a

Comment by Oleksandr Byelkin [ 2018-11-14 ]

Usual UPDATE accept ORDER BY/LIMIT and multi-UPDATE does not, with multi-table view in usual execution and prepare it checked by usual UPDATE rules and on execute by rules of multi-UPDATE (which it is actually is).

Comment by Elena Stepanova [ 2022-10-08 ]

The error until 10.3 was

10.2

 
query 'UPDATE v SET a = NULL ORDER BY a, b' failed: 1221: Incorrect usage of UPDATE and ORDER BY

10.3+ isn't affected, although I didn't search for the change which fixed it.

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