mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
311 lines
11 KiB
Text
311 lines
11 KiB
Text
--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 #
|