#At file:///home/thek/Development/cpp/mysqlbzr/5.1-bug39843/ based on revid:staale.smedseng@stripped
2825 Kristofer Pettersson 2009-03-04
Bug#39843 DELETE requires write access to table in subquery in where clause
An unnecessarily restrictive lock were taken on sub-SELECTs during DELETE.
During parsing, a global structure is reused for sub-SELECTs and the attribute
keeping track of lock options were not reset properly.
This patch introduces a new attribute to keep track on the syntactical lock
option elements found in a sub-SELECT and then sets the lock options accordingly.
Now the sub-SELECTs will try to acquire a READ lock if possible
instead of a WRITE lock as inherited from the outer DELETE statement.
@ mysql-test/r/lock.result
Added test case for bug39843
@ mysql-test/t/lock.test
Added test case for bug39843
@ sql/sql_lex.cc
* Reset member variable lock_option on each new query.
@ sql/sql_lex.h
* Introduced new member variable 'lock_option' which is keeping track of the syntactical
lock option of a (sub-)select query.
@ sql/sql_parse.cc
* Wrote comments to functions.
@ sql/sql_yacc.yy
* Introduced an attribute to keep track of syntactical lock options in sub-selects.
* Made sure that the default value TL_READ_DEFAULT is at the begining of each
subselect-rule.
modified:
mysql-test/r/lock.result
mysql-test/t/lock.test
sql/sql_lex.cc
sql/sql_lex.h
sql/sql_parse.cc
sql/sql_yacc.yy
=== modified file 'mysql-test/r/lock.result'
--- a/mysql-test/r/lock.result 2007-08-02 09:59:02 +0000
+++ b/mysql-test/r/lock.result 2009-03-04 15:22:40 +0000
@@ -166,4 +166,31 @@ 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.
=== modified file 'mysql-test/t/lock.test'
--- a/mysql-test/t/lock.test 2007-08-02 09:59:02 +0000
+++ b/mysql-test/t/lock.test 2009-03-04 15:22:40 +0000
@@ -214,4 +214,34 @@ 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.
=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc 2009-01-05 16:10:20 +0000
+++ b/sql/sql_lex.cc 2009-03-04 15:22:40 +0000
@@ -1559,6 +1559,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-01-15 18:11:25 +0000
+++ b/sql/sql_lex.h 2009-03-04 15:22:40 +0000
@@ -688,6 +688,9 @@ public:
int cur_pos_in_select_list;
List<udf_func> udf_list; /* udf function calls stack */
+
+ thr_lock_type lock_option; /* Per sub-query locking strategy */
+
/*
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-02-16 11:38:15 +0000
+++ b/sql/sql_parse.cc 2009-03-04 15:22:40 +0000
@@ -5580,6 +5580,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)
{
@@ -5594,6 +5602,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)
{
@@ -6411,7 +6431,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-16 11:38:15 +0000
+++ b/sql/sql_yacc.yy 2009-03-04 15:22:40 +0000
@@ -6489,7 +6489,8 @@ select_option:
{
if (check_simple_select())
MYSQL_YYABORT;
- Lex->lock_option= TL_READ_HIGH_PRIORITY;
+ 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; }
@@ -6535,6 +6536,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
@@ -6542,6 +6544,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;
}
;
@@ -12909,6 +12912,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();
Attachment: [text/bzr-bundle] bzr/kristofer.pettersson@sun.com-20090304152240-jjfxt0pqjuq4wspa.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (kristofer.pettersson:2825)Bug#39843 | Kristofer Pettersson | 4 Mar |
