Bug with a join and group by query.
От | Michael Richards |
---|---|
Тема | Bug with a join and group by query. |
Дата | |
Msg-id | Pine.BSF.3.96.990221220246.23679A-100000@scifair.acadiau.ca обсуждение исходный текст |
Ответы |
Re: [BUGS] Bug with a join and group by query.
|
Список | pgsql-bugs |
Hi. I think I found a bug. My query works in mysql. I have 2 tables. It's for a classifieds database. 1 is the categories, and the second is the postings. Postings are categorised and related to a category. I'll dump the contents of the tables and append it to the message if anyone wants test data. postgres 6.4 does: select cl_categories.catid,cl_categories.description,count(*) from cl_categories,cl_postings where catid=categoryid group by catid; ERROR: parser: illegal use of aggregates or non-group column in target list I tried about a kazillion variations on this query and they all seem to respond with the same error. Since it worked on mysql, I don't think it's a problem with what I'm trying to do... I still might try it on oracle if some are unconvinced. In mysql, however the query works. mysql> select cl_categories.catid,cl_categories.description,count(*) from cl_categories,cl_postings where catid=categoryid group by catid; +-------+-----------------------------+----------+ | catid | description | count(*) | +-------+-----------------------------+----------+ | 1 | Books for sale | 1 | | 4 | Computer equipment for sale | 2 | +-------+-----------------------------+----------+ 2 rows in set (0.01 sec) Here is the schema and data I was using: /usr/local/pgsql/bin/pg_dump -n -d -t cl_postings asu CREATE TABLE cl_postings (postid int4 NOT NULL, categoryid int4 NOT NULL, poster int4 NOT NULL, disabledate date, title varchar(80), contactemail1 varchar(80), contacttime1 varchar(20), contactphone1 varchar(15), contacttime2 varchar(20), contactphone2 varchar(15), contactname varchar(60), price float4, qualid int2, description varchar(4096), itemurl varchar(200)); INSERT INTO cl_postings values (1,4,100026809,'03-01-1999','4 port hub for sale','026809r@dragon.acadiau.ca',NULL,NULL,NULL,NULL,'Michael Richards',65,1,'I am selling a 4 port netgear hub. This would be excellent for people who have a desktop and laptop and want to use the network with both.',NULL); INSERT INTO cl_postings values (2,1,100026809,'03-01-1999','Modula-2 for sale','026809r@dragon.acadiau.ca',NULL,NULL,NULL,NULL,'Michael Richards',15,1,'I am selling a book entitled "Modula-2" It was used for a 1st year computer course.',NULL); INSERT INTO cl_postings values (3,4,100026809,'03-01-1999','network card','026809r@dragon.acadiau.ca',NULL,NULL,NULL,NULL,'Michael Richards',15,1,'I am selling a PCI NE-32 10 mbit NIC with 10base2 and 10baseT (RJ45) connectors. It works in windows, FreeBSD and linux.',NULL); CREATE UNIQUE INDEX cl_postings_pkey on cl_postings using btree ( postid int4_ops ); /usr/local/pgsql/bin/pg_dump -n -d -t cl_categories asu CREATE TABLE cl_categories (catid int4 NOT NULL, parentid int4, description varchar(250), postlife int4, enabled bool NOT NULL); INSERT INTO cl_categories values (1,NULL,'Books for sale',30,'t'); INSERT INTO cl_categories values (2,NULL,'Sublets',30,'t'); INSERT INTO cl_categories values (3,NULL,'Appliances for sale',30,'t'); INSERT INTO cl_categories values (4,NULL,'Computer equipment for sale',30,'t'); INSERT INTO cl_categories values (5,NULL,'Services',30,'t'); INSERT INTO cl_categories values (6,NULL,'Tutoring',30,'t'); INSERT INTO cl_categories values (7,NULL,'Automobiles for sale',30,'t'); CREATE UNIQUE INDEX cl_categories_pkey on cl_categories using btree ( catid int4_ops ); I think this is pretty much all that is needed. This is running on: uname -a FreeBSD hub.org 3.0-STABLE FreeBSD 3.0-STABLE #0: Wed Jan 27 17:20:21 EST 1999 root@hub.org:/usr/src/sys/compile/hub_org i386 The mysql is: Your MySQL connection id is 136 to server version: 3.22.16a-gamma on a: uname -a Linux melchior 2.2.1 #2 Wed Feb 17 04:44:56 PST 1999 i586 unknown -Michael
В списке pgsql-bugs по дате отправления: