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
|
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);
#
# The new non-standard keywords should still be useable as identifiers.
# The standard ones should not.
#
CREATE TABLE t ( of INT );
CREATE TABLE t0 ( skip INT, locked INT, nowait INT );
DROP TABLE t, t0;
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|
DROP PROCEDURE p0;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
#
# Test of the syntax.
#
#
# UPDATE ...
#
SELECT * FROM t1 FOR UPDATE;
a b
1 1
SELECT * FROM t1 FOR UPDATE NOWAIT;
a b
1 1
SELECT * FROM t1 FOR UPDATE SKIP LOCKED;
a b
1 1
#
# Dual locking clauses
#
SELECT * FROM t1 JOIN t2 FOR UPDATE;
a b a
1 1 1
SELECT * FROM t1 STRAIGHT_JOIN t2 FOR UPDATE;
a b a
1 1 1
#
# Derived tables and views.
#
SELECT 1 FROM ( SELECT 1 ) AS t2 FOR UPDATE;
1
1
SELECT 1 FROM v1 FOR UPDATE;
1
1
#
# Test of syntax errors.
#
SELECT * FROM t1 FOR SHARE WAIT WAIT;
ERROR 42000: 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 'SHARE WAIT WAIT' at line 1
SELECT * FROM t1 FOR SHARE WAIT NOWAIT;
ERROR 42000: 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 'SHARE WAIT NOWAIT' at line 1
SELECT * FROM t1 FOR SHARE WAIT SKIP LOCKED;
ERROR 42000: 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 'SHARE WAIT SKIP LOCKED' at line 1
SELECT * FROM t1 FOR SHARE WAIT 3 SKIP LOCKED;
ERROR 42000: 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 'SHARE WAIT 3 SKIP LOCKED' at line 1
SELECT * FROM t1 FOR SHARE NOWAIT SKIP LOCKED;
ERROR 42000: 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 'SHARE NOWAIT SKIP LOCKED' at line 1
SELECT 1 FOR UPDATE UNION SELECT 2;
ERROR 42000: 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 'UNION SELECT 2' at line 1
SELECT 1 LOCK IN SHARE MODE UNION SELECT 2;
ERROR 42000: 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 'UNION SELECT 2' at line 1
SELECT 1 FOR SHARE UNION SELECT 2;
ERROR 42000: 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 'SHARE UNION SELECT 2' at line 1
SELECT * FROM t1 LEFT JOIN t2 FOR UPDATE;
ERROR 42000: 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 'FOR UPDATE' at line 1
SELECT * FROM t1 LEFT JOIN t2 FOR SHARE;
ERROR 42000: 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 'FOR SHARE' at line 1
SELECT * FROM t1 LEFT JOIN t2 FOR SHARE OF t1 FOR UPDATE OF t2;
ERROR 42000: 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 'FOR SHARE OF t1 FOR UPDATE OF t2' at line 1
SELECT * FROM t1 RIGHT JOIN t2 FOR SHARE OF t1 FOR UPDATE OF t2;
ERROR 42000: 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 'FOR SHARE OF t1 FOR UPDATE OF t2' at line 1
#
# Test of error messages.
#
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;
MIN(a)
1
SELECT MAX(a) FROM t1 FOR UPDATE SKIP LOCKED;
MAX(a)
10
SELECT SUM(a) FROM t1 FOR UPDATE SKIP LOCKED;
SUM(a)
11
SELECT MIN(a) FROM t1 FOR UPDATE NOWAIT;
MIN(a)
1
SELECT MAX(a) FROM t1 FOR UPDATE NOWAIT;
MAX(a)
10
SELECT SUM(a) FROM t1 FOR UPDATE NOWAIT;
SUM(a)
11
SELECT DISTINCT * FROM t1 FOR UPDATE SKIP LOCKED;
a b
1 1
10 10
SELECT DISTINCT * FROM t1 FOR UPDATE NOWAIT;
a b
1 1
10 10
SELECT MIN(b) FROM t1 GROUP BY a FOR UPDATE SKIP LOCKED;
MIN(b)
1
10
#
# Regression testing.
#
DROP DATABASE db1;
DROP VIEW v1;
DROP TABLE t1, t2;
#
# Bug#25972285: UNCLEAR ERROR MESSAGE FOR NOWAIT
#
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 ;
a
connect con2,localhost,test2,,test;
BEGIN;
SELECT * FROM t1 FOR UPDATE NOWAIT;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
COMMIT;
connection default;
disconnect con1;
disconnect con2;
DROP TABLE t1;
DROP USER test@localhost;
DROP USER test2@localhost;
#
# Bug#30237291: "SELECT ... INTO VAR_NAME FOR UPDATE" NOT WORKING IN
# MYSQL 8
#
SELECT 1 FROM DUAL LIMIT 1 INTO @var FOR UPDATE;
Warnings:
Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
SELECT 1 FROM DUAL LIMIT 1 FOR UPDATE INTO @var;
Warnings:
Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
SELECT 1 FROM DUAL LIMIT 1 INTO @var FOR UPDATE INTO @var;
ERROR 42000: 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 'INTO @var' at line 1
SELECT 1 UNION SELECT 1 FOR UPDATE INTO @var;
Warnings:
Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
SELECT 1 UNION SELECT 1 INTO @var FOR UPDATE;
Warnings:
Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
|
