--source include/count_sessions.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 #

--error ER_PARSE_ERROR
CREATE TABLE t ( of INT );
CREATE TABLE t0 ( skip INT, locked INT, nowait INT );
DROP TABLE t0;

delimiter |;

--error ER_PARSE_ERROR
CREATE PROCEDURE p() 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 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 #  SHARE ...
--echo #
SELECT * FROM t1 FOR SHARE;
SELECT * FROM t1 FOR SHARE NOWAIT;
SELECT * FROM t1 FOR SHARE SKIP LOCKED;

--echo #
--echo #  OF ...
--echo #
SELECT * FROM t1 FOR SHARE OF t1;
SELECT * FROM t1 FOR SHARE OF t1 NOWAIT;
SELECT * FROM t1 FOR SHARE OF t1 SKIP LOCKED;

SELECT * FROM t1 FOR UPDATE OF t1;
SELECT * FROM t1 FOR UPDATE OF t1 NOWAIT;
SELECT * FROM t1 FOR UPDATE OF t1 SKIP LOCKED;

--echo #
--echo #  OF ...
--echo #
SELECT * FROM t1, t2 FOR SHARE OF t1, t2;
SELECT * FROM t1, t2 FOR SHARE OF t1, t2 NOWAIT;
SELECT * FROM t1, t2 FOR SHARE OF t1, t2 SKIP LOCKED;

SELECT * FROM t1, t2 FOR UPDATE OF t1, t2;
SELECT * FROM t1, t2 FOR UPDATE OF t1, t2 NOWAIT;
SELECT * FROM t1, t2 FOR UPDATE OF t1, t2 SKIP LOCKED;

--echo #
--echo #  Dual locking clauses
--echo #
SELECT * FROM t1 JOIN t2 FOR UPDATE;
SELECT * FROM t1 JOIN t2 FOR SHARE;
SELECT * FROM t1 STRAIGHT_JOIN t2 FOR UPDATE;
SELECT * FROM t1 STRAIGHT_JOIN t2 AS t12 FOR UPDATE OF t12;
SELECT * FROM t1 STRAIGHT_JOIN t2 FOR SHARE;
SELECT * FROM t1 STRAIGHT_JOIN t2 FOR SHARE OF t1;
SELECT * FROM t1 STRAIGHT_JOIN t2 FOR SHARE OF t1 FOR UPDATE OF t2;
SELECT * FROM t1 STRAIGHT_JOIN t2 AS t12 FOR UPDATE OF t12, t1 ;
SELECT * FROM t1 JOIN t2 FOR SHARE OF t1 FOR UPDATE OF t2;
SELECT * FROM t1 NATURAL JOIN t2 FOR SHARE OF t1 FOR UPDATE OF t2;
SELECT * FROM t1, t2 FOR SHARE OF t1 FOR UPDATE OF t2;
SELECT * FROM t1, t2 FOR SHARE OF t1 FOR SHARE OF t2;
SELECT * FROM t1 FOR SHARE OF t1 NOWAIT;
SELECT * FROM t1 FOR SHARE OF t1 SKIP LOCKED;
SELECT * FROM t1, t2 FOR SHARE OF t1 NOWAIT FOR SHARE OF t2 NOWAIT;

--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 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 deprecation warnings.
--echo #

delimiter |;

CREATE PROCEDURE p1()
BEGIN
  DECLARE c CURSOR FOR SELECT a FROM t1 FOR UPDATE;
END|

CREATE PROCEDURE p2()
BEGIN
  DECLARE c CURSOR FOR SELECT a FROM t1 LOCK IN SHARE MODE;
END|

delimiter ;|

DROP PROCEDURE p1;
DROP PROCEDURE p2;

SELECT MIN(a) FROM t1 FOR UPDATE;
SELECT MAX(a) FROM t1 FOR UPDATE;
SELECT SUM(a) FROM t1 FOR UPDATE;
SELECT DISTINCT * FROM t1 FOR UPDATE;
SELECT MIN(b) FROM t1 GROUP BY a FOR UPDATE;

SELECT MIN(a) FROM t1 LOCK IN SHARE MODE;
SELECT MAX(a) FROM t1 LOCK IN SHARE MODE;
SELECT SUM(a) FROM t1 LOCK IN SHARE MODE;
SELECT DISTINCT * FROM t1 LOCK IN SHARE MODE;
SELECT MIN(b) FROM t1 GROUP BY a LOCK IN SHARE MODE;

SELECT 1 UNION SELECT 2 FOR UPDATE;
SELECT 1 UNION SELECT 2 LOCK IN SHARE MODE;


--echo #
--echo # Test of error messages.
--echo #

--error ER_UNRESOLVED_TABLE_LOCK
SELECT * FROM t1 FOR SHARE OF t2;
--error ER_UNRESOLVED_TABLE_LOCK
SELECT * FROM t1 t1a FOR SHARE OF t1;
--error ER_UNRESOLVED_TABLE_LOCK
SELECT * FROM t1 t1a, t2 t2a FOR SHARE OF t1a, t2;
--error ER_UNRESOLVED_TABLE_LOCK
SELECT * FROM t1 STRAIGHT JOIN t2 FOR SHARE OF t1;
--error ER_UNRESOLVED_TABLE_LOCK
SELECT * FROM t1 STRAIGHT JOIN t2 FOR SHARE OF t1 FOR UPDATE OF t2;
--error ER_UNRESOLVED_TABLE_LOCK
SELECT * FROM t1 STRAIGHT JOIN t2 AS t12 FOR UPDATE OF t12, t1 ;

--error ER_BAD_DB_ERROR
SELECT * FROM no_such_database.t1 FOR SHARE OF no_such_database.t1;
--error ER_UNRESOLVED_TABLE_LOCK
SELECT * FROM no_such_database.t1 t1a FOR SHARE OF no_such_database.t1;
--error ER_BAD_DB_ERROR
SELECT * FROM no_such_database.t1 t1a FOR SHARE OF t1a;
--error ER_BAD_DB_ERROR
SELECT * FROM no_such_database.t1 FOR SHARE OF t1;
--error ER_UNRESOLVED_TABLE_LOCK
SELECT * FROM t1 FOR SHARE OF no_such_database.t1;

CREATE DATABASE db1;
CREATE TABLE db1.t1 ( a INT, b INT );
INSERT INTO t1 VALUES (10, 10);

--error ER_UNRESOLVED_TABLE_LOCK
SELECT * FROM t1 FOR SHARE OF db1.t1;

--error ER_DUPLICATE_TABLE_LOCK
SELECT * FROM t1 FOR UPDATE OF t1 FOR SHARE OF t1;

--error ER_DUPLICATE_TABLE_LOCK
SELECT * FROM t1, t2 FOR UPDATE OF t2 FOR SHARE OF t1 FOR UPDATE OF t2;

--error ER_DUPLICATE_TABLE_LOCK
SELECT * FROM t1, t2 FOR UPDATE FOR SHARE OF t1;
--error ER_DUPLICATE_TABLE_LOCK
SELECT * FROM t1, t2 FOR UPDATE FOR SHARE OF t2;
--error ER_DUPLICATE_TABLE_LOCK
SELECT * FROM t1, t2 FOR SHARE FOR UPDATE OF t1;
--error ER_DUPLICATE_TABLE_LOCK
SELECT * FROM t1, t2 FOR SHARE FOR UPDATE OF t2;
--error ER_DUPLICATE_TABLE_LOCK
SELECT * FROM t1, t2 FOR UPDATE OF t1 FOR SHARE;
--error ER_DUPLICATE_TABLE_LOCK
SELECT * FROM t1, t2 FOR UPDATE OF t2 FOR SHARE;

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 OF t1;
SELECT MIN(b) FROM t1 GROUP BY a FOR UPDATE SKIP LOCKED;
SELECT MIN(b) FROM t1 GROUP BY a FOR SHARE;

--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 #
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_NOWAIT
SELECT * FROM t1 FOR UPDATE NOWAIT;

COMMIT;

connection default;
disconnect con1;
disconnect con2;

--source include/wait_until_count_sessions.inc

DROP TABLE t1;
DROP USER test@localhost;
DROP USER test2@localhost;

--echo #
--echo # Bug#30521098: LIMIT CLAUSE CAN SUPPRESS LOCKING CLAUSES
--echo #

CREATE USER user_1@localhost;
GRANT CREATE, SELECT, UPDATE on *.* to user_1@localhost;

CREATE USER user_2@localhost;
GRANT CREATE, SELECT, UPDATE on *.* to user_2@localhost;

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);

connect (con1, localhost, user_1, , test);

BEGIN;
(SELECT * FROM t1 FOR UPDATE) LIMIT 1;

connect (con2,localhost,user_2,,test);
BEGIN;

--error ER_LOCK_NOWAIT
SELECT * FROM t1 FOR UPDATE NOWAIT;

COMMIT;

connection default;
disconnect con1;
disconnect con2;

--source include/wait_until_count_sessions.inc

DROP TABLE t1;
DROP USER user_1@localhost;
DROP USER user_2@localhost;

--echo #
--echo # Bug#30237291: "SELECT ... INTO VAR_NAME FOR UPDATE" NOT WORKING IN
--echo #               MYSQL 8
--echo #

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;

--echo #
--echo # Bug #32705614 ERROR LOG REPORTS
--echo #     `GOT ERROR 203 WHEN READING TABLE` FREQUENTLY
--echo #

CREATE TABLE tt (id INT PRIMARY KEY, age INT);
INSERT INTO tt VALUES (1,1),(2,2);

BEGIN;
SELECT * FROM tt WHERE id = 2 FOR UPDATE;

connect (con1, localhost, root);
connection con1;

--error ER_LOCK_NOWAIT
SELECT * FROM tt WHERE id = 2 FOR UPDATE NOWAIT;
--error ER_LOCK_NOWAIT
SELECT * FROM tt WHERE id = 2 FOR UPDATE NOWAIT;
--error ER_LOCK_NOWAIT
SELECT * FROM tt WHERE id = 2 FOR UPDATE NOWAIT;

--echo # connection default
connection default;
disconnect con1;
--source include/wait_until_count_sessions.inc

DROP TABLE tt;