Re: Group by bug?

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.