% 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?
With our nix tooling, run this:
Then make this request:
I get the following log:
Note the following subquery we create:
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:
The first two don't work with PostgREST right now, because we qualify everything in
order=
with the table name: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?