[Commits] Rev 2842: Merge in file:///home/igor/maria/maria-5.3/

Igor Babaev igor at askmonty.org
Wed Nov 3 01:08:02 EET 2010


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

------------------------------------------------------------
revno: 2842 [merge]
revision-id: igor at askmonty.org-20101102230802-vg1oaa3smmayb03n
parent: igor at askmonty.org-20101102230439-4hs1yg4pmmyd8csq
parent: igor at askmonty.org-20101102171221-sn0rb0sbcpj7qiec
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3
timestamp: Tue 2010-11-02 16:08:02 -0700
message:
  Merge
modified:
  mysql-test/r/maria_mrr.result  maria_mrr.result-20101030015559-2mow70w1pwesh5l2-2
  mysql-test/t/maria_mrr.test    maria_mrr.test-20101030015559-2mow70w1pwesh5l2-1
  storage/maria/ha_maria.cc      sp1f-ha_maria.cc-20060411134405-dmngb4v5x5fxlxhff527ud3etiutxuxk
-------------- 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 17:07:46 +0000
@@ -335,3 +335,110 @@
 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;
+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)
+) ENGINE=ARIA;
+INSERT INTO t1 VALUES 
+(10,3,8,'v'),(11,3,8,'f'),(12,3,5,'v'),(13,2,8,'s'),(14,1,8,'a'),
+(15,0,6,'p'),(16,8,7,'z'),(17,5,2,'a'),(18,9,5,'h'),(19,5,7,'h'),
+(20,4,2,'v'),(21,2,9,'v'),(22,33,142,'b'),(23,5,3,'y'),(24,1,0,'v'),
+(25,9,3,'m'),(26,1,5,'z'),(27,3,9,'n'),(28,8,1,'d'),(29,231,107,'a');
+SET SESSION join_cache_level = 0;
+EXPLAIN
+SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ALL	i	NULL	NULL	NULL	20	
+1	SIMPLE	s	ALL	PRIMARY,i	NULL	NULL	NULL	20	Range checked for each record (index map: 0x3)
+SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j;
+f
+142
+142
+107
+EXPLAIN
+SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j GROUP BY f;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ALL	i	NULL	NULL	NULL	20	Using temporary; Using filesort
+1	SIMPLE	s	ALL	PRIMARY,i	NULL	NULL	NULL	20	Range checked for each record (index map: 0x3)
+SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j GROUP BY f;
+f
+107
+142
+EXPLAIN
+SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j GROUP BY f LIMIT 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	s	ALL	PRIMARY,i	NULL	NULL	NULL	20	Using temporary; Using filesort
+1	SIMPLE	t	ALL	i	NULL	NULL	NULL	20	Using where
+SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j GROUP BY f LIMIT 1;
+f
+107
+SET SESSION join_cache_level=DEFAULT;
+DROP TABLE t1;

=== modified file 'mysql-test/t/maria_mrr.test'
--- a/mysql-test/t/maria_mrr.test	2010-11-02 23:04:39 +0000
+++ b/mysql-test/t/maria_mrr.test	2010-11-02 23:08:02 +0000
@@ -51,3 +51,78 @@
   WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
 
 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;
+
+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)
+) ENGINE=ARIA;
+INSERT INTO t1 VALUES 
+  (10,3,8,'v'),(11,3,8,'f'),(12,3,5,'v'),(13,2,8,'s'),(14,1,8,'a'),
+  (15,0,6,'p'),(16,8,7,'z'),(17,5,2,'a'),(18,9,5,'h'),(19,5,7,'h'),
+  (20,4,2,'v'),(21,2,9,'v'),(22,33,142,'b'),(23,5,3,'y'),(24,1,0,'v'),
+  (25,9,3,'m'),(26,1,5,'z'),(27,3,9,'n'),(28,8,1,'d'),(29,231,107,'a');
+
+SET SESSION join_cache_level = 0;
+
+EXPLAIN
+SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j;
+SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j;
+
+EXPLAIN
+SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j GROUP BY f;
+SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j GROUP BY f;
+
+EXPLAIN
+SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j GROUP BY f LIMIT 1;
+SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j GROUP BY f LIMIT 1;
+
+SET SESSION join_cache_level=DEFAULT;
+
+DROP TABLE t1;

=== 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 17:07:46 +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