Re: archive items not in correct section order

Tim Clarke <tim(dot)clarke(at)minerva-analytics(dot)info> writes:
> On 27/08/18 15:22, Tom Lane wrote:
>> That's not supposed to happen. Can you create a test case, by any chance?

> It wasn't the cross-tab/pivot, it was this materialized view:

Hm, could I trouble you for a self-contained test case? I tried to
flesh it out as attached, but I'm not seeing any error with this.
So there must be some other moving part ...

regards, tom lane

drop schema rating cascade;
drop schema r cascade;

create schema rating;
create table rating.cy (f1 int, cid int, ye int);

create schema r;

set search_path = r;

create table c(id int);
create table f(id int, cid int, ye int, ytext text);
create table wb(cid int, fid int, prop float8);

CREATE materialized VIEW r.b AS
SELECT
c.id,
f.ytext,
min(coalesce(
(select
case
when wb.prop >= 0.333 then 4
when wb.prop >= 0.25 then 3
when wb.prop >= 0.15 then 2
when wb.prop >= 0.1 then 1
else 0
end
FROM r.wb
where
wb.cid = c.id and
wb.fid = f.id), 0)) as score
FROM
rating.cy,
c,
f
WHERE
c.id = f.cid AND
f.cid = cy.cid AND
f.ye = cy.ye
GROUP BY
1, 2
LIMIT 1;