diff options
| author | unknown <monty@mashka.mysql.fi> | 2003-01-19 17:15:14 +0200 |
|---|---|---|
| committer | unknown <monty@mashka.mysql.fi> | 2003-01-19 17:15:14 +0200 |
| commit | 09b1e7d6076748b198f54008495cf4e60e156b86 (patch) | |
| tree | debffe8500d53e9050a799b81caa446a02b631d9 /mysql-test | |
| parent | 88df4e732605df862dd264f65788ab1d58a9b153 (diff) | |
| download | mariadb-git-09b1e7d6076748b198f54008495cf4e60e156b86.tar.gz | |
Fixed bug in LEFT JOIN with impossible ON/WHERE expression
mysql-test/r/join.result:
Test of LEFT JOIN bug
mysql-test/t/join.test:
Test of LEFT JOIN bug
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/join.result | 13 | ||||
| -rw-r--r-- | mysql-test/t/join.test | 9 |
2 files changed, 18 insertions, 4 deletions
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 96113dcdc8b..ff608825b9c 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -14,8 +14,7 @@ insert into t1 values (101); insert into t1 values (105); insert into t1 values (106); insert into t1 values (107); -insert into t2 values (107); -insert into t2 values (75); +insert into t2 values (107),(75),(1000); select t1.id, t2.id from t1, t2 where t2.id = t1.id; id id 107 107 @@ -28,6 +27,16 @@ select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t2.id; id count(t2.id) 75 1 107 1 +select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null; +id id +NULL 75 +explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null; +table type possible_keys key key_len ref rows Extra +t1 const PRIMARY NULL NULL NULL 1 Impossible ON condition +t2 ALL NULL NULL NULL NULL 3 Using where +explain select t1.id, t2.id from t1, t2 where t2.id = t1.id and t1.id <0 and t1.id > 0; +Comment +Impossible WHERE noticed after reading const tables drop table t1,t2; CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 18006e8fd22..f58281af003 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -19,13 +19,18 @@ insert into t1 values (105); insert into t1 values (106); insert into t1 values (107); -insert into t2 values (107); -insert into t2 values (75); +insert into t2 values (107),(75),(1000); select t1.id, t2.id from t1, t2 where t2.id = t1.id; select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t1.id; select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t2.id; +# +# Test problems with impossible ON or WHERE +# +select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null; +explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null; +explain select t1.id, t2.id from t1, t2 where t2.id = t1.id and t1.id <0 and t1.id > 0; drop table t1,t2; # |