Re: Wish: remove ancient constructs from Postgres

On Mon, Feb 27, 2006 at 06:59:21PM +0300, Nikolay Samokhvalov wrote:
> On 2/27/06, Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> > >'DISTINCT ON' is evil constuction, because (w/o any 'ORDER BY') it
> > >produses unpredictable result, as 'ORDER BY random()' does.
> >
> > And so does UNION in the standard under some circumstances (look at
> > anywhere in the spec that a query expression is possibly
> > non-deterministic), so I think that's a weak argument.
> >
> it's completely different thing. look at the spec and you'll
> understand the difference. in two words, with 'DISTINCT ON' we lose
> some values (from some columns), when UNION not (it just removes
> duplicates, comparing _entire_ rows).

Wait, you're complaining because SQL lets you produce non-deterministic
results? There are plenty or way to acheive this in standard SQL too.
This statement:

select pronargs, first( cast(prolang as integer) ) from pg_proc group by pronargs;

Produces non-deterministic results also, just like DISTINCT ON ().
Using LIMIT/OFFSET with an underspecified ORDER BY produces
"unpredicatble" results. We provide the tools, but if people want aim
them at their feet and blow them off, that's not something we can do
anything about. If anything, it seems you're arguing for the removal of
the random() function because it's non-deterministic.

Hey, and sometimes I want a non-deterministic output. It's nice
postgresql can give me that too...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.