#At file:///home/kgeorge/mysql/work/B48293-5.0-bugteam/ based on revid:joro@stripped
2828 Georgi Kodinov 2009-10-30
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.
modified:
mysql-test/r/analyse.result
mysql-test/r/subselect.result
mysql-test/t/analyse.test
mysql-test/t/subselect.test
sql/sql_select.cc
sql/sql_yacc.yy
=== modified file 'mysql-test/r/analyse.result'
--- a/mysql-test/r/analyse.result 2009-10-14 08:16:04 +0000
+++ b/mysql-test/r/analyse.result 2009-10-30 09:40:44 +0000
@@ -19,81 +19,10 @@ test.t1.empty_string 0 0 4 0 0.0000 NU
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 PROCE
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
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2009-10-14 08:16:04 +0000
+++ b/mysql-test/r/subselect.result 2009-10-30 09:40:44 +0000
@@ -75,7 +75,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1
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;
=== modified file 'mysql-test/t/analyse.test'
--- a/mysql-test/t/analyse.test 2009-10-14 08:16:04 +0000
+++ b/mysql-test/t/analyse.test 2009-10-30 09:40:44 +0000
@@ -10,37 +10,14 @@ insert into t1 values (1,2,"","Y","2002-
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 PROCE
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
=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test 2009-10-14 08:16:04 +0000
+++ b/mysql-test/t/subselect.test 2009-10-30 09:40:44 +0000
@@ -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;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-10-21 09:04:08 +0000
+++ b/sql/sql_select.cc 2009-10-30 09:40:44 +0000
@@ -622,6 +622,18 @@ JOIN::prepare(Item ***rref_pointer_array
MYF(0)); /* purecov: inspected */
goto err; /* purecov: inspected */
}
+ if (thd->lex->derived_tables)
+ {
+ my_error(ER_WRONG_USAGE, MYF(0), "PROCEDURE",
+ thd->lex->derived_tables & DERIVED_VIEW ?
+ "view" : "subquery");
+ goto err;
+ }
+ if (thd->lex->sql_command != SQLCOM_SELECT)
+ {
+ my_error(ER_WRONG_USAGE, MYF(0), "PROCEDURE", "non-SELECT");
+ goto err;
+ }
}
if (!procedure && result && result->prepare(fields_list, unit_arg))
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2009-10-14 08:16:04 +0000
+++ b/sql/sql_yacc.yy 2009-10-30 09:40:44 +0000
@@ -7334,8 +7334,7 @@ procedure_clause:
MYSQL_YYABORT;
}
- if (&lex->select_lex != lex->current_select ||
- lex->select_lex.get_table_list()->derived)
+ if (&lex->select_lex != lex->current_select)
{
my_error(ER_WRONG_USAGE, MYF(0), "PROCEDURE", "subquery");
MYSQL_YYABORT;
Attachment: [text/bzr-bundle] bzr/joro@sun.com-20091030094044-j5vvd5ji6ke7t349.bundle
| Thread |
|---|
| • bzr commit into mysql-5.0 branch (joro:2828) Bug#48293 | Georgi Kodinov | 30 Oct |
