Search fails when using shelving filter in OPAC with no transcendent bib sources

[Jeff Davis]

EG 3.1+

If your system has no transcendent bib sources, using the shelving location filter in the OPAC will always return zero search results. This is due to a syntax error in the constructed SQL query.

Steps to reproduce:

1. UPDATE config.bib_source SET transcendant = false;
2. Restart services (required due to immutable database function, see below).
3. Do an advanced search in the OPAC using the shelving location filter. The bib search SQL query fails with a syntax error and your search therefore returns zero results.

When you use a shelving location filter in the OPAC, your search query includes this line:

,b_attr AS (SELECT (RTRIM(b_attrs,'|&'))::query_int AS vis_test FROM asset.patron_default_visibility_mask() x)

This causes a syntax error if b_attrs is an empty string. The value of b_attrs is derived from asset.bib_source_default(), which only looks at transcendent bib sources; if you don't have any transcendent bib sources, b_attrs will be an empty string. EG needs to take that possibility into account in order to avoid the syntax error. I don't have an immediate fix.

A few notes:

- Since the asset.bib_source_default function is immutable, any changes to config.bib_source.transcendant may not be immediately reflected in your results (i.e. you might not get the syntax error right away). Restarting EG services after setting all bib sources to transcendant = false seemed to force the error to show up in my test environment.

- Staff client search queries are apparently not affected by the syntax error because they are constructed slightly differently.

- I've only tested on 3.1, but it looks like the asset.patron_default_visibility_mask function has not changed, so all versions of EG through master may be affected.

[Jeff Davis (jdavis-sitka)]

Bug 1744489 sounds similar but is a different issue.

[Jeff Davis (jdavis-sitka)]

I opened bug 1828301 to address the fact that asset.bib_source_default() isn't actually immutable. It's a separate issue but it does complicate testing of the shelving location issue in the present bug.