Thread: 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