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

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