bk commit into 6.0 tree (mhansson:1.2609) BUG#35020

[mhansson]

Below is the list of changes that have just been committed into a local
6.0 repository of mhansson.  When mhansson does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-04-01 11:29:01+02:00, mhansson@riffraff.(none) +3 -0
  Bug#35020: illegal sql_cache select syntax
  
  The select option flags SQL_CACHE and SQL_NO_CACHE were available
  for use in subqueries, which they're not supposed to.
  
  Fixed by creating special parser rules for select options in
  subqueries.
  
  Added tests that SQL_CACHE / SQL_NO_CACHE may only be
  used once on the first top-level SELECT statement and not
  in presence of its opposite.

  mysql-test/r/query_cache.result@stripped, 2008-04-01 11:28:58+02:00,
mhansson@riffraff.(none) +51 -56
    Bug#35020: Changed test result.

  mysql-test/t/query_cache.test@stripped, 2008-04-01 11:28:59+02:00, mhansson@riffraff.(none)
+81 -28
    Bug#35020: Test case.
    
    Tests of forbidden syntax have been move to below this
    test case.
    Test for Bug#29053 has been commented out.

  sql/sql_yacc.yy@stripped, 2008-04-01 11:28:59+02:00, mhansson@riffraff.(none) +83 -33
    Bug#35020: Added new rule set to differentiate subquery options from 
    top-level query options:
    
    - opt_query_expression_options
    - query_expression_option_list
    - query_expression_option
    
    As an added benefit, we got rid of a shift/reduce conflict.

diff -Nrup a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
--- a/mysql-test/r/query_cache.result	2008-01-07 21:11:41 +01:00
+++ b/mysql-test/r/query_cache.result	2008-04-01 11:28:58 +02:00
@@ -176,21 +176,6 @@ a
 1
 2
 3
-select * from t1 union select sql_cache * from t1;
-a
-1
-2
-3
-select * from t1 where a IN (select sql_cache a from t1);
-a
-1
-2
-3
-select * from t1 where a IN (select a from t1 union select sql_cache a from t1);
-a
-1
-2
-3
 show status like "Qcache_hits";
 Variable_name	Value
 Qcache_hits	4
@@ -207,41 +192,6 @@ a
 1
 2
 3
-select * from t1 union select sql_no_cache * from t1;
-a
-1
-2
-3
-select * from t1 where a IN (select sql_no_cache a from t1);
-a
-1
-2
-3
-select * from t1 where a IN (select a from t1 union select sql_no_cache a from t1);
-a
-1
-2
-3
-select sql_cache sql_no_cache * from t1;
-a
-1
-2
-3
-select sql_cache  * from t1 union select sql_no_cache * from t1;
-a
-1
-2
-3
-select sql_cache * from t1 where a IN (select sql_no_cache a from t1);
-a
-1
-2
-3
-select sql_cache * from t1 where a IN (select a from t1 union select sql_no_cache a from
t1);
-a
-1
-2
-3
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
 Qcache_queries_in_cache	0
@@ -1490,12 +1440,6 @@ insert into t1 values ('c');
 a
 drop table t1;
 set GLOBAL query_cache_size= default;
-set GLOBAL query_cache_size=1000000;
-create table t1 (a char);
-insert into t1 values ('c');
-a
-drop table t1;
-set GLOBAL query_cache_size= default;
 SET GLOBAL query_cache_size=64*1024*1024;
 CREATE TABLE t1 (id INT);
 CREATE PROCEDURE proc29856(IN theUPC TEXT)
@@ -1670,3 +1614,54 @@ SELECT 1 FROM t1 GROUP BY
 1
 DROP TABLE t1;
 End of 5.1 tests
+CREATE TABLE t1( a INT );
+SET @v = ( SELECT SQL_CACHE    1 );
+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 '1 )' at line 1
+SET @v = ( SELECT SQL_NO_CACHE 1 );
+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 '1 )' at line 1
+SELECT a FROM t1 WHERE a IN ( SELECT SQL_CACHE    a FROM t1 );
+ERROR 42S22: Unknown column 'SQL_CACHE' in 'field list'
+SELECT a FROM t1 WHERE a IN ( SELECT SQL_NO_CACHE a FROM t1 );
+ERROR 42S22: Unknown column 'SQL_NO_CACHE' in 'field list'
+SELECT ( SELECT SQL_CACHE a FROM t1 );
+ERROR 42S22: Unknown column 'SQL_CACHE' in 'field list'
+SELECT ( SELECT SQL_NO_CACHE a FROM t1 );
+ERROR 42S22: Unknown column 'SQL_NO_CACHE' in 'field list'
+SELECT SQL_CACHE * FROM t1;
+a
+SELECT SQL_NO_CACHE * FROM t1;
+a
+SELECT * FROM t1 UNION SELECT SQL_CACHE * FROM t1;
+ERROR 42000: Incorrect usage/placement of 'SQL_CACHE'
+SELECT * FROM t1 UNION SELECT SQL_NO_CACHE * FROM t1;
+ERROR 42000: Incorrect usage/placement of 'SQL_NO_CACHE'
+SELECT * FROM t1 WHERE a IN (SELECT SQL_CACHE a FROM t1);
+ERROR 42S22: Unknown column 'SQL_CACHE' in 'field list'
+SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 UNION SELECT SQL_CACHE a FROM t1);
+ERROR 42S22: Unknown column 'SQL_CACHE' in 'field list'
+SELECT * FROM t1 UNION SELECT SQL_NO_CACHE * FROM t1;
+ERROR 42000: Incorrect usage/placement of 'SQL_NO_CACHE'
+SELECT * FROM t1 WHERE a IN (SELECT SQL_NO_CACHE a FROM t1);
+ERROR 42S22: Unknown column 'SQL_NO_CACHE' in 'field list'
+SELECT * FROM t1 WHERE a IN 
+(SELECT a FROM t1 UNION SELECT SQL_NO_CACHE a FROM t1);
+ERROR 42S22: Unknown column 'SQL_NO_CACHE' in 'field list'
+SELECT SQL_CACHE SQL_NO_CACHE * FROM t1;
+ERROR HY000: Incorrect usage of SQL_CACHE and SQL_NO_CACHE
+SELECT SQL_NO_CACHE SQL_CACHE * FROM t1;
+ERROR HY000: Incorrect usage of SQL_NO_CACHE and SQL_CACHE
+SELECT SQL_CACHE * FROM t1 UNION SELECT SQL_CACHE * FROM t1;
+ERROR 42000: Incorrect usage/placement of 'SQL_CACHE'
+SELECT SQL_CACHE * FROM t1 UNION SELECT SQL_NO_CACHE * FROM t1;
+ERROR 42000: Incorrect usage/placement of 'SQL_NO_CACHE'
+SELECT SQL_NO_CACHE * FROM t1 UNION SELECT SQL_CACHE * FROM t1;
+ERROR 42000: Incorrect usage/placement of 'SQL_CACHE'
+SELECT SQL_NO_CACHE * FROM t1 UNION SELECT SQL_NO_CACHE * FROM t1;
+ERROR 42000: Incorrect usage/placement of 'SQL_NO_CACHE'
+SELECT SQL_CACHE * FROM t1 WHERE a IN 
+(SELECT SQL_NO_CACHE a FROM t1);
+ERROR 42S22: Unknown column 'SQL_NO_CACHE' in 'field list'
+SELECT SQL_CACHE * FROM t1 WHERE a IN 
+(SELECT a FROM t1 UNION SELECT SQL_NO_CACHE a FROM t1);
+ERROR 42S22: Unknown column 'SQL_NO_CACHE' in 'field list'
+DROP TABLE t1;
diff -Nrup a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
--- a/mysql-test/t/query_cache.test	2008-01-07 21:11:41 +01:00
+++ b/mysql-test/t/query_cache.test	2008-04-01 11:28:59 +02:00
@@ -94,10 +94,6 @@ select sql_cache * from t1 union select 
 set query_cache_type=2;
 select sql_cache * from t1 union select * from t1;
 
-# all sql_cache statements, except for the first select, are ignored.
-select * from t1 union select sql_cache * from t1;
-select * from t1 where a IN (select sql_cache a from t1);
-select * from t1 where a IN (select a from t1 union select sql_cache a from t1);
 show status like "Qcache_hits";
 show status like "Qcache_queries_in_cache";
 set query_cache_type=on;
@@ -110,15 +106,6 @@ show status like "Qcache_queries_in_cach
 # SELECT SQL_NO_CACHE
 #
 select sql_no_cache * from t1;
-# sql_no_cache can occur in any nested select to turn on cacheing for the whole
-# expression and it will always override a sql_cache statement.
-select * from t1 union select sql_no_cache * from t1;
-select * from t1 where a IN (select sql_no_cache a from t1);
-select * from t1 where a IN (select a from t1 union select sql_no_cache a from t1);
-select sql_cache sql_no_cache * from t1;
-select sql_cache  * from t1 union select sql_no_cache * from t1;
-select sql_cache * from t1 where a IN (select sql_no_cache a from t1);
-select sql_cache * from t1 where a IN (select a from t1 union select sql_no_cache a from
t1);
 show status like "Qcache_queries_in_cache";
 drop table t1;
 #
@@ -1040,22 +1027,25 @@ set GLOBAL query_cache_size= default;
 # Bug #29053 SQL_CACHE in UNION causes non-deterministic functions to be cached
 #
 
-set GLOBAL query_cache_size=1000000;
-
-create table t1 (a char);
-insert into t1 values ('c');
-
-let $q1= `select RAND() from t1 union select sql_cache 1 from t1;`;
-let $q2= `select RAND() from t1 union select sql_cache 1 from t1;`; 
-
+# This syntax is no longer allowed, therefore the test case has been commented 
+# out.
+# See test for Bug#35020 below.
+#set GLOBAL query_cache_size=1000000;
+#
+#create table t1 (a char);
+#insert into t1 values ('c');
+#
+#let $q1= `select RAND() from t1 union select sql_cache 1 from t1;`;
+#let $q2= `select RAND() from t1 union select sql_cache 1 from t1;`; 
+#
 # disabling the logging of the query because the times are different each run.
---disable_query_log
-eval select a  from t1 where "$q1" = "$q2";
---enable_query_log
-
-drop table t1;
-
-set GLOBAL query_cache_size= default;
+#--disable_query_log
+#eval select a  from t1 where "$q1" = "$q2";
+#--enable_query_log
+#
+#drop table t1;
+#
+#set GLOBAL query_cache_size= default;
 
 #
 # Bug#29856: Insufficient buffer space led to a server crash.
@@ -1318,3 +1308,66 @@ DROP TABLE t1;
 
 --echo End of 5.1 tests
 
+#
+# Bug#35020: illegal sql_cache select syntax
+#
+CREATE TABLE t1( a INT );
+
+--error ER_PARSE_ERROR
+SET @v = ( SELECT SQL_CACHE    1 );
+--error ER_PARSE_ERROR
+SET @v = ( SELECT SQL_NO_CACHE 1 );
+
+#
+# Keywords 'SQL_CACHE' and 'SQL_NO_CACHE' are allowed as column names.
+# Hence the error messages are not intuitive.
+#
+--error ER_BAD_FIELD_ERROR 
+SELECT a FROM t1 WHERE a IN ( SELECT SQL_CACHE    a FROM t1 );
+--error ER_BAD_FIELD_ERROR 
+SELECT a FROM t1 WHERE a IN ( SELECT SQL_NO_CACHE a FROM t1 );
+--error ER_BAD_FIELD_ERROR
+SELECT ( SELECT SQL_CACHE a FROM t1 );
+--error ER_BAD_FIELD_ERROR
+SELECT ( SELECT SQL_NO_CACHE a FROM t1 );
+
+SELECT SQL_CACHE * FROM t1;
+SELECT SQL_NO_CACHE * FROM t1;
+
+# SQL_CACHE is only allowed once in first top-level select.
+--error ER_CANT_USE_OPTION_HERE
+SELECT * FROM t1 UNION SELECT SQL_CACHE * FROM t1;
+--error ER_CANT_USE_OPTION_HERE
+SELECT * FROM t1 UNION SELECT SQL_NO_CACHE * FROM t1;
+--error ER_BAD_FIELD_ERROR
+SELECT * FROM t1 WHERE a IN (SELECT SQL_CACHE a FROM t1);
+--error ER_BAD_FIELD_ERROR
+SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 UNION SELECT SQL_CACHE a FROM t1);
+
+--error ER_CANT_USE_OPTION_HERE
+SELECT * FROM t1 UNION SELECT SQL_NO_CACHE * FROM t1;
+--error ER_BAD_FIELD_ERROR
+SELECT * FROM t1 WHERE a IN (SELECT SQL_NO_CACHE a FROM t1);
+--error ER_BAD_FIELD_ERROR
+SELECT * FROM t1 WHERE a IN 
+  (SELECT a FROM t1 UNION SELECT SQL_NO_CACHE a FROM t1);
+--error ER_WRONG_USAGE
+SELECT SQL_CACHE SQL_NO_CACHE * FROM t1;
+--error ER_WRONG_USAGE
+SELECT SQL_NO_CACHE SQL_CACHE * FROM t1;
+--error ER_CANT_USE_OPTION_HERE
+SELECT SQL_CACHE * FROM t1 UNION SELECT SQL_CACHE * FROM t1;
+--error ER_CANT_USE_OPTION_HERE
+SELECT SQL_CACHE * FROM t1 UNION SELECT SQL_NO_CACHE * FROM t1;
+--error ER_CANT_USE_OPTION_HERE
+SELECT SQL_NO_CACHE * FROM t1 UNION SELECT SQL_CACHE * FROM t1;
+--error ER_CANT_USE_OPTION_HERE
+SELECT SQL_NO_CACHE * FROM t1 UNION SELECT SQL_NO_CACHE * FROM t1;
+--error ER_BAD_FIELD_ERROR
+SELECT SQL_CACHE * FROM t1 WHERE a IN 
+  (SELECT SQL_NO_CACHE a FROM t1);
+--error ER_BAD_FIELD_ERROR
+SELECT SQL_CACHE * FROM t1 WHERE a IN 
+  (SELECT a FROM t1 UNION SELECT SQL_NO_CACHE a FROM t1);
+
+DROP TABLE t1;
diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy
--- a/sql/sql_yacc.yy	2008-03-06 09:20:50 +01:00
+++ b/sql/sql_yacc.yy	2008-04-01 11:28:59 +02:00
@@ -589,10 +589,10 @@ bool my_yyoverflow(short **a, YYSTYPE **
 
 %pure_parser                                    /* We have threads */
 /*
-  Currently there are 174 shift/reduce conflicts.
+  Currently there are 173 shift/reduce conflicts.
   We should not introduce new conflicts any more.
 */
-%expect 174
+%expect 173
 
 /*
    Comments for TOKENS.
@@ -6578,49 +6578,63 @@ select_option_list:
         ;
 
 select_option:
-          STRAIGHT_JOIN { Select->options|= SELECT_STRAIGHT_JOIN; }
-        | HIGH_PRIORITY
+          query_expression_option
+        | SQL_NO_CACHE_SYM
           {
-            if (check_simple_select())
+            /* 
+              Allow this flag only on the first top-level SELECT statement, if
+              SQL_CACHE wasn't specified, and only once per query.
+             */
+            if (Lex->current_select != &Lex->select_lex)
+            {
+              my_error(ER_CANT_USE_OPTION_HERE, MYF(0), "SQL_NO_CACHE");
               MYSQL_YYABORT;
-            Lex->lock_option= TL_READ_HIGH_PRIORITY;
-          }
-        | DISTINCT         { Select->options|= SELECT_DISTINCT; }
-        | SQL_SMALL_RESULT { Select->options|= SELECT_SMALL_RESULT; }
-        | SQL_BIG_RESULT   { Select->options|= SELECT_BIG_RESULT; }
-        | SQL_BUFFER_RESULT
-          {
-            if (check_simple_select())
+            }
+            else if (Lex->select_lex.sql_cache == SELECT_LEX::SQL_CACHE)
+            {
+              my_error(ER_WRONG_USAGE, MYF(0), "SQL_CACHE", "SQL_NO_CACHE");
               MYSQL_YYABORT;
-            Select->options|= OPTION_BUFFER_RESULT;
-          }
-        | SQL_CALC_FOUND_ROWS
-          {
-            if (check_simple_select())
+            }
+            else if (Lex->select_lex.sql_cache == SELECT_LEX::SQL_NO_CACHE)
+            {
+              my_error(ER_DUP_ARGUMENT, MYF(0), "SQL_NO_CACHE");
               MYSQL_YYABORT;
-            Select->options|= OPTION_FOUND_ROWS;
-          }
-        | SQL_NO_CACHE_SYM
-          {
-            Lex->safe_to_cache_query=0;
-            Lex->select_lex.options&= ~OPTION_TO_QUERY_CACHE;
-            Lex->select_lex.sql_cache= SELECT_LEX::SQL_NO_CACHE;
+            }
+            else
+            {
+              Lex->safe_to_cache_query=0;
+              Lex->select_lex.options&= ~OPTION_TO_QUERY_CACHE;
+              Lex->select_lex.sql_cache= SELECT_LEX::SQL_NO_CACHE;
+            }
           }
         | SQL_CACHE_SYM
           {
-            /*
-             Honor this flag only if SQL_NO_CACHE wasn't specified AND
-             we are parsing the outermost SELECT in the query.
-            */
-            if (Lex->select_lex.sql_cache != SELECT_LEX::SQL_NO_CACHE &&
-                Lex->current_select == &Lex->select_lex)
+            /* 
+              Allow this flag only on the first top-level SELECT statement, if
+              SQL_NO_CACHE wasn't specified, and only once per query.
+             */
+            if (Lex->current_select != &Lex->select_lex)
+            {
+              my_error(ER_CANT_USE_OPTION_HERE, MYF(0), "SQL_CACHE");
+              MYSQL_YYABORT;
+            }         
+            else if (Lex->select_lex.sql_cache == SELECT_LEX::SQL_NO_CACHE)
+            {
+              my_error(ER_WRONG_USAGE, MYF(0), "SQL_NO_CACHE", "SQL_CACHE");
+              MYSQL_YYABORT;
+            }
+            else if (Lex->select_lex.sql_cache == SELECT_LEX::SQL_CACHE)
+            {
+              my_error(ER_DUP_ARGUMENT, MYF(0), "SQL_CACHE");
+              MYSQL_YYABORT;
+            }
+            else
             {
               Lex->safe_to_cache_query=1;
               Lex->select_lex.options|= OPTION_TO_QUERY_CACHE;
               Lex->select_lex.sql_cache= SELECT_LEX::SQL_CACHE;
             }
           }
-        | ALL { Select->options|= SELECT_ALL; }
         ;
 
 select_lock_type:
@@ -8124,7 +8138,7 @@ select_part2_derived:
               mysql_init_select(lex);
             lex->current_select->parsing_place= SELECT_LIST;
           }
-          select_options select_item_list
+          opt_query_expression_options select_item_list
           {
             Select->parsing_place= NO_MATTER;
           }
@@ -12257,6 +12271,42 @@ subselect_end:
             lex->current_select->select_n_where_fields+=
             child->select_n_where_fields;
           }
+        ;
+
+opt_query_expression_options:
+          /* empty */
+        | query_expression_option_list
+        ;
+
+query_expression_option_list:
+          query_expression_option_list query_expression_option
+        | query_expression_option
+        ;
+
+query_expression_option:
+          STRAIGHT_JOIN { Select->options|= SELECT_STRAIGHT_JOIN; }
+        | HIGH_PRIORITY
+          {
+            if (check_simple_select())
+              MYSQL_YYABORT;
+            Lex->lock_option= TL_READ_HIGH_PRIORITY;
+          }
+        | DISTINCT         { Select->options|= SELECT_DISTINCT; }
+        | SQL_SMALL_RESULT { Select->options|= SELECT_SMALL_RESULT; }
+        | SQL_BIG_RESULT   { Select->options|= SELECT_BIG_RESULT; }
+        | SQL_BUFFER_RESULT
+          {
+            if (check_simple_select())
+              MYSQL_YYABORT;
+            Select->options|= OPTION_BUFFER_RESULT;
+          }
+        | SQL_CALC_FOUND_ROWS
+          {
+            if (check_simple_select())
+              MYSQL_YYABORT;
+            Select->options|= OPTION_FOUND_ROWS;
+          }
+        | ALL { Select->options|= SELECT_ALL; }
         ;
 
 /**************************************************************************