#5 DB Error - SELECT DISTINCT/ORDER BY

[Jeff Balderson]

I've recently switched from from MySQL to Postgresql
for the back-end DB. I've noticed that when trying to
sort an alert listing by sig_name, I run into the
following error:

ERROR: For SELECT DISTINCT, ORDER BY expressions must
appear in target list

It appears that the problem is with the query that was
being performed:

SELECT DISTINCT signature, count(signature) as sig_cnt,
min(timestamp),
max(timestamp) ,
MIN(sig_class_id)
FROM acid_event
WHERE acid_event.ip_proto= '6'
GROUP BY signature
ORDER BY sig_class_id DESC
LIMIT 50
OFFSET 0;

You'll notice that it tells the DB to "ORDER BY
sig_class_id", but "sig_class_id" itself is never
requested, only 'MIN(sig_class_id)' is. Postgres (and
probably most other databases) is happy with either one
of the following queries:

=========
Query #1 (modified 'MIN(sig_class_id)' line)
=========
SELECT DISTINCT signature, count(signature) as sig_cnt,
min(timestamp),
max(timestamp) ,
MIN(sig_class_id) as sig_class_id
FROM acid_event
WHERE acid_event.ip_proto= '6'
GROUP BY signature
ORDER BY sig_class_id DESC
LIMIT 50
OFFSET 0;

=========
Query #2 (modified 'ORDER BY' line)
=========
SELECT DISTINCT signature, count(signature) as sig_cnt,
min(timestamp),
max(timestamp) ,
MIN(sig_class_id) as sig_class_id
FROM acid_event
WHERE acid_event.ip_proto= '6'
GROUP BY signature
ORDER BY MIN(sig_class_id) DESC
LIMIT 50
OFFSET 0;

I've noted the same problem when trying to sort by
classification. I haven't tested any of the other sorts.

I tested Query #1 with MySQL (v3.23.41-1 -- RedHat
package), and it works if you get rid of the 'OFFSET' line.

I'm using:

RedHat Linux v7.2
Snort v1.90 (schema 106)
ACID v0.9.6b22
ADODB v1.71
Postgresql v7.1.3-2 (RedHat released version)