Обсуждение: BUG #5377: group by problem

The following bug has been logged online:

Bug reference:      5377
Logged by:          michel rosa
Email address:      mrosa@geomatic.ch
PostgreSQL version: 8.3.10
Operating system:   debian
Description:        group by problem
Details:

Hello,

I have the following problem

select to_char(upload_date, 'yyyy-mm-dd'),cost,count(cost) from
evadeo.tileshop
where status=2
and source=3
and upload_date>='2010-03-01'
and upload_date<='2010-03-02'
group by to_char(upload_date, 'yyyy-mm-dd'),cost
order by to_char(upload_date, 'yyyy-mm-dd'),cost

give as result

  to_char   | cost | count
------------+------+-------
 2010-03-01 |    0 |   345
 2010-03-01 |  0.5 |    38
 2010-03-01 |    1 |     9
 2010-03-01 |  1.5 |   164
 2010-03-02 |    0 |   837
 2010-03-02 |  0.5 |   136
 2010-03-02 |  1.5 |   138

but when upload_date<='2010-03-03', I get extras rows for 2010-03-02

select to_char(upload_date, 'yyyy-mm-dd'),cost,count(cost) from
evadeo.tileshop
where status=2
and source=3
and upload_date>='2010-03-01'
and upload_date<='2010-03-03'
group by to_char(upload_date, 'yyyy-mm-dd'),cost
order by to_char(upload_date, 'yyyy-mm-dd'),cost

 to_char   | cost | count
------------+------+-------
 2010-03-01 |    0 |   345
 2010-03-01 |  0.5 |    38
 2010-03-01 |    1 |     9
 2010-03-01 |  1.5 |   164
 2010-03-02 |    0 |  1217
 2010-03-02 |  0.5 |   287
 2010-03-02 |    1 | 11894
 2010-03-02 |  1.5 |   379
 2010-03-02 |  2.0 |     7
 2010-03-02 |  3.0 |     3
 2010-03-03 |    0 |  7393
 2010-03-03 |  0.5 |    85
 2010-03-03 |  1.5 |   152


do you have an explication ?
Is it really a bug ?

thanks

"michel rosa" <mrosa@geomatic.ch> wrote:

> I get extras rows for 2010-03-02

I'm not sure I understand your concern.
Is it that the result set includes these rows?:

>   to_char   | cost | count
> ------------+------+-------
>  2010-03-02 |  2.0 |     7
>  2010-03-02 |  3.0 |     3

What do you get from?:

select * from evadeo.tileshop
where status=2
and source=3
and upload_date = '2010-03-02'
and cost > 1.5;

-Kevin

"michel rosa" <mrosa@geomatic.ch> writes:
> ...
> and upload_date<='2010-03-02'
> give as result
>  2010-03-02 |    0 |   837
>  2010-03-02 |  0.5 |   136
>  2010-03-02 |  1.5 |   138

> but when upload_date<='2010-03-03', I get extras rows for 2010-03-02

Is the upload_date column actually a date?  I think maybe it's a
timestamp and the "extra" rows correspond to times after midnight.
Your cutoff conditions are being interpreted as
    upload_date<='2010-03-02 00:00:00'
etc.

            regards, tom lane