Thread

Is this legal???

Ronald Baljeu <rjb@xs4all.nl> — 1998-04-08T14:39:53Z

Hi hackers,

I have an SQL-question and a related core dump :-)

> create table test
> (
>    col1   text,
>    col2   text,
>    col3   text
> );
> CREATE
> insert into test values ('one', 'two', 'three');
> INSERT 96299 1
> select col1, count(*) from test group by col1;
> col1|count
> ----+-----
> one |    1
> (1 row)

Now I am going to do something illegal:

> select col1, col3, count(*) from test group by col1;
> ERROR:  parser: illegal use of aggregates or non-group column in target list

Obviously, I did not use the aggregate correctly, but look at the last
bit of this error message. If I understand this correctly, all the columns
in the target list must also be stated in the grouping list. In a way,
this makes sense, because the extra columns in the target list
would be undefined: these columns would originate from a random row (tuple)
per group.

My question: is the following query legal?

> select col1, col3 from test group by col1;
> col1|col3 
> ----+-----
> one |three
> (1 row)

Shouldn't Postgres complain about 'col3'? It is not in the grouping list.

What actually brought me to that question is a core dump in a (faulty)
query which, after isolating the problem, looks like this:

> select col1, col3 from test where 1 = 1 group by col1;
> FATAL:  unrecognized data from the backend.  It probably dumped core.
> FATAL:  unrecognized data from the backend.  It probably dumped core.

If I delete the '1 = 1' or replace 'col3' by 'col2' the query produces
normal results. I'm running the snapshot of April 6 on Linux kernel 2.0.33.

Cheers,
Ronald