GROUP BY column alias?

Поиск
Список
Период
Сортировка
От Eric B. Ridge
Тема GROUP BY column alias?
Дата
Msg-id 21F0F343-AA46-4F0D-8E0F-A22149FAA511@tcdi.com
обсуждение исходный текст
Ответы Re: GROUP BY column alias?  (Scott Bailey <artacus@comcast.net>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Chris Barnes
Дата:
Сообщение: Re: Setting a table to be ignored by autovacuum
Следующее
От: Scott Bailey
Дата:
Сообщение: Re: GROUP BY column alias?