Re: BUG #17925: Incorrect select query result - Mailing list pgsql-bugs
| From | Joakim Goldkuhl |
|---|---|
| Subject | Re: BUG #17925: Incorrect select query result |
| Date | |
| Msg-id | CABrXk-Kg61o5hXq1Xjf+t=nZXC-Sgr9CQKG+ZHDaYOE-0qC2kQ@mail.gmail.com Whole thread Raw |
| In response to | Re: BUG #17925: Incorrect select query result (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: BUG #17925: Incorrect select query result
|
| List | pgsql-bugs |
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.
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
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
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
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
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
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
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
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@sss.pgh.pa.us> wrote:
PG Bug reporting form <noreply@postgresql.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
pgsql-bugs by date: