Re: weird behaviour...

>
>
>---start of your message----
>
>
How-To-Repeat:

How-To-Repeat:

How-To-Repeat:

How-To-Repeat:




>This behaviour may be consistent at some low level of logic, but the
>results look
>weird to me...
>
>Basically I see differnt numbers of results returned betwen INNER and RIGHT
>joins between two multipart primary key tables (joining on the primary key).
>
>All fine and as you would expect...
>
>SELECT *
>A RIGHT join B
>on A.one = B.one and A.two = B.two
>= 300 rows
>
>SELECT *
>A INNER join B
>on A.one = B.one and A.two = B.two
>= 200 rows
>
>SELECT *
>A RIGHT join B
>on A.one = B.one and A.two = B.two
>WHERE B.one IS NOT NULL
>= 200 rows
>
>All fine right? but then, try and get mysql to admit any null rows...
>
>SELECT *
>A RIGHT join B
>on A.one = B.one and A.two = B.two
>WHERE B.one IS NULL # <--
>= 0 rows # < !!!
>
>Sadly this simplified version does not actually cause the bug, it just
>highlightes
>the problem in this real query below...
>
>select left(a.sccs,10), left(c.name,70),
>concat(
> max(if(b.tax_id = 1,1,0)),
> max(if(a.tax_id = 2,1,0)),
> max(if(a.tax_id = 2759,1,0)
>)) as VECTOR
>
>from b right join a
>on a.tax_id = b.tax_id
>and a.sccs = b.sccs
>
>inner join c
>on c.sccs = a.sccs
>where b.tax_id is not null
>group by a.sccs
>
>having VECTOR LIKE "111"; # Permutation vector (works fine with
>table 'a' alone)
>
>
>Thanks, Dan.
>
>
>
>
>
>
>---end of your message-------
>
>MySQL Development Team
>
>



------------------------------------------------------------ ---------
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 bugs-thread13871@lists.mysql.com
To unsubscribe, e-mail