summaryrefslogtreecommitdiff
path: root/mysql-test/t/join_cache.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t/join_cache.test')
-rw-r--r--mysql-test/t/join_cache.test143
1 files changed, 130 insertions, 13 deletions
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index ca0d10c3ee1..caf5af57df7 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;
@@ -2425,7 +2457,15 @@ CREATE TABLE t1 (
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');
+ (25,3,'m'), (26,5,'a'), (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,
@@ -2435,7 +2475,7 @@ INSERT INTO t2 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'),
(20,2,'v'), (21,9,'v'), (22,142,'b'), (23,3,'y'), (24,0,'v'),
- (25,3,'m'), (26,5,'z'), (27,9,'n'), (28,1,'d'), (29,107,'a');
+ (25,3,'m'), (26,5,'b'), (27,9,'n'), (28,1,'d'), (29,107,'a');
CREATE TABLE t3 (
pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
@@ -2449,23 +2489,29 @@ INSERT INTO t3 VALUES
SET SESSION join_cache_level=1;
EXPLAIN
-SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v
+SELECT t2.v FROM t1, t2, t3
+WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
GROUP BY t2.v ORDER BY t1.pk,t2.v;
-SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v
+SELECT t2.v FROM t1, t2, t3
+WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
GROUP BY t2.v ORDER BY t1.pk,t2.v;
SET SESSION join_cache_level=6;
EXPLAIN
-SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v
+SELECT t2.v FROM t1, t2, t3
+WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
GROUP BY t2.v ORDER BY t1.pk,t2.v;
-SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v
+SELECT t2.v FROM t1, t2, t3
+WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
GROUP BY t2.v ORDER BY t1.pk,t2.v;
SET SESSION join_cache_level=4;
EXPLAIN
-SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v
+SELECT t2.v FROM t1, t2, t3
+WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
GROUP BY t2.v ORDER BY t1.pk,t2.v;
-SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v
+SELECT t2.v FROM t1, t2, t3
+WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
GROUP BY t2.v ORDER BY t1.pk,t2.v;
DROP TABLE t1,t2,t3;
@@ -2531,6 +2577,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 = 256;
@@ -2567,6 +2618,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 +2671,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 +2694,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 = 256;
@@ -2665,6 +2738,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 +2843,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 +3021,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 +3146,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 +3171,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 = 256;
@@ -3137,5 +3225,34 @@ SET SESSION join_cache_level = DEFAULT;
DROP TABLE t1, t2;
+--echo #
+--echo # Bug #887479: join_cache_level=3 + semijoin=on
+--echo #
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (3914,17), (3710,5), (3888,20);
+
+CREATE TABLE t2 (c int, KEY (c));
+INSERT INTO t2 VALUES (27), (17), (33), (20), (3), (7), (18), (2);
+
+SET @tmp887479_optimizer_switch=@@optimizer_switch;
+SET SESSION optimizer_switch='semijoin=on';
+SET SESSION optimizer_switch='semijoin_with_cache=on';
+
+SET SESSION join_cache_level=1;
+EXPLAIN
+SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2);
+SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2);
+
+SET SESSION join_cache_level=3;
+EXPLAIN
+SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2);
+SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2);
+
+SET SESSION join_cache_level = DEFAULT;
+SET optimizer_switch=@tmp887479_optimizer_switch;
+
+DROP TABLE t1,t2;
+
# this must be the last command in the file
set @@optimizer_switch=@save_optimizer_switch;
View Lorry Controller Status