Re: Obvious data mismatch in View2 which basically SELECT * from View1

Ben <bentenzha(at)outlook(dot)com> <MWHPR06MB2400E1FA4AAD12440EA96B79B93E0(at)MWHPR06MB2400(dot)namprd06(dot)prod(dot)outlook(dot)com> writes:
> You are right none of them is what I originally used to create them
> which should be
> `select * from utlog.stats_per_shift_filtered_b0206`, but they do look
> different,

This discrepancy could explain a lot:

>      (stats_per_shift_filtered_u0206.wsft)::character varying AS wsft,

vs

>      stats_per_shift_filtered_b0206.wsft,

Your problem query has a WHERE clause testing wsft, so this difference
is subtly affecting what that WHERE clause means. That could result in
a plan change, thereby changing the row output order, which as already
noted would be sufficient to explain the inconsistency.

If I had to bet I'd suspect that the extra cast is blocking use of an
index on wsft.

regards, tom lane