diff options
Diffstat (limited to 'mysql-test/r')
42 files changed, 1154 insertions, 357 deletions
diff --git a/mysql-test/r/compress.result b/mysql-test/r/compress.result index 1d3752f09e7..287691448bb 100644 --- a/mysql-test/r/compress.result +++ b/mysql-test/r/compress.result @@ -189,37 +189,37 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; fld1 fld3 -218401 faithful +012001 flanking +013602 foldout +013606 fingerings 018007 fanatic -228311 fated 018017 featherweight -218022 feed -088303 feminine -058004 Fenton -038017 fetched 018054 fetters -208101 fiftieth -238007 filial -013606 fingerings -218008 finishers -038205 firearm -188505 fitting -202301 Fitzpatrick -238008 fixedly -012001 flanking 018103 flint 018104 flopping +036002 funereal +038017 fetched +038205 firearm +058004 Fenton +088303 feminine +186002 freakish 188007 flurried -013602 foldout +188505 fitting +198006 furthermore +202301 Fitzpatrick +208101 fiftieth +208113 freest +218008 finishers +218022 feed +218401 faithful 226205 foothill -232102 forgivably +226209 furnishings 228306 forthcoming -186002 freakish -208113 freest +228311 fated 231315 freezes -036002 funereal -226209 furnishings -198006 furthermore +232102 forgivably +238007 filial +238008 fixedly select fld3 from t2 where fld3 like "L%" and fld3 = "ok"; fld3 select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly"); @@ -1395,15 +1395,15 @@ id select_type table type possible_keys key key_len ref rows Extra explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 @@ -1419,15 +1419,15 @@ id select_type table type possible_keys key key_len ref rows Extra explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 88e4bade130..d33d03332a3 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -104,7 +104,7 @@ insert into t1 (b) values ("hello"),("my"),("world"); create table t2 (key (b)) select * from t1; explain select * from t2 where b="world"; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref B B 21 const 1 Using where +1 SIMPLE t2 ref B B 21 const 1 Using index condition select * from t2 where b="world"; a B 3 world diff --git a/mysql-test/r/ctype_collate.result b/mysql-test/r/ctype_collate.result index b42094550bd..51e3cb779e2 100644 --- a/mysql-test/r/ctype_collate.result +++ b/mysql-test/r/ctype_collate.result @@ -566,31 +566,31 @@ INSERT INTO t1 VALUES ('i','i'); INSERT INTO t1 VALUES ('j','j'); EXPLAIN SELECT * FROM t1 WHERE s1='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref s1 s1 11 const 1 Using where +1 SIMPLE t1 ref s1 s1 11 const 1 Using index condition EXPLAIN SELECT * FROM t1 WHERE s2='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref s2 s2 11 const 1 Using where +1 SIMPLE t1 ref s2 s2 11 const 1 Using index condition EXPLAIN SELECT * FROM t1 WHERE s1='a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref s1 s1 11 const 1 Using where +1 SIMPLE t1 ref s1 s1 11 const 1 Using index condition EXPLAIN SELECT * FROM t1 WHERE s2='a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where EXPLAIN SELECT * FROM t1 WHERE s1 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range s1 s1 11 NULL 2 Using where +1 SIMPLE t1 range s1 s1 11 NULL 2 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE s2 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where EXPLAIN SELECT * FROM t1 WHERE s1 IN ('a','b' COLLATE latin1_german1_ci); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range s1 s1 11 NULL 2 Using where +1 SIMPLE t1 range s1 s1 11 NULL 2 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE s2 IN ('a','b' COLLATE latin1_german1_ci); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where EXPLAIN SELECT * FROM t1 WHERE s1 LIKE 'a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range s1 s1 11 NULL 1 Using where +1 SIMPLE t1 range s1 s1 11 NULL 1 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE s2 LIKE 'a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result index 47797af3cbe..0ebff29fb98 100644 --- a/mysql-test/r/ctype_cp1251.result +++ b/mysql-test/r/ctype_cp1251.result @@ -65,8 +65,8 @@ insert into t1 (a) values ('air'), ('tn_fakira'),('vw_silvia'),('vw_starshi'),('vw_geo'),('vw_b0x1'); select * from t1 where a like 'we_%'; a b -we_iliyan NULL +we_toshko NULL we_ivo NULL +we_iliyan NULL we_martin NULL -we_toshko NULL drop table t1; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index e0324af8cfd..4febf4638b0 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -174,7 +174,7 @@ INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2'); explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 4 Using temporary -1 SIMPLE t3 ref a a 5 test.t1.b 2 Using where; Using index +1 SIMPLE t3 ref a a 5 test.t1.b 2 Using index 1 SIMPLE t2 index a a 4 NULL 5 Using where; Using index; Distinct; Using join buffer SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; a @@ -190,7 +190,7 @@ insert into t3 select * from t4; explain select distinct t1.a from t1,t3 where t1.a=t3.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary -1 SIMPLE t3 ref a a 5 test.t1.a 11 Using where; Using index; Distinct +1 SIMPLE t3 ref a a 5 test.t1.a 11 Using index; Distinct select distinct t1.a from t1,t3 where t1.a=t3.a; a 1 diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index a81ff554ca2..336f44356b4 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -13,7 +13,7 @@ id str 3 foo explain select * from t1 where str is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref str str 11 const 1 Using where +1 SIMPLE t1 ref str str 11 const 1 Using index condition explain select * from t1 where str="foo"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const str str 11 const 1 @@ -50,7 +50,7 @@ insert into ÔÁÂ (ËÏÌ0) values (1); insert into ÔÁÂ (ËÏÌ0) values (2); explain select ËÏÌ0 from ÔÁÂ where ËÏÌ0=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE ÔÁÂ ref ÉÎÄ0,ÉÎÄ01 ÉÎÄ0 5 const 1 Using where; Using index +1 SIMPLE ÔÁÂ ref ÉÎÄ0,ÉÎÄ01 ÉÎÄ0 5 const 1 Using index drop table ÔÁÂ; set names latin1; select 3 into @v1; diff --git a/mysql-test/r/func_concat.result b/mysql-test/r/func_concat.result index 75b4888fbb2..b2b5a956fac 100644 --- a/mysql-test/r/func_concat.result +++ b/mysql-test/r/func_concat.result @@ -117,6 +117,6 @@ EXPLAIN SELECT CONCAT('gui_', t2.a), t1.d FROM t2 LEFT JOIN t1 ON t1.a = CONCAT('gui_', t2.a) AND t1.b = 'a' AND t1.c = 'b'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL PRIMARY 102 NULL 3 Using index -1 SIMPLE t1 eq_ref PRIMARY,a PRIMARY 318 func,const,const 1 +1 SIMPLE t1 eq_ref PRIMARY,a PRIMARY 318 func,const,const 1 Using where DROP TABLE t1, t2; # End of 5.1 tests diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 3d989ad1730..189efc1d4bf 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1333,7 +1333,7 @@ CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) ); INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 ); EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 5 const 2 Using where +1 SIMPLE t2 ref a a 5 const 2 SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1; MIN(b) MIN(c) 3 2 diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 88a822a2fa6..6e0a3d98e86 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -241,7 +241,7 @@ insert into t2 select C.a*2+1, 'yes' from t1 C; explain select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 5 NULL 12 Using where +1 SIMPLE t2 range a a 5 NULL 12 Using index condition; Using MRR select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18); a filler 1 yes @@ -256,10 +256,10 @@ a filler 19 yes explain select * from t2 force index(a) where a NOT IN (2,2,2,2,2,2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 5 NULL 912 Using where +1 SIMPLE t2 range a a 5 NULL 912 Using index condition; Using MRR explain select * from t2 force index(a) where a <> 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 5 NULL 912 Using where +1 SIMPLE t2 range a a 5 NULL 912 Using index condition; Using MRR drop table t2; create table t2 (a datetime, filler char(200), key(a)); insert into t2 select '2006-04-25 10:00:00' + interval C.a minute, @@ -271,7 +271,7 @@ select * from t2 where a NOT IN ( '2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', '2006-04-25 10:06:00', '2006-04-25 10:08:00'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 9 NULL 18 Using where +1 SIMPLE t2 range a a 9 NULL 18 Using index condition; Using MRR select * from t2 where a NOT IN ( '2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', '2006-04-25 10:06:00', '2006-04-25 10:08:00'); @@ -295,7 +295,7 @@ insert into t2 values ('fon', '1'), ('fop','1'), ('barbaq','1'), ('barbas','1'), ('bazbazbay', '1'),('zz','1'); explain select * from t2 where a not in('foo','barbar', 'bazbazbaz'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 13 NULL 7 Using where +1 SIMPLE t2 range a a 13 NULL 7 Using index condition; Using MRR drop table t2; create table t2 (a decimal(10,5), filler char(200), key(a)); insert into t2 select 345.67890, 'no' from t1 A, t1 B; @@ -306,7 +306,7 @@ insert into t2 values (0, '1'), (22334.123,'1'), (33333,'1'), explain select * from t2 where a not in (345.67890, 43245.34, 64224.56344); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 7 NULL 7 Using where +1 SIMPLE t2 range a a 7 NULL 7 Using index condition; Using MRR select * from t2 where a not in (345.67890, 43245.34, 64224.56344); a filler 0.00000 1 diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result index ddf675e2f73..fe0f5f39aca 100644 --- a/mysql-test/r/heap.result +++ b/mysql-test/r/heap.result @@ -204,7 +204,7 @@ key a (a) INSERT INTO t1 VALUES (10), (10), (10); EXPLAIN SELECT * FROM t1 WHERE a=10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 5 const 3 Using where +1 SIMPLE t1 ref a a 5 const 3 SELECT * FROM t1 WHERE a=10; a 10 diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index c639b20de91..0eecf24df14 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -19,7 +19,7 @@ Table Op Msg_type Msg_text test.t0 analyze status OK explain select * from t0 where key1 < 3 or key1 > 1020; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 range i1 i1 4 NULL 78 Using where +1 SIMPLE t0 range i1 i1 4 NULL 78 Using index condition; Using MRR explain select * from t0 where key1 < 3 or key2 > 1020; id select_type table type possible_keys key key_len ref rows Extra @@ -72,7 +72,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 17 Using sort_union(i1,i2); Using where explain select * from t0 where key2 = 45 or key1 <=> null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 range i1,i2 i2 4 NULL 1 Using where +1 SIMPLE t0 range i1,i2 i2 4 NULL 1 Using where; Using MRR explain select * from t0 where key2 = 45 or key1 is not null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where @@ -115,7 +115,7 @@ id select_type table type possible_keys key key_len ref rows Extra explain select * from t0 where (key1 < 3 or key2 < 3) and (key3 < 100); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 range i1,i2,i3 i3 4 NULL 95 Using where +1 SIMPLE t0 range i1,i2,i3 i3 4 NULL 95 Using index condition; Using where; Using MRR explain select * from t0 where (key1 < 3 or key2 < 3) and (key3 < 1000); id select_type table type possible_keys key key_len ref rows Extra @@ -275,7 +275,7 @@ id select_type table type possible_keys key key_len ref rows Extra explain select * from t0,t1 where t0.key1 < 3 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 range i1 i1 4 NULL 3 Using where +1 SIMPLE t0 range i1 i1 4 NULL 3 Using index condition; Using MRR 1 SIMPLE t1 ALL i1,i8 NULL NULL NULL 1024 Range checked for each record (index map: 0x81) explain select * from t1 where key1=3 or key2=4 union select * from t1 where key1<4 or key3=5; @@ -1326,19 +1326,19 @@ primary key (pk1, pk2) ); explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 7 Using where +1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 7 Using index condition; Using where; Using MRR select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2 -1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 -1 11 0 0 0 0 0 0 0 11 0 filler-data-11 filler2 -1 12 0 0 0 0 0 0 0 12 0 filler-data-12 filler2 -1 13 0 0 0 0 0 0 0 13 0 filler-data-13 filler2 -1 14 0 0 0 0 0 0 0 14 0 filler-data-14 filler2 -1 15 0 0 0 0 0 0 0 15 0 filler-data-15 filler2 -1 16 0 0 0 0 0 0 0 16 0 filler-data-16 filler2 -1 17 0 0 0 0 0 0 0 17 0 filler-data-17 filler2 -1 18 0 0 0 0 0 0 0 18 0 filler-data-18 filler2 1 19 0 0 0 0 0 0 0 19 0 filler-data-19 filler2 +1 18 0 0 0 0 0 0 0 18 0 filler-data-18 filler2 +1 17 0 0 0 0 0 0 0 17 0 filler-data-17 filler2 +1 16 0 0 0 0 0 0 0 16 0 filler-data-16 filler2 +1 15 0 0 0 0 0 0 0 15 0 filler-data-15 filler2 +1 14 0 0 0 0 0 0 0 14 0 filler-data-14 filler2 +1 13 0 0 0 0 0 0 0 13 0 filler-data-13 filler2 +1 12 0 0 0 0 0 0 0 12 0 filler-data-12 filler2 +1 11 0 0 0 0 0 0 0 11 0 filler-data-11 filler2 +1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 44c436e00ff..7fb1cd5f037 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1293,7 +1293,7 @@ count(*) 623 explain select * from t1 where c between 1 and 2500; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c c 5 NULL # Using where +1 SIMPLE t1 range c c 5 NULL # Using index condition; Using MRR update t1 set c=a; explain select * from t1 where c between 1 and 2500; id select_type table type possible_keys key key_len ref rows Extra @@ -1986,7 +1986,7 @@ qq *a *a*a * explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v,v_2 # 13 const # Using where +1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 @@ -2162,7 +2162,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 303 const # Using where; Using index explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 303 const # Using where +1 SIMPLE t1 ref v v 303 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 diff --git a/mysql-test/r/innodb_mrr.result b/mysql-test/r/innodb_mrr.result new file mode 100644 index 00000000000..bf72ce822df --- /dev/null +++ b/mysql-test/r/innodb_mrr.result @@ -0,0 +1,404 @@ +drop table if exists t1,t2,t3,t4; +set @save_storage_engine= @@storage_engine; +set storage_engine=InnoDB; +create table t1(a int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C; +create table t3 ( +a char(8) not null, b char(8) not null, filler char(200), +key(a) +); +insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A; +insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'), +'filler-1' from t2 A; +insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'), +'filler-2' from t2 A; +select a,filler from t3 where a >= 'c-9011=w'; +a filler +select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w'; +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or +(a>='c-1014=w' and a <= 'c-1015=w'); +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +insert into t3 values ('c-1013=z', 'c-1013=z', 'err'); +insert into t3 values ('a-1014=w', 'a-1014=w', 'err'); +select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or +(a>='c-1014=w' and a <= 'c-1015=w'); +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +delete from t3 where b in ('c-1013=z', 'a-1014=w'); +select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or +a='c-1014=w' or a='c-1015=w'; +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +insert into t3 values ('c-1013=w', 'del-me', 'inserted'); +select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or +a='c-1014=w' or a='c-1015=w'; +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +c-1013=w inserted +delete from t3 where b='del-me'; +alter table t3 add primary key(b); +select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or +b IN ('c-1019=w', 'c-1020=w', 'c-1021=w', +'c-1022=w', 'c-1023=w', 'c-1024=w'); +b filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1016=w filler +c-1017=w filler +c-1018=w filler +c-1019=w filler +c-1020=w filler +c-1021=w filler +c-1022=w filler +c-1023=w filler +c-1024=w filler +select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or +b IN ('c-1021=w', 'c-1022=w', 'c-1023=w'); +b filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1016=w filler +c-1017=w filler +c-1018=w filler +c-1019=w filler +c-1020=w filler +c-1021=w filler +c-1022=w filler +c-1023=w filler +select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or +b IN ('c-1019=w', 'c-1020=w') or +(b>='c-1021=w' and b<= 'c-1023=w'); +b filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1016=w filler +c-1017=w filler +c-1018=w filler +c-1019=w filler +c-1020=w filler +c-1021=w filler +c-1022=w filler +c-1023=w filler +create table t4 (a varchar(10), b int, c char(10), filler char(200), +key idx1 (a, b, c)); +insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15; +insert into t4 (a,b,c,filler) +select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15; +insert into t4 (a,b,c,filler) +select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15; +insert into t4 (a,b,c,filler) +select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15; +insert into t4 (a,b,c,filler) +select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500; +explain +select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' + or c='no-such-row2'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Using MRR +select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' + or c='no-such-row2'); +a b c filler +NULL NULL NULL NULL-15 +NULL NULL NULL NULL-14 +NULL NULL NULL NULL-13 +NULL NULL NULL NULL-12 +NULL NULL NULL NULL-11 +NULL NULL NULL NULL-10 +NULL NULL NULL NULL-9 +NULL NULL NULL NULL-8 +NULL NULL NULL NULL-7 +NULL NULL NULL NULL-6 +NULL NULL NULL NULL-5 +NULL NULL NULL NULL-4 +NULL NULL NULL NULL-3 +NULL NULL NULL NULL-2 +NULL NULL NULL NULL-1 +explain +select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Using MRR +select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); +a b c filler +b-1 NULL c-1 NULL-15 +b-1 NULL c-1 NULL-14 +b-1 NULL c-1 NULL-13 +b-1 NULL c-1 NULL-12 +b-1 NULL c-1 NULL-11 +b-1 NULL c-1 NULL-10 +b-1 NULL c-1 NULL-9 +b-1 NULL c-1 NULL-8 +b-1 NULL c-1 NULL-7 +b-1 NULL c-1 NULL-6 +b-1 NULL c-1 NULL-5 +b-1 NULL c-1 NULL-4 +b-1 NULL c-1 NULL-3 +b-1 NULL c-1 NULL-2 +b-1 NULL c-1 NULL-1 +bb-1 NULL cc-2 NULL-15 +bb-1 NULL cc-2 NULL-14 +bb-1 NULL cc-2 NULL-13 +bb-1 NULL cc-2 NULL-12 +bb-1 NULL cc-2 NULL-11 +bb-1 NULL cc-2 NULL-10 +bb-1 NULL cc-2 NULL-9 +bb-1 NULL cc-2 NULL-8 +bb-1 NULL cc-2 NULL-7 +bb-1 NULL cc-2 NULL-6 +bb-1 NULL cc-2 NULL-5 +bb-1 NULL cc-2 NULL-4 +bb-1 NULL cc-2 NULL-3 +bb-1 NULL cc-2 NULL-2 +bb-1 NULL cc-2 NULL-1 +select * from t4 ignore index(idx1) where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); +a b c filler +b-1 NULL c-1 NULL-15 +b-1 NULL c-1 NULL-14 +b-1 NULL c-1 NULL-13 +b-1 NULL c-1 NULL-12 +b-1 NULL c-1 NULL-11 +b-1 NULL c-1 NULL-10 +b-1 NULL c-1 NULL-9 +b-1 NULL c-1 NULL-8 +b-1 NULL c-1 NULL-7 +b-1 NULL c-1 NULL-6 +b-1 NULL c-1 NULL-5 +b-1 NULL c-1 NULL-4 +b-1 NULL c-1 NULL-3 +b-1 NULL c-1 NULL-2 +b-1 NULL c-1 NULL-1 +bb-1 NULL cc-2 NULL-15 +bb-1 NULL cc-2 NULL-14 +bb-1 NULL cc-2 NULL-13 +bb-1 NULL cc-2 NULL-12 +bb-1 NULL cc-2 NULL-11 +bb-1 NULL cc-2 NULL-10 +bb-1 NULL cc-2 NULL-9 +bb-1 NULL cc-2 NULL-8 +bb-1 NULL cc-2 NULL-7 +bb-1 NULL cc-2 NULL-6 +bb-1 NULL cc-2 NULL-5 +bb-1 NULL cc-2 NULL-4 +bb-1 NULL cc-2 NULL-3 +bb-1 NULL cc-2 NULL-2 +bb-1 NULL cc-2 NULL-1 +drop table t1, t2, t3, t4; +create table t1 (a int, b int not null,unique key (a,b),index(b)); +insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); +create table t2 like t1; +insert into t2 select * from t1; +alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); +select * from t1 where a is null; +a b c +NULL 7 0 +NULL 9 0 +NULL 9 0 +select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; +a b c +NULL 9 0 +NULL 9 0 +select * from t1 where a is null and b=9 or a is null and b=7 limit 3; +a b c +NULL 7 0 +NULL 9 0 +NULL 9 0 +drop table t1, t2; +set storage_engine= @save_storage_engine; +set @read_rnd_buffer_size_save= @@read_rnd_buffer_size; +set read_rnd_buffer_size=64; +Warnings: +Warning 1292 Truncated incorrect read_rnd_buffer_size value: '64' +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) ) engine=InnoDB; +insert into t2 select +concat('a-', 1000 + A.a, '-a'), +concat('b-', 1000 + B.a, '-b'), +concat('c-', 1000 + C.a, '-c'), +'filler' +from t1 A, t1 B, t1 C; +explain +select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range a a 9 NULL 99 Using index condition; Using MRR +select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a'; +count(length(a) + length(filler)) +100 +drop table t2; +create table t2 (a char(100), b char(100), c char(100), d int, +filler char(10), key(d), primary key (a,b,c)) engine= innodb; +insert into t2 select A.a, B.a, B.a, A.a, 'filler' from t1 A, t1 B; +explain select * from t2 force index (d) where d < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range d d 5 NULL 47 Using index condition; Using MRR +drop table t2; +drop table t1; +set @@read_rnd_buffer_size= @read_rnd_buffer_size_save; +create table t1 (f1 int not null, f2 int not null,f3 int not null, f4 char(1), primary key (f1,f2), key ix(f3))Engine=InnoDB; +select * from t1 where (f3>=5 and f3<=10) or (f3>=1 and f3<=4); +f1 f2 f3 f4 +1 1 1 A +2 2 2 A +3 3 3 A +4 4 4 A +5 5 5 A +6 6 6 A +7 7 7 A +8 8 8 A +9 9 9 A +10 10 10 A +drop table t1; + +BUG#37977: Wrong result returned on GROUP BY + OR + Innodb + +CREATE TABLE t1 ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`int_nokey` int(11) NOT NULL, +`int_key` int(11) NOT NULL, +`date_key` date NOT NULL, +`date_nokey` date NOT NULL, +`time_key` time NOT NULL, +`time_nokey` time NOT NULL, +`datetime_key` datetime NOT NULL, +`datetime_nokey` datetime NOT NULL, +`varchar_key` varchar(5) DEFAULT NULL, +`varchar_nokey` varchar(5) DEFAULT NULL, +PRIMARY KEY (`pk`), +KEY `int_key` (`int_key`), +KEY `date_key` (`date_key`), +KEY `time_key` (`time_key`), +KEY `datetime_key` (`datetime_key`), +KEY `varchar_key` (`varchar_key`) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(1,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14 05:34:08','2007-09-14 05:34:08','qk','qk'), +(2,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'), +(3,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04 15:42:50','aew','aew'), +(4,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23 13:23:35',NULL,NULL), +(5,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19 11:01:28','qu','qu'); +select pk from t1 WHERE `varchar_key` > 'kr' group by pk; +pk +1 +5 +select pk from t1 WHERE `int_nokey` IS NULL OR `varchar_key` > 'kr' group by pk; +pk +1 +5 +drop table t1; +# +# BUG#39447: Error with NOT NULL condition and LIMIT 1 +# +CREATE TABLE t1 ( +id int(11) NOT NULL, +parent_id int(11) DEFAULT NULL, +name varchar(10) DEFAULT NULL, +PRIMARY KEY (id), +KEY ind_parent_id (parent_id) +) ENGINE=InnoDB; +insert into t1 (id, parent_id, name) values +(10,NULL,'A'), +(20,10,'B'), +(30,10,'C'), +(40,NULL,'D'), +(50,40,'E'), +(60,40,'F'), +(70,NULL,'J'); +SELECT id FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; +id +60 +This must show type=index, extra=Using where +explain SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using where +SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; +id parent_id name +60 40 F +drop table t1; diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 3ff5f04b6c6..09f99fcf80e 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -356,7 +356,7 @@ SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index -1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index +1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%' OR FALSE; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 736ecf1d90e..2f75aee6380 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -869,7 +869,7 @@ insert into t3 select * from t2 where a < 800; explain select * from t2,t3 where t2.a < 200 and t2.b=t3.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL a,b NULL NULL NULL 1000 Using where -1 SIMPLE t3 ref b b 5 test.t2.b 1 Using where +1 SIMPLE t3 ref b b 5 test.t2.b 1 drop table t1, t2, t3; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index 2bdac08e7fd..6718274184d 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -74,7 +74,7 @@ WHERE t3.a=1 OR t3.c IS NULL; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 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`.`t2` left join (`test`.`t3` join `test`.`t4`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where ((`test`.`t3`.`a` = 1) or isnull(`test`.`t3`.`c`)) SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b @@ -150,7 +150,7 @@ WHERE t3.a>1 OR t3.c IS NULL; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t5 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`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where ((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) @@ -180,7 +180,7 @@ WHERE (t3.a>1 OR t3.c IS NULL) AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +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 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`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where (((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) and ((`test`.`t5`.`a` < 3) or isnull(`test`.`t5`.`c`))) @@ -230,7 +230,7 @@ ON t7.b=t8.b AND t6.b < 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using join buffer -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `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` from `test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10))) where 1 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b @@ -546,12 +546,12 @@ 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 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +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 -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 +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 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` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) 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))) where ((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`))) SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, @@ -837,8 +837,8 @@ WHERE t1.a <= 2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +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 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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) where (`test`.`t1`.`a` <= 2) CREATE INDEX idx_b ON t2(b); @@ -851,7 +851,7 @@ ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b; 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 -1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 +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`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where 1 @@ -1054,8 +1054,8 @@ t0.b=t1.b AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); 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 where +1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 +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 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 @@ -1196,13 +1196,13 @@ INSERT INTO t3 VALUES (0), (1), (2), (3), (4), (5); EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 21 Using index -1 SIMPLE t3 index c c 5 NULL 6 Using index +1 SIMPLE t3 index c c 5 NULL 6 Using where; Using index 1 SIMPLE t2 ref b b 5 test.t3.c 2 Using index EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 21 Using index 1 SIMPLE t3 index c c 5 NULL 6 Using index -1 SIMPLE t2 ref b b 5 test.t3.c 2 Using index +1 SIMPLE t2 ref b b 5 test.t3.c 2 Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c NULL 0 0 @@ -1273,7 +1273,7 @@ EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 21 Using index 1 SIMPLE t3 index c c 5 NULL 0 Using index -1 SIMPLE t2 ref b b 5 test.t3.c 2 Using index +1 SIMPLE t2 ref b b 5 test.t3.c 2 Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c NULL NULL NULL @@ -1317,8 +1317,8 @@ c11 c21 c31 EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ALL NULL NULL NULL NULL 0 -1 SIMPLE t3 ALL NULL NULL NULL NULL 0 +1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where DROP TABLE t1,t2,t3; CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL); INSERT INTO t1 VALUES (23, 2340), (26, 9900); @@ -1447,12 +1447,12 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X 1 SIMPLE t3 ref a a 5 test.t2.b X 1 SIMPLE t5 ref a a 5 test.t3.b X -1 SIMPLE t4 ref a a 5 test.t3.b X Using where +1 SIMPLE t4 ref a a 5 test.t3.b X Using index condition explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X -1 SIMPLE t3 ref a a 5 test.t2.b X Using where +1 SIMPLE t3 ref a a 5 test.t2.b X Using index condition 1 SIMPLE t4 ref a a 5 test.t3.b X 1 SIMPLE t6 ref a a 5 test.t4.b X 1 SIMPLE t5 ref a a 5 test.t2.b X diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 1e4fc91b8bd..5b81ea0e4fa 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -630,7 +630,7 @@ insert into t2 values (10,1),(20,2),(30,3); explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using index -1 SIMPLE t1 const PRIMARY PRIMARY 2 const 1 Using index +1 SIMPLE t1 const PRIMARY PRIMARY 2 const 1 Using where; Using index select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; fooID barID fooID 10 1 NULL @@ -688,8 +688,8 @@ a1 a2 b1 b2 c1 c2 explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where drop table t1, t2, t3; create table t1 ( a int(11), diff --git a/mysql-test/r/join_outer_innodb.result b/mysql-test/r/join_outer_innodb.result index e8a2d6f668b..56e557ec881 100644 --- a/mysql-test/r/join_outer_innodb.result +++ b/mysql-test/r/join_outer_innodb.result @@ -9,7 +9,7 @@ SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index -1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index +1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%' OR FALSE; diff --git a/mysql-test/r/key_cache.result b/mysql-test/r/key_cache.result index 08d8059f61b..8450b4632e8 100644 --- a/mysql-test/r/key_cache.result +++ b/mysql-test/r/key_cache.result @@ -134,7 +134,7 @@ i explain select count(*) from t1, t2 where t1.p = t2.i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index -1 SIMPLE t2 ref k1 k1 5 test.t1.p 2 Using where; Using index +1 SIMPLE t2 ref k1 k1 5 test.t1.p 2 Using index select count(*) from t1, t2 where t1.p = t2.i; count(*) 3 diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index e3633404db1..c49cf801fa7 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -673,7 +673,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE fileset_id = 2 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,files PRIMARY 35 NULL 5 Using where +1 SIMPLE t1 range PRIMARY,files PRIMARY 35 NULL 5 Using index condition; Using MRR EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2 AND file_code = '0000000115' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/mix2_myisam.result b/mysql-test/r/mix2_myisam.result index e0a3d1af089..676fd06fc8b 100644 --- a/mysql-test/r/mix2_myisam.result +++ b/mysql-test/r/mix2_myisam.result @@ -1114,11 +1114,11 @@ count(*) 29267 explain select * from t1 where c between 1 and 2500; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c c 5 NULL # Using where +1 SIMPLE t1 range c c 5 NULL # Using index condition; Using MRR update t1 set c=a; explain select * from t1 where c between 1 and 2500; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c c 5 NULL # Using where +1 SIMPLE t1 range c c 5 NULL # Using index condition; Using MRR drop table t1,t2; create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=MyISAM; insert into t1 (id) values (null),(null),(null),(null),(null); @@ -1559,7 +1559,7 @@ qq *a *a*a * explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v,v_2 # 13 const # Using where +1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 @@ -1735,7 +1735,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 303 const # Using where; Using index explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 303 const # Using where +1 SIMPLE t1 ref v v 303 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 0a02c25c322..c74ffb3ae6f 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -358,7 +358,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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 -1 SIMPLE t1 ref b b 5 test.t2.b 1 Using where +1 SIMPLE t1 ref b b 5 test.t2.b 1 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 @@ -368,10 +368,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a NULL NULL 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 4 Using index condition; Using MRR 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 Using where +1 SIMPLE t1 ref c,c_2 c 5 const 1 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 @@ -1230,7 +1230,7 @@ qq *a *a*a * explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v,v_2 # 13 const # Using where +1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 @@ -1406,7 +1406,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 303 const # Using where; Using index explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 303 const # Using where +1 SIMPLE t1 ref v v 303 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result new file mode 100644 index 00000000000..f3f89966da2 --- /dev/null +++ b/mysql-test/r/myisam_mrr.result @@ -0,0 +1,393 @@ +drop table if exists t1, t2, t3; +set @read_rnd_buffer_size_save= @@read_rnd_buffer_size; +set read_rnd_buffer_size=79; +Warnings: +Warning 1292 Truncated incorrect read_rnd_buffer_size value: '79' +select @@read_rnd_buffer_size; +@@read_rnd_buffer_size +8228 +create table t1(a int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C; +create table t3 ( +a char(8) not null, b char(8) not null, filler char(200), +key(a) +); +insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A; +insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'), +'filler-1' from t2 A; +insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'), +'filler-2' from t2 A; +select a,filler from t3 where a >= 'c-9011=w'; +a filler +select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w'; +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or +(a>='c-1014=w' and a <= 'c-1015=w'); +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +insert into t3 values ('c-1013=z', 'c-1013=z', 'err'); +insert into t3 values ('a-1014=w', 'a-1014=w', 'err'); +select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or +(a>='c-1014=w' and a <= 'c-1015=w'); +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +delete from t3 where b in ('c-1013=z', 'a-1014=w'); +select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or +a='c-1014=w' or a='c-1015=w'; +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +insert into t3 values ('c-1013=w', 'del-me', 'inserted'); +select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or +a='c-1014=w' or a='c-1015=w'; +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +c-1013=w inserted +delete from t3 where b='del-me'; +alter table t3 add primary key(b); +select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or +b IN ('c-1019=w', 'c-1020=w', 'c-1021=w', +'c-1022=w', 'c-1023=w', 'c-1024=w'); +b filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1016=w filler +c-1017=w filler +c-1018=w filler +c-1019=w filler +c-1020=w filler +c-1021=w filler +c-1022=w filler +c-1023=w filler +c-1024=w filler +select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or +b IN ('c-1021=w', 'c-1022=w', 'c-1023=w'); +b filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1016=w filler +c-1017=w filler +c-1018=w filler +c-1019=w filler +c-1020=w filler +c-1021=w filler +c-1022=w filler +c-1023=w filler +select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or +b IN ('c-1019=w', 'c-1020=w') or +(b>='c-1021=w' and b<= 'c-1023=w'); +b filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1016=w filler +c-1017=w filler +c-1018=w filler +c-1019=w filler +c-1020=w filler +c-1021=w filler +c-1022=w filler +c-1023=w filler +create table t4 (a varchar(10), b int, c char(10), filler char(200), +key idx1 (a, b, c)); +insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15; +insert into t4 (a,b,c,filler) +select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15; +insert into t4 (a,b,c,filler) +select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15; +insert into t4 (a,b,c,filler) +select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15; +insert into t4 (a,b,c,filler) +select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500; +explain +select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' + or c='no-such-row2'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t4 range idx1 idx1 29 NULL 10 Using index condition; Using MRR +select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' + or c='no-such-row2'); +a b c filler +NULL NULL NULL NULL-15 +NULL NULL NULL NULL-14 +NULL NULL NULL NULL-13 +NULL NULL NULL NULL-12 +NULL NULL NULL NULL-11 +NULL NULL NULL NULL-10 +NULL NULL NULL NULL-9 +NULL NULL NULL NULL-8 +NULL NULL NULL NULL-7 +NULL NULL NULL NULL-6 +NULL NULL NULL NULL-5 +NULL NULL NULL NULL-4 +NULL NULL NULL NULL-3 +NULL NULL NULL NULL-2 +NULL NULL NULL NULL-1 +explain +select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t4 range idx1 idx1 29 NULL 21 Using index condition; Using MRR +select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); +a b c filler +b-1 NULL c-1 NULL-15 +b-1 NULL c-1 NULL-14 +b-1 NULL c-1 NULL-13 +b-1 NULL c-1 NULL-12 +b-1 NULL c-1 NULL-11 +b-1 NULL c-1 NULL-10 +b-1 NULL c-1 NULL-9 +b-1 NULL c-1 NULL-8 +b-1 NULL c-1 NULL-7 +b-1 NULL c-1 NULL-6 +b-1 NULL c-1 NULL-5 +b-1 NULL c-1 NULL-4 +b-1 NULL c-1 NULL-3 +b-1 NULL c-1 NULL-2 +b-1 NULL c-1 NULL-1 +bb-1 NULL cc-2 NULL-15 +bb-1 NULL cc-2 NULL-14 +bb-1 NULL cc-2 NULL-13 +bb-1 NULL cc-2 NULL-12 +bb-1 NULL cc-2 NULL-11 +bb-1 NULL cc-2 NULL-10 +bb-1 NULL cc-2 NULL-9 +bb-1 NULL cc-2 NULL-8 +bb-1 NULL cc-2 NULL-7 +bb-1 NULL cc-2 NULL-6 +bb-1 NULL cc-2 NULL-5 +bb-1 NULL cc-2 NULL-4 +bb-1 NULL cc-2 NULL-3 +bb-1 NULL cc-2 NULL-2 +bb-1 NULL cc-2 NULL-1 +select * from t4 ignore index(idx1) where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); +a b c filler +b-1 NULL c-1 NULL-15 +b-1 NULL c-1 NULL-14 +b-1 NULL c-1 NULL-13 +b-1 NULL c-1 NULL-12 +b-1 NULL c-1 NULL-11 +b-1 NULL c-1 NULL-10 +b-1 NULL c-1 NULL-9 +b-1 NULL c-1 NULL-8 +b-1 NULL c-1 NULL-7 +b-1 NULL c-1 NULL-6 +b-1 NULL c-1 NULL-5 +b-1 NULL c-1 NULL-4 +b-1 NULL c-1 NULL-3 +b-1 NULL c-1 NULL-2 +b-1 NULL c-1 NULL-1 +bb-1 NULL cc-2 NULL-15 +bb-1 NULL cc-2 NULL-14 +bb-1 NULL cc-2 NULL-13 +bb-1 NULL cc-2 NULL-12 +bb-1 NULL cc-2 NULL-11 +bb-1 NULL cc-2 NULL-10 +bb-1 NULL cc-2 NULL-9 +bb-1 NULL cc-2 NULL-8 +bb-1 NULL cc-2 NULL-7 +bb-1 NULL cc-2 NULL-6 +bb-1 NULL cc-2 NULL-5 +bb-1 NULL cc-2 NULL-4 +bb-1 NULL cc-2 NULL-3 +bb-1 NULL cc-2 NULL-2 +bb-1 NULL cc-2 NULL-1 +drop table t1, t2, t3, t4; +create table t1 (a int, b int not null,unique key (a,b),index(b)); +insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); +create table t2 like t1; +insert into t2 select * from t1; +alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); +select * from t1 where a is null; +a b c +NULL 7 0 +NULL 9 0 +NULL 9 0 +select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; +a b c +NULL 9 0 +NULL 9 0 +select * from t1 where a is null and b=9 or a is null and b=7 limit 3; +a b c +NULL 7 0 +NULL 9 0 +NULL 9 0 +drop table t1, t2; +set @@read_rnd_buffer_size= @read_rnd_buffer_size_save; +CREATE TABLE t1 ( +ID int(10) unsigned NOT NULL AUTO_INCREMENT, +col1 int(10) unsigned DEFAULT NULL, +key1 int(10) unsigned NOT NULL DEFAULT '0', +key2 int(10) unsigned DEFAULT NULL, +text1 text, +text2 text, +col2 smallint(6) DEFAULT '100', +col3 enum('headers','bodyandsubject') NOT NULL DEFAULT 'bodyandsubject', +col4 tinyint(3) unsigned NOT NULL DEFAULT '0', +PRIMARY KEY (ID), +KEY (key1), +KEY (key2) +) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; +INSERT INTO t1 VALUES +(1,NULL,1130,NULL,'Hello',NULL,100,'bodyandsubject',0), +(2,NULL,1130,NULL,'bye',NULL,100,'bodyandsubject',0), +(3,NULL,1130,NULL,'red',NULL,100,'bodyandsubject',0), +(4,NULL,1130,NULL,'yellow',NULL,100,'bodyandsubject',0), +(5,NULL,1130,NULL,'blue',NULL,100,'bodyandsubject',0); +select * FROM t1 WHERE key1=1130 AND col1 IS NULL ORDER BY text1; +ID col1 key1 key2 text1 text2 col2 col3 col4 +5 NULL 1130 NULL blue NULL 100 bodyandsubject 0 +2 NULL 1130 NULL bye NULL 100 bodyandsubject 0 +1 NULL 1130 NULL Hello NULL 100 bodyandsubject 0 +3 NULL 1130 NULL red NULL 100 bodyandsubject 0 +4 NULL 1130 NULL yellow NULL 100 bodyandsubject 0 +drop table t1; + +BUG#37851: Crash in test_if_skip_sort_order tab->select is zero + +CREATE TABLE t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 ( +pk int(11) NOT NULL AUTO_INCREMENT, +int_key int(11) DEFAULT NULL, +PRIMARY KEY (pk), +KEY int_key (int_key) +); +INSERT INTO t2 VALUES (1,1),(2,6),(3,0); +EXPLAIN EXTENDED +SELECT MIN(t1.pk) +FROM t1 WHERE EXISTS ( +SELECT t2.pk +FROM t2 +WHERE t2.int_key IS NULL +GROUP BY t2.pk +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t2 ALL int_key int_key 5 3 33.33 Using index condition; Using filesort +Warnings: +Note 1003 select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0 +DROP TABLE t1, t2; +# +# BUG#42048 Discrepancy between MyISAM and Maria's ICP implementation +# +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b char(20), filler char(200), key(a,b(10))); +insert into t1 select A.a + 10*(B.a + 10*C.a), 'bbb','filler' from t0 A, t0 B, t0 C; +update t1 set b=repeat(char(65+a), 20) where a < 25; +This must show range + using index condition: +explain select * from t1 where a < 10 and b = repeat(char(65+a), 20); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 19 Using where +select * from t1 where a < 10 and b = repeat(char(65+a), 20); +a b filler +0 AAAAAAAAAAAAAAAAAAAA filler +1 BBBBBBBBBBBBBBBBBBBB filler +2 CCCCCCCCCCCCCCCCCCCC filler +3 DDDDDDDDDDDDDDDDDDDD filler +4 EEEEEEEEEEEEEEEEEEEE filler +5 FFFFFFFFFFFFFFFFFFFF filler +6 GGGGGGGGGGGGGGGGGGGG filler +7 HHHHHHHHHHHHHHHHHHHH filler +8 IIIIIIIIIIIIIIIIIIII filler +9 JJJJJJJJJJJJJJJJJJJJ filler +drop table t0,t1; +# +# BUG#41136: ORDER BY + range access: EXPLAIN shows "Using MRR" while MRR is actually not used +# +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, key(a)); +insert into t1 select A.a + 10 *(B.a + 10*C.a), A.a + 10 *(B.a + 10*C.a) from t0 A, t0 B, t0 C; +This mustn't show "Using MRR": +explain select * from t1 where a < 20 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 20 Using index condition +drop table t0, t1; diff --git a/mysql-test/r/negation_elimination.result b/mysql-test/r/negation_elimination.result index 91a4c273832..dea0d865d87 100644 --- a/mysql-test/r/negation_elimination.result +++ b/mysql-test/r/negation_elimination.result @@ -79,7 +79,7 @@ a 19 explain select * from t1 where not(a != 10); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 5 const 1 Using where; Using index +1 SIMPLE t1 ref a a 5 const 1 Using index select * from t1 where not(a != 1); a 1 diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index 1cdc48e6552..897366787f1 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -148,10 +148,10 @@ insert into t1 values (7,7), (8,8), (9,9), (10,10), (11,11), (12,12); explain select * from t1 where a between 2 and 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx idx 4 NULL 2 Using where +1 SIMPLE t1 range idx idx 4 NULL 2 Using index condition; Using MRR explain select * from t1 where a between 2 and 3 or b is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx idx 4 NULL 2 Using where +1 SIMPLE t1 range idx idx 4 NULL 2 Using index condition; Using MRR drop table t1; select cast(NULL as signed); cast(NULL as signed) @@ -170,7 +170,7 @@ insert into t1 select i*2 from t1; insert into t1 values(null); explain select * from t1 where i=2 or i is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null i i 5 const 9 Using where; Using index +1 SIMPLE t1 ref_or_null i i 5 const 9 Using index select count(*) from t1 where i=2 or i is null; count(*) 10 diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 58c587fe588..d59383f3037 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -21,10 +21,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index explain select * from t1 where (a is null or a = 7) and b=7; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index explain select * from t1 where (a is null or a = 7) and b=7 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index; Using filesort explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index @@ -151,7 +151,7 @@ alter table t1 modify b int null; insert into t1 values (7,null), (8,null), (8,7); explain select * from t1 where a = 7 and (b=7 or b is null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index +1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using index select * from t1 where a = 7 and (b=7 or b is null); a b 7 7 @@ -166,7 +166,7 @@ a b NULL 7 explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index +1 SIMPLE t1 ref_or_null a a 5 const 5 Using index select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); a b 7 NULL @@ -192,7 +192,7 @@ a a b explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); 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_or_null a a 10 test.t2.a,const 4 Using where; Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); a a b 7 7 7 @@ -202,7 +202,7 @@ a a b explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; 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_or_null a a 10 test.t2.a,const 4 Using where; Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; a a b 7 7 7 @@ -226,7 +226,7 @@ delete from t1 where a=8; explain select * from t2,t1 where t1.a=t2.a or t1.a is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 -1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using index explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 @@ -258,7 +258,7 @@ INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4 INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); explain select id from t1 where uniq_id is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1 idx1 5 const 5 Using where +1 SIMPLE t1 ref idx1 idx1 5 const 5 Using index condition explain select id from t1 where uniq_id =1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const idx1 idx1 5 const 1 diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 306fce1f3c2..334f1547276 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -514,7 +514,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using index condition drop table t1,t2,t3; CREATE TABLE t1 ( `titre` char(80) NOT NULL default '', @@ -609,7 +609,7 @@ FieldKey LongVal StringVal 1 2 1 EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range FieldKey,LongField,StringField FieldKey 38 NULL 4 Using where; Using filesort +1 SIMPLE t1 range FieldKey,LongField,StringField FieldKey 38 NULL 4 Using index condition; Using where; Using MRR; Using filesort SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; FieldKey LongVal StringVal 3 1 2 @@ -638,7 +638,7 @@ 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); 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 3 Using filesort select * from t1 where b=1 or b is null order by a; a b 1 1 @@ -647,7 +647,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 4 Using filesort select * from t1 where b=2 or b is null order by a; a b 3 NULL @@ -1004,7 +1004,7 @@ t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) ORDER BY c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort -1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where SELECT t2.b as c FROM t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) ORDER BY c; @@ -1107,7 +1107,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 5 NULL 73 Using where EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range k2 k2 5 NULL 386 Using where; Using filesort +1 SIMPLE t2 range k2 k2 5 NULL 386 Using index condition; Using where; Using MRR; Using filesort SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; id c3 6 14 @@ -1454,8 +1454,8 @@ SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b b 4 const 4 Using where; Using temporary; Using filesort -1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using where +1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using temporary; Using filesort +1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index 769d499fc0a..03859954dd1 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -137,7 +137,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index +1 SIMPLE t1 pNULL ref a a 4 const 1 Using index # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra @@ -466,7 +466,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index +1 SIMPLE t1 pNULL ref a a 4 const 1 Using index # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra @@ -1820,15 +1820,15 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3 ALL NULL NULL NULL NULL 910 Using where explain partitions select * from t2 where b = 4; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 Using where +1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 explain extended select * from t2 where b = 6; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ref b b 5 const 76 100.00 Using where +1 SIMPLE t2 ref b b 5 const 76 100.00 Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = 6) explain partitions select * from t2 where b = 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 Using where +1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 explain extended select * from t2 where b in (1,3,5); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL b NULL NULL NULL 910 40.66 Using where diff --git a/mysql-test/r/pool_of_threads.result b/mysql-test/r/pool_of_threads.result index 42460a8040c..0577abaf4ae 100644 --- a/mysql-test/r/pool_of_threads.result +++ b/mysql-test/r/pool_of_threads.result @@ -183,37 +183,37 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; fld1 fld3 -218401 faithful +012001 flanking +013602 foldout +013606 fingerings 018007 fanatic -228311 fated 018017 featherweight -218022 feed -088303 feminine -058004 Fenton -038017 fetched 018054 fetters -208101 fiftieth -238007 filial -013606 fingerings -218008 finishers -038205 firearm -188505 fitting -202301 Fitzpatrick -238008 fixedly -012001 flanking 018103 flint 018104 flopping +036002 funereal +038017 fetched +038205 firearm +058004 Fenton +088303 feminine +186002 freakish 188007 flurried -013602 foldout +188505 fitting +198006 furthermore +202301 Fitzpatrick +208101 fiftieth +208113 freest +218008 finishers +218022 feed +218401 faithful 226205 foothill -232102 forgivably +226209 furnishings 228306 forthcoming -186002 freakish -208113 freest +228311 fated 231315 freezes -036002 funereal -226209 furnishings -198006 furthermore +232102 forgivably +238007 filial +238008 fixedly select fld3 from t2 where fld3 like "L%" and fld3 = "ok"; fld3 select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly"); @@ -1389,15 +1389,15 @@ id select_type table type possible_keys key key_len ref rows Extra explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 @@ -1413,15 +1413,15 @@ id select_type table type possible_keys key key_len ref rows Extra explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index 8d578794d6d..174d7764cf3 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -465,9 +465,9 @@ def key 253 64 7 Y 0 31 8 def key_len 253 4096 1 Y 0 31 8 def ref 253 2048 0 Y 0 31 8 def rows 8 10 1 Y 32928 0 63 -def Extra 253 255 27 N 1 31 8 +def Extra 253 255 48 N 1 31 8 id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using index condition; Using MRR; Using filesort drop table if exists t2; create table t2 (id smallint, name varchar(20)) ; prepare stmt1 from ' insert into t2 values(?, ?) ' ; diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index ea6494962b6..e8add90ee0f 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -1509,8 +1509,8 @@ prepare stmt1 from 'insert into t1 values(?,?),(?,?)'; execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; select a,b from t1 where a in (@arg00,@arg02) ; a b -81 8-1 82 8-2 +81 8-1 set @arg00=9 ; set @arg01='nine' ; prepare stmt1 from 'insert into t1 set a=?, b=? '; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index cc5e8d2be96..27f62e180d2 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -220,31 +220,31 @@ insert into t1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9); update t1 set y=x; explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where -1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer +1 SIMPLE t1 ref y y 5 const 1 +1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using MRR; Using join buffer explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where -1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer +1 SIMPLE t1 ref y y 5 const 1 +1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using MRR; Using join buffer explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where -1 SIMPLE t2 range x x 5 NULL 3 Using where; Using join buffer +1 SIMPLE t1 ref y y 5 const 1 +1 SIMPLE t2 range x x 5 NULL 3 Using index condition; Using MRR; Using join buffer explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where -1 SIMPLE t2 range x x 5 NULL 3 Using where; Using join buffer +1 SIMPLE t1 ref y y 5 const 1 +1 SIMPLE t2 range x x 5 NULL 3 Using index condition; Using MRR; Using join buffer explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where -1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer +1 SIMPLE t1 ref y y 5 const 1 +1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using MRR; Using join buffer explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where -1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer +1 SIMPLE t1 ref y y 5 const 1 +1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using MRR; Using join buffer explain select count(*) from t1 where x in (1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref x x 5 const 1 Using where; Using index +1 SIMPLE t1 ref x x 5 const 1 Using index explain select count(*) from t1 where x in (1,2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index x x 5 NULL 9 Using where; Using index @@ -276,7 +276,7 @@ INSERT INTO t1 VALUES (33,5),(33,5),(33,5),(33,5),(34,5),(35,5); EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 5 NULL 2 Using where +1 SIMPLE t1 range a,b a 5 NULL 2 Using index condition; Using where; Using MRR SELECT * FROM t1 WHERE a IN(1,2) AND b=5; a b DROP TABLE t1; @@ -421,19 +421,19 @@ test.t1 analyze status OK test.t2 analyze status Table is already up to date explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using where +1 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using index condition; Using MRR 1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid > 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using where +1 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using index condition; Using MRR 1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using where +1 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using index condition; Using MRR 1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid != 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using where +1 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using index condition; Using MRR 1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; id name uid id name uid @@ -615,13 +615,13 @@ INSERT INTO t1 (a) VALUES ('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb'); explain select * from t1 where a='aaa'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 11 const 2 Using where +1 SIMPLE t1 ref a a 11 const 2 Using index condition explain select * from t1 where a=binary 'aaa'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 11 NULL 2 Using where +1 SIMPLE t1 range a a 11 NULL 2 Using index condition; Using MRR explain select * from t1 where a='aaa' collate latin1_bin; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 11 NULL 2 Using where +1 SIMPLE t1 range a a 11 NULL 2 Using index condition; Using MRR explain select * from t1 where a='aaa' collate latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a NULL NULL NULL 9 Using where @@ -704,7 +704,7 @@ WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND 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 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) SELECT s.oxid FROM t1 v, t1 s WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND @@ -878,10 +878,10 @@ INSERT INTO t1 VALUES (55,'C'), (56,'C'), (57,'C'), (58,'C'), (59,'C'), (60,'C'); EXPLAIN SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range status status 23 NULL 11 Using where +1 SIMPLE t1 range status status 23 NULL 11 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE status NOT IN ('A','B'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range status status 23 NULL 11 Using where +1 SIMPLE t1 range status status 23 NULL 11 Using index condition; Using MRR SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B'; id status 53 C @@ -910,10 +910,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range status status 23 NULL 11 Using where; Using index EXPLAIN SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range status status 23 NULL 10 Using where +1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range status status 23 NULL 10 Using where +1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Using MRR SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B'; id status 53 C @@ -1014,20 +1014,20 @@ create table t2 (a varchar(10), filler char(200), key(a)); insert into t2 select * from t1; explain select * from t1 where a between 'a' and 'a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 13 NULL # Using where +1 SIMPLE t1 range a a 13 NULL # Using index condition; Using MRR explain select * from t1 where a = 'a' or a='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 13 NULL # Using where +1 SIMPLE t1 range a a 13 NULL # Using index condition; Using MRR explain select * from t2 where a between 'a' and 'a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 13 const # Using where +1 SIMPLE t2 ref a a 13 const # Using index condition explain select * from t2 where a = 'a' or a='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 13 const # Using where +1 SIMPLE t2 ref a a 13 const # Using index condition update t1 set a='b' where a<>'a'; explain select * from t1 where a not between 'b' and 'b'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 13 NULL # Using where +1 SIMPLE t1 range a a 13 NULL # Using index condition; Using MRR select a, hex(filler) from t1 where a not between 'b' and 'b'; a hex(filler) a 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 @@ -1071,10 +1071,10 @@ id b c 0 3 4 EXPLAIN SELECT * FROM t1 WHERE b<=3 AND 3<=c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE 3 BETWEEN b AND c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using where; Using MRR SELECT * FROM t1 WHERE 0 < b OR 0 > c; id b c 0 3 4 @@ -1085,10 +1085,10 @@ id b c 0 3 4 EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where +1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where +1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where DROP TABLE t1; CREATE TABLE t1 ( item char(20) NOT NULL default '', @@ -1103,7 +1103,7 @@ INSERT INTO t1 VALUES ('A2','2005-12-01 08:00:00',1000); EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using where +1 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using index condition Warnings: Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 @@ -1151,7 +1151,7 @@ INSERT INTO t1 VALUES This must use range access: explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <= '2007-01-02 23:59:59'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range dateval dateval 4 NULL 2 Using where +1 SIMPLE t1 range dateval dateval 4 NULL 2 Using index condition; Using MRR drop table t1; CREATE TABLE t1 ( a varchar(32), index (a) @@ -1217,5 +1217,5 @@ Z In following EXPLAIN the access method should be ref, #rows~=500 (and not 2) explain select * from t2 where a=1000 and b<11; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 5 const 502 Using where +1 SIMPLE t2 ref a a 5 const 502 Using index condition drop table t1, t2; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 6b9a6b7c185..5065e540804 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -185,37 +185,37 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; fld1 fld3 -218401 faithful +012001 flanking +013602 foldout +013606 fingerings 018007 fanatic -228311 fated 018017 featherweight -218022 feed -088303 feminine -058004 Fenton -038017 fetched 018054 fetters -208101 fiftieth -238007 filial -013606 fingerings -218008 finishers -038205 firearm -188505 fitting -202301 Fitzpatrick -238008 fixedly -012001 flanking 018103 flint 018104 flopping +036002 funereal +038017 fetched +038205 firearm +058004 Fenton +088303 feminine +186002 freakish 188007 flurried -013602 foldout +188505 fitting +198006 furthermore +202301 Fitzpatrick +208101 fiftieth +208113 freest +218008 finishers +218022 feed +218401 faithful 226205 foothill -232102 forgivably +226209 furnishings 228306 forthcoming -186002 freakish -208113 freest +228311 fated 231315 freezes -036002 funereal -226209 furnishings -198006 furthermore +232102 forgivably +238007 filial +238008 fixedly select fld3 from t2 where fld3 like "L%" and fld3 = "ok"; fld3 select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly"); @@ -1391,15 +1391,15 @@ id select_type table type possible_keys key key_len ref rows Extra explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 @@ -1415,15 +1415,15 @@ id select_type table type possible_keys key key_len ref rows Extra explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 @@ -2362,7 +2362,7 @@ insert into t1 values (1,2), (2,2), (3,2), (4,2); insert into t2 values (1,3), (2,3), (3,4), (4,4); explain select * from t1 left join t2 on a=c where d in (4); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 Using where +1 SIMPLE t2 ref c,d d 5 const 2 1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer select * from t1 left join t2 on a=c where d in (4); a b c d @@ -2370,7 +2370,7 @@ a b c d 4 2 4 4 explain select * from t1 left join t2 on a=c where d = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 Using where +1 SIMPLE t2 ref c,d d 5 const 2 1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer select * from t1 left join t2 on a=c where d = 4; a b c d @@ -2397,11 +2397,11 @@ 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 -1 SIMPLE t2 ref a a 23 test.t1.a 2 +1 SIMPLE t2 ref a a 23 test.t1.a 2 Using where EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE 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 -1 SIMPLE t2 ref a a 23 test.t1.a 2 +1 SIMPLE t2 ref a a 23 test.t1.a 2 Using where DROP TABLE t1, t2; CREATE TABLE t1 ( city char(30) ); INSERT INTO t1 VALUES ('London'); @@ -2716,7 +2716,7 @@ explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and t2.b like '%%' order by t2.b limit 0,1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b,c b 5 const 1 Using where; Using temporary; Using filesort +1 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort 1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) DROP TABLE t1,t2,t3; @@ -3417,7 +3417,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using MRR DROP TABLE t1,t2; CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL); INSERT t1 SET i = 0; @@ -3453,7 +3453,7 @@ In next EXPLAIN, B.rows must be exactly 10: explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5 and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using where +1 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using index condition; Using where; Using MRR 1 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 10 drop table t1, t2; CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b)); @@ -3467,13 +3467,13 @@ INSERT INTO t2 VALUES EXPLAIN SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where -1 SIMPLE t2 ref c c 5 test.t1.a 2 Using where +1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using MRR +1 SIMPLE t2 ref c c 5 test.t1.a 2 EXPLAIN SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where -1 SIMPLE t2 ref c c 5 test.t1.a 2 Using where +1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where; Using MRR +1 SIMPLE t2 ref c c 5 test.t1.a 2 DROP TABLE t1, t2; create table t1 ( a int unsigned not null auto_increment primary key, @@ -3533,7 +3533,7 @@ WHERE t1.id=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 const idx1 NULL NULL NULL 1 -1 SIMPLE t3 ref idx1 idx1 5 const 3 Using where +1 SIMPLE t3 ref idx1 idx1 5 const 3 SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id WHERE t1.id=2; id a b c d e @@ -3562,19 +3562,19 @@ EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk < 'c' AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where DROP TABLE t1,t2; CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a)); @@ -3608,7 +3608,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si si 5 NULL 4 Using where +1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2,t3 @@ -3616,7 +3616,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND t3.a=t2.a AND t3.c IN ('bb','ee') ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si,ai si 5 NULL 4 Using where +1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 @@ -3624,7 +3624,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si si 5 NULL 2 Using where +1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2,t3 @@ -3632,7 +3632,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si,ai si 5 NULL 2 Using where +1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where DROP TABLE t1,t2,t3; CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); @@ -3752,7 +3752,7 @@ AND t1.ts BETWEEN t2.dt1 AND t2.dt2 AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t1 range ts ts 4 NULL 1 Using where +1 SIMPLE t1 range ts ts 4 NULL 1 Using index condition; Using where; Using MRR Warnings: Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1 SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 @@ -3854,7 +3854,7 @@ cc 3 7 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 ref name name 6 test.t1.name 2 +1 SIMPLE t2 ref name name 6 test.t1.name 2 Using where SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; name name n ccc NULL NULL @@ -3928,7 +3928,7 @@ cc 3 7 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 ref name name 6 test.t1.name 2 +1 SIMPLE t2 ref name name 6 test.t1.name 2 Using where SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; name name n ccc NULL NULL @@ -4376,12 +4376,12 @@ CREATE TABLE t1 (a INT KEY, b INT); INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where; Using MRR Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2 EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where; Using MRR Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` > 1)) limit 2 DROP TABLE t1; diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result index feac9efcb13..9419f256472 100644 --- a/mysql-test/r/select_safe.result +++ b/mysql-test/r/select_safe.result @@ -67,12 +67,12 @@ insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(nu 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 -1 SIMPLE t2 ref b b 21 test.t1.b 6 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 -1 SIMPLE t2 ref b b 21 test.t1.b 6 Using where +1 SIMPLE t2 ref b b 21 test.t1.b 6 SET MAX_SEEKS_FOR_KEY=DEFAULT; drop table t1; create table t1 (a int); diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 3ad556b8c30..d00d118abe3 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -6418,19 +6418,19 @@ INSERT INTO t1 VALUES (1), (2), (3), (4), (5); CREATE VIEW v1 AS SELECT c1 FROM t1; EXPLAIN SELECT * FROM t1 WHERE c1=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index +1 SIMPLE t1 ref c1 c1 5 const 1 Using index EXPLAIN SELECT * FROM t1 WHERE c1=f1(); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index +1 SIMPLE t1 ref c1 c1 5 const 1 Using index EXPLAIN SELECT * FROM v1 WHERE c1=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index +1 SIMPLE t1 ref c1 c1 5 const 1 Using index EXPLAIN SELECT * FROM v1 WHERE c1=f1(); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index +1 SIMPLE t1 ref c1 c1 5 const 1 Using index EXPLAIN SELECT * FROM t1 WHERE c1=f2(10); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index +1 SIMPLE t1 ref c1 c1 5 const 1 Using index EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL c1 5 NULL 5 Using where; Using index diff --git a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result index 9ad515a53a3..ca845e5ed91 100644 --- a/mysql-test/r/ssl.result +++ b/mysql-test/r/ssl.result @@ -186,37 +186,37 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; fld1 fld3 -218401 faithful +012001 flanking +013602 foldout +013606 fingerings 018007 fanatic -228311 fated 018017 featherweight -218022 feed -088303 feminine -058004 Fenton -038017 fetched 018054 fetters -208101 fiftieth -238007 filial -013606 fingerings -218008 finishers -038205 firearm -188505 fitting -202301 Fitzpatrick -238008 fixedly -012001 flanking 018103 flint 018104 flopping +036002 funereal +038017 fetched +038205 firearm +058004 Fenton +088303 feminine +186002 freakish 188007 flurried -013602 foldout +188505 fitting +198006 furthermore +202301 Fitzpatrick +208101 fiftieth +208113 freest +218008 finishers +218022 feed +218401 faithful 226205 foothill -232102 forgivably +226209 furnishings 228306 forthcoming -186002 freakish -208113 freest +228311 fated 231315 freezes -036002 funereal -226209 furnishings -198006 furthermore +232102 forgivably +238007 filial +238008 fixedly select fld3 from t2 where fld3 like "L%" and fld3 = "ok"; fld3 select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly"); @@ -1392,15 +1392,15 @@ id select_type table type possible_keys key key_len ref rows Extra explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 @@ -1416,15 +1416,15 @@ id select_type table type possible_keys key key_len ref rows Extra explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 diff --git a/mysql-test/r/ssl_compress.result b/mysql-test/r/ssl_compress.result index 1bd427a65e2..0dd8d40c8da 100644 --- a/mysql-test/r/ssl_compress.result +++ b/mysql-test/r/ssl_compress.result @@ -189,37 +189,37 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; fld1 fld3 -218401 faithful +012001 flanking +013602 foldout +013606 fingerings 018007 fanatic -228311 fated 018017 featherweight -218022 feed -088303 feminine -058004 Fenton -038017 fetched 018054 fetters -208101 fiftieth -238007 filial -013606 fingerings -218008 finishers -038205 firearm -188505 fitting -202301 Fitzpatrick -238008 fixedly -012001 flanking 018103 flint 018104 flopping +036002 funereal +038017 fetched +038205 firearm +058004 Fenton +088303 feminine +186002 freakish 188007 flurried -013602 foldout +188505 fitting +198006 furthermore +202301 Fitzpatrick +208101 fiftieth +208113 freest +218008 finishers +218022 feed +218401 faithful 226205 foothill -232102 forgivably +226209 furnishings 228306 forthcoming -186002 freakish -208113 freest +228311 fated 231315 freezes -036002 funereal -226209 furnishings -198006 furthermore +232102 forgivably +238007 filial +238008 fixedly select fld3 from t2 where fld3 like "L%" and fld3 = "ok"; fld3 select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly"); @@ -1395,15 +1395,15 @@ id select_type table type possible_keys key key_len ref rows Extra explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 @@ -1419,15 +1419,15 @@ id select_type table type possible_keys key key_len ref rows Extra explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index d184ae47df3..8e9ccfcb4eb 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -719,7 +719,7 @@ id 1 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index +1 PRIMARY t2 ref id id 5 const 1 100.00 Using index Warnings: Note 1249 Select 2 was reduced during optimization Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1) @@ -731,7 +731,7 @@ id 2 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index +1 PRIMARY t2 ref id id 5 const 1 100.00 Using index Warnings: Note 1249 Select 3 was reduced during optimization Note 1249 Select 2 was reduced during optimization @@ -903,7 +903,7 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a) explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` @@ -1231,7 +1231,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where +1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using index condition 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) AS `MAX(salary)` from `test`.`t1`)) @@ -1333,9 +1333,9 @@ a explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a))) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 @@ -1353,7 +1353,7 @@ a explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using index 2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) @@ -1767,7 +1767,7 @@ explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00 1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id 2 100.00 -1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where +1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using index condition Warnings: Note 1003 select `test`.`a`.`id` AS `id`,`test`.`a`.`text` AS `text`,`test`.`b`.`id` AS `id`,`test`.`b`.`text` AS `text`,`test`.`c`.`id` AS `id`,`test`.`c`.`text` AS `text` from `test`.`t1` `a` left join `test`.`t2` `b` on(((`test`.`b`.`id` = `test`.`a`.`id`) or isnull(`test`.`b`.`id`))) join `test`.`t1` `c` where (if(isnull(`test`.`b`.`id`),1000,`test`.`b`.`id`) = `test`.`c`.`id`) drop table t1,t2; @@ -2949,7 +2949,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r const PRIMARY PRIMARY 4 const 1 -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where +2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; @@ -2961,7 +2961,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r const PRIMARY PRIMARY 4 const 1 -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where +2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index a661ec5c78b..ea2c3fc75af 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -724,7 +724,7 @@ WHERE t3.name='xxx' AND t2.id=t3.id); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index; Full scan on NULL key -2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using index condition; Using where; Full scan on NULL key SELECT * FROM t1 WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 WHERE t3.name='xxx' AND t2.id=t3.id); diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index 05ec4eed6ba..e546b1a27ed 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -58,7 +58,7 @@ t0 left join (t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a) on t0.a=t1.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 4 100.00 -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on((`test`.`t0`.`a` = `test`.`t1`.`a`)) where 1 # Elimination with aggregate functions @@ -128,7 +128,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3 This should use facts and a1 tables: explain extended select id from v1 where attr1 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using where +1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using MRR 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #1 @@ -136,7 +136,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` whe This should use facts, a2 and its subquery: explain extended select id from v1 where attr2 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using where +1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using where; Using MRR 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using index 3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.a2.id 2 100.00 Using index Warnings: @@ -156,7 +156,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3 This should use facts and a1 tables: explain extended select id from v2 where attr1 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using where +1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using MRR 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #1 @@ -164,7 +164,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` whe This should use facts, a2 and its subquery: explain extended select id from v2 where attr2 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using where +1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using MRR 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using where; Using index 3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.f.id 2 100.00 Using index Warnings: @@ -194,7 +194,7 @@ t2.pk3=t2.pk1+1 and t2.pk2=t2.pk3+t2.col; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using where This must use only t1: explain select t1.* from t1 left join t2 on t2.pk2=t1.a and t2.pk1=t2.pk2+1 and @@ -212,7 +212,7 @@ explain select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.col; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where explain select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col; id select_type table type possible_keys key key_len ref rows Extra @@ -224,7 +224,7 @@ t1 left join ( t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) on t2.col=t1.col or t2.col=t1.col; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where explain select t1.*, t2.* from t1 left join @@ -247,12 +247,12 @@ this must not use table elimination: explain select t1.* from t1 left join t2 on t2.a='foo' collate latin1_general_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index -1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using where; Using index this must not use table elimination: explain select t1.* from t1 left join t2 on t2.a=t1.a collate latin1_general_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index -1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) drop table t1,t2; create table t1 (a int primary key); insert into t1 values (1),(2); @@ -262,12 +262,12 @@ this must not use table elimination: explain select t1.* from t1 left join t2 on t2.a=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index -1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using where; Using index this must not use table elimination: 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 4 NULL 2 Using index -1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) drop table t1, t2; create table t1 (a char(10) primary key); insert into t1 values ('foo'),('bar'); @@ -276,7 +276,7 @@ insert into t2 values ('foo'),('bar'); 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 -1 SIMPLE t2 ref a a 3 test.t1.a 2 +1 SIMPLE t2 ref a a 3 test.t1.a 2 Using where drop table t1, t2; # # check UPDATE/DELETE that look like they could be eliminated @@ -322,19 +322,19 @@ id select_type table type possible_keys key key_len ref rows Extra explain select t1.a from t1 left join t2 on t2.pk=t1.a or t2.b<t1.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Using where explain select t1.a from t1 left join t2 on t2.b<t1.b or t2.pk=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Using where explain select t1.a from t1 left join t2 on t2.pk between 10 and 20; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index explain select t1.a from t1 left join t2 on t2.pk between 0.5 and 1.5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index explain select t1.a from t1 left join t2 on t2.pk between 10 and 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 @@ -388,7 +388,7 @@ on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or where t1.d=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where explain select t1.* from @@ -405,7 +405,7 @@ select t1.* from t1 left join t2 on t2.pk=3 or t2.pk= 4; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index explain select t1.* from t1 left join t2 on t2.pk=3 or t2.pk= 3; @@ -416,5 +416,5 @@ select t1.* from t1 left join t2 on (t2.pk=3 and t2.b=3) or (t2.pk= 4 and t2.b=3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Using MRR drop table t1, t2; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index d81e80c96f9..72b17d6c324 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -547,7 +547,7 @@ NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL explain (select * from t1 where a=1) union (select * from t1 where b=1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 -2 UNION t1 ref b b 5 const 1 Using where +2 UNION t1 ref b b 5 const 1 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL drop table t1,t2; create table t1 ( id int not null auto_increment, primary key (id) ,user_name text ); diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 613939bfdf6..2c8e666febb 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1415,8 +1415,8 @@ a a b explain extended select * from t3 left join v3 on (t3.a = v3.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 create view v1 (a) as select a from t1; @@ -1430,8 +1430,8 @@ a a b explain extended select * from t3 left join v4 on (t3.a = v4.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);"; @@ -2132,12 +2132,12 @@ INSERT INTO t1 VALUES (2, 'foo2'); INSERT INTO t1 VALUES (1, 'foo1'); SELECT * FROM v1; id f -1 foo1 2 foo2 +1 foo1 SELECT * FROM v1; id f -1 foo1 2 foo2 +1 foo1 DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (pk int PRIMARY KEY, b int); @@ -2341,15 +2341,15 @@ CREATE VIEW v1 AS SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b; CREATE VIEW v2 AS SELECT t3.* FROM t1,t3 WHERE t1.a=t3.a; EXPLAIN SELECT t1.* FROM t1 JOIN t2 WHERE t1.a=t2.a AND t1.b=t2.b AND t1.a=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 5 const 1 Using where; Using index -1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index +1 SIMPLE t1 ref a a 5 const 1 Using index +1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using index EXPLAIN SELECT * FROM v1 WHERE a=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 5 const 1 Using where; Using index -1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index +1 SIMPLE t1 ref a a 5 const 1 Using index +1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using index EXPLAIN SELECT * FROM v2 WHERE a=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 5 const 1 Using where; Using index +1 SIMPLE t1 ref a a 5 const 1 Using index 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer DROP VIEW v1,v2; DROP TABLE t1,t2,t3; |