diff options
| -rw-r--r-- | mysql-test/r/analyse.result | 125 | ||||
| -rw-r--r-- | mysql-test/r/analyze.result | 66 | ||||
| -rw-r--r-- | mysql-test/r/func_analyse.result | 173 | ||||
| -rw-r--r-- | mysql-test/t/analyse.test | 135 | ||||
| -rw-r--r-- | mysql-test/t/analyze.test | 87 | ||||
| -rw-r--r-- | mysql-test/t/func_analyse.test | 172 |
6 files changed, 379 insertions, 379 deletions
diff --git a/mysql-test/r/analyse.result b/mysql-test/r/analyse.result deleted file mode 100644 index 1820782d2f8..00000000000 --- a/mysql-test/r/analyse.result +++ /dev/null @@ -1,125 +0,0 @@ -drop table if exists t1,t2; -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(); -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -count(*) 4 4 1 1 0 0 4.0000 0.0000 ENUM('4') NOT NULL -select * from t1 procedure analyse(); -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -test.t1.i 1 7 1 1 0 0 4.0000 2.2361 ENUM('1','3','5','7') NOT NULL -test.t1.j 2 8 1 1 0 0 5.0000 2.2361 ENUM('2','4','6','8') NOT NULL -test.t1.empty_string 0 0 4 0 0.0000 NULL CHAR(0) NOT NULL -test.t1.bool N Y 1 1 0 0 1.0000 NULL ENUM('N','Y') NOT NULL -test.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 -select * from t1 procedure analyse(2); -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -test.t1.i 1 7 1 1 0 0 4.0000 2.2361 TINYINT(1) UNSIGNED NOT NULL -test.t1.j 2 8 1 1 0 0 5.0000 2.2361 TINYINT(1) UNSIGNED NOT NULL -test.t1.empty_string 0 0 4 0 0.0000 NULL CHAR(0) NOT NULL -test.t1.bool N Y 1 1 0 0 1.0000 NULL ENUM('N','Y') NOT NULL -test.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 -create table t2 select * from t1 procedure analyse(); -ERROR HY000: Incorrect usage of PROCEDURE and non-SELECT -drop table t1; -EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(); -ERROR HY000: Incorrect usage of PROCEDURE and subquery -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(); -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -test.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 -drop table t1; -create table t1 (df decimal(5,1)); -insert into t1 values(1.1); -insert into t1 values(2.2); -select * from t1 procedure analyse(); -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -test.t1.df 1.1 2.2 13 13 0 0 1.65000 0.55000 ENUM('1.1','2.2') NOT NULL -drop table t1; -create table t1 (d double); -insert into t1 values (100000); -select * from t1 procedure analyse (1,1); -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -test.t1.d 100000 100000 6 6 0 0 100000 0 MEDIUMINT(6) UNSIGNED NOT NULL -drop table t1; -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(); -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -test.t1.product Computer TV 2 8 0 0 4.2500 NULL ENUM('Computer','Phone','TV') NOT NULL -sum(profit) 10 6900 11 11 0 0 1946.2500 2867.6719 ENUM('10','275','600','6900') NOT NULL -avg(profit) 10.0000 1380.0000 16 16 0 0 394.68750000 570.20033144 ENUM('10.0000','68.7500','120.0000','1380.0000') NOT NULL -drop table t1,t2; -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); -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -test.t1.f1 5.99900 9.55500 7 7 0 0 7.77700 1.77800 FLOAT(4,3) NOT NULL -select f2 from t1 procedure analyse(1, 1); -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -test.t1.f2 5.9999 9.5555 6 6 0 0 6.0000 NULL FLOAT(5,4) UNSIGNED NOT NULL -select f3 from t1 procedure analyse(1, 1); -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -test.t1.f3 5.99999 9.55555 7 7 0 0 7.77777 1.77778 FLOAT(6,5) NOT NULL -drop table t1; -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 (); -SELECT * FROM (SELECT * FROM t1) d PROCEDURE ANALYSE(); -ERROR HY000: Incorrect usage of PROCEDURE and subquery -DROP TABLE t1; -End of 4.1 tests -# -# Bug #48293: crash with procedure analyse, view with > 10 columns, -# having clause... -# -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; -#should have a derived table -EXPLAIN SELECT * FROM v1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -2 DERIVED t1 ALL NULL NULL NULL NULL 2 -#should not crash -SELECT * FROM v1 PROCEDURE analyse(); -ERROR HY000: Incorrect usage of PROCEDURE and view -#should not crash -SELECT * FROM t1 a, v1, t1 b PROCEDURE analyse(); -ERROR HY000: Incorrect usage of PROCEDURE and view -#should not crash -SELECT * FROM (SELECT * FROM t1 having a > 1) x PROCEDURE analyse(); -ERROR HY000: Incorrect usage of PROCEDURE and subquery -#should not crash -SELECT * FROM t1 a, (SELECT * FROM t1 having a > 1) x, t1 b PROCEDURE analyse(); -ERROR HY000: Incorrect usage of PROCEDURE and subquery -#should not crash -SELECT 1 FROM t1 group by a having a > 1 order by 1 PROCEDURE analyse(); -ERROR HY000: Can't use ORDER clause with this procedure -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1(a INT); -INSERT INTO t1 VALUES (1),(2); -# should not crash -CREATE TABLE t2 SELECT 1 FROM t1, t1 t3 GROUP BY t3.a PROCEDURE ANALYSE(); -ERROR HY000: Incorrect usage of PROCEDURE and non-SELECT -DROP TABLE t1; -End of 5.0 tests diff --git a/mysql-test/r/analyze.result b/mysql-test/r/analyze.result new file mode 100644 index 00000000000..df8a6c42924 --- /dev/null +++ b/mysql-test/r/analyze.result @@ -0,0 +1,66 @@ +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 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 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 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 OK +analyze table t1; +Table Op Msg_type Msg_text +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 +t1 1 a 1 a A 5 NULL NULL YES BTREE +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 MySQL 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 MySQL server version for the right syntax to use near 'extended' at line 1 +drop table t1; +End of 5.0 tests diff --git a/mysql-test/r/func_analyse.result b/mysql-test/r/func_analyse.result index df8a6c42924..1820782d2f8 100644 --- a/mysql-test/r/func_analyse.result +++ b/mysql-test/r/func_analyse.result @@ -1,66 +1,125 @@ -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 OK -unlock tables; -check table t1; -Table Op Msg_type Msg_text -test.t1 check status OK +drop table if exists t1,t2; +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(); +Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype +count(*) 4 4 1 1 0 0 4.0000 0.0000 ENUM('4') NOT NULL +select * from t1 procedure analyse(); +Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype +test.t1.i 1 7 1 1 0 0 4.0000 2.2361 ENUM('1','3','5','7') NOT NULL +test.t1.j 2 8 1 1 0 0 5.0000 2.2361 ENUM('2','4','6','8') NOT NULL +test.t1.empty_string 0 0 4 0 0.0000 NULL CHAR(0) NOT NULL +test.t1.bool N Y 1 1 0 0 1.0000 NULL ENUM('N','Y') NOT NULL +test.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 +select * from t1 procedure analyse(2); +Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype +test.t1.i 1 7 1 1 0 0 4.0000 2.2361 TINYINT(1) UNSIGNED NOT NULL +test.t1.j 2 8 1 1 0 0 5.0000 2.2361 TINYINT(1) UNSIGNED NOT NULL +test.t1.empty_string 0 0 4 0 0.0000 NULL CHAR(0) NOT NULL +test.t1.bool N Y 1 1 0 0 1.0000 NULL ENUM('N','Y') NOT NULL +test.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 +create table t2 select * from t1 procedure analyse(); +ERROR HY000: Incorrect usage of PROCEDURE and non-SELECT 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 OK -unlock tables; -check table t1; -Table Op Msg_type Msg_text -test.t1 check status OK +EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(); +ERROR HY000: Incorrect usage of PROCEDURE and subquery +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(); +Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype +test.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 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 OK -check table t1; -Table Op Msg_type Msg_text -test.t1 check status OK +create table t1 (df decimal(5,1)); +insert into t1 values(1.1); +insert into t1 values(2.2); +select * from t1 procedure analyse(); +Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype +test.t1.df 1.1 2.2 13 13 0 0 1.65000 0.55000 ENUM('1.1','2.2') NOT NULL 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 OK -analyze table t1; -Table Op Msg_type Msg_text -test.t1 analyze status Table is already up to date +create table t1 (d double); +insert into t1 values (100000); +select * from t1 procedure analyse (1,1); +Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype +test.t1.d 100000 100000 6 6 0 0 100000 0 MEDIUMINT(6) UNSIGNED NOT NULL drop table t1; -CREATE TABLE t1 (a int); -prepare stmt1 from "SELECT * FROM t1 PROCEDURE ANALYSE()"; -execute stmt1; +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(); Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -execute stmt1; +test.t1.product Computer TV 2 8 0 0 4.2500 NULL ENUM('Computer','Phone','TV') NOT NULL +sum(profit) 10 6900 11 11 0 0 1946.2500 2867.6719 ENUM('10','275','600','6900') NOT NULL +avg(profit) 10.0000 1380.0000 16 16 0 0 394.68750000 570.20033144 ENUM('10.0000','68.7500','120.0000','1380.0000') NOT NULL +drop table t1,t2; +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); 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 -t1 1 a 1 a A 5 NULL NULL YES BTREE +test.t1.f1 5.99900 9.55500 7 7 0 0 7.77700 1.77800 FLOAT(4,3) NOT NULL +select f2 from t1 procedure analyse(1, 1); +Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype +test.t1.f2 5.9999 9.5555 6 6 0 0 6.0000 NULL FLOAT(5,4) UNSIGNED NOT NULL +select f3 from t1 procedure analyse(1, 1); +Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype +test.t1.f3 5.99999 9.55555 7 7 0 0 7.77777 1.77778 FLOAT(6,5) NOT NULL drop table t1; +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 (); +SELECT * FROM (SELECT * FROM t1) d PROCEDURE ANALYSE(); +ERROR HY000: Incorrect usage of PROCEDURE and subquery +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 MySQL 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 MySQL server version for the right syntax to use near 'extended' at line 1 -drop table t1; +# +# Bug #48293: crash with procedure analyse, view with > 10 columns, +# having clause... +# +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; +#should have a derived table +EXPLAIN SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 +#should not crash +SELECT * FROM v1 PROCEDURE analyse(); +ERROR HY000: Incorrect usage of PROCEDURE and view +#should not crash +SELECT * FROM t1 a, v1, t1 b PROCEDURE analyse(); +ERROR HY000: Incorrect usage of PROCEDURE and view +#should not crash +SELECT * FROM (SELECT * FROM t1 having a > 1) x PROCEDURE analyse(); +ERROR HY000: Incorrect usage of PROCEDURE and subquery +#should not crash +SELECT * FROM t1 a, (SELECT * FROM t1 having a > 1) x, t1 b PROCEDURE analyse(); +ERROR HY000: Incorrect usage of PROCEDURE and subquery +#should not crash +SELECT 1 FROM t1 group by a having a > 1 order by 1 PROCEDURE analyse(); +ERROR HY000: Can't use ORDER clause with this procedure +DROP VIEW v1; +DROP TABLE t1; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1),(2); +# should not crash +CREATE TABLE t2 SELECT 1 FROM t1, t1 t3 GROUP BY t3.a PROCEDURE ANALYSE(); +ERROR HY000: Incorrect usage of PROCEDURE and non-SELECT +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/t/analyse.test b/mysql-test/t/analyse.test deleted file mode 100644 index 05f739bfd69..00000000000 --- a/mysql-test/t/analyse.test +++ /dev/null @@ -1,135 +0,0 @@ -# -# 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 diff --git a/mysql-test/t/analyze.test b/mysql-test/t/analyze.test new file mode 100644 index 00000000000..0903db1eca4 --- /dev/null +++ b/mysql-test/t/analyze.test @@ -0,0 +1,87 @@ +# +# 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 +# + +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 diff --git a/mysql-test/t/func_analyse.test b/mysql-test/t/func_analyse.test index 0903db1eca4..05f739bfd69 100644 --- a/mysql-test/t/func_analyse.test +++ b/mysql-test/t/func_analyse.test @@ -1,87 +1,135 @@ # -# 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 +# Test of procedure analyse # -create table t1 (a bigint); -lock tables t1 write; -insert into t1 values(0); -analyze table t1; -unlock tables; -check table t1; - +--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; -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; +--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; -create table t1 (a bigint); -insert into t1 values(0); -analyze table t1; -check 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 #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; +# +# 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; # -# procedure in PS BUG#13673 +# Bug #14138 ROLLUP and PROCEDURE ANALYSE() hang server # -CREATE TABLE t1 (a int); -prepare stmt1 from "SELECT * FROM t1 PROCEDURE ANALYSE()"; -execute stmt1; -execute stmt1; -deallocate prepare stmt1; -drop table t1; +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#15225 (ANALYZE temporary has no effect) +# Bug #20305 PROCEDURE ANALYSE() returns wrong M for FLOAT(M, D) and DOUBLE(M, D) # -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 +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 #30495: optimize table t1,t2,t3 extended errors +# Bug#46184 Crash, SELECT ... FROM derived table procedure analyze # -create table t1(a int); ---error 1064 -analyze table t1 extended; ---error 1064 -optimize table t1 extended; -drop table t1; +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 |