Cross-shard ORDER BY ignores expression sort keys

[levkk]

Cross-shard ORDER BY ignores expression sort keys

pgdog-enterprise ea00ace6, three shards.

When a query runs on several shards, pgdog merges the rows but ignores sort keys that are expressions. It sorts by the plain column keys only. The result is in the wrong order, and nothing reports an error.

Configuration

[[sharded_tables]]
database = "app"
column = "tenant_id"

CREATE TABLE tenant_items (
    tenant_id bigint NOT NULL,
    id bigint NOT NULL,
    name text NOT NULL,
    PRIMARY KEY (tenant_id, id)
);

Steps

INSERT INTO tenant_items (tenant_id, id, name) VALUES
    (1, 11, 'free'), (2, 12, 'pay'), (3, 13, 'free'),
    (4, 14, 'pay'), (5, 15, 'free'), (6, 16, 'pay');

SELECT id, name FROM tenant_items
ORDER BY CASE name WHEN 'pay' THEN 0 ELSE 1 END, id;

Expected

 12 | pay
 14 | pay
 16 | pay
 11 | free
 13 | free
 15 | free

Actual

 11 | free
 12 | pay
 13 | free
 14 | pay
 15 | free
 16 | pay

The rows are sorted by id only. The CASE key is dropped.

Workaround

Select the expression and order by its position. This returns the expected order:

SELECT id, name, CASE name WHEN 'pay' THEN 0 ELSE 1 END AS prio
FROM tenant_items
ORDER BY 3, 1;

Selecting the expression and repeating it in ORDER BY does not help. Ordering by the alias (ORDER BY prio, id) fails with column "prio" does not exist.

Expected behavior

pgdog sorts the merged rows by every ORDER BY key, including expressions. If it cannot, it rejects the query instead of returning rows in a different order.

[levkk]

Leaving notes here:

1. we need to move the ORDER BY handler into the rewriter (post route)
   2 we need to rewrite the query and move the CASE statement into the target column list and replace the ORDER BY predictable by a column reference (that's the "workaround")
2. we need to handle aggregates, since ordering can impact those (not sure how, need to dig in)

Current PR (#1742) implements the first two, but doesn't pass CI yet.