revision-id: 654b587999456e08fdfb3a909bafb19b9fa49830 (mariadb-10.2.16-149-g654b5879994)
parent(s): 4a026596f53f59619941c0e3ce5d36a056b6bd2e
author: Jan Lindström
committer: Jan Lindström
timestamp: 2018-09-19 11:33:22 +0300
message:
MDEV-17208: Test failure on galera.MW-286
Test changes only.
---
mysql-test/suite/galera/r/MW-286.result | 6 ++++--
mysql-test/suite/galera/t/MW-286.test | 6 ++++--
2 files changed, 8 insertions(+), 4 deletions(-)
diff --git a/mysql-test/suite/galera/r/MW-286.result b/mysql-test/suite/galera/r/MW-286.result
index adc996c1cbe..f3bef6f7516 100644
--- a/mysql-test/suite/galera/r/MW-286.result
+++ b/mysql-test/suite/galera/r/MW-286.result
@@ -1,13 +1,15 @@
-CREATE TABLE ten (f1 INTEGER);
+connection node_1;
+CREATE TABLE ten (f1 INTEGER) Engine=InnoDB;
INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB;
INSERT INTO t1 (f1) SELECT 000000 + (10000 * a1.f1) + (1000 * a2.f1) + (100 * a3.f1) + (10 * a4.f1) + a5.f1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5;
INSERT INTO t1 (f1) SELECT 100000 + (10000 * a1.f1) + (1000 * a2.f1) + (100 * a3.f1) + (10 * a4.f1) + a5.f1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5;;
+connection node_2;
SET GLOBAL wsrep_desync = TRUE;
SET wsrep_on = FALSE;
ALTER TABLE t1 ADD PRIMARY KEY (f1);
-ERROR 70100: Query execution was interrupted
SET wsrep_on = TRUE;
SET GLOBAL wsrep_desync = FALSE;
+connection node_1;
DROP TABLE t1;
DROP TABLE ten;
diff --git a/mysql-test/suite/galera/t/MW-286.test b/mysql-test/suite/galera/t/MW-286.test
index 1b2e322f078..426b4493bb7 100644
--- a/mysql-test/suite/galera/t/MW-286.test
+++ b/mysql-test/suite/galera/t/MW-286.test
@@ -7,7 +7,7 @@
--source include/big_test.inc
--connection node_1
-CREATE TABLE ten (f1 INTEGER);
+CREATE TABLE ten (f1 INTEGER) Engine=InnoDB;
INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB;
@@ -22,11 +22,13 @@ INSERT INTO t1 (f1) SELECT 000000 + (10000 * a1.f1) + (1000 * a2.f1) + (100 * a3
SET GLOBAL wsrep_desync = TRUE;
SET wsrep_on = FALSE;
---error ER_QUERY_INTERRUPTED
+--error 0,ER_QUERY_INTERRUPTED
ALTER TABLE t1 ADD PRIMARY KEY (f1);
SET wsrep_on = TRUE;
SET GLOBAL wsrep_desync = FALSE;
+--connection node_1
+reap;
DROP TABLE t1;
DROP TABLE ten;
1
0
revision-id: cd363fecbf0fffe738c9d96cd8751687e5b44d62 (mariadb-10.1.35-63-gcd363fe)
parent(s): cc616bea53be7740398aa8b4caf1c19d5f944635
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-09-19 01:24:46 -0700
message:
Removed duplicate tests.
---
mysql-test/r/stat_tables.result | 53 ----------------------------------
mysql-test/r/stat_tables_innodb.result | 53 ----------------------------------
mysql-test/t/stat_tables.test | 45 -----------------------------
3 files changed, 151 deletions(-)
diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index 224c734..40290ca 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -591,59 +591,6 @@ id select_type table type possible_keys key key_len ref rows Extra
set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
set use_stat_tables=@save_use_stat_tables;
#
-# MDEV-16757: manual addition of min/max statistics for BLOB
-#
-SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-Table Op Msg_type Msg_text
-test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
-test.t1 analyze status OK
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-DELETE FROM mysql.column_stats
-WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
-('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-SELECT pk FROM t1;
-pk
-1
-2
-DROP TABLE t1;
-set use_stat_tables=@save_use_stat_tables;
-#
-# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
-#
-SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-Table Op Msg_type Msg_text
-test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze status OK
-SELECT * FROM t1;
-pk c
-1 foo
-2 bar
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
-SELECT * FROM t1;
-pk a
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-DROP TABLE t1;
-set use_stat_tables=@save_use_stat_tables;
-#
# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
#
SET use_stat_tables= PREFERABLY;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index ba1dee3..1b1cd7d 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -618,59 +618,6 @@ id select_type table type possible_keys key key_len ref rows Extra
set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
set use_stat_tables=@save_use_stat_tables;
#
-# MDEV-16757: manual addition of min/max statistics for BLOB
-#
-SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-Table Op Msg_type Msg_text
-test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
-test.t1 analyze status OK
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-DELETE FROM mysql.column_stats
-WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
-('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-SELECT pk FROM t1;
-pk
-1
-2
-DROP TABLE t1;
-set use_stat_tables=@save_use_stat_tables;
-#
-# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
-#
-SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-Table Op Msg_type Msg_text
-test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze status OK
-SELECT * FROM t1;
-pk c
-1 foo
-2 bar
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
-SELECT * FROM t1;
-pk a
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-DROP TABLE t1;
-set use_stat_tables=@save_use_stat_tables;
-#
# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
#
SET use_stat_tables= PREFERABLY;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index c318cc5..85a1098 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -370,51 +370,6 @@ set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectiv
set use_stat_tables=@save_use_stat_tables;
--echo #
---echo # MDEV-16757: manual addition of min/max statistics for BLOB
---echo #
-
-SET use_stat_tables= PREFERABLY;
-
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
---sorted_result
-SELECT * FROM mysql.column_stats;
-DELETE FROM mysql.column_stats
- WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
- ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
---sorted_result
-SELECT * FROM mysql.column_stats;
-
-SELECT pk FROM t1;
-
-DROP TABLE t1;
-
-set use_stat_tables=@save_use_stat_tables;
-
---echo #
---echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
---echo #
-
-SET use_stat_tables= PREFERABLY;
-
-CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-SELECT * FROM t1;
-SELECT * FROM mysql.column_stats;
-
-CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
-SELECT * FROM t1;
-SELECT * FROM mysql.column_stats;
-
-DROP TABLE t1;
-
-set use_stat_tables=@save_use_stat_tables;
-
-
---echo #
--echo # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
--echo #
1
0
revision-id: 4c6ea0214b2074d65ec8c62fc8ae7762aee20aad (mariadb-10.1.34-32-g4c6ea02)
parent(s): 2a587d40b9e056e61e38229a6aeea3d817e2c777 cc616bea53be7740398aa8b4caf1c19d5f944635
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-09-19 00:57:43 -0700
message:
Merge
CMakeLists.txt | 6 +
VERSION | 2 +-
cmake/install_macros.cmake | 9 +
cmake/mysql_add_executable.cmake | 3 +
config.h.cmake | 6 -
debian/control | 4 +-
extra/mariabackup/backup_copy.cc | 19 +-
extra/mariabackup/fil_cur.cc | 4 +-
extra/mariabackup/wsrep.cc | 3 +-
extra/mariabackup/xtrabackup.cc | 35 +-
include/m_ctype.h | 28 +-
include/my_atomic.h | 2 +-
include/mysql/service_wsrep.h | 3 +
include/service_versions.h | 2 +-
include/sql_common.h | 2 +-
include/wsrep.h | 5 +
man/CMakeLists.txt | 6 +-
man/mariabackup.1 | 16 +
man/mbstream.1 | 16 +
man/mysql_embedded.1 | 1 +
man/tokuft_logdump.1 | 16 -
man/tokuft_logprint.1 | 16 +
man/wsrep_sst_mariabackup.1 | 16 +
man/wsrep_sst_rsync.1 | 4 +-
man/wsrep_sst_rsync_wan.1 | 16 +
mysql-test/extra/binlog_tests/binlog.test | 1 -
mysql-test/extra/rpl_tests/rpl_foreign_key.test | 60 -
mysql-test/include/ctype_mdev13118.inc | 15 +
mysql-test/mysql-test-run.pl | 197 +-
mysql-test/r/bench_count_distinct.result | 2 +-
mysql-test/r/ctype_binary.result | 23 +
mysql-test/r/ctype_eucjpms.result | 23 +
mysql-test/r/ctype_euckr.result | 29 +
mysql-test/r/ctype_gbk.result | 23 +
mysql-test/r/ctype_latin1.result | 23 +
mysql-test/r/ctype_ucs.result | 23 +
mysql-test/r/ctype_ujis.result | 23 +
mysql-test/r/ctype_utf16.result | 23 +
mysql-test/r/ctype_utf16le.result | 29 +
mysql-test/r/ctype_utf32.result | 23 +
mysql-test/r/ctype_utf8.result | 23 +
mysql-test/r/ctype_utf8mb4.result | 23 +
mysql-test/r/distinct.result | 2 +-
mysql-test/r/explain_json.result | 18 +-
mysql-test/r/flush.result | 24 +
mysql-test/r/func_isnull.result | 20 +
mysql-test/r/func_time.result | 5 +
mysql-test/r/gis.result | 16 +
mysql-test/r/grant.result | 4 +-
mysql-test/r/group_min_max.result | 91 +-
mysql-test/r/join.result | 6 +-
mysql-test/r/join_outer.result | 50 +
mysql-test/r/join_outer_jcl6.result | 50 +
mysql-test/r/mysql.result | 2 -
mysql-test/r/mysql_not_windows.result | 2 +
mysql-test/r/rename.result | 4 +
mysql-test/r/selectivity.result | 48 +-
mysql-test/r/selectivity_innodb.result | 48 +-
mysql-test/r/sp.result | 17 +
mysql-test/r/sp_notembedded.result | 2 -
mysql-test/r/stat_tables.result | 595 -
mysql-test/r/stat_tables_innodb.result | 624 -
mysql-test/r/subselect_extra_no_semijoin.result | 19 +
mysql-test/r/subselect_mat.result | 99 +-
mysql-test/r/subselect_mat_cost_bugs.result | 2 +-
mysql-test/r/subselect_sj_mat.result | 99 +-
mysql-test/r/union.result | 16 +
mysql-test/suite/galera/disabled.def | 11 +-
mysql-test/suite/galera/r/MW-336.result | 42 +-
mysql-test/suite/galera/r/MW-44.result | 32 +-
mysql-test/suite/galera/r/galera#505.result | 5 +
.../galera/r/galera_binlog_stmt_autoinc.result | 147 +
mysql-test/suite/galera/r/galera_defaults.result | 68 -
.../r/galera_ist_innodb_flush_logs,debug.rdiff | 103 +
.../galera/r/galera_ist_innodb_flush_logs.result | 96 -
.../galera/r/galera_ist_mysqldump,debug.rdiff | 106 +
.../suite/galera/r/galera_ist_mysqldump.result | 98 +-
.../suite/galera/r/galera_ist_rsync,debug.rdiff | 103 +
mysql-test/suite/galera/r/galera_ist_rsync.result | 97 -
.../galera/r/galera_ist_xtrabackup-v2,debug.rdiff | 103 +
.../suite/galera/r/galera_ist_xtrabackup-v2.result | 96 -
mysql-test/suite/galera/r/galera_kill_ddl.result | 1 +
.../suite/galera/r/galera_kill_largechanges.result | 1 +
.../suite/galera/r/galera_kill_smallchanges.result | 1 +
.../galera/r/galera_sst_mysqldump_with_key.result | 192 +-
.../suite/galera/r/galera_sst_rsync2,debug.rdiff | 103 +
mysql-test/suite/galera/r/galera_sst_rsync2.result | 262 +
.../galera/r/galera_sst_rsync_data_dir,debug.rdiff | 103 +
.../galera/r/galera_sst_rsync_data_dir.result | 262 +
.../r/galera_sst_xtrabackup-v2_data_dir.result | 262 +
.../suite/galera/r/galera_toi_truncate.result | 2 +
.../suite/galera/r/galera_var_desync_on.result | 2 -
.../suite/galera/r/galera_var_node_address.result | 2 +-
.../suite/galera/r/galera_var_slave_threads.result | 64 -
mysql-test/suite/galera/r/galera_wan.result | 5 +-
mysql-test/suite/galera/r/mysql-wsrep#332.result | 111 +
mysql-test/suite/galera/suite.pm | 2 +
mysql-test/suite/galera/t/MW-328A.test | 1 +
mysql-test/suite/galera/t/MW-328B.test | 1 +
mysql-test/suite/galera/t/MW-328C.test | 1 +
mysql-test/suite/galera/t/MW-336.test | 108 +-
mysql-test/suite/galera/t/MW-44-master.opt | 1 +
mysql-test/suite/galera/t/MW-44.test | 25 +-
mysql-test/suite/galera/t/galera#505.test | 26 +
.../suite/galera/t/galera_binlog_stmt_autoinc.test | 230 +
mysql-test/suite/galera/t/galera_defaults.test | 13 +-
.../suite/galera/t/galera_ist_mysqldump.test | 2 +
.../suite/galera/t/galera_ist_xtrabackup-v2.test | 5 +
mysql-test/suite/galera/t/galera_kill_ddl.test | 2 +
.../suite/galera/t/galera_kill_largechanges.test | 2 +
.../suite/galera/t/galera_kill_smallchanges.test | 2 +
.../galera/t/galera_sst_mysqldump_with_key.test | 7 +-
mysql-test/suite/galera/t/galera_sst_rsync2.cnf | 15 +
mysql-test/suite/galera/t/galera_sst_rsync2.test | 12 +
.../suite/galera/t/galera_sst_rsync_data_dir.cnf | 11 +
.../suite/galera/t/galera_sst_rsync_data_dir.test | 16 +
.../galera/t/galera_sst_xtrabackup-v2_data_dir.cnf | 16 +
.../t/galera_sst_xtrabackup-v2_data_dir.test | 23 +
mysql-test/suite/galera/t/galera_toi_truncate.test | 16 +-
.../suite/galera/t/galera_var_desync_on.test | 7 +-
.../suite/galera/t/galera_var_node_address.test | 8 +-
.../suite/galera/t/galera_var_slave_threads.test | 13 +
mysql-test/suite/galera/t/galera_wan.test | 12 +-
mysql-test/suite/galera/t/mysql-wsrep#332.test | 113 +
.../suite/galera_3nodes/r/galera_pc_weight.result | 31 +-
.../suite/galera_3nodes/t/galera_pc_weight.test | 55 +-
.../r/default_row_format_compatibility.result | 2 +-
mysql-test/suite/innodb/r/foreign-keys.result | 73 +
mysql-test/suite/innodb/r/foreign_key.result | 19 +
mysql-test/suite/innodb/r/innodb-lock.result | 29 +-
mysql-test/suite/innodb/r/innodb-wl5522.result | 263 +-
mysql-test/suite/innodb/t/foreign-keys.test | 87 +
mysql-test/suite/innodb/t/foreign_key.test | 25 +
mysql-test/suite/innodb/t/innodb-lock.test | 74 +-
mysql-test/suite/innodb/t/innodb-wl5522.test | 258 +-
.../suite/innodb_fts/r/fts_kill_query.result | 6 +
mysql-test/suite/innodb_fts/r/sync_ddl.result | 117 +
mysql-test/suite/innodb_fts/t/fts_kill_query.test | 30 +
mysql-test/suite/innodb_fts/t/sync_ddl.test | 177 +
mysql-test/suite/maria/concurrent.result | 28 +
mysql-test/suite/maria/concurrent.test | 28 +
mysql-test/suite/maria/create.result | 33 +
mysql-test/suite/maria/create.test | 42 +
mysql-test/suite/maria/maria.result | 4 +
mysql-test/suite/maria/maria.test | 10 +
mysql-test/suite/parts/r/truncate_locked.result | 7 +
mysql-test/suite/parts/t/truncate_locked.test | 10 +
mysql-test/suite/plugins/r/auth_ed25519.result | 2 +-
mysql-test/suite/plugins/r/processlist.result | 4 +-
mysql-test/suite/plugins/t/processlist.test | 8 +-
.../suite/rpl/r/rpl_foreign_key_innodb.result | 3 +-
mysql-test/suite/rpl/r/rpl_row_spatial.result | 14 +
mysql-test/suite/rpl/t/rpl_foreign_key_innodb.test | 62 +-
mysql-test/suite/rpl/t/rpl_row_spatial.test | 17 +
.../sys_vars/r/sysvars_innodb,32bit,xtradb.rdiff | 2 +-
.../suite/sys_vars/r/sysvars_innodb,xtradb.rdiff | 6 +-
mysql-test/suite/sys_vars/r/sysvars_innodb.result | 2 +-
mysql-test/suite/vcol/r/vcol_select_innodb.result | 2 +-
mysql-test/suite/vcol/r/vcol_select_myisam.result | 2 +-
.../suite/wsrep/include/check_galera_version.inc | 20 +-
mysql-test/suite/wsrep/r/variables.result | 56 +-
mysql-test/suite/wsrep/t/variables.test | 18 +-
mysql-test/t/bootstrap.test | 9 +
mysql-test/t/ctype_binary.test | 3 +
mysql-test/t/ctype_eucjpms.test | 2 +
mysql-test/t/ctype_euckr.test | 11 +
mysql-test/t/ctype_gbk.test | 3 +
mysql-test/t/ctype_latin1.test | 3 +
mysql-test/t/ctype_ucs.test | 4 +
mysql-test/t/ctype_ujis.test | 4 +
mysql-test/t/ctype_utf16.test | 5 +
mysql-test/t/ctype_utf16le.test | 13 +
mysql-test/t/ctype_utf32.test | 8 +
mysql-test/t/ctype_utf8.test | 7 +
mysql-test/t/ctype_utf8mb4.test | 8 +
mysql-test/t/flush.test | 32 +
mysql-test/t/func_isnull.test | 16 +
mysql-test/t/func_time.test | 4 +
mysql-test/t/gis.test | 15 +
mysql-test/t/grant.test | 3 +
mysql-test/t/group_min_max.test | 44 +
mysql-test/t/join.test | 3 +-
mysql-test/t/join_outer.test | 48 +
mysql-test/t/mysql.test | 5 +-
mysql-test/t/mysql_not_windows.test | 7 +
mysql-test/t/rename.test | 7 +
mysql-test/t/selectivity.test | 36 +
mysql-test/t/sp.test | 21 +
mysql-test/t/stat_tables.test | 375 -
mysql-test/t/subselect_extra_no_semijoin.test | 31 +-
mysql-test/t/subselect_sj_mat.test | 79 +
mysql-test/t/union.test | 15 +
mysql-test/unstable-tests | 253 +-
mysys/my_rename.c | 5 +-
plugin/auth_ed25519/server_ed25519.c | 2 +-
plugin/auth_pam/mapper/pam_user_map.c | 6 +-
scripts/CMakeLists.txt | 2 +
scripts/galera_new_cluster.sh | 3 -
scripts/galera_recovery.sh | 3 +-
scripts/mysql_install_db.pl.in | 2 +-
scripts/mysql_install_db.sh | 4 +-
scripts/mysql_system_tables_fix.sql | 50 +-
scripts/mysqld_multi.sh | 2 +-
scripts/wsrep_sst_common.sh | 32 +-
scripts/wsrep_sst_rsync.sh | 155 +-
scripts/wsrep_sst_xtrabackup-v2.sh | 28 +-
sql-common/client.c | 4 +
sql/events.cc | 2 +
sql/field.cc | 17 +-
sql/ha_partition.cc | 50 +
sql/ha_partition.h | 8 +
sql/handler.cc | 4 +-
sql/handler.h | 4 +-
sql/item.cc | 15 +-
sql/item_cmpfunc.cc | 13 +
sql/item_cmpfunc.h | 1 +
sql/item_strfunc.cc | 112 +-
sql/key.cc | 3 +-
sql/lex.h | 6 +-
sql/log.cc | 4 +-
sql/log_event.cc | 5 -
sql/mysqld.cc | 24 +-
sql/mysqld.h | 1 +
sql/opt_range.cc | 32 +-
sql/opt_subselect.cc | 72 +-
sql/protocol.cc | 8 +-
sql/share/CMakeLists.txt | 12 +-
sql/sp_head.cc | 33 +-
sql/sql_acl.cc | 17 +-
sql/sql_alter.cc | 20 +-
sql/sql_base.cc | 225 +-
sql/sql_base.h | 5 +-
sql/sql_class.cc | 3 +-
sql/sql_class.h | 6 +-
sql/sql_explain.cc | 2 +-
sql/sql_lex.h | 4 +-
sql/sql_list.h | 5 +-
sql/sql_parse.cc | 2 +
sql/sql_partition.cc | 12 +
sql/sql_plugin_services.ic | 3 +-
sql/sql_reload.cc | 13 +-
sql/sql_select.cc | 44 +-
sql/sql_show.cc | 11 +-
sql/sql_statistics.cc | 3 +
sql/sql_statistics.h | 29 +-
sql/sql_table.cc | 66 +-
sql/sql_time.cc | 2 +-
sql/sql_trigger.cc | 8 +-
sql/sql_truncate.cc | 5 +-
sql/sql_yacc.yy | 38 +-
sql/table.cc | 23 +-
sql/table.h | 59 +-
sql/table_cache.cc | 2 -
sql/tztime.cc | 2 +-
sql/wsrep_dummy.cc | 3 +
sql/wsrep_hton.cc | 9 +-
sql/wsrep_mysqld.cc | 289 +-
sql/wsrep_mysqld.h | 4 +-
sql/wsrep_sst.cc | 87 +-
sql/wsrep_sst.h | 1 +
sql/wsrep_var.cc | 10 +-
sql/wsrep_xid.cc | 5 +-
storage/connect/filamdbf.cpp | 4 +-
storage/connect/filamvct.cpp | 5 -
storage/connect/inihandl.cpp | 2 +-
storage/connect/javaconn.cpp | 6 +-
storage/connect/jdbconn.cpp | 10 +-
storage/connect/jmgoconn.cpp | 4 +-
storage/connect/tabjmg.cpp | 2 +-
storage/connect/xindex.cpp | 0
storage/innobase/btr/btr0scrub.cc | 2 +-
storage/innobase/buf/buf0buddy.cc | 2 +-
storage/innobase/buf/buf0buf.cc | 4 +-
storage/innobase/buf/buf0dump.cc | 6 +-
storage/innobase/buf/buf0lru.cc | 2 +-
storage/innobase/dict/dict0stats_bg.cc | 11 +-
storage/innobase/fil/fil0crypt.cc | 2 +-
storage/innobase/fts/fts0ast.cc | 9 +-
storage/innobase/fts/fts0fts.cc | 76 +-
storage/innobase/fts/fts0opt.cc | 175 +-
storage/innobase/fts/fts0pars.cc | 16 +-
storage/innobase/fts/fts0pars.y | 16 +-
storage/innobase/fts/fts0que.cc | 17 +-
storage/innobase/handler/ha_innodb.cc | 81 +-
storage/innobase/handler/handler0alter.cc | 86 +-
storage/innobase/handler/i_s.cc | 19 +-
storage/innobase/include/dict0mem.h | 3 +
storage/innobase/include/fts0ast.h | 6 +-
storage/innobase/include/fts0fts.h | 8 +-
storage/innobase/include/fts0priv.h | 18 +-
storage/innobase/include/row0ftsort.h | 10 +-
storage/innobase/include/univ.i | 5 +-
storage/innobase/lock/lock0lock.cc | 5 +-
storage/innobase/log/log0log.cc | 13 +-
storage/innobase/row/row0ftsort.cc | 1 -
storage/innobase/row/row0import.cc | 89 +-
storage/innobase/row/row0mysql.cc | 19 +-
storage/innobase/srv/srv0srv.cc | 14 +-
storage/innobase/trx/trx0purge.cc | 18 +-
storage/innobase/trx/trx0sys.cc | 12 +-
storage/innobase/trx/trx0trx.cc | 9 +-
storage/innobase/trx/trx0undo.cc | 3 +-
storage/maria/ha_maria.cc | 3 +
storage/maria/ma_blockrec.c | 18 +-
storage/maria/ma_commit.c | 2 +-
storage/maria/ma_info.c | 6 +-
storage/maria/ma_norec.c | 4 +-
storage/maria/ma_recovery.c | 8 +-
storage/maria/ma_test2.c | 4 +-
storage/maria/maria_chk.c | 4 +-
storage/maria/maria_def.h | 2 +
storage/mroonga/ha_mroonga.cpp | 9 +-
storage/mroonga/ha_mroonga.hpp | 4 -
storage/mroonga/vendor/groonga/lib/expr.c | 2 +-
storage/myisam/ha_myisam.cc | 6 +-
storage/myisam/mi_check.c | 6 +-
storage/myisam/mi_locking.c | 6 +-
storage/sphinx/ha_sphinx.cc | 2 +-
.../mysql-test/spider/include/init_child2_1.inc | 14 +
.../mysql-test/spider/include/init_master_1.inc | 4 +
.../mysql-test/spider/r/spider_fixes_part.result | 48 +
.../mysql-test/spider/t/spider_fixes_part.test | 109 +
storage/tokudb/CMakeLists.txt | 8 +-
storage/tokudb/PerconaFT/CMakeLists.txt | 8 +-
.../cmake_modules/TokuSetupCompiler.cmake | 3 +
.../tokudb/PerconaFT/ft/cachetable/cachetable.cc | 21 +-
.../tokudb/PerconaFT/ft/cachetable/cachetable.h | 8 +-
.../tokudb/PerconaFT/ft/ft-cachetable-wrappers.cc | 3 -
storage/tokudb/PerconaFT/ft/ft-test-helpers.cc | 3 -
storage/tokudb/PerconaFT/ft/ft.h | 3 +
storage/tokudb/PerconaFT/ft/node.cc | 2 +
.../PerconaFT/ft/serialize/block_allocator.cc | 2 +-
.../tokudb/PerconaFT/ft/tests/cachetable-4357.cc | 4 -
.../tokudb/PerconaFT/ft/tests/cachetable-4365.cc | 4 -
.../tokudb/PerconaFT/ft/tests/cachetable-5097.cc | 6 +-
.../tokudb/PerconaFT/ft/tests/cachetable-5978-2.cc | 7 +-
.../tokudb/PerconaFT/ft/tests/cachetable-5978.cc | 13 +-
.../PerconaFT/ft/tests/cachetable-all-write.cc | 5 +-
.../ft/tests/cachetable-checkpoint-pending.cc | 8 +-
.../ft/tests/cachetable-checkpoint-pinned-nodes.cc | 6 +-
.../ft/tests/cachetable-cleaner-checkpoint.cc | 5 +-
.../ft/tests/cachetable-cleaner-checkpoint2.cc | 5 +-
.../cachetable-cleaner-thread-attrs-accumulate.cc | 8 +-
.../cachetable-cleaner-thread-everything-pinned.cc | 5 +-
...etable-cleaner-thread-nothing-needs-flushing.cc | 5 +-
.../cachetable-cleaner-thread-same-fullhash.cc | 7 +-
.../ft/tests/cachetable-cleaner-thread-simple.cc | 7 +-
.../ft/tests/cachetable-clock-eviction.cc | 9 +-
.../ft/tests/cachetable-clock-eviction2.cc | 9 +-
.../ft/tests/cachetable-clock-eviction3.cc | 9 +-
.../ft/tests/cachetable-clock-eviction4.cc | 9 +-
.../ft/tests/cachetable-clone-checkpoint.cc | 5 +-
.../cachetable-clone-partial-fetch-pinned-node.cc | 7 +-
.../ft/tests/cachetable-clone-partial-fetch.cc | 7 +-
.../ft/tests/cachetable-clone-pin-nonblocking.cc | 7 +-
.../ft/tests/cachetable-clone-unpin-remove.cc | 5 +-
.../ft/tests/cachetable-eviction-close-test.cc | 4 -
.../ft/tests/cachetable-eviction-close-test2.cc | 4 -
.../ft/tests/cachetable-eviction-getandpin-test.cc | 14 +-
.../tests/cachetable-eviction-getandpin-test2.cc | 12 +-
.../ft/tests/cachetable-fetch-inducing-evictor.cc | 15 +-
.../ft/tests/cachetable-flush-during-cleaner.cc | 3 +-
.../ft/tests/cachetable-getandpin-test.cc | 8 +-
.../cachetable-kibbutz_and_flush_cachefile.cc | 3 +-
.../PerconaFT/ft/tests/cachetable-partial-fetch.cc | 18 +-
.../ft/tests/cachetable-pin-checkpoint.cc | 6 -
.../cachetable-pin-nonblocking-checkpoint-clean.cc | 9 +-
.../ft/tests/cachetable-prefetch-close-test.cc | 2 -
.../ft/tests/cachetable-prefetch-getandpin-test.cc | 12 +-
.../ft/tests/cachetable-put-checkpoint.cc | 9 -
.../PerconaFT/ft/tests/cachetable-simple-clone.cc | 7 +-
.../PerconaFT/ft/tests/cachetable-simple-clone2.cc | 5 +-
.../PerconaFT/ft/tests/cachetable-simple-close.cc | 20 +-
.../ft/tests/cachetable-simple-maybe-get-pin.cc | 3 +-
.../ft/tests/cachetable-simple-pin-cheap.cc | 9 +-
.../ft/tests/cachetable-simple-pin-dep-nodes.cc | 8 +-
.../cachetable-simple-pin-nonblocking-cheap.cc | 19 +-
.../ft/tests/cachetable-simple-pin-nonblocking.cc | 13 +-
.../PerconaFT/ft/tests/cachetable-simple-pin.cc | 11 +-
.../ft/tests/cachetable-simple-put-dep-nodes.cc | 6 +-
.../cachetable-simple-read-pin-nonblocking.cc | 13 +-
.../ft/tests/cachetable-simple-read-pin.cc | 13 +-
.../cachetable-simple-unpin-remove-checkpoint.cc | 7 +-
.../PerconaFT/ft/tests/cachetable-simple-verify.cc | 5 +-
.../tokudb/PerconaFT/ft/tests/cachetable-test.cc | 22 +-
.../ft/tests/cachetable-unpin-and-remove-test.cc | 4 +-
.../cachetable-unpin-remove-and-checkpoint.cc | 6 +-
.../PerconaFT/ft/tests/cachetable-unpin-test.cc | 2 -
storage/tokudb/PerconaFT/ft/tests/test-TDB2-pe.cc | 178 +
storage/tokudb/PerconaFT/ft/tests/test-TDB89.cc | 208 +
storage/tokudb/PerconaFT/ft/txn/rollback-apply.cc | 2 +
storage/tokudb/PerconaFT/ft/txn/rollback.cc | 2 +-
storage/tokudb/PerconaFT/ftcxx/malloc_utils.cpp | 2 +-
storage/tokudb/PerconaFT/ftcxx/malloc_utils.hpp | 2 +-
storage/tokudb/PerconaFT/portability/memory.cc | 14 +-
storage/tokudb/PerconaFT/portability/toku_assert.h | 2 +-
.../tokudb/PerconaFT/portability/toku_debug_sync.h | 3 +-
.../PerconaFT/portability/toku_instr_mysql.cc | 6 +-
.../PerconaFT/portability/toku_instrumentation.h | 6 +-
.../PerconaFT/portability/toku_portability.h | 2 +-
.../tokudb/PerconaFT/portability/toku_race_tools.h | 2 +-
storage/tokudb/PerconaFT/src/tests/get_last_key.cc | 32 +-
storage/tokudb/PerconaFT/src/ydb.cc | 3 +
storage/tokudb/PerconaFT/src/ydb_lib.cc | 2 +-
storage/tokudb/PerconaFT/util/dmt.cc | 4 +-
storage/tokudb/PerconaFT/util/minicron.cc | 3 +-
storage/tokudb/PerconaFT/util/scoped_malloc.cc | 2 +-
.../util/tests/minicron-change-period-data-race.cc | 66 +
storage/tokudb/ha_tokudb.cc | 325 +-
storage/tokudb/ha_tokudb.h | 92 +-
storage/tokudb/ha_tokudb_admin.cc | 8 +-
storage/tokudb/ha_tokudb_alter_55.cc | 4 +
storage/tokudb/ha_tokudb_alter_56.cc | 265 +-
storage/tokudb/ha_tokudb_alter_common.cc | 6 +-
storage/tokudb/ha_tokudb_update.cc | 96 +-
storage/tokudb/hatoku_cmp.cc | 33 +-
storage/tokudb/hatoku_cmp.h | 14 +-
storage/tokudb/hatoku_defines.h | 51 +-
storage/tokudb/hatoku_hton.cc | 183 +-
storage/tokudb/hatoku_hton.h | 25 +-
storage/tokudb/mysql-test/rpl/disabled.def | 1 +
.../mysql-test/rpl/r/rpl_mixed_replace_into.result | 21 +
.../rpl/r/rpl_parallel_tokudb_delete_pk.result | 5 -
...pl_parallel_tokudb_update_pk_uc0_lookup0.result | 5 -
.../rpl/r/rpl_parallel_tokudb_write_pk.result | 2 -
.../mysql-test/rpl/r/rpl_row_replace_into.result | 21 +
.../mysql-test/rpl/r/rpl_stmt_replace_into.result | 21 +
.../mysql-test/rpl/r/rpl_xa_interleave.result | 59 +
.../mysql-test/rpl/t/rpl_mixed_replace_into.test | 25 +
.../mysql-test/rpl/t/rpl_row_replace_into.test | 25 +
.../mysql-test/rpl/t/rpl_stmt_replace_into.test | 25 +
.../tokudb/mysql-test/rpl/t/rpl_xa_interleave.test | 103 +
.../tokudb/include/fast_update_gen_footer.inc | 2 +
.../include/fast_update_gen_footer_silent.inc | 9 +
.../tokudb/include/fast_update_gen_header.inc | 6 +
.../mysql-test/tokudb/include/fast_update_int.inc | 48 +
.../tokudb/include/fast_upsert_gen_header.inc | 6 +
.../mysql-test/tokudb/include/fast_upsert_int.inc | 19 +
.../tokudb/mysql-test/tokudb/include/have_mrr.inc | 0
.../tokudb/include/setup_fast_update_upsert.inc | 8 +
.../tokudb/mysql-test/tokudb/r/compressions.result | 11 +
.../tokudb/r/fast_update_binlog_mixed.result | 225 +-
.../tokudb/r/fast_update_binlog_row.result | 19 +-
.../tokudb/r/fast_update_binlog_statement.result | 222 +-
.../mysql-test/tokudb/r/fast_update_blobs.result | 18253 +---------
.../r/fast_update_blobs_fixed_varchar.result | 33026 ------------------
.../tokudb/r/fast_update_blobs_with_varchar.result | 32771 +-----------------
.../mysql-test/tokudb/r/fast_update_char.result | 60 +-
.../tokudb/r/fast_update_deadlock.result | 19 +-
.../tokudb/r/fast_update_decr_floor.result | 314 +-
.../r/fast_update_disable_slow_update.result | 7 -
.../mysql-test/tokudb/r/fast_update_error.result | 12 +-
.../mysql-test/tokudb/r/fast_update_int.result | 562 +-
.../tokudb/r/fast_update_int_bounds.result | 52 +-
.../mysql-test/tokudb/r/fast_update_key.result | 54 +-
.../mysql-test/tokudb/r/fast_update_sqlmode.result | 21 +-
.../tokudb/r/fast_update_uint_bounds.result | 36 +-
.../mysql-test/tokudb/r/fast_update_varchar.result | 13575 +-------
.../mysql-test/tokudb/r/fast_upsert_bin_pad.result | Bin 659 -> 738 bytes
.../mysql-test/tokudb/r/fast_upsert_char.result | 24 +-
.../tokudb/r/fast_upsert_deadlock.result | 19 +-
.../mysql-test/tokudb/r/fast_upsert_int.result | 428 +-
.../mysql-test/tokudb/r/fast_upsert_key.result | 43 +-
.../mysql-test/tokudb/r/fast_upsert_sqlmode.result | 23 +-
.../mysql-test/tokudb/r/fast_upsert_values.result | 18 +-
.../tokudb/mysql-test/tokudb/r/tokudb_mrr.result | 326 +
storage/tokudb/mysql-test/tokudb/suite.pm | 6 +
.../tokudb/mysql-test/tokudb/t/compressions.test | 68 +
storage/tokudb/mysql-test/tokudb/t/disabled.def | 24 -
.../tokudb/t/fast_update_binlog_mixed-master.opt | 2 +
.../tokudb/t/fast_update_binlog_mixed.test | 15 +-
.../tokudb/t/fast_update_binlog_row-master.opt | 2 +
.../tokudb/t/fast_update_binlog_row.test | 19 +-
.../t/fast_update_binlog_statement-master.opt | 2 +
.../tokudb/t/fast_update_binlog_statement.test | 15 +-
.../mysql-test/tokudb/t/fast_update_blobs.py | 57 -
.../mysql-test/tokudb/t/fast_update_blobs.test | 18575 +----------
.../tokudb/t/fast_update_blobs_fixed_varchar.py | 63 -
.../tokudb/t/fast_update_blobs_fixed_varchar.test | 33287 -------------------
.../tokudb/t/fast_update_blobs_with_varchar.py | 62 -
.../tokudb/t/fast_update_blobs_with_varchar.test | 33115 +-----------------
.../mysql-test/tokudb/t/fast_update_char.test | 66 +-
.../mysql-test/tokudb/t/fast_update_deadlock.test | 21 +-
.../mysql-test/tokudb/t/fast_update_decr_floor.py | 58 -
.../tokudb/t/fast_update_decr_floor.test | 409 +-
.../tokudb/t/fast_update_disable_slow_update.test | 17 -
.../mysql-test/tokudb/t/fast_update_error.test | 16 +-
.../tokudb/mysql-test/tokudb/t/fast_update_int.py | 77 -
.../mysql-test/tokudb/t/fast_update_int.test | 682 +-
.../tokudb/t/fast_update_int_bounds.test | 55 +-
.../mysql-test/tokudb/t/fast_update_key.test | 63 +-
.../mysql-test/tokudb/t/fast_update_sqlmode.test | 25 +-
.../tokudb/t/fast_update_uint_bounds.test | 42 +-
.../mysql-test/tokudb/t/fast_update_varchar.py | 63 -
.../mysql-test/tokudb/t/fast_update_varchar.test | 7390 +---
.../mysql-test/tokudb/t/fast_upsert_bin_pad.test | 19 +-
.../mysql-test/tokudb/t/fast_upsert_char.test | 27 +-
.../mysql-test/tokudb/t/fast_upsert_deadlock.test | 22 +-
.../tokudb/mysql-test/tokudb/t/fast_upsert_int.py | 50 -
.../mysql-test/tokudb/t/fast_upsert_int.test | 486 +-
.../mysql-test/tokudb/t/fast_upsert_key.test | 46 +-
.../mysql-test/tokudb/t/fast_upsert_sqlmode.test | 27 +-
.../mysql-test/tokudb/t/fast_upsert_values.test | 21 +-
storage/tokudb/mysql-test/tokudb/t/tokudb_mrr.test | 73 +
.../tokudb/mysql-test/tokudb_bugs/r/PS-3773.result | 8 +
.../r/alter_table_comment_rebuild_data.result | 177 +
.../tokudb_bugs/r/rpl_mixed_replace_into.result | 21 -
.../tokudb_bugs/r/rpl_row_replace_into.result | 21 -
.../tokudb_bugs/r/rpl_stmt_replace_into.result | 21 -
.../tokudb/mysql-test/tokudb_bugs/t/PS-3773.test | 26 +
.../t/alter_table_comment_rebuild_data.test | 188 +
.../tokudb_bugs/t/rpl_mixed_replace_into.test | 25 -
.../tokudb_bugs/t/rpl_row_replace_into.test | 25 -
.../tokudb_bugs/t/rpl_stmt_replace_into.test | 25 -
storage/tokudb/tokudb_debug.h | 5 -
storage/tokudb/tokudb_dir_cmd.h | 6 +-
storage/tokudb/tokudb_information_schema.cc | 74 +-
storage/tokudb/tokudb_sysvars.cc | 122 +-
storage/tokudb/tokudb_sysvars.h | 16 +-
storage/tokudb/tokudb_thread.h | 26 +-
storage/tokudb/tokudb_update_fun.cc | 230 +-
storage/xtradb/btr/btr0scrub.cc | 2 +-
storage/xtradb/buf/buf0buddy.cc | 2 +-
storage/xtradb/buf/buf0buf.cc | 4 +-
storage/xtradb/buf/buf0dump.cc | 6 +-
storage/xtradb/buf/buf0lru.cc | 2 +-
storage/xtradb/dict/dict0stats_bg.cc | 14 +-
storage/xtradb/fil/fil0crypt.cc | 2 +-
storage/xtradb/fts/fts0ast.cc | 9 +-
storage/xtradb/fts/fts0fts.cc | 76 +-
storage/xtradb/fts/fts0opt.cc | 175 +-
storage/xtradb/fts/fts0pars.cc | 16 +-
storage/xtradb/fts/fts0pars.y | 16 +-
storage/xtradb/fts/fts0que.cc | 17 +-
storage/xtradb/handler/ha_innodb.cc | 81 +-
storage/xtradb/handler/handler0alter.cc | 139 +-
storage/xtradb/handler/i_s.cc | 19 +-
storage/xtradb/include/dict0mem.h | 3 +
storage/xtradb/include/fts0ast.h | 6 +-
storage/xtradb/include/fts0fts.h | 8 +-
storage/xtradb/include/fts0priv.h | 18 +-
storage/xtradb/include/row0ftsort.h | 10 +-
storage/xtradb/include/univ.i | 3 +-
storage/xtradb/lock/lock0lock.cc | 5 +-
storage/xtradb/log/log0log.cc | 13 +-
storage/xtradb/row/row0ftsort.cc | 1 -
storage/xtradb/row/row0import.cc | 82 +-
storage/xtradb/row/row0log.cc | 12 +-
storage/xtradb/row/row0mysql.cc | 19 +-
storage/xtradb/srv/srv0srv.cc | 14 +-
storage/xtradb/trx/trx0purge.cc | 18 +-
storage/xtradb/trx/trx0sys.cc | 12 +-
storage/xtradb/trx/trx0trx.cc | 9 +-
storage/xtradb/trx/trx0undo.cc | 3 +-
strings/ctype-bin.c | 8 +-
strings/ctype-euc_kr.c | 4 +-
strings/ctype-mb.c | 100 +-
strings/ctype-simple.c | 22 +-
strings/ctype-ucs2.c | 78 +-
strings/ctype-ujis.c | 8 +-
strings/ctype-utf8.c | 20 +-
support-files/CMakeLists.txt | 2 +
unittest/sql/mf_iocache-t.cc | 4 +-
563 files changed, 12240 insertions(+), 198922 deletions(-)
diff --cc mysql-test/r/stat_tables.result
index 7d3012f,224c734..0000000
deleted file mode 100644,100644
--- a/mysql-test/r/stat_tables.result
+++ /dev/null
@@@ -1,595 -1,661 +1,0 @@@
--select @@global.use_stat_tables;
--@@global.use_stat_tables
--COMPLEMENTARY
--select @@session.use_stat_tables;
--@@session.use_stat_tables
--COMPLEMENTARY
--set @save_use_stat_tables=@@use_stat_tables;
--set use_stat_tables='preferably';
--DROP DATABASE IF EXISTS dbt3_s001;
--CREATE DATABASE dbt3_s001;
--use dbt3_s001;
--set @save_optimizer_switch=@@optimizer_switch;
--set optimizer_switch='extended_keys=off';
--select * from mysql.table_stats;
--db_name table_name cardinality
--dbt3_s001 customer 150
--dbt3_s001 lineitem 6005
--dbt3_s001 nation 25
--dbt3_s001 orders 1500
--dbt3_s001 part 200
--dbt3_s001 partsupp 700
--dbt3_s001 region 5
--dbt3_s001 supplier 10
--select * from mysql.index_stats;
--db_name table_name index_name prefix_arity avg_frequency
--dbt3_s001 customer PRIMARY 1 1.0000
--dbt3_s001 customer i_c_nationkey 1 6.0000
--dbt3_s001 lineitem PRIMARY 1 4.0033
--dbt3_s001 lineitem PRIMARY 2 1.0000
--dbt3_s001 lineitem i_l_shipdate 1 2.6500
--dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250
--dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786
--dbt3_s001 lineitem i_l_partkey 1 30.0250
--dbt3_s001 lineitem i_l_suppkey 1 600.5000
--dbt3_s001 lineitem i_l_receiptdate 1 2.6477
--dbt3_s001 lineitem i_l_orderkey 1 4.0033
--dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033
--dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404
--dbt3_s001 lineitem i_l_commitdate 1 2.7160
--dbt3_s001 nation PRIMARY 1 1.0000
--dbt3_s001 nation i_n_regionkey 1 5.0000
--dbt3_s001 orders PRIMARY 1 1.0000
--dbt3_s001 orders i_o_orderdate 1 1.3321
--dbt3_s001 orders i_o_custkey 1 15.0000
--dbt3_s001 part PRIMARY 1 1.0000
--dbt3_s001 part i_p_retailprice 1 1.0000
--dbt3_s001 partsupp PRIMARY 1 3.5000
--dbt3_s001 partsupp PRIMARY 2 1.0000
--dbt3_s001 partsupp i_ps_partkey 1 3.5000
--dbt3_s001 partsupp i_ps_suppkey 1 70.0000
--dbt3_s001 region PRIMARY 1 1.0000
--dbt3_s001 supplier PRIMARY 1 1.0000
--dbt3_s001 supplier i_s_nationkey 1 1.1111
--set optimizer_switch=@save_optimizer_switch;
--set @save_optimizer_switch=@@optimizer_switch;
--set optimizer_switch='index_condition_pushdown=off';
--EXPLAIN select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
--from customer, orders, lineitem, supplier, nation, region
--where c_custkey = o_custkey and l_orderkey = o_orderkey
--and l_suppkey = s_suppkey and c_nationkey = s_nationkey
--and s_nationkey = n_nationkey and n_regionkey = r_regionkey
--and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
-- and o_orderdate < date '1995-01-01' + interval '1' year
--group by n_name
--order by revenue desc;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 179 Using where; Using temporary; Using filesort
--1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where
--1 SIMPLE nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1
--1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
--1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
--1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where
--select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
--from customer, orders, lineitem, supplier, nation, region
--where c_custkey = o_custkey and l_orderkey = o_orderkey
--and l_suppkey = s_suppkey and c_nationkey = s_nationkey
--and s_nationkey = n_nationkey and n_regionkey = r_regionkey
--and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
-- and o_orderdate < date '1995-01-01' + interval '1' year
--group by n_name
--order by revenue desc;
--n_name revenue
--PERU 321915.8715
--ARGENTINA 69817.1451
--set optimizer_switch=@save_optimizer_switch;
--delete from mysql.index_stats;
--select * from mysql.table_stats;
--db_name table_name cardinality
--dbt3_s001 customer 150
--dbt3_s001 lineitem 6005
--dbt3_s001 nation 25
--dbt3_s001 orders 1500
--dbt3_s001 part 200
--dbt3_s001 partsupp 700
--dbt3_s001 region 5
--dbt3_s001 supplier 10
--select * from mysql.index_stats;
--db_name table_name index_name prefix_arity avg_frequency
--dbt3_s001 customer PRIMARY 1 1.0000
--dbt3_s001 customer i_c_nationkey 1 6.0000
--dbt3_s001 lineitem PRIMARY 1 4.0033
--dbt3_s001 lineitem PRIMARY 2 1.0000
--dbt3_s001 lineitem i_l_shipdate 1 2.6500
--dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250
--dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786
--dbt3_s001 lineitem i_l_partkey 1 30.0250
--dbt3_s001 lineitem i_l_suppkey 1 600.5000
--dbt3_s001 lineitem i_l_receiptdate 1 2.6477
--dbt3_s001 lineitem i_l_orderkey 1 4.0033
--dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033
--dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404
--dbt3_s001 lineitem i_l_commitdate 1 2.7160
--dbt3_s001 nation PRIMARY 1 1.0000
--dbt3_s001 nation i_n_regionkey 1 5.0000
--dbt3_s001 orders PRIMARY 1 1.0000
--dbt3_s001 orders i_o_orderdate 1 1.3321
--dbt3_s001 orders i_o_custkey 1 15.0000
--dbt3_s001 part PRIMARY 1 1.0000
--dbt3_s001 part i_p_retailprice 1 1.0000
--dbt3_s001 partsupp PRIMARY 1 3.5000
--dbt3_s001 partsupp PRIMARY 2 1.0000
--dbt3_s001 partsupp i_ps_partkey 1 3.5000
--dbt3_s001 partsupp i_ps_suppkey 1 70.0000
--dbt3_s001 region PRIMARY 1 1.0000
--dbt3_s001 supplier PRIMARY 1 1.0000
--dbt3_s001 supplier i_s_nationkey 1 1.1111
--select * from mysql.table_stats where table_name='orders';
--db_name table_name cardinality
--dbt3_s001 orders 1500
--select * from mysql.index_stats where table_name='orders';
--db_name table_name index_name prefix_arity avg_frequency
--dbt3_s001 orders PRIMARY 1 1.0000
--dbt3_s001 orders i_o_orderdate 1 1.3321
--dbt3_s001 orders i_o_custkey 1 15.0000
--select (select cardinality from mysql.table_stats where table_name='orders') /
--(select avg_frequency from mysql.index_stats
--where index_name='i_o_orderdate' and prefix_arity=1) as n_distinct;
--n_distinct
--1126.0416
--select count(distinct o_orderdate) from orders;
--count(distinct o_orderdate)
--1126
--select (select cardinality from mysql.table_stats where table_name='orders') /
--(select avg_frequency from mysql.index_stats
--where index_name='i_o_custkey' and prefix_arity=1) as n_distinct;
--n_distinct
--100.0000
--select count(distinct o_custkey) from orders;
--count(distinct o_custkey)
--100
--show index from orders;
--Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
--orders 0 PRIMARY 1 o_orderkey A 1500 NULL NULL BTREE
--orders 1 i_o_orderdate 1 o_orderDATE A 1126 NULL NULL YES BTREE
--orders 1 i_o_custkey 1 o_custkey A 100 NULL NULL YES BTREE
--select index_name, column_name, cardinality from information_schema.statistics
--where table_name='orders';
--index_name column_name cardinality
--PRIMARY o_orderkey 1500
--i_o_orderdate o_orderDATE 1126
--i_o_custkey o_custkey 100
--set @save_optimizer_switch=@@optimizer_switch;
--set optimizer_switch='index_condition_pushdown=off';
--EXPLAIN select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
--from customer, orders, lineitem, supplier, nation, region
--where c_custkey = o_custkey and l_orderkey = o_orderkey
--and l_suppkey = s_suppkey and c_nationkey = s_nationkey
--and s_nationkey = n_nationkey and n_regionkey = r_regionkey
--and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
-- and o_orderdate < date '1995-01-01' + interval '1' year
--group by n_name
--order by revenue desc;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 179 Using where; Using temporary; Using filesort
--1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where
--1 SIMPLE nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1
--1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
--1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
--1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where
--select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
--from customer, orders, lineitem, supplier, nation, region
--where c_custkey = o_custkey and l_orderkey = o_orderkey
--and l_suppkey = s_suppkey and c_nationkey = s_nationkey
--and s_nationkey = n_nationkey and n_regionkey = r_regionkey
--and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
-- and o_orderdate < date '1995-01-01' + interval '1' year
--group by n_name
--order by revenue desc;
--n_name revenue
--PERU 321915.8715
--ARGENTINA 69817.1451
--set optimizer_switch=@save_optimizer_switch;
--EXPLAIN select o_year,
--sum(case when nation = 'UNITED STATES' then volume else 0 end) /
--sum(volume) as mkt_share
--from (select extract(year from o_orderdate) as o_year,
--l_extendedprice * (1-l_discount) as volume,
--n2.n_name as nation
--from part, supplier, lineitem, orders, customer,
--nation n1, nation n2, region
--where p_partkey = l_partkey and s_suppkey = l_suppkey
--and l_orderkey = o_orderkey and o_custkey = c_custkey
--and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
--and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
--and o_orderdate between date '1995-01-01' and date '1996-12-31'
-- and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
--group by o_year
--order by o_year;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE orders ALL PRIMARY,i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where; Using temporary; Using filesort
--1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where
--1 SIMPLE n1 eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1
--1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
--1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
--1 SIMPLE part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where
--1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where
--1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1
--select o_year,
--sum(case when nation = 'UNITED STATES' then volume else 0 end) /
--sum(volume) as mkt_share
--from (select extract(year from o_orderdate) as o_year,
--l_extendedprice * (1-l_discount) as volume,
--n2.n_name as nation
--from part, supplier, lineitem, orders, customer,
--nation n1, nation n2, region
--where p_partkey = l_partkey and s_suppkey = l_suppkey
--and l_orderkey = o_orderkey and o_custkey = c_custkey
--and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
--and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
--and o_orderdate between date '1995-01-01' and date '1996-12-31'
-- and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
--group by o_year
--order by o_year;
--o_year mkt_share
--1995 0.4495521838895718
--1996 0.024585468215352495
--EXPLAIN select nation, o_year, sum(amount) as sum_profit
--from (select n_name as nation,
--extract(year from o_orderdate) as o_year,
--l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
--from part, supplier, lineitem, partsupp, orders, nation
--where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
--and ps_partkey = l_partkey and p_partkey = l_partkey
--and o_orderkey = l_orderkey and s_nationkey = n_nationkey
--and p_name like '%green%') as profit
--group by nation, o_year
--order by nation, o_year desc;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE supplier ALL PRIMARY,i_s_nationkey NULL NULL NULL 10 Using where; Using temporary; Using filesort
--1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1
--1 SIMPLE partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_suppkey 4 dbt3_s001.supplier.s_suppkey 70
--1 SIMPLE part eq_ref PRIMARY PRIMARY 4 dbt3_s001.partsupp.ps_partkey 1 Using where
--1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.supplier.s_suppkey 8
--1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
--select nation, o_year, sum(amount) as sum_profit
--from (select n_name as nation,
--extract(year from o_orderdate) as o_year,
--l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
--from part, supplier, lineitem, partsupp, orders, nation
--where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
--and ps_partkey = l_partkey and p_partkey = l_partkey
--and o_orderkey = l_orderkey and s_nationkey = n_nationkey
--and p_name like '%green%') as profit
--group by nation, o_year
--order by nation, o_year desc;
--nation o_year sum_profit
--ARGENTINA 1997 18247.873399999993
--ARGENTINA 1996 7731.089399999995
--ARGENTINA 1995 134490.5697
--ARGENTINA 1994 36767.101500000004
--ARGENTINA 1993 35857.08
--ARGENTINA 1992 35740
--ETHIOPIA 1998 2758.7801999999992
--ETHIOPIA 1997 19419.294599999997
--ETHIOPIA 1995 51231.87439999999
--ETHIOPIA 1994 3578.9478999999974
--ETHIOPIA 1992 1525.8234999999986
--IRAN 1998 37817.229600000006
--IRAN 1997 52643.77359999999
--IRAN 1996 70143.7761
--IRAN 1995 84094.58260000001
--IRAN 1994 18140.925599999995
--IRAN 1993 78655.1676
--IRAN 1992 87142.23960000002
--IRAQ 1998 22860.8082
--IRAQ 1997 93676.24359999999
--IRAQ 1996 45103.3242
--IRAQ 1994 36010.728599999995
--IRAQ 1993 33221.9399
--IRAQ 1992 47755.05900000001
--KENYA 1998 44194.831999999995
--KENYA 1997 57578.36259999999
--KENYA 1996 59195.90210000001
--KENYA 1995 79262.6278
--KENYA 1994 102360.66609999999
--KENYA 1993 128422.0196
--KENYA 1992 181517.2089
--MOROCCO 1998 41797.823199999984
--MOROCCO 1997 23685.801799999994
--MOROCCO 1996 62115.19579999998
--MOROCCO 1995 42442.64300000001
--MOROCCO 1994 48655.878000000004
--MOROCCO 1993 22926.744400000003
--MOROCCO 1992 32239.8088
--PERU 1998 86999.36459999997
--PERU 1997 121110.41070000001
--PERU 1996 177040.40759999995
--PERU 1995 122247.94520000002
--PERU 1994 88046.25329999998
--PERU 1993 49379.813799999996
--PERU 1992 80646.86050000001
--UNITED KINGDOM 1998 50577.25560000001
--UNITED KINGDOM 1997 114288.8605
--UNITED KINGDOM 1996 147684.46480000002
--UNITED KINGDOM 1995 225267.65759999998
--UNITED KINGDOM 1994 140595.5864
--UNITED KINGDOM 1993 322548.49210000003
--UNITED KINGDOM 1992 67747.88279999999
--UNITED STATES 1998 3957.0431999999996
--UNITED STATES 1997 94729.5704
--UNITED STATES 1996 79297.85670000002
--UNITED STATES 1995 62201.23360000001
--UNITED STATES 1994 43075.629899999985
--UNITED STATES 1993 27168.486199999996
--UNITED STATES 1992 34092.366
--set @save_optimizer_switch=@@optimizer_switch;
--set optimizer_switch='extended_keys=on';
--EXPLAIN select o_orderkey, p_partkey
--from part, lineitem, orders
--where p_retailprice > 1100 and o_orderdate='1997-01-01'
--and o_orderkey=l_orderkey and p_partkey=l_partkey;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const 1
--1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
--1 SIMPLE part eq_ref PRIMARY,i_p_retailprice PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where
--select o_orderkey, p_partkey
--from part, lineitem, orders
--where p_retailprice > 1100 and o_orderdate='1997-01-01'
--and o_orderkey=l_orderkey and p_partkey=l_partkey;
--o_orderkey p_partkey
--5895 200
--set optimizer_switch=@save_optimizer_switch;
--DROP DATABASE dbt3_s001;
--use test;
--#
--# Bug mdev-473: ANALYZE table locked for write
--#
--set use_stat_tables='complementary';
--create table t1 (i int);
--lock table t1 write;
--analyze table t1;
--Table Op Msg_type Msg_text
--test.t1 analyze status Engine-independent statistics collected
--test.t1 analyze status Table is already up to date
--alter table t1 add column a varchar(8);
--drop table t1;
--#
--# Bug mdev-487: memory leak in ANALYZE with stat tables
--#
--SET use_stat_tables = 'preferably';
--CREATE TABLE t1 (a INT);
--INSERT INTO t1 VALUES (1),(2);
--DELETE FROM t1 WHERE a=1;
--ANALYZE TABLE t1;
--Table Op Msg_type Msg_text
--test.t1 analyze status Engine-independent statistics collected
--test.t1 analyze status OK
--DROP TABLE t1;
--#
--# Bug mdev-518: corrupted/missing statistical tables
--#
--CREATE TABLE t1 (i int) ENGINE=MyISAM;
--INSERT INTO t1 VALUES (1),(2);
--FLUSH TABLE t1;
--SET use_stat_tables='never';
--EXPLAIN SELECT * FROM t1;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE t1 ALL NULL NULL NULL NULL 2
--FLUSH TABLES;
--SET use_stat_tables='preferably';
--EXPLAIN SELECT * FROM t1;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE t1 ALL NULL NULL NULL NULL 2
--DROP TABLE t1;
--set use_stat_tables=@save_use_stat_tables;
--#
--# Bug mdev-5204: invalid impossible where after reading const tables
--# when use_stat_tables = 'preferably'
--#
--set use_stat_tables = 'preferably';
--CREATE TABLE t1 (id int PRIMARY KEY) ENGINE=MyISAM;
--INSERT INTO t1 VALUES (1),(2);
--ANALYZE TABLE t1;
--Table Op Msg_type Msg_text
--test.t1 analyze status Engine-independent statistics collected
--test.t1 analyze status OK
--CREATE TABLE t2 (name char(3)) ENGINE=MyISAM;
--ANALYZE TABLE t2;
--Table Op Msg_type Msg_text
--test.t2 analyze status Engine-independent statistics collected
--test.t2 analyze status Table is already up to date
--INSERT INTO t2 VALUES ('USA'),('AUS');
--SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
--id name
--1 AUS
--EXPLAIN
--SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
--1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where
--ANALYZE TABLE t2;
--Table Op Msg_type Msg_text
--test.t2 analyze status Engine-independent statistics collected
--test.t2 analyze status OK
--SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
--id name
--1 AUS
--EXPLAIN
--SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
--1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
--DROP TABLE t1,t2;
--#
--# MDEV-7370: Server deadlocks on renaming a table for which persistent statistics exists
--#
--drop database if exists db1;
--drop database if exists db1;
--create database db1;
--create database db2;
--use db1;
--#
--# First, run the original testcase:
--#
--create table t1 (i int);
--insert into t1 values (10),(20);
--analyze table t1 persistent for all;
--Table Op Msg_type Msg_text
--db1.t1 analyze status Engine-independent statistics collected
--db1.t1 analyze status OK
--rename table t1 to db2.t1;
--# Verify that stats in the old database are gone:
--select * from mysql.column_stats where db_name='db1' and table_name='t1';
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--select * from mysql.table_stats where db_name='db1' and table_name='t1';
--db_name table_name cardinality
--# Verify that stats are present in the new database:
--select * from mysql.column_stats where db_name='db2' and table_name='t1';
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--db2 t1 i 10 20 0.0000 4.0000 1.0000 0 NULL NULL
--select * from mysql.table_stats where db_name='db2' and table_name='t1';
--db_name table_name cardinality
--db2 t1 2
--#
--# Now, try with more than one column and with indexes:
--#
--use test;
--create table t1(a int primary key);
--insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
--use db1;
--create table t2 (a int, b int, c int, key IDX1(a), key IDX2(a,b));
--insert into t2 select a/10, a/2, a from test.t1;
--analyze table t2 persistent for all;
--Table Op Msg_type Msg_text
--db1.t2 analyze status Engine-independent statistics collected
--db1.t2 analyze status Table is already up to date
--alter table t2 rename db2.t2;
--# Verify that stats in the old database are gone:
--select * from mysql.table_stats where db_name='db1' and table_name='t2';
--db_name table_name cardinality
--select * from mysql.column_stats where db_name='db1' and table_name='t2';
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--select * from mysql.index_stats where db_name='db1' and table_name='t2';
--db_name table_name index_name prefix_arity avg_frequency
--# Verify that stats are present in the new database:
--select * from mysql.table_stats where db_name='db2' and table_name='t2';
--db_name table_name cardinality
--db2 t2 10
--select * from mysql.column_stats where db_name='db2' and table_name='t2';
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--db2 t2 a 0 1 0.0000 4.0000 5.0000 0 NULL NULL
--db2 t2 b 0 5 0.0000 4.0000 1.6667 0 NULL NULL
--db2 t2 c 0 9 0.0000 4.0000 1.0000 0 NULL NULL
--select * from mysql.index_stats where db_name='db2' and table_name='t2';
--db_name table_name index_name prefix_arity avg_frequency
--db2 t2 IDX1 1 5.0000
--db2 t2 IDX2 1 5.0000
--db2 t2 IDX2 2 1.6667
--use db2;
--#
--# Now, rename within the same database and verify:
--#
--rename table t2 to t3;
--# No stats under old name:
--select * from mysql.table_stats where db_name='db2' and table_name='t2';
--db_name table_name cardinality
--select * from mysql.column_stats where db_name='db2' and table_name='t2';
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--select * from mysql.index_stats where db_name='db2' and table_name='t2';
--db_name table_name index_name prefix_arity avg_frequency
--# Stats under the new name:
--select * from mysql.table_stats where db_name='db2' and table_name='t3';
--db_name table_name cardinality
--db2 t3 10
--select * from mysql.column_stats where db_name='db2' and table_name='t3';
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--db2 t3 a 0 1 0.0000 4.0000 5.0000 0 NULL NULL
--db2 t3 b 0 5 0.0000 4.0000 1.6667 0 NULL NULL
--db2 t3 c 0 9 0.0000 4.0000 1.0000 0 NULL NULL
--select * from mysql.index_stats where db_name='db2' and table_name='t3';
--db_name table_name index_name prefix_arity avg_frequency
--db2 t3 IDX1 1 5.0000
--db2 t3 IDX2 1 5.0000
--db2 t3 IDX2 2 1.6667
--use test;
--drop database db1;
--drop database db2;
--drop table t1;
--#
--# MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed
--#
--SET use_stat_tables = PREFERABLY;
--SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' );
--CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' )
--NULL
-set use_stat_tables=@save_use_stat_tables;
-#
-# MDEV-16757: manual addition of min/max statistics for BLOB
-#
-SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-Table Op Msg_type Msg_text
-test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
-test.t1 analyze status OK
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-DELETE FROM mysql.column_stats
-WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
-('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-SELECT pk FROM t1;
-pk
-1
-2
-DROP TABLE t1;
-set use_stat_tables=@save_use_stat_tables;
-#
-# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
-#
-SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-Table Op Msg_type Msg_text
-test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze status OK
-SELECT * FROM t1;
-pk c
-1 foo
-2 bar
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
-SELECT * FROM t1;
-pk a
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-DROP TABLE t1;
-set use_stat_tables=@save_use_stat_tables;
-#
-# MDEV-17023: Crash during read_histogram_for_table with optimizer_use_condition_selectivity set to 4
-#
-set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
-set @@optimizer_use_condition_selectivity=4;
-set @@use_stat_tables= PREFERABLY;
-explain
-SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE PROFILING ALL NULL NULL NULL NULL NULL
-1 SIMPLE user ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
-set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
--set use_stat_tables=@save_use_stat_tables;
--#
--# MDEV-16757: manual addition of min/max statistics for BLOB
--#
--SET use_stat_tables= PREFERABLY;
--CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
--INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
--ANALYZE TABLE t1;
--Table Op Msg_type Msg_text
--test.t1 analyze status Engine-independent statistics collected
--test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
--test.t1 analyze status OK
--SELECT * FROM mysql.column_stats;
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
--DELETE FROM mysql.column_stats
--WHERE db_name='test' AND table_name='t1' AND column_name='t';
--INSERT INTO mysql.column_stats VALUES
--('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
--SELECT * FROM mysql.column_stats;
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
--test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
--SELECT pk FROM t1;
--pk
--1
--2
--DROP TABLE t1;
--set use_stat_tables=@save_use_stat_tables;
--#
--# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
--#
--SET use_stat_tables= PREFERABLY;
--CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
--INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
--ANALYZE TABLE t1;
--Table Op Msg_type Msg_text
--test.t1 analyze status Engine-independent statistics collected
--test.t1 analyze status OK
--SELECT * FROM t1;
--pk c
--1 foo
--2 bar
--SELECT * FROM mysql.column_stats;
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
--test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
--CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
--SELECT * FROM t1;
--pk a
--SELECT * FROM mysql.column_stats;
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--DROP TABLE t1;
--set use_stat_tables=@save_use_stat_tables;
--#
--# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
--#
--SET use_stat_tables= PREFERABLY;
--CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
--INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
--ANALYZE TABLE t1;
--Table Op Msg_type Msg_text
--test.t1 analyze status Engine-independent statistics collected
--test.t1 analyze status OK
--CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
--SELECT MAX(pk) FROM t1;
--MAX(pk)
--NULL
--DROP TABLE t1;
--set use_stat_tables=@save_use_stat_tables;
diff --cc mysql-test/r/stat_tables_innodb.result
index 7e44b87,ba1dee3..0000000
deleted file mode 100644,100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ /dev/null
@@@ -1,624 -1,690 +1,0 @@@
--SET SESSION STORAGE_ENGINE='InnoDB';
--set @save_optimizer_switch_for_stat_tables_test=@@optimizer_switch;
--set optimizer_switch='extended_keys=on';
--select @@global.use_stat_tables;
--@@global.use_stat_tables
--COMPLEMENTARY
--select @@session.use_stat_tables;
--@@session.use_stat_tables
--COMPLEMENTARY
--set @save_use_stat_tables=@@use_stat_tables;
--set use_stat_tables='preferably';
--DROP DATABASE IF EXISTS dbt3_s001;
--CREATE DATABASE dbt3_s001;
--use dbt3_s001;
--set @save_optimizer_switch=@@optimizer_switch;
--set optimizer_switch='extended_keys=off';
--select * from mysql.table_stats;
--db_name table_name cardinality
--dbt3_s001 customer 150
--dbt3_s001 lineitem 6005
--dbt3_s001 nation 25
--dbt3_s001 orders 1500
--dbt3_s001 part 200
--dbt3_s001 partsupp 700
--dbt3_s001 region 5
--dbt3_s001 supplier 10
--select * from mysql.index_stats;
--db_name table_name index_name prefix_arity avg_frequency
--dbt3_s001 customer PRIMARY 1 1.0000
--dbt3_s001 customer i_c_nationkey 1 6.0000
--dbt3_s001 lineitem PRIMARY 1 4.0033
--dbt3_s001 lineitem PRIMARY 2 1.0000
--dbt3_s001 lineitem i_l_shipdate 1 2.6500
--dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250
--dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786
--dbt3_s001 lineitem i_l_partkey 1 30.0250
--dbt3_s001 lineitem i_l_suppkey 1 600.5000
--dbt3_s001 lineitem i_l_receiptdate 1 2.6477
--dbt3_s001 lineitem i_l_orderkey 1 4.0033
--dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033
--dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404
--dbt3_s001 lineitem i_l_commitdate 1 2.7160
--dbt3_s001 nation PRIMARY 1 1.0000
--dbt3_s001 nation i_n_regionkey 1 5.0000
--dbt3_s001 orders PRIMARY 1 1.0000
--dbt3_s001 orders i_o_orderdate 1 1.3321
--dbt3_s001 orders i_o_custkey 1 15.0000
--dbt3_s001 part PRIMARY 1 1.0000
--dbt3_s001 part i_p_retailprice 1 1.0000
--dbt3_s001 partsupp PRIMARY 1 3.5000
--dbt3_s001 partsupp PRIMARY 2 1.0000
--dbt3_s001 partsupp i_ps_partkey 1 3.5000
--dbt3_s001 partsupp i_ps_suppkey 1 70.0000
--dbt3_s001 region PRIMARY 1 1.0000
--dbt3_s001 supplier PRIMARY 1 1.0000
--dbt3_s001 supplier i_s_nationkey 1 1.1111
--set optimizer_switch=@save_optimizer_switch;
--set @save_optimizer_switch=@@optimizer_switch;
--set optimizer_switch='index_condition_pushdown=off';
--EXPLAIN select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
--from customer, orders, lineitem, supplier, nation, region
--where c_custkey = o_custkey and l_orderkey = o_orderkey
--and l_suppkey = s_suppkey and c_nationkey = s_nationkey
--and s_nationkey = n_nationkey and n_regionkey = r_regionkey
--and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
-- and o_orderdate < date '1995-01-01' + interval '1' year
--group by n_name
--order by revenue desc;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 211 Using where; Using temporary; Using filesort
--1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where
--1 SIMPLE nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1
--1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.customer.c_nationkey 1 Using index
--1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
--1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
--select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
--from customer, orders, lineitem, supplier, nation, region
--where c_custkey = o_custkey and l_orderkey = o_orderkey
--and l_suppkey = s_suppkey and c_nationkey = s_nationkey
--and s_nationkey = n_nationkey and n_regionkey = r_regionkey
--and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
-- and o_orderdate < date '1995-01-01' + interval '1' year
--group by n_name
--order by revenue desc;
--n_name revenue
--PERU 321915.8715
--ARGENTINA 69817.1451
--set optimizer_switch=@save_optimizer_switch;
--delete from mysql.index_stats;
--select * from mysql.table_stats;
--db_name table_name cardinality
--dbt3_s001 customer 150
--dbt3_s001 lineitem 6005
--dbt3_s001 nation 25
--dbt3_s001 orders 1500
--dbt3_s001 part 200
--dbt3_s001 partsupp 700
--dbt3_s001 region 5
--dbt3_s001 supplier 10
--select * from mysql.index_stats;
--db_name table_name index_name prefix_arity avg_frequency
--dbt3_s001 customer PRIMARY 1 1.0000
--dbt3_s001 customer i_c_nationkey 1 6.0000
--dbt3_s001 customer i_c_nationkey 2 1.0000
--dbt3_s001 lineitem PRIMARY 1 4.0033
--dbt3_s001 lineitem PRIMARY 2 1.0000
--dbt3_s001 lineitem i_l_shipdate 1 2.6500
--dbt3_s001 lineitem i_l_shipdate 2 1.0149
--dbt3_s001 lineitem i_l_shipdate 3 1.0000
--dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250
--dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786
--dbt3_s001 lineitem i_l_suppkey_partkey 3 1.0030
--dbt3_s001 lineitem i_l_suppkey_partkey 4 1.0000
--dbt3_s001 lineitem i_l_partkey 1 30.0250
--dbt3_s001 lineitem i_l_partkey 2 1.0089
--dbt3_s001 lineitem i_l_partkey 3 1.0000
--dbt3_s001 lineitem i_l_suppkey 1 600.5000
--dbt3_s001 lineitem i_l_suppkey 2 1.2073
--dbt3_s001 lineitem i_l_suppkey 3 1.0000
--dbt3_s001 lineitem i_l_receiptdate 1 2.6477
--dbt3_s001 lineitem i_l_receiptdate 2 1.0152
--dbt3_s001 lineitem i_l_receiptdate 3 1.0000
--dbt3_s001 lineitem i_l_orderkey 1 4.0033
--dbt3_s001 lineitem i_l_orderkey 2 1.0000
--dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033
--dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404
--dbt3_s001 lineitem i_l_orderkey_quantity 3 1.0000
--dbt3_s001 lineitem i_l_commitdate 1 2.7160
--dbt3_s001 lineitem i_l_commitdate 2 1.0364
--dbt3_s001 lineitem i_l_commitdate 3 1.0000
--dbt3_s001 nation PRIMARY 1 1.0000
--dbt3_s001 nation i_n_regionkey 1 5.0000
--dbt3_s001 nation i_n_regionkey 2 1.0000
--dbt3_s001 orders PRIMARY 1 1.0000
--dbt3_s001 orders i_o_orderdate 1 1.3321
--dbt3_s001 orders i_o_orderdate 2 1.0000
--dbt3_s001 orders i_o_custkey 1 15.0000
--dbt3_s001 orders i_o_custkey 2 1.0000
--dbt3_s001 part PRIMARY 1 1.0000
--dbt3_s001 part i_p_retailprice 1 1.0000
--dbt3_s001 part i_p_retailprice 2 1.0000
--dbt3_s001 partsupp PRIMARY 1 3.5000
--dbt3_s001 partsupp PRIMARY 2 1.0000
--dbt3_s001 partsupp i_ps_partkey 1 3.5000
--dbt3_s001 partsupp i_ps_partkey 2 1.0000
--dbt3_s001 partsupp i_ps_suppkey 1 70.0000
--dbt3_s001 partsupp i_ps_suppkey 2 1.0000
--dbt3_s001 region PRIMARY 1 1.0000
--dbt3_s001 supplier PRIMARY 1 1.0000
--dbt3_s001 supplier i_s_nationkey 1 1.1111
--dbt3_s001 supplier i_s_nationkey 2 1.0000
--select * from mysql.table_stats where table_name='orders';
--db_name table_name cardinality
--dbt3_s001 orders 1500
--select * from mysql.index_stats where table_name='orders';
--db_name table_name index_name prefix_arity avg_frequency
--dbt3_s001 orders PRIMARY 1 1.0000
--dbt3_s001 orders i_o_orderdate 1 1.3321
--dbt3_s001 orders i_o_orderdate 2 1.0000
--dbt3_s001 orders i_o_custkey 1 15.0000
--dbt3_s001 orders i_o_custkey 2 1.0000
--select (select cardinality from mysql.table_stats where table_name='orders') /
--(select avg_frequency from mysql.index_stats
--where index_name='i_o_orderdate' and prefix_arity=1) as n_distinct;
--n_distinct
--1126.0416
--select count(distinct o_orderdate) from orders;
--count(distinct o_orderdate)
--1126
--select (select cardinality from mysql.table_stats where table_name='orders') /
--(select avg_frequency from mysql.index_stats
--where index_name='i_o_custkey' and prefix_arity=1) as n_distinct;
--n_distinct
--100.0000
--select count(distinct o_custkey) from orders;
--count(distinct o_custkey)
--100
--show index from orders;
--Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
--orders 0 PRIMARY 1 o_orderkey A 1500 NULL NULL BTREE
--orders 1 i_o_orderdate 1 o_orderDATE A 1126 NULL NULL YES BTREE
--orders 1 i_o_custkey 1 o_custkey A 100 NULL NULL YES BTREE
--select index_name, column_name, cardinality from information_schema.statistics
--where table_name='orders';
--index_name column_name cardinality
--PRIMARY o_orderkey 1500
--i_o_orderdate o_orderDATE 1126
--i_o_custkey o_custkey 100
--set @save_optimizer_switch=@@optimizer_switch;
--set optimizer_switch='index_condition_pushdown=off';
--EXPLAIN select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
--from customer, orders, lineitem, supplier, nation, region
--where c_custkey = o_custkey and l_orderkey = o_orderkey
--and l_suppkey = s_suppkey and c_nationkey = s_nationkey
--and s_nationkey = n_nationkey and n_regionkey = r_regionkey
--and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
-- and o_orderdate < date '1995-01-01' + interval '1' year
--group by n_name
--order by revenue desc;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 211 Using where; Using temporary; Using filesort
--1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where
--1 SIMPLE nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1
--1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.customer.c_nationkey 1 Using index
--1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
--1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
--select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
--from customer, orders, lineitem, supplier, nation, region
--where c_custkey = o_custkey and l_orderkey = o_orderkey
--and l_suppkey = s_suppkey and c_nationkey = s_nationkey
--and s_nationkey = n_nationkey and n_regionkey = r_regionkey
--and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
-- and o_orderdate < date '1995-01-01' + interval '1' year
--group by n_name
--order by revenue desc;
--n_name revenue
--PERU 321915.8715
--ARGENTINA 69817.1451
--set optimizer_switch=@save_optimizer_switch;
--EXPLAIN select o_year,
--sum(case when nation = 'UNITED STATES' then volume else 0 end) /
--sum(volume) as mkt_share
--from (select extract(year from o_orderdate) as o_year,
--l_extendedprice * (1-l_discount) as volume,
--n2.n_name as nation
--from part, supplier, lineitem, orders, customer,
--nation n1, nation n2, region
--where p_partkey = l_partkey and s_suppkey = l_suppkey
--and l_orderkey = o_orderkey and o_custkey = c_custkey
--and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
--and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
--and o_orderdate between date '1995-01-01' and date '1996-12-31'
-- and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
--group by o_year
--order by o_year;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE orders ALL PRIMARY,i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where; Using temporary; Using filesort
--1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where
--1 SIMPLE n1 eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1
--1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
--1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
--1 SIMPLE part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where
--1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where
--1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1
--select o_year,
--sum(case when nation = 'UNITED STATES' then volume else 0 end) /
--sum(volume) as mkt_share
--from (select extract(year from o_orderdate) as o_year,
--l_extendedprice * (1-l_discount) as volume,
--n2.n_name as nation
--from part, supplier, lineitem, orders, customer,
--nation n1, nation n2, region
--where p_partkey = l_partkey and s_suppkey = l_suppkey
--and l_orderkey = o_orderkey and o_custkey = c_custkey
--and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
--and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
--and o_orderdate between date '1995-01-01' and date '1996-12-31'
-- and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
--group by o_year
--order by o_year;
--o_year mkt_share
--1995 0.4495521838895718
--1996 0.024585468215352495
--EXPLAIN select nation, o_year, sum(amount) as sum_profit
--from (select n_name as nation,
--extract(year from o_orderdate) as o_year,
--l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
--from part, supplier, lineitem, partsupp, orders, nation
--where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
--and ps_partkey = l_partkey and p_partkey = l_partkey
--and o_orderkey = l_orderkey and s_nationkey = n_nationkey
--and p_name like '%green%') as profit
--group by nation, o_year
--order by nation, o_year desc;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE supplier index PRIMARY,i_s_nationkey i_s_nationkey 5 NULL 10 Using where; Using index; Using temporary; Using filesort
--1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1
--1 SIMPLE partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_suppkey 4 dbt3_s001.supplier.s_suppkey 70
--1 SIMPLE part eq_ref PRIMARY PRIMARY 4 dbt3_s001.partsupp.ps_partkey 1 Using where
--1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.supplier.s_suppkey 8
--1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
--select nation, o_year, sum(amount) as sum_profit
--from (select n_name as nation,
--extract(year from o_orderdate) as o_year,
--l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
--from part, supplier, lineitem, partsupp, orders, nation
--where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
--and ps_partkey = l_partkey and p_partkey = l_partkey
--and o_orderkey = l_orderkey and s_nationkey = n_nationkey
--and p_name like '%green%') as profit
--group by nation, o_year
--order by nation, o_year desc;
--nation o_year sum_profit
--ARGENTINA 1997 18247.873399999993
--ARGENTINA 1996 7731.089399999995
--ARGENTINA 1995 134490.5697
--ARGENTINA 1994 36767.101500000004
--ARGENTINA 1993 35857.08
--ARGENTINA 1992 35740
--ETHIOPIA 1998 2758.7801999999992
--ETHIOPIA 1997 19419.294599999997
--ETHIOPIA 1995 51231.87439999999
--ETHIOPIA 1994 3578.9478999999974
--ETHIOPIA 1992 1525.8234999999986
--IRAN 1998 37817.229600000006
--IRAN 1997 52643.77359999999
--IRAN 1996 70143.7761
--IRAN 1995 84094.58260000001
--IRAN 1994 18140.925599999995
--IRAN 1993 78655.1676
--IRAN 1992 87142.23960000002
--IRAQ 1998 22860.8082
--IRAQ 1997 93676.24359999999
--IRAQ 1996 45103.3242
--IRAQ 1994 36010.728599999995
--IRAQ 1993 33221.9399
--IRAQ 1992 47755.05900000001
--KENYA 1998 44194.831999999995
--KENYA 1997 57578.36259999999
--KENYA 1996 59195.90210000001
--KENYA 1995 79262.6278
--KENYA 1994 102360.66609999999
--KENYA 1993 128422.0196
--KENYA 1992 181517.2089
--MOROCCO 1998 41797.823199999984
--MOROCCO 1997 23685.801799999994
--MOROCCO 1996 62115.19579999998
--MOROCCO 1995 42442.64300000001
--MOROCCO 1994 48655.878000000004
--MOROCCO 1993 22926.744400000003
--MOROCCO 1992 32239.8088
--PERU 1998 86999.36459999997
--PERU 1997 121110.41070000001
--PERU 1996 177040.40759999995
--PERU 1995 122247.94520000002
--PERU 1994 88046.25329999998
--PERU 1993 49379.813799999996
--PERU 1992 80646.86050000001
--UNITED KINGDOM 1998 50577.25560000001
--UNITED KINGDOM 1997 114288.8605
--UNITED KINGDOM 1996 147684.46480000002
--UNITED KINGDOM 1995 225267.65759999998
--UNITED KINGDOM 1994 140595.5864
--UNITED KINGDOM 1993 322548.49210000003
--UNITED KINGDOM 1992 67747.88279999999
--UNITED STATES 1998 3957.0431999999996
--UNITED STATES 1997 94729.5704
--UNITED STATES 1996 79297.85670000002
--UNITED STATES 1995 62201.23360000001
--UNITED STATES 1994 43075.629899999985
--UNITED STATES 1993 27168.486199999996
--UNITED STATES 1992 34092.366
--set @save_optimizer_switch=@@optimizer_switch;
--set optimizer_switch='extended_keys=on';
--EXPLAIN select o_orderkey, p_partkey
--from part, lineitem, orders
--where p_retailprice > 1100 and o_orderdate='1997-01-01'
--and o_orderkey=l_orderkey and p_partkey=l_partkey;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE part range PRIMARY,i_p_retailprice i_p_retailprice 9 NULL 1 Using where; Using index
--1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const 1 Using index
--1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity i_l_partkey 9 dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey 1 Using index
--select o_orderkey, p_partkey
--from part, lineitem, orders
--where p_retailprice > 1100 and o_orderdate='1997-01-01'
--and o_orderkey=l_orderkey and p_partkey=l_partkey;
--o_orderkey p_partkey
--5895 200
--set optimizer_switch=@save_optimizer_switch;
--DROP DATABASE dbt3_s001;
--use test;
--#
--# Bug mdev-473: ANALYZE table locked for write
--#
--set use_stat_tables='complementary';
--create table t1 (i int);
--lock table t1 write;
--analyze table t1;
--Table Op Msg_type Msg_text
--test.t1 analyze status Engine-independent statistics collected
--test.t1 analyze status OK
--alter table t1 add column a varchar(8);
--drop table t1;
--#
--# Bug mdev-487: memory leak in ANALYZE with stat tables
--#
--SET use_stat_tables = 'preferably';
--CREATE TABLE t1 (a INT);
--INSERT INTO t1 VALUES (1),(2);
--DELETE FROM t1 WHERE a=1;
--ANALYZE TABLE t1;
--Table Op Msg_type Msg_text
--test.t1 analyze status Engine-independent statistics collected
--test.t1 analyze status OK
--DROP TABLE t1;
--#
--# Bug mdev-518: corrupted/missing statistical tables
--#
--CREATE TABLE t1 (i int) ENGINE=MyISAM;
--INSERT INTO t1 VALUES (1),(2);
--FLUSH TABLE t1;
--SET use_stat_tables='never';
--EXPLAIN SELECT * FROM t1;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE t1 ALL NULL NULL NULL NULL 2
--FLUSH TABLES;
--SET use_stat_tables='preferably';
--EXPLAIN SELECT * FROM t1;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE t1 ALL NULL NULL NULL NULL 2
--DROP TABLE t1;
--set use_stat_tables=@save_use_stat_tables;
--#
--# Bug mdev-5204: invalid impossible where after reading const tables
--# when use_stat_tables = 'preferably'
--#
--set use_stat_tables = 'preferably';
--CREATE TABLE t1 (id int PRIMARY KEY) ENGINE=MyISAM;
--INSERT INTO t1 VALUES (1),(2);
--ANALYZE TABLE t1;
--Table Op Msg_type Msg_text
--test.t1 analyze status Engine-independent statistics collected
--test.t1 analyze status OK
--CREATE TABLE t2 (name char(3)) ENGINE=MyISAM;
--ANALYZE TABLE t2;
--Table Op Msg_type Msg_text
--test.t2 analyze status Engine-independent statistics collected
--test.t2 analyze status Table is already up to date
--INSERT INTO t2 VALUES ('USA'),('AUS');
--SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
--id name
--1 AUS
--EXPLAIN
--SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
--1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where
--ANALYZE TABLE t2;
--Table Op Msg_type Msg_text
--test.t2 analyze status Engine-independent statistics collected
--test.t2 analyze status OK
--SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
--id name
--1 AUS
--EXPLAIN
--SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
--id select_type table type possible_keys key key_len ref rows Extra
--1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
--1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
--DROP TABLE t1,t2;
--#
--# MDEV-7370: Server deadlocks on renaming a table for which persistent statistics exists
--#
--drop database if exists db1;
--drop database if exists db1;
--create database db1;
--create database db2;
--use db1;
--#
--# First, run the original testcase:
--#
--create table t1 (i int);
--insert into t1 values (10),(20);
--analyze table t1 persistent for all;
--Table Op Msg_type Msg_text
--db1.t1 analyze status Engine-independent statistics collected
--db1.t1 analyze status OK
--rename table t1 to db2.t1;
--# Verify that stats in the old database are gone:
--select * from mysql.column_stats where db_name='db1' and table_name='t1';
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--select * from mysql.table_stats where db_name='db1' and table_name='t1';
--db_name table_name cardinality
--# Verify that stats are present in the new database:
--select * from mysql.column_stats where db_name='db2' and table_name='t1';
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--db2 t1 i 10 20 0.0000 4.0000 1.0000 0 NULL NULL
--select * from mysql.table_stats where db_name='db2' and table_name='t1';
--db_name table_name cardinality
--db2 t1 2
--#
--# Now, try with more than one column and with indexes:
--#
--use test;
--create table t1(a int primary key);
--insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
--use db1;
--create table t2 (a int, b int, c int, key IDX1(a), key IDX2(a,b));
--insert into t2 select a/10, a/2, a from test.t1;
--analyze table t2 persistent for all;
--Table Op Msg_type Msg_text
--db1.t2 analyze status Engine-independent statistics collected
--db1.t2 analyze status OK
--alter table t2 rename db2.t2;
--# Verify that stats in the old database are gone:
--select * from mysql.table_stats where db_name='db1' and table_name='t2';
--db_name table_name cardinality
--select * from mysql.column_stats where db_name='db1' and table_name='t2';
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--select * from mysql.index_stats where db_name='db1' and table_name='t2';
--db_name table_name index_name prefix_arity avg_frequency
--# Verify that stats are present in the new database:
--select * from mysql.table_stats where db_name='db2' and table_name='t2';
--db_name table_name cardinality
--db2 t2 10
--select * from mysql.column_stats where db_name='db2' and table_name='t2';
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--db2 t2 a 0 1 0.0000 4.0000 5.0000 0 NULL NULL
--db2 t2 b 0 5 0.0000 4.0000 1.6667 0 NULL NULL
--db2 t2 c 0 9 0.0000 4.0000 1.0000 0 NULL NULL
--select * from mysql.index_stats where db_name='db2' and table_name='t2';
--db_name table_name index_name prefix_arity avg_frequency
--db2 t2 IDX1 1 5.0000
--db2 t2 IDX2 1 5.0000
--db2 t2 IDX2 2 1.6667
--use db2;
--#
--# Now, rename within the same database and verify:
--#
--rename table t2 to t3;
--# No stats under old name:
--select * from mysql.table_stats where db_name='db2' and table_name='t2';
--db_name table_name cardinality
--select * from mysql.column_stats where db_name='db2' and table_name='t2';
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--select * from mysql.index_stats where db_name='db2' and table_name='t2';
--db_name table_name index_name prefix_arity avg_frequency
--# Stats under the new name:
--select * from mysql.table_stats where db_name='db2' and table_name='t3';
--db_name table_name cardinality
--db2 t3 10
--select * from mysql.column_stats where db_name='db2' and table_name='t3';
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--db2 t3 a 0 1 0.0000 4.0000 5.0000 0 NULL NULL
--db2 t3 b 0 5 0.0000 4.0000 1.6667 0 NULL NULL
--db2 t3 c 0 9 0.0000 4.0000 1.0000 0 NULL NULL
--select * from mysql.index_stats where db_name='db2' and table_name='t3';
--db_name table_name index_name prefix_arity avg_frequency
--db2 t3 IDX1 1 5.0000
--db2 t3 IDX2 1 5.0000
--db2 t3 IDX2 2 1.6667
--use test;
--drop database db1;
--drop database db2;
--drop table t1;
--#
--# MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed
--#
--SET use_stat_tables = PREFERABLY;
--SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' );
--CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' )
--NULL
-set use_stat_tables=@save_use_stat_tables;
-#
-# MDEV-16757: manual addition of min/max statistics for BLOB
-#
-SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-Table Op Msg_type Msg_text
-test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
-test.t1 analyze status OK
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-DELETE FROM mysql.column_stats
-WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
-('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-SELECT pk FROM t1;
-pk
-1
-2
-DROP TABLE t1;
-set use_stat_tables=@save_use_stat_tables;
-#
-# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
-#
-SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-Table Op Msg_type Msg_text
-test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze status OK
-SELECT * FROM t1;
-pk c
-1 foo
-2 bar
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
-SELECT * FROM t1;
-pk a
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-DROP TABLE t1;
-set use_stat_tables=@save_use_stat_tables;
-#
-# MDEV-17023: Crash during read_histogram_for_table with optimizer_use_condition_selectivity set to 4
-#
-set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
-set @@optimizer_use_condition_selectivity=4;
-set @@use_stat_tables= PREFERABLY;
-explain
-SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE PROFILING ALL NULL NULL NULL NULL NULL
-1 SIMPLE user ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
-set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
--set use_stat_tables=@save_use_stat_tables;
--#
--# MDEV-16757: manual addition of min/max statistics for BLOB
--#
--SET use_stat_tables= PREFERABLY;
--CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
--INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
--ANALYZE TABLE t1;
--Table Op Msg_type Msg_text
--test.t1 analyze status Engine-independent statistics collected
--test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
--test.t1 analyze status OK
--SELECT * FROM mysql.column_stats;
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
--DELETE FROM mysql.column_stats
--WHERE db_name='test' AND table_name='t1' AND column_name='t';
--INSERT INTO mysql.column_stats VALUES
--('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
--SELECT * FROM mysql.column_stats;
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
--test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
--SELECT pk FROM t1;
--pk
--1
--2
--DROP TABLE t1;
--set use_stat_tables=@save_use_stat_tables;
--#
--# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
--#
--SET use_stat_tables= PREFERABLY;
--CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
--INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
--ANALYZE TABLE t1;
--Table Op Msg_type Msg_text
--test.t1 analyze status Engine-independent statistics collected
--test.t1 analyze status OK
--SELECT * FROM t1;
--pk c
--1 foo
--2 bar
--SELECT * FROM mysql.column_stats;
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
--test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
--CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
--SELECT * FROM t1;
--pk a
--SELECT * FROM mysql.column_stats;
--db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
--DROP TABLE t1;
--set use_stat_tables=@save_use_stat_tables;
--#
--# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
--#
--SET use_stat_tables= PREFERABLY;
--CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
--INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
--ANALYZE TABLE t1;
--Table Op Msg_type Msg_text
--test.t1 analyze status Engine-independent statistics collected
--test.t1 analyze status OK
--CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
--SELECT MAX(pk) FROM t1;
--MAX(pk)
--NULL
--DROP TABLE t1;
--set use_stat_tables=@save_use_stat_tables;
--set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
--SET SESSION STORAGE_ENGINE=DEFAULT;
diff --cc mysql-test/t/stat_tables.test
index 316ae6f,c318cc5..0000000
deleted file mode 100644,100644
--- a/mysql-test/t/stat_tables.test
+++ /dev/null
@@@ -1,375 -1,432 +1,0 @@@
----source include/have_stat_tables.inc
--
--select @@global.use_stat_tables;
--select @@session.use_stat_tables;
--
--set @save_use_stat_tables=@@use_stat_tables;
--
--set use_stat_tables='preferably';
--
----disable_warnings
--DROP DATABASE IF EXISTS dbt3_s001;
----enable_warnings
--
--CREATE DATABASE dbt3_s001;
--
--use dbt3_s001;
--
--set @save_optimizer_switch=@@optimizer_switch;
--set optimizer_switch='extended_keys=off';
--
----disable_query_log
----disable_result_log
----disable_warnings
----source include/dbt3_s001.inc
--create index i_p_retailprice on part(p_retailprice);
--delete from mysql.table_stats;
--delete from mysql.column_stats;
--delete from mysql.index_stats;
--ANALYZE TABLE
--customer, lineitem, nation, orders, part, partsupp, region, supplier;
--FLUSH TABLE mysql.table_stats, mysql.index_stats;
----enable_warnings
----enable_result_log
----enable_query_log
--
--select * from mysql.table_stats;
--select * from mysql.index_stats;
--
--set optimizer_switch=@save_optimizer_switch;
--
--set @save_optimizer_switch=@@optimizer_switch;
--set optimizer_switch='index_condition_pushdown=off';
--
--let $Q5=
--select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
--from customer, orders, lineitem, supplier, nation, region
--where c_custkey = o_custkey and l_orderkey = o_orderkey
-- and l_suppkey = s_suppkey and c_nationkey = s_nationkey
-- and s_nationkey = n_nationkey and n_regionkey = r_regionkey
-- and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
-- and o_orderdate < date '1995-01-01' + interval '1' year
--group by n_name
--order by revenue desc;
--
--eval EXPLAIN $Q5;
--eval $Q5;
--
--set optimizer_switch=@save_optimizer_switch;
--
--delete from mysql.index_stats;
--
----disable_query_log
----disable_result_log
----disable_warnings
--ANALYZE TABLE
--customer, lineitem, nation, orders, part, partsupp, region, supplier;
--FLUSH TABLE mysql.table_stats, mysql.index_stats;
----enable_warnings
----enable_result_log
----enable_query_log
--
--select * from mysql.table_stats;
--select * from mysql.index_stats;
--
--select * from mysql.table_stats where table_name='orders';
--select * from mysql.index_stats where table_name='orders';
--select (select cardinality from mysql.table_stats where table_name='orders') /
-- (select avg_frequency from mysql.index_stats
-- where index_name='i_o_orderdate' and prefix_arity=1) as n_distinct;
--select count(distinct o_orderdate) from orders;
--select (select cardinality from mysql.table_stats where table_name='orders') /
-- (select avg_frequency from mysql.index_stats
-- where index_name='i_o_custkey' and prefix_arity=1) as n_distinct;
--select count(distinct o_custkey) from orders;
--show index from orders;
--select index_name, column_name, cardinality from information_schema.statistics
-- where table_name='orders';
--
--set @save_optimizer_switch=@@optimizer_switch;
--set optimizer_switch='index_condition_pushdown=off';
--
--eval EXPLAIN $Q5;
--eval $Q5;
--
--set optimizer_switch=@save_optimizer_switch;
--
--let $Q8=
--select o_year,
-- sum(case when nation = 'UNITED STATES' then volume else 0 end) /
-- sum(volume) as mkt_share
--from (select extract(year from o_orderdate) as o_year,
-- l_extendedprice * (1-l_discount) as volume,
-- n2.n_name as nation
-- from part, supplier, lineitem, orders, customer,
-- nation n1, nation n2, region
-- where p_partkey = l_partkey and s_suppkey = l_suppkey
-- and l_orderkey = o_orderkey and o_custkey = c_custkey
-- and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
-- and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
-- and o_orderdate between date '1995-01-01' and date '1996-12-31'
-- and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
--group by o_year
--order by o_year;
--
--eval EXPLAIN $Q8;
--eval $Q8;
--
--
--let $Q9=
--select nation, o_year, sum(amount) as sum_profit
--from (select n_name as nation,
-- extract(year from o_orderdate) as o_year,
-- l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
-- from part, supplier, lineitem, partsupp, orders, nation
-- where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
-- and ps_partkey = l_partkey and p_partkey = l_partkey
-- and o_orderkey = l_orderkey and s_nationkey = n_nationkey
-- and p_name like '%green%') as profit
--group by nation, o_year
--order by nation, o_year desc;
--
--eval EXPLAIN $Q9;
--eval $Q9;
--
--
--set @save_optimizer_switch=@@optimizer_switch;
--set optimizer_switch='extended_keys=on';
--
--let $QQ1=
--select o_orderkey, p_partkey
-- from part, lineitem, orders
-- where p_retailprice > 1100 and o_orderdate='1997-01-01'
-- and o_orderkey=l_orderkey and p_partkey=l_partkey;
--
--eval EXPLAIN $QQ1;
--eval $QQ1;
--
--set optimizer_switch=@save_optimizer_switch;
--
--
--DROP DATABASE dbt3_s001;
--
--use test;
--
----echo #
----echo # Bug mdev-473: ANALYZE table locked for write
----echo #
--
--set use_stat_tables='complementary';
--
--create table t1 (i int);
--
--lock table t1 write;
--analyze table t1;
--alter table t1 add column a varchar(8);
--
--drop table t1;
--
----echo #
----echo # Bug mdev-487: memory leak in ANALYZE with stat tables
----echo #
--
--SET use_stat_tables = 'preferably';
--
--CREATE TABLE t1 (a INT);
--INSERT INTO t1 VALUES (1),(2);
--DELETE FROM t1 WHERE a=1;
--
--ANALYZE TABLE t1;
--
--DROP TABLE t1;
--
----echo #
----echo # Bug mdev-518: corrupted/missing statistical tables
----echo #
--
--CREATE TABLE t1 (i int) ENGINE=MyISAM;
--INSERT INTO t1 VALUES (1),(2);
--
--FLUSH TABLE t1;
--SET use_stat_tables='never';
--EXPLAIN SELECT * FROM t1;
--
----move_file $MYSQLTEST_VARDIR/mysqld.1/data/mysql/table_stats.MYD $MYSQLTEST_VARDIR/mysqld.1/data/mysql/table_stats.MYD.save
--
--FLUSH TABLES;
--SET use_stat_tables='preferably';
----disable_warnings
--EXPLAIN SELECT * FROM t1;
----enable_warnings
--
--# Cleanup
----move_file $MYSQLTEST_VARDIR/mysqld.1/data/mysql/table_stats.MYD.save $MYSQLTEST_VARDIR/mysqld.1/data/mysql/table_stats.MYD
--DROP TABLE t1;
--
--set use_stat_tables=@save_use_stat_tables;
--
----echo #
----echo # Bug mdev-5204: invalid impossible where after reading const tables
----echo # when use_stat_tables = 'preferably'
----echo #
--
--set use_stat_tables = 'preferably';
--
--CREATE TABLE t1 (id int PRIMARY KEY) ENGINE=MyISAM;
--INSERT INTO t1 VALUES (1),(2);
--ANALYZE TABLE t1;
--
--CREATE TABLE t2 (name char(3)) ENGINE=MyISAM;
--ANALYZE TABLE t2;
--INSERT INTO t2 VALUES ('USA'),('AUS');
--
--SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
--EXPLAIN
--SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
--
--ANALYZE TABLE t2;
--
--SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
--EXPLAIN
--SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
--
--DROP TABLE t1,t2;
--
----echo #
----echo # MDEV-7370: Server deadlocks on renaming a table for which persistent statistics exists
----echo #
--
----disable_warnings
--drop database if exists db1;
--drop database if exists db1;
----enable_warnings
--
--create database db1;
--create database db2;
--use db1;
----echo #
----echo # First, run the original testcase:
----echo #
--create table t1 (i int);
--insert into t1 values (10),(20);
--analyze table t1 persistent for all;
--rename table t1 to db2.t1;
--
----echo # Verify that stats in the old database are gone:
--select * from mysql.column_stats where db_name='db1' and table_name='t1';
--select * from mysql.table_stats where db_name='db1' and table_name='t1';
--
----echo # Verify that stats are present in the new database:
--select * from mysql.column_stats where db_name='db2' and table_name='t1';
--select * from mysql.table_stats where db_name='db2' and table_name='t1';
--
--
----echo #
----echo # Now, try with more than one column and with indexes:
----echo #
--use test;
--create table t1(a int primary key);
--insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
--
--
--use db1;
--create table t2 (a int, b int, c int, key IDX1(a), key IDX2(a,b));
--insert into t2 select a/10, a/2, a from test.t1;
--analyze table t2 persistent for all;
--
--alter table t2 rename db2.t2;
--
----echo # Verify that stats in the old database are gone:
--select * from mysql.table_stats where db_name='db1' and table_name='t2';
--select * from mysql.column_stats where db_name='db1' and table_name='t2';
--select * from mysql.index_stats where db_name='db1' and table_name='t2';
--
----echo # Verify that stats are present in the new database:
--select * from mysql.table_stats where db_name='db2' and table_name='t2';
--select * from mysql.column_stats where db_name='db2' and table_name='t2';
--select * from mysql.index_stats where db_name='db2' and table_name='t2';
--
--use db2;
----echo #
----echo # Now, rename within the same database and verify:
----echo #
--rename table t2 to t3;
----echo # No stats under old name:
--select * from mysql.table_stats where db_name='db2' and table_name='t2';
--select * from mysql.column_stats where db_name='db2' and table_name='t2';
--select * from mysql.index_stats where db_name='db2' and table_name='t2';
----echo # Stats under the new name:
--select * from mysql.table_stats where db_name='db2' and table_name='t3';
--select * from mysql.column_stats where db_name='db2' and table_name='t3';
--select * from mysql.index_stats where db_name='db2' and table_name='t3';
--
--use test;
--drop database db1;
--drop database db2;
--drop table t1;
--
----echo #
----echo # MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed
----echo #
--
--SET use_stat_tables = PREFERABLY;
--SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' );
-set use_stat_tables=@save_use_stat_tables;
-
---echo #
---echo # MDEV-16757: manual addition of min/max statistics for BLOB
---echo #
-
-SET use_stat_tables= PREFERABLY;
-
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
---sorted_result
-SELECT * FROM mysql.column_stats;
-DELETE FROM mysql.column_stats
- WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
- ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
---sorted_result
-SELECT * FROM mysql.column_stats;
-
-SELECT pk FROM t1;
-
-DROP TABLE t1;
-
-set use_stat_tables=@save_use_stat_tables;
-
---echo #
---echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
---echo #
-
-SET use_stat_tables= PREFERABLY;
-
-CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-SELECT * FROM t1;
-SELECT * FROM mysql.column_stats;
-
-CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
-SELECT * FROM t1;
-SELECT * FROM mysql.column_stats;
-
-DROP TABLE t1;
-
-set use_stat_tables=@save_use_stat_tables;
-
---echo #
---echo # MDEV-17023: Crash during read_histogram_for_table with optimizer_use_condition_selectivity set to 4
---echo #
-
-set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
-set @@optimizer_use_condition_selectivity=4;
-set @@use_stat_tables= PREFERABLY;
-explain
-SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user;
-set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
--set use_stat_tables=@save_use_stat_tables;
--
----echo #
----echo # MDEV-16757: manual addition of min/max statistics for BLOB
----echo #
--
--SET use_stat_tables= PREFERABLY;
--
--CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
--INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
--ANALYZE TABLE t1;
----sorted_result
--SELECT * FROM mysql.column_stats;
--DELETE FROM mysql.column_stats
-- WHERE db_name='test' AND table_name='t1' AND column_name='t';
--INSERT INTO mysql.column_stats VALUES
-- ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
----sorted_result
--SELECT * FROM mysql.column_stats;
--
--SELECT pk FROM t1;
--
--DROP TABLE t1;
--
--set use_stat_tables=@save_use_stat_tables;
--
----echo #
----echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
----echo #
--
--SET use_stat_tables= PREFERABLY;
--
--CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
--INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
--ANALYZE TABLE t1;
--SELECT * FROM t1;
--SELECT * FROM mysql.column_stats;
--
--CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
--SELECT * FROM t1;
--SELECT * FROM mysql.column_stats;
--
--DROP TABLE t1;
--
--set use_stat_tables=@save_use_stat_tables;
-
--
----echo #
----echo # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
----echo #
--
--SET use_stat_tables= PREFERABLY;
--
--CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
--INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
--ANALYZE TABLE t1;
--CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
--
--SELECT MAX(pk) FROM t1;
--
--DROP TABLE t1;
--
--set use_stat_tables=@save_use_stat_tables;
1
0
revision-id: 2a587d40b9e056e61e38229a6aeea3d817e2c777 (mariadb-10.1.34-31-g2a587d4)
parent(s): 1fde449f1d4e8ebe12e989754bfdc84b123cf0b2
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-09-18 23:44:26 -0700
message:
Removed duplicate tests
---
mysql-test/r/stat_tables.result | 53 ----------------------------------
mysql-test/r/stat_tables_innodb.result | 53 ----------------------------------
mysql-test/t/stat_tables.test | 45 -----------------------------
3 files changed, 151 deletions(-)
diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index ceadb61..7d3012f 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -578,59 +578,6 @@ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_fr
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
#
-# MDEV-16757: manual addition of min/max statistics for BLOB
-#
-SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-Table Op Msg_type Msg_text
-test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
-test.t1 analyze status OK
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-DELETE FROM mysql.column_stats
-WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
-('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-SELECT pk FROM t1;
-pk
-1
-2
-DROP TABLE t1;
-set use_stat_tables=@save_use_stat_tables;
-#
-# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
-#
-SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-Table Op Msg_type Msg_text
-test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze status OK
-SELECT * FROM t1;
-pk c
-1 foo
-2 bar
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
-SELECT * FROM t1;
-pk a
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-DROP TABLE t1;
-set use_stat_tables=@save_use_stat_tables;
-#
# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
#
SET use_stat_tables= PREFERABLY;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index c5e7309..7e44b87 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -605,59 +605,6 @@ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_fr
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
#
-# MDEV-16757: manual addition of min/max statistics for BLOB
-#
-SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-Table Op Msg_type Msg_text
-test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
-test.t1 analyze status OK
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-DELETE FROM mysql.column_stats
-WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
-('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-SELECT pk FROM t1;
-pk
-1
-2
-DROP TABLE t1;
-set use_stat_tables=@save_use_stat_tables;
-#
-# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
-#
-SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-Table Op Msg_type Msg_text
-test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze status OK
-SELECT * FROM t1;
-pk c
-1 foo
-2 bar
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
-SELECT * FROM t1;
-pk a
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-DROP TABLE t1;
-set use_stat_tables=@save_use_stat_tables;
-#
# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
#
SET use_stat_tables= PREFERABLY;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index 2c9c1ec..316ae6f 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -358,51 +358,6 @@ DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
--echo #
---echo # MDEV-16757: manual addition of min/max statistics for BLOB
---echo #
-
-SET use_stat_tables= PREFERABLY;
-
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
---sorted_result
-SELECT * FROM mysql.column_stats;
-DELETE FROM mysql.column_stats
- WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
- ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
---sorted_result
-SELECT * FROM mysql.column_stats;
-
-SELECT pk FROM t1;
-
-DROP TABLE t1;
-
-set use_stat_tables=@save_use_stat_tables;
-
---echo #
---echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
---echo #
-
-SET use_stat_tables= PREFERABLY;
-
-CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
-INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
-ANALYZE TABLE t1;
-SELECT * FROM t1;
-SELECT * FROM mysql.column_stats;
-
-CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
-SELECT * FROM t1;
-SELECT * FROM mysql.column_stats;
-
-DROP TABLE t1;
-
-set use_stat_tables=@save_use_stat_tables;
-
-
---echo #
--echo # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
--echo #
1
0
[Commits] d3f01f6: MDEV-16711 Crash in Field_blob::store() while reading statistics
by IgorBabaev 19 Sep '18
by IgorBabaev 19 Sep '18
19 Sep '18
revision-id: d3f01f64f0e0031dd684f0c700c84b58568276d3 (mariadb-10.2.2-864-gd3f01f6)
parent(s): 809d8837ac6abd2b31a287927c978e41b0ddecf6
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-09-18 23:31:19 -0700
message:
MDEV-16711 Crash in Field_blob::store() while reading statistics
for the small InnoDB table
This bug was introduced by the patch 6c414fcf89510215d6d3466eb9992d444eadae89.
The patch has not taken into account that some objects of the Field_* types
are created only for TABLE_SHARE and the field 'table' is set to NULL
for them. In particular such are objects created to store statistical
min/max values for columns.
---
mysql-test/r/stat_tables.result | 52 ++++++++++++++++++++++------------
mysql-test/r/stat_tables_innodb.result | 52 ++++++++++++++++++++++------------
mysql-test/t/stat_tables.test | 39 ++++++++++++++++++-------
sql/field.cc | 5 ++++
4 files changed, 101 insertions(+), 47 deletions(-)
diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index 0ee0d69..279c09f 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -518,6 +518,34 @@ drop database db2;
drop table t1;
set use_stat_tables=@save_use_stat_tables;
#
+# MDEV-16757: manual addition of min/max statistics for BLOB
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
+test.t1 analyze status OK
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+DELETE FROM mysql.column_stats
+WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+SELECT pk FROM t1;
+pk
+1
+2
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
#
SET use_stat_tables= PREFERABLY;
@@ -543,30 +571,18 @@ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_fr
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
#
-# MDEV-16757: manual addition of min/max statistics for BLOB
+# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
#
SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
test.t1 analyze status OK
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-DELETE FROM mysql.column_stats
-WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
-('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-SELECT pk FROM t1;
-pk
-1
-2
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+SELECT MAX(pk) FROM t1;
+MAX(pk)
+NULL
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index b031950..d551afd 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -545,6 +545,34 @@ drop database db2;
drop table t1;
set use_stat_tables=@save_use_stat_tables;
#
+# MDEV-16757: manual addition of min/max statistics for BLOB
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
+test.t1 analyze status OK
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+DELETE FROM mysql.column_stats
+WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+SELECT pk FROM t1;
+pk
+1
+2
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
#
SET use_stat_tables= PREFERABLY;
@@ -570,31 +598,19 @@ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_fr
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
#
-# MDEV-16757: manual addition of min/max statistics for BLOB
+# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
#
SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
test.t1 analyze status OK
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-DELETE FROM mysql.column_stats
-WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
-('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-SELECT pk FROM t1;
-pk
-1
-2
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+SELECT MAX(pk) FROM t1;
+MAX(pk)
+NULL
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index 7660acf..cfe9255 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -308,6 +308,30 @@ drop table t1;
set use_stat_tables=@save_use_stat_tables;
--echo #
+--echo # MDEV-16757: manual addition of min/max statistics for BLOB
+--echo #
+
+SET use_stat_tables= PREFERABLY;
+
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+DELETE FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+ ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+--sorted_result
+SELECT * FROM mysql.column_stats;
+
+SELECT pk FROM t1;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
--echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
--echo #
@@ -328,24 +352,17 @@ DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
--echo #
---echo # MDEV-16757: manual addition of min/max statistics for BLOB
+--echo # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
--echo #
SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
ANALYZE TABLE t1;
---sorted_result
-SELECT * FROM mysql.column_stats;
-DELETE FROM mysql.column_stats
- WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
- ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
---sorted_result
-SELECT * FROM mysql.column_stats;
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
-SELECT pk FROM t1;
+SELECT MAX(pk) FROM t1;
DROP TABLE t1;
diff --git a/sql/field.cc b/sql/field.cc
index 50edec4..f4717de 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -7924,6 +7924,11 @@ int Field_blob::store(const char *from,uint length,CHARSET_INFO *cs)
return 0;
}
+ /*
+ For min/max fields of statistical data 'table' is set to NULL.
+ It could not be otherwise as this data is shared by many instances
+ of the same base table.
+ */
if (table && table->blob_storage) // GROUP_CONCAT with ORDER BY | DISTINCT
{
DBUG_ASSERT(!f_is_hex_escape(flags));
1
0
[Commits] d435da8: MDEV-16711 Crash in Field_blob::store() while reading statistics
by IgorBabaev 19 Sep '18
by IgorBabaev 19 Sep '18
19 Sep '18
revision-id: d435da80a09c1b4cb0ab76900177fb64a791efc0 (mariadb-10.2.2-864-gd435da8)
parent(s): 809d8837ac6abd2b31a287927c978e41b0ddecf6
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-09-18 23:30:13 -0700
message:
MDEV-16711 Crash in Field_blob::store() while reading statistics
for the small InnoDB table
This bug was introduced by the patch 6c414fcf89510215d6d3466eb9992d444eadae89.
The patch has not taken into account that some objects of the Field_* types
are created only for TABLE_SHARE and the field 'table' is set to NULL
for them. In particular such are objects created to store statistical
min/max values for columns.
# Conflicts:
# mysql-test/r/stat_tables.result
# mysql-test/r/stat_tables_innodb.result
# mysql-test/t/stat_tables.test
# sql/field.cc
---
mysql-test/r/stat_tables.result | 52 ++++++++++++++++++++++------------
mysql-test/r/stat_tables_innodb.result | 52 ++++++++++++++++++++++------------
mysql-test/t/stat_tables.test | 39 ++++++++++++++++++-------
sql/field.cc | 5 ++++
4 files changed, 101 insertions(+), 47 deletions(-)
diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index 0ee0d69..279c09f 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -518,6 +518,34 @@ drop database db2;
drop table t1;
set use_stat_tables=@save_use_stat_tables;
#
+# MDEV-16757: manual addition of min/max statistics for BLOB
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
+test.t1 analyze status OK
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+DELETE FROM mysql.column_stats
+WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+SELECT pk FROM t1;
+pk
+1
+2
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
#
SET use_stat_tables= PREFERABLY;
@@ -543,30 +571,18 @@ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_fr
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
#
-# MDEV-16757: manual addition of min/max statistics for BLOB
+# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
#
SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
test.t1 analyze status OK
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-DELETE FROM mysql.column_stats
-WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
-('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-SELECT pk FROM t1;
-pk
-1
-2
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+SELECT MAX(pk) FROM t1;
+MAX(pk)
+NULL
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index b031950..d551afd 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -545,6 +545,34 @@ drop database db2;
drop table t1;
set use_stat_tables=@save_use_stat_tables;
#
+# MDEV-16757: manual addition of min/max statistics for BLOB
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
+test.t1 analyze status OK
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+DELETE FROM mysql.column_stats
+WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+SELECT pk FROM t1;
+pk
+1
+2
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
#
SET use_stat_tables= PREFERABLY;
@@ -570,31 +598,19 @@ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_fr
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
#
-# MDEV-16757: manual addition of min/max statistics for BLOB
+# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
#
SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
-test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
test.t1 analyze status OK
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-DELETE FROM mysql.column_stats
-WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
-('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
-SELECT * FROM mysql.column_stats;
-db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
-test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
-SELECT pk FROM t1;
-pk
-1
-2
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+SELECT MAX(pk) FROM t1;
+MAX(pk)
+NULL
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index 7660acf..cfe9255 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -308,6 +308,30 @@ drop table t1;
set use_stat_tables=@save_use_stat_tables;
--echo #
+--echo # MDEV-16757: manual addition of min/max statistics for BLOB
+--echo #
+
+SET use_stat_tables= PREFERABLY;
+
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+DELETE FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+ ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+--sorted_result
+SELECT * FROM mysql.column_stats;
+
+SELECT pk FROM t1;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
--echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
--echo #
@@ -328,24 +352,17 @@ DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
--echo #
---echo # MDEV-16757: manual addition of min/max statistics for BLOB
+--echo # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
--echo #
SET use_stat_tables= PREFERABLY;
-CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
ANALYZE TABLE t1;
---sorted_result
-SELECT * FROM mysql.column_stats;
-DELETE FROM mysql.column_stats
- WHERE db_name='test' AND table_name='t1' AND column_name='t';
-INSERT INTO mysql.column_stats VALUES
- ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
---sorted_result
-SELECT * FROM mysql.column_stats;
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
-SELECT pk FROM t1;
+SELECT MAX(pk) FROM t1;
DROP TABLE t1;
diff --git a/sql/field.cc b/sql/field.cc
index 50edec4..f4717de 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -7924,6 +7924,11 @@ int Field_blob::store(const char *from,uint length,CHARSET_INFO *cs)
return 0;
}
+ /*
+ For min/max fields of statistical data 'table' is set to NULL.
+ It could not be otherwise as this data is shared by many instances
+ of the same base table.
+ */
if (table && table->blob_storage) // GROUP_CONCAT with ORDER BY | DISTINCT
{
DBUG_ASSERT(!f_is_hex_escape(flags));
1
0
[Commits] 4eafd2c: MDEV-16711 Crash in Field_blob::store() while reading statistics
by IgorBabaev 19 Sep '18
by IgorBabaev 19 Sep '18
19 Sep '18
revision-id: 4eafd2c36f87e5017b15731032b232ba5822c485 (mariadb-10.2.2-864-g4eafd2c)
parent(s): 809d8837ac6abd2b31a287927c978e41b0ddecf6
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-09-18 23:06:16 -0700
message:
MDEV-16711 Crash in Field_blob::store() while reading statistics
for the small InnoDB table
This bug was introduced by the patch 6c414fcf89510215d6d3466eb9992d444eadae89.
The patch has not taken into account that some objects of the Field_* types
are created only for TABLE_SHARE and the field 'table' is set to NULL
for them. In particular such are objects created to store statistical
min/max values for columns.
# Conflicts:
# mysql-test/r/stat_tables.result
# mysql-test/r/stat_tables_innodb.result
# mysql-test/t/stat_tables.test
# sql/field.cc
---
mysql-test/r/stat_tables.result | 16 ++++++++++++++++
mysql-test/r/stat_tables_innodb.result | 16 ++++++++++++++++
mysql-test/t/stat_tables.test | 17 +++++++++++++++++
sql/field.cc | 5 +++++
4 files changed, 54 insertions(+)
diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index 0ee0d69..c0f804f 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -570,3 +570,19 @@ pk
2
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+SELECT MAX(pk) FROM t1;
+MAX(pk)
+NULL
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index b031950..80a0a1d 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -597,5 +597,21 @@ pk
2
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+SELECT MAX(pk) FROM t1;
+MAX(pk)
+NULL
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index 7660acf..a4f768a 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -350,3 +350,20 @@ SELECT pk FROM t1;
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
+--echo #
+
+SET use_stat_tables= PREFERABLY;
+
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+
+SELECT MAX(pk) FROM t1;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
diff --git a/sql/field.cc b/sql/field.cc
index 50edec4..f4717de 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -7924,6 +7924,11 @@ int Field_blob::store(const char *from,uint length,CHARSET_INFO *cs)
return 0;
}
+ /*
+ For min/max fields of statistical data 'table' is set to NULL.
+ It could not be otherwise as this data is shared by many instances
+ of the same base table.
+ */
if (table && table->blob_storage) // GROUP_CONCAT with ORDER BY | DISTINCT
{
DBUG_ASSERT(!f_is_hex_escape(flags));
1
0
[Commits] 809d883: MDEV-16757 Memory leak after adding manually min/max statistical data
by IgorBabaev 19 Sep '18
by IgorBabaev 19 Sep '18
19 Sep '18
revision-id: 809d8837ac6abd2b31a287927c978e41b0ddecf6 (mariadb-10.2.2-863-g809d883)
parent(s): 7ccf06f4131a45ba33144f79934fee722a89a88d
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-09-18 21:57:42 -0700
message:
MDEV-16757 Memory leak after adding manually min/max statistical data
for blob column
ANALYZE TABLE <table> does not collect statistical data on min/max values
for BLOB columns of <table>. However these values can be added into
mysql.column_stats manually by executing proper statements.
Unfortunately this led to a memory leak because the memory allocated
for these values was never freed.
This patch provides the server with a function to free memory allocated
for min/max statistical values of BLOB types.
---
mysql-test/r/stat_tables.result | 28 ++++++++++++++++++++++++++++
mysql-test/r/stat_tables_innodb.result | 28 ++++++++++++++++++++++++++++
mysql-test/t/stat_tables.test | 24 ++++++++++++++++++++++++
sql/field.cc | 2 +-
sql/sql_statistics.cc | 33 +++++++++++++++++++++++++++++++++
sql/sql_statistics.h | 1 +
sql/table.cc | 1 +
7 files changed, 116 insertions(+), 1 deletion(-)
diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index 90232b7..0ee0d69 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -542,3 +542,31 @@ SELECT * FROM mysql.column_stats;
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16757: manual addition of min/max statistics for BLOB
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
+test.t1 analyze status OK
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+DELETE FROM mysql.column_stats
+WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+SELECT pk FROM t1;
+pk
+1
+2
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index 25e966b..b031950 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -569,5 +569,33 @@ SELECT * FROM mysql.column_stats;
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16757: manual addition of min/max statistics for BLOB
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
+test.t1 analyze status OK
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+DELETE FROM mysql.column_stats
+WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+SELECT pk FROM t1;
+pk
+1
+2
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index 5a06224..7660acf 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -326,3 +326,27 @@ SELECT * FROM mysql.column_stats;
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # MDEV-16757: manual addition of min/max statistics for BLOB
+--echo #
+
+SET use_stat_tables= PREFERABLY;
+
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+DELETE FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+ ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+--sorted_result
+SELECT * FROM mysql.column_stats;
+
+SELECT pk FROM t1;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
diff --git a/sql/field.cc b/sql/field.cc
index 93271ce..50edec4 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -7924,7 +7924,7 @@ int Field_blob::store(const char *from,uint length,CHARSET_INFO *cs)
return 0;
}
- if (table->blob_storage) // GROUP_CONCAT with ORDER BY | DISTINCT
+ if (table && table->blob_storage) // GROUP_CONCAT with ORDER BY | DISTINCT
{
DBUG_ASSERT(!f_is_hex_escape(flags));
DBUG_ASSERT(field_charset == cs);
diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc
index e1465d4..3a4ee54 100644
--- a/sql/sql_statistics.cc
+++ b/sql/sql_statistics.cc
@@ -3054,6 +3054,39 @@ int read_statistics_for_table(THD *thd, TABLE *table, TABLE_LIST *stat_tables)
/**
+ @breif
+ Cleanup of min/max statistical values for table share
+*/
+
+void delete_stat_values_for_table_share(TABLE_SHARE *table_share)
+{
+ TABLE_STATISTICS_CB *stats_cb= &table_share->stats_cb;
+ Table_statistics *table_stats= stats_cb->table_stats;
+ if (!table_stats)
+ return;
+ Column_statistics *column_stats= table_stats->column_stats;
+ if (!column_stats)
+ return;
+
+ for (Field **field_ptr= table_share->field;
+ *field_ptr;
+ field_ptr++, column_stats++)
+ {
+ if (column_stats->min_value)
+ {
+ delete column_stats->min_value;
+ column_stats->min_value= NULL;
+ }
+ if (column_stats->max_value)
+ {
+ delete column_stats->max_value;
+ column_stats->max_value= NULL;
+ }
+ }
+}
+
+
+/**
@brief
Check whether any statistics is to be read for tables from a table list
diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h
index eb59b14..5b26d1a 100644
--- a/sql/sql_statistics.h
+++ b/sql/sql_statistics.h
@@ -92,6 +92,7 @@ int read_statistics_for_tables_if_needed(THD *thd, TABLE_LIST *tables);
int collect_statistics_for_table(THD *thd, TABLE *table);
int alloc_statistics_for_table_share(THD* thd, TABLE_SHARE *share,
bool is_safe);
+void delete_stat_values_for_table_share(TABLE_SHARE *table_share);
int alloc_statistics_for_table(THD *thd, TABLE *table);
int update_statistics_for_table(THD *thd, TABLE *table);
int delete_statistics_for_table(THD *thd, LEX_CSTRING *db, LEX_CSTRING *tab);
diff --git a/sql/table.cc b/sql/table.cc
index 95ded6b..5c328f3 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -435,6 +435,7 @@ void TABLE_SHARE::destroy()
}
delete sequence;
+ delete_stat_values_for_table_share(this);
free_root(&stats_cb.mem_root, MYF(0));
stats_cb.stats_can_be_read= FALSE;
stats_cb.stats_is_read= FALSE;
1
0
[Commits] 7ccf06f: MDEV-16760 CREATE OR REPLACE TABLE never updates statistical tables
by IgorBabaev 19 Sep '18
by IgorBabaev 19 Sep '18
19 Sep '18
revision-id: 7ccf06f4131a45ba33144f79934fee722a89a88d (mariadb-10.2.2-862-g7ccf06f)
parent(s): ba9f5fe505077a42ac619bfd23e4d751fd8bd2b3
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-09-18 20:31:22 -0700
message:
MDEV-16760 CREATE OR REPLACE TABLE never updates statistical tables
If the command CREATE OR REPLACE TABLE really replaces a table then
it should remove all data on this table from all statistical tables.
---
mysql-test/r/stat_tables.result | 25 +++++++++++++++++++++++++
mysql-test/r/stat_tables_innodb.result | 25 +++++++++++++++++++++++++
mysql-test/t/stat_tables.test | 20 ++++++++++++++++++++
sql/sql_table.cc | 4 ++++
4 files changed, 74 insertions(+)
diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index fcced76..90232b7 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -517,3 +517,28 @@ drop database db1;
drop database db2;
drop table t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+SELECT * FROM t1;
+pk c
+1 foo
+2 bar
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+pk a
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index 42443bf..25e966b 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -544,5 +544,30 @@ drop database db1;
drop database db2;
drop table t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+SELECT * FROM t1;
+pk c
+1 foo
+2 bar
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+pk a
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index 4cbaa9e..5a06224 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -306,3 +306,23 @@ drop database db2;
drop table t1;
set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+--echo #
+
+SET use_stat_tables= PREFERABLY;
+
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+SELECT * FROM t1;
+SELECT * FROM mysql.column_stats;
+
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+SELECT * FROM mysql.column_stats;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 5fa65e3..79e9d2a 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -4660,6 +4660,10 @@ int create_table_impl(THD *thd,
{
if (options.or_replace())
{
+ LEX_CSTRING db_name= {db, strlen(db)};
+ LEX_CSTRING tab_name= {table_name, strlen(table_name)};
+ (void) delete_statistics_for_table(thd, &db_name, &tab_name);
+
TABLE_LIST table_list;
table_list.init_one_table(db, strlen(db), table_name,
strlen(table_name), table_name,
1
0
[Commits] 07ac6bf: MDEV-16760 CREATE OR REPLACE TABLE never updates statistical tables
by IgorBabaev 19 Sep '18
by IgorBabaev 19 Sep '18
19 Sep '18
revision-id: 07ac6bfff110889ba9299b0c81fc609afd9d75b2 (mariadb-10.2.2-862-g07ac6bf)
parent(s): ba9f5fe505077a42ac619bfd23e4d751fd8bd2b3
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-09-18 19:28:39 -0700
message:
MDEV-16760 CREATE OR REPLACE TABLE never updates statistical tables
If the command CREATE OR REPLACE TABLE really replaces a table then
it should remove all data on this table from all statistical tables.
---
mysql-test/r/stat_tables.result | 25 +++++++++++++++++++++++++
mysql-test/r/stat_tables_innodb.result | 25 +++++++++++++++++++++++++
mysql-test/t/stat_tables.test | 20 ++++++++++++++++++++
sql/sql_table.cc | 4 ++++
4 files changed, 74 insertions(+)
diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index fcced76..90232b7 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -517,3 +517,28 @@ drop database db1;
drop database db2;
drop table t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+SELECT * FROM t1;
+pk c
+1 foo
+2 bar
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+pk a
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index 42443bf..25e966b 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -544,5 +544,30 @@ drop database db1;
drop database db2;
drop table t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+SELECT * FROM t1;
+pk c
+1 foo
+2 bar
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+pk a
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index 4cbaa9e..5a06224 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -306,3 +306,23 @@ drop database db2;
drop table t1;
set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+--echo #
+
+SET use_stat_tables= PREFERABLY;
+
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+SELECT * FROM t1;
+SELECT * FROM mysql.column_stats;
+
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+SELECT * FROM mysql.column_stats;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 5fa65e3..7934a06 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -4660,6 +4660,10 @@ int create_table_impl(THD *thd,
{
if (options.or_replace())
{
+ LEX_STRING db_name= {(char *) db, strlen(db)};
+ LEX_STRING tab_name= {(char *) table_name, strlen(table_name)};
+ (void) delete_statistics_for_table(thd, &db_name, &tab_name);
+
TABLE_LIST table_list;
table_list.init_one_table(db, strlen(db), table_name,
strlen(table_name), table_name,
1
0