From: Sergey Glukhov Date: November 23 2010 11:05am Subject: bzr push into mysql-5.1-bugteam branch (sergey.glukhov:3515 to 3516) Bug#56862 List-Archive: http://lists.mysql.com/commits/124726 X-Bug: 56862 Message-Id: <201011231116.oAN3AAwV026313@rcsinet15.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3516 Sergey Glukhov 2010-11-23 Bug#56862 Execution of a query that uses index merge returns a wrong result In case of low memory sort buffer QUICK_INDEX_MERGE_SELECT creates temporary file where is stores row ids which meet QUICK_SELECT ranges except of clustered pk range, clustered range is processed separately. In init_read_record we check if temporary file is used and choose appropriate record access method. It does not take into account that temporary file contains partial result in case of QUICK_INDEX_MERGE_SELECT with clustered pk range. The fix is always to use rr_quick if QUICK_INDEX_MERGE_SELECT with clustered pk range is used. @ mysql-test/suite/innodb/r/innodb_mysql.result test case @ mysql-test/suite/innodb/t/innodb_mysql.test test case @ mysql-test/suite/innodb_plugin/r/innodb_mysql.result test case @ mysql-test/suite/innodb_plugin/t/innodb_mysql.test test case @ sql/opt_range.h added new method @ sql/records.cc The fix is always to use rr_quick if QUICK_INDEX_MERGE_SELECT with clustered pk range is used. modified: mysql-test/suite/innodb/r/innodb_mysql.result mysql-test/suite/innodb/t/innodb_mysql.test mysql-test/suite/innodb_plugin/r/innodb_mysql.result mysql-test/suite/innodb_plugin/t/innodb_mysql.test sql/opt_range.h sql/records.cc 3515 Alexander Nozdrin 2010-11-22 [merge] Merge from mysql-5.0-bugteam. removed: EXCEPTIONS-CLIENT modified: README === modified file 'mysql-test/suite/innodb/r/innodb_mysql.result' --- a/mysql-test/suite/innodb/r/innodb_mysql.result 2010-11-10 08:32:42 +0000 +++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2010-11-23 10:18:47 +0000 @@ -2599,6 +2599,46 @@ rows 3 Extra Using index DROP TABLE t1; # +# Bug#56862 Execution of a query that uses index merge returns a wrong result +# +CREATE TABLE t1 ( +pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, +a int, +b int, +INDEX idx(a)) +ENGINE=INNODB; +INSERT INTO t1(a,b) VALUES +(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), +(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), +(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700), +(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000); +INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1; +INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1; +INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1 VALUES (1000000, 0, 0); +SET SESSION sort_buffer_size = 1024*36; +EXPLAIN +SELECT COUNT(*) FROM +(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) +WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 3537 Using sort_union(idx,PRIMARY); Using where +SELECT COUNT(*) FROM +(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) +WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +COUNT(*) +1537 +SET SESSION sort_buffer_size = DEFAULT; +DROP TABLE t1; End of 5.1 tests # # Test for bug #39932 "create table fails if column for FK is in different === modified file 'mysql-test/suite/innodb/t/innodb_mysql.test' --- a/mysql-test/suite/innodb/t/innodb_mysql.test 2010-11-19 09:29:08 +0000 +++ b/mysql-test/suite/innodb/t/innodb_mysql.test 2010-11-23 10:18:47 +0000 @@ -824,6 +824,48 @@ CREATE INDEX b ON t1(a,b,c,d); DROP TABLE t1; --echo # +--echo # Bug#56862 Execution of a query that uses index merge returns a wrong result +--echo # + +CREATE TABLE t1 ( + pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, + a int, + b int, + INDEX idx(a)) +ENGINE=INNODB; + +INSERT INTO t1(a,b) VALUES + (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), + (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), + (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700), + (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000); +INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1; +INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1; +INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1 VALUES (1000000, 0, 0); + +SET SESSION sort_buffer_size = 1024*36; + +EXPLAIN +SELECT COUNT(*) FROM + (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) + WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; + +SELECT COUNT(*) FROM + (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) + WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; + +SET SESSION sort_buffer_size = DEFAULT; + +DROP TABLE t1; --echo End of 5.1 tests === modified file 'mysql-test/suite/innodb_plugin/r/innodb_mysql.result' --- a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result 2010-09-16 10:51:08 +0000 +++ b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result 2010-11-23 10:18:47 +0000 @@ -2400,4 +2400,42 @@ PACK_KEYS=0; CREATE INDEX a ON t1 (a); CREATE INDEX c on t1 (c); DROP TABLE t1; +CREATE TABLE t1 ( +pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, +a int, +b int, +INDEX idx(a)) +ENGINE=INNODB; +INSERT INTO t1(a,b) VALUES +(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), +(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), +(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700), +(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000); +INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1; +INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1; +INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1 VALUES (1000000, 0, 0); +SET SESSION sort_buffer_size = 1024*36; +EXPLAIN +SELECT COUNT(*) FROM +(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) +WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 3537 Using sort_union(idx,PRIMARY); Using where +SELECT COUNT(*) FROM +(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) +WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +COUNT(*) +1537 +SET SESSION sort_buffer_size = DEFAULT; +DROP TABLE t1; End of 5.1 tests === modified file 'mysql-test/suite/innodb_plugin/t/innodb_mysql.test' --- a/mysql-test/suite/innodb_plugin/t/innodb_mysql.test 2010-09-16 10:51:08 +0000 +++ b/mysql-test/suite/innodb_plugin/t/innodb_mysql.test 2010-11-23 10:18:47 +0000 @@ -645,5 +645,48 @@ CREATE INDEX c on t1 (c); DROP TABLE t1; +# +# Bug#56862 Execution of a query that uses index merge returns a wrong result +# + +CREATE TABLE t1 ( + pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, + a int, + b int, + INDEX idx(a)) +ENGINE=INNODB; + +INSERT INTO t1(a,b) VALUES + (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), + (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), + (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700), + (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000); +INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1; +INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1; +INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1 VALUES (1000000, 0, 0); + +SET SESSION sort_buffer_size = 1024*36; + +EXPLAIN +SELECT COUNT(*) FROM + (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) + WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; + +SELECT COUNT(*) FROM + (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) + WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; + +SET SESSION sort_buffer_size = DEFAULT; + +DROP TABLE t1; --echo End of 5.1 tests === modified file 'sql/opt_range.h' --- a/sql/opt_range.h 2010-07-19 09:03:52 +0000 +++ b/sql/opt_range.h 2010-11-23 10:18:47 +0000 @@ -265,6 +265,7 @@ public: virtual bool reverse_sorted() = 0; virtual bool unique_key_range() { return false; } + virtual bool clustered_pk_range() { return false; } enum { QS_TYPE_RANGE = 0, @@ -533,6 +534,8 @@ public: THD *thd; int read_keys_and_merge(); + bool clustered_pk_range() { return test(pk_quick_select); } + /* used to get rows collected in Unique */ READ_RECORD read_record; }; === modified file 'sql/records.cc' --- a/sql/records.cc 2010-02-26 13:16:46 +0000 +++ b/sql/records.cc 2010-11-23 10:18:47 +0000 @@ -194,6 +194,15 @@ void init_read_record(READ_RECORD *info, if (select && my_b_inited(&select->file)) tempfile= &select->file; + else if (select && select->quick && select->quick->clustered_pk_range()) + { + /* + In case of QUICK_INDEX_MERGE_SELECT with clustered pk range we have to + use its own access method(i.e QUICK_INDEX_MERGE_SELECT::get_next()) as + sort file does not contain rowids which satisfy clustered pk range. + */ + tempfile= 0; + } else tempfile= table->sort.io_cache; if (tempfile && my_b_inited(tempfile)) // Test if ref-records was used No bundle (reason: useless for push emails).