Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 311 insertions, 0 deletions

diff --git a/mysql-test/main/analyze.test b/mysql-test/main/analyze.test
new file mode 100644
index 00000000..fb07e11b
--- /dev/null
+++ b/mysql-test/main/analyze.test
@@ -0,0 +1,311 @@
+--source include/have_sequence.inc
+--source include/have_innodb.inc
+
+#
+# Bug #10901 Analyze Table on new table destroys table
+# This is minimal test case to get error
+# The problem was that analyze table wrote the shared state to the
+# file and this didn't include the inserts while locked. A check was
+# needed to ensure that state information was not updated when
+# executing analyze table for a locked table.  The analyze table had
+# to be within locks and check table had to be after unlocking since
+# then it brings the wrong state from disk rather than from the
+# currently correct internal state. The insert is needed since it
+# changes the file state, number of records.  The fix is to
+# synchronise the state of the shared state and the current state
+# before calling mi_state_info_write
+#
+
+--source include/default_optimizer_switch.inc
+
+create table t1 (a bigint);
+lock tables t1 write;
+insert into t1 values(0);
+analyze table t1;
+unlock tables;
+check table t1;
+
+drop table t1;
+
+create table t1 (a bigint);
+insert into t1 values(0);
+lock tables t1 write;
+delete from t1;
+analyze table t1;
+unlock tables;
+check table t1;
+
+drop table t1;
+
+create table t1 (a bigint);
+insert into t1 values(0);
+analyze table t1;
+check table t1;
+
+drop table t1;
+
+# Bug #14902 ANALYZE TABLE fails to recognize up-to-date tables
+# minimal test case to get an error.
+# The problem is happening when analysing table with FT index that
+# contains stopwords only. The first execution of analyze table should
+# mark index statistics as up to date so that next execution of this
+# statement will end up with Table is up to date status.
+create table t1 (a mediumtext, fulltext key key1(a)) charset utf8 collate utf8_general_ci engine myisam;
+insert into t1 values ('hello');
+
+analyze table t1;
+analyze table t1;
+
+drop table t1;
+
+#
+# procedure in PS BUG#13673
+#
+CREATE TABLE t1 (a int);
+prepare stmt1 from "SELECT * FROM t1 PROCEDURE ANALYSE()";
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+drop table t1;
+
+#
+# bug#15225 (ANALYZE temporary has no effect)
+#
+create temporary table t1(a int, index(a));
+insert into t1 values('1'),('2'),('3'),('4'),('5');
+analyze table t1;
+show index from t1;
+drop table t1;
+
+--echo End of 4.1 tests
+
+#
+# Bug #30495: optimize table t1,t2,t3 extended errors
+#
+create table t1(a int);
+--error 1064
+analyze table t1 extended;
+--error 1064
+optimize table t1 extended;
+drop table t1;
+
+--echo End of 5.0 tests
+
+--echo #
+--echo # Test analyze of text column (not yet supported)
+--echo #
+
+set optimizer_use_condition_selectivity=4;
+set histogram_type='single_prec_hb';
+set histogram_size=255;
+
+create table t1 (a int not null, t tinytext, tx text);
+insert into t1 select seq+1, repeat('X',seq*5), repeat('X',seq*10) from seq_0_to_50;
+insert into t1 select seq+100, repeat('X',5), "" from seq_1_to_10;
+analyze table t1;
+explain select count(*) from t1 where t='XXXXXX';
+select column_name, min_value, max_value, hist_size from mysql.column_stats where table_name='t1';
+
+drop table t1;
+
+set use_stat_tables=default;
+set histogram_type=default;
+set histogram_size=default;
+
+--echo #
+--echo # MDEV-31957 Concurrent ALTER and ANALYZE collecting statistics can
+--echo # result in stale statistical data
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(128));
+INSERT INTO t1 SELECT seq, CONCAT('s',seq) FROM seq_1_to_100;
+
+# We have to disable query log as the ANALYZE TABLE can be run in different
+# order. The important thing is what is finally in column_stats
+--disable_result_log
+--connect (con1,localhost,root,,)
+--send ALTER TABLE t1 MODIFY b BLOB
+
+--connection default
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+
+--connection con1
+--reap
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+--connection default
+--disconnect con1
+--enable_result_log
+select db_name,table_name,column_name from mysql.column_stats;
+drop table t1;
+
+--echo #
+--echo # Testing swapping columns
+--echo #
+
+create or replace table t1 (a int primary key, b varchar(100), c varchar(100), d varchar(100)) engine=innodb;
+insert into t1 select seq, repeat('b',seq),repeat('c',mod(seq,5)), repeat('d',mod(seq,10))  from seq_1_to_100;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
+alter table t1 change b c varchar(200), change c b varchar(200);
+select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
+alter table t1 change b c varchar(200), change c d varchar(200), change d b varchar(200) ;
+select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
+alter table t1 change b c varchar(200), change c d varchar(200), change d e varchar(200) ;
+select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
+alter table t1 change e d varchar(200), drop column d;
+select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
+
+--echo # Test having non existing column in column_stats
+
+insert into mysql.column_stats (db_name,table_name,column_name) values ("test","t1","b");
+alter table t1 change c d varchar(200), change d b varchar(200);
+select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
+
+--echo # Test having a conflicting temporary name
+insert into mysql.column_stats (db_name,table_name,column_name) values ("test","t1",concat("#sql_tmp_name#1",char(0)));
+alter table t1 change d b varchar(200), change b d varchar(200);
+select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
+
+drop table t1;
+truncate table mysql.column_stats;
+
+create or replace table t1 (a int primary key, b varchar(100), c varchar(100), d varchar(100)) engine=myisam;
+insert into t1 select seq, repeat('b',seq),repeat('c',mod(seq,5)), repeat('d',mod(seq,10))  from seq_1_to_100;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
+alter table t1 change b c varchar(200), change c b varchar(200);
+select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
+analyze table t1 persistent for columns(b,c) indexes all;
+alter table t1 change b c varchar(200), change c d varchar(200), change d b varchar(200) ;
+select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
+analyze table t1 persistent for columns(d) indexes all;
+alter table t1 change b c varchar(200), change c d varchar(200), change d e varchar(200) ;
+select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
+alter table t1 change e d varchar(200), drop column d;
+select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
+drop table t1;
+truncate table mysql.column_stats;
+
+create table t1 (a int, b blob, unique(b)) engine= innodb;
+analyze table t1 persistent for all;
+select column_name from mysql.column_stats where table_name = 't1';
+drop table t1;
+
+create table t1 (a int, b blob, c int generated always as (length(b)) virtual) engine= innodb;
+analyze table t1 persistent for all;
+select column_name from mysql.column_stats where table_name = 't1';
+drop table t1;
+
+CREATE or replace TABLE t1 (a INT, b CHAR(8));
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+ALTER TABLE t1 CHANGE b c INT, ORDER BY b;
+SELECT db_name, table_name, column_name FROM mysql.column_stats where table_name = 't1';
+drop table t1;
+
+CREATE or replace TABLE t1 (a INT, b CHAR(8));
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+ALTER TABLE t1 RENAME COLUMN b to c, ALGORITHM=COPY;
+SELECT db_name, table_name, column_name FROM mysql.column_stats where table_name = 't1';
+drop table t1;
+
+--echo #
+--echo # Testing swapping indexes
+--echo #
+
+create or replace table t1 (a int primary key, b varchar(100), c varchar(100), d varchar(100), index (b), index(c), index(d,b)) engine=innodb;
+insert into t1 select seq, repeat('b',seq),repeat('c',mod(seq,5)), repeat('d',mod(seq,10))  from seq_1_to_100;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+select * from mysql.index_stats order by index_name, prefix_arity;
+alter table t1 rename index b to c, rename index c to d, rename index d to b;
+select * from mysql.index_stats order by index_name;
+
+alter table t1 rename index b to c, rename index c to d, rename index d to e;
+select * from mysql.index_stats order by index_name, prefix_arity;
+alter table t1 rename index e to b;
+alter table t1 change b c varchar(200), change c d varchar(200), change d e varchar(200) ;
+show create table t1;
+select * from mysql.index_stats order by index_name, prefix_arity;
+
+--echo # Test having a conflicting temporary name
+insert into mysql.index_stats (db_name,table_name,index_name,prefix_arity) values ("test","t1",concat("#sql_tmp_name#1",char(0)),1);
+alter table t1 rename index c to d, rename index d to c;
+select * from mysql.index_stats order by index_name, prefix_arity;
+drop table t1;
+select * from mysql.index_stats order by index_name, prefix_arity;
+
+--echo #
+--echo # Test of adding key that replaces foreign key
+--echo #
+
+CREATE TABLE t1 (aaaa INT, b INT, KEY(b), FOREIGN KEY(aaaa) REFERENCES t1(b)) ENGINE=InnoDB;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+
+SELECT index_name FROM mysql.index_stats WHERE table_name = 't1' order by index_name;
+
+ALTER TABLE t1 ADD KEY idx(aaaa);
+SHOW CREATE TABLE t1;
+
+SELECT index_name FROM mysql.index_stats WHERE table_name = 't1' order by index_name;
+
+truncate table mysql.index_stats;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+SELECT index_name FROM mysql.index_stats WHERE table_name = 't1' order by index_name;
+
+--error ER_DROP_INDEX_FK
+ALTER TABLE t1 DROP KEY idx;
+DROP TABLE t1;
+
+--echo #
+--echo # Check index rename where name is not changed
+--echo #
+
+create or replace table t1 (a int primary key, b int, c int, key b (b,c));
+show create table t1;
+analyze table t1 persistent for all;
+select * from mysql.index_stats where table_name= "t1";
+alter ignore table t1 rename key `b` to b, LOCK=shared;
+select * from mysql.index_stats where table_name= "t1";
+alter ignore table t1 rename key `b` to `B`, LOCK=shared;
+select * from mysql.index_stats where table_name= "t1";
+drop table t1;
+
+--echo #
+--echo # Crash inis_eits_usable()
+--echo #
+
+CREATE TABLE t1 (a int) ENGINE=MyISAM;
+
+CREATE TABLE t2 (b int) ENGINE=MyISAM;
+
+INSERT INTO t1 (a) VALUES (4), (6);
+INSERT INTO t2 (b) VALUES (0), (8);
+
+set @save_join_cache_level=@@join_cache_level;
+set @save_optimizer_switch=@@optimizer_switch;
+SET join_cache_level=3;
+SET optimizer_switch='join_cache_hashed=on';
+SET optimizer_switch='join_cache_bka=on';
+set optimizer_switch='hash_join_cardinality=on';
+
+EXPLAIN
+SELECT * FROM t1, t2 WHERE b=a;
+SELECT * FROM t1, t2 WHERE b=a;
+DROP TABLE t1,t2;
+
+set @@optimizer_switch=@save_optimizer_switch;
+set @@join_cache_level=@save_join_cache_level;
+
+--echo #
+--echo # MDEV-32531 MSAN / Valgrind errors in Item_func_like::get_mm_leaf with
+--echo #            temporal field
+--echo #
+
+CREATE TABLE t1 (f DATE);
+INSERT INTO t1 VALUES ('1978-08-27'),('1906-04-30');
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+SELECT * FROM t1 WHERE f LIKE '2023%';
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.6 tests
+--echo #