[MDEV-10063] VIEWs and subqueries with FOR UPDATE Created: 2016-05-13  Updated: 2023-04-14

Status: Open
Project: MariaDB Server
Component/s: Views
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: upstream

Attachments: File innodb-timeout-small.test    
Issue Links:
Relates
relates to MDEV-10035 DBUG_ASSERT on CREATE VIEW v1 AS SELE... Closed

 Description   

This is similar to MDEV-10035, but now FOR UPDATE is used inside a subquery:

I run this script:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES (10);
 
DROP VIEW IF EXISTS v1;
 
CREATE VIEW v1 AS SELECT (SELECT a FROM t1 FOR UPDATE) FROM t1;
 
START TRANSACTION;
 
SELECT * FROM v1;

Now I open a new session and run this script:

START TRANSACTION;
 
SELECT a FROM t1 FOR UPDATE;

It immediately returns a result:

+------+
 
| a    |
 
+------+
 
|   10 |
 
+------+

This is wrong. Subselects with FOR UPDATE should be disallowed in VIEWs.

 Comments   
Comment by Sergei Golubchik [ 2016-05-13 ]

I'm not sure they should be disallowed. I see this can be solved in one of the following ways:

  • disallow FOR UPDATE (and other clauses like LOCK IN SHARE MODE, SQL_BIG_RESULT, etc — everything that applies to a SELECT statetement) in subqueries, derived, and views
  • allow them, but ignore — possible, but it's a bad choice
  • allow them, issue a warning that this clause will only have an effect when the view/derived/subquery is executed into a temporary table
  • implicitly always switch to temporary table execution path when one of these clauses is present
  • like the above, but issue an error for views, requesting a user to specify TEMPTABLE explicitly
Comment by Elena Stepanova [ 2016-05-14 ]

Also reproducible with MySQL 5.6, 5.7.

Comment by Alice Sherepa [ 2020-09-22 ]

in MySQL 8.0 seems to be "allow them, but ignore"

 
 
mysql> CREATE VIEW v2 AS SELECT a FROM t1 FOR UPDATE;
 
Query OK, 0 rows affected (0.06 sec)
 
 
 
mysql> show create view v2;
 
+------+---------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
 
| View | Create View                                                                                                                                 | character_set_client | collation_connection |
 
+------+---------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
 
| v2   | CREATE ALGORITHM=UNDEFINED DEFINER=`skip-grants user`@`skip-grants host` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a` from `t1` | utf8mb4              | utf8mb4_0900_ai_ci   |
 
+------+---------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
 
1 row in set (0.00 sec)
 
 
 
mysql> select * from v2;
 
+------+
 
| a    |
 
+------+
 
|   10 |
 
+------+
 
1 row in set (0.00 sec)
 
 
 
mysql> SELECT * FROM v1;
 
+-------------------------------+
 
| (SELECT a FROM t1 FOR UPDATE) |
 
+-------------------------------+
 
|                            10 |
 
+-------------------------------+
 
1 row in set (0.00 sec)
 
 
 
mysql> show create view v1;
 
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
 
| View | Create View                                                                                                                                                                                | character_set_client | collation_connection |
 
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
 
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`skip-grants user`@`skip-grants host` SQL SECURITY DEFINER VIEW `v1` AS select (select `t1`.`a` from `t1`) AS `(SELECT a FROM t1 FOR UPDATE)` from `t1` | utf8mb4              | utf8mb4_0900_ai_ci   |
 
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
 
1 row in set (0.00 sec)
 
 
 
mysql> select version();
 
+-----------+
 
| version() |
 
+-----------+
 
| 8.0.21    |
 
+-----------+
 
1 row in set (0.00 sec)

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