Обсуждение: timestamp group by bug???

Help - I'm not sure if this is a bug, but I wouldn't call it a feature! :-)

Here are my attempts at selecting out the counts for the number of records for
each particular day of the week. I'd like to be able to show the day of the
week sorted in the order of the days in the week. As you can see, I can select
out the information and print it in non-sorted order and I can sort it as
desired if I use the number of the day of the week, but I can't seem to print
it sorted as desired with the day (eg., MON, TUE,...) shown.

psql=# SELECT to_char(mytimestamp,'DY'),COUNT(*) FROM mytable GROUP BY
to_char(mytimestamp,'DY') ORDER BY to_char(mytimestamp,'DY');
 to_char | count
---------+-------
 FRI     |    21
 MON     |    23
 SAT     |    23
 SUN     |    25
 THU     |    22
 TUE     |    22
 WED     |    22
(7 rows)

psql=# SELECT to_char(mytimestamp,'DY'),COUNT(*) FROM mytable GROUP BY
to_char(mytimestamp,'DY') ORDER BY to_char(mytimestamp,'D');
ERROR:  column "mytable.mytimestamp" must appear in the GROUP BY clause or be
used in an aggregate function
psql=# SELECT to_char(mytimestamp,'DY'),COUNT(*) FROM mytable GROUP BY
to_char(mytimestamp,'D') ORDER BY to_char(mytimestamp,'D');
ERROR:  column "mytable.mytimestamp" must appear in the GROUP BY clause or be
used in an aggregate function
psql=# SELECT to_char(mytimestamp,'D'),COUNT(*) FROM mytable GROUP BY
to_char(mytimestamp,'D') ORDER BY to_char(mytimestamp,'D');
 to_char | count
---------+-------
 1       |    25
 2       |    23
 3       |    22
 4       |    22
 5       |    22
 6       |    21
 7       |    23
(7 rows)

psql=# SELECT to_char(mytimestamp,'D'),to_char(mytimestamp,'DY'),COUNT(*) FROM
mytable GROUP BY to_char(mytimestamp,'D') ORDER BY to_char(mytimestamp,'D');
ERROR:  column "mytable.mytimestamp" must appear in the GROUP BY clause or be
used in an aggregate function
psql=#

Thanks for your help,
Celia McInnis

"Celia McInnis" <celia@drmath.ca> writes:
> Help - I'm not sure if this is a bug, but I wouldn't call it a feature! :-)

Hardly a bug ... you told it to order by a textual value, why
would you expect a non-alphabetical sort ordering?

I would suggest ordering by EXTRACT(DOW FROM mytimestamp).
You'll probably have to list that as a second GROUP BY item in order
to make Postgres happy with the query.

            regards, tom lane

On Mon, 21 Mar 2005 12:56:52 -0500, Tom Lane wrote
> "Celia McInnis" <celia@drmath.ca> writes:
> > Help - I'm not sure if this is a bug, but I wouldn't call it a feature! :-
)
>
> Hardly a bug ... you told it to order by a textual value, why
> would you expect a non-alphabetical sort ordering?

I do since the "D" option gives a single digit day of week number (ie., text
sort would give identical ordering to text sort).

>
> I would suggest ordering by EXTRACT(DOW FROM mytimestamp).
> You'll probably have to list that as a second GROUP BY item in order
> to make Postgres happy with the query.
>
>             regards, tom lane

Thanks, Tom - the above statement is what pointed me in the correct
direction - that I needed to group by BOTH the name of the day and the number
of the day. Whether that number was expressed as a text field (as I did) or
as a numeical value (as you suggested) was irrelevant.

For example the following works just fine:

SELECT to_char(mytimestamp,'D'),to_char(mytimestamp,'DY'),COUNT(*) FROM
mytable GROUP BY to_char(mytimestamp,'D'),to_char(mytimestamp,'DY') ORDER BY
to_char(mytimestamp,'D');

rather than my original error:

SELECT to_char(mytimestamp,'D'),to_char(mytimestamp,'DY'),COUNT(*) FROM
mytable GROUP BY to_char(mytimestamp,'D') ORDER BY to_char(mytimestamp,'D');

By the way, as a novice, I am/was a little surprised at HAVING to do this,
since both things in the grouping are just simple functions of the same
underlying table variable mytimestamp.

It is also worth noting that some other databases do not require this double
grouping (though experiments show that they mess up in other ways concerning
groupings and orderings, so maybe they should have done as postgres has!) -
notably, the following works in mysql:

select date_format(mytimestamp,'%w'),date_format(mytimestamp,'%W'),count(*)
from mytable group by date_format(mytimestamp,'%w') order by date_format
(mytimestamp,'%w);

Thanks very much, Celia McInnis

"Celia McInnis" <celia@drmath.ca> writes:
> By the way, as a novice, I am/was a little surprised at HAVING to do this,
> since both things in the grouping are just simple functions of the same
> underlying table variable mytimestamp.

The parser cannot be expected to understand that they are equivalent
functions though.  As a counterexample imagine that one extracts the
month and the other the day-of-week; grouping by those two cases would
not yield equivalent results.  When they are equivalent, the extra
GROUP BY clause is redundant, but still needed to make the query pass
the parser's spec-driven sanity checking.

> notably, the following works in mysql:

MySQL is hardly a reliable reference for correct SQL behavior ;-).
My guess is that they are simply failing to test that the query has
well-defined grouping behavior at all.

            regards, tom lane