Hi All, I have a curious problem with a left join:-
create table foo (a int); insert into foo values(1); insert into foo values(2); create table bar1 (a int, b datetime not null); insert into bar1 values(1, '25-12-2001'); create table bar2 (a int, b datetime); insert into bar2 values(1, '25-12-2001'); create table bar3 (a int, b char(20) not null); insert into bar3 values(1, 'Wensleydale'); select * from foo left outer join bar1 on foo.a=bar1.a; select * from foo left outer join bar1 on foo.a=bar1.a where b is null; select * from foo left outer join bar2 on foo.a=bar2.a where b is null; select * from foo left outer join bar3 on foo.a=bar3.a where b is null; drop table foo; drop table bar1; drop table bar2; drop table bar3; The selects produce these results:- select * from foo left outer join bar1 on foo.a=bar1.a -------------- a a b 1 1 2025-12-20 01:00:00 2 NULL NULL -------------- select * from foo left outer join bar1 on foo.a=bar1.a where b is null -------------- -------------- select * from foo left outer join bar2 on foo.a=bar2.a where b is null -------------- a a b 2 NULL NULL -------------- select * from foo left outer join bar3 on foo.a=bar3.a where b is null -------------- a a b 2 NULL NULL -------------- The problem is that surely the second select should return some rows, is this a bug or am I doing something dumb? I've tried this on mysql Ver 11.15 Distrib 3.23.41, for pc-linux-gnu (i686) (AMD 1 CPU) and mysql Ver 11.15 Distrib 3.23.39, for pc-linux-gnu (i686) (Intel SMP 2 CPU) Thanks for any assistance, Jon Barker --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
