[Commits] Rev 2847: BUG#670417: Diverging results in maria-5.3-mwl128-dsmrr-cpk with join buffer, attempt 4 in file:///home/psergey/dev2/maria-5.3-mwl128-dsmrr-cpk-r2/

Sergey Petrunya psergey at askmonty.org
Mon Nov 8 19:37:02 EET 2010


At file:///home/psergey/dev2/maria-5.3-mwl128-dsmrr-cpk-r2/

------------------------------------------------------------
revno: 2847
revision-id: psergey at askmonty.org-20101108173701-mp2vzqyl8iuia74u
parent: psergey at askmonty.org-20101108121550-99d67ca38bwycbox
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: maria-5.3-mwl128-dsmrr-cpk-r2
timestamp: Mon 2010-11-08 20:37:01 +0300
message:
  BUG#670417: Diverging results in maria-5.3-mwl128-dsmrr-cpk with join buffer, attempt 4
  - Disable identical key handling optimization when
    IndexConditionPushdown is used
=== modified file 'mysql-test/r/myisam_mrr.result'
--- a/mysql-test/r/myisam_mrr.result	2010-11-02 16:32:26 +0000
+++ b/mysql-test/r/myisam_mrr.result	2010-11-08 17:37:01 +0000
@@ -462,3 +462,48 @@
 WHERE `key1` LIKE CONCAT( LEFT( '1' , 7 ) , '%' )
 ORDER BY col1 LIMIT 7;
 drop table t0, t1, t2;
+#
+# BUG#670417: Diverging results in maria-5.3-mwl128-dsmrr-cpk with join buffer (incremental, BKA join)
+#
+set @save_join_cache_level = @@join_cache_level;
+set join_cache_level = 6;
+set @save_join_buffer_size=@@join_buffer_size;
+set join_buffer_size = 136;
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_key int(11) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+INSERT INTO t1 VALUES 
+(10,8,'v','v'),(11,8,'f','f'), (12,5,'v','v'),
+(13,8,'s','s'),(14,8,'a','a'),(15,6,'p','p'),
+(16,7,'z','z'),(17,2,'a','a'),(18,5,'h','h'),
+(19,7,'h','h'),(20,2,'v','v'),(21,9,'v','v'),
+(22,142,'b','b'),(23,3,'y','y'),(24,0,'v','v'),
+(25,3,'m','m'),(26,5,'z','z'),(27,9,'n','n'),
+(28,1,'d','d'),(29,107,'a','a');
+SELECT COUNT(*) 
+FROM 
+t1 AS table2, t1 AS table3 
+where 
+table3.col_varchar_key = table2.col_varchar_key AND 
+table3.col_varchar_key = table2.col_varchar_nokey AND 
+table3.pk<>0;
+COUNT(*)
+50
+EXPLAIN SELECT COUNT(*) 
+FROM 
+t1 AS table2, t1 AS table3 
+where 
+table3.col_varchar_key = table2.col_varchar_key AND 
+table3.col_varchar_key = table2.col_varchar_nokey AND 
+table3.pk<>0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	table2	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
+1	SIMPLE	table3	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.table2.col_varchar_key	3	Using index condition(BKA); Using where; Using join buffer (flat, BKA join)
+set join_cache_level= @save_join_cache_level;
+set join_buffer_size= @save_join_buffer_size;
+drop table t1;

=== modified file 'mysql-test/t/myisam_mrr.test'
--- a/mysql-test/t/myisam_mrr.test	2010-09-15 12:14:19 +0000
+++ b/mysql-test/t/myisam_mrr.test	2010-11-08 17:37:01 +0000
@@ -169,3 +169,52 @@
 WHERE `key1` LIKE CONCAT( LEFT( '1' , 7 ) , '%' )
 ORDER BY col1 LIMIT 7;
 drop table t0, t1, t2;
+
+--echo #
+--echo # BUG#670417: Diverging results in maria-5.3-mwl128-dsmrr-cpk with join buffer (incremental, BKA join)
+--echo #
+
+set @save_join_cache_level = @@join_cache_level;
+set join_cache_level = 6;
+set @save_join_buffer_size=@@join_buffer_size;
+--disable_warnings
+set join_buffer_size = 136;
+--enable_warnings
+
+CREATE TABLE t1 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  col_int_key int(11) NOT NULL,
+  col_varchar_key varchar(1) NOT NULL,
+  col_varchar_nokey varchar(1) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+INSERT INTO t1 VALUES 
+  (10,8,'v','v'),(11,8,'f','f'), (12,5,'v','v'),
+  (13,8,'s','s'),(14,8,'a','a'),(15,6,'p','p'),
+  (16,7,'z','z'),(17,2,'a','a'),(18,5,'h','h'),
+  (19,7,'h','h'),(20,2,'v','v'),(21,9,'v','v'),
+  (22,142,'b','b'),(23,3,'y','y'),(24,0,'v','v'),
+  (25,3,'m','m'),(26,5,'z','z'),(27,9,'n','n'),
+  (28,1,'d','d'),(29,107,'a','a');
+
+SELECT COUNT(*) 
+FROM 
+  t1 AS table2, t1 AS table3 
+where 
+  table3.col_varchar_key = table2.col_varchar_key AND 
+  table3.col_varchar_key = table2.col_varchar_nokey AND 
+  table3.pk<>0;
+
+EXPLAIN SELECT COUNT(*) 
+FROM 
+  t1 AS table2, t1 AS table3 
+where 
+  table3.col_varchar_key = table2.col_varchar_key AND 
+  table3.col_varchar_key = table2.col_varchar_nokey AND 
+  table3.pk<>0;
+
+set join_cache_level= @save_join_cache_level;
+set join_buffer_size= @save_join_buffer_size;
+drop table t1;
+

=== modified file 'sql/multi_range_read.cc'
--- a/sql/multi_range_read.cc	2010-11-08 12:15:50 +0000
+++ b/sql/multi_range_read.cc	2010-11-08 17:37:01 +0000
@@ -472,6 +472,42 @@
   mrr_funcs= *seq_funcs;
   know_key_tuple_params= FALSE;
   buf_manager= buf_manager_arg;
+  /*
+    Short: don't do identical key handling when we have a pushed index
+    condition.
+
+    Long: In order to check pushed index condition, we need to have both 
+    index tuple table->record[0] and range_id.
+    
+    Key_value_records_iterator has special handling for case when we have
+    multiple (key_value, range_id) pairs with the same key_value. In that 
+    case it will make an index lookup only for the first such element, 
+    for subsequent elements it will only return the new range_id.
+
+    The problem here is that h->table->record[0] is shared with the part that
+    does full record retrieval with rnd_pos() calls, and if we have the
+    following scenario:
+
+     1. We scan ranges {(key_value, range_id1), (key_value, range_id2)}
+     2. Iterator makes a lookup with key_value, produces the (index_tuple,
+        range_id1) pair. Index tuple is read into table->record[0], which
+        allows us to check index condition.
+     3. At this point, we figure that key buffer is full, so we sort it,
+        and return control to Mrr_ordered_rndpos_reader.
+     3.1 Mrr_ordered_rndpos_reader gets rowids and makes rnd_pos() calls, which
+         puts some arbitrary data into table->record[0] in the process.
+     3.2 We ask the iterator for the next (rowid, range_id) pair. The iterator
+         puts in range_id2, and that shuld be sufficient (this is identical key
+         handling at work)
+         However, index tuple in table->record[0] has been destroyed and we 
+         can't check index conditon for (index_tuple, range_id2) now.
+
+    TODO: It is possible to support identical key handling and index condition
+    pushdown, working together (one possible solution is to save/restore the 
+    contents of table->record[0]). We will probably implement that.
+
+  */
+  disallow_identical_key_handling= test(mrr_funcs.skip_index_tuple);
   return 0;
 }
 
@@ -1123,7 +1159,8 @@
   uchar *save_cur_index_tuple= cur_index_tuple;
   while (!identical_key_it.read())
   {
-    if (Mrr_ordered_index_reader::key_tuple_cmp(owner, key_in_buf, 
+    if (owner->disallow_identical_key_handling ||
+        Mrr_ordered_index_reader::key_tuple_cmp(owner, key_in_buf, 
                                                 cur_index_tuple))
       break;
     last_identical_key_ptr= cur_index_tuple;

=== modified file 'sql/multi_range_read.h'
--- a/sql/multi_range_read.h	2010-11-08 12:15:50 +0000
+++ b/sql/multi_range_read.h	2010-11-08 17:37:01 +0000
@@ -289,6 +289,12 @@
   /* TRUE <=> need range association, buffers hold {rowid, range_id} pairs */
   bool is_mrr_assoc;
   
+  /*
+    TRUE <=> Don't do optimizations for identical key value (see comment in
+    Mrr_ordered_index_reader::init for details)
+  */
+  bool disallow_identical_key_handling;
+  
   /* Range sequence iteration members */
   RANGE_SEQ_IF mrr_funcs;
   range_seq_t mrr_iter;



More information about the commits mailing list