On 3/9/2010 10:56 AM, Scott Hess wrote:
> On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano<[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
