path: root/mysql-test/main/analyze.test

--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 #