Re: BUG #17925: Incorrect select query result

Since this is corporation data I cannot provide much data in this public
mailing list unfortunately. And I have not yet been able to reproduce this
behaviour on dummy data.
I notice the following behaviour from the query, even though it probably
isn't sufficient to find what's going on,

specifying query to training_id 709,

with ae_trainings as (
select * from trainings.activity_events
where training_id in (709))
select tb.training_id, tb.block, count(ae.trial_time_stimuli_start is not
null) suspicions_column_result
from trainings.training_blocks tb
left join ae_trainings ae
on ae.training_id = tb.training_id
and ae.block = tb.block
and ae.activity = tb.activity
and ae.event = 'trial'
where tb.training_id in (709)
and tb.block < 23
group by tb.training_id, tb.block
order by tb.training_id, tb.block desc
limit 10

returns expected result

709 22 70
709 21 68
709 20 71
709 19 68
709 18 76
709 17 73
709 16 69
709 15 70
709 14 68
709 13 71

also querying for 891 as in

with ae_trainings as (
select * from trainings.activity_events
where training_id in (891))
select tb.training_id, tb.block, count(ae.trial_time_stimuli_start is not
null) suspicions_column_result
from trainings.training_blocks tb
left join ae_trainings ae
on ae.training_id = tb.training_id
and ae.block = tb.block
and ae.activity = tb.activity
and ae.event = 'trial'
where tb.training_id in (891)
and tb.block < 23
group by tb.training_id, tb.block
order by tb.training_id, tb.block desc
limit 10

yields correct result

891 22 3
891 21 3
891 20 3
891 19 3
891 18 3
891 17 3
891 16 3
891 15 3
891 14 3
891 13 3

However, querying for both 709 and 891

with ae_trainings as (
select * from trainings.activity_events
where training_id in (709,891))
select tb.training_id, tb.block, count(ae.trial_time_stimuli_start is not
null) suspicions_column_result
from trainings.training_blocks tb
left join ae_trainings ae
on ae.training_id = tb.training_id
and ae.block = tb.block
and ae.activity = tb.activity
and ae.event = 'trial'
where tb.training_id in (709,891)
and tb.block < 23
group by tb.training_id, tb.block
order by tb.training_id, tb.block desc
limit 10

returns incorrect result for 709

709 22 5
709 21 5
709 20 5
709 19 5
709 18 5
709 17 5
709 16 5
709 15 5
709 14 5
709 13 5

If I remove the CTE by using

select tb.training_id, tb.block, count(ae.trial_time_stimuli_start is not
null) suspicions_column_result
from trainings.training_blocks tb
left join trainings.activity_events ae
on ae.training_id = tb.training_id
and ae.block = tb.block
and ae.activity = tb.activity
and ae.event = 'trial'
where tb.training_id in (709,891)
and tb.block < 23
group by tb.training_id, tb.block
order by tb.training_id, tb.block desc
limit 10

the result is correct again.

Also, I should mention that the database is running on RDS, managed by AWS,
so maybe you are not interested in looking into it in this case?

Thanks, Joakim

On Mon, May 8, 2023 at 9:29 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > Hello, this query returns different result depending on chosen execution
> > plan.
>
> This does seem fishy, but it's impossible to investigate with
> this amount of detail. Can you provide table declarations and
> sample data to go with the query?
>
> regards, tom lane
>