From 557a57f3a23c486fbe12b66306ab7adffd609677 Mon Sep 17 00:00:00 2001 From: Mithun C Y Date: Mon, 17 Aug 2015 15:23:47 +0530 Subject: Bug #21350175: SUBQUERIES IN PROCEDURE CLAUSE OF SELECT STATEMENT CAUSES SERVER FAILURES. Analysis : ========== During JOIN::prepare of sub-query which creates the derived tables we call setup_procedure. Here we call fix_fields for parameters of procedure clause. Calling setup_procedure at this point may cause issue. If sub-query is one of parameter being fixed it might lead to complicated dependencies on derived tables being prepared. SOLUTION : ========== In 5.6 with WL#6242, we have made procedure clause parameters can only be NUM, so sub-queries are not allowed as parameters. So in 5.5 we can block sub-queries in procedure clause parameters. This eliminates above conflicting dependencies. --- mysql-test/r/subselect.result | 2 +- mysql-test/t/subselect.test | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 49cf73677b1..aec8e07e071 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 parameters to procedure 'ANALYSE' +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 'SELECT 1))' at line 1 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/subselect.test b/mysql-test/t/subselect.test index aec0db59843..a5a4b2b5c32 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_PARAMETERS_TO_PROCEDURE +-- error ER_PARSE_ERROR 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