On Wed, Sep 16, 2020 at 9:26 AM Ben <benten...@outlook.com> wrote: > Dear list, > > Recently I am getting feedback, data in my analytic report is not > repeatable. From time to time they get different data for the same time > span. > (but IIRC previously it was OK). Therefore I started debuging the View > chain for that report, during which I bumped into this issue/phenomenon. > > In a over -simplified version: > > CREATE VIEW2 AS SELECT * FROM VIEW1; > SELECT col1 FROM VIEW2 WHERE cond1=True; > SELECT col1 FROM VIEW1 WHERE cond1=True; > > Now col1 from both views looks different. I don't know where to start to > solve this problem. > > The actual situation is a bit more than that, the following is the > actual query: > > > -- trying to audit utlog weighed stat > with t as ( > select '2020-07-01 00:00:00'::timestamp t0, '2020--07-02 > 0:0:0'::timestamp t1 > ) > --select * from t; > select * > -- from utlog.cache_stats_per_shift_per_reason_weighed_stats > -- from utlog.stats_per_shift_filtered_per_reason > from utlog.stats_per_shift_filtered (let's call > it #View2 for short) > -- from utlog.stats_per_shift_filtered_b0206 (let's call it > #View1 for short) > -- from utlog.stats_per_shift > cross join t > where wline = 'F02' and wts >= t.t0 and wts < t.t1 and wsft ='D' > limit 100 > ; >
Not sure if it might be something lost in your simplification here, but you have a LIMIT with no ORDER BY there. That basically means "give me 100 random rows" (but not with a very good random level). It does not return rows in a consistent/predictable order. So as long as that query is part of what you're doing, you should not be surprised if you get the rows in an inconsistent/unpredictable order, with whatever follow-on effects that might have. (And it can lead to weird follow-on effects like the ones you're talking about when used in larger query structures) -- Magnus Hagander Me: https://www.hagander.net/ <http://www.hagander.net/> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
