List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:April 12 2002 4:57pm
Subject:Re: BUG using left join.
View as plain text  
You are passing a string to compare to a date.  If you do this,  pass it 
in the string format.
Other wise  " and t_boxdaily.boxdate < 199901010000 "

Steve Briant wrote:

>Hi,
>
>I think I may have spotted a bug in connection with the LEFT JOIN statement.
>
>The following SQL
>
>select name, sum(repurchasedunits)
>from t_fund left join t_boxdaily on t_fund.fundid = t_boxdaily.fundid
>where t_fund.fundid = 'BB01'
>group by name
>
>produces the output as below:
>
>name	max(boxdate)	sum(repurchasedunits)
>----	------------	---------------------
>Marleborough Fund Managers (BB01)	2002-04-12	0
>
>(1 row(s) affected)
>
>
>However, a slight change to the join as below:
>
>select name, sum(repurchasedunits)
>from t_fund left join t_boxdaily on t_fund.fundid = t_boxdaily.fundid
>where t_fund.fundid = 'BB01' and t_boxdaily.boxdate < '19990101'
>group by name
>
>produces this output:
>
>name	max(boxdate)	sum(repurchasedunits)
>----	------------	---------------------
>
>(0 row(s) affected)
>
>
>
>As detailed in the release notes, this join should still give a match as per
>the first example shouldn't it ? What is causing it not to is that I have no
>records in t_boxdaily that have a boxdate previous to 1st January 1999.
>
>I am running MySQL version 3.23.37 under Windows 2000.
>
>Regards
>
>Steve Briant.
>
>
>
>---------------------------------------------------------------------
>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 <mysql-thread105973@stripped>
>To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


Thread
BUG using left join.Steve Briant12 Apr
  • RE: BUG using left join.BJ Phillips12 Apr
  • Re: BUG using left join.Gerald Clark12 Apr
    • Re: BUG using left join.ds15 Apr