Обсуждение: null answer - how?

select coalesce(sum(deltafromoctets),0)
  from trans,stats
 where stats_id=stats.id
   and (timeslicet < '1:05' OR timeslicet > '6:05')

returns a number (9188191930), whereas

select coalesce(sum(deltafromoctets),0)
  from trans,stats
 where stats_id=stats.id
   and (timeslicet < '1:05' OR timeslicet > '6:05')
   and timesliced < '01-May-2001'

prints 0 (or null without the coalesce) as does

select coalesce(sum(deltafromoctets),0)
  from trans,stats
 where stats_id=stats.id
   and timesliced < '01-May-2001'

How can this be?

select * from stats where timesliced is null or timeslicet is null
select * from trans where deltafromoctets is null

both retun 0 rows.

stats_id and stats.id are "not null"
timesliced date, timeslicet time, deltrafromoctets bigint.

Any hints?

Cheers,

Patrick

On Wed, 19 Sep 2001, Patrick Welche wrote:

> select coalesce(sum(deltafromoctets),0)
>   from trans,stats
>  where stats_id=stats.id
>    and (timeslicet < '1:05' OR timeslicet > '6:05')
>
> returns a number (9188191930), whereas
>
> select coalesce(sum(deltafromoctets),0)
>   from trans,stats
>  where stats_id=stats.id
>    and (timeslicet < '1:05' OR timeslicet > '6:05')
>    and timesliced < '01-May-2001'
>
> prints 0 (or null without the coalesce) as does
>
> select coalesce(sum(deltafromoctets),0)
>   from trans,stats
>  where stats_id=stats.id
>    and timesliced < '01-May-2001'
>
> How can this be?
>
> select * from stats where timesliced is null or timeslicet is null
> select * from trans where deltafromoctets is null
>
> both retun 0 rows.

Are there no rows with timesliced<'01-May-2001'?  I think in that
case sum() gives you one row with NULL.

On Wed, Sep 19, 2001 at 11:34:56AM -0700, Stephan Szabo wrote:
>
> Are there no rows with timesliced<'01-May-2001'?  I think in that
> case sum() gives you one row with NULL.

Thank you for the clue! *slaps forehead*

transatlantic=# select min(stats_id),max(stats_id) from trans;
 min  |  max
------+-------
 5415 | 12532
(1 row)

transatlantic=# select min(id),max(id) from stats where timesliced<'01-May-2001';
 min | max
-----+------
  10 | 2345
(1 row)

Cheers,

Patrick