Re: SELECT DISTINCT ON bug?

The following query give an error in 7.3

TAL=# SELECT distinct on(dai_genres.id), dsc from dai_genres,
rel_genres_goods where dai_genres.id=rel_genres_goods.major_id group by
id, dsc;
ERROR: parser: parse error at or near "," at character 34

I looked up the 7.3 docs and I thought I had the syntax right.

What's the proper syntaxt for the above query (selecting unique id's and
their associated dsc from the two tables joined on id=major_id)

Thanks.

PS I was doing this query using GROUP BY but using distinct (on) seems
like it should be faster, but I can't test it until I get the query
running ...

--

Jean-Christian Imbeault

On Wed, May 21, 2003 at 03:35:37PM +0900, Jean-Christian Imbeault wrote:
> The following query give an error in 7.3
>
> TAL=# SELECT distinct on(dai_genres.id), dsc from dai_genres,
> rel_genres_goods where dai_genres.id=rel_genres_goods.major_id group by
> id, dsc;
> ERROR: parser: parse error at or near "," at character 34

Drop the comma after the distinct on.

SELECT distinct on(dai_genres.id) dsc from dai_genres,

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington

Martijn van Oosterhout wrote:
>
> Drop the comma after the distinct on.
>
> SELECT distinct on(dai_genres.id) dsc from dai_genres,

Ok, that worked. But accroding to "\h select" the syntax is:

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]

I gather that to mean SELECT DISTINCT ON (*) , ...

Is the syntax give by "\h select" wrong or am I misinterpreting it?

Thanks,

--

Jean-Christian Imbeault

On Wed, May 21, 2003 at 05:08:33PM +0900, Jean-Christian Imbeault wrote:
> Martijn van Oosterhout wrote:
> >
> >Drop the comma after the distinct on.
> >
> >SELECT distinct on(dai_genres.id) dsc from dai_genres,
>
> Ok, that worked. But accroding to "\h select" the syntax is:
>
> SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
> * | expression [ AS output_name ] [, ...]
>
>
> I gather that to mean SELECT DISTINCT ON (*) , ...
>
> Is the syntax give by "\h select" wrong or am I misinterpreting it?

You're misinterpreting it, though I must admit it does seem ambiguous. It
should be read as:

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
{ * | expression [ AS output_name ] [, ...] }

See the {}.

Hope this helps,

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington

On Wed, 21 May 2003, Jean-Christian Imbeault wrote:

> Martijn van Oosterhout wrote:
> >
> > Drop the comma after the distinct on.
> >
> > SELECT distinct on(dai_genres.id) dsc from dai_genres,
>
> Ok, that worked. But accroding to "\h select" the syntax is:
>
> SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
> * | expression [ AS output_name ] [, ...]
>
>
> I gather that to mean SELECT DISTINCT ON (*) , ...
>
> Is the syntax give by "\h select" wrong or am I misinterpreting it?

That's a misinterpretation I'm afraid. There is no comma shown before the *
that is not part of the expression for the DISTINCT ON.

--
Nigel J. Andrews

Nigel J. Andrews wrote:
>>
>>I gather that to mean SELECT DISTINCT ON (*) , ...
>>
>>Is the syntax give by "\h select" wrong or am I misinterpreting it?
>
>
> That's a misinterpretation I'm afraid. There is no comma shown before the *
> that is not part of the expression for the DISTINCT ON.

You mean "after the *" right? ;)

After looking at it again I see how I misunderstood the syntax. But I do
find it intuitive to say:

SELECT DISTINCT ON(c1) c2,c3 FROM ...

But I can live with it considering DISTINCT ON is non-standard SQL :)

--

Jean-Christian Imbeault

On Wed, 21 May 2003, Jean-Christian Imbeault wrote:

> Nigel J. Andrews wrote:
> >>
> >>I gather that to mean SELECT DISTINCT ON (*) , ...
> >>
> >>Is the syntax give by "\h select" wrong or am I misinterpreting it?
> >
> >
> > That's a misinterpretation I'm afraid. There is no comma shown before the *
> > that is not part of the expression for the DISTINCT ON.
>
> You mean "after the *" right? ;)

No, I was refering to the syntax you'd pasted from psql:

Syntax:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]

Here the '*' I mention is on the second line of the statement and specifies the
returned columns. Before that point the only comma that can occur is within
the brackets giving the expression(s) of the DISTINCT ON.

Sorry for confusing you more.

>
> After looking at it again I see how I misunderstood the syntax...

Easily done.

--
Nigel J. Andrews

On Wed, May 21, 2003 at 10:58:26 +0100,
"Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> wrote:
>
> Syntax:
> SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
> * | expression [ AS output_name ] [, ...]

This syntax expression does seem wrong. Usually concatenation takes
precedence over alternation. I think there really should be {}s around
the second line.

On Wed, 21 May 2003, Bruno Wolff III wrote:

> On Wed, May 21, 2003 at 10:58:26 +0100,
> "Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> wrote:
> >
> > Syntax:
> > SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
> > * | expression [ AS output_name ] [, ...]
>
> This syntax expression does seem wrong. Usually concatenation takes
> precedence over alternation. I think there really should be {}s around
> the second line.

Wouldn't we also need them around the expression on the select line?

SELECT [ ALL | DISTINCT [ ON {( expression [, ...] ) } ] ]
{ * | expression [ AS output_name ] [, ...] }

On Thu, May 22, 2003 at 08:53:10 -0600,
"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> wrote:
>
> Wouldn't we also need them around the expression on the select line?
>
> SELECT [ ALL | DISTINCT [ ON {( expression [, ...] ) } ] ]
> { * | expression [ AS output_name ] [, ...] }

I don't think so. Where you put them you have enclosed tokens that
are only concatenated so precedence doesn't cause a problem.
The original format implies that you can have a select statement without
the SELECT keyword and that if you use the SELECT keyword you also have
to use * for the target list.

On Thu, 22 May 2003, Bruno Wolff III wrote:

> On Thu, May 22, 2003 at 08:53:10 -0600,
> "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> wrote:
> >
> > Wouldn't we also need them around the expression on the select line?
> >
> > SELECT [ ALL | DISTINCT [ ON {( expression [, ...] ) } ] ]
> > { * | expression [ AS output_name ] [, ...] }
>
> I don't think so. Where you put them you have enclosed tokens that
> are only concatenated so precedence doesn't cause a problem.
> The original format implies that you can have a select statement without
> the SELECT keyword and that if you use the SELECT keyword you also have
> to use * for the target list.

sorry, I meant to put them inside the | symbol on the left.

* | { expression...

And I'm pretty sure I put them in the wrong place on the top line too.

On Thu, May 22, 2003 at 11:05:20 -0600,
"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> wrote:
> On Thu, 22 May 2003, Bruno Wolff III wrote:
>
> > On Thu, May 22, 2003 at 08:53:10 -0600,
> > "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> wrote:
> > >
> > > Wouldn't we also need them around the expression on the select line?
> > >
> > > SELECT [ ALL | DISTINCT [ ON {( expression [, ...] ) } ] ]
> > > { * | expression [ AS output_name ] [, ...] }
> >
> > I don't think so. Where you put them you have enclosed tokens that
> > are only concatenated so precedence doesn't cause a problem.
> > The original format implies that you can have a select statement without
> > the SELECT keyword and that if you use the SELECT keyword you also have
> > to use * for the target list.
>
> sorry, I meant to put them inside the | symbol on the left.
>
> * | { expression...
>
> And I'm pretty sure I put them in the wrong place on the top line too.

On the first line the alternation is enclosed in []s so there isn't
a problem. The meaning is correct in that you can't use the ON keyword
if you used ALL.
On the second line you don't need to enclose the right hand side of
the alternation in {}s because the terms are all connected by
concatenation.

On Wed, 21 May 2003, Bruno Wolff III wrote:

> On Wed, May 21, 2003 at 10:58:26 +0100,
> "Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> wrote:
> >
> > Syntax:
> > SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
> > * | expression [ AS output_name ] [, ...]
>
> This syntax expression does seem wrong. Usually concatenation takes
> precedence over alternation. I think there really should be {}s around
> the second line.

Good point. As written, syntax says you can either select * OR you can
select field1,field2,field3, but you can't select *,oid ...

Since we allow select *,field,function,expression, it would seem we need
the {}s there around * | expression [ AS output_name ] [, ...]
part.

Reference for those who like reading the SQL spec:

3.2 Notation

[ ] Square brackets indicate optional elements in a formula.
The portion of the formula within the brackets may be
explicitly specified or may be omitted.

{ } Braces group elements in a formula. The portion of the for-
mula within the braces shall be explicitly specified.

| The alternative operator. The vertical bar indicates that
the portion of the formula following the bar is an alterna-
tive to the portion preceding the bar. If the vertical bar
appears at a position where it is not enclosed in braces
or square brackets, it specifies a complete alternative for
the element defined by the production rule. If the vertical
bar appears in a portion of a formula enclosed in braces or
square brackets, it specifies alternatives for the contents
of the innermost pair of such braces or brackets.

On Thu, May 22, 2003 at 12:05:46 -0600,
"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> wrote:
> On Wed, 21 May 2003, Bruno Wolff III wrote:
>
> > On Wed, May 21, 2003 at 10:58:26 +0100,
> > "Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> wrote:
> > >
> > > Syntax:
> > > SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
> > > * | expression [ AS output_name ] [, ...]
> >
> > This syntax expression does seem wrong. Usually concatenation takes
> > precedence over alternation. I think there really should be {}s around
> > the second line.
>
> Good point. As written, syntax says you can either select * OR you can
> select field1,field2,field3, but you can't select *,oid ...

This is a bit different than what I was looking at, but there still
is a problem. I didn't know you could mix * and other expressions.
I now think the second line should be:
{* | expression [ AS output_name ]} [, ...]

What I was complaining about was that the first line was only included
on the left side of that alternation. However the above fix takes care
of that problem as well.