From 3f619478f796eddbba6e39502fe941b285dd97b1 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Sat, 4 May 2024 20:00:34 +0200
Subject: Adding upstream version 1:10.11.6.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
---
 mysql-test/main/analyze.result | 457 +++++++++++++++++++++++++++++++++++++++++
 1 file changed, 457 insertions(+)
 create mode 100644 mysql-test/main/analyze.result

(limited to 'mysql-test/main/analyze.result')

diff --git a/mysql-test/main/analyze.result b/mysql-test/main/analyze.result
new file mode 100644
index 00000000..8819f15f
--- /dev/null
+++ b/mysql-test/main/analyze.result
@@ -0,0 +1,457 @@
+create table t1 (a bigint);
+lock tables t1 write;
+insert into t1 values(0);
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+unlock tables;
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+drop table t1;
+create table t1 (a bigint);
+insert into t1 values(0);
+lock tables t1 write;
+delete from t1;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+unlock tables;
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+drop table t1;
+create table t1 (a bigint);
+insert into t1 values(0);
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+drop table t1;
+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;
+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 'a'
+test.t1	analyze	status	OK
+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 'a'
+test.t1	analyze	status	Table is already up to date
+drop table t1;
+CREATE TABLE t1 (a int);
+prepare stmt1 from "SELECT * FROM t1 PROCEDURE ANALYSE()";
+execute stmt1;
+Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
+execute stmt1;
+Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
+deallocate prepare stmt1;
+drop table t1;
+create temporary table t1(a int, index(a));
+insert into t1 values('1'),('2'),('3'),('4'),('5');
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+show index from t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	a	1	a	A	5	NULL	NULL	YES	BTREE			NO
+drop table t1;
+End of 4.1 tests
+create table t1(a int);
+analyze table t1 extended;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'extended' at line 1
+optimize table t1 extended;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'extended' at line 1
+drop table t1;
+End of 5.0 tests
+#
+# Test analyze of text column (not yet supported)
+#
+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;
+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	Warning	Engine-independent statistics are not collected for column 'tx'
+test.t1	analyze	status	OK
+explain select count(*) from t1 where t='XXXXXX';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	61	Using where
+select column_name, min_value, max_value, hist_size from mysql.column_stats where table_name='t1';
+column_name	min_value	max_value	hist_size
+a	1	110	255
+drop table t1;
+set use_stat_tables=default;
+set histogram_type=default;
+set histogram_size=default;
+#
+# MDEV-31957 Concurrent ALTER and ANALYZE collecting statistics can
+# result in stale statistical data
+#
+CREATE TABLE t1 (a INT, b VARCHAR(128));
+INSERT INTO t1 SELECT seq, CONCAT('s',seq) FROM seq_1_to_100;
+connect  con1,localhost,root,,;
+ALTER TABLE t1 MODIFY b BLOB;
+connection default;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+connection con1;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+connection default;
+disconnect con1;
+select db_name,table_name,column_name from mysql.column_stats;
+db_name	table_name	column_name
+test	t1	a
+drop table t1;
+#
+# Testing swapping columns
+#
+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;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
+db_name	table_name	column_name	avg_length
+test	t1	a	4.0000
+test	t1	b	50.5000
+test	t1	c	2.0000
+test	t1	d	4.5000
+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;
+db_name	table_name	column_name	avg_length
+test	t1	a	4.0000
+test	t1	b	2.0000
+test	t1	c	50.5000
+test	t1	d	4.5000
+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;
+db_name	table_name	column_name	avg_length
+test	t1	a	4.0000
+test	t1	b	4.5000
+test	t1	c	2.0000
+test	t1	d	50.5000
+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;
+db_name	table_name	column_name	avg_length
+test	t1	a	4.0000
+test	t1	c	4.5000
+test	t1	d	2.0000
+test	t1	e	50.5000
+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;
+db_name	table_name	column_name	avg_length
+test	t1	a	4.0000
+test	t1	c	4.5000
+test	t1	d	50.5000
+# 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;
+db_name	table_name	column_name	avg_length
+test	t1	a	4.0000
+test	t1	b	50.5000
+test	t1	d	4.5000
+# 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;
+db_name	table_name	column_name	avg_length
+test	t1	a	4.0000
+test	t1	b	4.5000
+test	t1	d	50.5000
+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;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
+db_name	table_name	column_name	avg_length
+test	t1	a	4.0000
+test	t1	b	50.5000
+test	t1	c	2.0000
+test	t1	d	4.5000
+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;
+db_name	table_name	column_name	avg_length
+test	t1	a	4.0000
+test	t1	d	4.5000
+analyze table t1 persistent for columns(b,c) indexes all;
+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 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;
+db_name	table_name	column_name	avg_length
+test	t1	a	4.0000
+test	t1	b	50.5000
+test	t1	c	2.0000
+analyze table t1 persistent for columns(d) indexes all;
+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 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;
+db_name	table_name	column_name	avg_length
+test	t1	a	4.0000
+test	t1	c	50.5000
+test	t1	d	2.0000
+test	t1	e	50.5000
+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;
+db_name	table_name	column_name	avg_length
+test	t1	a	4.0000
+test	t1	c	50.5000
+test	t1	d	50.5000
+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;
+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 'b'
+test.t1	analyze	status	OK
+select column_name from mysql.column_stats where table_name = 't1';
+column_name
+a
+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;
+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 'b'
+test.t1	analyze	status	OK
+select column_name from mysql.column_stats where table_name = 't1';
+column_name
+a
+c
+drop table t1;
+CREATE or replace TABLE t1 (a INT, b CHAR(8));
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+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 CHANGE b c INT, ORDER BY b;
+SELECT db_name, table_name, column_name FROM mysql.column_stats where table_name = 't1';
+db_name	table_name	column_name
+test	t1	a
+test	t1	c
+drop table t1;
+CREATE or replace TABLE t1 (a INT, b CHAR(8));
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+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 RENAME COLUMN b to c, ALGORITHM=COPY;
+SELECT db_name, table_name, column_name FROM mysql.column_stats where table_name = 't1';
+db_name	table_name	column_name
+test	t1	a
+test	t1	c
+drop table t1;
+#
+# Testing swapping indexes
+#
+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;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+select * from mysql.index_stats order by index_name, prefix_arity;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	PRIMARY	1	1.0000
+test	t1	b	1	1.0000
+test	t1	b	2	1.0000
+test	t1	c	1	20.0000
+test	t1	c	2	1.0000
+test	t1	d	1	10.0000
+test	t1	d	2	1.0000
+test	t1	d	3	1.0000
+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;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	PRIMARY	1	1.0000
+test	t1	b	1	10.0000
+test	t1	b	2	1.0000
+test	t1	b	3	1.0000
+test	t1	c	1	1.0000
+test	t1	c	2	1.0000
+test	t1	d	1	20.0000
+test	t1	d	2	1.0000
+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;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	PRIMARY	1	1.0000
+test	t1	c	1	10.0000
+test	t1	c	2	1.0000
+test	t1	c	3	1.0000
+test	t1	d	1	1.0000
+test	t1	d	2	1.0000
+test	t1	e	1	20.0000
+test	t1	e	2	1.0000
+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;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `c` varchar(200) DEFAULT NULL,
+  `d` varchar(200) DEFAULT NULL,
+  `e` varchar(200) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `d` (`c`),
+  KEY `b` (`d`),
+  KEY `c` (`e`,`c`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+select * from mysql.index_stats order by index_name, prefix_arity;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	PRIMARY	1	1.0000
+test	t1	b	1	20.0000
+test	t1	b	2	1.0000
+test	t1	c	1	10.0000
+test	t1	c	2	1.0000
+test	t1	c	3	1.0000
+test	t1	d	1	1.0000
+test	t1	d	2	1.0000
+# 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;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	PRIMARY	1	1.0000
+test	t1	b	1	20.0000
+test	t1	b	2	1.0000
+test	t1	c	1	1.0000
+test	t1	c	2	1.0000
+test	t1	d	1	10.0000
+test	t1	d	2	1.0000
+test	t1	d	3	1.0000
+drop table t1;
+select * from mysql.index_stats order by index_name, prefix_arity;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+#
+# Test of adding key that replaces foreign key
+#
+CREATE TABLE t1 (aaaa INT, b INT, KEY(b), FOREIGN KEY(aaaa) REFERENCES t1(b)) ENGINE=InnoDB;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+SELECT index_name FROM mysql.index_stats WHERE table_name = 't1' order by index_name;
+index_name
+aaaa
+b
+ALTER TABLE t1 ADD KEY idx(aaaa);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `aaaa` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  KEY `b` (`b`),
+  KEY `idx` (`aaaa`),
+  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`aaaa`) REFERENCES `t1` (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT index_name FROM mysql.index_stats WHERE table_name = 't1' order by index_name;
+index_name
+b
+truncate table mysql.index_stats;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+SELECT index_name FROM mysql.index_stats WHERE table_name = 't1' order by index_name;
+index_name
+b
+idx
+ALTER TABLE t1 DROP KEY idx;
+ERROR HY000: Cannot drop index 'idx': needed in a foreign key constraint
+DROP TABLE t1;
+#
+# Check index rename where name is not changed
+#
+create or replace table t1 (a int primary key, b int, c int, key b (b,c));
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `b` (`b`,`c`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+select * from mysql.index_stats where table_name= "t1";
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	PRIMARY	1	1.0000
+test	t1	b	1	NULL
+test	t1	b	2	NULL
+alter ignore table t1 rename key `b` to b, LOCK=shared;
+select * from mysql.index_stats where table_name= "t1";
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	PRIMARY	1	1.0000
+test	t1	b	1	NULL
+test	t1	b	2	NULL
+alter ignore table t1 rename key `b` to `B`, LOCK=shared;
+select * from mysql.index_stats where table_name= "t1";
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	PRIMARY	1	1.0000
+test	t1	B	1	NULL
+test	t1	B	2	NULL
+drop table t1;
+#
+# Crash inis_eits_usable()
+#
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.a	2	Using where; Using join buffer (flat, BNLH join)
+SELECT * FROM t1, t2 WHERE b=a;
+a	b
+DROP TABLE t1,t2;
+set @@optimizer_switch=@save_optimizer_switch;
+set @@join_cache_level=@save_join_cache_level;
+#
+# MDEV-32531 MSAN / Valgrind errors in Item_func_like::get_mm_leaf with
+#            temporal field
+#
+CREATE TABLE t1 (f DATE);
+INSERT INTO t1 VALUES ('1978-08-27'),('1906-04-30');
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+SELECT * FROM t1 WHERE f LIKE '2023%';
+f
+DROP TABLE t1;
+#
+# End of 10.6 tests
+#
-- 
cgit v1.2.3