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.
