maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

Hi Sergei,

Please review a patch for MDEV-10030, which is a part of
MDEV-8909 union parser cleanup.

Thanks!

commit 0f4f2e3b5ebabce1308c45772785aec084092674
Author: Alexander Barkov <bar@xxxxxxxxxxx>
Date:   Thu May 5 12:29:36 2016 +0400

    MDEV-10030 sql_yacc.yy: Split table_expression and remove PROCEDURE from create_select, select_paren_derived, select_derived2, query_specification
    
    This change refactors the "table_expression" rule in sql_yacc.yy.
    
    Queries with subselects and derived tables, as well as "CREATE TABLE ... SELECT"
    now return syntax error instead of "Incorrect usage of PROCEDURE and ...".

diff --git a/mysql-test/r/func_analyse.result b/mysql-test/r/func_analyse.result
index 2c30055..bc8ec44 100644
--- a/mysql-test/r/func_analyse.result
+++ b/mysql-test/r/func_analyse.result
@@ -19,7 +19,7 @@ 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
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'procedure analyse()' at line 1
 drop table t1;
 EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE();
 ERROR HY000: Incorrect usage of PROCEDURE and subquery
@@ -120,7 +120,7 @@ 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
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE()' at line 1
 DROP TABLE t1;
 End of 5.0 tests
 #
@@ -149,3 +149,25 @@ Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_
 test.t2.f2	1	1	1	1	0	0	1.0000	0.0000	ENUM('1') NOT NULL
 DROP TABLE t1, t2;
 End of 5.1 tests
+#
+# Start of 10.2 tests
+#
+(SELECT 1 FROM DUAL PROCEDURE ANALYSE());
+Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
+1	1	1	1	1	0	0	1.0000	0.0000	ENUM('1') NOT NULL
+((SELECT 1 FROM DUAL PROCEDURE ANALYSE()));
+Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
+1	1	1	1	1	0	0	1.0000	0.0000	ENUM('1') NOT NULL
+SELECT * FROM t1 UNION SELECT * FROM t1 PROCEDURE analyse();
+ERROR HY000: Incorrect usage of PROCEDURE and subquery
+#
+# MDEV-10030 sql_yacc.yy: Split table_expression and remove PROCEDURE from create_select, select_paren_derived, select_derived2, query_specification
+#
+SELECT * FROM (SELECT * FROM t1 PROCEDURE ANALYSE());
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE())' at line 1
+SELECT * FROM t1 NATURAL JOIN (SELECT * FROM t2 PROCEDURE ANALYSE());
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE())' at line 1
+SELECT (SELECT 1 FROM t1 PROCEDURE ANALYSE()) FROM t2;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE()) FROM t2' at line 1
+SELECT ((SELECT 1 FROM t1 PROCEDURE ANALYSE())) FROM t2;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE())) FROM t2' at line 1
diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result
index 01cc9d7..18a8e13 100644
--- a/mysql-test/r/parser.result
+++ b/mysql-test/r/parser.result
@@ -686,7 +686,7 @@ FOR UPDATE) a;
 SELECT 1 FROM
 (SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
 PROCEDURE ANALYSE() FOR UPDATE) a;
-ERROR HY000: Incorrect usage of PROCEDURE and subquery
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE() FOR UPDATE) a' at line 3
 SELECT 1 FROM t1
 WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
 FOR UPDATE);
@@ -694,7 +694,7 @@ FOR UPDATE);
 SELECT 1 FROM t1
 WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
 PROCEDURE ANALYSE() FOR UPDATE);
-ERROR HY000: Incorrect usage of PROCEDURE and subquery
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE() FOR UPDATE)' at line 3
 SELECT 1 FROM t1
 UNION
 SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index b306965..1b11989 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -79,7 +79,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
 1
 1
 select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
-ERROR HY000: Incorrect usage of PROCEDURE and subquery
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE(1))' at line 1
 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB 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;
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 244f605..39d1f64 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -83,7 +83,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
 1
 1
 select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
-ERROR HY000: Incorrect usage of PROCEDURE and subquery
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE(1))' at line 1
 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB 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;
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index e095ed1..211d320 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -86,7 +86,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
 1
 1
 select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
-ERROR HY000: Incorrect usage of PROCEDURE and subquery
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE(1))' at line 1
 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB 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;
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index f471452..6ae9f8a 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -82,7 +82,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
 1
 1
 select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
-ERROR HY000: Incorrect usage of PROCEDURE and subquery
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE(1))' at line 1
 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB 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;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 5ead5de..9a3fcd1 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -85,7 +85,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
 1
 1
 select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
-ERROR HY000: Incorrect usage of PROCEDURE and subquery
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE(1))' at line 1
 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB 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;
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index c57c46b..10cf056 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -82,7 +82,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
 1
 1
 select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
-ERROR HY000: Incorrect usage of PROCEDURE and subquery
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE(1))' at line 1
 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB 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;
diff --git a/mysql-test/t/func_analyse.test b/mysql-test/t/func_analyse.test
index c77967a..6c30c0c 100644
--- a/mysql-test/t/func_analyse.test
+++ b/mysql-test/t/func_analyse.test
@@ -11,7 +11,7 @@ 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
+--error ER_PARSE_ERROR
 create table t2 select * from t1 procedure analyse();
 drop table t1;
 
@@ -127,7 +127,7 @@ CREATE TABLE t1(a INT);
 INSERT INTO t1 VALUES (1),(2);
 
 --echo # should not crash
---error ER_WRONG_USAGE
+--error ER_PARSE_ERROR
 CREATE TABLE t2 SELECT 1 FROM t1, t1 t3 GROUP BY t3.a PROCEDURE ANALYSE();
 
 DROP TABLE t1;
@@ -157,3 +157,27 @@ SELECT * FROM t2 LIMIT 1 PROCEDURE ANALYSE();
 DROP TABLE t1, t2;
 
 --echo End of 5.1 tests
+
+--echo #
+--echo # Start of 10.2 tests
+--echo #
+(SELECT 1 FROM DUAL PROCEDURE ANALYSE());
+((SELECT 1 FROM DUAL PROCEDURE ANALYSE()));
+
+# TODO:
+--error ER_WRONG_USAGE
+SELECT * FROM t1 UNION SELECT * FROM t1 PROCEDURE analyse();
+
+--echo #
+--echo # MDEV-10030 sql_yacc.yy: Split table_expression and remove PROCEDURE from create_select, select_paren_derived, select_derived2, query_specification
+--echo #
+
+--error ER_PARSE_ERROR
+SELECT * FROM (SELECT * FROM t1 PROCEDURE ANALYSE());
+--ERROR ER_PARSE_ERROR
+SELECT * FROM t1 NATURAL JOIN (SELECT * FROM t2 PROCEDURE ANALYSE());
+
+--error ER_PARSE_ERROR
+SELECT (SELECT 1 FROM t1 PROCEDURE ANALYSE()) FROM t2;
+--error ER_PARSE_ERROR
+SELECT ((SELECT 1 FROM t1 PROCEDURE ANALYSE())) FROM t2;
diff --git a/mysql-test/t/parser.test b/mysql-test/t/parser.test
index 0a19b03..86cc3c4 100644
--- a/mysql-test/t/parser.test
+++ b/mysql-test/t/parser.test
@@ -806,7 +806,7 @@ SELECT 1 FROM
   (SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
    FOR UPDATE) a;
 
---error ER_WRONG_USAGE
+--error ER_PARSE_ERROR
 SELECT 1 FROM
   (SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
    PROCEDURE ANALYSE() FOR UPDATE) a;
@@ -815,7 +815,7 @@ SELECT 1 FROM t1
   WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
                FOR UPDATE);
 
---error ER_WRONG_USAGE
+--error ER_PARSE_ERROR
 SELECT 1 FROM t1
   WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
                PROCEDURE ANALYSE() FOR UPDATE);
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 7a7c01e..5313a4b 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -51,7 +51,7 @@ SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c O
 SELECT * FROM (SELECT 1) a  WHERE 1 IN (SELECT 1,1);
 SELECT 1 IN (SELECT 1);
 SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
--- error ER_WRONG_USAGE
+-- error ER_PARSE_ERROR
 select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
 -- error ER_PARSE_ERROR
 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index a6bbfc8..0d8bfb3 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -988,6 +988,7 @@ JOIN::prepare(TABLE_LIST *tables_init,
     }
     if (thd->lex->derived_tables)
     {
+      // SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE()
       my_error(ER_WRONG_USAGE, MYF(0), "PROCEDURE", 
                thd->lex->derived_tables & DERIVED_VIEW ?
                "view" : "subquery"); 
@@ -995,6 +996,7 @@ JOIN::prepare(TABLE_LIST *tables_init,
     }
     if (thd->lex->sql_command != SQLCOM_SELECT)
     {
+      // EXPLAIN SELECT * FROM t1 PROCEDURE ANALYSE()
       my_error(ER_WRONG_USAGE, MYF(0), "PROCEDURE", "non-SELECT");
       goto err;
     }
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 5e1f5dc..445f986 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1037,10 +1037,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 %parse-param { THD *thd }
 %lex-param { THD *thd }
 /*
-  Currently there are 124 shift/reduce conflicts.
+  Currently there are 123 shift/reduce conflicts.
   We should not introduce new conflicts any more.
 */
-%expect 124
+%expect 123
 
 /*
    Comments for TOKENS.
@@ -5695,7 +5695,10 @@ create_select:
           {
             Select->parsing_place= NO_MATTER;
           }
-          table_expression
+          opt_table_expression
+          opt_order_clause
+          opt_limit_clause
+          opt_select_lock_type
           {
             /*
               The following work only with the local list, the global list
@@ -8535,7 +8538,10 @@ select_paren_derived:
             Lex->current_select->set_braces(true);
           }
           SELECT_SYM select_part2_derived
-          table_expression
+          opt_table_expression
+          opt_order_clause
+          opt_limit_clause
+          opt_select_lock_type
           {
             if (setup_select_in_parentheses(Lex))
               MYSQL_YYABORT;
@@ -8566,24 +8572,20 @@ select_part2:
         | select_options_and_item_list into opt_select_lock_type
         | select_options_and_item_list
           opt_into
-          from_clause
-          opt_where_clause
-          opt_group_clause
-          opt_having_clause
-          opt_window_clause
+          table_expression
           opt_order_clause
           opt_limit_clause
           opt_procedure_clause
           opt_into
           opt_select_lock_type
           {
-            if ($2 && $11)
+            if ($2 && $7)
             {
               /* double "INTO" clause */
               my_error(ER_WRONG_USAGE, MYF(0), "INTO", "INTO");
               MYSQL_YYABORT;
             }
-            if ($10 && ($2 || $11))
+            if ($6 && ($2 || $7))
             {
               /* "INTO" with "PROCEDURE ANALYSE" */
               my_error(ER_WRONG_USAGE, MYF(0), "PROCEDURE", "INTO");
@@ -8606,25 +8608,25 @@ select_options_and_item_list:
           }
         ;
 
+
+/**
+  <table expression>, as in the SQL standard.
+*/
 table_expression:
-          opt_from_clause
+          from_clause
           opt_where_clause
           opt_group_clause
           opt_having_clause
           opt_window_clause
-          opt_order_clause
-          opt_limit_clause
-          opt_procedure_clause
-          opt_select_lock_type
         ;
 
-from_clause:
-          FROM table_reference_list
+opt_table_expression:
+            /* Empty */
+          | table_expression
         ;
 
-opt_from_clause:
-          /* empty */
-        | from_clause
+from_clause:
+          FROM table_reference_list
         ;
 
 table_reference_list:
@@ -11208,7 +11210,10 @@ select_derived2:
           {
             Select->parsing_place= NO_MATTER;
           }
-          table_expression
+          opt_table_expression
+          opt_order_clause
+          opt_limit_clause
+          opt_select_lock_type
         ;
 
 get_select_lex:
@@ -11946,9 +11951,11 @@ opt_procedure_clause:
 
             if (&lex->select_lex != lex->current_select)
             {
+              // SELECT * FROM t1 UNION SELECT * FROM t2 PROCEDURE ANALYSE();
               my_error(ER_WRONG_USAGE, MYF(0), "PROCEDURE", "subquery");
               MYSQL_YYABORT;
             }
+
             lex->proc_list.elements=0;
             lex->proc_list.first=0;
             lex->proc_list.next= &lex->proc_list.first;
@@ -16359,7 +16366,10 @@ union_option:
 
 query_specification:
           SELECT_SYM select_init2_derived
-          table_expression
+          opt_table_expression
+          opt_order_clause
+          opt_limit_clause
+          opt_select_lock_type
           {
             $$= Lex->current_select->master_unit()->first_select();
           }