Thread: Parsing bug?

In the following query the field 'memid' is varchar(8).

Is the error message below a bug?

select substr(memid,1,1) as memtp, substr(memid,2,4) as newx
from memmast group by memtp, newx

ERROR:  column "memmast.memid" must appear in the GROUP BY clause or be used in
an aggregate function
--
Mike Nolan

> Doesn't look like a bug to me.  As far as I know only aggregation functions
> can occur in a select with group by for columns that are not in the group by
> clause.

I left out the 'count(*)' column, because the query fails with or without it.

The reason I think it may be an error is that if I include either of the
columns it works, but not if I include both of them.

To recap, the first two queries below work, the third does not:

OK:    select substr(memid,1,1) as memtp, count(*) from memmast
       group by memtp

OK:    select substr(memid,2,4) as newx, count(*) from memmast
       group by newx

FAIL:  select substr(memid,1,1) as memtp, substr(memid,2,4) as newx,
       count(*) from memmast group by memtp, newx
--
Mike Nolan

Mike Nolan <nolan@gw.tssi.com> writes:
> Is the error message below a bug?

> select substr(memid,1,1) as memtp, substr(memid,2,4) as newx
> from memmast group by memtp, newx

> ERROR:  column "memmast.memid" must appear in the GROUP BY clause or be used in
> an aggregate function

Works for me in every branch back to 7.1 ... what version are you using?

regression=# create table memmast (memid varchar(8));
CREATE
regression=# select substr(memid,1,1) as memtp, substr(memid,2,4) as newx, count(*) from memmast group by memtp, newx;
 memtp | newx | count
-------+------+-------
(0 rows)


            regards, tom lane

> Works for me in every branch back to 7.1 ... what version are you using?

7.4.1, but I figured out what I did wrong.  The alias for the first
column turns out to be the same as the name of another column in the table.
--
Mike Nolan