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 the
alias. 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
