Group by bug?

hi,

wd_test=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------+-------------------------------------------------
id | integer | not null default nextval('t1_id_seq'::regclass)
tag | text |

wd_test=# select * from t1;
id | tag
----+-----
1 | a
2 | a
3 | b
4 | c
5 | b
(5 rows)

---- this sql will not group by the case result.
wd_test=# select case t1.tag when 'a' then '1' else '0' end as tag,
count(*) from t1 group by tag;
tag | count
-----+-------
0 | 1
0 | 2
1 | 2
(3 rows)

---- this sql will group by the case result.
wd_test=# select case t1.tag when 'a' then '1' else '0' end as ttag,
count(*) from t1 group by ttag;
ttag | count
------+-------
0 | 3
1 | 2
(2 rows)

2012/12/28 wd <wd@wdicc.com>

hi,

wd_test=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------+-------------------------------------------------
id | integer | not null default nextval('t1_id_seq'::regclass)
tag | text |

wd_test=# select * from t1;
id | tag
----+-----
1 | a
2 | a
3 | b
4 | c
5 | b
(5 rows)

---- this sql will not group by the case result.
wd_test=# select case t1.tag when 'a' then '1' else '0' end as tag,
count(*) from t1 group by tag;

here the group by key tag is t1.tag,not the tag int the select list

tag | count
-----+-------
0 | 1
0 | 2
1 | 2
(3 rows)

---- this sql will group by the case result.
wd_test=# select case t1.tag when 'a' then '1' else '0' end as ttag,
count(*) from t1 group by ttag;

here the ttag is the select list ttag,it is equal with group by 1.

ttag | count
------+-------
0 | 3
1 | 2
(2 rows)

http://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-GROUPBY

GROUP BY will condense into a single row all selected rows that share
the same values for the grouped expressions. expression can be an input
column name, or the name or ordinal number of an output column (SELECT list
item), or an arbitrary expression formed from input-column values. *In
case of ambiguity, a GROUP BY name will be interpreted as an
input-column name rather than an output column name.*

so it is not a bug.