Eric B. Ridge wrote:
> Maybe I'm getting too old to SQL anymore, but I ran across something yesterday in a machine generated query that took
meover an hour to figure out.
>
> Here's a little testcase. Maybe somebody can explain why the last "Not Expected" case does what it does.
>
> select version();
> PostgreSQL 8.4.1 on i386-apple-darwin10.0.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc.
build5646), 64-bit
>
> create table foo(day timestamp);
> insert into foo values (now());
> insert into foo values (now());
>
> Expected:
> select day, count(*) from foo group by day;
> day | count
> ----------------------------+-------
> 2010-02-18 15:41:37.335357 | 1
> 2010-02-18 15:41:39.471746 | 1
> (2 rows)
>
> Expected:
> select day::date, count(*) from foo group by day;
> day | count
> ------------+-------
> 2010-02-18 | 1
> 2010-02-18 | 1
> (2 rows)
>
> Expected:
> select day::date, count(*) from foo group by day::date;
> day | count
> ------------+-------
> 2010-02-18 | 2
> (1 row)
>
> Expected:
> select day::date as bar, count(*) from foo group by bar;
> bar | count
> ------------+-------
> 2010-02-18 | 2
> (1 row)
>
> Not Expected:
> select day::date as day, count(*) from foo group by day;
> day | count
> ------------+-------
> 2010-02-18 | 1
> 2010-02-18 | 1
> (2 rows)
>
> Note in the last case, the "day" column is aliased as "day", but the group by using the physical "day" column, not
thealias. That boggles my mind, especially when you consider the case above it, where "day" is aliased as "bar" and
groupingby "bar" works as expected.
>
> eric
I'm not sure why you would be surprised by that behavior. You are
grouping by a timestamp, so any microsecond difference will be a new group.
If you want to make that work try:
SELECT day::date, --no need to alias as same name
COUNT(*) FROM foo
GROUP BY day::date;
Scott
