BUG #5377: group by problem

Started by michel rosaabout 16 years ago3 messagesbugs
Jump to latest
#1michel rosa
mrosa@geomatic.ch

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

#2Kevin Grittner
Kevin.Grittner@wicourts.gov
In reply to: michel rosa (#1)
Re: BUG #5377: group by problem

"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

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: michel rosa (#1)
Re: BUG #5377: group by problem

"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