Move this:
tries.status IN('running','waitkill','preemption'
to a where clause and remove it from the join.
Never actually tried to do a IN in a join before. I personally don't think
it should work.
Donny
> -----Original Message-----
> From: Sergei Golubchik [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 23, 2004 2:13 PM
> To: Dilipan Sebastiampillai
> Cc: [EMAIL PROTECTED]
> Subject: Re: 4.0.18 -> 4.1.7 LEFT JOIN problem
>
> Hi!
>
> On Nov 23, Dilipan Sebastiampillai wrote:
> > >
> > >
> > >Hi!
> > >
> > >On Nov 22, Dilipan Sebastiampillai wrote:
> > >
> > >
> > >>I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql
> query
> > >>using LEFT JOIN gives me different result.
> > >>The result from 4.0.18 does s real LEFT JOIN but 4.1.7 differs
> > >>sometimes and I don't want that.
> > >>
> > >>I think it comes from an 'optimization' .... how can I make a query
> > >>without using the LEFT JOIN optimization of MySQL 4.1.7 ?
> > >
> > >What is the query ?
> > >Can you provide a repeatable test case ?
> > >
> > the answer is amazingly wrong !
> > have a look :
> >
> > mysql> SELECT hosts.name,hosts.hostId, tries.hostId, tries.status FROM
> > hosts LEFT JOIN tries ON tries.hostId=hosts.hostId AND tries.status
> > IN('running','waitkill','preemption') LIMIT 20;
> > +---------+--------+--------+---------+
> > | name | hostId | hostId | status |
> > +---------+--------+--------+---------+
> > | chimp13 | 1530 | 1393 | running |
> > | chimp13 | 1530 | 1485 | running |
> > | chimp13 | 1530 | 1418 | running |
> > | chimp13 | 1530 | 1499 | running |
>
> I agree that it doesn't look right.
> But the query alone is not enough for me to repeat the bug.
> I need also both tables hosts and tries.
> If they are big, you may try to remove unrelated rows, or create a
> completely independent test case. Actually you can even upload big
> tables if you don't want to spend time on a test case.
>
> But only with a repeatable test case you can make sure that the bug
> won't be present in 4.1.8.
>
> Regards,
> Sergei
>
> --
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
> /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
> <___/ www.mysql.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
