pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18092: Unexpected Result by enable_seqscan
3+ messages / 3 participants
[nested] [flat]
* BUG #18092: Unexpected Result by enable_seqscan
@ 2023-09-07 05:52 PG Bug reporting form <noreply@postgresql.org>
2023-09-07 07:35 ` Re: BUG #18092: Unexpected Result by enable_seqscan Francisco Olarte <folarte@peoplecall.com>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2023-09-07 05:52 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: bajinsheng@u.nus.edu
The following bug has been logged on the website:
Bug reference: 18092
Logged by: Jinsheng Ba
Email address: bajinsheng@u.nus.edu
PostgreSQL version: 15.4
Operating system: Ubuntu
Description:
The same query returns different results when enable_seqscan is changed:
CREATE TABLE t0(c0 FLOAT);
INSERT INTO t0(c0) VALUES(0.8);
CREATE VIEW v0(c0) AS (SELECT 1);
CREATE INDEX i0 ON t0(c0);
UNLISTEN *;
ANALYZE;
INSERT INTO t0(c0) VALUES(0.1);
SET enable_seqscan=on;
SELECT DISTINCT ON (v0.c0) t0.c0 FROM v0, t0; -- {0.8}
SET enable_seqscan=false;
SELECT DISTINCT ON (v0.c0) t0.c0 FROM v0, t0; -- {0.1}
Reproduction:
docker run -p 5432:5432 -e POSTGRES_PASSWORD=root -it postgres:latest
psql "postgresql://postgres:root@localhost:5432"
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18092: Unexpected Result by enable_seqscan
2023-09-07 05:52 BUG #18092: Unexpected Result by enable_seqscan PG Bug reporting form <noreply@postgresql.org>
@ 2023-09-07 07:35 ` Francisco Olarte <folarte@peoplecall.com>
2023-09-07 11:05 ` Re: BUG #18092: Unexpected Result by enable_seqscan David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Francisco Olarte @ 2023-09-07 07:35 UTC (permalink / raw)
To: bajinsheng@u.nus.edu; pgsql-bugs@lists.postgresql.org
On Thu, 7 Sept 2023 at 09:10, PG Bug reporting form
<noreply@postgresql.org> wrote:
> The same query returns different results when enable_seqscan is changed:
> CREATE TABLE t0(c0 FLOAT);
> INSERT INTO t0(c0) VALUES(0.8);
> CREATE VIEW v0(c0) AS (SELECT 1);
> CREATE INDEX i0 ON t0(c0);
> UNLISTEN *;
> ANALYZE;
> INSERT INTO t0(c0) VALUES(0.1);
>
> SET enable_seqscan=on;
> SELECT DISTINCT ON (v0.c0) t0.c0 FROM v0, t0; -- {0.8}
> SET enable_seqscan=false;
> SELECT DISTINCT ON (v0.c0) t0.c0 FROM v0, t0; -- {0.1}
This seems a variation of your previous report. To me your queries
looks like you are asking Pg to "give me any row from this sets" and
then complaining when it does not give you the same under different
conditions.
Something like you put a red ball on the left of a table, a black one
on the right, ask me to pick one ball and then complain when I pick
the black when unconstrained and the red when constrained by "using
only your left hand".
Francisco Olarte.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18092: Unexpected Result by enable_seqscan
2023-09-07 05:52 BUG #18092: Unexpected Result by enable_seqscan PG Bug reporting form <noreply@postgresql.org>
2023-09-07 07:35 ` Re: BUG #18092: Unexpected Result by enable_seqscan Francisco Olarte <folarte@peoplecall.com>
@ 2023-09-07 11:05 ` David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: David Rowley @ 2023-09-07 11:05 UTC (permalink / raw)
To: Francisco Olarte <folarte@peoplecall.com>; +Cc: bajinsheng@u.nus.edu; pgsql-bugs@lists.postgresql.org
On Thu, 7 Sept 2023 at 19:35, Francisco Olarte <folarte@peoplecall.com> wrote:
>
> On Thu, 7 Sept 2023 at 09:10, PG Bug reporting form
> > SET enable_seqscan=on;
> > SELECT DISTINCT ON (v0.c0) t0.c0 FROM v0, t0; -- {0.8}
> > SET enable_seqscan=false;
> > SELECT DISTINCT ON (v0.c0) t0.c0 FROM v0, t0; -- {0.1}
>
> This seems a variation of your previous report. To me your queries
> looks like you are asking Pg to "give me any row from this sets" and
> then complaining when it does not give you the same under different
> conditions.
Yeah, I agree. This seems documented in [1]:
"Note that the “first row” of each set is unpredictable unless ORDER
BY is used to ensure that the desired row appears first."
David
[1] https://www.postgresql.org/docs/15/sql-select.html#SQL-DISTINCT
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-09-07 11:05 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-07 05:52 BUG #18092: Unexpected Result by enable_seqscan PG Bug reporting form <noreply@postgresql.org>
2023-09-07 07:35 ` Francisco Olarte <folarte@peoplecall.com>
2023-09-07 11:05 ` David Rowley <dgrowleyml@gmail.com>
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