pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17986: Inconsistent results of SELECT affected by btree index
5+ messages / 4 participants
[nested] [flat]
* BUG #17986: Inconsistent results of SELECT affected by btree index
@ 2023-06-20 20:54 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2023-06-20 20:54 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: zuming.jiang@inf.ethz.ch
The following bug has been logged on the website:
Bug reference: 17986
Logged by: Zuming Jiang
Email address: zuming.jiang@inf.ethz.ch
PostgreSQL version: 16beta1
Operating system: Ubuntu 20.04
Description:
My fuzzer finds a correctness bug in Postgres, which makes Postgres return
inconsistent results. This bug can be reproduced even after applying the
fixing patches for
https://www.postgresql.org/message-id/flat/17976-4b638b525e9a983b%40postgresql.org
https://www.postgresql.org/message-id/flat/17982-3fa239feecd6c1b3%40postgresql.org
https://www.postgresql.org/message-id/flat/17985-748b66607acd432e%40postgresql.org
--- Set up database ---
create table t0 (vkey int4);
create table t2 (vkey int4, pkey int4, c11 int4, c12 timestamp, c13
float8);
create table t3 (pkey int4, c16 float8, c17 timestamp);
insert into t0 values (29), (34);
insert into t2 values (21, 31000, 34, make_timestamp(2073, 8, 26, 1, 50, 3),
0.0);
insert into t2 values (38, 48000, -12, make_timestamp(2036, 7, 25, 22, 49,
37), 56.44);
insert into t3 values (50000, 4.37, make_timestamp(2021, 4, 18, 12, 41,
57));
CREATE INDEX i0 ON t2 USING btree (c13);
---
The fuzzer generates Test case 1:
--- Test case 1 ---
select * from t0
where exists (
select
ref_2.c13 as c_4
from
((t2 as ref_0 right outer join t2 as ref_1 on (ref_0.c13 =
ref_1.c13))
left outer join t2 as ref_2 on (ref_0.c11 = ref_2.vkey))
where ref_0.c11 = (
select
t0.vkey as c_0
from
t3 as ref_4
where (case when (((ref_4.pkey in (select ref_2.pkey))
or (not (ref_4.pkey in (select ref_2.pkey))))
or ((ref_4.pkey in (select ref_2.pkey)) is
null))
then ref_4.c17 else make_timestamp(2032, 9, 19, 6, 55,
5) end
) < ref_1.c12
order by c_0 asc limit 1)
union all select t3.c16 from t3 where false);
---
Because `(ref_4.pkey in (select ref_2.pkey))` could only be TRUE, FALSE, or
NULL, `(((ref_4.pkey in (select ref_2.pkey)) or (not (ref_4.pkey in (select
ref_2.pkey)))) or ((ref_4.pkey in (select ref_2.pkey)) is null))` must be
TRUE. Therefore, I replace`(((ref_4.pkey in (select ref_2.pkey)) or (not
(ref_4.pkey in (select ref_2.pkey)))) or ((ref_4.pkey in (select
ref_2.pkey)) is null))` with TRUE, and get Test case 2:
--- Test case 2 ---
select * from t0
where exists (
select
ref_2.c13 as c_4
from
((t2 as ref_0 right outer join t2 as ref_1 on (ref_0.c13 =
ref_1.c13))
left outer join t2 as ref_2 on (ref_0.c11 = ref_2.vkey))
where ref_0.c11 = (
select
t0.vkey as c_0
from
t3 as ref_4
where (case when true
then ref_4.c17 else make_timestamp(2032, 9, 19, 6, 55,
5) end
) < ref_1.c12
order by c_0 asc limit 1)
union all select t3.c16 from t3 where false);
---
--- Expected behavior ---
Test case 1 and Test case 2 return the same results.
--- Actual behavior ---
Test case 1 returns 1 row, while Test case 2 returns 0 rows.
Output of Test case 1:
vkey
------
34
(1 row)
Output of Test case 2:
vkey
------
(0 rows)
--- Postgres version ---
Github commit: efeb12ef0bfef0b5aa966a56bb4dbb1f936bda0c
Version: PostgreSQL 16beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
--- Platform information ---
Platform: Ubuntu 20.04
Kernel: Linux 5.4.0-147-generic
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17986: Inconsistent results of SELECT affected by btree index
@ 2023-06-20 23:40 Tomas Vondra <tomas.vondra@enterprisedb.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: Tomas Vondra @ 2023-06-20 23:40 UTC (permalink / raw)
To: zuming.jiang@inf.ethz.ch, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
On 6/20/23 22:54, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17986
> Logged by: Zuming Jiang
> Email address: zuming.jiang@inf.ethz.ch
> PostgreSQL version: 16beta1
> Operating system: Ubuntu 20.04
> Description:
>
> My fuzzer finds a correctness bug in Postgres, which makes Postgres return
> inconsistent results. This bug can be reproduced even after applying the
> fixing patches for
> https://www.postgresql.org/message-id/flat/17976-4b638b525e9a983b%40postgresql.org
>
> https://www.postgresql.org/message-id/flat/17982-3fa239feecd6c1b3%40postgresql.org
> https://www.postgresql.org/message-id/flat/17985-748b66607acd432e%40postgresql.org
>
> ...
>
> --- Postgres version ---
> Github commit: efeb12ef0bfef0b5aa966a56bb4dbb1f936bda0c
> Version: PostgreSQL 16beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
>
I can't reproduce this with current master - perhaps one of the commits
since efeb12ef0b fixes this too? Those should be for the patches you
mentioned, but it's likely a bit improved.
Can you try with current master?
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17986: Inconsistent results of SELECT affected by btree index
@ 2023-06-21 04:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tomas Vondra <tomas.vondra@enterprisedb.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-06-21 04:11 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@enterprisedb.com>; +Cc: zuming.jiang@inf.ethz.ch; pgsql-bugs@lists.postgresql.org
Tomas Vondra <tomas.vondra@enterprisedb.com> writes:
> On 6/20/23 22:54, PG Bug reporting form wrote:
>> My fuzzer finds a correctness bug in Postgres, which makes Postgres return
>> inconsistent results.
> I can't reproduce this with current master - perhaps one of the commits
> since efeb12ef0b fixes this too?
It looks like this is another manifestation of the hashjoin bug
fixed in 45392626c.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17986: Inconsistent results of SELECT affected by btree index
@ 2023-06-21 07:22 Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Zu-Ming Jiang @ 2023-06-21 07:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Tomas Vondra <tomas.vondra@enterprisedb.com>; +Cc: pgsql-bugs@lists.postgresql.org
Thanks for your information! I checked it again. Seems this bug can be
reproduced when I used "quick-fix-for-bug-17985.patch" for bug 17985,
but cannot be reproduced after I used "better-fix-for-bug-17985.patch".
So it was fixed by the patch "better-fix-for-bug-17985.patch".
Best wishes,
Zuming
------------------------------------------------------------------------
*From:* Tom Lane [mailto:tgl@sss.pgh.pa.us]
*Sent:* Wednesday, June 21, 2023 at 6:11 AM
*To:* Tomas Vondra
*Cc:* zuming.jiang@inf.ethz.ch; pgsql-bugs@lists.postgresql.org
*Subject:* BUG #17986: Inconsistent results of SELECT affected by btree
index
> Tomas Vondra<tomas.vondra@enterprisedb.com> writes:
>> On 6/20/23 22:54, PG Bug reporting form wrote:
>>> My fuzzer finds a correctness bug in Postgres, which makes Postgres return
>>> inconsistent results.
>> I can't reproduce this with current master - perhaps one of the commits
>> since efeb12ef0b fixes this too?
> It looks like this is another manifestation of the hashjoin bug
> fixed in 45392626c.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17986: Inconsistent results of SELECT affected by btree index
@ 2023-06-21 13:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2023-06-21 13:25 UTC (permalink / raw)
To: Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>; +Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>; pgsql-bugs@lists.postgresql.org
Zu-Ming Jiang <zuming.jiang@inf.ethz.ch> writes:
> Thanks for your information! I checked it again. Seems this bug can be
> reproduced when I used "quick-fix-for-bug-17985.patch" for bug 17985,
> but cannot be reproduced after I used "better-fix-for-bug-17985.patch".
> So it was fixed by the patch "better-fix-for-bug-17985.patch".
Oh, that's very interesting. There must be some other code path in
the executor that responds to the Hash node's allParams bit, and
doesn't do the right thing unless it's set. Not totally surprising
I guess; the quick-fix thing was definitely a hack.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-06-21 13:25 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-20 20:54 BUG #17986: Inconsistent results of SELECT affected by btree index PG Bug reporting form <noreply@postgresql.org>
2023-06-20 23:40 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-06-21 04:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-06-21 07:22 ` Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>
2023-06-21 13:25 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox