path: root/mysql-test/suite/perfschema/r/show_sanity.result

create table test.sanity(
`SHOW_MODE` varchar(48) NOT NULL,
`SOURCE` varchar(64) NOT NULL,
`VARIABLE_NAME` varchar(64) NOT NULL);
select thread_id from performance_schema.threads
where processlist_id = connection_id()
into @my_tid;
select @@global.show_compatibility_56 into @show_compatibility_56_save;
set @my_user='root';
set @my_host='localhost';

================================================================================
SHOW_COMPATIBILITY_56 = ON
================================================================================
set global show_compatibility_56 = on;

# STEP 1-1
insert into test.sanity
select "5.6", "I_S.GLOBAL_VARIABLES", variable_name
from information_schema.global_variables;
Warnings:
Warning	1287	'INFORMATION_SCHEMA.GLOBAL_VARIABLES' is deprecated and will be removed in a future release. Please use performance_schema.global_variables instead

# STEP 1-2
insert into test.sanity
select "5.6", "I_S.SESSION_VARIABLES", variable_name
from information_schema.session_variables;
Warnings:
Warning	1287	'INFORMATION_SCHEMA.SESSION_VARIABLES' is deprecated and will be removed in a future release. Please use performance_schema.session_variables instead

# STEP 1-3
insert into test.sanity
select "5.6", "P_S.GLOBAL_VARIABLES", variable_name
from performance_schema.global_variables;

# STEP 1-4
insert into test.sanity
select "5.6", "P_S.VARIABLES_BY_THREAD", variable_name
from performance_schema.variables_by_thread
where thread_id = @my_tid;

# STEP 1-5
insert into test.sanity
select "5.6", "P_S.SESSION_VARIABLES", variable_name
from performance_schema.session_variables;

# STEP 1-6
insert into test.sanity
select "5.6", "I_S.GLOBAL_STATUS", variable_name
from information_schema.global_status;
Warnings:
Warning	1287	'INFORMATION_SCHEMA.GLOBAL_STATUS' is deprecated and will be removed in a future release. Please use performance_schema.global_status instead

# STEP 1-7
insert into test.sanity
select "5.6", "I_S.SESSION_STATUS", variable_name
from information_schema.session_status;
Warnings:
Warning	1287	'INFORMATION_SCHEMA.SESSION_STATUS' is deprecated and will be removed in a future release. Please use performance_schema.session_status instead

# STEP 1-8
insert into test.sanity
select "5.6", "P_S.GLOBAL_STATUS", variable_name
from performance_schema.global_status;

# STEP 1-9
# EMPTY, NO WARNINGS (not available in 5.6 mode)
select *
from performance_schema.status_by_thread;
THREAD_ID	VARIABLE_NAME	VARIABLE_VALUE

# STEP 1-10
insert into test.sanity
select "5.6", "P_S.SESSION_STATUS", variable_name
from performance_schema.session_status;

# STEP 1-11
# EMPTY, NO WARNINGS (not available in 5.6 mode)
select *
from performance_schema.status_by_account;
USER	HOST	VARIABLE_NAME	VARIABLE_VALUE

# STEP 1-12
# EMPTY, NO WARNINGS (not available in 5.6 mode)
select *
from performance_schema.status_by_user;
USER	VARIABLE_NAME	VARIABLE_VALUE

# STEP 1-13
# EMPTY, NO WARNINGS (not available in 5.6 mode)
select *
from performance_schema.status_by_host;
HOST	VARIABLE_NAME	VARIABLE_VALUE

================================================================================
SHOW_COMPATIBILITY_56 = OFF
================================================================================
set global show_compatibility_56 = off;

# STEP 2-1
# FAILS WITH ERROR
select *
from information_schema.global_variables;
ERROR HY000: The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'

# STEP 2-2
# FAILS WITH ERROR
select *
from information_schema.session_variables;
ERROR HY000: The 'INFORMATION_SCHEMA.SESSION_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'

# STEP 2-3
insert into test.sanity
select "5.7", "P_S.GLOBAL_VARIABLES", variable_name
from performance_schema.global_variables;

# STEP 2-4
insert into test.sanity
select "5.7", "P_S.VARIABLES_BY_THREAD", variable_name
from performance_schema.variables_by_thread
where thread_id = @my_tid;

# STEP 2-5
insert into test.sanity
select "5.7", "P_S.SESSION_VARIABLES", variable_name
from performance_schema.session_variables;

# STEP 2-6
# FAILS WITH ERROR
select *
from information_schema.global_status;
ERROR HY000: The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'

# STEP 2-7
# FAILS WITH ERROR
select *
from information_schema.session_status;
ERROR HY000: The 'INFORMATION_SCHEMA.SESSION_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'

# STEP 2-8
insert into test.sanity
select "5.7", "P_S.GLOBAL_STATUS", variable_name
from performance_schema.global_status;

# STEP 2-9
insert into test.sanity
select "5.7", "P_S.STATUS_BY_THREAD", variable_name
from performance_schema.status_by_thread
where thread_id = @my_tid;

# STEP 2-10
insert into test.sanity
select "5.7", "P_S.SESSION_STATUS", variable_name
from performance_schema.session_status;

# STEP 2-11
insert into test.sanity
select "5.7", "P_S.STATUS_BY_ACCOUNT", variable_name
from performance_schema.status_by_account
where `user` = @my_user and `host` = @my_host;

# STEP 2-12
insert into test.sanity
select "5.7", "P_S.STATUS_BY_USER", variable_name
from performance_schema.status_by_user
where `user` = @my_user;

# STEP 2-13
insert into test.sanity
select "5.7", "P_S.STATUS_BY_HOST", variable_name
from performance_schema.status_by_host
where `host` = @my_host;

# STEP 2-14
# Known global variables in 5.6 that should not display in session queries.
# These should be global-only system variables.

# STEP 2-15
# Known global status in 5.6 that should not exist in session queries.
# These should be global-only status variables.

# STEP 2-15A
# COM counters appear in I_S.SESSION_STATUS, but not in P_S.SESSION_STATUS

# STEP 2-15B
# These slave status counters appear in I_S.SESSION_STATUS, but not in P_S.SESSION_STATUS
insert into test.sanity values
("JUNK: SLAVE-ONLY", "I_S.SESSION_STATUS", "SLAVE_HEARTBEAT_PERIOD"),
("JUNK: SLAVE-ONLY", "I_S.SESSION_STATUS", "SLAVE_LAST_HEARTBEAT"),
("JUNK: SLAVE-ONLY", "I_S.SESSION_STATUS", "SLAVE_RECEIVED_HEARTBEATS"),
("JUNK: SLAVE-ONLY", "I_S.SESSION_STATUS", "SLAVE_RETRIED_TRANSACTIONS"),
("JUNK: SLAVE-ONLY", "I_S.SESSION_STATUS", "SLAVE_RUNNING")
;

# STEP 2-16
# Known session-only status in 5.6 that should not exist in global queries.
insert into test.sanity values
("JUNK: SESSION-ONLY", "I_S.GLOBAL_STATUS", "COMPRESSION"),
("JUNK: SESSION-ONLY", "I_S.GLOBAL_STATUS", "LAST_QUERY_COST"),
("JUNK: SESSION-ONLY", "I_S.GLOBAL_STATUS", "LAST_QUERY_PARTIAL_PLANS")
;

# STEP 2-16A
# COM counters appear in I_S.GLOBAL_STATUS but not in P_S.GLOBAL_STATUS

# STEP 2-16B
# These slave status counters appear in I_S.GLOBAL_STATUS, but not in P_S.GLOBAL_STATUS
insert into test.sanity values
("JUNK: SLAVE-ONLY", "I_S.GLOBAL_STATUS", "SLAVE_HEARTBEAT_PERIOD"),
("JUNK: SLAVE-ONLY", "I_S.GLOBAL_STATUS", "SLAVE_LAST_HEARTBEAT"),
("JUNK: SLAVE-ONLY", "I_S.GLOBAL_STATUS", "SLAVE_RECEIVED_HEARTBEATS"),
("JUNK: SLAVE-ONLY", "I_S.GLOBAL_STATUS", "SLAVE_RETRIED_TRANSACTIONS"),
("JUNK: SLAVE-ONLY", "I_S.GLOBAL_STATUS", "SLAVE_RUNNING")
;

# STEP 2-17
# Status variables that should not exist in queries that aggregate across threads.
# Criteria: Session-only OR non-aggregatable.
insert into test.sanity values
("JUNK", "NO AGGREGATE", "COMPRESSION"),
("JUNK", "NO AGGREGATE", "LAST_QUERY_COST"),
("JUNK", "NO AGGREGATE", "LAST_QUERY_PARTIAL_PLANS"),
("JUNK", "NO AGGREGATE", "OPEN_TABLES"),
("JUNK", "NO AGGREGATE", "QUERIES"),
("JUNK", "NO AGGREGATE", "SLOW_LAUNCH_THREADS"),
("JUNK", "NO AGGREGATE", "SSL_CIPHER"),
("JUNK", "NO AGGREGATE", "SSL_CIPHER_LIST"),
("JUNK", "NO AGGREGATE", "SSL_DEFAULT_TIMEOUT"),
("JUNK", "NO AGGREGATE", "SSL_SERVER_NOT_AFTER"),
("JUNK", "NO AGGREGATE", "SSL_SERVER_NOT_BEFORE"),
("JUNK", "NO AGGREGATE", "SSL_SESSIONS_REUSED"),
("JUNK", "NO AGGREGATE", "SSL_VERIFY_DEPTH"),
("JUNK", "NO AGGREGATE", "SSL_VERIFY_MODE"),
("JUNK", "NO AGGREGATE", "SSL_VERSION")
;

================================================================================
TEST 1A
Present in 5.6 P_S.GLOBAL_VARIABLES (1-3)
Missing in 5.6 I_S.GLOBAL_VARIABLES (1-1)
================================================================================
select * from test.sanity
where show_mode = "5.6"
  and source = "P_S.GLOBAL_VARIABLES"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.6"
    and source = "I_S.GLOBAL_VARIABLES")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 1B
Present in 5.7 P_S.GLOBAL_VARIABLES (2-3)
Missing in 5.6 I_S.GLOBAL_VARIABLES (1-1)
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.GLOBAL_VARIABLES"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.6"
    and source = "I_S.GLOBAL_VARIABLES")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 2A
Present in 5.6 I_S.GLOBAL_VARIABLES (1-1)
Missing in 5.6 P_S.GLOBAL_VARIABLES (1-3)
================================================================================
select * from test.sanity
where show_mode = "5.6"
  and source = "I_S.GLOBAL_VARIABLES"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.6"
    and source = "P_S.GLOBAL_VARIABLES")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME
5.6	I_S.GLOBAL_VARIABLES	SQL_LOG_BIN

================================================================================
TEST 2B
Present in 5.6 I_S.GLOBAL_VARIABLES (1-1)
Missing in 5.7 P_S.GLOBAL_VARIABLES (2-3)
================================================================================
select * from test.sanity
where show_mode = "5.6"
  and source = "I_S.GLOBAL_VARIABLES"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.GLOBAL_VARIABLES")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME
5.6	I_S.GLOBAL_VARIABLES	SQL_LOG_BIN

================================================================================
TEST 3A
Present in 5.6 P_S.SESSION_VARIABLES (1-5)
Missing in 5.6 I_S.SESSION_VARIABLES (1-2)
================================================================================
select * from test.sanity
where show_mode = "5.6"
  and source = "P_S.SESSION_VARIABLES"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.6"
    and source = "I_S.SESSION_VARIABLES")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 3B
Present in 5.7 P_S.SESSION_VARIABLES (2-5)
Missing in 5.6 I_S.SESSION_VARIABLES (1-2)
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.SESSION_VARIABLES"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.6"
    and source = "I_S.SESSION_VARIABLES")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 4A
Present in 5.6 I_S.SESSION_VARIABLES (1-2)
Missing in 5.6 P_S.SESSION_VARIABLES (1-5)
================================================================================
select * from test.sanity
where show_mode = "5.6"
  and source = "I_S.SESSION_VARIABLES"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.6"
    and source = "P_S.SESSION_VARIABLES")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME
5.6	I_S.SESSION_VARIABLES	GTID_EXECUTED

================================================================================
TEST 4B
Present in 5.6 I_S.SESSION_VARIABLES (1-2)
Missing in 5.7 P_S.SESSION_VARIABLES (2-5)
================================================================================
select * from test.sanity
where show_mode = "5.6"
  and source = "I_S.SESSION_VARIABLES"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.SESSION_VARIABLES")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME
5.6	I_S.SESSION_VARIABLES	GTID_EXECUTED

================================================================================
TEST 5A
Present in 5.6 P_S.VARIABLES_BY_THREAD (1-4)
Missing in 5.6 I_S.SESSION_VARIABLES (1-2)
================================================================================
select * from test.sanity
where show_mode = "5.6"
  and source = "P_S.VARIABLES_BY_THREAD"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.6"
    and source = "I_S.SESSION_VARIABLES")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 5B
Present in 5.7 P_S.VARIABLES_BY_THREAD (2-4)
Missing in 5.6 I_S.SESSION_VARIABLES (1-2)
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.VARIABLES_BY_THREAD"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.6"
    and source = "I_S.SESSION_VARIABLES")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 5C
Present in 5.6 I_S.SESSION_VARIABLES (1-2)
Missing in 5.6 P_S.VARIABLES_BY_THREAD (1-4)
EXPECT NOTHING BESIDE KNOWN GLOBAL VARIABLES (2-14)
================================================================================
select * from test.sanity
where show_mode = "5.6"
  and source = "I_S.SESSION_VARIABLES"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.6"
    and source = "P_S.VARIABLES_BY_THREAD")
and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "JUNK: GLOBAL-ONLY"
    and source = "I_S.SESSION_VARIABLES")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME
5.6	I_S.SESSION_VARIABLES	BINLOG_TRANSACTION_DEPENDENCY_HISTORY_SIZE
5.6	I_S.SESSION_VARIABLES	BINLOG_TRANSACTION_DEPENDENCY_TRACKING
5.6	I_S.SESSION_VARIABLES	GTID_EXECUTED
5.6	I_S.SESSION_VARIABLES	INNODB_DEADLOCK_DETECT
5.6	I_S.SESSION_VARIABLES	INNODB_STATS_INCLUDE_DELETE_MARKED
5.6	I_S.SESSION_VARIABLES	KEYRING_OPERATIONS
5.6	I_S.SESSION_VARIABLES	LOG_STATEMENTS_UNSAFE_FOR_BINLOG
5.6	I_S.SESSION_VARIABLES	PERFORMANCE_SCHEMA_SHOW_PROCESSLIST
5.6	I_S.SESSION_VARIABLES	REPLICATION_OPTIMIZE_FOR_STATIC_PLUGIN_CONFIG
5.6	I_S.SESSION_VARIABLES	REPLICATION_SENDER_OBSERVE_COMMIT_ONLY
5.6	I_S.SESSION_VARIABLES	TLS_VERSION

================================================================================
TEST 5D
Present in 5.6 I_S.SESSION_VARIABLES (1-2)
Missing in 5.7 P_S.VARIABLES_BY_THREAD (2-5)
EXPECT NOTHING BESIDE KNOWN GLOBAL VARIABLES (2-14)
================================================================================
select * from test.sanity
where show_mode = "5.6"
  and source = "I_S.SESSION_VARIABLES"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.VARIABLES_BY_THREAD")
and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "JUNK: GLOBAL-ONLY"
    and source = "I_S.SESSION_VARIABLES")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME
5.6	I_S.SESSION_VARIABLES	BINLOG_TRANSACTION_DEPENDENCY_HISTORY_SIZE
5.6	I_S.SESSION_VARIABLES	BINLOG_TRANSACTION_DEPENDENCY_TRACKING
5.6	I_S.SESSION_VARIABLES	GTID_EXECUTED
5.6	I_S.SESSION_VARIABLES	INNODB_DEADLOCK_DETECT
5.6	I_S.SESSION_VARIABLES	INNODB_STATS_INCLUDE_DELETE_MARKED
5.6	I_S.SESSION_VARIABLES	KEYRING_OPERATIONS
5.6	I_S.SESSION_VARIABLES	LOG_STATEMENTS_UNSAFE_FOR_BINLOG
5.6	I_S.SESSION_VARIABLES	PERFORMANCE_SCHEMA_SHOW_PROCESSLIST
5.6	I_S.SESSION_VARIABLES	REPLICATION_OPTIMIZE_FOR_STATIC_PLUGIN_CONFIG
5.6	I_S.SESSION_VARIABLES	REPLICATION_SENDER_OBSERVE_COMMIT_ONLY
5.6	I_S.SESSION_VARIABLES	TLS_VERSION

================================================================================
TEST 5E
KNOWN GLOBAL VARIABLES (2-14)
Expect missing in 5.6 P_S.VARIABLES_BY_THREAD (1-4)
================================================================================
select * from test.sanity
where show_mode = "JUNK: GLOBAL-ONLY"
  and source = "I_S.SESSION_VARIABLES"
  and upper(variable_name) in (
select upper(variable_name) from test.sanity
where show_mode = "5.6"
    and source = "P_S.VARIABLES_BY_THREAD")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 5F
KNOWN GLOBAL VARIABLES (2-14)
Expect missing in 5.7 P_S.VARIABLES_BY_THREAD (2-5)
================================================================================
select * from test.sanity
where show_mode = "JUNK: GLOBAL-ONLY"
  and source = "I_S.SESSION_VARIABLES"
  and upper(variable_name) in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.VARIABLES_BY_THREAD")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 6A
Present in 5.7 P_S.GLOBAL_STATUS (2-8)
Missing in 5.6 I_S.GLOBAL_STATUS (1-6)
EXPECT NOTHING
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.GLOBAL_STATUS"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.6"
    and source = "I_S.GLOBAL_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 6B
Present in 5.7 P_S.GLOBAL_STATUS (2-8)
Missing in 5.6 P_S.GLOBAL_STATUS (1-8)
EXPECT NOTHING
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.GLOBAL_STATUS"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.6"
    and source = "P_S.GLOBAL_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 6C
Present in 5.6 P_S.GLOBAL_STATUS (1-8)
Missing in 5.7 P_S.GLOBAL_STATUS (2-8)
EXPECT NOTHING
================================================================================
select * from test.sanity
where show_mode = "5.6"
  and source = "P_S.GLOBAL_STATUS"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.GLOBAL_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 7
Present in 5.6 I_S.GLOBAL_STATUS (1-6)
Missing in 5.7 P_S.GLOBAL_STATUS (2-8)
EXPECT NOTHING AFTER REMOVING KNOWN SESSION-ONLY, COM COUNTERS AND SLAVE-ONLY (2-16 & 2-16A & 2-16B)
================================================================================
select * from test.sanity
where show_mode = "5.6"
  and source = "I_S.GLOBAL_STATUS"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where (show_mode = "JUNK: SESSION-ONLY" or show_mode = "JUNK: COM COUNTER" or show_mode = "JUNK: SLAVE-ONLY")
and source = "I_S.GLOBAL_STATUS")
and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.GLOBAL_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME
5.6	I_S.GLOBAL_STATUS	COM_SHUTDOWN

================================================================================
TEST 8
KNOWN SESSION-ONLY (2-16)
KNOWN COM COUNTERS (2-16A)
KNOWN SLAVE-ONLY (2-16B)
Status variables that should NOT be present in 5.7 P_S.GLOBAL_STATUS (2-8)
EXPECT NOTHING
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.GLOBAL_STATUS"
  and upper(variable_name) in (
select upper(variable_name) from test.sanity
where (show_mode = "JUNK: SESSION-ONLY" or show_mode = "JUNK: COM COUNTER" or show_mode = "JUNK: SLAVE-ONLY")
and source = "I_S.GLOBAL_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 9A
Present in 5.7 P_S.SESSION_STATUS (2-10)
Missing in 5.6 I_S.SESSION_STATUS (1-7)
EXPECT NOTHING
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.SESSION_STATUS"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.6"
    and source = "I_S.SESSION_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 9B
Present in 5.7 P_S.SESSION_STATUS (2-10)
Missing in 5.6 P_S.SESSION_STATUS (1-10)
EXPECT NOTHING
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.SESSION_STATUS"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.6"
    and source = "P_S.SESSION_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 9C
Present in 5.6 P_S.SESSION_STATUS (1-10)
Missing in 5.7 P_S.SESSION_STATUS (2-10)
EXPECT NOTHING
================================================================================
select * from test.sanity
where show_mode = "5.6"
  and source = "P_S.SESSION_STATUS"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.SESSION_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 10
Present in 5.6 I_S.SESSION_STATUS (1-7)
Missing in 5.7 P_S.SESSION_STATUS (2-10)
EXPECT NOTHING AFTER REMOVING KNOWN COM COUNTERS AND SLAVE-ONLY (2-15A & 2-15B)
================================================================================
select * from test.sanity
where show_mode = "5.6"
  and source = "I_S.SESSION_STATUS"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where (show_mode = "JUNK: COM COUNTER" or show_mode = "JUNK: SLAVE-ONLY")
and source = "I_S.SESSION_STATUS")
and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.SESSION_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME
5.6	I_S.SESSION_STATUS	COM_SHUTDOWN

================================================================================
TEST 11
KNOWN COM COUNTERS (2-15A)
KNOWN SLAVE-ONLY (2-15B)
Status variables that should NOT be present in 5.7 P_S.SESSION_STATUS (2-10)
EXPECT NOTHING
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.SESSION_STATUS"
  and upper(variable_name) in (
select upper(variable_name) from test.sanity
where (show_mode = "JUNK: COM COUNTER" or show_mode = "JUNK: SLAVE-ONLY")
and source = "I_S.SESSION_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 12
Present in 5.7 P_S.SESSION_STATUS (2-10)
Missing in 5.7 P_S.STATUS_BY_THREAD
EXPECT NOTHING AFTER REMOVING KNOWN GLOBAL-ONLY (2-15)
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.SESSION_STATUS"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.STATUS_BY_THREAD")
and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where (show_mode = "JUNK: GLOBAL-ONLY")
and source = "I_S.SESSION_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 13
Present in 5.7 P_S.STATUS_BY_THREAD
Missing in 5.7 P_S.SESSION_STATUS (2-10)
EXPECT NOTHING
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.STATUS_BY_THREAD"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.SESSION_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 14
Present in 5.7 P_S.SESSION_STATUS (2-10)
Missing in 5.7 P_S.STATUS_BY_ACCOUNT
EXPECT NOTHING AFTER REMOVING
- KNOWN GLOBAL-ONLY (2-15)
- STATUS VARS THAT CAN'T BE AGGREGATED (2-17)
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.SESSION_STATUS"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "JUNK"
    and source = "NO AGGREGATE")
and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where (show_mode = "JUNK: GLOBAL-ONLY")
and source = "I_S.SESSION_STATUS")
and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.STATUS_BY_ACCOUNT")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 15
Present in 5.7 P_S.STATUS_BY_ACCOUNT
Missing in 5.7 P_S.SESSION_STATUS (2-10)
EXPECT NOTHING
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.STATUS_BY_ACCOUNT"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.SESSION_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 16
KNOWN STATUS VARS THAT CAN'T BE AGGREGATED (2-17)
Status variables that should NOT be present in 5.7 P_S.STATUS_BY_ACCOUNT
================================================================================
select * from performance_schema.status_by_account
where upper(variable_name) in (
select upper(variable_name) from test.sanity
where show_mode = "JUNK"
    and source = "NO AGGREGATE")
order by variable_name;
USER	HOST	VARIABLE_NAME	VARIABLE_VALUE

================================================================================
TEST 17
Present in 5.7 P_S.SESSION_STATUS (2-10)
Missing in 5.7 P_S.STATUS_BY_USER
EXPECT NOTHING AFTER REMOVING
- KNOWN GLOBAL-ONLY (2-15)
- STATUS VARS THAT CAN'T BE AGGREGATED (2-17)
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.SESSION_STATUS"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "JUNK"
    and source = "NO AGGREGATE")
and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where (show_mode = "JUNK: GLOBAL-ONLY")
and source = "I_S.SESSION_STATUS")
and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.STATUS_BY_USER")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 18
Present in 5.7 P_S.STATUS_BY_USER
Missing in 5.7 P_S.SESSION_STATUS (2-10)
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.STATUS_BY_USER"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.SESSION_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 19
KNOWN STATUS VARS THAT CAN'T BE AGGREGATED (2-17)
Status variables that should NOT be present in 5.7 P_S.STATUS_BY_USER
================================================================================
select * from performance_schema.status_by_user
where upper(variable_name) in (
select upper(variable_name) from test.sanity
where show_mode = "JUNK"
    and source = "NO AGGREGATE")
order by variable_name;
USER	VARIABLE_NAME	VARIABLE_VALUE

================================================================================
TEST 20
Present in 5.7 P_S.SESSION_STATUS (2-10)
Missing in 5.7 P_S.STATUS_BY_HOST
EXPECT NOTHING AFTER REMOVING
- KNOWN GLOBAL-ONLY (2-15)
- STATUS VARS THAT CAN'T BE AGGREGATED (2-17)
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.SESSION_STATUS"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "JUNK"
    and source = "NO AGGREGATE")
and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where (show_mode = "JUNK: GLOBAL-ONLY")
and source = "I_S.SESSION_STATUS")
and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.STATUS_BY_HOST")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 21
Present in 5.7 P_S.STATUS_BY_HOST
Missing in 5.7 P_S.SESSION_STATUS (2-10)
================================================================================
select * from test.sanity
where show_mode = "5.7"
  and source = "P_S.STATUS_BY_HOST"
  and upper(variable_name) not in (
select upper(variable_name) from test.sanity
where show_mode = "5.7"
    and source = "P_S.SESSION_STATUS")
order by show_mode, source, variable_name;
SHOW_MODE	SOURCE	VARIABLE_NAME

================================================================================
TEST 22
KNOWN STATUS VARS THAT CAN'T BE AGGREGATED (2-17)
Status variables that should NOT be present in 5.7 P_S.STATUS_BY_HOST
================================================================================
select * from performance_schema.status_by_host
where upper(variable_name) in (
select upper(variable_name) from test.sanity
where show_mode = "JUNK"
    and source = "NO AGGREGATE")
order by variable_name;
HOST	VARIABLE_NAME	VARIABLE_VALUE

================================================================================
CLEAN UP
================================================================================
set @@global.show_compatibility_56 = @show_compatibility_56_save;
drop table test.sanity;