left outer join bug?

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;



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

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
/ /|_/ / // /\ \/ /_/ / /__ 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/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: left outer join bug?

Hello Don,

Friday, October 24, 2003, 1:40:16 PM, you wrote:

DC> if i understand the left outer join, the following should
DC> product a 3 row result. instead i get 2.

DC> SELECT l.name, l.num, r.name, r.num
DC> FROM ltest l LEFT OUTER JOIN ltest r ON l.same = 3
DC> WHERE r.num BETWEEN l.min AND l.max
DC> AND l.name != r.name;

mysql> SELECT l.name, l.num,l.min,l.max,
-> r.num between l.min and l.max as in_range, r.name, r.num
-> FROM ltest l LEFT OUTER JOIN ltest r ON l.same = 3;
+------+------+------+------+----------+------+------+
| name | num | min | max | in_range | name | num |
+------+------+------+------+----------+------+------+
| a | 5 | 1 | 10 | 1 | a | 5 |
| a | 5 | 1 | 10 | 1 | b | 3 |
| a | 5 | 1 | 10 | 0 | c | 15 |
| b | 3 | 1 | 10 | 1 | a | 5 |
| b | 3 | 1 | 10 | 1 | b | 3 |
| b | 3 | 1 | 10 | 0 | c | 15 |
| c | 15 | 11 | 20 | 0 | a | 5 |
| c | 15 | 11 | 20 | 0 | b | 3 |
| c | 15 | 11 | 20 | 1 | c | 15 |
+------+------+------+------+----------+------+------+

As you can see, it behaves absolutely correctly.

a is in range of a and b, b is in range of a and b, c is only in range
of c. As a result of filtering on l.name != r.name it leaves only 2
rows, which is in range of each other.

--
To receive my PGP public key send letter with subject "Get PGP public
key" to weirdan@ukr.net.

WBR, D.S. AKA Weirdan mailto:weirdan@ukr.net


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org