Re: ROWID bug in select statement with a GROUP BY clause

>From: uunet!TRMETU.BITNET!AYKUTY (AYKUT YILMAZ) >Subject: ROWID bug in select statement with a GROUP BY clause >Date: 22 Sep 92 16:56:37 GMT >X-Informix-List-Id: <news.1865> > >Below is a SELECT statement which runs quite normally under version 4.0 >but gives an error when used under version 4.1. The environment is an >NCR 3445 with SCO UNIX 3.2.2. > >The problem is caused by the 'column' ROWID in combination with 'GROUP BY', >the error issued is -294: The column 'column-name' must be in the GROUP BY >list. > >SELECT a.ROWID, > a.field1, > SUM (b.field2) > FROM table1 a, > table2 b > WHERE b.id = a.id > GROUP BY field1 I would suggest to you that the bug was in the 4.0 release and has been fixed in the 4.1 release, and that your statement should never have worked. If you use a GROUP BY clause, all the non-aggregate columns should be specified in the GROUP BY clause, either by name or by position in the select list. You will need to revise your select statement to read: SELECT a.ROWID, a.field1, SUM (b.field2) FROM table1 a, table2 b WHERE b.id = a.id GROUP BY a.ROWID, a.field1; or: SELECT a.ROWID, a.field1, SUM (b.field2) FROM table1 a, table2 b WHERE b.id = a.id GROUP BY 1, 2; >Is there a bug fix for this ROWID - GROUP BY problem and if there is, is >there an anonymous ftp site that we can get the fix? Informix does not distribute bug fixes like that. (I'm tempted to say Informix does not distribute bug fixes period -- that is not quite true, but is a reasonable starting point for the discussion.) Yours sincerely, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>