CLUSTER sort on abbreviated expressions is broken

From: Thomas Munro <thomas(dot)munro(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: CLUSTER sort on abbreviated expressions is broken
Date: 2022-04-03 04:05:00
Message-ID: CA+hUKG+bA+bmwD36_oDxAoLrCwZjVtST2fqe=b4=qZcmU7u89A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

Independently of a problem with a recent commit, it seems that
$SUBJECT in all releases (well, I only tested as far back as 11). I
attach an addition to the tests to show this, but here's a stand-alone
repro:

DROP TABLE IF EXISTS clstr_expression;

CREATE TABLE clstr_expression(id serial primary key, a int, b text COLLATE "C");
INSERT INTO clstr_expression(a, b) SELECT g.i % 42, 'prefix'||g.i FROM
generate_series(1, 133) g(i);
CREATE INDEX clstr_expression_minus_a ON clstr_expression ((-a), b);
CREATE INDEX clstr_expression_upper_b ON clstr_expression ((upper(b)));

CLUSTER clstr_expression USING clstr_expression_minus_a;
WITH rows AS
(SELECT ctid, lag(a) OVER (ORDER BY ctid) AS la, a FROM clstr_expression)
SELECT * FROM rows WHERE la < a;

All good, and now for the part that I think is misbehaving:

CLUSTER clstr_expression USING clstr_expression_upper_b;
WITH rows AS
(SELECT ctid, lag(b) OVER (ORDER BY ctid) AS lb, b FROM clstr_expression)
SELECT * FROM rows WHERE upper(lb) > upper(b);

That should produce no rows. It works as expected if you SET
enable_seqscan = off and re-run CLUSTER, revealing that it's the
seq-scan-and-sort strategy that is broken. It also works as expected
for non-yet-abbreviatable collations.

Attachment Content-Type Size
0001-Add-simple-test-for-CLUSTER-on-expression-indexes.patch text/x-patch 2.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrei Zubkov 2022-04-03 04:32:47 Re: [PATCH] Tracking statements entry timestamp in pg_stat_statements
Previous Message Thomas Munro 2022-04-03 02:52:37 Re: CFBot failing with "Failed to start an instance"