diff options
| author | Igor Babaev <igor@askmonty.org> | 2011-09-30 18:55:02 -0700 |
|---|---|---|
| committer | Igor Babaev <igor@askmonty.org> | 2011-09-30 18:55:02 -0700 |
| commit | 715dc5f99ddf852a5ef45bcb68c6c86298f6a7c3 (patch) | |
| tree | 3557e3bfdfa8723372daf13fa6f46ffdd30483b6 /mysql-test | |
| parent | 42b928ca05fca3ae8858566622a3a483a70aa3fc (diff) | |
| download | mariadb-git-715dc5f99ddf852a5ef45bcb68c6c86298f6a7c3.tar.gz | |
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.
Diffstat (limited to 'mysql-test')
60 files changed, 700 insertions, 215 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 5a1ae859423..dfeb90b44a0 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -739,15 +739,17 @@ INSERT INTO t1 VALUES ('c'); 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; EXPLAIN SELECT * FROM t1, t2, v1 WHERE t2.a=t1.a AND t2.a=v1.a AND t2.a=v1.b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ref a a 4 const 1 Using index -1 PRIMARY <derived2> ref key1 key1 10 test.t1.a,test.t1.a 2 -2 DERIVED t3 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort +1 PRIMARY <derived2> ref key0 key0 10 const,const 1 +2 DERIVED t3 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort SELECT * FROM t1, t2, v1 WHERE t2.a=t1.a AND t2.a=v1.a AND t2.a=v1.b; a a a b c c c c @@ -1014,7 +1016,9 @@ 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'); CREATE VIEW v1 AS SELECT DISTINCT t2.b FROM t1,t2,t3 WHERE t3.a = t2.a; diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 9d9e738b696..33ec3d1f39c 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -497,6 +497,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; id select_type table type possible_keys key key_len ref rows Extra @@ -512,15 +514,15 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b b 5 const 4 Using where +1 SIMPLE t1 ref b b 5 const 5 Using where EXPLAIN SELECT * FROM t1 USE INDEX(b) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b b 5 const 4 Using where +1 SIMPLE t1 ref b b 5 const 5 Using where EXPLAIN SELECT * FROM t1 FORCE INDEX(b) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b b 5 const 4 Using where +1 SIMPLE t1 ref b b 5 const 5 Using where DROP TABLE t1; create table t1(a text,b date,fulltext index(a))engine=myisam; insert into t1 set a='water',b='2008-08-04'; diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index b544772f383..333a2b82719 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -3037,12 +3037,12 @@ t1.metaid = t2.metaid AND t1.affiliateid = '2'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 system PRIMARY NULL NULL NULL 1 1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 1 -1 SIMPLE t4 ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 const 1 Using index condition; Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index -1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaidformatid 4 test.t1.metaid 1 Using index condition; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 eq_ref PRIMARY,t4_formatclassid,t4_formats_idx PRIMARY 4 test.t3.formatid 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer (incremental, BNL join) 1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan @@ -3151,6 +3151,8 @@ 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; Warnings: Warning 1292 Truncated incorrect join_buffer_size value: '32' @@ -3176,6 +3178,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; id select_type table type possible_keys key key_len ref rows Extra @@ -3566,6 +3569,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; explain select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 @@ -3613,14 +3619,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'); analyze table t2,t3,t4; set join_cache_level=1; explain @@ -3965,7 +3983,9 @@ DROP TABLE t1,t2,t3,t4; 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; id select_type table type possible_keys key key_len ref rows Extra @@ -4000,6 +4020,7 @@ CREATE TABLE t1 (b varchar(100)); 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; id select_type table type possible_keys key key_len ref rows Extra @@ -4114,6 +4135,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; CREATE TABLE t2 SELECT * FROM t3; @@ -4124,55 +4156,87 @@ WHERE t3.u <='a' AND t2.j < 5 AND t3.v = t2.u; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 16 1 SIMPLE t2 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 hash_ALL idx #hash#idx 3 test.t2.u 20 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t3 hash_ALL idx #hash#idx 3 test.t2.u 40 Using where; Using join buffer (flat, BNLH join) SELECT t1.i, t1.d, t1.v, t2.i, t2.d, t2.t, t2.v FROM t1,t2,t3 WHERE t3.u <='a' AND t2.j < 5 AND t3.v = t2.u; i d v i d t v +0 2008-06-04 p 1 2002-07-13 06:34:26 v +0 2008-06-04 p 4 2006-10-14 14:59:37 v 0 2008-06-04 p 5 1900-01-01 22:45:53 a 0 2008-06-04 p 5 1900-01-01 22:45:53 a 0 2008-06-04 p 5 1900-01-01 22:45:53 a +1 1900-01-01 a 1 2002-07-13 06:34:26 v +1 1900-01-01 a 4 2006-10-14 14:59:37 v 1 1900-01-01 a 5 1900-01-01 22:45:53 a 1 1900-01-01 a 5 1900-01-01 22:45:53 a 1 1900-01-01 a 5 1900-01-01 22:45:53 a +1 2002-07-13 v 1 2002-07-13 06:34:26 v +1 2002-07-13 v 4 2006-10-14 14:59:37 v 1 2002-07-13 v 5 1900-01-01 22:45:53 a 1 2002-07-13 v 5 1900-01-01 22:45:53 a 1 2002-07-13 v 5 1900-01-01 22:45:53 a +1 2006-04-02 z 1 2002-07-13 06:34:26 v +1 2006-04-02 z 4 2006-10-14 14:59:37 v 1 2006-04-02 z 5 1900-01-01 22:45:53 a 1 2006-04-02 z 5 1900-01-01 22:45:53 a 1 2006-04-02 z 5 1900-01-01 22:45:53 a +2 1900-01-01 v 1 2002-07-13 06:34:26 v +2 1900-01-01 v 4 2006-10-14 14:59:37 v 2 1900-01-01 v 5 1900-01-01 22:45:53 a 2 1900-01-01 v 5 1900-01-01 22:45:53 a 2 1900-01-01 v 5 1900-01-01 22:45:53 a +2 2009-10-02 s 1 2002-07-13 06:34:26 v +2 2009-10-02 s 4 2006-10-14 14:59:37 v 2 2009-10-02 s 5 1900-01-01 22:45:53 a 2 2009-10-02 s 5 1900-01-01 22:45:53 a 2 2009-10-02 s 5 1900-01-01 22:45:53 a +3 1900-01-01 v 1 2002-07-13 06:34:26 v +3 1900-01-01 v 4 2006-10-14 14:59:37 v 3 1900-01-01 v 5 1900-01-01 22:45:53 a 3 1900-01-01 v 5 1900-01-01 22:45:53 a 3 1900-01-01 v 5 1900-01-01 22:45:53 a +3 2006-10-19 n 1 2002-07-13 06:34:26 v +3 2006-10-19 n 4 2006-10-14 14:59:37 v 3 2006-10-19 n 5 1900-01-01 22:45:53 a 3 2006-10-19 n 5 1900-01-01 22:45:53 a 3 2006-10-19 n 5 1900-01-01 22:45:53 a +3 2008-12-04 v 1 2002-07-13 06:34:26 v +3 2008-12-04 v 4 2006-10-14 14:59:37 v 3 2008-12-04 v 5 1900-01-01 22:45:53 a 3 2008-12-04 v 5 1900-01-01 22:45:53 a 3 2008-12-04 v 5 1900-01-01 22:45:53 a +3 2009-03-28 f 1 2002-07-13 06:34:26 v +3 2009-03-28 f 4 2006-10-14 14:59:37 v 3 2009-03-28 f 5 1900-01-01 22:45:53 a 3 2009-03-28 f 5 1900-01-01 22:45:53 a 3 2009-03-28 f 5 1900-01-01 22:45:53 a +4 2006-10-14 v 1 2002-07-13 06:34:26 v +4 2006-10-14 v 4 2006-10-14 14:59:37 v 4 2006-10-14 v 5 1900-01-01 22:45:53 a 4 2006-10-14 v 5 1900-01-01 22:45:53 a 4 2006-10-14 v 5 1900-01-01 22:45:53 a +5 1900-01-01 a 1 2002-07-13 06:34:26 v +5 1900-01-01 a 4 2006-10-14 14:59:37 v 5 1900-01-01 a 5 1900-01-01 22:45:53 a 5 1900-01-01 a 5 1900-01-01 22:45:53 a 5 1900-01-01 a 5 1900-01-01 22:45:53 a +5 2004-09-18 h 1 2002-07-13 06:34:26 v +5 2004-09-18 h 4 2006-10-14 14:59:37 v 5 2004-09-18 h 5 1900-01-01 22:45:53 a 5 2004-09-18 h 5 1900-01-01 22:45:53 a 5 2004-09-18 h 5 1900-01-01 22:45:53 a +5 2008-04-04 y 1 2002-07-13 06:34:26 v +5 2008-04-04 y 4 2006-10-14 14:59:37 v 5 2008-04-04 y 5 1900-01-01 22:45:53 a 5 2008-04-04 y 5 1900-01-01 22:45:53 a 5 2008-04-04 y 5 1900-01-01 22:45:53 a +8 2005-06-08 d 1 2002-07-13 06:34:26 v +8 2005-06-08 d 4 2006-10-14 14:59:37 v 8 2005-06-08 d 5 1900-01-01 22:45:53 a 8 2005-06-08 d 5 1900-01-01 22:45:53 a 8 2005-06-08 d 5 1900-01-01 22:45:53 a +8 2009-01-13 z 1 2002-07-13 06:34:26 v +8 2009-01-13 z 4 2006-10-14 14:59:37 v 8 2009-01-13 z 5 1900-01-01 22:45:53 a 8 2009-01-13 z 5 1900-01-01 22:45:53 a 8 2009-01-13 z 5 1900-01-01 22:45:53 a @@ -4191,6 +4255,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, PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2(v,i) @@ -4216,7 +4288,7 @@ GROUP BY t2.v ORDER BY t1.pk,t2.v; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index idx1 idx2 7 NULL 20 Using index; Using temporary; Using filesort 1 SIMPLE t3 eq_ref PRIMARY,idx2 PRIMARY 4 test.t2.i 1 Using where -1 SIMPLE t1 ref idx2 idx2 3 test.t3.v 2 +1 SIMPLE t1 ref idx2 idx2 3 test.t3.v 5 SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v GROUP BY t2.v ORDER BY t1.pk,t2.v; v @@ -4224,7 +4296,13 @@ h z p n +d +f +s +a v +m +y 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 @@ -4232,15 +4310,21 @@ GROUP BY t2.v ORDER BY t1.pk,t2.v; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL idx1 NULL NULL NULL 20 Using temporary; Using filesort 1 SIMPLE t3 eq_ref PRIMARY,idx2 PRIMARY 4 test.t2.i 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t1 ref idx2 idx2 3 test.t3.v 2 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t1 ref idx2 idx2 3 test.t3.v 5 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v GROUP BY t2.v ORDER BY t1.pk,t2.v; v -h z +h n -v p +d +m +y +a +f +s +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 @@ -4248,7 +4332,7 @@ GROUP BY t2.v ORDER BY t1.pk,t2.v; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL idx1 NULL NULL NULL 20 Using temporary; Using filesort 1 SIMPLE t3 hash_ALL PRIMARY,idx2 #hash#PRIMARY 4 test.t2.i 20 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE t1 hash_ALL idx2 #hash#idx2 3 test.t3.v 15 Using join buffer (incremental, BNLH join) +1 SIMPLE t1 hash_ALL idx2 #hash#idx2 3 test.t3.v 45 Using join buffer (incremental, BNLH join) SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v GROUP BY t2.v ORDER BY t1.pk,t2.v; v @@ -4257,6 +4341,12 @@ z n v p +d +m +y +a +f +s DROP TABLE t1,t2,t3; SET SESSION join_cache_level=DEFAULT; # @@ -4320,13 +4410,18 @@ 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; EXPLAIN SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 36 Using where -1 SIMPLE t2 hash_ALL idx #hash#idx 10 test.t1.a,const 15 Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL idx #hash#idx 10 test.t1.a,const 30 Using join buffer (flat, BNLH join) SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99; a c SET SESSION join_cache_level = DEFAULT; @@ -4353,20 +4448,27 @@ 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; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where -1 SIMPLE t2 ref idx idx 13 test.t1.v 2 +1 SIMPLE t2 ref idx idx 13 test.t1.v 3 SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v; v i +abcdefjh 4 f 4 f 4 EXPLAIN SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 -1 SIMPLE t2 ref idx idx 13 func 2 Using index condition +1 SIMPLE t2 ref idx idx 13 func 3 Using index condition SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); v i f 5 @@ -4376,16 +4478,17 @@ EXPLAIN SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where -1 SIMPLE t2 hash_ALL idx #hash#idx 13 test.t1.v 18 Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL idx #hash#idx 13 test.t1.v 36 Using join buffer (flat, BNLH join) SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v; v i f 4 f 4 +abcdefjh 4 EXPLAIN SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 -1 SIMPLE t2 hash_ALL idx #hash#idx 13 func 18 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL idx #hash#idx 13 func 36 Using where; Using join buffer (flat, BNLH join) SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); v i f 5 @@ -4416,6 +4519,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, i int, @@ -4431,6 +4541,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; SET SESSION join_cache_level = 4; EXPLAIN @@ -4438,8 +4557,8 @@ SELECT t3.i FROM t1,t2,t3 WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index idx idx 13 NULL 7 Using where; Using index -1 SIMPLE t2 hash_ALL idx #hash#idx 1003 test.t1.v 18 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE t3 hash_ALL idx #hash#idx 1002 func 32 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t2 hash_ALL idx #hash#idx 1003 test.t1.v 36 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t3 hash_ALL idx #hash#idx 1002 func 64 Using where; Using join buffer (incremental, BNLH join) SELECT t3.i FROM t1,t2,t3 WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0; i @@ -4464,19 +4583,28 @@ 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; pk a pk a 2 aa 30 aa 2 aa 31 aa 2 aa 42 aa +2 aa 130 aa +2 aa 131 aa +2 aa 142 aa 3 bb 4 bb 3 bb 32 bb +3 bb 14 bb +3 bb 132 bb SET SESSION join_cache_level = 4; EXPLAIN SELECT * FROM t1,t2 WHERE t2.a=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL idx NULL NULL NULL 3 Using where -1 SIMPLE t2 hash_ALL idx #hash#idx 515 test.t1.a 12 Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL idx #hash#idx 515 test.t1.a 24 Using join buffer (flat, BNLH join) SELECT * FROM t1,t2 WHERE t2.a=t1.a; pk a pk a 2 aa 30 aa @@ -4484,6 +4612,11 @@ pk a pk a 2 aa 31 aa 3 bb 32 bb 2 aa 42 aa +2 aa 130 aa +3 bb 14 bb +2 aa 131 aa +3 bb 132 bb +2 aa 142 aa SET SESSION join_cache_level = DEFAULT; DROP TABLE t1,t2; # @@ -4566,6 +4699,9 @@ CREATE TABLE t3 (pk int, a3 int, c3 int, d3 int) ; 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); SET SESSION optimizer_switch = 'outer_join_with_cache=on'; @@ -4783,13 +4919,16 @@ 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; EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where -1 SIMPLE t2 hash_ALL idx #hash#idx 35 test.t1.a 15 Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL idx #hash#idx 35 test.t1.a 30 Using join buffer (flat, BNLH join) SELECT * FROM t1,t2 WHERE t1.a=t2.a; pk a pk a 20 BBBB 1 Bbbb @@ -4802,6 +4941,16 @@ pk a pk a 40 DD 12 dd 50 ee 13 EE 50 ee 14 ee +20 BBBB 101 Bbbb +20 BBBB 103 bbbb +10 AAA 104 AaA +30 Cc 105 CC +30 Cc 106 cC +10 AAA 108 AAA +20 BBBB 109 bBbB +40 DD 112 dd +50 ee 113 EE +50 ee 114 ee SET SESSION join_cache_level = DEFAULT; DROP TABLE t1,t2; # @@ -4926,13 +5075,15 @@ 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; EXPLAIN SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL f1 NULL NULL NULL 2 Using where -1 SIMPLE t2 hash_ALL f1 #hash#f1 13 test.t1.f1 12 Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL f1 #hash#f1 13 test.t1.f1 20 Using join buffer (flat, BNLH join) SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; f1 f2 f1 f2 SET SESSION join_cache_level = DEFAULT; @@ -4945,17 +5096,20 @@ 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; SET SESSION join_cache_level = 4; EXPLAIN SELECT a FROM t1,t2 WHERE t2.v = t1.v ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE t2 hash_ALL idx #hash#idx 4 test.t1.v 8 Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL idx #hash#idx 4 test.t1.v 16 Using join buffer (flat, BNLH join) SELECT a FROM t1,t2 WHERE t2.v = t1.v ; a 11 +18 SET SESSION join_cache_level = 1; EXPLAIN SELECT a FROM t1,t2 WHERE t2.v = t1.v ; @@ -4965,6 +5119,7 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT a FROM t1,t2 WHERE t2.v = t1.v ; a 11 +18 SET SESSION join_cache_level = DEFAULT; SET SESSION join_buffer_size = DEFAULT; DROP TABLE t1,t2; diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index bdf650b97bc..234ac4a88d9 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -841,25 +841,26 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`))) where (`test`.`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 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; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) 1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t3`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`b` is not null))) where 1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t2`.`a` > 0) and (`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t3`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`b` is not null))) where 1 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; a b a b a b 4 2 1 2 3 2 4 2 1 2 3 2 @@ -878,7 +879,7 @@ LEFT JOIN 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 ( @@ -907,16 +908,18 @@ t0.b=t1.b AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t2`.`a` > 0) and (`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +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); EXPLAIN EXTENDED @@ -928,7 +931,7 @@ LEFT JOIN 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 ( @@ -937,7 +940,7 @@ LEFT JOIN 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 @@ -957,16 +960,17 @@ t0.b=t1.b AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where +1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t2`.`a` > 0) and (`test`.`t4`.`a` > 0) and (`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t5`.`a` > 0) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`))) +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 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, @@ -977,16 +981,16 @@ LEFT JOIN 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 @@ -1006,16 +1010,17 @@ t0.b=t1.b AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where +1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t2`.`a` > 0) and (`test`.`t4`.`a` > 0) and (`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t8`.`a` >= 0) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t5`.`a` > 0) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`))) +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); EXPLAIN EXTENDED @@ -1040,7 +1045,7 @@ ON t6.b >= 2 AND t5.b=t7.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 @@ -1056,16 +1061,16 @@ t0.b=t1.b AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where 1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 100.00 -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where +1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2) and (`test`.`t1`.`a` > 0))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`))) SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 @@ -1087,7 +1092,7 @@ ON t6.b >= 2 AND t5.b=t7.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 @@ -1124,6 +1129,11 @@ a b 3 3 4 2 5 3 +-1 9 +-3 10 +-2 8 +-4 11 +-5 15 SELECT t3.a,t3.b FROM t3; a b diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result index d11955226ae..ff5a31b599b 100644 --- a/mysql-test/r/join_nested_jcl6.result +++ b/mysql-test/r/join_nested_jcl6.result @@ -850,25 +850,26 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where (`test`.`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 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; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) 1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t3`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`b` is not null))) where 1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t2`.`a` > 0) and (`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t3`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`b` is not null))) where 1 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; a b a b a b 4 2 1 2 3 2 4 2 1 2 4 2 @@ -887,7 +888,7 @@ LEFT JOIN 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 ( @@ -916,16 +917,18 @@ t0.b=t1.b AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t2`.`a` > 0) and (`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +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); EXPLAIN EXTENDED @@ -937,7 +940,7 @@ LEFT JOIN 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 ( @@ -946,7 +949,7 @@ LEFT JOIN 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 @@ -966,16 +969,17 @@ t0.b=t1.b AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t2`.`a` > 0) and (`test`.`t4`.`a` > 0) and (`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t5`.`a` > 0) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`))) +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 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, @@ -986,16 +990,16 @@ LEFT JOIN 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 @@ -1015,16 +1019,17 @@ t0.b=t1.b AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t2`.`a` > 0) and (`test`.`t4`.`a` > 0) and (`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t8`.`a` >= 0) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t5`.`a` > 0) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`))) +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); EXPLAIN EXTENDED @@ -1049,7 +1054,7 @@ ON t6.b >= 2 AND t5.b=t7.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 @@ -1065,16 +1070,16 @@ t0.b=t1.b AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where 1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 100.00 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t8 ref idx_b idx_b 5 test.t7.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t7`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t7`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2) and (`test`.`t1`.`a` > 0))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`))) SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 @@ -1096,7 +1101,7 @@ ON t6.b >= 2 AND t5.b=t7.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 @@ -1110,15 +1115,15 @@ t0.b=t1.b AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); a b a b a b a b a b a b a b a b a b a b -1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2 1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2 +1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2 1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1 -1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1 1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2 +1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1 1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 -1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 +1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2 1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1 @@ -1133,6 +1138,11 @@ a b 3 3 4 2 5 3 +-1 9 +-3 10 +-2 8 +-4 11 +-5 15 SELECT t3.a,t3.b FROM t3; a b @@ -1850,6 +1860,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 FROM t5 @@ -1858,14 +1871,14 @@ 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 ) 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; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 -1 SIMPLE t7 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t6 ALL b_i NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t7 ref PRIMARY,b_i b_i 5 test.t5.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t6 ALL PRIMARY,b_i NULL NULL NULL 7 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t8 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b FROM t5 @@ -1877,7 +1890,7 @@ t8 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; a b a b a b a b 2 2 1 2 2 2 1 2 2 2 3 2 2 2 1 2 diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 85470f772d2..03eaa7f78a0 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1478,6 +1478,8 @@ create table t2 like t1; 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; Table Op Msg_type Msg_text test.t1 analyze status OK diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index ff83c3bfd03..de24283a485 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1487,6 +1487,8 @@ create table t2 like t1; 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; Table Op Msg_type Msg_text test.t1 analyze status OK diff --git a/mysql-test/r/maria_mrr.result b/mysql-test/r/maria_mrr.result index 09679adc10a..de7a129e897 100644 --- a/mysql-test/r/maria_mrr.result +++ b/mysql-test/r/maria_mrr.result @@ -344,7 +344,9 @@ pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, 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; COUNT(t1.v) @@ -355,7 +357,7 @@ WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL idx 7 NULL 15 Using index 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 16 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 17 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 25 Using where; Using join buffer (flat, BNL join) SELECT COUNT(t1.v) FROM t1, t2, t3 WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; COUNT(t1.v) @@ -381,11 +383,8 @@ PRIMARY KEY (pk), KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key) ) 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 ) ENGINE=Aria; diff --git a/mysql-test/r/mrr_icp_extra.result b/mysql-test/r/mrr_icp_extra.result index 3014ec948f8..a4df3680dec 100644 --- a/mysql-test/r/mrr_icp_extra.result +++ b/mysql-test/r/mrr_icp_extra.result @@ -69,9 +69,10 @@ DROP TABLE t1,t2,t3; # 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort select * from t1 where b=1 or b is null order by a; a b 1 1 @@ -80,7 +81,7 @@ a b 4 NULL explain select * from t1 where b=2 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort select * from t1 where b=2 or b is null order by a; a b 3 NULL diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 1b61e286e53..e02c57ee8b9 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -329,7 +329,7 @@ ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e); ERROR 42000: Specified key was too long; max key length is 1000 bytes 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; @@ -355,10 +355,34 @@ explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL a NULL NULL NULL 2 1 SIMPLE t1 ref a a 4 test.t2.a 3 +INSERT into t1 values (2,4,5), (7,8,4), (8,3,1), (9,7,2), (5,5,9); +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 b 1 b A 10 NULL NULL YES BTREE +t1 1 c 1 c A 10 NULL NULL YES BTREE +t1 1 a 1 a A 10 NULL NULL BTREE +t1 1 a 2 b A 10 NULL NULL YES BTREE +t1 1 c_2 1 c A 10 NULL NULL YES BTREE +t1 1 c_2 2 a A 10 NULL NULL BTREE explain select * from t1,t2 where t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL b NULL NULL NULL 2 Using where 1 SIMPLE t1 ref b b 5 test.t2.b 1 +delete from t1 where t1.a>1; +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 b 1 b A 5 NULL NULL YES BTREE +t1 1 c 1 c A 5 NULL NULL YES BTREE +t1 1 a 1 a A 1 NULL NULL BTREE +t1 1 a 2 b A 5 NULL NULL YES BTREE +t1 1 c_2 1 c A 5 NULL NULL YES BTREE +t1 1 c_2 2 a A 5 NULL NULL BTREE explain select * from t1,t2 force index(c) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result index d6af5cd3e49..b7ff8981ef5 100644 --- a/mysql-test/r/myisam_mrr.result +++ b/mysql-test/r/myisam_mrr.result @@ -488,6 +488,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 t1 AS table2, t1 AS table3 @@ -496,7 +504,7 @@ table3.col_varchar_key = table2.col_varchar_key AND table3.col_varchar_key = table2.col_varchar_nokey AND table3.pk<>0; COUNT(*) -50 +200 EXPLAIN SELECT COUNT(*) FROM t1 AS table2, t1 AS table3 @@ -505,8 +513,8 @@ table3.col_varchar_key = table2.col_varchar_key AND table3.col_varchar_key = table2.col_varchar_nokey AND table3.pk<>0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE table2 ALL col_varchar_key NULL NULL NULL 20 Using where -1 SIMPLE table3 ref PRIMARY,col_varchar_key col_varchar_key 3 test.table2.col_varchar_key 3 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE table2 ALL col_varchar_key NULL NULL NULL 40 Using where +1 SIMPLE table3 ref PRIMARY,col_varchar_key col_varchar_key 3 test.table2.col_varchar_key 5 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan set join_cache_level= @save_join_cache_level; set join_buffer_size= @save_join_buffer_size; drop table t1; diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 5e6cdebbdd7..5d6b2a18003 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -642,9 +642,10 @@ id 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 3 Using where; Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 4 Using where; Using filesort select * from t1 where b=1 or b is null order by a; a b 1 1 @@ -653,7 +654,7 @@ a b 4 NULL explain select * from t1 where b=2 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 4 Using where; Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 3 Using where; Using filesort select * from t1 where b=2 or b is null order by a; a b 3 NULL @@ -1464,7 +1465,7 @@ INSERT INTO t1 VALUES (1, 10), (2, NULL); EXPLAIN SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a_c,a a_c 10 const,const 1 Using where +1 SIMPLE t1 ref_or_null a_c,a a_c 10 const,const 2 Using where; Using index; Using filesort # Must return 1 row SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; col diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index 75097b8d411..6d6371e263f 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -2340,16 +2340,17 @@ 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 where X.b = Y.b and (X.a=1 or X.a=2) and (Y.a=2 or Y.a=3); id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE X p1,p2 ALL a,b NULL NULL NULL 2 Using where +1 SIMPLE X p1,p2 range a,b a 4 NULL 4 Using where 1 SIMPLE Y p2,p3 ref a,b b 4 test.X.b 2 Using where explain partitions select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2); id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE X p1,p2 ALL a NULL NULL NULL 2 Using where +1 SIMPLE X p1,p2 range a a 4 NULL 4 Using where 1 SIMPLE Y p1,p2 ref a a 4 test.X.a 2 drop table t1; create table t1 (a int) partition by hash(a) partitions 20; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index de93b0eb8c7..6155a0ee433 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -698,6 +698,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 WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND @@ -705,7 +717,7 @@ v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using where -1 SIMPLE s ALL OXLEFT NULL NULL NULL 6 Range checked for each record (index map: 0x4) +1 SIMPLE s ALL OXLEFT NULL NULL NULL 12 Range checked for each record (index map: 0x4) SELECT s.oxid FROM t1 v, t1 s WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND v.oxrootid ='d8c4177d09f8b11f5.52725521' AND diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index fdd8c6ca7ba..cf6ce2b9d24 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -700,6 +700,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 WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND @@ -707,7 +719,7 @@ v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using index condition -1 SIMPLE s ALL OXLEFT NULL NULL NULL 6 Range checked for each record (index map: 0x4) +1 SIMPLE s ALL OXLEFT NULL NULL NULL 12 Range checked for each record (index map: 0x4) SELECT s.oxid FROM t1 v, t1 s WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND v.oxrootid ='d8c4177d09f8b11f5.52725521' AND diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 8f4d5eba1fe..fde22cb20fb 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2394,6 +2394,7 @@ CREATE TABLE t1 ( a BLOB, INDEX (a(20)) ); 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; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index 28f03867f25..9037b58858f 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -2403,6 +2403,7 @@ CREATE TABLE t1 ( a BLOB, INDEX (a(20)) ); 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; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index 8f4d5eba1fe..fde22cb20fb 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -2394,6 +2394,7 @@ CREATE TABLE t1 ( a BLOB, INDEX (a(20)) ); 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; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result index 26fb008c2a0..39ce1fd00dd 100644 --- a/mysql-test/r/select_safe.result +++ b/mysql-test/r/select_safe.result @@ -64,14 +64,16 @@ analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL b NULL NULL NULL 21 Using where +1 SIMPLE t1 ALL b NULL NULL NULL 41 Using where 1 SIMPLE t2 ref b b 21 test.t1.b 6 set MAX_SEEKS_FOR_KEY=1; explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL b NULL NULL NULL 21 Using where +1 SIMPLE t1 ALL b NULL NULL NULL 41 Using where 1 SIMPLE t2 ref b b 21 test.t1.b 6 SET MAX_SEEKS_FOR_KEY=DEFAULT; drop table t1; diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index 67b9ef0a2e9..a7b6e40d9c9 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -125,14 +125,14 @@ DOCID DOCNAME DOCTYPEID FOLDERID AUTHOR CREATED TITLE SUBTITLE DOCABSTRACT PUBLI c373e9f5ad07993f3859444553544200 Last Discussion c373e9f5ad079174ff17444553544200 c373e9f5ad0796c0eca4444553544200 Goldilocks 2003-06-09 11:21:06 Title: Last Discussion NULL Setting new abstract and keeping doc checked out 2003-06-09 10:51:26 2003-06-09 10:51:26 NULL NULL NULL 03eea05112b845949f3fd03278b5fe43 2003-06-09 11:21:06 admin 0 NULL Discussion NULL NULL EXPLAIN SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 const 6 Using index condition; Using where -1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 Using where -1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 Using where -1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 Using where -1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 Using where +1 PRIMARY t4 ALL PRIMARY NULL NULL NULL 10 1 PRIMARY t2 ALL DDOCTYPEID_IDX,DFOLDERID_IDX NULL NULL NULL 9 Using where; Using join buffer (flat, BNL join) 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1 -1 PRIMARY t4 eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1 +1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t2.FOLDERID 1 Using where +1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where +1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where +1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where +1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where drop table t1, t2, t3, t4; CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB; INSERT INTO t1 VALUES (1),(2); diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 4bb2e524861..1996994fa24 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -82,6 +82,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); select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; @@ -104,7 +105,7 @@ oref a 1 1 show status like '%Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 11 +Handler_read_rnd_next 14 delete from t2; insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); set optimizer_switch='subquery_cache=off'; @@ -122,7 +123,7 @@ Handler_read_key 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 -Handler_read_rnd_next 35 +Handler_read_rnd_next 50 select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; Z No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1. @@ -1135,9 +1136,9 @@ insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where -1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using where -1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t3) +1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; LooseScan +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) drop table t1, t3, t4; create table t1 (a int) as select * from t0 where a < 5; set @save_max_heap_table_size=@@max_heap_table_size; diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index 6e739ee983a..e9cf259dbb3 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -91,6 +91,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); select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; @@ -113,7 +114,7 @@ oref a 1 1 show status like '%Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 11 +Handler_read_rnd_next 14 delete from t2; insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); set optimizer_switch='subquery_cache=off'; @@ -131,7 +132,7 @@ Handler_read_key 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 -Handler_read_rnd_next 35 +Handler_read_rnd_next 50 select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; Z No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1. @@ -821,6 +822,8 @@ LEFT JOIN t1 AS INNR ON ( INNR2.int_key = INNR.int_key ) WHERE INNR.varchar_key > 'n{' ); varchar_nokey +NULL +p DROP TABLE t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (11); @@ -1144,9 +1147,9 @@ insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where -1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using where -1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t3) +1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Rowid-ordered scan; LooseScan +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) drop table t1, t3, t4; create table t1 (a int) as select * from t0 where a < 5; set @save_max_heap_table_size=@@max_heap_table_size; diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result index a642c498e53..5d645ed698d 100644 --- a/mysql-test/r/subselect_mat_cost.result +++ b/mysql-test/r/subselect_mat_cost.result @@ -343,18 +343,20 @@ 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'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY CountryLanguage index NULL PRIMARY 33 NULL 984 Using where; Using index +1 PRIMARY CountryLanguage range Language Language 30 NULL 72 Using index condition; Using where; Rowid-ordered scan 2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 2 Using index condition 2 DEPENDENT SUBQUERY Country eq_ref PRIMARY PRIMARY 3 world.City.Country 1 Using where; Using index 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'); count(*) -979 +88 Q2.3m: MATERIALIZATION with the PARTIAL_MATCH_MERGE strategy, because the HAVING clause prevents the use of the index on City(Name), and in practice reduces diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index 79f53c1ff86..1e613f298bd 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -64,6 +64,12 @@ KEY c3 (c3,c2)); 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; create table t1b like t1; @@ -88,13 +94,13 @@ FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEFT JOIN t1a ON (t1a.c2 = t1b.pk AND 2) WHERE t1.pk) ; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -2 DEPENDENT SUBQUERY t2 index c3 c3 9 NULL 2 100.00 Using index -2 DEPENDENT SUBQUERY t1b ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t1a ref c2 c2 5 test.t1b.pk 2 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 9 100.00 Using where +2 DEPENDENT SUBQUERY t1b ALL NULL NULL NULL NULL 9 100.00 +2 DEPENDENT SUBQUERY t1a ref c2 c2 5 test.t1b.pk 1 100.00 Using where +2 DEPENDENT SUBQUERY t2 index c3 c3 9 NULL 2 100.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on((2 and (`test`.`t1a`.`c2` = `test`.`t1b`.`pk`))) where ((`test`.`t1`.`pk` <> 0) and (<cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`) and (`test`.`t1b`.`c4` = `test`.`t2`.`c3`)))) +Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on((2 and (`test`.`t1a`.`c2` = `test`.`t1b`.`pk`))) where ((`test`.`t1`.`pk` <> 0) and (<cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`) and (`test`.`t2`.`c3` = `test`.`t1b`.`c4`)))) SELECT pk FROM t1 WHERE c1 IN diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index e0ec0072db9..c3200af01dd 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -1779,6 +1779,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); CREATE TABLE t4 ( a int); diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index e4e66ce01f2..155bffe4d73 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -16,6 +16,7 @@ b int, 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; a b 1 1 @@ -33,6 +34,16 @@ a b 7 4 8 4 9 5 +10 5 +11 6 +12 7 +13 8 +14 9 +15 10 +16 11 +17 12 +18 13 +19 14 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary @@ -51,6 +62,7 @@ pk1 char(200), pk2 char(200), pk3 char(200), 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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary @@ -58,6 +70,8 @@ id select_type table type possible_keys key key_len ref rows Extra select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1 +1 1 101 101 101 +2 2 102 102 102 2 2 2 2 2 set @save_max_heap_table_size= @@max_heap_table_size; set max_heap_table_size=16384; @@ -420,6 +434,7 @@ 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; create table t2 (a int, b int, primary key(a)); insert into t2 select * from t1; Table t2, unlike table t1, should be displayed as pulled out @@ -428,11 +443,11 @@ where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and t1.b=t2.b); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary -1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End temporary +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Using where; FirstMatch(t2) Warnings: Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t0`.`a`) and (`test`.`t2`.`a` = `test`.`t0`.`a`)) +Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where ((`test`.`t1`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`a` = `test`.`t0`.`a`) and (`test`.`t1`.`a` = `test`.`t0`.`a`)) update t1 set a=3, b=11 where a=4; update t2 set b=11 where a=3; select * from t0 where t0.a in diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 234fd625886..3b98dc8b34f 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -25,6 +25,7 @@ b int, 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; a b 1 1 @@ -42,6 +43,16 @@ a b 7 4 8 4 9 5 +10 5 +11 6 +12 7 +13 8 +14 9 +15 10 +16 11 +17 12 +18 13 +19 14 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary @@ -60,6 +71,7 @@ pk1 char(200), pk2 char(200), pk3 char(200), 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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary @@ -67,6 +79,8 @@ id select_type table type possible_keys key key_len ref rows Extra select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1 +1 1 101 101 101 +2 2 102 102 102 2 2 2 2 2 set @save_max_heap_table_size= @@max_heap_table_size; set max_heap_table_size=16384; @@ -429,6 +443,7 @@ 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; create table t2 (a int, b int, primary key(a)); insert into t2 select * from t1; Table t2, unlike table t1, should be displayed as pulled out @@ -436,12 +451,12 @@ explain extended select * from t0 where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and t1.b=t2.b); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End temporary; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Using where; FirstMatch(t2); Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan Warnings: Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t0`.`a`) and (`test`.`t2`.`a` = `test`.`t0`.`a`)) +Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where ((`test`.`t1`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`a` = `test`.`t0`.`a`) and (`test`.`t1`.`a` = `test`.`t0`.`a`)) update t1 set a=3, b=11 where a=4; update t2 set b=11 where a=3; # Not anymore: diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 3541653a863..0b84c99e3e7 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -18,6 +18,7 @@ b int, 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; a b 1 1 @@ -35,6 +36,16 @@ a b 7 4 8 4 9 5 +10 5 +11 6 +12 7 +13 8 +14 9 +15 10 +16 11 +17 12 +18 13 +19 14 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 @@ -54,14 +65,17 @@ pk1 char(200), pk2 char(200), pk3 char(200), 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); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL b NULL NULL NULL 10 +1 PRIMARY t3 ALL b NULL NULL NULL 20 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1 +1 1 101 101 101 +2 2 102 102 102 2 2 2 2 2 set @save_max_heap_table_size= @@max_heap_table_size; set max_heap_table_size=16384; @@ -106,9 +120,8 @@ set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 -2 SUBQUERY t2 index b b 5 NULL 10 Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t2 ref b b 5 test.t1.a 2 Using index; FirstMatch(t1) select * from t1; a b 1 1 @@ -432,6 +445,7 @@ 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; create table t2 (a int, b int, primary key(a)); insert into t2 select * from t1; Table t2, unlike table t1, should be displayed as pulled out @@ -440,11 +454,11 @@ where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and t1.b=t2.b); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary -1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End temporary +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Using where; FirstMatch(t2) Warnings: Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t0`.`a`) and (`test`.`t2`.`a` = `test`.`t0`.`a`)) +Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where ((`test`.`t1`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`a` = `test`.`t0`.`a`) and (`test`.`t1`.`a` = `test`.`t0`.`a`)) update t1 set a=3, b=11 where a=4; update t2 set b=11 where a=3; select * from t0 where t0.a in diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 4d0464d1bf5..b5fbf42ccf9 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -1790,6 +1790,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); CREATE TABLE t4 ( a int); @@ -1922,6 +1923,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/r/table_elim.result b/mysql-test/r/table_elim.result index 3ad02914ca8..b4d3a2f6f2f 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -272,7 +272,8 @@ drop table t1, t2; 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; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index diff --git a/mysql-test/suite/maria/r/maria.result b/mysql-test/suite/maria/r/maria.result index 077cf58f41f..4ff0b57a230 100644 --- a/mysql-test/suite/maria/r/maria.result +++ b/mysql-test/suite/maria/r/maria.result @@ -361,7 +361,8 @@ ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e); ERROR 42000: Specified key was too long; max key length is 1208 bytes 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); +INSERT into t1 values (2,4,5), (7,8,4), (8,3,1), (9,7,2), (5,5,9); 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; @@ -372,24 +373,24 @@ Table Op Msg_type Msg_text test.t1 check status OK show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 b 1 b A 5 NULL NULL YES BTREE -t1 1 c 1 c A 5 NULL NULL YES BTREE -t1 1 a 1 a A 1 NULL NULL BTREE -t1 1 a 2 b A 5 NULL NULL YES BTREE -t1 1 c_2 1 c A 5 NULL NULL YES BTREE -t1 1 c_2 2 a A 5 NULL NULL BTREE +t1 1 b 1 b A 10 NULL NULL YES BTREE +t1 1 c 1 c A 10 NULL NULL YES BTREE +t1 1 a 1 a A 10 NULL NULL BTREE +t1 1 a 2 b A 10 NULL NULL YES BTREE +t1 1 c_2 1 c A 10 NULL NULL YES BTREE +t1 1 c_2 2 a A 10 NULL NULL BTREE explain select * from t1,t2 where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL a NULL NULL NULL 2 -1 SIMPLE t1 ref a a 4 test.t2.a 3 +1 SIMPLE t1 ref a a 4 test.t2.a 1 explain select * from t1,t2 force index(a) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL a NULL NULL NULL 2 -1 SIMPLE t1 ref a a 4 test.t2.a 3 +1 SIMPLE t1 ref a a 4 test.t2.a 1 explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL a NULL NULL NULL 2 -1 SIMPLE t1 ref a a 4 test.t2.a 3 +1 SIMPLE t1 ref a a 4 test.t2.a 1 explain select * from t1,t2 where t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL b NULL NULL NULL 2 Using where @@ -397,19 +398,19 @@ id select_type table type possible_keys key key_len ref rows Extra explain select * from t1,t2 force index(c) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 -1 SIMPLE t1 ref a a 4 test.t2.a 3 +1 SIMPLE t1 ref a a 4 test.t2.a 1 explain select * from t1 where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 4 NULL 4 Using where +1 SIMPLE t1 range a a 4 NULL 5 Using where explain select * from t1 force index (a) where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 4 NULL 4 Using where +1 SIMPLE t1 range a a 4 NULL 5 Using where explain select * from t1 where c=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c,c_2 c 5 const 1 +1 SIMPLE t1 ref c,c_2 c 5 const 2 explain select * from t1 use index() where c=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where drop table t1,t2; create table t1 (a int not null auto_increment primary key, b varchar(255)); insert into t1 (b) values (repeat('a',100)),(repeat('b',100)),(repeat('c',100)); diff --git a/mysql-test/suite/maria/t/maria.test b/mysql-test/suite/maria/t/maria.test index 40738525372..ebeedbb23cc 100644 --- a/mysql-test/suite/maria/t/maria.test +++ b/mysql-test/suite/maria/t/maria.test @@ -386,7 +386,8 @@ 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); +INSERT into t1 values (2,4,5), (7,8,4), (8,3,1), (9,7,2), (5,5,9); 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; diff --git a/mysql-test/suite/pbxt/r/join_nested.result b/mysql-test/suite/pbxt/r/join_nested.result index b4edf18f2f5..1a195cb13a8 100644 --- a/mysql-test/suite/pbxt/r/join_nested.result +++ b/mysql-test/suite/pbxt/r/join_nested.result @@ -1017,6 +1017,7 @@ id select_type table type possible_keys key key_len ref rows Extra ATTENTION: the above EXPLAIN has several competing QEPs with identical . costs. To combat the plan change it uses --sorted_result and . and --replace tricks +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); EXPLAIN @@ -1041,7 +1042,7 @@ ON t6.b >= 2 AND t5.b=t7.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 @@ -1089,7 +1090,7 @@ ON t6.b >= 2 AND t5.b=t7.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/suite/pbxt/r/key_diff.result b/mysql-test/suite/pbxt/r/key_diff.result index 33bedfcc39e..99bc7e7f8ad 100644 --- a/mysql-test/suite/pbxt/r/key_diff.result +++ b/mysql-test/suite/pbxt/r/key_diff.result @@ -6,7 +6,12 @@ KEY (a), KEY (b) ); INSERT INTO t1 VALUES ('A','B'),('b','A'),('C','c'),('D','E'),('a','a'); -select * from t1,t1 as t2; +INSERT INTO t1 VALUES +('AA','BB'),('bb','AA'),('CC','cc'),('DD','EE'),('aa','aa'); +INSERT INTO t1 VALUES +('AAA','BBB'),('bbb','AAA'),('CCC','ccc'),('DDD','EEE'),('aaa','aaa'); +select * from t1,t1 as t2 +where length(t1.A)=1 and length(t2.B)=1 ; a b a b A B A B b A A B @@ -33,11 +38,14 @@ b A a a C c a a D E a a a a a a -explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B; +explain select t1.*,t2.* from t1,t1 as t2 +where t1.A=t2.B and length(t1.A)=1 and length(t2.B)=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL a NULL NULL NULL 5 +1 SIMPLE t1 ALL a NULL NULL NULL 15 Using where 1 SIMPLE t2 ref b b 4 test.t1.a 1 Using where -select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a; +select t1.*,t2.* from t1,t1 as t2 +where t1.A=t2.B and length(t1.A)=1 and length(t2.B)=1 +order by binary t1.a,t2.a; a b a b A B a a A B b A diff --git a/mysql-test/suite/pbxt/t/join_nested.test b/mysql-test/suite/pbxt/t/join_nested.test index 3b72dc1e293..98ffcbc9a8a 100644 --- a/mysql-test/suite/pbxt/t/join_nested.test +++ b/mysql-test/suite/pbxt/t/join_nested.test @@ -587,6 +587,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, --echo . costs. To combat the plan change it uses --sorted_result and --echo . and --replace tricks +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); @@ -614,7 +615,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 @@ -653,7 +654,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/suite/pbxt/t/key_diff.test b/mysql-test/suite/pbxt/t/key_diff.test index 5e9d7bac9cc..fc93c7418ab 100644 --- a/mysql-test/suite/pbxt/t/key_diff.test +++ b/mysql-test/suite/pbxt/t/key_diff.test @@ -13,11 +13,19 @@ CREATE TABLE t1 ( ); INSERT INTO t1 VALUES ('A','B'),('b','A'),('C','c'),('D','E'),('a','a'); +INSERT INTO t1 VALUES + ('AA','BB'),('bb','AA'),('CC','cc'),('DD','EE'),('aa','aa'); +INSERT INTO t1 VALUES + ('AAA','BBB'),('bbb','AAA'),('CCC','ccc'),('DDD','EEE'),('aaa','aaa'); -select * from t1,t1 as t2; -explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B; +select * from t1,t1 as t2 + where length(t1.A)=1 and length(t2.B)=1 ; +explain select t1.*,t2.* from t1,t1 as t2 + where t1.A=t2.B and length(t1.A)=1 and length(t2.B)=1; #select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B; -select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a; +select t1.*,t2.* from t1,t1 as t2 + where t1.A=t2.B and length(t1.A)=1 and length(t2.B)=1 +order by binary t1.a,t2.a; select * from t1 where a='a'; drop table t1; diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result index 58bd048ec85..693ea0d9174 100644 --- a/mysql-test/suite/vcol/r/vcol_misc.result +++ b/mysql-test/suite/vcol/r/vcol_misc.result @@ -139,6 +139,9 @@ create table t2 (c int, d int, v int as (d+1), index idx(c)); insert into t2(c,d) values (20, 100), (20, 300), (30, 100), (30, 200), (40, 500), (70, 100), (40, 300), (60, 100), (40, 100), (70, 100); +insert into t2(c,d) values +(120, 100), (150, 300), (130, 100), (130, 200), (140, 500), +(170, 100), (180, 300), (160, 100), (40, 100), (170, 100); set join_cache_level=6; explain select * from t1,t2 where t1.b=t2.c and d <= 100; diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test index a0616d187da..f87cb5fbec8 100644 --- a/mysql-test/suite/vcol/t/vcol_misc.test +++ b/mysql-test/suite/vcol/t/vcol_misc.test @@ -151,6 +151,9 @@ create table t2 (c int, d int, v int as (d+1), index idx(c)); insert into t2(c,d) values (20, 100), (20, 300), (30, 100), (30, 200), (40, 500), (70, 100), (40, 300), (60, 100), (40, 100), (70, 100); +insert into t2(c,d) values + (120, 100), (150, 300), (130, 100), (130, 200), (140, 500), + (170, 100), (180, 300), (160, 100), (40, 100), (170, 100); set join_cache_level=6; explain 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; |