Re: left outer join bug?

Don Caldwell writes:
> if i understand the left outer join, the following should
> product a 3 row result. instead i get 2. 
> 
> 
> -- mysql --version
> -- mysql  Ver 12.21 Distrib 4.0.14, for sun-solaris2.8 (sparc)
> 
> -- the table
> CREATE TABLE IF NOT EXISTS ltest (
>   name varchar(32),
>   num integer,
>   same integer,
>   min integer,
>   max integer
> );
> 
> DELETE FROM ltest;
> 
> -- 'a' 'b' in range 'c' out of range
> INSERT INTO ltest VALUES('a', 5,  3, 1,  10);
> INSERT INTO ltest VALUES('b', 3,  3, 1,  10);
> INSERT INTO ltest VALUES('c', 15, 3, 11, 20);
> 
> -- i expect 2 rows in a regular join but 3 in a left outer join
> -- here i expect 3 rows but get only 2
> SELECT l.name, l.num, r.name, r.num
> FROM ltest l LEFT OUTER JOIN ltest r ON l.same = 3
> WHERE r.num BETWEEN l.min AND l.max
> AND l.name != r.name;
> 

Hi!

The above is not a bug.

The reason why you get only two rows is due to eh last constraint. 

If you remove it, you get:


name    num     name    num
a       5       a       5
b       3       a       5
a       5       b       3
b       3       b       3
c       15      c       15


and as you can see l.name = r.name in 3 rows.

-- 

Sincerely,

-- 
For technical support contracts, go to https://order.mysql.com/?ref=msmi
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Sinisa Milivojevic <[email protected]>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB
/_/  /_/\_, /___/\___\_\___/   Fulltime Developer and Support Coordinator
       <___/   www.mysql.com   Larnaca, Cyprus


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]