Fixed a cost estimation bug introduced into in the function best_access_path

of the 5.3 code line after a merge with 5.2 on 2010-10-28
in order not to allow the cost to access a joined table to be equal
to 0 ever.

Expanded data sets for many test cases to get the same execution plans
as before.

22 files changed, 177 insertions, 30 deletions

diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 9ac47055265..1c8cbec8a3e 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -363,7 +363,9 @@ CREATE TABLE t2 (a varchar(1) , KEY (a)) ;
 INSERT INTO t2 VALUES ('c'), (NULL), ('r');
 
 CREATE TABLE t3 (a varchar(1), b varchar(1));
-INSERT INTO t3 VALUES ('e', 'c'), ('c', 'c'), ('c', 'r');
+INSERT INTO t3 VALUES
+  ('e', 'c'), ('c', 'c'), ('c', 'r'), ('g', 'a'), ('b', 'x'), ('b', 'y'),
+  ('h', 'w'), ('d', 'z'), ('k', 'v'), ('j', 's'), ('m', 'p'), ('l', 'q');
 
 CREATE VIEW v1 AS SELECT a, MIN(b) AS b FROM t3 GROUP BY a;
 
@@ -599,7 +601,10 @@ INSERT INTO t1 VALUES (0);
 CREATE TABLE t2 (a  varchar(32), b int, KEY (a)) ;
 INSERT INTO t2 VALUES
   ('j',28), ('c',29), ('i',26), ('c',29), ('k',27),
-  ('j',28), ('c',29), ('i',25), ('d',26), ('k',27);
+  ('j',28), ('c',29), ('i',25), ('d',26), ('k',27),
+  ('n',28), ('d',29), ('m',26), ('e',29), ('p',27),
+  ('w',28), ('x',29), ('y',25), ('z',26), ('s',27);
+
 
 CREATE TABLE t3 (a varchar(32));
 INSERT INTO t3 VALUES ('j'), ('c');
diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test
index 7af9d3706e6..11cb4eedf05 100644
--- a/mysql-test/t/fulltext.test
+++ b/mysql-test/t/fulltext.test
@@ -428,6 +428,8 @@ DROP TABLE t1;
 CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b));
 INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1),
                      ('test', 1),('test', 2),('test', 3),('test', 4);
+INSERT INTO t1 VALUES('test', 5),('test', 6),('test', 7),('test', 8),
+                     ('test', 5),('test', 6),('test', 7),('test', 8);
 
 EXPLAIN SELECT * FROM t1
 WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 925cec81854..433deced90e 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -1220,7 +1220,9 @@ CREATE TABLE t2 (a int, b int, INDEX idx(a));
 INSERT INTO t1 VALUES (5,30), (3,20), (7,40), (2,10), (8,30), (1,10), (4,20);     
 INSERT INTO t2 VALUES (7,10), (1,20), (2,20), (8,20), (8,10), (1,20);
 INSERT INTO t2 VALUES (1,10), (4,20), (3,20), (7,20), (7,10), (1,20); 
-    
+INSERT INTO t2 VALUES (17,10), (11,20), (12,20), (18,20), (18,10), (11,20);
+INSERT INTO t2 VALUES (11,10), (14,20), (13,20), (17,20), (17,10), (11,20); 
+   
 set join_buffer_size=32; 
 set join_cache_level=8;  
  
@@ -1241,6 +1243,7 @@ CREATE TABLE t1 (a int NOT NULL);
 INSERT INTO t1 VALUES (2), (4), (3), (5), (1);
 CREATE TABLE t2 (a int NOT NULL, b int NOT NULL, INDEX i_a(a));
 INSERT INTO t2 VALUES (4,10), (2,10), (2,30), (2,20), (4,20);
+INSERT INTO t2 VALUES (14,10), (12,10), (15,30), (12,20), (14,20);
 
 EXPLAIN
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
@@ -1564,6 +1567,9 @@ create table t2 (id1 int, id2 int, index idx2 (id1));
 insert into t2 values 
   (20, 100), (30, 400), (20, 400), (30, 200), (10, 300), (10, 200), (40, 100),
   (40, 200), (30, 300), (10, 400), (20, 200), (20, 300);   
+insert into t2 values 
+  (21, 10), (31, 400), (21, 400), (31, 200), (11, 300), (11, 200), (41, 100),
+  (41, 200), (31, 300), (11, 400), (21, 200), (21, 300);   
 
 set join_cache_level=6;
 
@@ -1602,14 +1608,26 @@ insert into t2 values
   (30, 'bbb'), (10, 'b'), (70, 'bbbbbbb'), (60, 'bbbbbb'),
   (31, 'bbb'), (11, 'b'), (71, 'bbbbbbb'), (61, 'bbbbbb'),
   (32, 'bbb'), (12, 'b'), (72, 'bbbbbbb'), (62, 'bbbbbb');
+insert into t2 values
+  (130, 'bbb'), (110, 'b'), (170, 'bbbbbbb'), (160, 'bbbbbb'),
+  (131, 'bbb'), (111, 'b'), (171, 'bbbbbbb'), (161, 'bbbbbb'),
+  (132, 'bbb'), (112, 'b'), (172, 'bbbbbbb'), (162, 'bbbbbb');
 insert into t3 values
   (4000, 'dddd'), (3000, 'ddd'), (1000, 'd'), (8000, 'dddddddd'),
   (4001, 'dddd'), (3001, 'ddd'), (1001, 'd'), (8001, 'dddddddd'),
   (4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd');
+insert into t3 values
+  (14000, 'dddd'), (13000, 'ddd'), (11000, 'd'), (18000, 'dddddddd'),
+  (14001, 'dddd'), (13001, 'ddd'), (11001, 'd'), (18001, 'dddddddd'),
+  (4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd');
 insert into t4 values
   (200, 'cc'), (600, 'cccccc'), (300, 'ccc'), (500, 'ccccc'),
   (201, 'cc'), (601, 'cccccc'), (301, 'ccc'), (501, 'ccccc'),
   (202, 'cc'), (602, 'cccccc'), (302, 'ccc'), (502, 'ccccc');
+insert into t4 values
+  (1200, 'cc'), (1600, 'cccccc'), (1300, 'ccc'), (1500, 'ccccc'),
+  (1201, 'cc'), (1601, 'cccccc'), (1301, 'ccc'), (1501, 'ccccc'),
+  (1202, 'cc'), (1602, 'cccccc'), (1302, 'ccc'), (1502, 'ccccc');
 
 --disable_result_log
 --disable_warnings
@@ -2264,7 +2282,9 @@ CREATE TABLE t1 (b int);
 INSERT INTO t1 VALUES (NULL),(3);
 
 CREATE TABLE t2 (a int, b int, KEY (b));
-INSERT INTO t2 VALUES (100,NULL),(150,200);
+INSERT INTO t2 VALUES
+  (100,NULL),(150,200),(50,150),(250,350),(180,210),(100,150),
+  (101,NULL),(151,200),(51,150),(251,350),(181,210),(101,150);
 
 set join_cache_level = 5;
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
@@ -2292,6 +2312,7 @@ INSERT INTO t1 VALUES (NULL),("some varchar");
 
 CREATE TABLE t2 (a int, b varchar(100), KEY (b));
 INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar");
+INSERT INTO t2 VALUES (100,NULL),(150,"long varchar"),(200,"varchar"),(250,"long long long varchar");
 
 set join_cache_level = 5;
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
@@ -2393,6 +2414,17 @@ INSERT INTO t3 VALUES
   (1,0,'2002-07-13','06:34:26','v','v'), (9,3,'2003-01-03','18:07:38','m','m'),
   (1,5,'2006-04-02','13:55:23','z','z'), (3,9,'2006-10-19','20:32:28','n','n'),
   (8,1,'2005-06-08','11:57:44','d','d'), (231,107,'2006-12-26','03:10:35','a','a');
+INSERT INTO t3 VALUES
+  (103,108,'2008-12-04','00:00:00','a','v'), (103,108,'2009-03-28','00:00:00','b','f'),
+  (103,105,'1900-01-01','00:55:47','c','v'), (102,108,'2009-10-02','00:00:00','d','s'),
+  (100,108,'1900-01-01','20:51:59','e','a'), (100,106,'2008-06-04','09:47:27','f','p'),
+  (108,107,'2009-01-13','21:58:29','g','z'), (105,102,'1900-01-01','22:45:53','h','a'),
+  (109,105,'2008-01-28','14:06:48','i','h'), (105,107,'2004-09-18','22:17:16','j','h'),
+  (104,102,'2006-10-14','14:59:37','k','v'), (102,109,'1900-01-01','23:37:40','l','v'),
+  (1033,1142,'2000-11-28','14:14:01','m','b'), (105,103,'2008-04-04','02:54:19','n','y'),
+  (100,100,'2002-07-13','06:34:26','o','v'), (109,103,'2003-01-03','18:07:38','p','m'),
+  (100,105,'2006-04-02','13:55:23','q','z'), (103,109,'2006-10-19','20:32:28','s','n'),
+  (108,100,'2005-06-08','11:57:44','t','d'), (1231,1107,'2006-12-26','03:10:35','v','a');
 
 CREATE TABLE t1 SELECT * FROM t3;
 DELETE FROM t1 WHERE i > 8;
@@ -2426,6 +2458,14 @@ INSERT INTO t1 VALUES
   (10,8,'v'), (11,8,'f'), (12,5,'v'), (13,8,'s'), (14,8,'a'),
   (15,6,'p'), (16,7,'z'), (17,2,'a'), (18,5,'h'), (19,7,'h'),
   (25,3,'m'), (26,5,'z'), (27,9,'n'), (28,1,'d'), (29,107,'a');
+INSERT INTO t1 VALUES
+  (110,8,'x'), (111,8,'y'), (112,5,'v'), (113,8,'z'), (114,8,'i'),
+  (115,6,'j'), (116,7,'t'), (117,2,'b'), (118,5,'j'), (119,7,'w'),
+  (125,3,'q'), (126,5,'o'), (127,9,'n'), (128,1,'e'), (129,107,'c');
+INSERT INTO t1 VALUES
+  (210,8,'b'), (211,8,'c'), (212,5,'d'), (213,8,'e'), (214,8,'g'),
+  (215,6,'f'), (216,7,'h'), (217,2,'i'), (218,5,'j'), (219,7,'k'),
+  (225,3,'l'), (226,5,'m'), (227,9,'n'), (228,1,'o'), (229,107,'p');
 
 CREATE TABLE t2 (
   pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
@@ -2531,6 +2571,11 @@ INSERT INTO t2 VALUES
   (1, 12, 102), (8, 81, 801), (7, 70, 700), (12, 120, 1200),
   (8, 82, 802), (1, 13, 103), (1, 14, 104), (3, 31, 301),
   (1, 15, 105), (8, 83, 803), (7, 71, 701);
+INSERT INTO t2 VALUES
+  (108, 80, 800), (101, 10, 100), (101, 11, 101), (103, 30, 300),
+  (101, 12, 102), (108, 81, 801), (107, 70, 700), (1012, 120, 1200),
+  (108, 82, 802), (101, 13, 103), (101, 14, 104), (103, 31, 301),
+  (101, 15, 105), (108, 83, 803), (107, 71, 701);
 
 SET SESSION join_cache_level = 4;
 SET SESSION join_buffer_size = 192;
@@ -2567,6 +2612,12 @@ INSERT INTO t2 VALUES
   ('abcdefjhjk',1015414784), ('or',4), ('now',0), ('abcdefjhjk',-32702464),
   ('abcdefjhjk',4), ('time',1078394880), ('f',4), ('m',-1845559296), 
   ('ff', 5), ('abcdefjhjk',-1074397184);
+INSERT INTO t2 VALUES
+  ('dig',5), ('were',-1631322112), ('is',3), ('abcdefjhjl',3), 
+  ('abcdefjh',4), ('told',-824573952), ('tt',0),('vv',-1711013888),
+  ('abcdefjhjj',1015414784), ('and',4), ('here',0), ('abcdefjhjm',-32702464),
+  ('abcdefjhji',4), ('space',1078394880), ('fs',4), ('mn',-1845559296), 
+  ('fq', 5), ('abcdefjhjp',-1074397184);
 
 EXPLAIN 
 SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v;
@@ -2614,6 +2665,13 @@ INSERT INTO t2 VALUES
  (11,6,'yes'), (12,NULL,'will'), (13,NULL,'o'), (14,NULL,'k'), (15,NULL,'she'),
  (16,-1450835968,'abcdefjhjkl'), (17,-975831040,'abcdefjhjkl'), (18,NULL,'z'),
  (19,-343932928,'t');
+INSERT INTO t2 VALUES 
+ (101,6,'yes'), (102,NULL,'will'), (103,NULL,'o'), (104,NULL,'k'), (105,NULL,'she'),
+ (106,-1450835968,'abcdefjhjkl'), (107,-975831040,'abcdefjhjkl'), (108,NULL,'z'),
+ (100,-343932928,'t'),
+ (111,6,'yes'), (112,NULL,'will'), (113,NULL,'o'), (114,NULL,'k'), (115,NULL,'she'),
+ (116,-1450835968,'abcdefjhjkl'), (117,-975831040,'abcdefjhjkl'), (118,NULL,'z'),
+ (119,-343932928,'t');
 
 CREATE TABLE t3 (
   pk int NOT NULL PRIMARY KEY,
@@ -2630,6 +2688,15 @@ INSERT INTO t3 VALUES
 (26,NULL,'all'), (27,1443168256,'c'), (28,1427046400,'right'),
 (31,7,'abcdefjhjkl'), (32,6,'y'), (33,NULL,'to'), (34,7,'n'), (35,7,'look'),
 (36,NULL,'all'), (37,1443168256,'c'), (38,1427046400,'right');
+INSERT INTO t3 VALUES 
+(101,7,'abcdefjhjkl'),(102,6,'y'), (103,NULL,'to'),(104,7,'n'),(105,7,'look'),
+(106,NULL,'all'), (107,1443168256,'c'), (108,1427046400,'right'),
+(111,7,'abcdefjhjkl'), (112,6,'y'), (113,NULL,'to'), (114,7,'n'), (115,7,'look'),
+(116,NULL,'all'), (117,1443168256,'c'), (118,1427046400,'right'),
+(121,7,'abcdefjhjkl'), (122,6,'y'), (123,NULL,'to'), (124,7,'n'), (125,7,'look'),
+(126,NULL,'all'), (127,1443168256,'c'), (128,1427046400,'right'),
+(131,7,'abcdefjhjkl'), (132,6,'y'), (133,NULL,'to'), (134,7,'n'), (135,7,'look'),
+(136,NULL,'all'), (137,1443168256,'c'), (138,1427046400,'right');
 
 SET SESSION join_buffer_size = 192;
 
@@ -2665,6 +2732,10 @@ INSERT INTO t2 VALUES
   (10, 'a'), (20, 'c'), (30, 'aa'), (4, 'bb'),
   (11, 'a'), (21, 'c'), (31, 'aa'), (41, 'cc'),
   (12, 'a'), (22, 'c'), (32, 'bb'), (42, 'aa');
+INSERT INTO t2 VALUES 
+  (110, 'a'), (120, 'c'), (130, 'aa'), (14, 'bb'),
+  (111, 'a'), (121, 'c'), (131, 'aa'), (141, 'cc'),
+  (112, 'a'), (122, 'c'), (132, 'bb'), (142, 'aa');
 
 SELECT * FROM t1,t2 WHERE t2.a=t1.a;
 
@@ -2766,6 +2837,9 @@ INSERT IGNORE INTO t3 VALUES  (9,0,0,2), (1,0,0,7);
 
 CREATE TABLE t4 (pk int, a4 int, INDEX idx(a4)) ;
 INSERT IGNORE INTO t4 VALUES (2,NULL), (8,0);
+INSERT IGNORE INTO t4 VALUES (12,10), (18,20);
+INSERT IGNORE INTO t4 VALUES (22,11), (28,21);
+INSERT IGNORE INTO t4 VALUES (32,12), (38,22);
 
 CREATE TABLE t5 (pk int, a5 int) ;
 INSERT IGNORE INTO t5 VALUES (2,0), (8,0);
@@ -2941,7 +3015,10 @@ INSERT INTO t1 VALUES
 INSERT INTO t2 VALUES
   (1,'Bbbb'), (2,'BBB'), (3,'bbbb'), (4,'AaA'), (5,'CC'),
   (6,'cC'), (7,'CCC'), (8,'AAA'), (9,'bBbB'), (10,'aaaa'),
-  (11,'a'), (12,'dd'), (13,'EE'), (14,'ee'), (15,'D');
+  (11,'a'), (12,'dd'), (13,'EE'), (14,'ee'), (15,'D'),
+  (101,'Bbbb'), (102,'BBB'), (103,'bbbb'), (104,'AaA'), (105,'CC'),
+  (106,'cC'), (107,'CCC'), (108,'AAA'), (109,'bBbB'), (110,'aaaa'),
+  (111,'a'), (112,'dd'), (113,'EE'), (114,'ee'), (115,'D');
 
 SET SESSION join_cache_level = 4;
 
@@ -3063,7 +3140,10 @@ CREATE TABLE t2 ( f1 varchar(10) , f2 int(11) , KEY (f1));
 INSERT INTO t2 VALUES
   ('hgtofubn',1), ('GDOXZ',91), ('n',2), ('fggxgalh',88),
   ('hgtofu',1), ('GDO',101), ('n',3), ('fggxga',55),
-  ('hgtofu',3), ('GDO',33), ('nn',3), ('fggxgarrr',77);
+  ('hgtofu',3), ('GDO',33), ('nn',3), ('fggxgarrr',77),
+  ('jgtofu',3), ('JDO',33), ('mn',3), ('jggxgarrr',77),
+  ('igtofu',3), ('IDO',33), ('ln',3), ('iggxgarrr',77);
+
 
 SET SESSION join_cache_level=3;
 
@@ -3085,7 +3165,9 @@ INSERT INTO t1 VALUES ('o'), ('u');
 CREATE TABLE t2 (a int, v varchar(1), INDEX idx (v)) ;
 INSERT INTO t2 VALUES
   (8,NULL), (10,'b'), (5,'k'), (4,NULL),
-  (1,NULL), (11,'u'), (7,NULL), (2,'d');
+  (1,NULL), (11,'u'), (7,NULL), (2,'d'), 
+  (18,'u'), (11,'b'), (15,'k'), (12,'d'), 
+  (18,'x'), (11,'y'), (15,'l'), (12,'e'); 
 
 SET SESSION join_buffer_size = 255;
 
diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index deda56eb8ee..b617331de38 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -454,6 +454,7 @@ SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
        ON t3.a=1 AND t2.b=t4.b
     WHERE t1.a <= 2;
 
+INSERT INTO t2 VALUES  (-1,9,0), (-3,10,0), (-2,8,0), (-4,11,0), (-5,15,0);
 CREATE INDEX idx_b ON t2(b);
 
 EXPLAIN EXTENDED
@@ -461,12 +462,12 @@ SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
   FROM (t3,t4)
        LEFT JOIN              
        (t1,t2)
-       ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
+       ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b AND t2.a>0;
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
   FROM (t3,t4)
        LEFT JOIN              
        (t1,t2)
-       ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
+       ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b AND t2.a>0;
 
 EXPLAIN EXTENDED
 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
@@ -477,7 +478,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
          t2
          LEFT JOIN              
          (t3, t4)
-         ON t3.a=1 AND t2.b=t4.b,
+         ON t3.a=1 AND t2.b=t4.b AND t2.a>0,
          t5 
          LEFT JOIN 
          ( 
@@ -504,6 +505,8 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
            (t8.b=t9.b OR t8.c IS NULL) AND
            (t9.a=1); 
 
+INSERT INTO t4 VALUES (-3,12,0), (-4,13,0), (-1,11,0), (-3,11,0), (-5,15,0);
+INSERT INTO t5 VALUES (-3,11,0), (-2,12,0), (-3,13,0), (-4,12,0);
 CREATE INDEX idx_b ON t4(b);
 CREATE INDEX idx_b ON t5(b);
 
@@ -516,7 +519,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
          t2
          LEFT JOIN              
          (t3, t4)
-         ON t3.a=1 AND t2.b=t4.b,
+         ON t3.a=1 AND t2.b=t4.b AND t2.a>0 AND t4.a>0,
          t5 
          LEFT JOIN 
          ( 
@@ -525,7 +528,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
            t8
            ON t7.b=t8.b AND t6.b < 10
          )
-         ON t6.b >= 2 AND t5.b=t7.b 
+         ON t6.b >= 2 AND t5.b=t7.b AND t5.a>0 
        )
        ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
           (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
@@ -543,6 +546,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
            (t8.b=t9.b OR t8.c IS NULL) AND
            (t9.a=1); 
 
+INSERT INTO t8 VALUES (-3,12,0), (-1,14,0), (-5,15,0), (-1,11,0), (-4,13,0);
 CREATE INDEX idx_b ON t8(b);
 
 EXPLAIN EXTENDED
@@ -554,16 +558,16 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
          t2
          LEFT JOIN              
          (t3, t4)
-         ON t3.a=1 AND t2.b=t4.b,
+         ON t3.a=1 AND t2.b=t4.b AND t2.a>0 AND t4.a>0,
          t5 
          LEFT JOIN 
          ( 
            (t6, t7)
            LEFT JOIN 
            t8
-           ON t7.b=t8.b AND t6.b < 10
+           ON t7.b=t8.b AND t6.b < 10 AND t8.a>=0
          )
-         ON t6.b >= 2 AND t5.b=t7.b 
+         ON t6.b >= 2 AND t5.b=t7.b AND t5.a>0
        )
        ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
           (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
@@ -581,6 +585,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
            (t8.b=t9.b OR t8.c IS NULL) AND
            (t9.a=1); 
 
+INSERT INTO t1 VALUES (-1,133,0), (-2,12,0), (-3,11,0), (-5,15,0);
 CREATE INDEX idx_b ON t1(b);
 CREATE INDEX idx_a ON t0(a);
 
@@ -606,7 +611,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
        )
        ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
           (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
-          (t1.a != 2),
+          (t1.a != 2) AND t1.a>0,
        t9
      WHERE t0.a=1 AND
            t0.b=t1.b AND          
@@ -641,7 +646,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
        )
        ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
           (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
-          (t1.a != 2),
+          (t1.a != 2) AND t1.a>0,
        t9
      WHERE t0.a=1 AND
            t0.b=t1.b AND          
diff --git a/mysql-test/t/join_nested_jcl6.test b/mysql-test/t/join_nested_jcl6.test
index 6b04d8d58b5..809755b1fbf 100644
--- a/mysql-test/t/join_nested_jcl6.test
+++ b/mysql-test/t/join_nested_jcl6.test
@@ -26,6 +26,9 @@ INSERT INTO t5 VALUES (1,1,0), (2,2,0), (3,3,0);
 INSERT INTO t6 VALUES (1,2,0), (3,2,0), (6,1,0);
 INSERT INTO t7 VALUES (1,1,0), (2,2,0);
 INSERT INTO t8 VALUES (0,2,0), (1,2,0);
+INSERT INTO t6 VALUES (-1,12,0), (-3,13,0), (-6,11,0), (-4,14,0);
+INSERT INTO t7 VALUES (-1,11,0), (-2,12,0), (-3,13,0), (-4,14,0), (-5,15,0);
+INSERT INTO t8 VALUES (-3,13,0), (-1,12,0), (-2,14,0), (-5,15,0), (-4,16,0);
 
 EXPLAIN
 SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
@@ -35,10 +38,10 @@ SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
          (t6, t7)
          LEFT JOIN 
          t8
-         ON t7.b=t8.b AND t6.b < 10
+         ON t7.b=t8.b AND t6.b < 10 
        )
        ON t6.b >= 2 AND t5.b=t7.b AND
-          (t8.a > 0 OR t8.c IS NULL);
+          (t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0;
 
 SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
   FROM t5 
@@ -50,7 +53,7 @@ SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
          ON t7.b=t8.b AND t6.b < 10
        )
        ON t6.b >= 2 AND t5.b=t7.b AND
-          (t8.a > 0 OR t8.c IS NULL);
+          (t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0;
 
 DELETE FROM t5;
 DELETE FROM t6;
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 2311edc46b7..f88759c7b67 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1064,6 +1064,8 @@ insert into t2 select if(t1.a is null, 10, t1.a) from t1;
 
 create table t3 (a int, b int, index idx(a));  
 insert into t3 values (1, 100), (3, 301), (4, 402), (1, 102), (1, 101);
+insert into t3 values (11, 100), (33, 301), (44, 402), (11, 102), (11, 101);
+insert into t3 values (22, 100), (53, 301), (64, 402), (22, 102), (22, 101);
 
 analyze table t1,t2,t3;
 
diff --git a/mysql-test/t/maria_mrr.test b/mysql-test/t/maria_mrr.test
index 939deac6bb7..4cd4c277a7f 100644
--- a/mysql-test/t/maria_mrr.test
+++ b/mysql-test/t/maria_mrr.test
@@ -74,7 +74,9 @@ CREATE TABLE t3(
   PRIMARY KEY (pk), INDEX idx (v, i)
 ) ENGINE=ARIA;
 INSERT INTO t3 SELECT * FROM t1;
-INSERT INTO t3 VALUES (88, 442, 'y'), (99, 445, 'w') ;
+INSERT INTO t3 VALUES
+  (88, 442, 'y'), (99, 445, 'w'), (87, 442, 'z'), (98, 445, 'v'), (86, 442, 'x'),
+  (97, 445, 't'), (85, 442, 'b'), (96, 445, 'l'), (84, 442, 'a'), (95, 445, 'k');
 
 SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx)
   WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
@@ -105,11 +107,8 @@ CREATE TABLE t1 (
 ) ENGINE=Aria;
 
 INSERT INTO t1 VALUES
-(1,'z'),
-(2,'abcdefjhjkl'),
-(3,'in'),
-(4,'abcdefjhjkl'),
-(6,'abcdefjhjkl');
+  (1,'z'), (2,'abcdefjhjkl'), (3,'in'), (4,'abcdefjhjkl'), (6,'abcdefjhjkl'),
+  (11,'zx'), (12,'abcdefjhjm'), (13,'jn'), (14,'abcdefjhjp'), (16,'abcdefjhjr');
 
 CREATE TABLE t2 (
   col_varchar_10_latin1 varchar(10) DEFAULT NULL
diff --git a/mysql-test/t/mrr_icp_extra.test b/mysql-test/t/mrr_icp_extra.test
index 2d0fd527dcf..f1b21154449 100644
--- a/mysql-test/t/mrr_icp_extra.test
+++ b/mysql-test/t/mrr_icp_extra.test
@@ -57,6 +57,7 @@ DROP TABLE t1,t2,t3;
 --echo #
 create table t1(a int, b int, index(b));
 insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
+insert into t1 values (2, 11), (1, 11), (4, 14), (3, 14), (6, 12), (5, 12);
 explain select * from t1 where b=1 or b is null order by a;
 select * from t1 where b=1 or b is null order by a;
 explain select * from t1 where b=2 or b is null order by a;
diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test
index e912eb754f4..2f3eff31ff5 100644
--- a/mysql-test/t/myisam.test
+++ b/mysql-test/t/myisam.test
@@ -350,7 +350,7 @@ DROP TABLE t1;
 #
 
 CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a));
-INSERT into t1 values (0, null, 0), (0, null, 1), (0, null, 2), (0, null,3), (1,1,4);
+INSERT into t1 values (0,null,0), (0,null,1), (0,null,2), (0,null,3), (1,1,4);
 create table t2 (a int not null, b int, c int, key(b), key(c), key(a));
 INSERT into t2 values (1,1,1), (2,2,2);
 optimize table t1;
@@ -358,7 +358,13 @@ show index from t1;
 explain select * from t1,t2 where t1.a=t2.a;
 explain select * from t1,t2 force index(a) where t1.a=t2.a;
 explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a;
+INSERT into t1 values (2,4,5), (7,8,4), (8,3,1), (9,7,2), (5,5,9);
+optimize table t1;
+show index from t1;
 explain select * from t1,t2 where t1.b=t2.b;
+delete from t1 where t1.a>1;
+optimize table t1;
+show index from t1;
 explain select * from t1,t2 force index(c) where t1.a=t2.a;
 explain select * from t1 where a=0 or a=2;
 explain select * from t1 force index (a) where a=0 or a=2;
diff --git a/mysql-test/t/myisam_mrr.test b/mysql-test/t/myisam_mrr.test
index 86024da6276..1e070ec9a34 100644
--- a/mysql-test/t/myisam_mrr.test
+++ b/mysql-test/t/myisam_mrr.test
@@ -199,6 +199,14 @@ INSERT INTO t1 VALUES
   (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');
+INSERT INTO t1 VALUES 
+  (110,8,'v','v'),(111,8,'f','f'), (112,5,'v','v'),
+  (113,8,'s','s'),(114,8,'a','a'),(115,6,'p','p'),
+  (116,7,'z','z'),(117,2,'a','a'),(118,5,'h','h'),
+  (119,7,'h','h'),(120,2,'v','v'),(121,9,'v','v'),
+  (122,142,'b','b'),(123,3,'y','y'),(124,0,'v','v'),
+  (125,3,'m','m'),(126,5,'z','z'),(127,9,'n','n'),
+  (128,1,'d','d'),(129,107,'a','a');
 
 SELECT COUNT(*) 
 FROM 
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 1b7d22cc677..ebcc762bd59 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -440,6 +440,7 @@ drop table t1;
 
 create table t1(a int, b int, index(b));
 insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
+insert into t1 values (12, 11), (11, 11), (14, 3), (13, 5), (16, 12), (15, 12);
 explain select * from t1 where b=1 or b is null order by a;
 select * from t1 where b=1 or b is null order by a;
 explain select * from t1 where b=2 or b is null order by a;
diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test
index db544d4643f..f1ec8964769 100644
--- a/mysql-test/t/partition_pruning.test
+++ b/mysql-test/t/partition_pruning.test
@@ -872,6 +872,7 @@ drop table t1;
 create table t1 (a int not null, b int not null, key(a), key(b))
   partition by hash(a) partitions 4;
 insert into t1 values (1,1),(2,2),(3,3),(4,4);
+insert into t1 values (5,5),(6,6),(7,7),(8,8);
 
 explain partitions 
 select * from t1 X, t1 Y 
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 0a34bac32ba..746d6bad896 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -554,6 +554,18 @@ INSERT INTO t1 VALUES
  'd8c4177d09f8b11f5.52725521'),
 ('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11,
  'd8c4177d09f8b11f5.52725521');
+INSERT INTO t1 VALUES
+('d8c4177d09f8b11f5.52725522','oxrootid',1,40,'d8c4177d09f8b11f5.52725522'),
+('d8c4177d151affab2.81582771','d8c4177d09f8b11f5.52725521',2,3,
+ 'd8c4177d09f8b11f5.52725522'),
+('d8c4177d206a333d2.74422678','d8c4177d09f8b11f5.52725521',4,5,
+ 'd8c4177d09f8b11f5.52725522'),
+('d8c4177d225791924.30714721','d8c4177d09f8b11f5.52725521',6,7,
+ 'd8c4177d09f8b11f5.52725522'),
+('d8c4177d2380fc201.39666694','d8c4177d09f8b11f5.52725521',8,9,
+ 'd8c4177d09f8b11f5.52725522'),
+('d8c4177d24ccef970.14957925','d8c4177d09f8b11f5.52725521',10,11,
+ 'd8c4177d09f8b11f5.52725522');
 
 EXPLAIN
 SELECT s.oxid FROM t1 v, t1 s 
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index d2447dae193..9fbfef53eb6 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -1961,6 +1961,7 @@ CREATE TABLE t2 ( a BLOB, INDEX (a(20)) );
 
 INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five');
 INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five');
+INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five');
 
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a;
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a;
diff --git a/mysql-test/t/select_safe.test b/mysql-test/t/select_safe.test
index 481779e76d7..5691de956cb 100644
--- a/mysql-test/t/select_safe.test
+++ b/mysql-test/t/select_safe.test
@@ -58,6 +58,8 @@ SELECT * from t1;
 #
 analyze table t1;
 insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
+insert into t1 values (null,"b"),(null,"b"),(null,"c"),(null,"c"),(null,"d"),(null,"d"),(null,"e"),(null,"e"),(null,"a"),(null,"e");
+insert into t1 values (null,"x"),(null,"x"),(null,"y"),(null,"y"),(null,"z"),(null,"z"),(null,"v"),(null,"v"),(null,"a"),(null,"v");
 explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
 set MAX_SEEKS_FOR_KEY=1;
 explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index 73ee11eb78d..f3faf44289e 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -81,6 +81,7 @@ insert into t1 values
   (2, 3),
   (2, NULL),
   (3, NULL);
+insert into t1 values (5, 7), (8, 9), (4, 1); 
 
 create table t2 (a int, oref int);
 insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4);
diff --git a/mysql-test/t/subselect_mat_cost.test b/mysql-test/t/subselect_mat_cost.test
index 8a0d1ac702d..bfe4a5bcebb 100644
--- a/mysql-test/t/subselect_mat_cost.test
+++ b/mysql-test/t/subselect_mat_cost.test
@@ -222,13 +222,15 @@ select count(*)
 from CountryLanguage
 where (Language, Country) NOT IN
       (SELECT City.Name, Country.Code
-       FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000));
+       FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000))
+      AND Language IN ('English','Spanish');
 
 select count(*)
 from CountryLanguage
 where (Language, Country) NOT IN
       (SELECT City.Name, Country.Code
-       FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000));
+       FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000))
+      AND Language IN ('English','Spanish');
 
 -- echo Q2.3m:
 -- echo MATERIALIZATION with the PARTIAL_MATCH_MERGE strategy, because the HAVING
diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test
index dbff9594309..0f20e2ac0a8 100644
--- a/mysql-test/t/subselect_mat_cost_bugs.test
+++ b/mysql-test/t/subselect_mat_cost_bugs.test
@@ -89,6 +89,12 @@ CREATE TABLE t1 (
 INSERT INTO t1 VALUES (10,7,8,'v','v');
 INSERT INTO t1 VALUES (11,1,9,'r','r');
 INSERT INTO t1 VALUES (12,5,9,'a','a');
+INSERT INTO t1 VALUES (13,7,18,'v','v');
+INSERT INTO t1 VALUES (14,1,19,'r','r');
+INSERT INTO t1 VALUES (15,5,29,'a','a');
+INSERT INTO t1 VALUES (17,7,38,'v','v');
+INSERT INTO t1 VALUES (18,1,39,'r','r');
+INSERT INTO t1 VALUES (19,5,49,'a','a');
 
 create table t1a like t1;
 insert into t1a select * from t1;
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index cc9518d9de7..80b712d19e1 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -1625,6 +1625,7 @@ set @tmp834739=@@optimizer_switch;
 set optimizer_switch='semijoin=on,loosescan=on,materialization=off,firstmatch=off';
 CREATE TABLE t2 ( b int, c int, KEY (b)) ;
 INSERT INTO t2 VALUES (1,0),(1,0),(9,0),(1,0),(5,0);
+INSERT INTO t2 VALUES (2,0),(3,0),(8,0),(6,0),(5,0);
 
 CREATE TABLE t3 ( a int);
 INSERT INTO t3 VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index ed96168a6c8..051c4e590f0 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -28,6 +28,7 @@ create table t2 (
   key(b)
 );
 insert into t2 select a, a/2 from t0;
+insert into t2 select a+10, a+10/2 from t0;
 
 select * from t1;
 select * from t2;
@@ -43,6 +44,8 @@ create table t3 (
    primary key(pk1, pk2, pk3)
 ) engine=innodb;
 insert into t3 select a,a, a,a,a from t0;
+insert into t3 select a,a, a+100,a+100,a+100 from t0;
+
 
 explain select * from t3 where b in (select a from t1);
 select * from t3 where b in (select a from t1);
@@ -575,6 +578,7 @@ insert into t0 values (0),(1),(2),(3),(4);
 
 create table t1 (a int, b int, key(a));
 insert into t1 select a,a from t0;
+insert into t1 select a+5,a from t0;
 
 create table t2 (a int, b int, primary key(a));
 insert into t2 select * from t1;
diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test
index 44ee52686b3..e475adbf9a8 100644
--- a/mysql-test/t/subselect_sj_jcl6.test
+++ b/mysql-test/t/subselect_sj_jcl6.test
@@ -22,6 +22,8 @@ CREATE TABLE t0 (a INT);
 INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
 CREATE TABLE t1 (a INT, b INT, KEY(a));
 INSERT INTO t1 SELECT a, a from t0;
+INSERT INTO t1 SELECT a+5, a from t0;
+INSERT INTO t1 SELECT a+10, a from t0;
 CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
 INSERT INTO t2 SELECT * FROM t1;
 UPDATE t1 SET a=3, b=11 WHERE a=4;
diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test
index 5576362b396..3d17c7f5513 100644
--- a/mysql-test/t/table_elim.test
+++ b/mysql-test/t/table_elim.test
@@ -223,7 +223,8 @@ create table t1 (a char(10) primary key);
 insert into t1 values ('foo'),('bar');
 
 create table t2 (a char(10), unique key(a(2)));
-insert into t2 values ('foo'),('bar');
+insert into t2 values
+  ('foo'),('bar'),('boo'),('car'),('coo'),('par'),('doo'),('tar');
 
 explain select t1.* from t1 left join t2 on t2.a=t1.a;