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 |
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" |