#At file:///home/gluh/MySQL/mysql-5.1-bugteam/ based on revid:georgi.kodinov@stripped
3551 Sergey Glukhov 2010-11-02
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
=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result 2010-10-05 08:11:56 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2010-11-02 15:07:45 +0000
@@ -2620,3 +2620,41 @@ t2 CREATE TABLE `t2` (
CONSTRAINT `x` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t2, 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;
=== modified file 'mysql-test/suite/innodb/t/innodb_mysql.test'
--- a/mysql-test/suite/innodb/t/innodb_mysql.test 2010-10-05 08:11:56 +0000
+++ b/mysql-test/suite/innodb/t/innodb_mysql.test 2010-11-02 15:07:45 +0000
@@ -845,3 +845,47 @@ create table t2 (fk int, key x (fk),
constraint x foreign key (FK) references t1 (PK)) engine=InnoDB;
show create table t2;
drop table t2, 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;
=== 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-02 15:07:45 +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-02 15:07:45 +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-02 15:07:45 +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-02 15:07:45 +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
Attachment: [text/bzr-bundle] bzr/sergey.glukhov@oracle.com-20101102150745-ejxt7p1n0ibukvr2.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (sergey.glukhov:3551) Bug#56862 | Sergey Glukhov | 2 Nov |
