"David Rowley" <dgrowley@gmail.com> writes:
> Also while testing I noticed that this query didn't error out when it should
> have: (Of course I only noticed because Sybase did)
> WITH RECURSIVE bom(parentpart,childpart,quantity,rn) AS (
> SELECT parentpart,childpart,quantity,ROW_NUMBER() OVER (ORDER BY
> parentpart,childpart)
> FROM billofmaterials
> WHERE parentpart = 'KITCHEN'
> UNION ALL
> SELECT b.parentpart,b.childpart,b.quantity,ROW_NUMBER() OVER (ORDER BY
> parentpart,childpart)
> FROM billofmaterials b,bom
> WHERE b.parentpart = bom.childpart
> )
> SELECT * FROM bom;
> Notice the ORDER BY in the recursive part of the query orders by an
> ambiguous column without complaint.
Actually, it's not ambiguous according to our interpretation of ORDER BY
clauses: the first attempt is to match an output column name, so it's
seizing on the first SELECT column (b.parentpart) as being the intended
sort key for "parentpart", and similarly for "childpart". You'd get the
same thing if you did "ORDER BY 1,2".
We could disable all those special rules for window cases, but then we'd
have to document how window ORDER BY is different from query ORDER BY,
etc. I think it'd be more confusing not less so.
regards, tom lane