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.

1 files changed, 87 insertions, 5 deletions

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;