MDEV-25433: SKIP LOCKED should imply NOWAITbb-10.6-danielblack-MDEV-25433-skip-locked-implies-no-wait

This also means SKIP LOCKED is compatible with the NOWAIT/
WAIT n syntax consisten with the MySQL-8.0 implementation.

5 files changed, 21 insertions, 24 deletions

diff --git a/mysql-test/main/locking_clause.result b/mysql-test/main/locking_clause.result
index 0307711ee8e..d2ebfe4799f 100644
--- a/mysql-test/main/locking_clause.result
+++ b/mysql-test/main/locking_clause.result
@@ -60,6 +60,10 @@ 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;
diff --git a/mysql-test/main/locking_clause.test b/mysql-test/main/locking_clause.test
index 407ac4e74f7..a27546a5cab 100644
--- a/mysql-test/main/locking_clause.test
+++ b/mysql-test/main/locking_clause.test
@@ -65,6 +65,10 @@ 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;
diff --git a/mysql-test/suite/innodb/r/create_table_insert_skip_locked.result b/mysql-test/suite/innodb/r/create_table_insert_skip_locked.result
index 65f61093e34..3f1b8e4ee55 100644
--- a/mysql-test/suite/innodb/r/create_table_insert_skip_locked.result
+++ b/mysql-test/suite/innodb/r/create_table_insert_skip_locked.result
@@ -21,13 +21,13 @@ SELECT * FROM s0;
 seat_id	state
 1	0
 2	0
-CREATE TEMPORARY TABLE s1 AS SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE NOWAIT SKIP LOCKED;
+CREATE TEMPORARY TABLE s1 AS SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
 SELECT * FROM s1;
 seat_id	state
 3	0
 4	0
 connection default;
-CREATE TEMPORARY TABLE s0 AS SELECT * FROM t1 WHERE state = 0 LOCK IN SHARE MODE NOWAIT SKIP LOCKED;
+CREATE TEMPORARY TABLE s0 AS SELECT * FROM t1 WHERE state = 0 LOCK IN SHARE MODE SKIP LOCKED;
 SELECT * FROM s0;
 seat_id	state
 1	0
@@ -52,13 +52,13 @@ seat_id	state
 1	0
 2	0
 CREATE TEMPORARY TABLE t2s LIKE t1;
-INSERT INTO t2s SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE NOWAIT SKIP LOCKED RETURNING seat_id, state;
+INSERT INTO t2s SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED RETURNING seat_id, state;
 seat_id	state
 3	0
 4	0
 connection default;
 CREATE TEMPORARY TABLE t2s LIKE t1;
-INSERT INTO t2s SELECT * FROM t1 WHERE state = 0 LOCK IN SHARE MODE NOWAIT SKIP LOCKED RETURNING seat_id, state;
+INSERT INTO t2s SELECT * FROM t1 WHERE state = 0 LOCK IN SHARE MODE SKIP LOCKED RETURNING seat_id, state;
 seat_id	state
 1	0
 2	0
diff --git a/mysql-test/suite/innodb/t/create_table_insert_skip_locked.test b/mysql-test/suite/innodb/t/create_table_insert_skip_locked.test
index e94c4eacd9b..e889883e9d7 100644
--- a/mysql-test/suite/innodb/t/create_table_insert_skip_locked.test
+++ b/mysql-test/suite/innodb/t/create_table_insert_skip_locked.test
@@ -27,11 +27,11 @@ BEGIN;
 CREATE TEMPORARY TABLE s0 AS SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;
 SELECT * FROM s0;
 
-CREATE TEMPORARY TABLE s1 AS SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE NOWAIT SKIP LOCKED;
+CREATE TEMPORARY TABLE s1 AS SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
 SELECT * FROM s1;
 
 connection default;
-CREATE TEMPORARY TABLE s0 AS SELECT * FROM t1 WHERE state = 0 LOCK IN SHARE MODE NOWAIT SKIP LOCKED;
+CREATE TEMPORARY TABLE s0 AS SELECT * FROM t1 WHERE state = 0 LOCK IN SHARE MODE SKIP LOCKED;
 SELECT * FROM s0;
 
 COMMIT;
@@ -56,11 +56,11 @@ BEGIN;
 INSERT INTO t2 SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE RETURNING seat_id, state;
 
 CREATE TEMPORARY TABLE t2s LIKE t1;
-INSERT INTO t2s SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE NOWAIT SKIP LOCKED RETURNING seat_id, state;
+INSERT INTO t2s SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED RETURNING seat_id, state;
 
 connection default;
 CREATE TEMPORARY TABLE t2s LIKE t1;
-INSERT INTO t2s SELECT * FROM t1 WHERE state = 0 LOCK IN SHARE MODE NOWAIT SKIP LOCKED RETURNING seat_id, state;
+INSERT INTO t2s SELECT * FROM t1 WHERE state = 0 LOCK IN SHARE MODE SKIP LOCKED RETURNING seat_id, state;
 COMMIT;
 DROP TABLE t2s;
 
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index be7df51ff42..06b02bc2b93 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1380,7 +1380,7 @@ End SQL_MODE_ORACLE_SPECIFIC */
         udf_type opt_local opt_no_write_to_binlog
         opt_temporary all_or_any opt_distinct opt_glimit_clause
         opt_ignore_leaves fulltext_options union_option
-        opt_not opt_skip_locked
+        opt_not
         transaction_access_mode_types
         opt_natural_language_mode opt_query_expansion
         opt_ev_status opt_ev_on_completion ev_on_completion opt_ev_comment
@@ -9132,33 +9132,22 @@ opt_select_lock_type:
         }
         ;
 
-opt_skip_locked:
-        /* empty */
-        {
-          $$= 0;
-        }
-        | SKIP_SYM LOCKED_SYM
-        {
-          Lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_SKIP_LOCKED);
-          $$= 1;
-        }
-
 opt_lock_wait_timeout_new:
         /* empty */
         {
           $$.empty();
         }
-        | WAIT_SYM ulong_num opt_skip_locked
+        | WAIT_SYM ulong_num
         {
+          $$.empty();
           $$.defined_timeout= TRUE;
           $$.timeout= $2;
-          $$.skip_locked= $3;
         }
-        | NOWAIT_SYM opt_skip_locked
+        | NOWAIT_SYM
         {
+          $$.empty();
           $$.defined_timeout= TRUE;
           $$.timeout= 0;
-          $$.skip_locked= $2;
         }
         | SKIP_SYM LOCKED_SYM
         {