From dfc87e8c191ea22c4705b672ba5214bf6933dcb7 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 13 Sep 2007 15:39:16 +0500 Subject: BUG#30590 - delete from memory table with composite btree primary key DELETE query against memory table with btree index may remove not all matching rows. This happens only when DELETE uses index read method to find matching rows. E.g. for queries like DELETE FROM t1 WHERE a=1. Fixed by reverting fix for BUG9719 and applying proper solution. heap/hp_delete.c: Reverted fix for BUG9719 as it makes queries like DELETE FROM t1 WHERE a=1 to remove not all matching rows (assuming this is memory table and there is btree key over `a`). This happens because we calculate info->lastkey_len in heap_rkey(). When we enter heap_rnext(), info->lastkey_len is 0 (set by hp_rb_delete_key()). We need to preserve info->lastkey_len in this situation, otherwise tree_search_key() will always return smallest value in a tree. heap/hp_rfirst.c: If we're performing index_first on a table that was taken from table cache, info->lastkey_len is initialized to previous query. Thus we set info->lastkey_len to proper value for subsequent heap_rnext() calls. This is needed for DELETE queries only, otherwise this variable is not used. Note that the same workaround may be needed for heap_rlast(), but for now heap_rlast() is never used for DELETE queries. heap/hp_rnext.c: An optimization for DELETE queries that use index_first()/index_next(). Use faster tree_search_edge() instead of tree_search_key(). mysql-test/r/heap_btree.result: A test case for BUG#30590. mysql-test/t/heap_btree.test: A test case for BUG#30590. --- heap/hp_delete.c | 3 --- heap/hp_rfirst.c | 11 +++++++++++ heap/hp_rnext.c | 29 +++++++++++++++++++++++++++++ mysql-test/r/heap_btree.result | 7 +++++++ mysql-test/t/heap_btree.test | 9 +++++++++ 5 files changed, 56 insertions(+), 3 deletions(-) diff --git a/heap/hp_delete.c b/heap/hp_delete.c index 266a9da6ca3..90e537081d3 100644 --- a/heap/hp_delete.c +++ b/heap/hp_delete.c @@ -73,10 +73,7 @@ int hp_rb_delete_key(HP_INFO *info, register HP_KEYDEF *keyinfo, int res; if (flag) - { info->last_pos= NULL; /* For heap_rnext/heap_rprev */ - info->lastkey_len= 0; - } custom_arg.keyseg= keyinfo->seg; custom_arg.key_length= hp_rb_make_key(keyinfo, info->recbuf, record, recpos); diff --git a/heap/hp_rfirst.c b/heap/hp_rfirst.c index 85548fea212..6e7ee45af5f 100644 --- a/heap/hp_rfirst.c +++ b/heap/hp_rfirst.c @@ -36,6 +36,17 @@ int heap_rfirst(HP_INFO *info, byte *record, int inx) sizeof(byte*)); info->current_ptr = pos; memcpy(record, pos, (size_t)share->reclength); + /* + If we're performing index_first on a table that was taken from + table cache, info->lastkey_len is initialized to previous query. + Thus we set info->lastkey_len to proper value for subsequent + heap_rnext() calls. + This is needed for DELETE queries only, otherwise this variable is + not used. + Note that the same workaround may be needed for heap_rlast(), but + for now heap_rlast() is never used for DELETE queries. + */ + info->lastkey_len= 0; info->update = HA_STATE_AKTIV; } else diff --git a/heap/hp_rnext.c b/heap/hp_rnext.c index a1bc480333e..513e184c743 100644 --- a/heap/hp_rnext.c +++ b/heap/hp_rnext.c @@ -34,11 +34,40 @@ int heap_rnext(HP_INFO *info, byte *record) heap_rb_param custom_arg; if (info->last_pos) + { + /* + We enter this branch for non-DELETE queries after heap_rkey() + or heap_rfirst(). As last key position (info->last_pos) is available, + we only need to climb the tree using tree_search_next(). + */ pos = tree_search_next(&keyinfo->rb_tree, &info->last_pos, offsetof(TREE_ELEMENT, left), offsetof(TREE_ELEMENT, right)); + } + else if (!info->lastkey_len) + { + /* + We enter this branch only for DELETE queries after heap_rfirst(). E.g. + DELETE FROM t1 WHERE a<10. As last key position is not available + (last key is removed by heap_delete()), we must restart search as it + is done in heap_rfirst(). + + It should be safe to handle this situation without this branch. That is + branch below should find smallest element in a tree as lastkey_len is + zero. tree_search_edge() is a kind of optimisation here as it should be + faster than tree_search_key(). + */ + pos= tree_search_edge(&keyinfo->rb_tree, info->parents, + &info->last_pos, offsetof(TREE_ELEMENT, left)); + } else { + /* + We enter this branch only for DELETE queries after heap_rkey(). E.g. + DELETE FROM t1 WHERE a=10. As last key position is not available + (last key is removed by heap_delete()), we must restart search as it + is done in heap_rkey(). + */ custom_arg.keyseg = keyinfo->seg; custom_arg.key_length = info->lastkey_len; custom_arg.search_flag = SEARCH_SAME | SEARCH_FIND; diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result index 72be6a3e400..639fe1ff897 100644 --- a/mysql-test/r/heap_btree.result +++ b/mysql-test/r/heap_btree.result @@ -307,4 +307,11 @@ UNIQUE USING BTREE(c1) ) ENGINE= MEMORY DEFAULT CHARSET= utf8; INSERT INTO t1 VALUES('1'), ('2'); DROP TABLE t1; +CREATE TABLE t1 (a INT, KEY USING BTREE(a)) ENGINE=MEMORY; +INSERT INTO t1 VALUES(1),(2),(2); +DELETE FROM t1 WHERE a=2; +SELECT * FROM t1; +a +1 +DROP TABLE t1; End of 4.1 tests diff --git a/mysql-test/t/heap_btree.test b/mysql-test/t/heap_btree.test index f8d6afbad04..849b7e12843 100644 --- a/mysql-test/t/heap_btree.test +++ b/mysql-test/t/heap_btree.test @@ -213,4 +213,13 @@ CREATE TABLE t1 ( INSERT INTO t1 VALUES('1'), ('2'); DROP TABLE t1; +# +# BUG#30590 - delete from memory table with composite btree primary key +# +CREATE TABLE t1 (a INT, KEY USING BTREE(a)) ENGINE=MEMORY; +INSERT INTO t1 VALUES(1),(2),(2); +DELETE FROM t1 WHERE a=2; +SELECT * FROM t1; +DROP TABLE t1; + --echo End of 4.1 tests -- cgit v1.2.1 From ff49c551febeb244b36523c06c6379513141b533 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 9 Oct 2007 13:10:51 +0200 Subject: Bug#15522 - create ... select and with merge tables Added test case. The fix for Bug#20662 does also fix this one. mysql-test/r/merge.result: Bug#15522 - create ... select and with merge tables Added test result. mysql-test/t/merge.test: Bug#15522 - create ... select and with merge tables Added test case. --- mysql-test/r/merge.result | 5 +++++ mysql-test/t/merge.test | 14 ++++++++++++++ 2 files changed, 19 insertions(+) diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 42669eeb66f..24a56fbf575 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -876,4 +876,9 @@ CHECK TABLE tm1; Table Op Msg_type Msg_text test.tm1 check status OK DROP TABLE tm1, t1, t2; +CREATE TABLE t1(c1 INT); +CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST; +CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; +ERROR HY000: You can't specify target table 't1' for update in FROM clause +DROP TABLE t1, t2; End of 5.0 tests diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index c3e5cef5e63..b5c1a01fe8e 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -507,4 +507,18 @@ SELECT * FROM tm1; CHECK TABLE tm1; DROP TABLE tm1, t1, t2; +# +# Bug#15522 - create ... select and with merge tables +# +# This was fixed together with Bug#20662 (Infinite loop in CREATE TABLE +# IF NOT EXISTS ... SELECT with locked tables). +# The new behavior for MERGE tables is consistent with the +# CREATE TABLE SELECT behavior for ordinary tables. +# +CREATE TABLE t1(c1 INT); +CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST; +--error ER_UPDATE_TABLE_USED +CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; +DROP TABLE t1, t2; + --echo End of 5.0 tests -- cgit v1.2.1