From 851e250953e531c2c5189dff0d7202cb79acf5d6 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Fri, 30 Oct 2009 11:40:44 +0200 Subject: Bug #48293: crash with procedure analyse, view with > 10 columns, having clause... The fix for bug 46184 was not very complete. It was not covering views using temporary tables and multiple tables in a FROM clause. Fixed by reverting the fix for 46184 and making a more general check that is checking at the right execution stage and for all of the non-supported cases. Now PROCEDURE ANALYZE on non-top level SELECT is also forbidden. Updated the analyse.test and subselect.test accordingly. --- mysql-test/r/analyse.result | 112 +++++++++++++++--------------------------- mysql-test/r/subselect.result | 2 +- mysql-test/t/analyse.test | 70 ++++++++++++++++---------- mysql-test/t/subselect.test | 2 +- 4 files changed, 86 insertions(+), 100 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/analyse.result b/mysql-test/r/analyse.result index 1e3a2985f74..1820782d2f8 100644 --- a/mysql-test/r/analyse.result +++ b/mysql-test/r/analyse.result @@ -19,81 +19,10 @@ 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(); -select * from t2; -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 -drop table t1,t2; +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 (a int not null); -create table t2 select * from t1 where 0=1 procedure analyse(); -show create table t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `Field_name` varbinary(255) NOT NULL default '', - `Min_value` varbinary(255) default NULL, - `Max_value` varbinary(255) default NULL, - `Min_length` bigint(11) NOT NULL default '0', - `Max_length` bigint(11) NOT NULL default '0', - `Empties_or_zeros` bigint(11) NOT NULL default '0', - `Nulls` bigint(11) NOT NULL default '0', - `Avg_value_or_avg_length` varbinary(255) NOT NULL default '', - `Std` varbinary(255) default NULL, - `Optimal_fieldtype` varbinary(64) NOT NULL default '' -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -select * from t1 where 0=1 procedure analyse(); -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -insert into t1 values(1); -drop table t2; -create table t2 select * from t1 where 0=1 procedure analyse(); -show create table t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `Field_name` varbinary(255) NOT NULL default '', - `Min_value` varbinary(255) default NULL, - `Max_value` varbinary(255) default NULL, - `Min_length` bigint(11) NOT NULL default '0', - `Max_length` bigint(11) NOT NULL default '0', - `Empties_or_zeros` bigint(11) NOT NULL default '0', - `Nulls` bigint(11) NOT NULL default '0', - `Avg_value_or_avg_length` varbinary(255) NOT NULL default '', - `Std` varbinary(255) default NULL, - `Optimal_fieldtype` varbinary(64) NOT NULL default '' -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -select * from t2; -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -insert into t2 select * from t1 procedure analyse(); -select * from t2; -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -test.t1.a 1 1 1 1 0 0 1.0000 0.0000 ENUM('1') NOT NULL -insert into t1 values(2); -drop table t2; -create table t2 select * from t1 where 0=1 procedure analyse(); -show create table t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `Field_name` varbinary(255) NOT NULL default '', - `Min_value` varbinary(255) default NULL, - `Max_value` varbinary(255) default NULL, - `Min_length` bigint(11) NOT NULL default '0', - `Max_length` bigint(11) NOT NULL default '0', - `Empties_or_zeros` bigint(11) NOT NULL default '0', - `Nulls` bigint(11) NOT NULL default '0', - `Avg_value_or_avg_length` varbinary(255) NOT NULL default '', - `Std` varbinary(255) default NULL, - `Optimal_fieldtype` varbinary(64) NOT NULL default '' -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -select * from t2; -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -insert into t2 select * from t1 procedure analyse(); -select * from t2; -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -test.t1.a 1 2 1 1 0 0 1.5000 0.5000 ENUM('1','2') NOT NULL -drop table t1,t2; 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(); @@ -157,3 +86,40 @@ 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 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/subselect.result b/mysql-test/r/subselect.result index eacde19ab1c..8c239d5c349 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -75,7 +75,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); ERROR HY000: Incorrect usage of PROCEDURE and subquery SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -ERROR HY000: Incorrect usage of PROCEDURE and subquery +ERROR HY000: Incorrect parameters to procedure 'ANALYSE' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; diff --git a/mysql-test/t/analyse.test b/mysql-test/t/analyse.test index d8466df14bf..05f739bfd69 100644 --- a/mysql-test/t/analyse.test +++ b/mysql-test/t/analyse.test @@ -10,36 +10,13 @@ insert into t1 values (1,2,"","Y","2002-03-03"), (3,4,"","N","2002-03-04"), (5,6 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(); -select * from t2; -drop table t1,t2; +drop table t1; --error ER_WRONG_USAGE EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(); -# -# Test with impossible where -# -create table t1 (a int not null); -create table t2 select * from t1 where 0=1 procedure analyse(); -show create table t2; -select * from t1 where 0=1 procedure analyse(); -insert into t1 values(1); -drop table t2; -create table t2 select * from t1 where 0=1 procedure analyse(); -show create table t2; -select * from t2; -insert into t2 select * from t1 procedure analyse(); -select * from t2; -insert into t1 values(2); -drop table t2; -create table t2 select * from t1 where 0=1 procedure analyse(); -show create table t2; -select * from t2; -insert into t2 select * from t1 procedure analyse(); -select * from t2; -drop table t1,t2; - # # Bug#2813 - analyse does not quote string values in enums from string # @@ -113,3 +90,46 @@ 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/subselect.test b/mysql-test/t/subselect.test index 79918ca78a2..2b5d36da796 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -30,7 +30,7 @@ SELECT 1 IN (SELECT 1); SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); -- error ER_WRONG_USAGE select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); --- error ER_WRONG_USAGE +-- error ER_WRONG_PARAMETERS_TO_PROCEDURE SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -- error ER_BAD_FIELD_ERROR SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; -- cgit v1.2.1