From: | Boszormenyi Zoltan <zb(at)cybertec(dot)at> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Cc: | Hans-Juergen Schoenig <hs(at)cybertec(dot)at> |
Subject: | GROUP BY bug or feature? |
Date: | 2009-10-26 21:46:20 |
Message-ID: | 4AE618AC.6040306@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
we have come across a problem where we need an inverted index,
an array of IDs ordered by another condition. We came up
with this scheme:
-- final inverted index
CREATE TABLE product.t_product_inv (
word text primary key not null,
ids bigint[]
);
-- transition table, "word" contains a single lexeme
-- from an original table's "description" field
CREATE TABLE product.t_product_inv0 (
word text not null,
id bigint not null,
price numeric -- not null
);
CREATE INDEX t_product_inv0_idx ON product.t_product_inv0 (word, price
NULLS FIRST, id);
CREATE OR REPLACE FUNCTION array_append_1(bigint[], numeric, bigint)
RETURNS bigint[] AS $$select array_append($1, $3)$$ LANGUAGE SQL;
CREATE AGGREGATE array_accum_1 (numeric, bigint)
(
sfunc = array_append_1,
stype = bigint[],
initcond = '{}'
);
I would like the get the list of IDs ordered by the price field:
INSERT INTO product.t_product_inv
SELECT word, array_accum_1(price, id) FROM product.t_product_inv0
GROUP BY word ORDER BY word, price NULLS FIRST, id;
However, I get an error:
ERROR: column "t_product_inv0.price" must appear in the GROUP BY clause
or be used in an aggregate function
LINE 3: GROUP BY word ORDER BY word, price NULLS FIRST, id;
^
The condition in the error message is true, the field "price"
is indeed used in an aggregate function but ignored on purpose
in the sfunc. So I obviously didn't expect the error to happen.
Is the bug in our approach or in the check for the
GROUP BY/ORDER BY/aggregated variables?
This was tested on 8.4.1, 8.5CVS from 20090930 and from today.
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/
From | Date | Subject | |
---|---|---|---|
Next Message | Kris Jurka | 2009-10-26 21:49:37 | Re: Parsing config files in a directory |
Previous Message | Dimitri Fontaine | 2009-10-26 21:33:09 | Re: Parsing config files in a directory |