Thread

Bug with a join and group by query.

Michael Richards <miker@scifair.acadiau.ca> — 1999-02-22T02:03:04Z

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