Обсуждение: BUG #18092: Unexpected Result by enable_seqscan
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"
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.
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