From: | Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com> |
---|---|
To: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
Cc: | Bartosz Polnik <bartoszpolnik(at)gmail(dot)com>, PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: BUG #15577: Query returns different results when executed multiple times |
Date: | 2019-01-08 00:42:45 |
Message-ID: | CAEepm=3s70u7-E9aSdsSq+_S_cm2GnT4Tk3_vC81PDxe=hCFRw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Tue, Jan 8, 2019 at 1:40 PM Thomas Munro
<thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> On Tue, Jan 8, 2019 at 1:31 PM David Rowley
> <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> > On Tue, 8 Jan 2019 at 13:09, Bartosz Polnik <bartoszpolnik(at)gmail(dot)com> wrote:
> > > (26 rows)
> > >
> > > Here's an example with only 21 rows:
> >
> > Interestingly between those two results, all your 31 distinct rows
> > exist. I threw them into a table and did:
> >
> > # select * from t1 full join t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c;
> > a | b | c | a | b | c
> > ----------+--------+---------+----------+--------+---------
> > 16114789 | 292051 | 3038539 | 16114789 | 292051 | 3038539
> > 16114811 | 298605 | 2893188 | | |
> > 16114811 | 298605 | 2893391 | | |
> > 16114811 | 298605 | 2893809 | 16114811 | 298605 | 2893809
> > 16114811 | 298605 | 2983360 | | |
> > 16114811 | 298605 | 2987038 | | |
> > 16114811 | 298605 | 2998909 | | |
> > 16114811 | 298605 | 2998966 | | |
> > 16114811 | 298605 | 3010694 | | |
> > 16114811 | 298605 | 3026078 | | |
> > 16114811 | 298605 | 3038221 | | |
> > 16114811 | 298605 | 3038877 | | |
> > 16114813 | 342353 | 3052371 | 16114813 | 342353 | 3052371
> > 16114814 | 331329 | 2946332 | 16114814 | 331329 | 2946332
> > 16115106 | 350047 | 2902075 | 16115106 | 350047 | 2902075
> > 16115788 | 348539 | 2913754 | 16115788 | 348539 | 2913754
> > 16115788 | 348539 | 2913874 | 16115788 | 348539 | 2913874
> > 16115788 | 348539 | 3039173 | 16115788 | 348539 | 3039173
> > 16115861 | 350487 | 2933633 | 16115861 | 350487 | 2933633
> > 16116066 | 351434 | 3010909 | 16116066 | 351434 | 3010909
> > 16116069 | 351585 | 3025941 | 16116069 | 351585 | 3025941
> > 16116185 | 328860 | 2936924 | 16116185 | 328860 | 2936924
> > 16116241 | 295971 | 3038921 | 16116241 | 295971 | 3038921
> > 16116249 | 296708 | 3038888 | 16116249 | 296708 | 3038888
> > | | | 16116256 | 293541 | 2901933
> > 16116256 | 293541 | 2901938 | 16116256 | 293541 | 2901938
> > | | | 16116256 | 293541 | 2997160
> > | | | 16116260 | 290583 | 2901669
> > | | | 16116260 | 290583 | 2921135
> > | | | 16116260 | 290583 | 2947914
> > 16116260 | 290583 | 2955483 | 16116260 | 290583 | 2955483
> > (31 rows)
> >
> > IOW, it does not seem like there's a particlar pattern to what rows are missing.
>
> Is the pattern something like this? When the index probe of b should
> find multiple matches, it's returning only one and then we move onto
> the next outer row? For example we see this for table_c_id = 298605,
> of which we see 11 examples in the 31-row output (with different
> table_b_id values), but only one in the 21-row output, with the first
> table_b_id value.
It's as if it thought it was executing a semi-join.
--
Thomas Munro
http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2019-01-08 00:47:56 | Re: BUG #15577: Query returns different results when executed multiple times |
Previous Message | Thomas Munro | 2019-01-08 00:40:56 | Re: BUG #15577: Query returns different results when executed multiple times |