From 06eaf7232e9a920468c0f8d74dcf2fe8b555501c Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 13 Apr 2024 14:24:36 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- mysql-test/main/locking_clause.result | 164 ++++++++++++++++++++++++++++++++++ 1 file changed, 164 insertions(+) create mode 100644 mysql-test/main/locking_clause.result (limited to 'mysql-test/main/locking_clause.result') diff --git a/mysql-test/main/locking_clause.result b/mysql-test/main/locking_clause.result new file mode 100644 index 00000000..d2ebfe47 --- /dev/null +++ b/mysql-test/main/locking_clause.result @@ -0,0 +1,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 ' INTO FROM...' instead +SELECT 1 FROM DUAL LIMIT 1 FOR UPDATE INTO @var; +Warnings: +Warning 1287 ' INTO 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 ' INTO FROM...' instead +SELECT 1 UNION SELECT 1 INTO @var FOR UPDATE; +Warnings: +Warning 1287 ' INTO FROM...' instead -- cgit v1.2.3