1 files changed, 170 insertions, 0 deletions
diff --git a/mysql-test/suite/perfschema/t/selects.test b/mysql-test/suite/perfschema/t/selects.test
new file mode 100644
index 00000000000..eb8d8f7ed31
--- /dev/null
+++ b/mysql-test/suite/perfschema/t/selects.test
@@ -0,0 +1,170 @@
+--source include/not_embedded.inc
+--source include/have_perfschema.inc
+
+#
+# WL#4814, 4.1.2 STORAGE ENGINE, FSE8: Selects
+#
+
+# Make some data that we can work on:
+
+UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
+ENGINE=MyISAM;
+INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+
+# ORDER BY, GROUP BY and HAVING
+
+--replace_column 2 [NUM_BYTES]
+SELECT OPERATION, SUM(NUMBER_OF_BYTES) AS TOTAL
+FROM performance_schema.events_waits_history_long
+GROUP BY OPERATION
+HAVING TOTAL IS NOT NULL
+ORDER BY OPERATION
+LIMIT 1;
+
+# Sub SELECT
+--replace_column 1 [EVENT_ID]
+SELECT EVENT_ID FROM performance_schema.events_waits_current
+WHERE THREAD_ID IN
+  (SELECT THREAD_ID FROM performance_schema.threads)
+AND EVENT_NAME IN
+  (SELECT NAME FROM performance_schema.setup_instruments
+   WHERE NAME LIKE "wait/synch/%")
+LIMIT 1;
+
+# JOIN
+
+--replace_column 1 [EVENT_ID]
+SELECT DISTINCT EVENT_ID
+FROM performance_schema.events_waits_current
+JOIN performance_schema.events_waits_history USING (EVENT_ID)
+JOIN performance_schema.events_waits_history_long USING (EVENT_ID)
+ORDER BY EVENT_ID
+LIMIT 1;
+
+# Self JOIN
+
+--replace_column 1 [THREAD_ID] 2 [EVENT_ID] 3 [EVENT_NAME] 4 [TIMER_WAIT]
+SELECT t1.THREAD_ID, t2.EVENT_ID, t3.EVENT_NAME, t4.TIMER_WAIT
+FROM performance_schema.events_waits_history t1
+JOIN performance_schema.events_waits_history t2 USING (EVENT_ID)
+JOIN performance_schema.events_waits_history t3 ON (t2.THREAD_ID = t3.THREAD_ID)
+JOIN performance_schema.events_waits_history t4 ON (t3.EVENT_NAME = t4.EVENT_NAME)
+ORDER BY t1.EVENT_ID, t2.EVENT_ID
+LIMIT 5;
+
+# UNION
+--replace_column 1 [THREAD_ID] 2 [EVENT_ID]
+SELECT THREAD_ID, EVENT_ID FROM (
+SELECT THREAD_ID, EVENT_ID FROM performance_schema.events_waits_current
+UNION
+SELECT THREAD_ID, EVENT_ID FROM performance_schema.events_waits_history
+UNION
+SELECT THREAD_ID, EVENT_ID FROM performance_schema.events_waits_history_long
+) t1 ORDER BY THREAD_ID, EVENT_ID
+LIMIT 5;
+
+# EVENT
+
+# Check that the event_scheduler is really running
+--source include/running_event_scheduler.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS t_event;
+DROP EVENT IF EXISTS t_ps_event;
+--enable_warnings
+CREATE TABLE t_event AS
+SELECT EVENT_ID FROM performance_schema.events_waits_current
+WHERE 1 = 2;
+CREATE EVENT t_ps_event
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
+ON COMPLETION PRESERVE
+DO INSERT INTO t_event
+   SELECT DISTINCT EVENT_ID
+   FROM performance_schema.events_waits_current
+   JOIN performance_schema.events_waits_history USING (EVENT_ID)
+   ORDER BY EVENT_ID
+   LIMIT 1;
+
+# TRIGGER
+
+ALTER TABLE t1 ADD COLUMN c INT;
+
+--disable_warnings
+DROP TRIGGER IF EXISTS t_ps_trigger;
+--enable_warnings
+delimiter |;
+
+CREATE TRIGGER t_ps_trigger BEFORE INSERT ON t1
+  FOR EACH ROW BEGIN
+     SET NEW.c = (SELECT MAX(EVENT_ID)
+                  FROM performance_schema.events_waits_current);
+  END;
+|
+
+delimiter ;|
+
+INSERT INTO t1 (id) VALUES (11), (12), (13);
+
+--replace_column 2 [EVENT_ID]
+SELECT id, c FROM t1 WHERE id > 10 ORDER BY c;
+
+DROP TRIGGER t_ps_trigger;
+
+# PROCEDURE
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS t_ps_proc;
+--enable_warnings
+delimiter |;
+
+CREATE PROCEDURE t_ps_proc(IN conid INT, OUT pid INT)
+BEGIN
+   SELECT thread_id FROM performance_schema.threads
+   WHERE PROCESSLIST_ID = conid INTO pid;
+END;
+
+|
+
+delimiter ;|
+
+CALL t_ps_proc(connection_id(), @p_id);
+
+# FUNCTION
+
+--disable_warnings
+DROP FUNCTION IF EXISTS t_ps_proc;
+--enable_warnings
+delimiter |;
+
+CREATE FUNCTION t_ps_func(conid INT) RETURNS int
+BEGIN
+   return (SELECT thread_id FROM performance_schema.threads
+           WHERE PROCESSLIST_ID = conid);
+END;
+
+|
+
+delimiter ;|
+
+SELECT t_ps_func(connection_id()) = @p_id;
+
+# We might reach this point too early which means the event scheduler has not
+# executed our "t_ps_event". Therefore we poll till the record was inserted
+# and run our test statement afterwards.
+let $wait_timeout= 20;
+let $wait_condition= SELECT COUNT(*) = 1 FROM t_event;
+--source include/wait_condition.inc
+--replace_column 1 [EVENT_ID]
+SELECT * FROM t_event;
+
+# Clean up
+DROP PROCEDURE t_ps_proc;
+DROP FUNCTION t_ps_func;
+DROP EVENT t_ps_event;
+DROP TABLE t1;
+DROP TABLE t_event;