Re: weird behaviour...
am 01.03.2003 13:17:52 von Sinisa MilivojevicDan Bolser writes:
> >
> >SELECT *
> >A RIGHT join B
> >on A.one = B.one and A.two = B.two
> >WHERE B.one IS NULL # <--
> >= 0 rows # < !!!
Why would this query return rows ??
It actually can not return any rows as the ON conditions filter out
all rows containing NULL in either A.one or B.one.
> >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.
> >
Regarding the above query, omit "HAVING" and see if there are any rows
containing VECTOR = "111".
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com
Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/
------------------------------------------------------------ ---------
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-thread13872@lists.mysql.com
To unsubscribe, e-mail