[Commits] Rev 2839: Fixed LP bug #669420. in file:///home/igor/maria/maria-5.3-bug669420/

Igor Babaev igor at askmonty.org
Tue Nov 2 18:28:56 EET 2010


At file:///home/igor/maria/maria-5.3-bug669420/

------------------------------------------------------------
revno: 2839
revision-id: igor at askmonty.org-20101102162855-ux6i46rb8yy1irg0
parent: igor at askmonty.org-20101030015939-swvo5gymoid0y1cl
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-bug669420
timestamp: Tue 2010-11-02 09:28:55 -0700
message:
  Fixed LP bug #669420.
  This bug in the MRR code for Maria engine caused wrong results when
  MRR was used to scan ranges for each record.
   
-------------- next part --------------
=== modified file 'mysql-test/r/maria_mrr.result'
--- a/mysql-test/r/maria_mrr.result	2010-10-30 01:59:39 +0000
+++ b/mysql-test/r/maria_mrr.result	2010-11-02 16:28:55 +0000
@@ -335,3 +335,68 @@
 1	SIMPLE	t2	ALL	PRIMARY,idx	NULL	NULL	NULL	16	Using where; Using join buffer
 1	SIMPLE	t3	ref	PRIMARY,idx	idx	3	test.t2.v	2	Using index condition; Using where
 DROP TABLE t1,t2,t3;
+#
+# Bug #669420: MRR for Range checked for each record
+#
+CREATE TABLE t1 (
+pk int NOT NULL PRIMARY KEY,
+j int NOT NULL,
+i int NOT NULL,
+v varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
+INDEX i (i),
+INDEX vi (v,i)
+) ENGINE=ARIA;
+INSERT INTO t1 VALUES (10,3,8,'v'),(11,3,8,'f');
+CREATE TABLE t2 (
+pk int NOT NULL PRIMARY KEY,
+j int  NOT NULL,
+i int  NOT NULL,
+v varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
+INDEX i (i),
+INDEX vi (v,i)
+) ENGINE=ARIA;
+INSERT INTO t2 VALUES (10,9,3,'i'),(11,101,186,'x'),(12,0,1,'g');
+SET SESSION join_cache_level=0;
+EXPLAIN 
+SELECT t1.i, t2.i, t2.v, t3.pk, t3.v FROM t1, t2, t2 t3
+WHERE t2.i != 0 AND t3.pk >= t2.i AND t3.v >= t2.v;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	i	4	NULL	2	Using index
+1	SIMPLE	t2	index	i,vi	vi	7	NULL	3	Using where; Using index
+1	SIMPLE	t3	ALL	PRIMARY,vi	NULL	NULL	NULL	3	Range checked for each record (index map: 0x5)
+SELECT t1.i, t2.i, t2.v, t3.pk, t3.v FROM t1, t2, t2 t3
+WHERE t2.i != 0 AND t3.pk >= t2.i AND t3.v >= t2.v;
+i	i	v	pk	v
+8	1	g	10	i
+8	1	g	11	x
+8	1	g	12	g
+8	3	i	10	i
+8	3	i	11	x
+8	1	g	10	i
+8	1	g	11	x
+8	1	g	12	g
+8	3	i	10	i
+8	3	i	11	x
+SET SESSION join_cache_level=1;
+EXPLAIN 
+SELECT t1.i, t2.i, t2.v, t3.pk, t3.v FROM t1, t2, t2 t3
+WHERE t2.i != 0 AND t3.pk >= t2.i AND t3.v >= t2.v;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	i	4	NULL	2	Using index
+1	SIMPLE	t2	index	i,vi	vi	7	NULL	3	Using where; Using index; Using join buffer
+1	SIMPLE	t3	ALL	PRIMARY,vi	NULL	NULL	NULL	3	Range checked for each record (index map: 0x5)
+SELECT t1.i, t2.i, t2.v, t3.pk, t3.v FROM t1, t2, t2 t3
+WHERE t2.i != 0 AND t3.pk >= t2.i AND t3.v >= t2.v;
+i	i	v	pk	v
+8	3	i	10	i
+8	3	i	11	x
+8	3	i	10	i
+8	3	i	11	x
+8	1	g	10	i
+8	1	g	11	x
+8	1	g	12	g
+8	1	g	10	i
+8	1	g	11	x
+8	1	g	12	g
+SET SESSION join_cache_level=DEFAULT;
+DROP TABLE t1,t2;

=== modified file 'mysql-test/t/maria_mrr.test'
--- a/mysql-test/t/maria_mrr.test	2010-10-30 01:59:39 +0000
+++ b/mysql-test/t/maria_mrr.test	2010-11-02 16:28:55 +0000
@@ -52,6 +52,52 @@
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # Bug #669420: MRR for Range checked for each record
+--echo #
+
+CREATE TABLE t1 (
+  pk int NOT NULL PRIMARY KEY,
+  j int NOT NULL,
+  i int NOT NULL,
+  v varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
+  INDEX i (i),
+  INDEX vi (v,i)
+) ENGINE=ARIA;
+INSERT INTO t1 VALUES (10,3,8,'v'),(11,3,8,'f');
+
+CREATE TABLE t2 (
+  pk int NOT NULL PRIMARY KEY,
+  j int  NOT NULL,
+  i int  NOT NULL,
+  v varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
+  INDEX i (i),
+  INDEX vi (v,i)
+) ENGINE=ARIA;
+INSERT INTO t2 VALUES (10,9,3,'i'),(11,101,186,'x'),(12,0,1,'g');
+
+SET SESSION join_cache_level=0;
+
+EXPLAIN 
+SELECT t1.i, t2.i, t2.v, t3.pk, t3.v FROM t1, t2, t2 t3
+  WHERE t2.i != 0 AND t3.pk >= t2.i AND t3.v >= t2.v;
+SELECT t1.i, t2.i, t2.v, t3.pk, t3.v FROM t1, t2, t2 t3
+  WHERE t2.i != 0 AND t3.pk >= t2.i AND t3.v >= t2.v;
+
+SET SESSION join_cache_level=1;
+
+EXPLAIN 
+SELECT t1.i, t2.i, t2.v, t3.pk, t3.v FROM t1, t2, t2 t3
+  WHERE t2.i != 0 AND t3.pk >= t2.i AND t3.v >= t2.v;
+SELECT t1.i, t2.i, t2.v, t3.pk, t3.v FROM t1, t2, t2 t3
+  WHERE t2.i != 0 AND t3.pk >= t2.i AND t3.v >= t2.v;
+
+SET SESSION join_cache_level=DEFAULT;
+
+DROP TABLE t1,t2;
+
+
+
 
 
 

=== modified file 'storage/maria/ha_maria.cc'
--- a/storage/maria/ha_maria.cc	2010-10-14 23:01:40 +0000
+++ b/storage/maria/ha_maria.cc	2010-11-02 16:28:55 +0000
@@ -2286,6 +2286,7 @@
 
 int ha_maria::rnd_end()
 {
+  ds_mrr.dsmrr_close();
   /* Safe to call even if we don't have started a scan */
   maria_scan_end(file);
   return 0;



More information about the commits mailing list