maria-developers team mailing list archive

Thread
Date

Please review MDEV-10051 Fix subselect to return a syntax error instead of "Incorrect usage of UNION and LIMIT"

To: Sergei Golubchik <serg@xxxxxxxxxxx>, maria-developers <maria-developers@xxxxxxxxxxxxxxxxxxx>
From: Alexander Barkov <bar@xxxxxxxxxxx>
Date: Tue, 10 May 2016 18:01:46 +0400
User-agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.6.0

Hello Sergei,

Please review the next step for
MDEV-8909 union parser cleanup

Now it's MDEV-10051, and it fixes subselects.


Note, I decided to fix derived tables in a separate patch later.
So there will be one more patch blocking MDEV-8909.


Thanks!

diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result
index 1a39bca..889fadb 100644
--- a/mysql-test/r/parser.result
+++ b/mysql-test/r/parser.result
@@ -820,19 +820,19 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
 SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1;
 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 'UNION SELECT 1 FROM t1' at line 1
 SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1);
-ERROR HY000: Incorrect usage of UNION and ORDER BY
+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 'UNION SELECT 1 FROM t1)' at line 1
 SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1) a;
 ERROR HY000: Incorrect usage of UNION and ORDER BY
 SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1;
 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 'UNION SELECT 1 FROM t1' at line 1
 SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1);
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 'UNION SELECT 1 FROM t1)' at line 1
 SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1) a;
 ERROR HY000: Incorrect usage of UNION and LIMIT
 SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1;
 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 'UNION SELECT 1 FROM t1' at line 1
 SELECT (SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1);
-ERROR HY000: Incorrect usage of UNION and ORDER BY
+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 'UNION SELECT 1 FROM t1)' at line 1
 SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1) a;
 ERROR HY000: Incorrect usage of UNION and ORDER BY
 SELECT 1 FROM t1 LIMIT 1 ORDER BY 1 UNION SELECT 1 FROM t1;
@@ -844,25 +844,25 @@ ERROR HY000: Incorrect usage of UNION and ORDER BY
 SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 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 'UNION SELECT 1 FROM t1 ORDER BY 1' at line 1
 SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1);
-ERROR HY000: Incorrect usage of UNION and ORDER BY
+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 'UNION SELECT 1 FROM t1 ORDER BY 1)' at line 1
 SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1) a;
 ERROR HY000: Incorrect usage of UNION and ORDER BY
 SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 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 'UNION SELECT 1 FROM t1 LIMIT 1' at line 1
 SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1);
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 'UNION SELECT 1 FROM t1 LIMIT 1)' at line 1
 SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1) a;
 ERROR HY000: Incorrect usage of UNION and LIMIT
 SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 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 'UNION SELECT 1 FROM t1 ORDER BY 1' at line 1
 SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1);
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 'UNION SELECT 1 FROM t1 ORDER BY 1)' at line 1
 SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1) a;
 ERROR HY000: Incorrect usage of UNION and LIMIT
 SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 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 'UNION SELECT 1 FROM t1 LIMIT 1' at line 1
 SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1);
-ERROR HY000: Incorrect usage of UNION and ORDER BY
+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 'UNION SELECT 1 FROM t1 LIMIT 1)' at line 1
 SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1) a;
 ERROR HY000: Incorrect usage of UNION and ORDER BY
 DROP TABLE t1;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 1b11989..c083df2 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1056,7 +1056,7 @@ create table t1 (a float);
 select 10.5 IN (SELECT * from t1 LIMIT 1);
 ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 'UNION SELECT 1.5)' at line 1
 select 10.5 IN (SELECT * from t1 UNION SELECT 1.5 LIMIT 1);
 ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 drop table t1;
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 39d1f64..f176b7f 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -1060,7 +1060,7 @@ create table t1 (a float);
 select 10.5 IN (SELECT * from t1 LIMIT 1);
 ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 'UNION SELECT 1.5)' at line 1
 select 10.5 IN (SELECT * from t1 UNION SELECT 1.5 LIMIT 1);
 ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 drop table t1;
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 211d320..011b159 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -1063,7 +1063,7 @@ create table t1 (a float);
 select 10.5 IN (SELECT * from t1 LIMIT 1);
 ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 'UNION SELECT 1.5)' at line 1
 select 10.5 IN (SELECT * from t1 UNION SELECT 1.5 LIMIT 1);
 ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 drop table t1;
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 6ae9f8a..3ccdaa8 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -1059,7 +1059,7 @@ create table t1 (a float);
 select 10.5 IN (SELECT * from t1 LIMIT 1);
 ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 'UNION SELECT 1.5)' at line 1
 select 10.5 IN (SELECT * from t1 UNION SELECT 1.5 LIMIT 1);
 ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 drop table t1;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 9a3fcd1..9116f7e 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -1062,7 +1062,7 @@ create table t1 (a float);
 select 10.5 IN (SELECT * from t1 LIMIT 1);
 ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 'UNION SELECT 1.5)' at line 1
 select 10.5 IN (SELECT * from t1 UNION SELECT 1.5 LIMIT 1);
 ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 drop table t1;
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 10cf056..7287ef5 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -1059,7 +1059,7 @@ create table t1 (a float);
 select 10.5 IN (SELECT * from t1 LIMIT 1);
 ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 'UNION SELECT 1.5)' at line 1
 select 10.5 IN (SELECT * from t1 UNION SELECT 1.5 LIMIT 1);
 ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 drop table t1;
diff --git a/mysql-test/t/parser.test b/mysql-test/t/parser.test
index a143a61..c03bcb4 100644
--- a/mysql-test/t/parser.test
+++ b/mysql-test/t/parser.test
@@ -949,7 +949,7 @@ eval SELECT 1 FROM ($q) a;
 let $q=SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1;
 --error ER_PARSE_ERROR
 eval $q;
---error ER_WRONG_USAGE
+--error ER_PARSE_ERROR
 eval SELECT ($q);
 --error ER_WRONG_USAGE
 eval SELECT 1 FROM ($q) a;
@@ -957,7 +957,7 @@ eval SELECT 1 FROM ($q) a;
 let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1;
 --error ER_PARSE_ERROR
 eval $q;
---error ER_WRONG_USAGE
+--error ER_PARSE_ERROR
 eval SELECT ($q);
 --error ER_WRONG_USAGE
 eval SELECT 1 FROM ($q) a;
@@ -965,7 +965,7 @@ eval SELECT 1 FROM ($q) a;
 let $q=SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1;
 --error ER_PARSE_ERROR
 eval $q;
---error ER_WRONG_USAGE
+--error ER_PARSE_ERROR
 eval SELECT ($q);
 --error ER_WRONG_USAGE
 eval SELECT 1 FROM ($q) a;
@@ -981,7 +981,7 @@ eval SELECT 1 FROM ($q) a;
 let $q=SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1;
 --error ER_PARSE_ERROR
 eval $q;
---error ER_WRONG_USAGE
+--error ER_PARSE_ERROR
 eval SELECT ($q);
 --error ER_WRONG_USAGE
 eval SELECT 1 FROM ($q) a;
@@ -989,7 +989,7 @@ eval SELECT 1 FROM ($q) a;
 let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1;
 --error ER_PARSE_ERROR
 eval $q;
---error ER_WRONG_USAGE
+--error ER_PARSE_ERROR
 eval SELECT ($q);
 --error ER_WRONG_USAGE
 eval SELECT 1 FROM ($q) a;
@@ -997,7 +997,7 @@ eval SELECT 1 FROM ($q) a;
 let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1;
 --error ER_PARSE_ERROR
 eval $q;
---error ER_WRONG_USAGE
+--error ER_PARSE_ERROR
 eval SELECT ($q);
 --error ER_WRONG_USAGE
 eval SELECT 1 FROM ($q) a;
@@ -1005,7 +1005,7 @@ eval SELECT 1 FROM ($q) a;
 let $q=SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1;
 --error ER_PARSE_ERROR
 eval $q;
---error ER_WRONG_USAGE
+--error ER_PARSE_ERROR
 eval SELECT ($q);
 --error ER_WRONG_USAGE
 eval SELECT 1 FROM ($q) a;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 5313a4b..c19613d 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -569,7 +569,7 @@ drop table t1, t2;
 create table t1 (a float);
 -- error ER_NOT_SUPPORTED_YET
 select 10.5 IN (SELECT * from t1 LIMIT 1);
--- error ER_WRONG_USAGE
+-- error ER_PARSE_ERROR
 select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
 -- error ER_NOT_SUPPORTED_YET
 select 10.5 IN (SELECT * from t1 UNION SELECT 1.5 LIMIT 1);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 178d21d..4024e0e 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1918,9 +1918,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 
 %type <variable> internal_variable_name
 
-%type <select_lex> subselect
-        get_select_lex query_term
-        query_expression_body
+%type <select_lex> subselect query_expression get_select_lex
 
 %type <boolfunc2creator> comp_op
 
@@ -11115,14 +11113,7 @@ select_derived_union:
               MYSQL_YYABORT;
             }
           }
-        | select_derived_union
-          UNION_SYM
-          union_option
-          {
-            if (add_select_to_union_list(Lex, (bool)$3, FALSE))
-              MYSQL_YYABORT;
-          }
-          query_term
+        | select_derived_union union_head_non_top select_derived_union_term
           {
             /*
               Remove from the name resolution context stack the context of the
@@ -11138,6 +11129,23 @@ select_derived_union:
           }
         ;
 
+
+/*
+  select_derived_union_term is temporarily needed for select_derived_union,
+  and will be removed soon.
+
+  select_derived_union will reuse these rules instead:
+  - union_list_non_top, or
+  - query_expression_body
+*/
+select_derived_union_term:
+          query_specification opt_select_lock_type
+        | query_specification order_or_limit opt_select_lock_type
+        | '(' select_paren_derived ')'
+        | '(' select_paren_derived ')' union_order_or_limit
+        ;
+
+
 /* The equivalent of select_init2 for nested queries. */
 select_init2_derived:
           select_part2_derived
@@ -16358,43 +16366,46 @@ union_option:
         | ALL       { $$=0; }
         ;
 
-query_term:
+query_specification:
           SELECT_SYM select_init2_derived
           opt_table_expression
-          opt_order_clause
-          opt_limit_clause
-          opt_select_lock_type
-          {
-            $$= Lex->current_select->master_unit()->first_select();
-          }
-        | '(' select_paren_derived ')'
-          opt_union_order_or_limit
-          {
-            $$= Lex->current_select->master_unit()->first_select();
-          }
         ;
 
 query_expression_body:
-          query_term
-        | query_expression_body
+          query_specification opt_select_lock_type
+        | query_specification opt_select_lock_type union_list_non_top
+        | query_specification order_or_limit opt_select_lock_type
+        | '(' select_paren_derived ')'
+        | '(' select_paren_derived ')' union_order_or_limit
+        | '(' select_paren_derived ')' union_list_non_top
+        ;
+
+/*
+  Start a UNION, for non-top level query expressions.
+*/
+union_head_non_top:
           UNION_SYM union_option 
           {
-            if (add_select_to_union_list(Lex, (bool)$3, FALSE))
+            if (add_select_to_union_list(Lex, (bool)$2, FALSE))
               MYSQL_YYABORT;
           }
-          query_term
-          {
-            Lex->pop_context();
-            $$= $1;
-          }
+        ;
+
+union_list_non_top:
+          union_head_non_top query_expression_body { Lex->pop_context(); }
         ;
 
 /* Corresponds to <query expression> in the SQL:2003 standard. */
 subselect:
-          subselect_start opt_with_clause query_expression_body subselect_end
-          { 
-	    $3->set_with_clause($2);
-            $$= $3;
+          subselect_start query_expression subselect_end
+          { $$= $2; }
+        ;
+
+query_expression:
+          opt_with_clause query_expression_body
+          {
+            $$= Lex->current_select->master_unit()->first_select();
+            $$->set_with_clause($1);
           }
         ;

Follow ups

Re: Please review MDEV-10051 Fix subselect to return a syntax error instead of "Incorrect usage of UNION and LIMIT"
From: Sergei Golubchik, 2016-05-11