diff options
Diffstat (limited to 'mysql-test/r/select.result')
| -rw-r--r-- | mysql-test/r/select.result | 56 |
1 files changed, 53 insertions, 3 deletions
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index fbce0e9290a..e83bbc2fc8c 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2568,21 +2568,51 @@ fld1 fld1 250503 250505 250504 250505 250505 250505 +insert into t2 (fld1, companynr) values (999999,99); select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; companynr companyname +99 NULL +select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null; +count(*) +1199 explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists explain select t2.companynr,companyname from t4 left join t2 using (companynr) where 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 -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Not exists +1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 41 40 -explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; +delete from t2 where fld1=999999; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; +table type possible_keys key key_len ref rows Extra +t2 ALL NULL NULL NULL NULL 1199 Using where +t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; +table type possible_keys key key_len ref rows Extra +t2 ALL NULL NULL NULL NULL 1199 Using where +t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; +table type possible_keys key key_len ref rows Extra +t2 ALL NULL NULL NULL NULL 1199 Using where +t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; +table type possible_keys key key_len ref rows Extra +t4 ALL NULL NULL NULL NULL 12 +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 < 0 or t4.companynr > 0; +table type possible_keys key key_len ref rows Extra +t4 ALL PRIMARY NULL NULL NULL 12 +t2 ALL NULL NULL NULL NULL 1199 Using where +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; +table type possible_keys key key_len ref rows Extra +t4 ALL NULL NULL NULL NULL 12 +t2 ALL NULL NULL NULL NULL 1199 Using where +plain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using temporary 1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using where; Using index @@ -3487,3 +3517,23 @@ aa id t2_id id 5 8303 2520 2520 6 8304 2521 2521 drop table t1,t2; +create table t1 (id1 int NOT NULL); +create table t2 (id2 int NOT NULL); +create table t3 (id3 int NOT NULL); +create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4)); +insert into t1 values (1); +insert into t1 values (2); +insert into t2 values (1); +insert into t4 values (1,1); +explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 +left join t4 on id3 = id4 where id2 = 1 or id4 = 1; +table type possible_keys key key_len ref rows Extra +t3 system NULL NULL NULL NULL 0 const row not found +t1 ALL NULL NULL NULL NULL 2 +t2 ALL NULL NULL NULL NULL 1 +t4 ALL id4 NULL NULL NULL 1 Using where +select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 +left join t4 on id3 = id4 where id2 = 1 or id4 = 1; +id1 id2 id3 id4 id44 +1 1 NULL NULL NULL +drop table t1,t2,t3,t4; |