1drop table if exists t1,t2; 2create table t1 (i int, j int, empty_string char(10), bool char(1), d date); 3insert into t1 values (1,2,"","Y","2002-03-03"), (3,4,"","N","2002-03-04"), (5,6,"","Y","2002-03-04"), (7,8,"","N","2002-03-05"); 4select count(*) from t1 procedure analyse(); 5Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 6count(*) 4 4 1 1 0 0 4.0000 0.0000 ENUM('4') NOT NULL 7Warnings: 8Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 9select * from t1 procedure analyse(); 10Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 11test.t1.i 1 7 1 1 0 0 4.0000 2.2361 ENUM('1','3','5','7') NOT NULL 12test.t1.j 2 8 1 1 0 0 5.0000 2.2361 ENUM('2','4','6','8') NOT NULL 13test.t1.empty_string 0 0 4 0 0.0000 NULL CHAR(0) NOT NULL 14test.t1.bool N Y 1 1 0 0 1.0000 NULL ENUM('N','Y') NOT NULL 15test.t1.d 2002-03-03 2002-03-05 10 10 0 0 10.0000 NULL ENUM('2002-03-03','2002-03-04','2002-03-05') NOT NULL 16Warnings: 17Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 18select * from t1 procedure analyse(2); 19Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 20test.t1.i 1 7 1 1 0 0 4.0000 2.2361 TINYINT(1) UNSIGNED NOT NULL 21test.t1.j 2 8 1 1 0 0 5.0000 2.2361 TINYINT(1) UNSIGNED NOT NULL 22test.t1.empty_string 0 0 4 0 0.0000 NULL CHAR(0) NOT NULL 23test.t1.bool N Y 1 1 0 0 1.0000 NULL ENUM('N','Y') NOT NULL 24test.t1.d 2002-03-03 2002-03-05 10 10 0 0 10.0000 NULL ENUM('2002-03-03','2002-03-04','2002-03-05') NOT NULL 25Warnings: 26Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 27create table t2 select * from t1 procedure analyse(); 28ERROR HY000: Incorrect usage of PROCEDURE and non-SELECT 29drop table t1; 30SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(); 31Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 321 1 1 1 1 0 0 1.0000 0.0000 ENUM('1') NOT NULL 33Warnings: 34Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 35EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(); 36id select_type table partitions type possible_keys key key_len ref rows filtered Extra 371 PRIMARY <derived2> NULL system NULL NULL NULL NULL 1 100.00 NULL 382 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 39Warnings: 40Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 41Note 1003 /* select#1 */ select 1 AS `1` from dual 42EXPLAIN SELECT 1 FROM (SELECT 1) a; 43id select_type table partitions type possible_keys key key_len ref rows filtered Extra 441 PRIMARY <derived2> NULL system NULL NULL NULL NULL 1 100.00 NULL 452 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 46Warnings: 47Note 1003 /* select#1 */ select 1 AS `1` from dual 48create table t1 (v varchar(128)); 49insert into t1 values ('abc'),('abc\'def\\hij\"klm\0opq'),('\''),('\"'),('\\'),('a\0'),('b\''),('c\"'),('d\\'),('\'b'),('\"c'),('\\d'),('a\0\0\0b'),('a\'\'\'\'b'),('a\"\"\"\"b'),('a\\\\\\\\b'),('\'\0\\\"'),('\'\''),('\"\"'),('\\\\'),('The\ZEnd'); 50select * from t1 procedure analyse(); 51Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 52test.t1.v " \\ 1 19 0 0 3.7619 NULL ENUM('"','""','"c','\'\0\\"','\'','\'\'','\'b','a\0\0\0b','a\0','a""""b','a\'\'\'\'b','abc','abc\'def\\hij"klm\0opq','a\\\\\\\\b','b\'','c"','d\\','The\ZEnd','\\','\\d','\\\\') NOT NULL 53Warnings: 54Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 55drop table t1; 56create table t1 (df decimal(5,1)); 57insert into t1 values(1.1); 58insert into t1 values(2.2); 59select * from t1 procedure analyse(); 60Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 61test.t1.df 1.1 2.2 13 13 0 0 1.65000 0.55000 ENUM('1.1','2.2') NOT NULL 62Warnings: 63Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 64drop table t1; 65create table t1 (d double); 66insert into t1 values (100000); 67select * from t1 procedure analyse (1,1); 68Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 69test.t1.d 100000 100000 6 6 0 0 100000 0 MEDIUMINT(6) UNSIGNED NOT NULL 70Warnings: 71Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 72drop table t1; 73create table t1 (product varchar(32), country_id int not null, year int, 74profit int); 75insert into t1 values ( 'Computer', 2,2000, 1200), 76( 'TV', 1, 1999, 150), 77( 'Calculator', 1, 1999,50), 78( 'Computer', 1, 1999,1500), 79( 'Computer', 1, 2000,1500), 80( 'TV', 1, 2000, 150), 81( 'TV', 2, 2000, 100), 82( 'TV', 2, 2000, 100), 83( 'Calculator', 1, 2000,75), 84( 'Calculator', 2, 2000,75), 85( 'TV', 1, 1999, 100), 86( 'Computer', 1, 1999,1200), 87( 'Computer', 2, 2000,1500), 88( 'Calculator', 2, 2000,75), 89( 'Phone', 3, 2003,10) 90; 91create table t2 (country_id int primary key, country char(20) not null); 92insert into t2 values (1, 'USA'),(2,'India'), (3,'Finland'); 93select product, sum(profit),avg(profit) from t1 group by product with rollup; 94product sum(profit) avg(profit) 95Calculator 275 68.7500 96Computer 6900 1380.0000 97Phone 10 10.0000 98TV 600 120.0000 99NULL 7785 519.0000 100select product, sum(profit),avg(profit) from t1 group by product with rollup procedure analyse(); 101Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 102test.t1.product Calculator TV 2 10 0 0 5.4000 NULL ENUM('Calculator','Computer','Phone','TV') NOT NULL 103sum(profit) 10 6900 11 11 0 0 1677.0000 2620.8426 ENUM('10','275','600','6900') NOT NULL 104avg(profit) 10.0000 1380.0000 16 16 0 0 339.75000000 521.70417863 ENUM('10.0000','68.7500','120.0000','1380.0000') NOT NULL 105Warnings: 106Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 107drop table t1,t2; 108create table t1 (f1 double(10,5), f2 char(10), f3 double(10,5)); 109insert into t1 values (5.999, "5.9999", 5.99999), (9.555, "9.5555", 9.55555); 110select f1 from t1 procedure analyse(1, 1); 111Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 112test.t1.f1 5.99900 9.55500 7 7 0 0 7.77700 1.77800 FLOAT(4,3) NOT NULL 113Warnings: 114Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 115select f2 from t1 procedure analyse(1, 1); 116Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 117test.t1.f2 5.9999 9.5555 6 6 0 0 6.0000 NULL FLOAT(5,4) UNSIGNED NOT NULL 118Warnings: 119Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 120select f3 from t1 procedure analyse(1, 1); 121Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 122test.t1.f3 5.99999 9.55555 7 7 0 0 7.77777 1.77778 FLOAT(6,5) NOT NULL 123Warnings: 124Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 125drop table t1; 126set @optimizer_switch_saved=@@session.optimizer_switch; 127set optimizer_switch='derived_merge=off'; 128CREATE TABLE t1(a INT,b INT,c INT,d INT,e INT,f INT,g INT,h INT,i INT,j INT,k INT); 129INSERT INTO t1 VALUES (); 130SELECT * FROM (SELECT * FROM t1) d PROCEDURE ANALYSE(); 131Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 132d.a NULL NULL 0 0 0 1 0.0 0.0 CHAR(0) 133d.b NULL NULL 0 0 0 1 0.0 0.0 CHAR(0) 134d.c NULL NULL 0 0 0 1 0.0 0.0 CHAR(0) 135d.d NULL NULL 0 0 0 1 0.0 0.0 CHAR(0) 136d.e NULL NULL 0 0 0 1 0.0 0.0 CHAR(0) 137d.f NULL NULL 0 0 0 1 0.0 0.0 CHAR(0) 138d.g NULL NULL 0 0 0 1 0.0 0.0 CHAR(0) 139d.h NULL NULL 0 0 0 1 0.0 0.0 CHAR(0) 140d.i NULL NULL 0 0 0 1 0.0 0.0 CHAR(0) 141d.j NULL NULL 0 0 0 1 0.0 0.0 CHAR(0) 142d.k NULL NULL 0 0 0 1 0.0 0.0 CHAR(0) 143Warnings: 144Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 145DROP TABLE t1; 146set @@session.optimizer_switch=@optimizer_switch_saved; 147End of 4.1 tests 148# 149# Bug #48293: crash with procedure analyse, view with > 10 columns, 150# having clause... 151# 152CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT, 153f INT, g INT, h INT, i INT, j INT,k INT); 154INSERT INTO t1 VALUES (),(); 155ANALYZE TABLE t1; 156CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; 157#should have a derived table 158EXPLAIN SELECT * FROM v1; 159id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1601 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 100.00 NULL 1612 DERIVED t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 162Warnings: 163Note 1003 /* select#1 */ select `v1`.`a` AS `a`,`v1`.`b` AS `b`,`v1`.`c` AS `c`,`v1`.`d` AS `d`,`v1`.`e` AS `e`,`v1`.`f` AS `f`,`v1`.`g` AS `g`,`v1`.`h` AS `h`,`v1`.`i` AS `i`,`v1`.`j` AS `j`,`v1`.`k` AS `k` from `test`.`v1` 164#should not crash 165SELECT * FROM v1 PROCEDURE analyse(); 166Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 167v1.a NULL NULL 0 0 0 2 0.0 0.0 CHAR(0) 168v1.b NULL NULL 0 0 0 2 0.0 0.0 CHAR(0) 169v1.c NULL NULL 0 0 0 2 0.0 0.0 CHAR(0) 170v1.d NULL NULL 0 0 0 2 0.0 0.0 CHAR(0) 171v1.e NULL NULL 0 0 0 2 0.0 0.0 CHAR(0) 172v1.f NULL NULL 0 0 0 2 0.0 0.0 CHAR(0) 173v1.g NULL NULL 0 0 0 2 0.0 0.0 CHAR(0) 174v1.h NULL NULL 0 0 0 2 0.0 0.0 CHAR(0) 175v1.i NULL NULL 0 0 0 2 0.0 0.0 CHAR(0) 176v1.j NULL NULL 0 0 0 2 0.0 0.0 CHAR(0) 177v1.k NULL NULL 0 0 0 2 0.0 0.0 CHAR(0) 178Warnings: 179Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 180#should not crash 181SELECT * FROM t1 a, v1, t1 b PROCEDURE analyse(); 182Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 183test.a.a NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 184test.a.b NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 185test.a.c NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 186test.a.d NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 187test.a.e NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 188test.a.f NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 189test.a.g NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 190test.a.h NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 191test.a.i NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 192test.a.j NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 193test.a.k NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 194v1.a NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 195v1.b NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 196v1.c NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 197v1.d NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 198v1.e NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 199v1.f NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 200v1.g NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 201v1.h NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 202v1.i NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 203v1.j NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 204v1.k NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 205test.b.a NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 206test.b.b NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 207test.b.c NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 208test.b.d NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 209test.b.e NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 210test.b.f NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 211test.b.g NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 212test.b.h NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 213test.b.i NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 214test.b.j NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 215test.b.k NULL NULL 0 0 0 8 0.0 0.0 CHAR(0) 216Warnings: 217Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 218#should not crash 219SELECT * FROM (SELECT * FROM t1 having a > 1) x PROCEDURE analyse(); 220Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 221Warnings: 222Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 223#should not crash 224SELECT * FROM t1 a, (SELECT * FROM t1 having a > 1) x, t1 b PROCEDURE analyse(); 225Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 226Warnings: 227Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 228#should not crash 229SELECT 1 FROM t1 group by a having a > 1 order by 1 PROCEDURE analyse(); 230Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 231Warnings: 232Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 233DROP VIEW v1; 234DROP TABLE t1; 235CREATE TABLE t1(a INT); 236INSERT INTO t1 VALUES (1),(2); 237# should not crash 238CREATE TABLE t2 SELECT 1 FROM t1, t1 t3 GROUP BY t3.a PROCEDURE ANALYSE(); 239ERROR HY000: Incorrect usage of PROCEDURE and non-SELECT 240DROP TABLE t1; 241End of 5.0 tests 242# 243# Bug#11765202: Dbug_violation_helper::~Dbug_violation_helper(): Assertion `!_entered' failed. 244# 245DROP TABLE IF EXISTS t1; 246Warnings: 247Note 1051 Unknown table 'test.t1' 248CREATE TABLE t1 (a VARCHAR(2) CHARSET UTF8 NOT NULL); 249INSERT INTO t1 VALUES ('e'),('e'),('e-'); 250SELECT * FROM t1 PROCEDURE ANALYSE(); 251Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 252test.t1.a e e- 1 2 0 0 1.3333 NULL ENUM('e','e-') NOT NULL 253Warnings: 254Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 255DROP TABLE t1; 256# 257# Bug#11756242 48137: PROCEDURE ANALYSE() LEAKS MEMORY WHEN RETURNING NULL 258# 259CREATE TABLE t1(f1 INT) ENGINE=MYISAM; 260CREATE TABLE t2(f2 INT) ENGINE=INNODB; 261INSERT INTO t2 VALUES (1); 262SELECT DISTINCTROW f1 FROM t1 NATURAL RIGHT OUTER JOIN t2 PROCEDURE ANALYSE(); 263Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 264test.t1.f1 NULL NULL 0 0 0 1 0.0 0.0 CHAR(0) 265Warnings: 266Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 267SELECT * FROM t2 LIMIT 1 PROCEDURE ANALYSE(); 268Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 269test.t2.f2 1 1 1 1 0 0 1.0000 0.0000 ENUM('1') NOT NULL 270Warnings: 271Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 272DROP TABLE t1, t2; 273End of 5.1 tests 274# 275# Bug #47338 assertion in handler::ha_external_lock 276# 277drop table if exists t1; 278CREATE TEMPORARY TABLE t1 (f2 INT, f1 INT, PRIMARY KEY (f1)) ENGINE = MyISAM; 279INSERT t1 ( f1 ) VALUES ( 5 ); 280INSERT t1 ( f1 ) VALUES ( 6 ); 281ALTER TABLE t1 ENGINE = MyISAM; 282ANALYZE TABLE t1; 283Table Op Msg_type Msg_text 284test.t1 analyze status OK 285SELECT f1,f2 FROM t1 A WHERE f1 BETWEEN 0 AND 1; 286f1 f2 287drop table t1; 288# 289# Bug #13358379 EXPLAIN SELECT ... PROCEDURE ANALYZE CRASHES THE SERVER 290# 291CREATE TABLE t1 (i INT); 292EXPLAIN SELECT * FROM t1 PROCEDURE ANALYSE(); 293DROP TABLE t1; 294# 295# WL#6242: Move "PROCEDURE ANALYSE" implementation 296# to select_send-like class 297# 298# Coverage tests 299# 300 301SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 302Warnings: 303Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 304CREATE TABLE t1 (a INT); 305# Trivial query: 306SELECT * FROM t1 PROCEDURE ANALYSE(); 307Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 308Warnings: 309Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 310# PROCEDURE ANALYSE in PS: 311INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 312PREPARE stmt1 FROM "SELECT * FROM t1 PROCEDURE ANALYSE()"; 313Warnings: 314Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 315EXECUTE stmt1; 316Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 317test.t1.a 1 5 1 1 0 0 3.0000 1.4142 ENUM('1','2','3','4','5') NOT NULL 318EXECUTE stmt1; 319Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 320test.t1.a 1 5 1 1 0 0 3.0000 1.4142 ENUM('1','2','3','4','5') NOT NULL 321EXECUTE stmt1; 322Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 323test.t1.a 1 5 1 1 0 0 3.0000 1.4142 ENUM('1','2','3','4','5') NOT NULL 324DEALLOCATE PREPARE stmt1; 325# PROCEDURE ANALYSE in SP: 326CREATE PROCEDURE p1() 327BEGIN 328SELECT * FROM t1 PROCEDURE ANALYSE(); 329END;| 330Warnings: 331Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 332CALL p1(); 333Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 334test.t1.a 1 5 1 1 0 0 3.0000 1.4142 ENUM('1','2','3','4','5') NOT NULL 335Warnings: 336Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 337DROP PROCEDURE p1; 338# PROCEDURE ANALYSE in SP cursor: 339CREATE PROCEDURE p1() 340BEGIN 341DECLARE c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 CHAR(20); 342DECLARE done INT DEFAULT 0; 343DECLARE cur1 CURSOR FOR SELECT * FROM t1 PROCEDURE ANALYSE(); 344DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 345OPEN cur1; 346read_loop: LOOP 347FETCH cur1 INTO c1, c2, c3, c4, c5, c6, c7, c8, c9, c10; 348IF done THEN 349LEAVE read_loop; 350END IF; 351END LOOP; 352CLOSE cur1; 353END;| 354Warnings: 355Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 356CALL p1(); 357Warnings: 358Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 359DROP PROCEDURE p1; 360# EXPLAIN with or wihtout PROCEDURE ANALYSE should be same: 361ANALYZE TABLE t1; 362EXPLAIN SELECT * FROM t1, (SELECT * FROM t1) tt1 WHERE t1.a = tt1.a; 363id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3641 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 3651 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 20.00 Using where; Using join buffer (Block Nested Loop) 366Warnings: 367Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t1`.`a`) 368EXPLAIN SELECT * FROM t1, (SELECT * FROM t1) tt1 WHERE t1.a = tt1.a PROCEDURE ANALYSE(); 369id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3701 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 3711 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 20.00 Using where; Using join buffer (Block Nested Loop) 372Warnings: 373Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 374Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t1`.`a`) 375CREATE TABLE t2 (i INT, j INT); 376# PROCEDURE ANALYSE ignores ROLLUP rows: 377INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4); 378SELECT i, AVG(j) FROM t2 GROUP BY i WITH ROLLUP; 379i AVG(j) 3801 1.0000 3812 2.0000 3823 3.0000 3834 4.0000 384NULL 2.5000 385SELECT i, AVG(j) FROM t2 GROUP BY i WITH ROLLUP PROCEDURE ANALYSE(); 386Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 387test.t2.i 1 4 1 1 0 0 2.8000 1.1662 ENUM('1','2','3','4') NOT NULL 388AVG(j) 1.0000 4.0000 16 16 0 0 2.80000000 1.16619038 ENUM('1.0000','2.0000','3.0000','4.0000') NOT NULL 389Warnings: 390Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 391SELECT i, AVG(j) FROM t2 GROUP BY i PROCEDURE ANALYSE(); 392Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 393test.t2.i 1 4 1 1 0 0 2.5000 1.1180 ENUM('1','2','3','4') NOT NULL 394AVG(j) 1.0000 4.0000 16 16 0 0 2.50000000 1.11803399 ENUM('1.0000','2.0000','3.0000','4.0000') NOT NULL 395Warnings: 396Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 397# LIMIT affects original SELECT output, not PROCEDURE ANALYSE output: 398SELECT * FROM t2 LIMIT 1 PROCEDURE ANALYSE(); 399Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype 400test.t2.i 1 1 1 1 0 0 1.0000 0.0000 ENUM('1') NOT NULL 401test.t2.j 1 1 1 1 0 0 1.0000 0.0000 ENUM('1') NOT NULL 402Warnings: 403Warning 1681 'PROCEDURE ANALYSE' is deprecated and will be removed in a future release. 404# Wrong usage: CREATE TABLE/VIEW and INSERT from SELECT: 405CREATE TABLE t SELECT * FROM t1 PROCEDURE ANALYSE(); 406ERROR HY000: Incorrect usage of PROCEDURE and non-SELECT 407CREATE VIEW v AS SELECT * FROM t1 PROCEDURE ANALYSE(); 408ERROR HY000: View's SELECT contains a 'PROCEDURE' clause 409INSERT INTO t1 SELECT * FROM t1 PROCEDURE ANALYSE(); 410ERROR HY000: Incorrect usage of PROCEDURE and non-SELECT 411# Wrong usage: subquery with PROCEDURE ANALYSE(): 412SELECT (SELECT * FROM t1 PROCEDURE ANALYSE()); 413ERROR HY000: Incorrect usage of PROCEDURE and subquery 414SELECT (SELECT * FROM t1 PROCEDURE ANALYSE()) PROCEDURE ANALYSE; 415ERROR HY000: Incorrect usage of PROCEDURE and subquery 416SELECT * FROM (SELECT * FROM t1 PROCEDURE ANALYSE()) tt1; 417ERROR HY000: Incorrect usage of PROCEDURE and subquery 418SELECT * FROM t1 WHERE a IN (SELECT * FROM t1 PROCEDURE ANALYSE()); 419ERROR HY000: Incorrect usage of PROCEDURE and subquery 420# Wrong usage: SELECT ... INTO 421SELECT i INTO @a FROM t1 LIMIT 1 PROCEDURE ANALYSE(); 422ERROR HY000: Incorrect usage of PROCEDURE and INTO 423SELECT * INTO OUTFILE "MYSQLTEST_VARDIR/wl6242.outfile" FROM t1 PROCEDURE ANALYSE(); 424ERROR HY000: Incorrect usage of PROCEDURE and INTO 425SELECT * INTO DUMPFILE "MYSQLTEST_VARDIR/wl6242.dumpfile" FROM t1 PROCEDURE ANALYSE(); 426ERROR HY000: Incorrect usage of PROCEDURE and INTO 427# Wrong usage: UNION: 428SELECT * FROM t1 UNION SELECT * FROM t1 PROCEDURE ANALYSE(); 429ERROR HY000: Incorrect usage of PROCEDURE and subquery 430SELECT * FROM t1 PROCEDURE ANALYSE() UNION SELECT * FROM t1; 431ERROR HY000: Incorrect usage of UNION and SELECT ... PROCEDURE ANALYSE() 432# Wrong 1st parameter: 433SELECT * FROM t1 PROCEDURE ANALYSE((SELECT 1)); 434ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT 1))' at line 1 435SELECT * FROM t1 PROCEDURE ANALYSE('test'); 436ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''test')' at line 1 437SELECT * FROM t1 PROCEDURE ANALYSE(-100); 438ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-100)' at line 1 439SELECT * FROM t1 PROCEDURE ANALYSE(3.1415); 440ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3.1415)' at line 1 441# Wrong 2nd parameter: 442SELECT * FROM t1 PROCEDURE ANALYSE(100, (SELECT 1)); 443ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT 1))' at line 1 444SELECT * FROM t1 PROCEDURE ANALYSE(100, 'test'); 445ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''test')' at line 1 446SELECT * FROM t1 PROCEDURE ANALYSE(100, -100); 447ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-100)' at line 1 448SELECT * FROM t1 PROCEDURE ANALYSE(100, 3.1415); 449ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3.1415)' at line 1 450# Wrong parameter count: 451SELECT * FROM t1 PROCEDURE ANALYSE(100, 200, 300); 452ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 300)' at line 1 453DROP TABLE t1, t2; 454SET sql_mode = default; 455# End of 5.6 tests 456