bzr push into mysql-6.0-bugteam branch (kristofer.pettersson:3107)

[Kristofer Pettersson]

 3107 Kristofer Pettersson	2009-03-06 [merge]
      Automerge

    added:
      mysql-test/suite/rpl/r/rpl_mixed_bit_pk.result
      mysql-test/suite/rpl/t/rpl_mixed_bit_pk.test
    modified:
      mysql-test/r/variables.result
      mysql-test/suite/binlog/r/binlog_unsafe.result
      mysql-test/suite/binlog/t/binlog_unsafe.test
      mysql-test/suite/funcs_1/r/is_columns_falcon.result
      mysql-test/suite/funcs_1/r/is_columns_innodb.result
      mysql-test/suite/funcs_1/r/is_columns_memory.result
      mysql-test/suite/funcs_1/r/is_columns_myisam.result
      mysql-test/t/variables.test
      sql/item_func.cc
      sql/log.cc
      sql/mysqld.cc
      sql/rpl_record.cc
      sql/set_var.cc
      sql/sql_class.cc
      sql/sql_class.h
      sql/sql_show.cc
      sql/sql_table.cc
=== modified file 'mysql-test/r/lock.result'
--- a/mysql-test/r/lock.result	2008-10-21 23:12:53 +0000
+++ b/mysql-test/r/lock.result	2009-03-05 15:44:02 +0000
@@ -173,6 +173,33 @@ ERROR HY000: View's SELECT refers to a t
 Cleanup.
 
 drop table t2, t3;
+#
+# Bug#39843 DELETE requires write access to table in subquery in where clause
+#
+DROP TABLE IF EXISTS t1,t2;
+CREATE TABLE t1 (
+table1_rowid SMALLINT NOT NULL
+);
+CREATE TABLE t2 (
+table2_rowid SMALLINT NOT NULL
+);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1);
+LOCK TABLES t1 WRITE, t2 READ;
+# Sub-select should not try to aquire a write lock.
+DELETE FROM t1 
+WHERE EXISTS 
+( 
+SELECT 'x' 
+FROM t2
+WHERE t1.table1_rowid = t2.table2_rowid
+) ;
+# While implementing the patch we didn't break old behavior;
+# The following sub-select should still requires a write lock:
+SELECT * FROM t1 WHERE 1 IN (SELECT * FROM t2 FOR UPDATE);
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+UNLOCK TABLES;
+DROP TABLE t1,t2;
 End of 5.1 tests.
 #
 # Ensure that FLUSH TABLES doesn't substitute a base locked table

=== modified file 'mysql-test/t/lock.test'
--- a/mysql-test/t/lock.test	2008-10-21 23:12:53 +0000
+++ b/mysql-test/t/lock.test	2009-03-05 15:44:02 +0000
@@ -221,6 +221,36 @@ create view v_bug5719 as select * from t
 --echo
 drop table t2, t3;
 
+--echo #
+--echo # Bug#39843 DELETE requires write access to table in subquery in where clause
+--echo #
+--disable_warnings
+DROP TABLE IF EXISTS t1,t2;
+--enable_warnings
+CREATE TABLE t1 (
+table1_rowid SMALLINT NOT NULL
+);
+CREATE TABLE t2 (
+table2_rowid SMALLINT NOT NULL
+);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1);
+LOCK TABLES t1 WRITE, t2 READ;
+--echo # Sub-select should not try to aquire a write lock.
+DELETE FROM t1 
+WHERE EXISTS 
+( 
+SELECT 'x' 
+FROM t2
+WHERE t1.table1_rowid = t2.table2_rowid
+) ;
+--echo # While implementing the patch we didn't break old behavior;
+--echo # The following sub-select should still requires a write lock:
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+SELECT * FROM t1 WHERE 1 IN (SELECT * FROM t2 FOR UPDATE);
+UNLOCK TABLES;
+DROP TABLE t1,t2;
+
 --echo End of 5.1 tests.
 
 --echo #

=== modified file 'sql/sql_cache.cc'
--- a/sql/sql_cache.cc	2009-02-20 12:37:37 +0000
+++ b/sql/sql_cache.cc	2009-03-05 11:44:37 +0000
@@ -419,6 +419,43 @@ TYPELIB query_cache_type_typelib=
   array_elements(query_cache_type_names)-1,"", query_cache_type_names, NULL
 };
 
+
+/**
+  Helper function for determine if a SELECT statement has a SQL_NO_CACHE
+  directive.
+  
+  @param sql A pointer to the first white space character after SELECT
+  
+  @return
+   @retval TRUE The character string contains SQL_NO_CACHE
+   @retval FALSE No directive found.
+*/
+ 
+static bool has_no_cache_directive(char *sql)
+{
+  int i=0;
+  while (sql[i] == ' ')
+    ++i;
+    
+  if (my_toupper(system_charset_info, sql[i])    == 'S' &&
+      my_toupper(system_charset_info, sql[i+1])  == 'Q' &&
+      my_toupper(system_charset_info, sql[i+2])  == 'L' &&
+      my_toupper(system_charset_info, sql[i+3])  == '_' &&
+      my_toupper(system_charset_info, sql[i+4])  == 'N' &&
+      my_toupper(system_charset_info, sql[i+5])  == 'O' &&
+      my_toupper(system_charset_info, sql[i+6])  == '_' &&
+      my_toupper(system_charset_info, sql[i+7])  == 'C' &&
+      my_toupper(system_charset_info, sql[i+8])  == 'A' &&
+      my_toupper(system_charset_info, sql[i+9])  == 'C' &&
+      my_toupper(system_charset_info, sql[i+10]) == 'H' &&
+      my_toupper(system_charset_info, sql[i+11]) == 'E' &&
+      my_toupper(system_charset_info, sql[i+12]) == ' ')
+    return TRUE;
+  
+  return FALSE;       
+}
+
+
 /*****************************************************************************
  Query_cache_block_table method(s)
 *****************************************************************************/
@@ -1254,6 +1291,16 @@ Query_cache::send_result_to_client(THD *
       DBUG_PRINT("qcache", ("The statement is not a SELECT; Not cached"));
       goto err;
     }
+    
+    if (query_length > 20 && has_no_cache_directive(&sql[i+6]))
+    {
+      /*
+        We do not increase 'refused' statistics here since it will be done
+        later when the query is parsed.
+      */
+      DBUG_PRINT("qcache", ("The statement has a SQL_NO_CACHE directive"));
+      goto err;
+    }
   }
 
   STRUCT_LOCK(&structure_guard_mutex);

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2009-02-05 12:49:39 +0000
+++ b/sql/sql_lex.cc	2009-03-05 15:44:02 +0000
@@ -1626,6 +1626,7 @@ void st_select_lex::init_query()
   exclude_from_table_unique_test= no_wrap_view_item= FALSE;
   nest_level= 0;
   link_next= 0;
+  lock_option= TL_READ_DEFAULT;
 }
 
 void st_select_lex::init_select()

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2009-02-04 12:34:03 +0000
+++ b/sql/sql_lex.h	2009-03-05 15:44:02 +0000
@@ -698,6 +698,15 @@ public:
   int cur_pos_in_select_list;
 
   List<udf_func>     udf_list;                  /* udf function calls stack */
+
+  /**
+    Per sub-query locking strategy.
+    Note: This variable might interfer with the corresponding statement-level
+    variable Lex::lock_option because on how different parser rules depend
+    on eachother.
+  */
+  thr_lock_type lock_option;
+
   /* 
     This is a copy of the original JOIN USING list that comes from
     the parser. The parser :

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2009-03-05 10:26:27 +0000
+++ b/sql/sql_parse.cc	2009-03-06 09:24:11 +0000
@@ -5513,6 +5513,14 @@ void mysql_reset_thd_for_next_command(TH
 }
 
 
+/**
+  Resets the lex->current_select object.
+  @note It is assumed that lex->current_select != NULL
+
+  This function is a wrapper around select_lex->init_select() with an added
+  check for the special situation when using INTO OUTFILE and LOAD DATA.
+*/
+
 void
 mysql_init_select(LEX *lex)
 {
@@ -5527,6 +5535,18 @@ mysql_init_select(LEX *lex)
 }
 
 
+/**
+  Used to allocate a new SELECT_LEX object on the current thd mem_root and
+  link it into the relevant lists.
+
+  This function is always followed by mysql_init_select.
+
+  @see mysql_init_select
+
+  @retval TRUE An error occurred
+  @retval FALSE The new SELECT_LEX was successfully allocated.
+*/
+
 bool
 mysql_new_select(LEX *lex, bool move_down)
 {
@@ -6363,7 +6383,6 @@ void st_select_lex::set_lock_for_tables(
   DBUG_ENTER("set_lock_for_tables");
   DBUG_PRINT("enter", ("lock_type: %d  for_update: %d", lock_type,
 		       for_update));
-
   for (TABLE_LIST *tables= (TABLE_LIST*) table_list.first;
        tables;
        tables= tables->next_local)

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2009-02-19 14:10:58 +0000
+++ b/sql/sql_yacc.yy	2009-03-05 15:44:02 +0000
@@ -6973,6 +6973,7 @@ select_lock_type:
           {
             LEX *lex=Lex;
             lex->current_select->set_lock_for_tables(TL_WRITE);
+            lex->current_select->lock_option= TL_WRITE;
             lex->safe_to_cache_query=0;
           }
         | LOCK_SYM IN_SYM SHARE_SYM MODE_SYM
@@ -6980,6 +6981,7 @@ select_lock_type:
             LEX *lex=Lex;
             lex->current_select->
               set_lock_for_tables(TL_READ_WITH_SHARED_LOCKS);
+            lex->current_select->lock_option= TL_READ_WITH_SHARED_LOCKS;
             lex->safe_to_cache_query=0;
           }
         ;
@@ -13525,6 +13527,18 @@ subselect_start:
 subselect_end:
           {
             LEX *lex=Lex;
+            /*
+              Set the required lock level for the tables associated with the
+              current sub-select. This will overwrite previous lock options set
+              using st_select_lex::add_table_to_list in any of the following
+              rules: single_multi, table_wild_one, load_data, table_alias_ref,
+              table_factor.
+              The default lock level is TL_READ_DEFAULT but it can be modified
+              with query options specific for a certain (sub-)SELECT.
+            */
+            lex->current_select->
+              set_lock_for_tables(lex->current_select->lock_option);
+
             lex->pop_context();
             SELECT_LEX *child= lex->current_select;
             lex->current_select = lex->current_select->return_after_parsing();
@@ -13556,6 +13570,7 @@ query_expression_option:
             if (check_simple_select())
               MYSQL_YYABORT;
             Lex->lock_option= TL_READ_HIGH_PRIORITY;
+            Lex->current_select->lock_option= TL_READ_HIGH_PRIORITY;
           }
         | DISTINCT         { Select->options|= SELECT_DISTINCT; }
         | SQL_SMALL_RESULT { Select->options|= SELECT_SMALL_RESULT; }


Attachment: [text/bzr-bundle] bzr/[e-mail]