[MDEV-29537] Creation of view with UNION and SELECT ... FOR UPDATE in definition is failed with error Created: 2022-09-14  Updated: 2024-01-23

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4, 10.5, 10.6, 10.11

Type: Bug Priority: Minor
Reporter: Lena Startseva Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: view-protocol

Issue Links:
PartOf
is part of MDEV-27691 make working view-protocol Open
Relates
relates to MDEV-23203 SELECT FOR UPDATE with UNION should n... Closed

 Description   

Test:

CREATE TABLE t1 (i INT);
 
create view v1 as SELECT 1 FROM t1
 
UNION
 
SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
 
  FOR UPDATE;
 
select * from v1;
 
drop view v1;

Expected result:

1
 
1

Actual result:

query 'select * from v1' failed: ER_PARSE_ERROR (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'order by 1' at line 1



 Comments   
Comment by Marko Mäkelä [ 2022-09-14 ]

Is it a good idea to allow locking reads in a view, or in joins whose execution order might not be deterministic, depending on the query plan? Note: there is also LOCK IN SHARE MODE.

Comment by Lena Startseva [ 2022-09-15 ]

marko, I agree with you, but since the documentation does not prohibit this and in version 10.3 this test passes successfully, I was forced to create a bug.

Comment by Oleksandr Byelkin [ 2022-12-01 ]

According to https://mariadb.com/kb/en/create-view/ the options can be used, if they will be taken is undefined.

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