Re: archive items not in correct section order - Mailing list pgsql-general

Tim Clarke <tim.clarke@minerva-analytics.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;