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

Reply via email to