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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Thalis Kalfigkopoulos <tkalfigo(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Different results from view and from its defintion query [w/ windowing function]
Date: 2012-08-20 15:16:27
Message-ID: 527.1345475787@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thalis Kalfigkopoulos 2012-08-20 16:33:31 Re: Different results from view and from its defintion query [w/ windowing function]
Previous Message Merlin Moncure 2012-08-20 15:12:37 Re: function depend on view