Hi, Jan, I take it as that you are fine with how FastBit is handling null values right now. I have no objection to this view and would take this as an excuse to not change how FastBit handles nulls in the results. As I dug into the issue a bit more in the last few days, it appeared that revamping the handling of nulls in the results is a pretty big job. If there is no urgent need for revamping it, we would be glad to put our effort elsewhere -- like restoring the computation of median that was lost in the restructuring of the code.
I own you an explanation on this issue. Your patch did include the computation of median, however that implementation was using the class ibis::selected. Because the class ibis::selected is very much the same as ibis::selectClause, in the middle of December the class ibis::selected was removed. Due to an oversight on my part, I have neglected to move the computation of median from that class. I will copy the code over today and restore the computation of median. My apologies for messing this one up. John On 1/13/2010 1:10 AM, Jan Steemann wrote: > Hi, > > I have seen this discussion going on about NULL values and wanted to share my > 2 cents: > > MySQL does not include NULL values in aggregate functions such as > - COUNT(expression) > - COUNT(DISTINCT expression) > - AVG(expression) > - SUM(expression) > - MIN(expression) > - MAX(expression) > - STDDEV(expression) > - ... > Expressions that return NULL values are treated as not being existant for > these aggregate functions. > COUNT(column) will fall into the above so NULL values would be excluded when > counting. > > COUNT(*) is different as it does include NULL values, so it is more like a > row/record count, not a count on a specific column or expression (even on a > one-column table). > > I think all other databases I used in the past had about the same behaviour. > > Best regards > J > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of K. John Wu > Sent: Tuesday, January 12, 2010 8:54 PM > To: FastBit Users > Subject: Re: [FastBit-users] NULL values > > Hi, Andreas, > > Thanks for the feedback. What I interpret MySQL is doing is that it > is silently dropping the null values in the results. Internally, I > presume MySQL is actually counting the row with null value in the > result set, it only exclude the null value when computing the average. > FastBit does something slightly different, it exclude the null > values from the result set. In your MySQL example, if COUNT(*) > returns 1, it would indicate MySQL is doing the same thing as FastBit. > Guess we have some work to do to make FastBit conform to the SQL > standard in this regard. > > John > > > On 1/12/2010 11:05 AM, Andreas Streichardt wrote: >> >>> SELECT AVG(a),COUNT(a) FROM test; >>> +--------+----------+ >>> >>> | AVG(a) | COUNT(a) | >>> >>> +--------+----------+ >>> >>> | 2.0000 | 1 | >>> >>> +--------+----------+ >>> >>> This is more or less the same you just did in fastbit. >> >> And this is what i really meant ;): >> >> mysql> SELECT AVG(a),COUNT(*) FROM test; >> +--------+----------+ >> | AVG(a) | COUNT(*) | >> +--------+----------+ >> | 2.0000 | 2 | >> +--------+----------+ >> >> >>> >>> SELECT AVG(a),COUNT(a) FROM test; >>> +--------+----------+ >>> >>> | AVG(a) | COUNT(a) | >>> >>> +--------+----------+ >>> >>> | 2.0000 | 1 | >>> >>> +--------+----------+ >>> >>> This is of course a bit different as COUNT on a specific column doesn't >>> count the NULLs of this column. >>> >> >> _______________________________________________ >> FastBit-users mailing list >> [email protected] >> https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users > _______________________________________________ > FastBit-users mailing list > [email protected] > https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users > _______________________________________________ > FastBit-users mailing list > [email protected] > https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users _______________________________________________ FastBit-users mailing list [email protected] https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users
