1 files changed, 210 insertions, 0 deletions
diff --git a/mysql-test/suite/perfschema/t/prepared_statements.test b/mysql-test/suite/perfschema/t/prepared_statements.test
new file mode 100644
index 00000000000..75d10238ac1
--- /dev/null
+++ b/mysql-test/suite/perfschema/t/prepared_statements.test
@@ -0,0 +1,210 @@
+# Test for prepared statement instrumentation 
+
+--source include/not_embedded.inc
+--source include/have_perfschema.inc
+--source include/no_protocol.inc
+
+CREATE DATABASE db;
+USE db;
+
+--let $psi_select =  SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances 
+--let $psi_truncate =  TRUNCATE TABLE performance_schema.prepared_statements_instances 
+--let $eshl_select = SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' 
+--let $eshl_truncate = TRUNCATE TABLE performance_schema.events_statements_history_long 
+
+--eval $psi_truncate
+--eval $eshl_truncate 
+
+--source suite/perfschema/include/prepared_stmts_setup.inc
+
+--vertical_results
+--eval $psi_select
+--eval $eshl_select
+--horizontal_results
+
+--source suite/perfschema/include/prepared_stmts_execution.inc 
+
+--vertical_results
+--eval $psi_select
+--eval $eshl_select
+--horizontal_results
+
+# Truncate to reset the statistics
+--eval $psi_truncate
+
+# check whether the statistics are reset
+--vertical_results
+--eval $psi_select
+--horizontal_results
+
+--source suite/perfschema/include/prepared_stmts_deallocation.inc
+
+--vertical_results
+# select query on prepared_statements_instances table must return empty set
+--eval $psi_select
+--eval $eshl_select
+--horizontal_results
+
+# truncate
+--eval $eshl_truncate
+
+#
+# Test to check the instrumentation of prepared statements 
+# when all consumers in setup_consumers are disabled.
+# 
+
+# Disable all consumers
+UPDATE performance_schema.setup_consumers SET ENABLED = 'NO';
+
+--source  suite/perfschema/include/prepared_stmts_setup.inc
+
+--vertical_results
+--eval $psi_select
+--eval $eshl_select
+--horizontal_results
+
+--source suite/perfschema/include/prepared_stmts_execution.inc
+
+--vertical_results
+--eval $psi_select
+--eval $eshl_select
+--horizontal_results
+
+--source suite/perfschema/include/prepared_stmts_deallocation.inc
+
+--vertical_results
+--eval $psi_select
+--eval $eshl_select
+--horizontal_results
+
+# truncate
+--eval $eshl_truncate
+
+# restore the initial set-up of consumers table
+UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
+
+#
+# Aggregation 
+#
+
+PREPARE st FROM 'SELECT SUM(1000 + ?) AS total';
+
+--vertical_results
+--eval $psi_select
+--horizontal_results
+
+SET @d=100;
+EXECUTE st USING @d;
+
+--vertical_results
+--eval $psi_select
+--horizontal_results
+
+let $i=5;
+while($i)
+{ 
+  SET @d = @d + 100;
+  EXECUTE st USING @d;
+  --vertical_results
+  --eval $psi_select
+  --horizontal_results
+  dec $i;  
+}
+
+# truncate 
+--eval $psi_truncate
+--vertical_results
+--eval $psi_select
+--horizontal_results
+
+#
+# check whether the instrumented prepared statement is removed from the 
+# prepared_statement_instances table after de allocation of the prepared
+# statement even if instrumentation is disabled
+#
+
+# Disable prepared statement instrumentation
+UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' 
+       WHERE NAME like "statement/sql/execute%";
+
+SET @d=3274;
+# COUNT_EXECUTE should remain 0. No increment must be seen.
+EXECUTE st USING @d;
+--vertical_results
+--eval $psi_select
+--horizontal_results
+
+UPDATE performance_schema.setup_instruments SET ENABLED = 'NO'
+       WHERE NAME like "statement/sql/prepare%";
+
+DEALLOCATE PREPARE st;
+# should return empty set.
+--vertical_results
+--eval $psi_select
+--horizontal_results
+
+# Restore back teh initial set-up of setup_instruments table 
+
+UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
+       WHERE NAME like "statement/sql/prepare%";
+UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' 
+       WHERE NAME like "statement/sql/execute%";
+
+
+# Reprepare
+
+CREATE TABLE tab(
+   Id   INT,
+   name CHAR(10),
+   age  INT
+);
+
+INSERT INTO tab VALUES(1,"Nakshatr",25),(2,"chanda",24),(3,"tejas",78);
+
+PREPARE st FROM 'SELECT * FROM tab';
+
+--vertical_results
+--eval $psi_select
+--horizontal_results
+
+EXECUTE st;
+
+--vertical_results
+--eval $psi_select
+--horizontal_results
+
+ALTER TABLE tab DROP COLUMN age;
+
+EXECUTE st;
+
+--vertical_results
+--eval $psi_select
+--horizontal_results
+
+ALTER TABLE tab ADD COLUMN age INT NULL;
+
+EXECUTE st;
+
+--vertical_results
+--eval $psi_select
+--horizontal_results
+
+# check if the statistics are reset 
+--eval $psi_truncate
+--vertical_results
+--eval $psi_select
+--horizontal_results
+
+DEALLOCATE PREPARE st;
+
+--vertical_results
+--eval $psi_select
+--horizontal_results
+
+# clean up
+# truncate
+--eval $psi_truncate 
+--eval $eshl_truncate
+
+DROP TABLE tab;
+DROP DATABASE db;