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

[Thalis Kalfigkopoulos <tkalfigo@xxxxxxxxx>]

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@xxxxxxxxxxxxx> wrote:

Thalis Kalfigkopoulos <tkalfigo@xxxxxxxxx> 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