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 [email protected].
  
  WBR, D.S. AKA Weirdan                         mailto:[email protected]


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