List:General Discussion« Previous MessageNext Message »
From:Benjamin Pflugmann Date:September 17 2002 8:51pm
Subject:Re: aggregate functions producing bad data
View as plain text  
Hi.

Could you run the following query:

SELECT	  IF(call_end - call_start > 5, id, NULL) AS long,
	  IF(call_end - call_start <= 5, id, NULL) AS short,
	  COUNT(*)
FROM	  t_calllog_calls
GROUP BY  long, short

Given that I have not made any mistake, that should list all rows
which do not "behave" as expected for whatever reason. From your
description, you expect there to be two rows:

	long	short
	  0	non-0
	non-0	  0

But you should get some additional row(s), which account for the
difference you observe. Once you know in which way the condition fails
(e.g. both comparisons resulting in NULL), you can search for these
rows:

SELECT	  IF(call_end - call_start > 5, id, NULL) AS long,
	  IF(call_end - call_start <= 5, id, NULL) AS short,
	  *
FROM	  t_calllog_calls
HAVING	  ISNULL(short) AND ISNULL(long)

(of course you may have to change this accordingly).


One of may best bets would be that call_end and call_start are of some
data type for which your comparison results in NULL due to some
illegal value or because the comparison is not defined or whatever.

Please report any findings you get.

Regards,

	Benjamin.


PS: People, please learn to quote. There is no reason to quote 60
    lines of mail footers!


On Tue 2002-09-17 at 12:37:59 -0700, runger@stripped 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
[...]

-- 
benjamin-mysql@stripped
Thread
aggregate functions producing bad dataRichard Unger17 Sep
  • Re: aggregate functions producing bad dataBrian Reichert17 Sep
    • Re: aggregate functions producing bad dataDan Nelson17 Sep
  • Re: aggregate functions producing bad dataGelu Gogancea17 Sep
  • Re: aggregate functions producing bad dataGelu Gogancea17 Sep
    • Re: aggregate functions producing bad datamtoth18 Sep
  • Re: aggregate functions producing bad dataGelu Gogancea17 Sep
RE: aggregate functions producing bad dataRichard Unger17 Sep
RE: aggregate functions producing bad dataRichard Unger17 Sep
  • RE: aggregate functions producing bad datamtoth17 Sep
RE: aggregate functions producing bad dataRichard Unger17 Sep
  • Re: aggregate functions producing bad dataJay17 Sep
    • re: Re: aggregate functions producing bad dataVictoria Reznichenko18 Sep
RE: aggregate functions producing bad dataRichard Unger17 Sep
RE: aggregate functions producing bad dataRichard Unger17 Sep
  • Re: aggregate functions producing bad dataBenjamin Pflugmann18 Sep
RE: aggregate functions producing bad dataGerben Gieling18 Sep