cyril.ballagny@abbd.fr writes:
> Below is the script to reproduce the database. At the end of this message
> there are two queries which should produce the same output but it is
> different. I don't understand why. It seems that it is related to the
> ORDER_BY on view singledoublons0 and array_agg function on view
> singledoublons1.
AFAICT the problem is that these views are underdetermined. In
particular, the order of the input to the array_agg aggregate
is underdetermined, so that it's possible for it to produce different
array outputs for the same group depending on how the planner and
executor happen to do the grouping step needed for singledoublons1.
And that means that the GROUP BY at the next level up may or may not
see different array_auto_id values among the set of groups with the
same ratio_exaus/groupid combination.
I was able to make the instability go away by changing the intermediate
view like this:
CREATE OR REPLACE VIEW singledoublons1 AS
SELECT singledoublons0.groupid,
array_agg(singledoublons0.auto_id ORDER BY auto_id) AS array_auto_id,
^^^^^^^^^^^^^^^^
singledoublons0.priority, singledoublons0.ratio_exaus
FROM singledoublons0
GROUP BY singledoublons0.priority, singledoublons0.ratio_exaus,
singledoublons0.groupid;
That's a bit brute-force and you might be able to do better with your real
data, but anyway the problem is that you need to ensure that the array_agg
result is uniquely determined, which it isn't with the definitions and
data as presented.
regards, tom lane