27 січня 2010 р. 19:01 Matthew Wakeling <matthew(at)flymine(dot)org> написав:
> On Wed, 27 Jan 2010, Віталій Тимчишин wrote:
>
>> How about SELECT SUM (case when id > 1200000 and id < 1210000 then 1 end)
>> from tbl_tracker;
>>
>
> That is very interesting.
>
>
> * All the functions should be noop for null input
>>
>
> Alas, not true for COUNT(*), AVG(), etc.
>
> select avg(b), count(b), count(*) from (values (2),(null))a(b)
gives (2.0, 1, 2) for me, so AVG is in game. Sure, it won't work for
count(*), but optimizer already knows which aggregates are strict and which
are not, so no new information is needed.
Best regards, Vitalii Tymchyshyn