List:Commits« Previous MessageNext Message »
From:Oystein.Grovlen Date:May 7 2010 8:04am
Subject:bzr push into mysql-next-mr-bugfixing branch (oystein.grovlen:3140 to
3141) Bug#43618
View as plain text  
 3141 oystein.grovlen@stripped	2010-05-07
      Bug#43618: MyISAM&Maria returns wrong results with 'between' 
                 on timestamp
      
      (Backporting of jorgen.loland@stripped)
         
      When conditions are pushed to an index (ICP), the same conditions
      is not checked for records returned by the index. It is assumed
      that all records qualify for these conditions.
            
      However, for MyISAM, these conditions were checked only for
      forward index lookups (mi_rnext), and not for reverse order
      lookups (mi_rprev). Thus, records that did not qualify could be
      returned if mi_rprev was used.
            
      This patch makes mi_rprev check the pushed conditions in the 
      same way as mi_rnext does.
     @ mysql-test/r/select.result
        Added test for BUG#43618
     @ mysql-test/r/select_jcl6.result
        Added test for BUG#43618
     @ mysql-test/r/subselect3.result
        Records that did not qualify for insertion due to non-matching WHERE condition were inserted due to missing condition check for reverse index lookup with ICP. These records are no longer inserted.
     @ mysql-test/r/subselect3_jcl6.result
        Records that did not qualify for insertion due to non-matching WHERE condition were inserted due to missing condition check for reverse index lookup with ICP. These records are no longer inserted.
     @ mysql-test/t/select.test
        Added test for BUG#43618
     @ storage/myisam/mi_rnext.c
        Modify comment to reflect that record is skipped if it does not qualify for some pushed condition (ICP)
     @ storage/myisam/mi_rprev.c
        Skip record if record does not qualify for a pushed condition (ICP)

    modified:
      mysql-test/r/select.result
      mysql-test/r/select_jcl6.result
      mysql-test/r/subselect3.result
      mysql-test/r/subselect3_jcl6.result
      mysql-test/t/select.test
      storage/myisam/mi_rnext.c
      storage/myisam/mi_rprev.c
 3140 oystein.grovlen@stripped	2010-05-07
      Bug#48093: 6.0 Server not processing equivalent IN clauses 
                 properly with Innodb tables
      
      (Backporting of jorgen.loland@stripped)
            
      When MRR was used to lookup multiple values in an index that
      return ROR ordered records, it was assumed that the returned
      records from MRR were also ROR ordered. This is not true since
      MRR returns all records retrieved for the first value, then all
      records for the second value and so on. Although the records are
      ROR ordered for each value, the end result is not ROR order.
            
      Since the optimizer thought multi-value lookups returned ROR
      ordered records, it tried to do index merge on the returned
      records. These turned out not to be ROR ordered, resulting in
      incorrect merging and in turn missing rows in the result set.
            
      The fix is to invalidate ROR ordering for index lookup 
      algorithms when looking up multiple key values.
     @ mysql-test/r/innodb_mysql.result
        Added test for BUG#48093
     @ mysql-test/t/innodb_mysql.test
        Added test for BUG#48093
     @ sql/opt_range.cc
        Invalidate ROR ordering for index lookup algorithms when looking up multiple key values.

    modified:
      mysql-test/r/innodb_mysql.result
      mysql-test/t/innodb_mysql.test
      sql/opt_range.cc
=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result	2010-05-02 18:01:00 +0000
+++ b/mysql-test/r/select.result	2010-05-07 08:04:15 +0000
@@ -4857,3 +4857,46 @@ SELECT * FROM t1 WHERE 102 < c;
 a	b	c
 DROP TABLE t1;
 End of 5.1 tests
+#
+# BUG#43618: MyISAM&Maria returns wrong results with 'between' 
+#            on timestamp
+#
+CREATE TABLE t1(
+ts TIMESTAMP NOT NULL, 
+c char NULL,
+PRIMARY KEY(ts)
+) ENGINE=myisam;
+INSERT INTO t1 VALUES
+('1971-01-01','a'),
+('2007-05-25','b'),
+('2008-01-01','c'),
+('2038-01-09','d');
+# Enable Index condition pushdown
+select @old_icp:=@@optimizer_switch;
+@old_icp:=@@optimizer_switch
+#
+set optimizer_switch= 'engine_condition_pushdown=on';
+
+# Execute select with invalid timestamp, desc ordering
+SELECT *
+FROM t1 
+WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 
+ORDER BY ts DESC
+LIMIT 2;
+ts	c
+2008-01-01 00:00:00	c
+2007-05-25 00:00:00	b
+
+# Should use index condition
+EXPLAIN
+SELECT *
+FROM t1 
+WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 
+ORDER BY ts DESC
+LIMIT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using index condition
+
+# Restore old value for Index condition pushdown
+set optimizer_switch=@old_icp;
+DROP TABLE t1;

=== modified file 'mysql-test/r/select_jcl6.result'
--- a/mysql-test/r/select_jcl6.result	2010-05-02 18:01:00 +0000
+++ b/mysql-test/r/select_jcl6.result	2010-05-07 08:04:15 +0000
@@ -4861,6 +4861,49 @@ SELECT * FROM t1 WHERE 102 < c;
 a	b	c
 DROP TABLE t1;
 End of 5.1 tests
+#
+# BUG#43618: MyISAM&Maria returns wrong results with 'between' 
+#            on timestamp
+#
+CREATE TABLE t1(
+ts TIMESTAMP NOT NULL, 
+c char NULL,
+PRIMARY KEY(ts)
+) ENGINE=myisam;
+INSERT INTO t1 VALUES
+('1971-01-01','a'),
+('2007-05-25','b'),
+('2008-01-01','c'),
+('2038-01-09','d');
+# Enable Index condition pushdown
+select @old_icp:=@@optimizer_switch;
+@old_icp:=@@optimizer_switch
+#
+set optimizer_switch= 'engine_condition_pushdown=on';
+
+# Execute select with invalid timestamp, desc ordering
+SELECT *
+FROM t1 
+WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 
+ORDER BY ts DESC
+LIMIT 2;
+ts	c
+2008-01-01 00:00:00	c
+2007-05-25 00:00:00	b
+
+# Should use index condition
+EXPLAIN
+SELECT *
+FROM t1 
+WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 
+ORDER BY ts DESC
+LIMIT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using index condition
+
+# Restore old value for Index condition pushdown
+set optimizer_switch=@old_icp;
+DROP TABLE t1;
 set join_cache_level=default;
 show variables like 'join_cache_level';
 Variable_name	Value

=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2010-05-06 14:03:49 +0000
+++ b/mysql-test/r/subselect3.result	2010-05-07 08:04:15 +0000
@@ -1061,7 +1061,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t11	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort; Start materialize; Scan
 1	PRIMARY	t12	ALL	NULL	NULL	NULL	NULL	8	Using where; End materialize; Using join buffer
 1	PRIMARY	t21	ALL	NULL	NULL	NULL	NULL	26	Using where; Using join buffer
-1	PRIMARY	t22	ALL	NULL	NULL	NULL	NULL	32	Using where; Using join buffer
+1	PRIMARY	t22	ALL	NULL	NULL	NULL	NULL	26	Using where; Using join buffer
 select t21.* from t21,t22 where t21.a = t22.a and 
 t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
 a	b	c

=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result	2010-05-06 14:03:49 +0000
+++ b/mysql-test/r/subselect3_jcl6.result	2010-05-07 08:04:15 +0000
@@ -1065,7 +1065,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t11	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort; Start materialize; Scan
 1	PRIMARY	t12	ALL	NULL	NULL	NULL	NULL	8	Using where; End materialize; Using join buffer
 1	PRIMARY	t21	ALL	NULL	NULL	NULL	NULL	26	Using where; Using join buffer
-1	PRIMARY	t22	ALL	NULL	NULL	NULL	NULL	32	Using where; Using join buffer
+1	PRIMARY	t22	ALL	NULL	NULL	NULL	NULL	26	Using where; Using join buffer
 select t21.* from t21,t22 where t21.a = t22.a and 
 t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
 a	b	c

=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test	2010-03-19 08:29:12 +0000
+++ b/mysql-test/t/select.test	2010-05-07 08:04:15 +0000
@@ -4118,3 +4118,53 @@ DROP TABLE t1;
 
 
 --echo End of 5.1 tests
+
+--echo #
+--echo # BUG#43618: MyISAM&Maria returns wrong results with 'between' 
+--echo #            on timestamp
+--echo #
+
+CREATE TABLE t1(
+   ts TIMESTAMP NOT NULL, 
+   c char NULL,
+   PRIMARY KEY(ts)
+) ENGINE=myisam;
+
+INSERT INTO t1 VALUES
+   ('1971-01-01','a'),
+   ('2007-05-25','b'),
+   ('2008-01-01','c'),
+   ('2038-01-09','d');
+
+--echo # Enable Index condition pushdown
+--replace_column 1 #
+select @old_icp:=@@optimizer_switch;
+set optimizer_switch= 'engine_condition_pushdown=on';
+
+--disable_warnings
+
+--echo
+--echo # Execute select with invalid timestamp, desc ordering
+SELECT *
+FROM t1 
+WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 
+ORDER BY ts DESC
+LIMIT 2; 
+
+--echo
+--echo # Should use index condition
+EXPLAIN
+SELECT *
+FROM t1 
+WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 
+ORDER BY ts DESC
+LIMIT 2; 
+--echo
+
+--enable_warnings
+
+--echo # Restore old value for Index condition pushdown
+set optimizer_switch=@old_icp;
+
+DROP TABLE t1;
+

=== modified file 'storage/myisam/mi_rnext.c'
--- a/storage/myisam/mi_rnext.c	2010-05-05 08:11:45 +0000
+++ b/storage/myisam/mi_rnext.c	2010-05-07 08:04:15 +0000
@@ -89,7 +89,10 @@ int mi_rnext(MI_INFO *info, uchar *buf, 
            (info->index_cond_func &&
            !(res= mi_check_index_cond(info, inx, buf))))
     {
-      /* Skip rows inserted by other threads since we got a lock */
+      /* 
+         Skip rows that are either inserted by other threads since
+         we got a lock or do not match pushed index conditions
+      */
       if  ((error=_mi_search_next(info,info->s->keyinfo+inx,
                                   info->lastkey,
                                   info->lastkey_length,

=== modified file 'storage/myisam/mi_rprev.c'
--- a/storage/myisam/mi_rprev.c	2009-12-05 01:26:15 +0000
+++ b/storage/myisam/mi_rprev.c	2010-05-07 08:04:15 +0000
@@ -51,6 +51,33 @@ int mi_rprev(MI_INFO *info, uchar *buf, 
     error=_mi_search(info,share->keyinfo+inx,info->lastkey,
 		     USE_WHOLE_KEY, flag, share->state.key_root[inx]);
 
+  if (!error)
+  {
+    int res= 0;
+    while ((share->concurrent_insert && 
+            info->lastpos >= info->state->data_file_length) ||
+           (info->index_cond_func &&
+            !(res= mi_check_index_cond(info, inx, buf))))
+    {
+      /* 
+         Skip rows that are either inserted by other threads since
+         we got a lock or do not match pushed index conditions
+      */
+      if  ((error=_mi_search_next(info,share->keyinfo+inx,info->lastkey,
+                                  info->lastkey_length,
+                                  SEARCH_SMALLER,
+                                  share->state.key_root[inx])))
+        break;
+    }
+    if (!error && res == 2) 
+    {
+      if (share->concurrent_insert)
+        rw_unlock(&share->key_root_lock[inx]);
+      info->lastpos= HA_OFFSET_ERROR;
+      DBUG_RETURN(my_errno= HA_ERR_END_OF_FILE);
+    }
+  }
+
   if (share->concurrent_insert)
   {
     if (!error)
@@ -67,6 +94,7 @@ int mi_rprev(MI_INFO *info, uchar *buf, 
     }
     mysql_rwlock_unlock(&share->key_root_lock[inx]);
   }
+
   info->update&= (HA_STATE_CHANGED | HA_STATE_ROW_CHANGED);
   info->update|= HA_STATE_PREV_FOUND;
   if (error)


Attachment: [text/bzr-bundle]
Thread
bzr push into mysql-next-mr-bugfixing branch (oystein.grovlen:3140 to3141) Bug#43618Oystein.Grovlen7 May