Unexpected results when embedding and filtering the same table twice #4075
Description
Environment
- PostgreSQL version: v17
- PostgREST version: v10.2.0 to v13.0.0
- Operating system: MacOS
Description of issue
This happened to a user in v10.2.0 but I could reproduce it in later versions including v13.0.0. Taking these tables and data:
create type visit_type as enum ('vacation', 'work');
create table places (
id int primary key generated always as identity,
name text not null
);
create table visits (
id int primary key generated always as identity,
place_id int not null references places(id),
start_time timestamp,
end_time timestamp,
visit_type visit_type
);
insert into places (name) values ('Lake'), ('Mountain'), ('Beach');
insert into visits (place_id, start_time, end_time, visit_type)
values (1, '2025-01-01 10:00','2025-01-01 11:00', 'vacation'),
(1, '2025-01-01 15:00','2025-01-01 16:00', 'vacation'),
(1, '2025-01-01 20:00', '2025-01-01 21:00', 'work'),
(2, '2024-11-01 09:00','2024-11-01 10:00', 'vacation'),
(3, '2024-12-02 13:00','2024-12-02 14:00', 'vacation'),
(1, '2023-01-02 20:00','2023-01-01 21:00', 'work');If we take places and embed visits twice (one for work and others for non-work visits) we get the following response:
curl --get 'localhost:3030/places'\
-d 'select=name,visits(*),work_visits:visits(*)' \
-d 'id=eq.1' \
-d 'visits.start_time=gt.20250101+00:00' \
-d 'visits.end_time=lt.20250101+24:00' \
-d 'visits.visit_type=neq.work' \
-d 'work_visits.start_time=gt.20250101+00:00' \
-d 'work_visits.end_time=lt.20250101+24:00' \
-d 'work_visits.visit_type=eq.work'[
{
"name": "Lake",
"work_visits": [],
"visits": [
{
"id": 1,
"end_time": "2025-01-01T11:00:00",
"place_id": 1,
"start_time": "2025-01-01T10:00:00",
"visit_type": "vacation"
},
{
"id": 2,
"end_time": "2025-01-01T16:00:00",
"place_id": 1,
"start_time": "2025-01-01T15:00:00",
"visit_type": "vacation"
},
{
"id": 3,
"end_time": "2025-01-01T21:00:00",
"place_id": 1,
"start_time": "2025-01-01T20:00:00",
"visit_type": "work"
},
{
"id": 6,
"end_time": "2023-01-01T21:00:00",
"place_id": 1,
"start_time": "2023-01-02T20:00:00",
"visit_type": "work"
}
]
}
]The work_vists embed doesn't show anything (it should show the one with "id": 3), and the visits embed shows work visits and even those outside of the date range. There may be something wrong when building the query and assigning the filters to the joined tables.
SQL output
WITH pgrst_source AS
(SELECT "public"."places"."name",
COALESCE("places_work_visits_1"."places_work_visits_1", '[]') AS "work_visits",
COALESCE("places_visits_1"."places_visits_1", '[]') AS "visits"
FROM "public"."places"
LEFT JOIN LATERAL
(SELECT json_agg("places_work_visits_1")::jsonb AS "places_work_visits_1"
FROM
(SELECT "visits_1".*
FROM "public"."visits" AS "visits_1"
WHERE "visits_1"."start_time" > $1
AND "visits_1"."end_time" < $2
AND "visits_1"."visit_type" <> $3
AND "visits_1"."start_time" > $4
AND "visits_1"."end_time" < $5
AND "visits_1"."visit_type" = $6
AND "visits_1"."place_id" = "public"."places"."id") AS "places_work_visits_1") AS "places_work_visits_1" ON TRUE
LEFT JOIN LATERAL
(SELECT json_agg("places_visits_1")::jsonb AS "places_visits_1"
FROM
(SELECT "visits_1".*
FROM "public"."visits" AS "visits_1"
WHERE "visits_1"."place_id" = "public"."places"."id") AS "places_visits_1") AS "places_visits_1" ON TRUE
WHERE "public"."places"."id" = $7)
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
-- [36659] DETAIL: Parameters: $1 = '2025-01-01 00:00:00', $2 = '2025-01-02 00:00:00', $3 = 'work', $4 = '2025-01-01 00:00:00', $5 = '2025-01-02 00:00:00', $6 = 'work', $7 = '1'Solution
Permanent solution is pending but the workaround is to use an alias for both tables. The alias must be different from the table name, e.g. using place_visits:place_visits still fails, but non_work_visits:place_visits will work as expected.