1 files changed, 162 insertions, 0 deletions
diff --git a/mysql-test/suite/storage_engine/delete_low_prio.test b/mysql-test/suite/storage_engine/delete_low_prio.test
new file mode 100644
index 00000000000..34677df5373
--- /dev/null
+++ b/mysql-test/suite/storage_engine/delete_low_prio.test
@@ -0,0 +1,162 @@
+# 
+# DELETE LOW_PRIORITY
+#
+--source have_engine.inc
+
+--source include/count_sessions.inc
+
+--enable_connect_log
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+--let $create_definiiton = a $int_col, b $char_col
+--source create_table.inc
+INSERT INTO t1 (a,b) VALUES (1,'f'),(2,'b');
+
+# We will have 3 connections: 
+#   con1 will start SELECT which should give us enough time;
+#   default will run DELETE 
+#   con2 will then start another SELECT.
+# With LOW_PRIORITY_UPDATES = 0,
+# with standard DELETE we should see all rows in con1 resultset,
+# but no rows in con2 resultset.
+# With DELETE LOW_PRIORITY we should see all rows in both resultsets.
+# Then we will set LOW_PRIORITY_UPDATES to 1.
+# Then with standard DELETE we should see all rows in both resultsets.
+
+SET LOW_PRIORITY_UPDATES = 0;
+SET lock_wait_timeout = 4;
+
+# Normal DELETE with low_priority_updates=0
+
+--connect (con1,localhost,root,,)
+SET lock_wait_timeout = 2;
+--send
+SELECT SLEEP(1) FROM t1;
+
+--connection default
+let $show_statement = SHOW PROCESSLIST;
+let $field = State;
+let $condition = = 'User sleep';
+# We don't need to wait long, 
+# if the feature works, threads
+# should show up in the processlist right away
+let $wait_timeout = 2;
+--source include/wait_show_condition.inc
+
+--send
+DELETE FROM t1;
+
+--connect (con2,localhost,root,,)
+SET lock_wait_timeout = 3;
+
+let $field = Info;
+let $condition =  = 'DELETE FROM t1';
+let $wait_timeout = 2;
+--source include/wait_show_condition.inc
+if (!$found)
+{
+  --let $mysql_errname = timeout in wait_show_condition.inc
+  --let $functionality = DELETE or table locking
+  --source unexpected_result.inc
+}
+if ($found)
+{
+  SELECT SLEEP(1) FROM t1;
+}
+
+--connection con1
+--reap
+
+--connection default
+--reap
+--sorted_result
+SELECT * FROM t1;
+INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b');
+
+
+# DELETE LOW_PRIORITY
+
+--connection con1
+--send
+SELECT SLEEP(1) FROM t1;
+
+--connection default
+let $field = State;
+let $condition = = 'User sleep';
+let $wait_timeout = 2;
+--source include/wait_show_condition.inc
+--send
+DELETE LOW_PRIORITY FROM t1;
+
+--connection con2
+let $field = Info;
+let $condition =  = 'DELETE LOW_PRIORITY FROM t1';
+let $wait_timeout = 2;
+--source include/wait_show_condition.inc
+if (!$found)
+{
+  --let $mysql_errname = timeout in wait_show_condition.inc
+  --let $functionality = DELETE LOW_PRIORITY or table locking
+  --source unexpected_result.inc
+}
+if ($found)
+{
+  SELECT SLEEP(1) FROM t1;
+}
+
+--connection con1
+--reap
+
+--connection default
+--reap
+--sorted_result
+SELECT * FROM t1;
+INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b');
+
+SET LOW_PRIORITY_UPDATES = 1;
+
+# Normal DELETE with low_priority_updates=1
+
+--connection con1
+--send
+SELECT SLEEP(1) FROM t1;
+
+--connection default
+let $field = State;
+let $condition = = 'User sleep';
+let $wait_timeout = 2;
+--source include/wait_show_condition.inc
+--send
+DELETE FROM t1;
+
+--connection con2
+let $field = Info;
+let $condition =  = 'DELETE FROM t1';
+let $wait_timeout = 2;
+--source include/wait_show_condition.inc
+if ($found)
+{
+  SELECT SLEEP(1) FROM t1;
+}
+
+--connection con1
+--reap
+
+--connection default
+--reap
+--sorted_result
+SELECT * FROM t1;
+
+--disconnect con1
+--disconnect con2
+
+# Cleanup
+DROP TABLE t1;
+
+--source include/wait_until_count_sessions.inc
+
+--source cleanup_engine.inc
+