RE: MIN/MAX( IF( ...) ) problems: min bigger than max
Is it fixed only in MySQL 4.x, or will it be in the next point release of 3.x as well? Cheers, Rich > -Original Message- >Thanks for bug report! > >If NULL is present (t_calllog_calls.call_end - >t_calllog_calls.call_start < 5) the result_type is changed from >int to char. > >It was fixed. - 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
aggregate functions producing bad data
My query: SELECT count(id), count( IF(call_end - call_start > 5, id, NULL) ), count( IF(call_end - call_start <= 5, id, NULL) ) FROM t_calllog_calls; My result: 1994 1956 35 However, 1956 + 35 != 1994. Running MySQL 3.23.49-nt Cheers, Rich - 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: aggregate functions producing bad data
I _thought_ it was the fault of the aggregate function, but now I'm really confused! When I produce them as 3 separate queries, I still get numbers that don't add up: mysql> select count(id) from t_calllog_calls; +---+ | count(id) | +---+ | 1994 | +---+ 1 row in set (0.00 sec) mysql> select count(id) from t_calllog_calls where call_end - call_start > 5; +---+ | count(id) | +---+ | 1956 | +---+ 1 row in set (0.00 sec) mysql> select count(id) from t_calllog_calls where call_end - call_start <= 5; +---+ | count(id) | +---+ |35 | +---+ -Original Message- From: Richard Unger Sent: Tuesday, September 17, 2002 12:00 PM To: '[EMAIL PROTECTED]' Subject: aggregate functions producing bad data My query: SELECT count(id), count( IF(call_end - call_start > 5, id, NULL) ), count( IF(call_end - call_start <= 5, id, NULL) ) FROM t_calllog_calls; My result: 1994 1956 35 However, 1956 + 35 != 1994. Running MySQL 3.23.49-nt Cheers, Rich - 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: aggregate functions producing bad data
That would just give me 1994 for all 3 columns of output. What I want to know is how many calls were longer than 5 seconds, how many were <= 5 seconds, and the total number of calls. Cheers, Rich > -Original Message- > From: Gelu Gogancea [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, September 17, 2002 12:08 PM > To: Richard Unger; [EMAIL PROTECTED] > Subject: Re: aggregate functions producing bad data > > > Hi, > I think you should try to use 0 instead of NULL > > Regards, > > Gelu > _ > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > - Original Message - > From: "Richard Unger" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Tuesday, September 17, 2002 9:59 PM > Subject: aggregate functions producing bad data > > > My query: > > SELECT > count(id), > count( IF(call_end - call_start > 5, id, NULL) ), > count( IF(call_end - call_start <= 5, id, NULL) ) > FROM > t_calllog_calls; > > My result: > > 1994 > 1956 > 35 > > However, 1956 + 35 != 1994. > > Running MySQL 3.23.49-nt > > Cheers, > Rich > > - > 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 <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > - 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: aggregate functions producing bad data
id is the primary key. There are no entries in the DB where id=NULL Cheers, Rich > -Original Message- > From: Brian Reichert [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, September 17, 2002 12:16 PM > To: Richard Unger > Cc: [EMAIL PROTECTED] > Subject: Re: aggregate functions producing bad data > > > On Tue, Sep 17, 2002 at 11:59:42AM -0700, Richard Unger wrote: > > My query: > > > > SELECT > > count(id), > > count( IF(call_end - call_start > 5, id, NULL) ), > > count( IF(call_end - call_start <= 5, id, NULL) ) > > FROM > > t_calllog_calls; > > > > My result: > > > > 1994 > > 1956 > > 35 > > > > However, 1956 + 35 != 1994. > > > > Running MySQL 3.23.49-nt > > One thing I'd check: what happens if t_calllog_calls.id is NULL? > > > Cheers, > > Rich > > -- > Brian 'you Bastard' Reichert <[EMAIL PROTECTED]> > 37 Crystal Ave. #303 Daytime number: (603) 434-6842 > Derry NH 03038-1713 USA Intel > architecture: the left-hand path > - 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: aggregate functions producing bad data
> -Original Message- > From: Dan Nelson [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, September 17, 2002 12:31 PM > To: Brian Reichert > Cc: Richard Unger; [EMAIL PROTECTED] > Subject: Re: aggregate functions producing bad data > > > In the last episode (Sep 17), Brian Reichert said: > > On Tue, Sep 17, 2002 at 11:59:42AM -0700, Richard Unger wrote: > > > My query: > > > > > > SELECT > > > count(id), > > > count( IF(call_end - call_start > 5, id, NULL) ), > > > count( IF(call_end - call_start <= 5, id, NULL) ) > > > FROM > > > t_calllog_calls; > > > > > > My result: > > > > > > 1994 > > > 1956 > > > 35 > > > > > > However, 1956 + 35 != 1994. > > > > > > Running MySQL 3.23.49-nt > > > > One thing I'd check: what happens if t_calllog_calls.id is NULL? > > Also check if call_end or call_start are NULL; that will > cause both IFs > to fail. Don't you love NULLs :) > Ooh, good suggestion. I hadn't thought of that! ...but no, none of the call_start or call_end fields are NULL. Yeah, I love nulls :) Cheers, Rich - 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: aggregate functions producing bad data
mysql> Select count(id) from t_calllog_calls where id IS NULL; +---+ | count(id) | +---+ | 0 | +---+ 1 row in set (0.00 sec) > -Original Message- > From: mtoth [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, September 17, 2002 12:39 PM > To: Richard Unger > Cc: [EMAIL PROTECTED] > Subject: RE: aggregate functions producing bad data > > > what does > Select count(id) from t_calllog_calls where id IS NULL > give you? > > > On Tue, 17 Sep 2002, Richard Unger wrote: > > > That would just give me 1994 for all 3 columns of output. > What I want to know is how many calls were longer than 5 > seconds, how many were <= 5 seconds, and the total number of calls. > > > > Cheers, > > Rich > > > > > > > -Original Message- > > > From: Gelu Gogancea [mailto:[EMAIL PROTECTED]] > > > Sent: Tuesday, September 17, 2002 12:08 PM > > > To: Richard Unger; [EMAIL PROTECTED] > > > Subject: Re: aggregate functions producing bad data > > > > > > > > > Hi, > > > I think you should try to use 0 instead of NULL > > > > > > Regards, > > > > > > Gelu > > > _________ > > > G.NET SOFTWARE COMPANY > > > > > > Permanent e-mail address : [EMAIL PROTECTED] > > > [EMAIL PROTECTED] > > > - Original Message - > > > From: "Richard Unger" <[EMAIL PROTECTED]> > > > To: <[EMAIL PROTECTED]> > > > Sent: Tuesday, September 17, 2002 9:59 PM > > > Subject: aggregate functions producing bad data > > > > > > > > > My query: > > > > > > SELECT > > > count(id), > > > count( IF(call_end - call_start > 5, id, NULL) ), > > > count( IF(call_end - call_start <= 5, id, NULL) ) > > > FROM > > > t_calllog_calls; > > > > > > My result: > > > > > > 1994 > > > 1956 > > > 35 > > > > > > However, 1956 + 35 != 1994. > > > > > > Running MySQL 3.23.49-nt > > > > > > Cheers, > > > Rich > > > > > > > - > > > 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 > <[EMAIL PROTECTED]> > > > To unsubscribe, e-mail > > > <[EMAIL PROTECTED]> > > > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > > > > > > > > > > > > > > > > > - > > 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 <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > > > > > > - 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FW: aggregate functions producing bad data
MySQL <-- to defeat the spam filter -Original Message- From: Richard Unger Sent: Tuesday, September 17, 2002 1:46 PM To: 'Dan Nelson' Cc: [EMAIL PROTECTED] Subject: RE: aggregate functions producing bad data Thanks, I figured it out. It turns out there were some calls for which call_end IS NULL. I had originally eliminated that possiblity because I mistakenly checked if call_end = NULL (instead of IS). Thanks for pointing me in the right direction. Cheers, Rich > -Original Message- > From: Dan Nelson [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, September 17, 2002 1:13 PM > To: Richard Unger > Cc: [EMAIL PROTECTED] > Subject: Re: aggregate functions producing bad data > > > In the last episode (Sep 17), Richard Unger said: > > From: Dan Nelson [mailto:[EMAIL PROTECTED]] > > > In the last episode (Sep 17), Brian Reichert said: > > > > One thing I'd check: what happens if > t_calllog_calls.id is NULL? > > > > > > Also check if call_end or call_start are NULL; that will > > > cause both IFs > > > to fail. Don't you love NULLs :) > > > > Ooh, good suggestion. I hadn't thought of that! > > > > ...but no, none of the call_start or call_end fields are NULL. > > Next step is to do a "REPAIR TABLE t_calllog_calls", in case > your index > got damaged at some point. The next step after that is to make a copy > of your table, and remove records one by one until your total works, > then find out what's special about the last record you removed. > > -- > Dan Nelson > [EMAIL PROTECTED] > - 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
