Aggregation + order + json field accessor + prepared statements = broken

[wolfgangwalther]

With our nix tooling, run this:

PGRST_DB_AGGREGATES_ENABLED=1 postgrest-with-postgresql-16 -f test/spec/fixtures/load.sql postgrest-run

Then make this request:

% curl --fail-with-body 'http://localhost:3000/complex_items?select=settings->foo->>bar,count()&order=settings->foo->>bar'
curl: (22) The requested URL returned error: 400
{"code":"42803","details":null,"hint":null,"message":"column \"complex_items.settings\" must appear in the GROUP BY clause or be used in an aggregate function"}%               

I get the following log:

2024-06-27 14:12:00.728 UTC [4014043] DETAIL:  parameters: $1 = '"test", "public"', $2 = 'postgrest_test_anonymous', $3 = '{"role":"postgrest_test_anonymous"}', $4 = 'GET', $5 = '/complex_items', $6 = '{"user-agent":"curl/8.8.0","host":"localhost:3000","accept":"*/*"}', $7 = '{}'
2024-06-27 14:12:00.729 UTC [4014043] ERROR:  column "complex_items.settings" must appear in the GROUP BY clause or be used in an aggregate function at character 171
2024-06-27 14:12:00.729 UTC [4014043] STATEMENT:  WITH pgrst_source AS ( SELECT "test"."complex_items"."settings"->$1->>$2 AS "bar", COUNT("test"."complex_items".*) FROM "test"."complex_items"    GROUP BY "bar" ORDER BY "test"."complex_items"."settings"->$3->>$4    )  SELECT null::bigint AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, coalesce(json_agg(_postgrest_t), '[]') AS body, nullif(current_setting('response.headers', true), '') AS response_headers, nullif(current_setting('response.status', true), '') AS response_status, '' AS response_inserted FROM ( SELECT * FROM pgrst_source ) _postgrest_t

Note the following subquery we create:

SELECT
  "test"."complex_items"."settings"->$1->>$2 AS "bar",
  COUNT("test"."complex_items".*)
FROM "test"."complex_items"
GROUP BY "bar"
ORDER BY "test"."complex_items"."settings"->$3->>$4

This breaks when PREPAREing the query, because it's not known to PostgreSQL that $3 and $4 are the same as $1 and $2.

On the SQL level there are multiple ways do get a proper query:

ORDER BY 1
ORDER BY "bar"
ORDER BY "test"."complex_items"."settings"->$1->>$2

The first two don't work with PostgREST right now, because we qualify everything in order= with the table name:

{"code":"42703","details":null,"hint":null,"message":"column complex_items.1 does not exist"}
{"code":"42703","details":null,"hint":null,"message":"column complex_items.bar does not exist"}

The last one... is not that easy to implement, I guess.

What would happen if we were not qualifying the order-by columns with the table name? Or maybe only if we get an integer-only order-by?

[wolfgangwalther]

What would happen if we were not qualifying the order-by columns with the table name?

Answer: It would break computed columns.

[laurenceisla]

The last one... is not that easy to implement, I guess.

One way to do it would be to check every entry in order=... and verify if it's present as an alias in the select=.... If it is, then sent as-is. If it's not, then qualify it. It should also help in ordering by the aggregates themselves, too.

Another way would be to send orders ending in or starting with : as aliases, e.g. oder=bar: or something like that, to avoid checking every entry.

[wolfgangwalther]

One way to do it would be to check every entry in order=... and verify if it's present as an alias in the select=.... If it is, then sent as-is. If it's not, then qualify it. It should also help in ordering by the aggregates themselves, too.

Technically this would be a breaking change in cases where somebody was renaming table.column_a to column_b via alias - but was still ordering by table.column_b.