1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
--source include/have_innodb.inc
CREATE TABLE t1 ( a INT, b INT );
INSERT INTO t1 VALUES (1, 1);
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE TABLE t2 ( a INT );
INSERT INTO t2 VALUES (1);
--echo #
--echo # The new non-standard keywords should still be useable as identifiers.
--echo # The standard ones should not.
--echo #
CREATE TABLE t ( of INT );
CREATE TABLE t0 ( skip INT, locked INT, nowait INT );
DROP TABLE t, t0;
delimiter |;
CREATE PROCEDURE p0() BEGIN of: LOOP LEAVE of; END LOOP of; END|
CREATE PROCEDURE p1() BEGIN skip: LOOP LEAVE skip; END LOOP skip; END|
CREATE PROCEDURE p2() BEGIN locked: LOOP LEAVE locked; END LOOP locked; END|
CREATE PROCEDURE p3() BEGIN nowait: LOOP LEAVE nowait; END LOOP nowait; END|
delimiter ;|
DROP PROCEDURE p0;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
--echo #
--echo # Test of the syntax.
--echo #
--echo #
--echo # UPDATE ...
--echo #
SELECT * FROM t1 FOR UPDATE;
SELECT * FROM t1 FOR UPDATE NOWAIT;
SELECT * FROM t1 FOR UPDATE SKIP LOCKED;
--echo #
--echo # Dual locking clauses
--echo #
SELECT * FROM t1 JOIN t2 FOR UPDATE;
SELECT * FROM t1 STRAIGHT_JOIN t2 FOR UPDATE;
--echo #
--echo # Derived tables and views.
--echo #
SELECT 1 FROM ( SELECT 1 ) AS t2 FOR UPDATE;
SELECT 1 FROM v1 FOR UPDATE;
--echo #
--echo # Test of syntax errors.
--echo #
--error ER_PARSE_ERROR
SELECT * FROM t1 FOR SHARE WAIT WAIT;
--error ER_PARSE_ERROR
SELECT * FROM t1 FOR SHARE WAIT NOWAIT;
--error ER_PARSE_ERROR
SELECT * FROM t1 FOR SHARE WAIT SKIP LOCKED;
--error ER_PARSE_ERROR
SELECT * FROM t1 FOR SHARE WAIT 3 SKIP LOCKED;
--error ER_PARSE_ERROR
SELECT * FROM t1 FOR SHARE NOWAIT SKIP LOCKED;
--error ER_PARSE_ERROR
SELECT 1 FOR UPDATE UNION SELECT 2;
--error ER_PARSE_ERROR
SELECT 1 LOCK IN SHARE MODE UNION SELECT 2;
--error ER_PARSE_ERROR
SELECT 1 FOR SHARE UNION SELECT 2;
--error ER_PARSE_ERROR
SELECT * FROM t1 LEFT JOIN t2 FOR UPDATE;
--error ER_PARSE_ERROR
SELECT * FROM t1 LEFT JOIN t2 FOR SHARE;
--error ER_PARSE_ERROR
SELECT * FROM t1 LEFT JOIN t2 FOR SHARE OF t1 FOR UPDATE OF t2;
--error ER_PARSE_ERROR
SELECT * FROM t1 RIGHT JOIN t2 FOR SHARE OF t1 FOR UPDATE OF t2;
--echo #
--echo # Test of error messages.
--echo #
CREATE DATABASE db1;
CREATE TABLE db1.t1 ( a INT, b INT );
INSERT INTO t1 VALUES (10, 10);
SELECT MIN(a) FROM t1 FOR UPDATE SKIP LOCKED;
SELECT MAX(a) FROM t1 FOR UPDATE SKIP LOCKED;
SELECT SUM(a) FROM t1 FOR UPDATE SKIP LOCKED;
SELECT MIN(a) FROM t1 FOR UPDATE NOWAIT;
SELECT MAX(a) FROM t1 FOR UPDATE NOWAIT;
SELECT SUM(a) FROM t1 FOR UPDATE NOWAIT;
SELECT DISTINCT * FROM t1 FOR UPDATE SKIP LOCKED;
SELECT DISTINCT * FROM t1 FOR UPDATE NOWAIT;
SELECT MIN(b) FROM t1 GROUP BY a FOR UPDATE SKIP LOCKED;
--echo #
--echo # Regression testing.
--echo #
DROP DATABASE db1;
DROP VIEW v1;
DROP TABLE t1, t2;
--echo #
--echo # Bug#25972285: UNCLEAR ERROR MESSAGE FOR NOWAIT
--echo #
--disable_view_protocol
CREATE USER test@localhost;
GRANT CREATE, SELECT, UPDATE on *.* to test@localhost;
CREATE USER test2@localhost;
GRANT CREATE, SELECT, UPDATE on *.* to test2@localhost;
CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
INSERT INTO t1 VALUES ( 1 );
connect (con1, localhost, test, , test);
BEGIN;
SELECT * FROM t1 WHERE a = 2 FOR UPDATE ;
connect (con2,localhost,test2,,test);
BEGIN;
--error ER_LOCK_WAIT_TIMEOUT
SELECT * FROM t1 FOR UPDATE NOWAIT;
COMMIT;
connection default;
disconnect con1;
disconnect con2;
DROP TABLE t1;
DROP USER test@localhost;
DROP USER test2@localhost;
--enable_view_protocol
--echo #
--echo # Bug#30237291: "SELECT ... INTO VAR_NAME FOR UPDATE" NOT WORKING IN
--echo # MYSQL 8
--echo #
--enable_prepare_warnings
SELECT 1 FROM DUAL LIMIT 1 INTO @var FOR UPDATE;
SELECT 1 FROM DUAL LIMIT 1 FOR UPDATE INTO @var;
# Double INTO should fail:
--error ER_PARSE_ERROR
SELECT 1 FROM DUAL LIMIT 1 INTO @var FOR UPDATE INTO @var;
SELECT 1 UNION SELECT 1 FOR UPDATE INTO @var;
SELECT 1 UNION SELECT 1 INTO @var FOR UPDATE;
--disable_prepare_warnings
|
