On Tue, Mar 9, 2010 at 10:46 AM, Tim Romano <tim.rom...@yahoo.com> wrote: > On 3/9/2010 10:56 AM, Scott Hess wrote: >> On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano<tim.rom...@yahoo.com> wrote: >>> Of these three: >>> >>> select c from T where 1=2 // returns 0 rows >>> select min(c) from T where 1=2 // returns 1 row >>> select min(88,99) from T where 1=2 // returns 0 rows >>> >>> the only case that "threw" me is the second one, where a row is returned >>> despite a WHERE condition that should yield an empty set (or so I thought). >>> >> The first and last cases will run for each row in the WHERE clause. >> The second case is aggregating over all c, and will always return one >> row, even if the WHERE clause selects many rows, so it is consistent >> for it to return one row if the WHERE clause selects for no rows. >> It's as if you coded it like this: >> >> SELECT min(SELECT c FROM t WHERE ...) >> >> meaning the minimum of that set of inputs, and if that set is empty, >> there is no minimum, so you get a result of NULL, but not no result, >> if you see what I mean. > > Thanks for the replies, Scott and Igor and Pavel and Puneet. What I see > is that an aggregate function needs to partner with the GROUP BY clause > in order for nulls to be removed from the aggregated set. > > select min(c) from T where 1=2 > > returns 1 row that contains <null> despite the presence of the aggregate > function > > and so > > select min(c) is null from T where 1 =2 > > returns true (1). But > > select min(c) from T where 1=2 > /group by/ foo > > returns no rows, presumably because the null value was removed from the > aggregated set.
No, the null value is not removed, group-by groups the results, and you have no results, so there are no groups, so min(c) never comes up at all. When you run min(c) across the entire table, the table _does_ exist, though it might be empty, so min(c) has to have a result, and since there are no c, the result has to be NULL. [BTW, if you had a group-by, but no values are present for column c in that group of rows, min(c) would also be NULL for that group.] NULL means that there was a result, and the result was not a value, as distinct from not having a result at all. -scott _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
