Thread: Bug? Changing where distinct occurs produces error?

OK I'm either insane or found a bug in 8.1.3

If you execute say:

SELECT DISTINCT(ua.user_id),pa.poll_id FROM user_answers ua, poll_answers
pa WHERE pa.poll_answer_id = ua.poll_answer_id AND ua.active='Y';

Everything is fine, however if you run

SELECT pa.poll_id,DISTINCT(ua.user_id) FROM user_answers ua, poll_answers
pa WHERE pa.poll_answer_id = ua.poll_answer_id AND ua.active='Y';

ERROR:  syntax error at or near "DISTINCT" at character 19
LINE 1: SELECT pa.poll_id,DISTINCT(ua.user_id) FROM user_answers ua,...
                          ^
Further if I wrap it in a SUM (with a group by pa.poll_id) I get no error.

I'm a little confused as to what is wrong with my second example there?

--
"Genius might be described as a supreme capacity for getting its possessors
into trouble of all kinds."
-- Samuel Butler

On Fri, 7 Jul 2006, Michael Loftis wrote:

> OK I'm either insane or found a bug in 8.1.3
>
> If you execute say:
>
> SELECT DISTINCT(ua.user_id),pa.poll_id FROM user_answers ua, poll_answers
> pa WHERE pa.poll_answer_id = ua.poll_answer_id AND ua.active='Y';
>
> Everything is fine, however if you run
>
> SELECT pa.poll_id,DISTINCT(ua.user_id) FROM user_answers ua, poll_answers
> pa WHERE pa.poll_answer_id = ua.poll_answer_id AND ua.active='Y';

This statement is invalid. DISTINCT is a set quantifier and either comes
before the select list or as the first thing in a set function
specification.

> SELECT DISTINCT(ua.user_id),pa.poll_id FROM user_answers ua, poll_answers
> pa WHERE pa.poll_answer_id = ua.poll_answer_id AND ua.active='Y';
>
> Everything is fine, however if you run
>
> SELECT pa.poll_id,DISTINCT(ua.user_id) FROM user_answers ua, poll_answers
> pa WHERE pa.poll_answer_id = ua.poll_answer_id AND ua.active='Y';
>
> ERROR:  syntax error at or near "DISTINCT" at character 19
> LINE 1: SELECT pa.poll_id,DISTINCT(ua.user_id) FROM user_answers ua,...
>                           ^

Notice: http://www.postgresql.org/docs/8.1/interactive/sql-select.html

According to the syntax for a select a distinct | distinct on must be the first column specified
in the syntax.

So perhaps it is designed that way.

Regards,

Richard Broersma Jr.

--On July 7, 2006 3:22:01 PM -0700 Richard Broersma Jr
<rabroersma@yahoo.com> wrote:

> Notice: http://www.postgresql.org/docs/8.1/interactive/sql-select.html
>
> According to the syntax for a select a distinct | distinct on must be the
> first column specified in the syntax.
>
> So perhaps it is designed that way.


Ahhh I see, I keep thinking of DISTINCT as a Aggregator function.  That's
my mistake.

Thanks all!