From 2763b52d0a0ad85818f5c8259b4dd1d060e568e7 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 25 Jul 2003 14:32:03 +0200 Subject: outer join, impossible on condition, where, and usable key for range bug#926 --- mysql-test/r/select.result | 20 ++++++++++++++++++++ mysql-test/t/select.test | 20 ++++++++++++++++++++ 2 files changed, 40 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 811c396ea67..bde527b9b44 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3508,3 +3508,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; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index c2e451ea7f3..9884c88b420 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1832,3 +1832,23 @@ INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522); select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5; drop table t1,t2; +# +# outer join, impossible on condition, where, and usable key for range +# +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; +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; + +drop table t1,t2,t3,t4; + -- cgit v1.2.1 From 687ebba461075af77544c3bf1d268148fc3b2c90 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 31 Jul 2003 13:45:35 +0200 Subject: bug #942. docid == ftb->lastpos in join on looping over nested table --- mysql-test/r/fulltext.result | 11 +++++++++++ mysql-test/t/fulltext.test | 16 +++++++++++++++- 2 files changed, 26 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 646c1a7bee2..737390865f1 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -256,3 +256,14 @@ select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); ref_mag test drop table t1; +create table t1 (t1_id int(11) primary key, name varchar(32)); +insert into t1 values (1, 'data1'); +insert into t1 values (2, 'data2'); +create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32)); +insert into t2 values (1, 1, 'xxfoo'); +insert into t2 values (2, 1, 'xxbar'); +insert into t2 values (3, 1, 'xxbuz'); +select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode); +t1_id name t2_id t1_id name +1 data1 1 1 xxfoo +drop table t1,t2; diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 942552f5e98..387a36f1f52 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -189,7 +189,7 @@ select * from t1 where match (a) against ('aaaa'); drop table t1; # -# bug 283 by jocelyn fournier +# bug #283 by jocelyn fournier # FULLTEXT index on a TEXT filed converted to a CHAR field doesn't work anymore # @@ -201,3 +201,17 @@ alter table t1 change ref_mag ref_mag char (255) not null; select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); drop table t1; +# +# bug #942: JOIN +# + +create table t1 (t1_id int(11) primary key, name varchar(32)); +insert into t1 values (1, 'data1'); +insert into t1 values (2, 'data2'); +create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32)); +insert into t2 values (1, 1, 'xxfoo'); +insert into t2 values (2, 1, 'xxbar'); +insert into t2 values (3, 1, 'xxbuz'); +select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode); +drop table t1,t2; + -- cgit v1.2.1