diff options
Diffstat (limited to 'mysql-test/t/func_analyse.test')
-rw-r--r-- | mysql-test/t/func_analyse.test | 135 |
1 files changed, 135 insertions, 0 deletions
diff --git a/mysql-test/t/func_analyse.test b/mysql-test/t/func_analyse.test new file mode 100644 index 00000000000..05f739bfd69 --- /dev/null +++ b/mysql-test/t/func_analyse.test @@ -0,0 +1,135 @@ +# +# Test of procedure analyse +# + +--disable_warnings +drop table if exists t1,t2; +--enable_warnings +create table t1 (i int, j int, empty_string char(10), bool char(1), d date); +insert 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"); +select count(*) from t1 procedure analyse(); +select * from t1 procedure analyse(); +select * from t1 procedure analyse(2); +--error ER_WRONG_USAGE +create table t2 select * from t1 procedure analyse(); +drop table t1; + +--error ER_WRONG_USAGE +EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(); + +# +# Bug#2813 - analyse does not quote string values in enums from string +# + +create table t1 (v varchar(128)); +insert 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'); +select * from t1 procedure analyse(); +drop table t1; + +#decimal-related test + +create table t1 (df decimal(5,1)); +insert into t1 values(1.1); +insert into t1 values(2.2); +select * from t1 procedure analyse(); +drop table t1; + +# +# Bug#10716 - Procedure Analyse results in wrong values for optimal field type +# + +create table t1 (d double); +insert into t1 values (100000); +select * from t1 procedure analyse (1,1); +drop table t1; + +# +# Bug #14138 ROLLUP and PROCEDURE ANALYSE() hang server +# +create table t1 (product varchar(32), country_id int not null, year int, + profit int); +insert into t1 values ( 'Computer', 2,2000, 1200), + ( 'TV', 1, 1999, 150), + ( 'Calculator', 1, 1999,50), + ( 'Computer', 1, 1999,1500), + ( 'Computer', 1, 2000,1500), + ( 'TV', 1, 2000, 150), + ( 'TV', 2, 2000, 100), + ( 'TV', 2, 2000, 100), + ( 'Calculator', 1, 2000,75), + ( 'Calculator', 2, 2000,75), + ( 'TV', 1, 1999, 100), + ( 'Computer', 1, 1999,1200), + ( 'Computer', 2, 2000,1500), + ( 'Calculator', 2, 2000,75), + ( 'Phone', 3, 2003,10) + ; +create table t2 (country_id int primary key, country char(20) not null); +insert into t2 values (1, 'USA'),(2,'India'), (3,'Finland'); +select product, sum(profit),avg(profit) from t1 group by product with rollup procedure analyse(); +drop table t1,t2; + +# +# Bug #20305 PROCEDURE ANALYSE() returns wrong M for FLOAT(M, D) and DOUBLE(M, D) +# + +create table t1 (f1 double(10,5), f2 char(10), f3 double(10,5)); +insert into t1 values (5.999, "5.9999", 5.99999), (9.555, "9.5555", 9.55555); +select f1 from t1 procedure analyse(1, 1); +select f2 from t1 procedure analyse(1, 1); +select f3 from t1 procedure analyse(1, 1); +drop table t1; + +# +# Bug#46184 Crash, SELECT ... FROM derived table procedure analyze +# +CREATE TABLE t1(a INT,b INT,c INT,d INT,e INT,f INT,g INT,h INT,i INT,j INT,k INT); +INSERT INTO t1 VALUES (); +--error ER_WRONG_USAGE +SELECT * FROM (SELECT * FROM t1) d PROCEDURE ANALYSE(); +DROP TABLE t1; + +--echo End of 4.1 tests + +--echo # +--echo # Bug #48293: crash with procedure analyse, view with > 10 columns, +--echo # having clause... +--echo # + +CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT, + f INT, g INT, h INT, i INT, j INT,k INT); +INSERT INTO t1 VALUES (),(); + +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +--echo #should have a derived table +EXPLAIN SELECT * FROM v1; +--echo #should not crash +--error ER_WRONG_USAGE +SELECT * FROM v1 PROCEDURE analyse(); +--echo #should not crash +--error ER_WRONG_USAGE +SELECT * FROM t1 a, v1, t1 b PROCEDURE analyse(); +--echo #should not crash +--error ER_WRONG_USAGE +SELECT * FROM (SELECT * FROM t1 having a > 1) x PROCEDURE analyse(); +--echo #should not crash +--error ER_WRONG_USAGE +SELECT * FROM t1 a, (SELECT * FROM t1 having a > 1) x, t1 b PROCEDURE analyse(); +--echo #should not crash +--error ER_ORDER_WITH_PROC +SELECT 1 FROM t1 group by a having a > 1 order by 1 PROCEDURE analyse(); + +DROP VIEW v1; +DROP TABLE t1; + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1),(2); + +--echo # should not crash +--error ER_WRONG_USAGE +CREATE TABLE t2 SELECT 1 FROM t1, t1 t3 GROUP BY t3.a PROCEDURE ANALYSE(); + +DROP TABLE t1; + + +--echo End of 5.0 tests |