diff options
| -rw-r--r-- | mysql-test/r/view.result | 14 | ||||
| -rw-r--r-- | mysql-test/t/view.test | 19 | ||||
| -rw-r--r-- | sql/sql_base.cc | 34 |
3 files changed, 63 insertions, 4 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index ef40a408932..684c4950acd 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1977,3 +1977,17 @@ A B DROP VIEW v1; DROP TABLE t1; +CREATE TABLE t1 ( bug_table_seq INTEGER NOT NULL); +CREATE OR REPLACE VIEW v1 AS SELECT * from t1; +DROP PROCEDURE IF EXISTS p1; +Warnings: +Note 1305 PROCEDURE p1 does not exist +CREATE PROCEDURE p1 ( ) +BEGIN +DO (SELECT @next := IFNULL(max(bug_table_seq),0) + 1 FROM v1); +INSERT INTO t1 VALUES (1); +END // +CALL p1(); +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index d296d5ebee5..8eeac41efcd 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -1804,7 +1804,6 @@ drop table t1; # # Test for bug #11771: wrong query_id in SELECT * FROM <view> # - CREATE TABLE t1 (f1 char) ENGINE = innodb; INSERT INTO t1 VALUES ('A'); CREATE VIEW v1 AS SELECT * FROM t1; @@ -1815,3 +1814,21 @@ SELECT * FROM t1; DROP VIEW v1; DROP TABLE t1; + +# +# opening table in correct locking mode (BUG#9597) +# +CREATE TABLE t1 ( bug_table_seq INTEGER NOT NULL); +CREATE OR REPLACE VIEW v1 AS SELECT * from t1; +DROP PROCEDURE IF EXISTS p1; +delimiter //; +CREATE PROCEDURE p1 ( ) +BEGIN + DO (SELECT @next := IFNULL(max(bug_table_seq),0) + 1 FROM v1); + INSERT INTO t1 VALUES (1); +END // +delimiter ;// +CALL p1(); +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 2ee1c8c24cc..7021f61a052 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1041,6 +1041,8 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, if (thd->locked_tables || thd->prelocked_mode) { // Using table locks + TABLE *best_table= 0; + int best_distance= INT_MIN, distance; for (table=thd->open_tables; table ; table=table->next) { if (table->s->key_length == key_length && @@ -1049,11 +1051,37 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, table->query_id != thd->query_id && /* skip tables already used by this query */ !(thd->prelocked_mode && table->query_id)) { - table->query_id= thd->query_id; - DBUG_PRINT("info",("Using locked table")); - goto reset; + distance= ((int) table->reginfo.lock_type - + (int) table_list->lock_type); + /* + Find a table that either has the exact lock type requested, + or has the best suitable lock. In case there is no locked + table that has an equal or higher lock than requested, + we still maitain the best_table to produce an error message + about wrong lock mode on the table. The best_table is changed + if bd < 0 <= d or bd < d < 0 or 0 <= d < bd. + + distance < 0 - we have not enough high lock mode + distance > 0 - we have lock mode higher then we require + distance == 0 - we have lock mode exactly which we need + */ + if (best_distance < 0 && distance > best_distance || + distance >= 0 && distance < best_distance) + { + best_distance= distance; + best_table= table; + if (best_distance == 0) + break; + } } } + if (best_table) + { + table= best_table; + table->query_id= thd->query_id; + DBUG_PRINT("info",("Using locked table")); + goto reset; + } /* is it view? (it is work around to allow to open view with locked tables, |