Обсуждение: unhelpful error message

Hi,
I tested it on 8.3.7 and on 8.4, but it doesn't seem to be version
bound.

steps to reproduce:

# create table sold_products (items int4, product_id int4);
# create table products (id int4, codename text);
# select sp.count, p.codename from sold_products sp join products p on sp.product_id = p.id;

shown error:
ERROR:  column "p.codename" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select sp.count, p.codename from sold_products sp join produ...
                         ^

Now - the problem is that I "forgot" name of the column, thought it will be "count", but it's "items".

the error doesn't say that I used column that doesn't exist - it says something about grouping.

I know it is related to the fact that this works:

select sp.count from sold_products sp;

but i'm not sure if it this (rather obscure) functionality is really worth the trouble.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

hubert depesz lubaczewski <depesz@depesz.com> writes:
> # create table sold_products (items int4, product_id int4);
> # create table products (id int4, codename text);
> # select sp.count, p.codename from sold_products sp join products p on sp.product_id = p.id;

> shown error:
> ERROR:  column "p.codename" must appear in the GROUP BY clause or be used in an aggregate function
> LINE 1: select sp.count, p.codename from sold_products sp join produ...
>                          ^

Per the fine manual, sp.count is another way of writing count(sp).
Does it make more sense now?

            regards, tom lane

On Thu, Jun 18, 2009 at 10:03:21AM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <depesz@depesz.com> writes:
> > # create table sold_products (items int4, product_id int4);
> > # create table products (id int4, codename text);
> > # select sp.count, p.codename from sold_products sp join products p on sp.product_id = p.id;
>
> > shown error:
> > ERROR:  column "p.codename" must appear in the GROUP BY clause or be used in an aggregate function
> > LINE 1: select sp.count, p.codename from sold_products sp join produ...
> >                          ^
>
> Per the fine manual, sp.count is another way of writing count(sp).
> Does it make more sense now?

I know why it works that way. But it took me while to figure it out, and
the select sp.count from table sp, seems to be rather hacky thing, not
really well known, and of no big use.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Per the fine manual, sp.count is another way of writing count(sp).

Wow, that seems a horrid kludge.  Is the standard responsible for that
one, or is it a PostgreSQL extension?

Could you point me at where in the fine manual this is covered?  I've
never stumbled across it in the many hours I've spent in reading the
manual.  A search didn't help much.  I poked around all the relevant
sections I could think of without success.  I even resorted to:

find -name '*.sgml' | xargs grep -i \\.count

which only kicked out:

./doc/src/sgml/ltree.sgml:
   Example: <literal>Top.Countries.Europe.Russia</literal>

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Per the fine manual, sp.count is another way of writing count(sp).

> Wow, that seems a horrid kludge.  Is the standard responsible for that
> one, or is it a PostgreSQL extension?

Actually, we inherited that from PostQUEL.  It's not in the standard.

> Could you point me at where in the fine manual this is covered?

Look under "computed fields" in the index ... looks like it's towards
the bottom of 34.4.2 in the 8.3 docs.
http://www.postgresql.org/docs/8.3/static/xfunc-sql.html#AEN40267

I had thought it was mentioned somewhere in chapter 4 as well, but
am not seeing it there right now.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Look under "computed fields" in the index ... looks like it's
> towards the bottom of 34.4.2 in the 8.3 docs.
> http://www.postgresql.org/docs/8.3/static/xfunc-sql.html#AEN40267
>
> I had thought it was mentioned somewhere in chapter 4 as well, but
> am not seeing it there right now.

It's used in an example in 34.4.2 without a lot of definition.  From
experimenting a bit, it appears that when referencing a composite data
value, any function which can take as its only parameter an instance
of that composite type can be used as though it were a field name.
This includes user functions written in any language, as well as
built-in aggregates (and presumably any other functions which accept a
composite type as the only parameter).  Is that correct?  Any
restrictions or exceptions?   (I assume that they are only allowed to
retrieve values -- it doesn't seem like it would make sense to SET a
value into such a "computed field".)

It's clearly not particular to SQL functions, so it deserves mention
outside of the context you referenced.  Chapter 4 does seem like a
good place.  Under Column References or Function Calls (or both)?

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> It's used in an example in 34.4.2 without a lot of definition.  From
> experimenting a bit, it appears that when referencing a composite data
> value, any function which can take as its only parameter an instance
> of that composite type can be used as though it were a field name.
> This includes user functions written in any language, as well as
> built-in aggregates (and presumably any other functions which accept a
> composite type as the only parameter).  Is that correct?

It goes the other way too: a column name can be used as though it were
a function.  You might want to look at the comments for and in
ParseFuncOrColumn in backend/parser/parse_func.c.

> Any
> restrictions or exceptions?   (I assume that they are only allowed to
> retrieve values -- it doesn't seem like it would make sense to SET a
> value into such a "computed field".)

Right, this is only in places where a function call would be sensible.

> It's clearly not particular to SQL functions, so it deserves mention
> outside of the context you referenced.  Chapter 4 does seem like a
> good place.  Under Column References or Function Calls (or both)?

Not sure.  I don't want to repeat a long spiel in both places ...

            regards, tom lane