Skip to content

collection free-text search fails when optional fields are null #344

@hrodmn

Description

@hrodmn

The logic that handles free-text queries was not working as expected when either title or keywords was null:

where_segments := where_segments || format(
$quote$
(
to_tsvector('english', content->'properties'->>'description') ||
to_tsvector('english', content->'properties'->>'title') ||
to_tsvector('english', content->'properties'->'keywords')
) @@ %L
$quote$,
ft_query
);

The test cases that I added all had title, description, keywords filled out so the null cases were never tested!

(
'Stranger Things',
'Some teenagers drop out of school to fight monsters',
'monster, scary, dark, 80s',
-180, -90, 180, 90,
'2016-01-01T00:00:00Z',
'2025-12-31T23:59:59Z'
),
(
'The Bear',
'Another story about why you should not start a restaurant',
'restaurant, funny, sad, great',
-180, -90, 180, 90,
'2022-01-01T00:00:00Z',
'2025-12-31T23:59:59Z'
),
(
'Godzilla',
'A large lizard takes its revenge',
'scary, lizard, monster',
-180, -90, 180, 90,
'1954-01-01T00:00:00Z',
null
),
(
'Chefs Table',
'Another great story that make you wonder if you should go to a restaurant',
'restaurant, food, michelin',
-180, -90, 180, 90,
'2019-01-01T00:00:00Z',
'2025-12-31T23:59:59Z'
);

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions