bk commit into 5.1 tree (holyfoot:1.2359) BUG#24186

[holyfoot]

Below is the list of changes that have just been committed into a local
5.1 repository of hf. When hf does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-12-18 10:50:35+04:00, holyfoot@stripped +3 -0
  bug #24186 (nested query across partitions returns fewer records)
  
  Subselect's engine checks table->status field to determine if the
  record was properly found when we use keyread on the table.
  Partition's engine tries to read all it's partitions looking for given
  key before return. So if matching record was found in first partition, and
  no matching records was in second, we have table->status=NOT_FOUND after
  partition searching returns. It makes subselects to skip matching data
  in result.
  Here i just added table->status=0 if we found something matching before.

  mysql-test/r/partition.result@stripped, 2006-12-18 10:50:30+04:00, holyfoot@stripped +28 -0
    result fixed

  mysql-test/t/partition.test@stripped, 2006-12-18 10:50:30+04:00, holyfoot@stripped +33 -0
    testcase

  sql/ha_partition.cc@stripped, 2006-12-18 10:50:30+04:00, holyfoot@stripped +1 -0
    table->status has to be cleaned here

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	holyfoot
# Host:	deer.(none)
# Root:	/home/hf/work/24186/my51-24186

--- 1.56/mysql-test/r/partition.result	2006-12-18 10:50:44 +04:00
+++ 1.57/mysql-test/r/partition.result	2006-12-18 10:50:44 +04:00
@@ -1128,4 +1128,32 @@ a
 18446744073709551613
 18446744073709551614
 drop table t1;
+CREATE TABLE t1 (
+taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+id int(11) NOT NULL DEFAULT '0',
+status varchar(20) NOT NULL DEFAULT '',
+PRIMARY KEY (id,taken)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+PARTITION BY RANGE (to_days(taken))
+(
+PARTITION p15 VALUES LESS THAN (732950) ,
+PARTITION p16 VALUES LESS THAN MAXVALUE ) ;
+INSERT INTO t1 VALUES
+('2006-09-27 21:50:01',22589,'Open'),
+('2006-09-29 21:50:01',22589,'Verified');
+DROP TABLE IF EXISTS t2;
+Warnings:
+Note	1051	Unknown table 't2'
+CREATE TABLE t2 (
+id int(8) NOT NULL,
+severity tinyint(4) NOT NULL DEFAULT '0',
+priority tinyint(4) NOT NULL DEFAULT '0',
+status varchar(20) DEFAULT NULL,
+alien tinyint(4) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES
+(22589,1,1,'Need Feedback',0);
+SELECT t2.id FROM t2 WHERE t2.id IN (SELECT id FROM t1 WHERE status = 'Verified');
+id
+22589
 End of 5.1 tests

--- 1.50/mysql-test/t/partition.test	2006-12-18 10:50:44 +04:00
+++ 1.51/mysql-test/t/partition.test	2006-12-18 10:50:44 +04:00
@@ -1354,4 +1354,37 @@ delete from t1 where a = 184467440737095
 select * from t1;
 drop table t1;
 
+#
+# Bug 24186: Nested query across partitions returns fewer rows
+#
+
+CREATE TABLE t1 (
+  taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+  id int(11) NOT NULL DEFAULT '0',
+  status varchar(20) NOT NULL DEFAULT '',
+  PRIMARY KEY (id,taken)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+PARTITION BY RANGE (to_days(taken))
+(
+PARTITION p15 VALUES LESS THAN (732950) ,
+PARTITION p16 VALUES LESS THAN MAXVALUE ) ;
+
+
+INSERT INTO t1 VALUES
+('2006-09-27 21:50:01',22589,'Open'),
+('2006-09-29 21:50:01',22589,'Verified');
+
+DROP TABLE IF EXISTS t2;
+CREATE TABLE t2 (
+  id int(8) NOT NULL,
+  severity tinyint(4) NOT NULL DEFAULT '0',
+  priority tinyint(4) NOT NULL DEFAULT '0',
+  status varchar(20) DEFAULT NULL,
+  alien tinyint(4) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+INSERT INTO t2 VALUES
+(22589,1,1,'Need Feedback',0);
+
+SELECT t2.id FROM t2 WHERE t2.id IN (SELECT id FROM t1 WHERE status = 'Verified');
 --echo End of 5.1 tests

--- 1.80/sql/ha_partition.cc	2006-12-18 10:50:44 +04:00
+++ 1.81/sql/ha_partition.cc	2006-12-18 10:50:44 +04:00
@@ -4016,6 +4016,7 @@ int ha_partition::handle_ordered_index_s
     m_queue.elements= j;
     queue_fix(&m_queue);
     return_top_record(buf);
+    table->status= 0;
     DBUG_PRINT("info", ("Record returned from partition %d", m_top_entry));
     DBUG_RETURN(0);
   }