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. Regards Tim Romano _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users