1# 2# Test of procedure analyse 3# 4-- source include/have_innodb.inc 5 6--disable_warnings 7drop table if exists t1,t2; 8--enable_warnings 9create table t1 (i int, j int, empty_string char(10), bool char(1), d date); 10insert 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"); 11select count(*) from t1 procedure analyse(); 12select * from t1 procedure analyse(); 13select * from t1 procedure analyse(2); 14--error ER_PARSE_ERROR 15create table t2 select * from t1 procedure analyse(); 16drop table t1; 17 18--error ER_WRONG_USAGE 19EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(); 20 21# 22# Bug#2813 - analyse does not quote string values in enums from string 23# 24 25create table t1 (v varchar(128)); 26insert 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'); 27select * from t1 procedure analyse(); 28drop table t1; 29 30#decimal-related test 31 32create table t1 (df decimal(5,1)); 33insert into t1 values(1.1); 34insert into t1 values(2.2); 35select * from t1 procedure analyse(); 36drop table t1; 37 38# 39# Bug#10716 - Procedure Analyse results in wrong values for optimal field type 40# 41 42create table t1 (d double); 43insert into t1 values (100000); 44select * from t1 procedure analyse (1,1); 45drop table t1; 46 47# 48# Bug #14138 ROLLUP and PROCEDURE ANALYSE() hang server 49# 50create table t1 (product varchar(32), country_id int not null, year int, 51 profit int); 52insert into t1 values ( 'Computer', 2,2000, 1200), 53 ( 'TV', 1, 1999, 150), 54 ( 'Calculator', 1, 1999,50), 55 ( 'Computer', 1, 1999,1500), 56 ( 'Computer', 1, 2000,1500), 57 ( 'TV', 1, 2000, 150), 58 ( 'TV', 2, 2000, 100), 59 ( 'TV', 2, 2000, 100), 60 ( 'Calculator', 1, 2000,75), 61 ( 'Calculator', 2, 2000,75), 62 ( 'TV', 1, 1999, 100), 63 ( 'Computer', 1, 1999,1200), 64 ( 'Computer', 2, 2000,1500), 65 ( 'Calculator', 2, 2000,75), 66 ( 'Phone', 3, 2003,10) 67 ; 68create table t2 (country_id int primary key, country char(20) not null); 69insert into t2 values (1, 'USA'),(2,'India'), (3,'Finland'); 70select product, sum(profit),avg(profit) from t1 group by product with rollup procedure analyse(); 71drop table t1,t2; 72 73# 74# Bug #20305 PROCEDURE ANALYSE() returns wrong M for FLOAT(M, D) and DOUBLE(M, D) 75# 76 77create table t1 (f1 double(10,5), f2 char(10), f3 double(10,5)); 78insert into t1 values (5.999, "5.9999", 5.99999), (9.555, "9.5555", 9.55555); 79select f1 from t1 procedure analyse(1, 1); 80select f2 from t1 procedure analyse(1, 1); 81select f3 from t1 procedure analyse(1, 1); 82drop table t1; 83 84# 85# Bug#46184 Crash, SELECT ... FROM derived table procedure analyze 86# 87CREATE TABLE t1(a INT,b INT,c INT,d INT,e INT,f INT,g INT,h INT,i INT,j INT,k INT); 88INSERT INTO t1 VALUES (); 89--error ER_WRONG_USAGE 90SELECT * FROM (SELECT * FROM t1) d PROCEDURE ANALYSE(); 91DROP TABLE t1; 92 93--echo End of 4.1 tests 94 95--echo # 96--echo # Bug #48293: crash with procedure analyse, view with > 10 columns, 97--echo # having clause... 98--echo # 99 100CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT, 101 f INT, g INT, h INT, i INT, j INT,k INT); 102INSERT INTO t1 VALUES (),(); 103 104CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; 105--echo #should have a derived table 106EXPLAIN SELECT * FROM v1; 107--echo #should not crash 108--error ER_WRONG_USAGE 109SELECT * FROM v1 PROCEDURE analyse(); 110--echo #should not crash 111--error ER_WRONG_USAGE 112SELECT * FROM t1 a, v1, t1 b PROCEDURE analyse(); 113--echo #should not crash 114--error ER_WRONG_USAGE 115SELECT * FROM (SELECT * FROM t1 having a > 1) x PROCEDURE analyse(); 116--echo #should not crash 117--error ER_WRONG_USAGE 118SELECT * FROM t1 a, (SELECT * FROM t1 having a > 1) x, t1 b PROCEDURE analyse(); 119--echo #should not crash 120--error ER_ORDER_WITH_PROC 121SELECT 1 FROM t1 group by a having a > 1 order by 1 PROCEDURE analyse(); 122 123DROP VIEW v1; 124DROP TABLE t1; 125 126CREATE TABLE t1(a INT); 127INSERT INTO t1 VALUES (1),(2); 128 129--echo # should not crash 130--error ER_PARSE_ERROR 131CREATE TABLE t2 SELECT 1 FROM t1, t1 t3 GROUP BY t3.a PROCEDURE ANALYSE(); 132 133DROP TABLE t1; 134 135 136--echo End of 5.0 tests 137 138--echo # 139--echo # Bug#11765202: Dbug_violation_helper::~Dbug_violation_helper(): Assertion `!_entered' failed. 140--echo # 141 142DROP TABLE IF EXISTS t1; 143CREATE TABLE t1 (a VARCHAR(2) CHARSET UTF8 NOT NULL); 144INSERT INTO t1 VALUES ('e'),('e'),('e-'); 145SELECT * FROM t1 PROCEDURE ANALYSE(); 146DROP TABLE t1; 147 148--echo # 149--echo # Bug#11756242 48137: PROCEDURE ANALYSE() LEAKS MEMORY WHEN RETURNING NULL 150--echo # 151 152CREATE TABLE t1(f1 INT) ENGINE=MYISAM; 153CREATE TABLE t2(f2 INT) ENGINE=INNODB; 154INSERT INTO t2 VALUES (1); 155SELECT DISTINCTROW f1 FROM t1 NATURAL RIGHT OUTER JOIN t2 PROCEDURE ANALYSE(); 156SELECT * FROM t2 LIMIT 1 PROCEDURE ANALYSE(); 157DROP TABLE t1, t2; 158 159--echo End of 5.1 tests 160 161--echo # 162--echo # Start of 10.2 tests 163--echo # 164--error ER_PARSE_ERROR 165(SELECT 1 FROM DUAL PROCEDURE ANALYSE()); 166--error ER_PARSE_ERROR 167((SELECT 1 FROM DUAL PROCEDURE ANALYSE())); 168(SELECT 1 FROM DUAL) PROCEDURE ANALYSE(); 169((SELECT 1 FROM DUAL)) PROCEDURE ANALYSE(); 170 171create table t1 (a int); 172--error ER_CANT_USE_OPTION_HERE 173SELECT * FROM t1 UNION SELECT * FROM t1 PROCEDURE analyse(); 174drop table t1; 175 176--echo # 177--echo # MDEV-10030 sql_yacc.yy: Split table_expression and remove PROCEDURE from create_select, select_paren_derived, select_derived2, query_specification 178--echo # 179 180--error ER_PARSE_ERROR 181SELECT * FROM (SELECT * FROM t1 PROCEDURE ANALYSE()); 182--ERROR ER_PARSE_ERROR 183SELECT * FROM t1 NATURAL JOIN (SELECT * FROM t2 PROCEDURE ANALYSE()); 184 185--error ER_PARSE_ERROR 186SELECT (SELECT 1 FROM t1 PROCEDURE ANALYSE()) FROM t2; 187--error ER_PARSE_ERROR 188SELECT ((SELECT 1 FROM t1 PROCEDURE ANALYSE())) FROM t2; 189 190 191--echo # 192--echo # Start of 10.4 tests 193--echo # 194 195--echo # 196--echo # MDEV-16309 Split ::create_tmp_field() into virtual methods in Item 197--echo # 198 199CREATE TABLE t1 (a INT); 200INSERT INTO t1 VALUES (1),(2),(3); 201--vertical_results 202DELIMITER $$; 203BEGIN NOT ATOMIC 204 DECLARE rec ROW(Field_name TEXT, 205 Min_value TEXT, 206 Max_value TEXT, 207 Min_length TEXT, 208 Max_length TEXT, 209 Empties_or_zeros TEXT, 210 Nulls TEXT, 211 Avg_value_or_avg_length TEXT, 212 Std TEXT, 213 Optimal_fieldtype TEXT); 214 DECLARE c CURSOR FOR SELECT * FROM t1 PROCEDURE analyse(); 215 OPEN c; 216 FETCH c INTO rec; 217 CLOSE c; 218 SELECT rec.field_name, 219 rec.Min_value, rec.Max_value, 220 rec.Min_length, rec. Max_length, 221 rec.Empties_or_zeros, rec.Nulls, 222 rec.Avg_value_or_avg_length, rec.Std, 223 rec.Optimal_fieldtype; 224END; 225$$ 226DELIMITER ;$$ 227--horizontal_results 228DROP TABLE t1; 229 230--echo # 231--echo # End of 10.4 tests 232--echo # 233