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

From: Thalis Kalfigkopoulos <tkalfigo(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Different results from view and from its defintion query [w/ windowing function]
Date: 2012-08-20 04:55:38
Message-ID: CAEkCx9HkHjqfq-3fPpZreiyWefpTT_A2u6xG-JtL7YYAQhVjHw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Sorry for the lack of a more appropriate title.
The summary of my problem is: i run a query and I get some results; then I
create a view using this query, and I run the same query on the view, and
get different results. Details follow.

On the original table the analytical data is as follows:
# SELECT id,experiment,insertedon,score FROM data WHERE id=1160;

id | experiment | insertedon | score
--------+----------------------+---------------------+--------
1160 | alpha | 2012-08-19 01:01:12 | 220.69
1160 | alpha | 2012-08-19 01:01:22 | 220.69
1160 | beta | 2012-08-19 01:01:31 | 220.7
1160 | beta | 2012-08-19 01:01:42 | 220.7
1160 | beta | 2012-08-19 01:01:54 | 220.7

My query of interest using windowing functions is:

# SELECT id, experiment, first_value(insertedon) OVER (PARTITION BY score,
id) AS first_insertedon, score FROM data WHERE id=1160;
id | experiment | first_insertedon | score |
--------+----------------------+---------------------+--------+----------
1160 | alpha | 2012-08-19 01:01:12 | 220.69 |
1160 | alpha | 2012-08-19 01:01:12 | 220.69 |
1160 | beta | 2012-08-19 01:01:31 | 220.7 |
1160 | beta | 2012-08-19 01:01:31 | 220.7 |
1160 | beta | 2012-08-19 01:01:31 | 220.7 |

So far so good. I then create the view on this last query without the WHERE
condition:
# CREATE VIEW clustered_view AS SELECT id, experiment,
first_value(insertedon) OVER (PARTITION BY score, id) AS first_insertedon,
score FROM data;

I see the view created correctly and its definition is according to the
mentioned SQL query. I now select from the view adding the WHERE condition:
# SELECT * from clustered_view WHERE id=1160;

id | experiment | first_insertedon | score |
--------+----------------------+---------------------+--------+
1160 | alpha | 2012-08-19 01:01:12 | 220.69 |
1160 | alpha | 2012-08-19 01:01:12 | 220.69 |
1160 | beta | 2012-08-19 01:01:54 | 220.7 |
1160 | beta | 2012-08-19 01:01:54 | 220.7 |
1160 | beta | 2012-08-19 01:01:54 | 220.7 |

As you see, the 'first_insertedon' timestamp for the experiment 'beta' is
no longer the first of the timestamps i.e. '2012-08-19 01:01:31' as the
original query's results gave correctly, but it's now the last one i.e.
'2012-08-19 01:01:54'

Any ideas? Missing the obvious?

TIA,
Thalis K.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Craig Ringer 2012-08-20 05:50:57 Re: importing updated files into a database
Previous Message Chris Travers 2012-08-20 03:52:07 Re: How hard would a "path" operator be to implement in PostgreSQL