Re: Different results from view and from its defintion query [w/ windowing function]

Hi Tom,

and thanks for the reply (I had the pleasure of meeting you 11 years ago in
Pittsburgh; still a pleasure seeing your concise and helpful replies.)

In the end I went for a change of window function. Using "min(insertedon)"
instead of "first_value(insertedon)" works correctly.

Alternatively your suggestion of adding an "ORDER BY insertedon" clause
also seems to work. It makes the first_value(insertedon) behave as
min(insertedon).

thanks again,
Thalis K.

On Mon, Aug 20, 2012 at 12:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Thalis Kalfigkopoulos <tkalfigo(at)gmail(dot)com> writes:
> > # SELECT id, experiment, first_value(insertedon) OVER (PARTITION BY
> score,
> > id) AS first_insertedon, score FROM data WHERE id=1160;
>
> > [ versus ]
>
> > # CREATE VIEW clustered_view AS SELECT id, experiment,
> > first_value(insertedon) OVER (PARTITION BY score, id) AS
> first_insertedon,
> > score FROM data;
>
> > # SELECT * from clustered_view WHERE id=1160;
>
> One possible reason these produce different results is in that in the
> first case, the WHERE condition eliminates rows from the window
> function's consideration. In the second case, it doesn't --- the WHERE
> only filters the result rows from the view. However, the fact that "id"
> is part of the partition list may insulate you from that; not quite sure
> without seeing a more complete example.
>
> Another likely reason for trouble is that the window function seems
> underspecified: without any ORDER BY clause, you are going to get a
> random one of the insertedon values for the same score and id. It's
> entirely likely that moving the WHERE clause would change the plan
> enough to change the ordering of the rows seen by the window function.
> Possibly you should be using min() instead of first_value().
>
> regards, tom lane
>