Merge siva.hindu.god:/home/tsmith/m/bk/50

into  siva.hindu.god:/home/tsmith/m/bk/maint/50


mysql-test/r/type_datetime.result:
  Auto merged
mysql-test/t/outfile.test:
  Auto merged
mysql-test/t/type_datetime.test:
  Auto merged
sql/field.cc:
  Auto merged
sql/item.cc:
  Auto merged
sql/item.h:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_func.cc:
  Auto merged
sql/item_func.h:
  Auto merged
sql/my_decimal.h:
  Auto merged
sql/mysql_priv.h:
  Auto merged
sql/sp.cc:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_class.h:
  Auto merged

1 files changed, 54 insertions, 0 deletions

diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index c9e1de8c3ab..90f9047291c 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -518,4 +518,58 @@ select * from t1;
 
 drop table t1;
 
+#
+# Bug #28189: optimizer erroniously prefers ref access to range access 
+#             for an InnoDB table
+#
+
+CREATE TABLE t1(
+  id int AUTO_INCREMENT PRIMARY KEY,
+  stat_id int NOT NULL,
+  acct_id int DEFAULT NULL,
+  INDEX idx1 (stat_id, acct_id),
+  INDEX idx2 (acct_id)
+) ENGINE=MyISAM;
+
+CREATE TABLE t2(
+  id int AUTO_INCREMENT PRIMARY KEY,
+  stat_id int NOT NULL,
+  acct_id int DEFAULT NULL,
+  INDEX idx1 (stat_id, acct_id),
+  INDEX idx2 (acct_id)
+) ENGINE=InnoDB;
+
+INSERT INTO t1(stat_id,acct_id) VALUES
+  (1,759), (2,831), (3,785), (4,854), (1,921),
+  (1,553), (2,589), (3,743), (2,827), (2,545),
+  (4,779), (4,783), (1,597), (1,785), (4,832),
+  (1,741), (1,833), (3,788), (2,973), (1,907);
+
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+UPDATE t1 SET acct_id=785 
+  WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id);
+OPTIMIZE TABLE t1;
+
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(*) FROM t1 WHERE acct_id=785;
+
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785; 
+
+INSERT INTO t2 SELECT * FROM t1;
+OPTIMIZE TABLE t2;
+
+EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785;
+
+DROP TABLE t1,t2; 
+
 --echo End of 5.0 tests