MySQL Bugs: #8733: server accepts malformed query (multiply mentioned distinct)

Bug #8733	server accepts malformed query (multiply mentioned distinct)
Submitted:	23 Feb 2005 15:05	Modified:	2 Jul 2005 9:02
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Any (*)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
see the list of queries below, the server accepts all of them as valid and executes them.
according to the doc:
http://dev.mysql.com/doc/mysql/en/select.html

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]

this element is a one of a list, apears onece or not element. you should have none, or just one of them.

similiar things aply to HIGH_PRIORITY STRAIGHT_JOIN SQL_CACHE .....

the amount of repetition is not limited (except maybe for the maximum size of a query)

How to repeat:
select distinct distinct a from (select 1 a union select 2) x;
select distinctrow distinct a from (select 1 a union select 2) x;
select all all a from (select 1 a union select 2) x;
# next one is impossible
select all distinct  a from (select 1 a union select 2) x;
select high_priority high_priority  a from (select 1 a union select 2) x;

Suggested fix:
-

Option 1, Amend parser to conform to syntax as specified in the manual:

===== sql/sql_yacc.yy 1.379 vs edited =====
--- 1.379/sql/sql_yacc.yy	2005-02-18 13:19:04 +00:00
+++ edited/sql/sql_yacc.yy	2005-02-24 18:41:21 +00:00
@@ -2467,42 +2467,71 @@
 	;
 
 select_options:
-	/* empty*/
-	| select_option_list;
+	select_option_distinct
+        select_option_priority
+        select_option_straight
+        select_option_small
+        select_option_big
+        select_option_buffer
+        select_option_cache
+        select_option_calc;
 
-select_option_list:
-	select_option_list select_option
-	| select_option;
-
-select_option:
-	STRAIGHT_JOIN { Select->options|= SELECT_STRAIGHT_JOIN; }
+select_option_distinct:
+	/* Empty */	{}
+	| ALL		{}
+	| DISTINCT	{ Select->options|= SELECT_DISTINCT; }
+        ;
+select_option_priority:
+	/* Empty */	{}
 	| HIGH_PRIORITY
 	  {
 	    if (check_simple_select())
 	      YYABORT;
 	    Lex->lock_option= TL_READ_HIGH_PRIORITY;
 	  }
-	| DISTINCT	{ Select->options|= SELECT_DISTINCT; }
+	;
+
+select_option_straight:
+	/* Empty */	{}
+	| STRAIGHT_JOIN { Select->options|= SELECT_STRAIGHT_JOIN; }
+        ;
+
+select_option_small:
+	/* Empty */	{}
 	| SQL_SMALL_RESULT { Select->options|= SELECT_SMALL_RESULT; }
+        ;
+select_option_big:
+	/* Empty */	{}
 	| SQL_BIG_RESULT { Select->options|= SELECT_BIG_RESULT; }
+        ;
+
+select_option_buffer:
+	/* Empty */	{}
 	| SQL_BUFFER_RESULT
 	  {
 	    if (check_simple_select())
 	      YYABORT;
 	    Select->options|= OPTION_BUFFER_RESULT;
 	  }
+	;
+
+select_option_calc:
+	/* Empty */	{}
 	| SQL_CALC_FOUND_ROWS
 	  {
 	    if (check_simple_select())
 	      YYABORT;
 	    Select->options|= OPTION_FOUND_ROWS;
 	  }
+        ;
+
+select_option_cache:
+	/* Empty */	{}
 	| SQL_NO_CACHE_SYM { Lex->safe_to_cache_query=0; }
 	| SQL_CACHE_SYM
 	  {
 	    Lex->select_lex.options|= OPTION_TO_QUERY_CACHE;
 	  }
-	| ALL		{}
 	;
 
 select_lock_type:

Option #2, provide guards on actions - so parser is not sensitive to order of options:

===== sql/sql_yacc.yy 1.379 vs edited =====
--- 1.379/sql/sql_yacc.yy	2005-02-18 13:19:04 +00:00
+++ edited/sql/sql_yacc.yy	2005-02-24 19:24:05 +00:00
@@ -619,7 +619,7 @@
         table_option opt_if_not_exists opt_no_write_to_binlog opt_var_type
         opt_var_ident_type delete_option opt_temporary all_or_any opt_distinct
         opt_ignore_leaves fulltext_options spatial_type union_option
-        start_transaction_opts
+        start_transaction_opts select_option_list select_option
 
 %type <ulong_num>
 	ULONG_NUM raid_types merge_insert_types
@@ -2468,41 +2468,68 @@
 
 select_options:
 	/* empty*/
-	| select_option_list;
+	| select_option_list
+        {
+          uint val= $1 & 0x0000ffff;
+          if (0x00000001 & val)
+            Select->options|= SELECT_STRAIGHT_JOIN;
+          if (0x00000002 & val)
+            Lex->lock_option= TL_READ_HIGH_PRIORITY;
+          if (0x00000004 & val)
+            Select->options|= SELECT_DISTINCT;
+          if (0x00000008 & val)
+            Select->options|= SELECT_SMALL_RESULT;
+          if (0x00000010 & val)
+            Select->options|= SELECT_BIG_RESULT;
+          if (0x00000020 & val)
+            Select->options|= OPTION_BUFFER_RESULT;
+          if (0x00000040 & val)
+	    Select->options|= OPTION_FOUND_ROWS;
+          if (0x00000080 & val)
+            Lex->safe_to_cache_query=0;
+          if (0x00000100 & val)
+            Lex->select_lex.options|= OPTION_TO_QUERY_CACHE;
+        }
+        ;
 
 select_option_list:
 	select_option_list select_option
+        {
+          if (($1 & $2) & 0x7fff0000)
+          {
+            yyerror(ER(ER_SYNTAX_ERROR));
+            YYABORT;
+          }
+          $$= $1 | $2;
+        }
 	| select_option;
 
 select_option:
-	STRAIGHT_JOIN { Select->options|= SELECT_STRAIGHT_JOIN; }
+	STRAIGHT_JOIN { $$= 0x00010001; }
 	| HIGH_PRIORITY
 	  {
 	    if (check_simple_select())
 	      YYABORT;
-	    Lex->lock_option= TL_READ_HIGH_PRIORITY;
+            $$= 0x00020002;
 	  }
-	| DISTINCT	{ Select->options|= SELECT_DISTINCT; }
-	| SQL_SMALL_RESULT { Select->options|= SELECT_SMALL_RESULT; }
-	| SQL_BIG_RESULT { Select->options|= SELECT_BIG_RESULT; }
+	| DISTINCT	{ $$= 0x00040004; }
+	| SQL_SMALL_RESULT { $$= 0x00080008; }
+	| SQL_BIG_RESULT { $$= 0x00080010; }
 	| SQL_BUFFER_RESULT
 	  {
 	    if (check_simple_select())
 	      YYABORT;
-	    Select->options|= OPTION_BUFFER_RESULT;
+            $$= 0x00100020;
 	  }
 	| SQL_CALC_FOUND_ROWS
 	  {
 	    if (check_simple_select())
 	      YYABORT;
-	    Select->options|= OPTION_FOUND_ROWS;
-	  }
-	| SQL_NO_CACHE_SYM { Lex->safe_to_cache_query=0; }
-	| SQL_CACHE_SYM
-	  {
-	    Lex->select_lex.options|= OPTION_TO_QUERY_CACHE;
+            $$= 0x00200040;
 	  }
-	| ALL		{}
+	| SQL_NO_CACHE_SYM { $$= 0x00400080; }
+	| SQL_CACHE_SYM { $$= 0x00400100; }
+	| ALL		{ $$= 0x00040000; }
 	;
 
 select_lock_type:

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24810